Very high load avg for MySQL database [closed]

Question

I have a backend server which response to my clients app, it has more than 20k~25k hit per second and this make my server load average very high, I believe that my MySQL config is not appropriate.

In addition, our backend system comes with its own caching system, but some queries can not be cached and my database has 26 tables (All with InnoDB engine) that the largest table has about 600 thousand rows.

My Server Spec

Processor Name  Common KVM processor
Vendor ID   GenuineIntel
CPU Core: DualCore + 2 thread core
Processor Speed (MHz)   3192.746
Total Memory    5991576 kB
Free Memory 206468 kB
Total Swap Memory   4685816 kB
Free Swap Memory    4516588 kB
Apache 2.2.25   Running
MySQL 5.6.17    Running

Operation System

CentOS release 6.3 (Final)
Kernel \r on an \m

Current my.cnf config

[mysqld]
#------ MySQL Base Config --------
port = 9032
#------ InnoDB Config -----------
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_fast_shutdown=0
innodb_log_buffer_size=256M
innodb_log_file_size=512M
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instances=2
query_cache_size=0
#------ Log Slow Queries ---------
#long_query_time=2
#slow_query_log=1
#slow_query_log_file=/etc/mysql_log_files/log-slow-queries.log

MySQL tuner result

 >>  MySQLTuner 1.3.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
[OK] Currently running supported MySQL version 5.6.17
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: Warning: Using a password on the command line interface can be insecure.
+ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
[--] Data in InnoDB tables: 196M (Tables: 37)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 12

