Connecting every 1 second to DB

hey everyone,

I'm building a browser game with a frontend in flash, backend in .net, tied together with a Web Service using JSON encoding. Everything seems to work fine, but in some sections of the game, it seems like the DB blocks the connection and it only will work again if I reload the page.

I have the impression MySql blocks the host because of too many open / close connections in a short period of time I haven't manged to debug or log this error yet, all I get is a 500 error. Every time the client does a POST I open and close connection many times.

My question is, if it is essential to make connections every 1 second, should I maintain one global connection instead of opening and closing or maybe should each access to the database be a new thread? Perhaps mysql is getting moody because of interrupted connections. I must bear in mind the idea of this is that many users will be posting simultaneously.

Thanks for the help!

Re: Connecting every 1 second to DB

I was thinking in having the connection as a singleton for each user so they do not open and close continously. Not sure if this will solve the problem though...And I'm not sure if it's a good idea either

Re: Connecting every 1 second to DB

Not sure about MySql, but incase of SQL Server, it pools the connections, so even if you close, SQL server maintains the connection, but if there any change in the connection string, it will recreates or will open a new connection.

Re: Connecting every 1 second to DB

Hi,

bastrix
I'm building a browser game with a frontend in flash

Nowadays I see more and more shifting to HTML5 instead as Safari and IE10 on Metro (Windows 8) will not support plugins anymore like Flash or Silverlight.

bastrix
if it is essential to make connections every 1 second

What I'm wondering about if it's really necessary to write every second to the database. Perhaps you can rather go for a CQRS approach with an event store where you "log" your events and rely on eventual consistency to get things written to the (non)relational database.

Grz, Kris.

Re: Connecting every 1 second to DB

vsdev

Not sure about MySql, but incase of SQL Server, it pools the connections, so even if you close, SQL server maintains the connection, but if there any change in the connection string, it will recreates or will open a new connection.

Yes vsdev, thats exactly what I think the problem is.

XIII, thanks very much for your reply. I'm a flash developer, so I'm very bored of the debate of flash vs HTML 5. That does't mean Im against HTML5, just that I'm not here to discuss that.

Re: Connecting every 1 second to DB

XIII

What I'm wondering about if it's really necessary to write every second to the database. Perhaps you can rather go for a CQRS approach with an event store where you "log" your events and rely on eventual consistency to get things written to the (non)relational database.

Grz, Kris.

Mmm I'm not sure if this will work... And also have not got a clue how to implement this

Re: Connecting every 1 second to DB

bastrix
I'm not sure if this will work...

It depends largely on what you want/need to do with the just inserted information and if it's important to have it there directly on spot. CQRS doesn't promise you that but it promises that somewhere in the, near, future the data will be there. If it's just for storing points or something then you're good to go. If your client applications rely on the one second time frame then it's likely better to opt for something else.

Grz, Kris.

Re: Connecting every 1 second to DB

bastrix
I was thinking in having the connection as a singleton for each user so they do not open and close continously. Not sure if this will solve the problem though

On above, I would say if you can confirm that MySQL does not pool connections, than using a Singleton is the way to go when managing a bunch of connections being opened and closed often. However my next few comments really void the whole 'polling' approach anyways and this might be a moot point.

For the remainder, I am going to stick directly to your questioning line since it seems that your mine is made up on the technologies being used (Flash and .NET), but I agree with Kris in looking to HTML5 web sockets in the future. See the idea is that rather than poll for the data you could use one of the many options to push data to the client. HTML5 web sockets is the best bet in the future. As of today with purely .NET technologies I would recommend a WCF Duplex service with a Silverlight client (see here). COMET is another way to push data to the client today (see here). So you see I would begin to look at technologies and method in the long run that push data to the client rather than relying on the client pulling (via polling every 1 second) the data from the server. You have lots of options.

I am making a stretch here because I am not a Flash developer, but I did find some documentation on using sockets with Flash to potentially push data to your client (you have to investigate further on Flash forums probably to see if this is the correct method). This might take some work to re-engineer from your current poll of the db every 1 second, but the effort will be worth what you get back in smoothness and performance.

flash.net: public class Socket:
http://help.adobe.com/en_US/FlashPlatform/reference/actionscript/3/flash/net/Socket.html

Hope this helps!

Re: Connecting every 1 second to DB

Hi, many thanks for the detailed explanation. HTML 5 or Silverlight or not an option right now I'm afraid. That would mean throwing away 5 months of development, and start studying HTML 5 or Silverlight.

I would be really convenient to find a solution using the current technology. I'm sure there has to be a setting which will increase performance and fix this issue.

I have noticed i have "pooling" set to false in the connection string. So this might not be the issue. I'm going to set up a simple logging system to find the exact mysql error.

Many thanks for thelp.

Re: Connecting every 1 second to DB

Hi,

bastrix
Silverlight

Silverlight seems to go down the same path as Flash so simply invest in HTML5 if you find the time.

bastrix
I have noticed i have "pooling" set to false in the connection string. So this might not be the issue. I'm going to set up a simple logging system to find the exact mysql error.

It's also a good idea to ask at a dedicated MySQL forum about performance. Likely there's some magic setting or way to optimize that you may only find out in such a way. If you do get a solid answer please do come back to this thread.

Grz, Kris.

Re: Connecting every 1 second to DB

bastrix
I would be really convenient to find a solution using the current technology. I'm sure there has to be a setting which will increase performance and fix this issue.

Since you are set with using Flash, did you at least look into using sockets with Flash to push the data as opposed to pulling the data on interval? You too would need to reach out to a Flash forum to get any detailed support on that topic as I have never tried it. The concept of push vs pull though is one to understand regardless of the technology used.

Re: Connecting every 1 second to DB

Yes, I will also check a mySql forum. thanks

atconway

Since you are set with using Flash, did you at least look into using sockets with Flash to push the data as opposed to pulling the data on interval? You too would need to reach out to a Flash forum to get any detailed support on that topic as I have never tried it. The concept of push vs pull though is one to understand regardless of the technology used.

I would love to make a real time game using socket connection, I looked into Smartfoxserver and we got some simple games running in my old company. The thing is it's a lot more messy and expensive to set up. Since this is a hobby-project, and I've worked with web services before, I thought it would be a good solution since real time is not really required for the game. However for the gameplay to be decent, it must update every few seconds.

I've worked with the clientside of casino games and facebook games the las couple of years using this same structure (casino games posted every 1 second, and facebook games posted every 5) , but never built the whole engine from scratch myself. So I'm not very experienced with the server side of the structure, but I know there is a way to make it work :)

Even if I can get it to work posting every 3 to 5 seconds it would be great. But that throws an exception after some time also. So there is definatly something wrong in the server settings...