How many selects per second can a mysql server run?
I'm writing a business plan and I have to simulate the cost when my website will be reach from 500,000 unique visitors.
- visitors: 500,000
- pageviews: 1,500,000
- spider pageviews: 500,000
- total pageviews: 2,000,000
Each page does 50 queries +-
- queries per day: 100 Million
- per hour: 4 Million
- per minute: 70,000
- per second: 1,200
- peak: 3,000
Doing this calculation I need to perform 3,000 queries second... what kind of server can handle it?
The problem is: actually my site is doing 2,000 visits day, and having -+ 150/200 queries / second... starting from this point I'll expect 50,000 queries / second.
How many servers I need in cluster or replication do manage this job?
I used to work for an e-commerce company with a website that had several million page hits per day. We had a single DELL PE 1750 with 2 single core CPUs and 2GB of RAM, database size approx. 4GB. At peak times this server handled up to 50k+ queries per second.
Having said this: the database was well structured, all queries were finely tuned (we had weekly sessions analysing the slow query logs and fixing up queries and indexes) and the server setup was also fine-tuned. Caching is definitely a good idea, but MySQL does that anyway, you just have to analyse the performance and then fine tune how your memory is used (query cache vs other options).
From that experience I can tell you that the highest impact is caused by missing indexes, wrong indexes and bad database design (e.g. long string fields as primary keys and similar nonsense).
It all depends on how complex the query is, and how much memory the servers have, and how fast the disks are.
If the queries are very simple, or very well tuned then a single large database server can handle that. If however the queries are very complex (or simple but poorly tuned) then you'll need several servers.