2

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)
1
  • Could you also post RAM, HDD and Network throughput at times when this issue occurs. Use sysstat (vmstat, iostat and nfsiostat) through the sar command options. If nothing else, this will familiarise you with this essential piece of kit for any Linux user's toolbox. Good intro here. – Vérace Apr 22 '15 at 0:00
3

Ignore the "fragmented" alarm; it's bogus. Ignore the max "possible" memory; it's bogus.

Focus on the slow queries; they are causing the high CPU. What are the slow queries?

Plan A: Glance at SHOW FULL PROCESSLIST; frequently. You will see one or two queries there most of the time.

Plan B: Turn on the slowlog; set long_query_time = 2; and (after a day), run pt_query_digest. It will tell you the worst queries.

Given a bad query (or two), show us

  • SHOW CREATE TABLE
  • EXPLAIN SELECT ...

We should be able to help you dramatically shrink the CPU load.

(Contrary to what @dborba suggested, I ruled out swapping and I/O. Those tend to show up as low CPU, but high latency for queries. We may need to address it again after adding some INDEXes, etc.)

9
  • Thanks Rick, I did turn on the slow log option for MySQL, hopefully I have something by tomorrow. Is pt_query_digest a module to be installed on teh server? – Sana Apr 22 '15 at 0:38
  • It's a script that is available at percona.com. – Rick James Apr 22 '15 at 0:40
  • Alternatively, just look in the slowlog. (The script summarizes the log.) – Rick James Apr 22 '15 at 0:41
  • Most of the queries run under 1 sec... inspecting the slow-log-file for almost all quries, I don't see any query running very slow... But I do see many rows being inspected – Sana Apr 22 '15 at 4:10
  • If "Rows examined" is much higher than "Rows sent", and you don't have a LIMIT or GROUP BY, then possibly the query could be sped up by adding an INDEX. Let's see one. Also, if you have hundreds of queries per second, they might each run under 1 second, but still pile up and cause high CPU. Set long_query_time = 0.5 to capture some of them in the slowlog. – Rick James Apr 22 '15 at 16:01
1

The following is probably an issue: [!!] Maximum possible memory usage: 13.4G (347% of installed RAM)

Due to limited memory, the database is probably using the swap/page file a lot, as well as significant disk i/o through out. This might cause CPU utilization to remain rather high at all times.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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