Hi, so for purely educational purposes I have been wanting to build a simple chat application. My first intentions where to create a simple form based application and sent and received messages to my database, but this leads me to some questions.
Even if I use AJAX to pull any new messages from the database, how would I be able to make the pulls every few seconds while not overloading MySQL with to many connections?
Not only how would I do this with out overloading MySQL, but killing my hosting bandwidth?
03-23-2009, 12:59 AM
Well, you WILL kill your bandwidth if you have 7,348 simultaneous users. But a handful of users? Each hitting the server with an "anything new" request every (say) 3 to 5 seconds? Noise.
Do I assume you will be coding this in PHP? So does PHP have the equivalent of ASP and ASP.NET's "Application" object? Basically, in those technologies, there is an object that *ALL* threads/pages have acccess to on a shared basis. There's a simple locking mechanism so that one user can change the contents while forcing all others to wait, thus avoiding deadlocks. If you have that capability--or can build it--then the easy answer is that when a user *posts* a message, you add it to the DB, yes, but you *ALSO* add it to an array that is stored at that global level (using lockout, of course). And you timestamp it.
So now when a user's AJAX (or equivalent...consider using an <IFRAME> or maybe even hidden <IFRAME>) hits the site every 3 seconds you only have to ask the application-scope object "what's the latest timestamp". The caller supplies the timestamp of the last message that the caller has knowledge of, of course, and now a simple comparison allows you to send back any new messages.
VERY low DB overhead.
You can, of course, do the equivalent all in the DB. And chances are that if you have any decent caching at all for that DB then the table you are hitting to look for updates will still be "hot" in memory, so it's not really going to be a too-heavy DB hit. But if you can avoid the DB hit, do so.
If you are going to support multiple simultaneous but separate chats, you COULD still use the global app object, but it might be much easier to just use the DB. Depends on what capabilities the hypothetical PHP global scope object/collection/whatever has.
I could write this in ASP in about 30 minutes, and I'd just use an <IFRAME> for the chat area and have the server simply refresh the <IFRAME> each time. Wouldn't be efficient, but would sure be up and running quickly. (Oh, yes, and would support simultaneous separate conversations.)
Yeah PHP and MySQL is what I use with my hosting. I guess I might have been overly worried cause I have never ran so many requests to and from my database before so I just didn't know what my results in both bandwidth and database performance would be like.
Thanks for the advise Old Pedant good looking out.
03-23-2009, 07:45 PM
Standard default for simultaneous mysql connections is 150 IIRC. Don't know what server load that creates, but I'm guessing if that would be large overhead they'd use a lower number.
mysql was set up originally to be consumer/user friendly and fast.
03-23-2009, 08:57 PM
Our installation only had 100, using MySQL with Tomcat and Apache.
But that's PLENTY! Tomcat really bogs down if you give it much more than 20 to 50 threads (depending on the processor[s]). So even with 50 threads [simultaneous pages being served], that allows for 2 connections per thread. In a chat app, I can't see why you'd need more than 1 per thread. We ran stress tests on that configuration with 10,000 simulated clients and never had a real hiccup (or at least none that weren't the fault of our code).
I'd be real surprised if PHP and Apache didn't perform quite similarly.
I also simulated thousand records on my end. With 10 connections going at once. I was receiving a my_sql error about to many connections but after I added the close connection potions to my get and post everything seems to be running smooth.
03-23-2009, 10:10 PM
LOL! Yeah, unlike ASP, if you don't close connections in JSP or PHP you are hosed. Sloppy coding of the frameworks, in my opinion. It wouldn't be hard for the frameworks to keep track of which threads opened a connection and auto-close them when the thread went away or change what page it is servicing. Now, granted, doing it the ASP way leads to sloppy user programming, but I think that's better than taking a site down because of a rare code path that for some reason doesn't get the connection closed.