Skip to main content

MySQL Optimization

Kurogane
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
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
>> MySQLTuner 1.6.18 - Major Hayden >> 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 user@% to user@SpecificDNSorIp 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
[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

Comments

5 comments

Date Votes
  • cPanelMichael
    Hello, You could try implementing some of the advice suggested by the tuner: -------- Recommendations ------------------------------------------------------- -------------------- General recommendations: Restrict Host for user@% to user@SpecificDNSorIp 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.
    0
  • Kurogane
    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.
    0
  • Infopro
    It sounds like there's something wrong with at least one of your accounts websites. Optimizing MySQL is not a silver bullet.
    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

    You need to find out which site is crippling your server like that.
    0
  • Kurogane
    I know what account/site is because is only one site is hosted in the server. So you think is script issue?
    0
  • cPanelMichael
    Are you using any particular scripts such as WordPress on the account? Thank you.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post