Hi,
Yesterday happened to be one of the busiest days for us ever
on our MySQL backed web site. For the entire day MySQL was
hit with up to 1200 queries/second, and many queries were
being delayed at least 2-15 seconds. We were serving about 300%
of our normal load.
In my opinion MySQL scaled more gracefully than I have ever
known in 4 years. It did not go into a downward spiral where once
it went above a threshold all work stopped. Instead, everything became
gradually slower, but still functioned albeit less quickly.
Right now I'm trying to figure out what bottlenecks the RDBMS
was hitting so we could throw some hardware at the problem.
I am not the kind of person to solve technical issues with hardware,
but given that nothing else appears to be wrong and I've
done every reasonable optimization possible, we have little choice.
However, I can't figure out exactly why it was slow. Key efficiency
was at 100%, and I *believe* the InnoDB buffer pool was large enough
as the InnoDB monitor was reporting 1000 / 1000 cache hits.
iostat showed the usual levels of disk I/O.
What other statistics can I look at?
We're using MySQL 3.23.54
Our hardware is dual P3 1GHz, 2GB of RAM, and about 56GB of IDE RAID-1 backed disk
(3ware escalade).
We're running Red Hat's Linux v2.4.7-10smp
my.cnf:
set-variable = innodb_buffer_pool_size=800M
set-variable = innodb_additional_mem_pool_size=50M
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=75M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=0
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
set-variable = wait_timeout=3600
set-variable = tmp_table_size=8000000
set-variable = max_connections=315
set-variable = table_cache=256
set-variable = sort_buffer=4M
set-variable = key_buffer=128M
set-variable = thread_cache_size=350
set-variable = max_connect_errors=50000
Could anyone suggest anything?
--
Michael Bacarella | Netgraft Corp
| 545 Eighth Ave #401
Systems Analysis | New York, NY 10018
Technical Support | 212 946-1038 | 646 641-8662
Managed Services | http://netgraft.com/
---------------------------------------------------------------------
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