-------- Security Recommendations  -------------------------------------------
Warning: Using a password on the command line interface can be insecure.
[OK] All database users have passwords assigned
Warning: Using a password on the command line interface can be insecure.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5h 38m 40s (2M q [109.747 qps], 633K conn, TX: 2B, RX: 2B)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 416.0M global + 1.1M per thread (151 max threads)
[OK] Maximum possible memory usage: 585.9M (10% of installed RAM)
[OK] Slow queries: 0% (881/2M)
[!!] Highest connection usage: 100%  (152/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/97.0K
[OK] Key buffer hit rate: 100.0% (291K cached / 0 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (30 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 678
[!!] Temporary tables created on disk: 49% (284K on disk / 575K total)
[OK] Thread cache hit rate: 84% (97K created / 633K connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 0% (20/5K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[!!] InnoDB  buffer pool / data size: 128.0M/196.4M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce or eliminate persistent connections to reduce connection usage
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (>= 8M)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    innodb_buffer_pool_size (>= 196M)

Load Avg via top command

top - 21:30:55 up  5:31,  1 user,  load average: 113.62, 46.11, 28.78
Tasks: 602 total,  47 running, 555 sleeping,   0 stopped,   0 zombie
Cpu(s): 84.0%us,  5.1%sy,  0.0%ni,  0.0%id,  0.0%wa,  4.5%hi,  5.0%si,  1.4%st
Mem:   5991576k total,  5872992k used,   118584k free,    42204k buffers
Swap:  4685816k total,   170160k used,  4515656k free,  2420740k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 1858 mysql     20   0 3317m 780m 5408 S 115.6 13.3 186:43.13 mysqld
 2661 apache    20   0  151m  15m 4944 S  0.9  0.3   0:09.48 httpd
 7521 apache    20   0  149m  12m 4964 R  0.9  0.2   0:03.38 httpd
 8115 apache    20   0  151m  14m 4544 R  0.9  0.3   0:02.71 httpd
 9529 apache    20   0  149m  11m 3960 S  0.9  0.2   0:00.47 httpd
 9748 apache    20   0  149m  10m 3880 S  0.9  0.2   0:00.27 httpd
 9770 apache    20   0  149m  10m 3908 S  0.9  0.2   0:00.22 httpd
 9844 apache    20   0  151m  13m 4128 R  0.9  0.2   0:00.29 httpd
   11 root      20   0     0    0    0 S  0.7  0.0   4:11.22 events/0
 3363 apache    20   0  150m  13m 4496 R  0.7  0.2   0:06.18 httpd
 6869 apache    20   0  150m  14m 5276 S  0.7  0.2   0:04.88 httpd
 6899 apache    20   0  149m  12m 4568 R  0.7  0.2   0:04.57 httpd
 7428 apache    20   0  150m  13m 4784 R  0.7  0.2   0:03.37 httpd
 7904 apache    20   0  151m  13m 4480 R  0.7  0.2   0:02.15 httpd
 8125 apache    20   0  152m  15m 4620 S  0.7  0.3   0:02.43 httpd
 8150 apache    20   0  152m  15m 4232 S  0.7  0.3   0:02.71 httpd

Any suggestion or ideas on how to come down this high load avg?

P.S I definitely close every connection but I don't know that why MySQL tuner show me highest connections usage!

Answer 1

Improving performance is usually not a single stop solution. But looking at your conf and the results of mysql tuner you can do the following

1. Increase innodb_buffer_pool_size by a LOT. That is usually quite critical for InnoDB tables. Further set the following two paramenters in you cnf file for InnoDB

sort_buffer_size=2M
join_buffer_size=2M

You might need to tweak the values a bit and increase it.

2. Make certain all your join statements use indexes and since you are using InnoDB it will be even better if you use the FOREIGN KEY constrain (MySQL automatically creates an index for that) where appropriate. Also check your slow queries log to see if any other queries are in need of some extra indexes. Use EXPLAIN to help with any optimizations

3. Query Cache. That would have been a certainty if you were using MyISAM tables but with InnoDB is a bit more complicated. It all depends how often you update you data through transactions. You can read up more here . Still you know your application better so that might be an avenue that you will want to explore.

Answer 2

OBSERVATION #1

Take a look back at your top output

Cpu(s): 84.0%us,  5.1%sy,  0.0%ni,  0.0%id,  0.0%wa,  4.5%hi,  5.0%si,  1.4%st

See the last status variable 1.4%st ? What is that ?

According to In Linux “top” command what are us, sy, ni, id, wa, hi, si and st (for CPU usage)?

st, "steal time", is only relevant in virtualized environments. It represents time when the real CPU was not available to the current virtual machine - it was "stolen" from that VM by the hypervisor (either to run another VM, or for its own needs).

OBSERVATION #2

The InnoDB settings you have in the question would suit a bare metal machine or a good VM. You could scale back some of those settings given the steal mentioned earlier.

Since mysqltuner.pl output says this

[!!] InnoDB  buffer pool / data size: 128.0M/196.4M

you have so little InnoDB data (196.4M). Thus, you do not need a bigger buffer pool.

Please take a look at the InnoDB Architecture

When the buffer pool was 128M, you have the log buffer twice as big (256M) OUCH !!! Every time the Log Buffer got full, it was flushing into both redo log files (ib_logfile0 and ib_logfile1).

SUGGESTIONS

Since you only have 6GB of RAM, I recommend the follow settings

innodb_buffer_pool_size=256M
innodb_buffer_pool_instances=1
innodb_log_file_size=64M
innodb_log_buffer_size=32M
innodb_read_io_threads=8
innodb_write_io_threads=8

OPTIONAL : I would make the VM have 8G of RAM instead og 6G

OPTIONAL : You need a better VM with more IOPs.

GIVE IT A TRY !!!

UPDATE 2014-10-10 14:24 EDT

Take another at this

Temporary tables created on disk: 49% (284K on disk / 575K total)

This is usually a strong indicator of the following

• Increase tmp_table_size
• Increase max_heap_table_size

Since you have only 6GB or RAM, or even if you had 8GB, you should leave them alone. What you should work on is finding queries that can benefit from additional indexes.

Please see my posts on various reasons and scenarios for proper indexing of queries

• Oct 17, 2012 : Combining columns in index
• Jan 05, 2012 : How should I design a relationship table for friendship?
• Aug 15, 2011 : How do you increase MySQL performance using current server resources?
• Jul 22, 2011 : Prevent copying to temp table (sql)