Mysql High Usage

Dear all ; hi ; my server has high usage on CPU and the most process are because of MYSQL ; could you please help me to modify my.cnf in the best as possible ? My Server Detailes :

Total processors: 8
Intel(R) Xeon(R) CPU E5-1630 v3 @ 3.70GHz
Speed : 3700.051 MHz
Cache :10240 KB
---------------------------------
Memory : 64GB DDR4
--------------------------------
SSD Drives
-------------------------------
CloudLinux
--------------------------------

My Current my.cnf :

[mysql]
#port                           = 3306
#socket                         = /var/lib/mysql/mysql.sock
[mysqld]
#user                           = mysql
innodb_lock_wait_timeout=2000
#local-infile=0
default-storage-engine=InnoDB
#socket                         = /var/lib/mysql/mysql.sock
key-buffer-size=32M
myisam-recover="FORCE,BACKUP"
thread_concurrency=16
max-allowed-packet=16M
max-connect-errors=1000000
datadir="/var/lib/mysql/"
expire-logs-days=14
sync-binlog=1
tmp-table-size=32M
max-heap-table-size=32M
query-cache-type=1
query-cache-size=128M
max-connections=2000
thread-cache-size=150
open-files-limit=65535
table-definition-cache=4096
table-open-cache=1000
innodb-flush-method=O_DIRECT
innodb-log-files-in-group=2
innodb-flush-log-at-trx-commit=1
innodb-file-per-table=1
innodb-buffer-pool-size=16G
innodb_buffer_pool_instances=16G
log-queries-not-using-indexes=1
innodb_file_per_table=1
max_allowed_packet=536870912
open_files_limit=50000

MYSQL Tune Result :

# perl mysqltuner.pl
>>  MySQLTuner 1.6.4 - 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.6.28
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 13G (Tables: 30915)
[--] Data in InnoDB tables: 10G (Tables: 26599)
[--] Data in MEMORY tables: 8M (Tables: 793)
Argument "" isn't numeric in numeric gt (>) at mysqltuner.pl line 1253 (#1)
    (W numeric) The indicated string was fed as an argument to an operator
    that expected a numeric value instead.  If you're fortunate the message
    will identify which operator was so unfortunate.

[OK] Total fragmented tables:

-------- 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
Argument "" isn't numeric in numeric eq (==) at mysqltuner.pl line 1492 (#1)
Argument "" isn't numeric in numeric eq (==) at mysqltuner.pl line 1500 (#1)

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4m 0s (131K q [548.046 qps], 2K conn, TX: 2B, RX: 18M)
[--] Reads / Writes: 76% / 24%
[--] Binary logging is disabled
[--] Total buffers: 17.2G global + 64.5M per thread (2000 max threads)
[OK] Maximum reached memory usage: 18.6G (29.54% of installed RAM)
[!!] Maximum possible memory usage: 143.1G (227.89% of installed RAM)
[!!] Slow queries: 9% (12K/131K)
[OK] Highest usage of available connections: 1% (22/2000)
[OK] Aborted connections: 0.04%  (1/2512)
[OK] Query cache efficiency: 67.9% (73K cached / 108K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (22 temp sorts / 5K sorts)
[!!] Joins performed without indexes: 328
[!!] Temporary tables created on disk: 74% (4K on disk / 5K total)
[OK] Thread cache hit rate: 99% (22 created / 2K connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 3% (1K/50K)
[OK] Table locks acquired immediately: 99% (51K immediate / 51K locks)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 19.5% (209M used / 1B cache)
[!!] Cannot calculate MyISAM index size - re-run script as root user

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 16.0G/10.8G
[!!] InnoDB buffer pool instances: 64
[!!] InnoDB Used buffer: 14.61% (153174 used/ 1048576 total)
[OK] InnoDB Read buffer efficiency: 99.95% (121791677 hits/ 121849663 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 3215 writes)

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

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

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

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

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

-------- Recommendations -----------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Adjust your join queries to always utilize indexes
    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:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    innodb_buffer_pool_instances(=16)

vanessa

February 08, 2016 15:58

If MySQL is using a lot of resources, you need to start my looking at what's going on in there. A lot of people think there's some magic switch in my.cnf that can fix this, but that's simply not how it works, and no one here can give you anything but a guess on what settings they thing are best. Download mysqltuner.pl, run it, and learn to troubleshoot or hire something that can.

cPanelMichael

February 09, 2016 16:17

Hello :) It's also important to let MySQL run as long as possible (preferably 24 hours) before running the tuner to get the most accurate results. Thank you.

kdean

February 09, 2016 17:04

You do seem to have a misconfiguration of your innodb_buffer_pool_instances which is looking for a value of 1 to 64, but you have it set to 16G which is causing it to set to it's highest value of 64 which is dividing your buffer pool into 64 256 MB max memory instances. The default is 8 which would divide the buffer pool into 8 2G slices. I wouldn't go higher than 16 since MySQL recommends at least 1 GB per instance. You can also try lower values all the way down to 1. You just need to gadge the performance differences on your system. MySQL :: MySQL 5.6 Reference Manual :: 14.11 InnoDB Startup Options and System Variables

Mysql High Usage

Comments

Didn't find what you were looking for?