Hi,
I'm facing an issue with my primary mySQL server. It powers a web site,
which loads almost 100% dynamic data. At peak times of the day, it
seems to me that the Database is becomming maxxed. (Thus bogging the
site)
I'm aware that there are simply too many variables to completely answer
this question - number of tables, size of tables, the queries
themselves, system configuration, type of data, program code, etc.. but
I'm hoping to get a ball park idea of the limitations of my current
hardware.
The system is a Pentium 850, 1Gb Ram, Ultra-II SCSI Disks, 256 Mb Cache,
running FreeBSD 4.1 and the binary copy of Mysql for FreeBSD. (Had some
unreleated problems with the thread libraries..)
The system averages 550 Queries per second, over the course of a normal
week. Most of these queries are what I'd describe as "average" joins
on a table of 5000 records to two tables of about 30,000 records. If
anything I've overindexed. the database is a total of 100Mb, and the
system typically has 0 disk usage.
During off peak times I'm getting great performance, Fast -
responsive. During peak hours, the usage spikes to 700 to 900 Queries
per second, the DB box doesn't thrash, it just slows down. Perviously
fast indexed queries start getting logged as slow, and even a lookup by
primary key id can take 2 or 3 seconds. I suspect the system is simply
CPU bound.
So, the question is this: Is the above usage, given an "average"
database, ok code, and optimized queries, considered ok? Or should I be
looking for some kind of configuration/query problem? Have I simply
maxxed out my hardware?
I'm sure that further tuning may be able to squeeze more speed out of
the hardware, but I'm looking for a speed boost of double (new site
coming on line shortly). Yet before throwing more money on hardware (and
the time to setup replication), I'd like to get some kind of 3rd party
information saying "Your system is performing well enough, more tweaks
will only give you a 10 to 20 percent boost."
For people running largish mySQL servers, what kind of hardware do you
have to support a volume in the range of 2,000+ queries per second?
Thanks, I appreciate any time taken in answers.
--A
Ps: If I've missed any good references to this in documentation, please
let me know - I haven't found a good reference for "what is good
performance" in any searching.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
