Kurogane

Member
Dec 2, 2007
21
0
51
Hi,

I've a problem with mysql i'm getting very but very high CPU usage and i not know how i can solved it

Code:
top - 22:50:48 up 7 days, 15:34,  4 users,  load average: 16.64, 14.86, 14.75
Tasks: 211 total,   1 running, 210 sleeping,   0 stopped,   0 zombie
Cpu(s): 96.2%us,  1.3%sy,  0.0%ni,  2.1%id,  0.1%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:  32900252k total, 32637120k used,   263132k free,   984688k buffers
Swap:  1569780k total,    73260k used,  1496520k free, 11498392k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
30053 mysql     20   0 12.8g 2.9g 7224 S 614.7  9.4 149:18.22 mysqld
show engine innodb status

paste.ee/p/7N2Zk

MySQLTuner

Code:
 >>  MySQLTuner 1.6.18 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.52-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics ---------------------------------------------                                                                                                                                                                                               --------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My                                                                                                                                                                                               ISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 179M (Tables: 133)
[--] Data in InnoDB tables: 2G (Tables: 74)
[OK] Total fragmented tables: 0

-------- Security Recommendations ----------------------------------------------                                                                                                                                                                                               --------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations ------------------------------------------                                                                                                                                                                                               --------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics ---------------------------------------------------                                                                                                                                                                                               --------------------
[--] Up for: 3h 0m 37s (1M q [115.484 qps], 55K conn, TX: 85G, RX: 161M)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 31.4G
[--] Max MySQL memory    : 9.5G
[--] Other process memory: 5.4G
[--] Total buffers: 8.1G global + 2.8M per thread (500 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 9.2G (29.29% of installed RAM)
[OK] Maximum possible memory usage: 9.5G (30.13% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory                                                                                                                                                                                                available
[!!] Slow queries: 8% (112K/1M)
[OK] Highest usage of available connections: 80% (402/500)
[!!] Aborted connections: 20.79%  (11447/55056)
[OK] Query cache is disabled by default due to mutex contention on multiprocesso                                                                                                                                                                                               r machines.
[OK] Sorts requiring temporary tables: 0% (12 temp sorts / 271K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 50% (89K on disk / 178K total)
[OK] Thread cache hit rate: 98% (896 created / 55K connections)
[OK] Table cache hit rate: 88% (1K open / 1K opened)
[OK] Open file limit used: 0% (234/65K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] Binlog cache memory access: 99.98% ( 17098 Memory / 17101 Total)

-------- Performance schema ----------------------------------------------------                                                                                                                                                                                               --------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ----------------------------------------------------                                                                                                                                                                                               --------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics --------------------------------------------------------                                                                                                                                                                                               --------------------
[!!] Key buffer used: 20.5% (13M used / 67M cache)
[OK] Key buffer size / total MyISAM indexes: 64.0M/55.8M
[OK] Read Key buffer hit rate: 97.4% (618K cached / 16K reads)
[!!] Write Key buffer hit rate: 14.0% (1K cached / 1K writes)

-------- AriaDB Metrics --------------------------------------------------------                                                                                                                                                                                               --------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics --------------------------------------------------------                                                                                                                                                                                               --------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 8.0G/2.9G
[OK] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (80960321191 hits/ 80960472731 total                                                                                                                                                                                               )
[!!] InnoDB Write Log efficiency: 17.77% (7154 hits/ 40248 total)
[!!] InnoDB log waits: 0.00% (1 waits / 33094 writes)

-------- TokuDB Metrics --------------------------------------------------------                                                                                                                                                                                               --------------------
[--] TokuDB is disabled.

-------- Galera Metrics --------------------------------------------------------                                                                                                                                                                                               --------------------
[--] Galera is disabled.

-------- Replication Metrics ---------------------------------------------------                                                                                                                                                                                               --------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations -------------------------------------------------------                                                                                                                                                                                               --------------------
General recommendations:
    Restrict Host for [email protected]% to [email protected]
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce or eliminate unclosed connections and network issues
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    innodb_log_buffer_size (>= 8M)
my.cnf

Code:
[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid
skip-external-locking
skip-name-resolve

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

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

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

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

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 1536

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

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log
general_log                       = 1
general_log_file               = /var/lib/mysql/general.log
 
Last edited:

cPanelMichael

Technical Support Community Manager
Staff member
Apr 11, 2011
47,910
2,233
363
cPanel Access Level
DataCenter Provider
Twitter
Hello,

You could try implementing some of the advice suggested by the tuner:

-------- Recommendations ------------------------------------------------------- --------------------
General recommendations:
Restrict Host for [email protected]% to [email protected]
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce or eliminate unclosed connections and network issues
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
tmp_table_size (> 32M)
max_heap_table_size (> 32M)
innodb_log_buffer_size (>= 8M)
Then, let MySQL run for at least 24 hours, and run the tuner again to see if there's new advice.

Thank you.
 

Kurogane

Member
Dec 2, 2007
21
0
51
That is not the issue i can't wait 24h because we a getting the same issue.

My website can't not load because is to slow because MySQL when restating in a few minutes my website is not available or very slow.
 

Kurogane

Member
Dec 2, 2007
21
0
51
I know what account/site is because is only one site is hosted in the server.

So you think is script issue?