MySQL CPU usage above 95%

Ubuntu 14.04.1 LTS, 4GB Ram Hi There i am attaching output of lscpu: root@mysql:~# lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 4 On-line CPU(s) list: 0-3 Thread(s) per core: 1 Core(s) per socket: 1 Socket(s): 4 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family: 6 Model: 45 Stepping: 7 CPU MHz: 2593.840 BogoMIPS: 5257.44 Hypervisor vendor: Xen Virtualization type: full L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 20480K NUMA node0 CPU(s): 0-3 Output of free -m : root@mysql:~# free -m total used free shared buffers cached Mem: 3947 3650 297 0 135 472 -/+ buffers/cache: 3042 905 And here is my config:


[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/run/mysqld/mysqld.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/run/mysqld/mysqld.sock
pid-file                       = /var/run/mysqld/mysqld.pid

# MyISAM #
key-buffer-size                = 1024M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE
sysdate-is-now                 = 1
innodb                         = FORCE
innodb-strict-mode             = 1

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
#log-bin                        = /var/lib/mysql/mysql-bin
#expire-logs-days               = 1
#sync-binlog                    = 1
#binlog_format                  = mixed

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 1
query-cache-size               = 2M
query-cache-limit              = 3M
max-connections                = 10
thread-cache-size              = 35
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 128M
innodb-flush-log-at-trx-commit = 1
#innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2G


# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
long_query_time                = 1
log-queries-not-using-indexes  = 0
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log


 >>  MySQLTuner 1.1.1 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.19-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in InnoDB tables: 8G (Tables: 190)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in CSV tables: 0B (Tables: 4)
[!!] Total fragmented tables: 14

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 19h 53m 32s (7M q [49.914 qps], 448 conn, TX: 4B, RX: 1B)
[--] Reads / Writes: 50% / 50%
[--] Total buffers: 3.0G global + 1.1M per thread (10 max threads)
[OK] Maximum possible memory usage: 3.1G (79% of installed RAM)
[OK] Slow queries: 0% (10/7M)
[!!] Highest connection usage: 100%  (11/10)
[OK] Key buffer size / total MyISAM indexes: 1.0G/97.0K
[OK] Key buffer hit rate: 100.0% (214K cached / 1 reads)
[!!] Query cache efficiency: 1.8% (71K cached / 4M selects)
[OK] Query cache prunes per day: 0
[!!] Sorts requiring temporary tables: 13% (27 temp sorts / 198 sorts)
[OK] Temporary tables created on disk: 20% (417 on disk / 2K total)
[OK] Thread cache hit rate: 97% (11 created / 448 connections)
[!!] Table cache hit rate: 19% (381 open / 1K opened)
[OK] Open file limit used: 0% (57/65K)
[OK] Table locks acquired immediately: 100% (7M immediate / 7M locks)
[!!] InnoDB data size / buffer pool: 8.4G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce or eliminate persistent connections to reduce connection usage
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    max_connections (> 10)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_limit (> 3M, or use smaller result sets)
    sort_buffer_size (> 256K)
    read_rnd_buffer_size (> 256K)
    table_cache (> 4096)
    innodb_buffer_pool_size (>= 8G)

Any suggests what i can do so my server never hung or overload? It serve a big (Dating) site that has only on messages table over 19million records.

smoge

November 02, 2014 20:18

Add ram. Increase max connections as 10 is very low. All that stuff in MySQL tuner suggestions.

cPanelMichael

November 03, 2014 16:02

Hello :) I just wanted to make note of the fact that you mentioned using Ubuntu, which is not a supported OS at this time: cPanel Installation - Choose an OS Thank you.

MySQL CPU usage above 95%

Comments

Didn't find what you were looking for?