Mysql & replication, please advise

Forum Moderators: open

Message Too Old, No Replies

Mysql & replication, please advise

FiRe

5:37 pm on Oct 30, 2008 (gmt 0)

I have a site that hosts a chatroom built using AJAX and PHP running off 1 MySQL database.

When you are in a chatroom it fires off an AJAX request every 2.5 seconds which does a number of queries to return the latest chat conversations and user lists. If there are 100 users in a chatroom I am going to have big issues with MySQL such as too many connections and so on.

Would you recommend I setup replication and have a master and a slave? This chatroom is completely free I am not making any money so I dont have the budget to buy multiple servers (I own a linux VPS). How else could I avoid scaling problems?

Thanks for your advice!

physics

12:49 am on Nov 6, 2008 (gmt 0)

If I understand the question correctly then cacheing might be your answer.
So if there are 100 users, one will fire off the AJAX request, if the query hasn't been performed in the past 3 seconds then perform the query and dump the results in a file (and use them), if it has been performed in the last few seconds (check timestamp of file), then use the cached version of the results.
Repeat.
This way the query is only performed once every three seconds no matter how many users there are and the load is taken off the database.