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.

We are using MySQL 5.5 and the database is approximately 10TB. The load avg is constantly high during day time. Following are the configurations and a snap shot of a 'top' command. I wanted suggestions on what can be optimized and also want to find out, is the Server CPU bound or IO bound or has memory issues?

24 core CPU

The load average even reaches 45 regularly.

Top output:

13:58:56 up 19 days, 10:43,  2 users,  load average: 25.29, 23.83, 24.75
Tasks: 464 total,   1 running, 463 sleeping,   0 stopped,   0 zombie
Cpu(s): 44.8%us,  4.8%sy,  0.0%ni, 40.0%id,  7.1%wa,  0.1%hi,  3.1%si,  0.0%st
Mem:  528608200k total, 526427308k used,  2180892k free,    64988k buffers
Swap:  2097144k total,      440k used,  2096704k free, 151052240k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                                  
20923 mysql     15   0  353g 352g 7224 S 1250.4 69.9 171156:43 mysqld 

Configuration:

##### SIZES
tmp_table_size                 = 16G
max_heap_table_size            = 16G
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 4096
thread_cache_size              = 100
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 4096
and innodb_read and write_io_threads are set to the default value of 4

##### INNODB 
innodb_buffer_pool_size        = 300G
innodb_buffer_pool_instances   = 8
innodb-old-blocks-pct          = 25
innodb-old-blocks-time         = 1000
innodb_flush_neighbor_pages    = area
innodb_io_capacity             = 20000
innodb_adaptive_flushing_method= estimate
innodb_stats_on_metadata       = ON
innodb_buffer_pool_restore_at_startup = 86400
innodb_log_files_in_group      = 2
innodb_log_file_size           = 2G
innodb_log_buffer_size         = 32M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_open_files              = 4000

Any suggestions or help is appreciated.

share|improve this question
    
Please take more time to format your question next time - people are more likely to help you. –  Phil Dec 23 '13 at 21:13
    
Sure, will take care next time. Thanks! –  user3117535 Dec 23 '13 at 21:30
    
Anyone? Any help or suggestions would help. Thanks. –  user3117535 Dec 27 '13 at 17:42

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.