MySQL is killing the server IO.

Posted by OneOfOne on Super User See other posts from Super User or by OneOfOne
Published on 2011-01-14T21:52:49Z Indexed on 2011/01/14 21:55 UTC
Read the original article Hit count: 535

Filed under:
|
|
|

I manage a fairly large/busy vBulletin forums (~2-3k requests per second, 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 (6 cores) 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

© Super User or respective owner

MySQL is killing the server IO.

Posted by OneOfOne on Server Fault See other posts from Server Fault or by OneOfOne
Published on 2011-01-14T22:27:45Z Indexed on 2011/01/14 22:55 UTC
Read the original article Hit count: 535

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

© Server Fault or respective owner

Related posts about mysql

Related posts about slow