Skip to main content

mysql cpu usage optimize

seng fatt
hi i have some serious issue with mysql this is my.cnf file
[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 max-connect-errors=100 datadir="/var/lib/mysql/" expire-logs-days=14 sync-binlog=1 tmp-table-size=50M max-heap-table-size=50M query-cache-type=0 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_buffer_pool_instances=1 innodb-file-per-table=1 log-queries-not-using-indexes=1 innodb_file_per_table=1 max_allowed_packet=1073741824 open_files_limit=50000 #innodb_buffer_pool_size=3000M key_buffer_size = 350M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 max_connections = 50 #default is 150 wait_timeout = 30 #default is 28800 innodb_buffer_pool_size = 2000M innodb_log_file_size = 8M innodb_thread_concurrency = 8 innodb_file_per_table query_cache_size = 16M log-queries-not-using-indexes
this mysqltunner.pl results:
>> MySQLTuner 1.7.0 - 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 [!!] Currently running unsupported MySQL version 10.0.28-MariaDB [OK] Operating on 64-bit architecture -------- Log file Recommendations ---------------------------------------------- -------------------- [--] Log file: /var/lib/mysql/server.example.com.err(63K) [OK] Log file /var/lib/mysql/server.example.com.err exists [OK] Log file /var/lib/mysql/server.example.com.err is readable. [OK] Log file /var/lib/mysql/server.example.com.err is not empty [OK] Log file /var/lib/mysql/server.example.com.err is smaller than 32 Mb [!!] /var/lib/mysql/server.example.com.err contains 72 warning(s). [!!] /var/lib/mysql/server.example.com.err contains 31 error(s). [--] 22 start(s) detected in /var/lib/mysql/server.example.com.err [--] 1) 161211 23:48:20 [Note] /usr/sbin/mysqld: ready for connections. [--] 2) 161211 23:45:24 [Note] /usr/sbin/mysqld: ready for connections. [--] 3) 161211 23:44:46 [Note] /usr/sbin/mysqld: ready for connections. [--] 4) 161211 23:34:56 [Note] /usr/sbin/mysqld: ready for connections. [--] 5) 161211 23:25:28 [Note] /usr/sbin/mysqld: ready for connections. [--] 6) 161211 23:24:16 [Note] /usr/sbin/mysqld: ready for connections. [--] 7) 161211 23:07:13 [Note] /usr/sbin/mysqld: ready for connections. [--] 8) 161211 22:57:43 [Note] /usr/sbin/mysqld: ready for connections. [--] 9) 161211 22:56:10 [Note] /usr/sbin/mysqld: ready for connections. [--] 10) 161211 22:55:47 [Note] /usr/sbin/mysqld: ready for connections. [--] 25 shutdown(s) detected in /var/lib/mysql/server.example.com.err [--] 1) 161211 23:48:19 [Note] /usr/sbin/mysqld: Shutdown complete [--] 2) 161211 23:45:23 [Note] /usr/sbin/mysqld: Shutdown complete [--] 3) 161211 23:44:44 [Note] /usr/sbin/mysqld: Shutdown complete [--] 4) 161211 23:34:55 [Note] /usr/sbin/mysqld: Shutdown complete [--] 5) 161211 23:25:27 [Note] /usr/sbin/mysqld: Shutdown complete [--] 6) 161211 23:06:35 [Note] /usr/sbin/mysqld: Shutdown complete [--] 7) 161211 23:06:34 [Note] /usr/sbin/mysqld: Shutdown complete [--] 8) 161211 22:57:23 [Note] /usr/sbin/mysqld: Shutdown complete [--] 9) 161211 22:57:02 [Note] /usr/sbin/mysqld: Shutdown complete [--] 10) 161211 22:56:58 [Note] /usr/sbin/mysqld: Shutdown complete -------- Storage Engine Statistics --------------------------------------------- -------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyIS AM +MyISAM +PERFORMANCE_SCHEMA [--] Data in MyISAM tables: 879M (Tables: 19) [--] Data in InnoDB tables: 186M (Tables: 68) [OK] Total fragmented tables: 0 -------- Security Recommendations ---------------------------------------------- -------------------- [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [!!] User 'munin@localhost' has user name as password. [--] There are 612 basic passwords in the list. -------- CVE Security Recommendations ------------------------------------------ -------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics --------------------------------------------------- -------------------- [--] Up for: 29m 11s (214K q [122.612 qps], 3K conn, TX: 140G, RX: 17M) [--] Reads / Writes: 97% / 3% [--] Binary logging is disabled [--] Physical Memory : 9.6G [--] Max MySQL memory : 2.6G [--] Other process memory: 1.4G [--] Total buffers: 2.5G global + 2.7M per thread (50 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 2.5G (26.26% of installed RAM) [OK] Maximum possible memory usage: 2.6G (27.35% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [!!] Slow queries: 11% (24K/214K) [OK] Highest usage of available connections: 20% (10/50) [OK] Aborted connections: 0.06% (2/3470) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 16K sorts) [OK] No joins without indexes [!!] Temporary tables created on disk: 60% (992 on disk / 1K total) [OK] Thread cache hit rate: 99% (10 created / 3K connections) [OK] Table cache hit rate: 152% (184 open / 121 opened) [OK] Open file limit used: 0% (105/50K) [OK] Table locks acquired immediately: 99% (212K immediate / 213K locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 4 thread(s). [--] Using default value is good enough for your version (10.0.28-MariaDB) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 31.5% (115M used / 367M cache) [OK] Key buffer size / total MyISAM indexes: 350.0M/224.2M [OK] Read Key buffer hit rate: 99.1% (5M cached / 44K reads) [!!] Write Key buffer hit rate: 7.7% (22K cached / 20K writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 8 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 2.0G/186.9M [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.8 %): 8.0M * 2/2.0G should be equal 25% [OK] InnoDB buffer pool instances: 1 [--] InnoDB Buffer Pool Chunk Size not used or defined in your version [OK] InnoDB Read buffer efficiency: 99.97% (24245691 hits/ 24253713 total) [!!] InnoDB Write Log efficiency: 52.48% (3107 hits/ 5920 total) [OK] InnoDB log waits: 0.00% (0 waits / 2813 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/1B [OK] Aria pagecache hit rate: 99.8% (425K cached / 913 reads) -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- RocksDB Metrics --------------------------------------------------------------------------- [--] RocksDB is disabled. -------- Spider Metrics ---------------------------------------------------------------------------- [--] Spider is disabled. -------- Connect Metrics --------------------------------------------------------------------------- [--] Connect 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: Control warning line(s) into /var/lib/mysql/server.example.com.err file Control error line(s) into /var/lib/mysql/server.example.com.err file Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); ) MySQL started within last 24 hours - recommendations may be inaccurate Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 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 (> 50M) max_heap_table_size (> 50M) innodb_log_file_size should be equals to 1/4 of buffer pool size (=500M) if possible.
and this is my process list
root@server [~]# mysqladmin processlist +------+-----------------+-----------+-----------------+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +------+-----------------+-----------+-----------------+---------+------+-------+------------------+----------+ | 4332 | hubpre13_amnran | localhost | hubpre13_amnran | Sleep | 0 | | | 0.000 | | 4333 | hubpre13_amnran | localhost | hubpre13_amnran | Sleep | 2 | | | 0.000 | | 4334 | hubpre13_amnran | localhost | hubpre13_amnran | Sleep | 1 | | | 0.000 | | 4335 | hubpre13_amnran | localhost | hubpre13_amnran | Sleep | 1 | | | 0.000 | | 4336 | root | localhost | | Query | 0 | init | show processlist | 0.000 | +------+-----------------+-----------+-----------------+---------+------+-------+------------------+----------+
i dont have any heavy query according to my programer but mysql using cpu for nothing! please help me thank you any suggestion?

Comments

3 comments

Date Votes
  • Infopro
    You can't get proper results from a system that's not been up at least 24 hours: [--] Up for: 29m 11s (214K q [122.612 qps], 3K conn, TX: 140G, RX: 17M)
    Once you do that, the section titled, General recommendations: in the output, might be a nice place to start. ;)
    0
  • seng fatt
    You can't get proper results from a system that's not been up at least 24 hours: Once you do that, the section titled, General recommendations: in the output, might be a nice place to start. ;)

    this is new log
    root@server [~]# perl mysqltuner.pl >> MySQLTuner 1.7.0 - 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 [!!] Currently running unsupported MySQL version 10.0.28-MariaDB [OK] Operating on 64-bit architecture -------- Log file Recommendations ---------------------------------------------- -------------------- [--] Log file: /var/lib/mysql/server.example.com.err(63K) [OK] Log file /var/lib/mysql/server.example.com.err exists [OK] Log file /var/lib/mysql/server.example.com.err is readable. [OK] Log file /var/lib/mysql/server.example.com.err is not empty [OK] Log file /var/lib/mysql/server.example.com.err is smaller than 32 Mb [!!] /var/lib/mysql/server.example.com.err contains 72 warning(s). [!!] /var/lib/mysql/server.example.com.err contains 31 error(s). [--] 22 start(s) detected in /var/lib/mysql/server.eaglescity.com.err [--] 1) 161211 23:48:20 [Note] /usr/sbin/mysqld: ready for connections. [--] 2) 161211 23:45:24 [Note] /usr/sbin/mysqld: ready for connections. [--] 3) 161211 23:44:46 [Note] /usr/sbin/mysqld: ready for connections. [--] 4) 161211 23:34:56 [Note] /usr/sbin/mysqld: ready for connections. [--] 5) 161211 23:25:28 [Note] /usr/sbin/mysqld: ready for connections. [--] 6) 161211 23:24:16 [Note] /usr/sbin/mysqld: ready for connections. [--] 7) 161211 23:07:13 [Note] /usr/sbin/mysqld: ready for connections. [--] 8) 161211 22:57:43 [Note] /usr/sbin/mysqld: ready for connections. [--] 9) 161211 22:56:10 [Note] /usr/sbin/mysqld: ready for connections. [--] 10) 161211 22:55:47 [Note] /usr/sbin/mysqld: ready for connections. [--] 25 shutdown(s) detected in /var/lib/mysql/server.example.com.err [--] 1) 161211 23:48:19 [Note] /usr/sbin/mysqld: Shutdown complete [--] 2) 161211 23:45:23 [Note] /usr/sbin/mysqld: Shutdown complete [--] 3) 161211 23:44:44 [Note] /usr/sbin/mysqld: Shutdown complete [--] 4) 161211 23:34:55 [Note] /usr/sbin/mysqld: Shutdown complete [--] 5) 161211 23:25:27 [Note] /usr/sbin/mysqld: Shutdown complete [--] 6) 161211 23:06:35 [Note] /usr/sbin/mysqld: Shutdown complete [--] 7) 161211 23:06:34 [Note] /usr/sbin/mysqld: Shutdown complete [--] 8) 161211 22:57:23 [Note] /usr/sbin/mysqld: Shutdown complete [--] 9) 161211 22:57:02 [Note] /usr/sbin/mysqld: Shutdown complete [--] 10) 161211 22:56:58 [Note] /usr/sbin/mysqld: Shutdown complete -------- Storage Engine Statistics --------------------------------------------- -------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyIS AM +MyISAM +PERFORMANCE_SCHEMA [--] Data in MyISAM tables: 901M (Tables: 19) [--] Data in InnoDB tables: 202M (Tables: 68) [OK] Total fragmented tables: 0 -------- Security Recommendations ---------------------------------------------- -------------------- [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [!!] User 'munin@localhost' has user name as password. [--] There are 612 basic passwords in the list. -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 1d 0h 23m 17s (9M q [106.656 qps], 128K conn, TX: 7651G, RX: 673M) [--] Reads / Writes: 97% / 3% [--] Binary logging is disabled [--] Physical Memory : 9.6G [--] Max MySQL memory : 2.6G [--] Other process memory: 1.7G [--] Total buffers: 2.5G global + 2.7M per thread (50 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 2.5G (26.31% of installed RAM) [OK] Maximum possible memory usage: 2.6G (27.35% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [!!] Slow queries: 12% (1M/9M) [OK] Highest usage of available connections: 24% (12/50) [OK] Aborted connections: 0.01% (9/128490) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (16 temp sorts / 677K sorts) [OK] No joins without indexes [!!] Temporary tables created on disk: 63% (16K on disk / 26K total) [OK] Thread cache hit rate: 99% (78 created / 128K connections) [OK] Table cache hit rate: 135% (203 open / 150 opened) [OK] Open file limit used: 0% (114/50K) [OK] Table locks acquired immediately: 99% (9M immediate / 9M locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 4 thread(s). [--] Using default value is good enough for your version (10.0.28-MariaDB) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 58.3% (213M used / 367M cache) [OK] Key buffer size / total MyISAM indexes: 350.0M/236.1M [OK] Read Key buffer hit rate: 99.9% (195M cached / 128K reads) [!!] Write Key buffer hit rate: 12.2% (732K cached / 643K writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 8 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 2.0G/202.5M [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.8 %): 8.0M * 2/2.0G should be equal 25% [OK] InnoDB buffer pool instances: 1 [--] InnoDB Buffer Pool Chunk Size not used or defined in your version [OK] InnoDB Read buffer efficiency: 100.00% (797716161 hits/ 797726689 total) [!!] InnoDB Write Log efficiency: 53.07% (120023 hits/ 226147 total) [OK] InnoDB log waits: 0.00% (0 waits / 106124 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/1B [OK] Aria pagecache hit rate: 99.8% (5M cached / 13K reads) -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- RocksDB Metrics --------------------------------------------------------------------------- [--] RocksDB is disabled. -------- Spider Metrics ---------------------------------------------------------------------------- [--] Spider is disabled. -------- Connect Metrics --------------------------------------------------------------------------- [--] Connect 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: Control warning line(s) into /var/lib/mysql/server.eaglescity.com.err file Control error line(s) into /var/lib/mysql/server.eaglescity.com.err file Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); ) Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 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 (> 50M) max_heap_table_size (> 50M) innodb_log_file_size should be equals to 1/4 of buffer pool size (=500M) if possible.
    0
  • cPanelMichael
    Hello, You can review the "Variables to adjust" section and make adjustments in your /etc/my.cnf to meet those suggestions. Also, you may want to use a command such as "mysqladmin processlist;" when the server resource usage is high to see if you can determine which databases are the culprit. Thank you.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post