16 Cores 12 GB RAM server MySql Configuration - my.cnf

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

@DTest Table Structure

CREATE TABLE IF NOT EXISTS `nameapp` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) NOT NULL,
  `name` varchar(225) CHARACTER SET latin1 NOT NULL,
  `pub` int(11) NOT NULL,
  `datetime` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

Slow query log

# User@Host: exootnet_lab[exootnet_lab] @ localhost []
# Query_time: 2.044988  Lock_time: 0.000098 Rows_sent: 0  Rows_examined: 0
SET timestamp=1326290464;
INSERT INTO `nameapp` (`uid`,`name`,`pub`,`datetime`) VALUES ('455330','AWESOMENESS','1','1326290462');

Answer 1

As a first course of action, I'd watch the output of 'iostat -xk 10 10' to see if the disk IO is saturating the line.

I notice that you sent specs for your memory system, but not your disk system. What's the underlying array here that you're writing so much to? If you are insert-heavy, then that could play a big part.