I manage a fairly large/busy vBulletin forums (running on gigenet cloud), the database is ~ 10 GB (~9 milion posts, ~60 queries per second), lately MySQL have been grinding the disk like there's no tomorrow according to iotop and slowing the site.
The last idea I can think of is using replication, but I'm not sure how much that would help and worried about database sync.
I'm out of ideas, any tips on how to improve the situation would be highly appreciated.
Specs :
Debian Lenny 64bit
~12Ghz (6x2GHz) CPU, 7520gb RAM, 160gb disk.
Kernel : 2.6.32-4-amd64
mysqld Ver 5.1.54-0.dotdeb.0 for debian-linux-gnu on x86_64 ((Debian))
Other software:
vBulletin 3.8.4
memcached 1.2.2
PHP 5.3.5-0.dotdeb.0 (fpm-fcgi) (built: Jan 7 2011 00:07:27)
lighttpd/1.4.28 (ssl) - a light and fast webserver
PHP and vBulletin are configured to use memcached.
MySQL Settings :
[mysqld]
key_buffer = 128M
max_allowed_packet = 16M
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 1024
query_cache_limit = 2M
query_cache_size = 128M
expire_logs_days = 10
max_binlog_size = 100M
key_buffer_size = 128M
join_buffer_size = 8M
tmp_table_size = 16M
max_heap_table_size = 16M
table_cache = 96
Other :
From the cloud's IO chart, we're averaging 100mb/s read.
> vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
9 0 73140 36336 8968 1859160 0 0 42 15 3 2 6 1 89 5
> /etc/init.d/mysql status
Threads: 49 Questions: 252139 Slow queries: 164 Opens: 53573 Flush tables: 1 Open tables: 337 Queries per second avg: 61.302.
moved from superuser
