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.
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
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.
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.
Read my blog | Twitter Interested in Azure, ASP.NET (MVC), jQuery, WCF, EF, MS SQL, ...
Keep the forums clean: report to the moderation team!
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.
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
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.
Read my blog | Twitter Interested in Azure, ASP.NET (MVC), jQuery, WCF, EF, MS SQL, ...
Keep the forums clean: report to the moderation team!
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.
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.
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.
Read my blog | Twitter Interested in Azure, ASP.NET (MVC), jQuery, WCF, EF, MS SQL, ...
Keep the forums clean: report to the moderation team!
bastrix
Member
58 Points
150 Posts
Connecting every 1 second to DB
Apr 30, 2012 05:02 PM|LINK
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!
bastrix
Member
58 Points
150 Posts
Re: Connecting every 1 second to DB
Apr 30, 2012 05:19 PM|LINK
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
vsdev
Contributor
2230 Points
453 Posts
Re: Connecting every 1 second to DB
Apr 30, 2012 05:19 PM|LINK
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.
Blog: dotnetthoughts
Download Capture It Plus
XIII
All-Star
182702 Points
23463 Posts
ASPInsiders
Moderator
MVP
Re: Connecting every 1 second to DB
Apr 30, 2012 05:33 PM|LINK
Hi,
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.
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.
Interested in Azure, ASP.NET (MVC), jQuery, WCF, EF, MS SQL, ...
Keep the forums clean: report to the moderation team!
bastrix
Member
58 Points
150 Posts
Re: Connecting every 1 second to DB
Apr 30, 2012 05:48 PM|LINK
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.
bastrix
Member
58 Points
150 Posts
Re: Connecting every 1 second to DB
Apr 30, 2012 05:52 PM|LINK
Mmm I'm not sure if this will work... And also have not got a clue how to implement this
XIII
All-Star
182702 Points
23463 Posts
ASPInsiders
Moderator
MVP
Re: Connecting every 1 second to DB
Apr 30, 2012 06:12 PM|LINK
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.
Interested in Azure, ASP.NET (MVC), jQuery, WCF, EF, MS SQL, ...
Keep the forums clean: report to the moderation team!
atconway
All-Star
16846 Points
2756 Posts
Re: Connecting every 1 second to DB
Apr 30, 2012 06:42 PM|LINK
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!
bastrix
Member
58 Points
150 Posts
Re: Connecting every 1 second to DB
May 01, 2012 12:59 PM|LINK
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.
XIII
All-Star
182702 Points
23463 Posts
ASPInsiders
Moderator
MVP
Re: Connecting every 1 second to DB
May 01, 2012 01:13 PM|LINK
Hi,
Silverlight seems to go down the same path as Flash so simply invest in HTML5 if you find the time.
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.
Interested in Azure, ASP.NET (MVC), jQuery, WCF, EF, MS SQL, ...
Keep the forums clean: report to the moderation team!