Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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');
share|improve this question
    
show us the table chemas, show us the sql query, show us the output of "explain extended select....;" - then we can help –  pQd Jan 11 '12 at 13:50
    
@pQd the tables and mysql queries are all optimized up to best of our knowledge the only thing that we're not sure of is the server config. From the current config is theres any bottlenecks that we need to fix? –  kornesh Jan 11 '12 at 13:53
    
is there anything in the slow query log? –  Mike Jan 11 '12 at 14:01
    
@Mike yea there're alots of INSERT queries of (user registration and user activity log queries) –  kornesh Jan 11 '12 at 14:08
    
might help if you paste some of those.. your issues are probably your queries not the server. –  Mike Jan 11 '12 at 14:09
show 10 more comments

1 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.

share|improve this answer
    
I've added the results to the question –  kornesh Jan 11 '12 at 13:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.