mysql real-world limitations...
Hey y'all, I was wondering if any seriously hardcore mysql hands could toss out an opinion here...
I've got this app in beta which is gonna scale in a cellular way across lots of different servers -- not federated, not slaved, just basically different servers handling different regions and backing everything up every few minutes to the core. I'm trying to figure out how to maximize the number of users on each server when it launches while minimizing launch costs and keeping it cheaply scalable.
What I want to know is, given the stats listed below, (1). how many users do you think this app will manage to accommodate on the Dual Pentium 4 3.2 Ghz box it's currently running on, before it runs into delays and trouble, and (2). what multiple of that number could be accommodated on a single Xeon box?
I can't seem to find any docs on what the real-world capacities are for a mysql server based on differing processor speeds. Assume for the exercise that the server is in the same room as all the other servers and transfer is done over fiber; I'm guessing the bottleneck will be the SELECTs...
Here are the test averages, for 10 concurrent beta users pushing the system fairly hard:
PHP Code:
(STAT : MIN - MAX, AVG)
QUERIES/SEC : 7 - 82, avg. 24 (including inserts & updates)
INSERTS/SEC : 0 - 4, avg. 0
UPDATES/SEC : 0 - 24, avg. 8
TRAFFIC : Up to 116,500, AVG: 48,900
CPU Usage: u16.9 s10.81 cu642.61 cs0 - .00244% CPU load
.141 requests/sec - 2646 B/second - 18.3 kB/request
14 requests currently being processed, 8 idle workers
(this includes the httpd making the mysql calls).
So if that's 10 users, can I cram 10,000 on there as the CPU load would suggest? Or what do you think the upward limit is on this box, and how many more could I get on a faster one?
Thanks to anyone with the knowledge who responds to this...
(edit) I should have noted too that the query cache hit ratio on those selects is averaging around 45%...
