I am a newbie to web programming. I am building a community site with multiple read/write queries at every page. Everything working well for now. But as the site grows I am afraid my single MySQL server will not be able to handle all the traffic and will get the dreaded "too many connections" error. I have looked into replication and splitting the read queries to the slave and the write to the master seems effecient. But are there any other ways of doing this? Is this how major website sout there do it?
You may want to post how many users you expect, page views, SQL queries. Per month or per day, although the critical number is what is the stress level for rush hour traffic (lunch hour?). What type of growth are you expecting that is causing fear? Is the application giving you trouble already?
Before you get a second server, make sure you make this one as efficient as possible.
- Views indexed correctly?
- Joins used where possible to minimize SQL queries?
- Information is cached in memory if static?
Tons of other performance tweaks you can do. If all these are done, upgrading memory could help. Once the application is a peak performer and still needs more juice it's be time to look at either additional or better servers.
we are actually in a process of building a affiliate pogram and the requirements of the program are that it display the raw clicks and uniques and sales for a affiliate for this week and previous week. According to the first program design every signle field was being calculated from a table which logs all hits according to date-time. That ersulted in a worst case senerio of 14 queries for raw and uniques and another 14 for sales calculation. So testing it in a 70,000 record set the processing time was more than a second (which was totally unacceptable). We put in a slave just for the affiliate stats and queried that but that didn't help either( so here is where the question came in).
But since the previous days uniques, raw and sales are not goign to change we started dumping them in another table which improved things significantly. So for the moment we are not worried.