Below is my .cnf file and if I see my CPU consumption 99.99% of it my the mysqld commands. The MySQL server is connected from remote machines that update data in it frequently, but I make sure that the remote server's open a connection, read/write/update and then close it. Also the remote server reads a lot.

What can I do to reduce my cpu consumption. FYI, I am using 2 core CPU with 4GB RAM.

[client]
port          = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice          = 0

[mysqld]
user          = mysqluser
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port          = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
wait_timeout    = 20
interactive_timeout = 60

bind-address            = <IP-ADDRESS>
key_buffer            = 16M
max_allowed_packet  = 16M
thread_stack            = 192K
thread_cache_size   = 8
myisam-recover      = BACKUP
max_connections     = 300

query_cache_limit   = 20M
query_cache_size        = 128M

log_error = /var/log/mysql/error.log
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 4
log-queries-not-using-indexes

expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer      = 16M

Also pasting output from mysqltuner.

 >>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.38-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in InnoDB tables: 2G (Tables: 26)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 26

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21m 51s (37K q [28.525 qps], 31K conn, TX: 6M, RX: 7M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 304.0M global + 2.7M per thread (5000 max threads)
[!!] Maximum possible memory usage: 13.4G (347% of installed RAM)
[!!] Slow queries: 18% (6K/37K)
[OK] Highest usage of available connections: 0% (16/5000)
[OK] Key buffer size / total MyISAM indexes: 16.0M/100.0K
[OK] Query cache efficiency: 20.2% (7K cached / 36K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[OK] Temporary tables created on disk: 25% (54 on disk / 215 total)
[OK] Thread cache hit rate: 99% (16 created / 31K connections)
[OK] Table cache hit rate: 25% (74 open / 289 opened)
[OK] Open file limit used: 0% (49/25K)
[OK] Table locks acquired immediately: 100% (29K immediate / 29K locks)
[!!] InnoDB  buffer pool / data size: 128.0M/2.9G
[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 your overall MySQL memory footprint for system stability
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    innodb_buffer_pool_size (>= 2G)

I wasn't getting this issue at all, but when I started connecting like 10 remote servers to this MySQL server and when those ten servers started doing SELECT and UPDATE queries on this single MySQL server, I see this rise in CPU consumption.

up vote 2 down vote accepted

I suspect that you have several issues here that are stacking up and causing problems.

  1. Disk i/o is fragmented, and probably inefficient. OPTIMIZE TABLE on each of your tables should sort that out and help a bit.
  2. Memory is tuned horribly, likely causing issues with having to swap frequently.
  3. Slow queries - this is the big suspect for your high CPU usage. I'd check your slow log and see what you can optimize by adding indexes/reorganizing queries/etc. There's probably a fairly common query thats doing tens or hundreds of thousands of comparisons that are killing your performance.

If none of those bump up performance to what you want, it may be time to scale up your database server. 2 cores doesn't tell me anything about the processing power of the machine, and 4 GB of ram is pretty tiny these days.

For more specific advice, please add more detail to your description. SSDs or rotating disks? What CPU? What class of ram? How many queries/min? How many clients, etc?

  • I use a 4GB Ram 20GB SSD Disk Ubuntu 14.04 – Sana Apr 21 '15 at 22:31
  • I have the slow queries log inspected and the maximum query time for a SELECT query was 0.4 for 311416 rows examined. – Sana Apr 21 '15 at 22:34
  • When you say "2. Memory is tuned horribly... " What do you mean by that? How can I overcome this problem? – Sana Apr 21 '15 at 22:35
  • Well, the mysqltuner script is telling you. Your maximum memory usage is 3.4x how much ram you have, 2.7 megs per thread is really not that much, and you have a 128 meg innodb buffer pool for almost 3 gigs of data. – Ryan Gooler Apr 21 '15 at 22:38
  • So one solution would be to increase the buffer pool by increasing query_cache_size from 128MB to 1GB? ?? – Sana Apr 21 '15 at 22:41

If you are able to play around with settings a little, I would try this in my.cnf:

innodb_buffer_pool_size = 3000M

then restart mysql and check your performance. This would allow you to keep much more of your database in memory, reducing the disk/memory thrashing that you may be seeing. Given that you have 4Gb memory, and assuming this server is ONLY a db server, you can increase the buffer pool size up to about 80% of the system memory.

  • Doesn't do anything! :(. I have 4GB ram and I have some ML engine running therefore I made innodb_buffer_pool_size = 1000M instead – Sana Apr 22 '15 at 3:36
  • What is an ML engine? You might be running too many different apps on your instance, and you should definitely look into optimizing the tables, and getting more resources for this database. It sounds like you could actually use something closer to 16Gb for this to behave properly. – Chad Smith Apr 22 '15 at 3:42
  • Machine Learning python engine that crunches lots of data on the MySQL database that resides on the same server... Basically I am bootstrapping my startup and I have everything at one place. – Sana Apr 22 '15 at 3:55
  • Then you can probably expect continued poor performance until you can throw more resources at the problem :) – Chad Smith Apr 22 '15 at 4:05
  • Didn't know running SELECT and UPDATE queries from multiple servers on a signle MySQL database can literally eat up my resources – Sana Apr 22 '15 at 4:13

What happens: you have try to put an elefant into the hamster's ball.

What to do: decrease the memory footprint for mysql

. . . . .
. . . . .
[mysqld]
user                    = mysqluser
pid-file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
log_error               = /var/log/mysql/error.log
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time         = 10
log-queries-not-using-indexes
expire_logs_days        = 10
max_binlog_size         = 100M

port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
skip-external-locking
wait_timeout            = 20
interactive_timeout     = 60

bind-address            = <IP-ADDRESS>
myisam-recover          = BACKUP

## REASONABLE MEMORY PARAMETERS
max_connections         = 64
thread_cache_size       = 2
thread_concurrency      = 4
key_buffer_size         = 64M
join_buffer_size        = 2M
sort_buffer_size        = 2M
read_buffer_size        = 2M
read_rnd_buffer_size    = 8M
myisam_sort_buffer_size = 16M
query_cache_limit       = 2M
query_cache_size        = 32M
table_open_cache        = 512
max_allowed_packet      = 1M
. . . . .
. . . . .

You should analyze your most frequent queries with EXPLAIN command. The output will tell you how your queries use table indices, which directly affects the performance.

  • Slow queries are all queries that lasts for more then 4 seconds (by config) despite of reason. When server is configured properly, most probable reason for slow query is the lack of index or some other query inefficiency. Here the reason is the massive system swapping/paging because of badly configured mysql – Kondybas Apr 23 '15 at 9:27

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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