Mysql config optimizing - slow IO response time for 12GB RAM 2 x 2.4GHz Server

Question

The server takes 20+ seconds (wait time/slow IO response time) to response to a HTTP request even with memcached and APC installed. I believe this has something to do with MYSQL since the site as lots INSERT queries.

Any help would be greatly appreciated. Thanks in advance.

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
               6.38    0.03    1.05    0.40    0.00   92.14
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          10.37    0.00    1.61    3.14    0.00   84.87
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           9.40    0.00    1.41    1.53    0.00   87.67
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          10.02    0.00    1.42    1.09    0.00   87.46
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           9.32    0.00    1.31    0.78    0.00   88.59
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          10.62    0.00    1.57    2.37    0.00   85.44

Dedicated server specs:

2 x Intel Xeon-Westmere 5620-Quadcore [2.4GHz] processors
6 x Hynix DDR3 2GB DDR3 x8 [2GB] (total of 12GB)
2 x Western Digital WD Caviar RE4 WD5003ABYX [500GB]
MySQL version: 5.1.56-log MySQL Community Server (GPL)
Current database size: 4.2 GiB ~25,350,659rows

Here is the current my.cnf configuration:

[mysqld]
set-variable = max_connections=1024
log-slow-queries
safe-show-database

back_log                        = 80
max_connect_errors              = 9999999
table_cache                     = 5000
binlog_cache_size               = 1M
max_heap_table_size             = 128M
sort_buffer_size                = 500K
join_buffer_size                = 500K
thread_cache_size               = 100
#thread_concurrency              = 16
query_cache_size                = 512M
query_cache_limit               = 8M
query_cache_min_res_unit        = 2K
thread_stack                    = 192K
tmp_table_size                  = 384M
default_table_type              = INNODB


long_query_time                 = 2
log-slow-queries=/var/log/mysql.slow.queries.log

innodb_additional_mem_pool_size = 80M
innodb_buffer_pool_size         = 9G
innodb_file_io_threads          = 4
innodb_thread_concurrency       = 33
innodb_log_buffer_size          = 20M
innodb_log_file_size            = 800M
innodb_log_files_in_group       = 3
innodb_max_dirty_pages_pct      = 90
innodb_lock_wait_timeout        = 120
innodb_file_per_table           = 1


read_rnd_buffer_size=2M

thread_concurrency=33

Answer 1

Check how long are your PHP scripts running using mtime(true). If they are running slow, set "checkpoints" in your scripts to determine which part of scripts slowing down response speed.

Answer 2

You may find the answers from this question usefull. To improve mysql performance, it helped me a lot on my production application. Give this a try.

Answer 3

You can install mytop on your machine to keep track of running queries while tuning your performance. Your problem might not be due to your mysql server...