MySQL chat performance

[bPHP]

Hi! I just created a chat website, which is basically a MySQL based chat. I'm running it on a small 256MB VPS at inmotion hosting... My question is... Is this even possible? Today I had like 30 people chatting and the server crashed... I know I probably don't have it very optimized... I have my.conf for 150 concurrent connections. What suggestions can you give me to increase performance? Do I really need a dedicated server if I wan't to have a lot of people in this chat?

 

Thanks!!!!!

[Kryptix]

Without seeing the code we don't know...

[PFMaBiSmAd]

What do the server logs show as the cause of the 'crash'? You cannot fix a problem until you identify the cause of the problem.

[bPHP]

I don't see anything on the logs... People from the server told me I was using too many resources and my server was shut down. I talked to a systems guy and he said it was do to MySQL processes.

 

The code is pretty big but I can tell you a bit how it works.

When someone logs in it looks in a table of connected people for someone. Then it creates two tables to store the messages, as well as two additional tables for typing a message messages. Then this is not executed anymore and while people are chatting, there is a while loop with a 1 second sleep right before ending it that makes like 3 or 4 SELECTS to look for messages (one for the typing messages). It also does some inserting and deletes the last message read (so I keep the messages table always with one message only)

 

I would say that on average there are 10 database operations per second per person chatting. 40 people would be 400 operations per second... Is this a lot for a 256MB VPS? How much would a dedicated server sustain?

 

I would like some general info on this as well as some information to know if it is worth it to give a dedicated server a try.

 

Thanks for any input and for reading this long message!

[ignace]

From reading your description I can already tell you have gone about it the wrong way. You should not create tables for the moment people log-in they should already be present and serve all of your users.

[bPHP]

Thanks... I'll try to modify that! What about the SQL operations per second? What if I had like 5 per second? Is that still a big number when I have 50 people connected? I'm not really sure how to know this and how to monitor it, I'm kind of a noob when it comes to MySQL.

 

Thanks!

[bPHP]

Also... Any ideas on how to make a proper chat design? It is a 1-1 chat system, you get in, you chat with just one person, and then to talk to another person you have to close the conversation with this person and search for a new one... Any ideas on a good design, if I should keep all conversations in just one table, or if I should create tables for each pair of people... I don't really know how to design this optimally from the database part.

 

Thanks!