My MYSQL results into heavy CPU load

Support
1

Below is the output of mysqltuner script.

[--] Skipped version check for MySQLTuner script
 >>  MySQLTuner 1.7.8 - Major Hayden <major@mhtx.net>
 >>  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
[!!] Attempted to use login credentials, but they were invalid.

[root@www home]# ./mysqltuner.pl
 >>  MySQLTuner 1.7.8 - Major Hayden <major@mhtx.net>
 >>  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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.5.57-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(1001K)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 7848 warning(s).
[!!] /var/log/mysqld.log contains 337 error(s).
[--] 123 start(s) detected in /var/log/mysqld.log
[--] 1) 180331 13:42:18 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 2) 180331 12:59:17 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 3) 180331 12:13:07 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 4) 180331 12:04:55 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 5) 180331 11:59:03 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 6) 180330 16:52:04 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 7) 180329 16:47:18 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 8) 180329 15:37:18 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 9) 180329 12:42:40 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 10) 180329 12:27:55 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 120 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 180331 13:42:17 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 2) 180331 12:59:16 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 3) 180331 12:13:06 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 4) 180331 12:04:53 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 5) 180331 11:59:01 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 6) 180330 16:52:03 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 7) 180329 16:47:16 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 8) 180329 15:37:16 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 9) 180329 12:42:38 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 10) 180329 12:27:54 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 70G (Tables: 207)
[--] Data in InnoDB tables: 7G (Tables: 129)
[!!] Total fragmented tables: 1

-------- 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: 34m 45s (355K q [170.675 qps], 5K conn, TX: 544M, RX: 45M)
[--] Reads / Writes: 12% / 88%
[--] Binary logging is disabled
[--] Physical Memory     : 31.4G
[--] Max MySQL memory    : 663.2M
[--] Other process memory: 1.9G
[--] Total buffers: 248.0M global + 2.8M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 666.0M (2.07% of installed RAM)
[OK] Maximum[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (951/355K)
[!!] Highest connection usage: 100%  (152/151)
[OK] Aborted connections: 0.74%  (39/5241)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 95.2% (235K cached / 247K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (4 temp sorts / 918 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 1% (26 on disk / 1K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 96% (181 open / 188 opened)
[OK] Open file limit used: 0% (285/50K)
[OK] Table locks acquired immediately: 99% (143K immediate / 143K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (8M used / 8M cache)
[!!] Key buffer size / total MyISAM indexes: 8.0M/25.5G
[!!] Read Key buffer hit rate: 90.5% (87M cached / 8M reads)
[!!] Write Key buffer hit rate: 92.4% (263K cached / 243K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[!!] InnoDB File per table is not activated
[!!] InnoDB buffer pool / data size: 128.0M/7.6G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (7.8125 %): 5.0M * 2/128.0M 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.24% (10280890 hits/ 10359303 total)
[OK] InnoDB Write log efficiency: 99.29% (224014 hits/ 225625 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1611 writes)

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

-------- 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.
[--] Binlog format: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysqld.log file
    Control error line(s) into /var/log/mysqld.log file
    Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE `tendersinfo_com`.`tenders_archive`; -- can free 3964.72454833984 MB
    Total freed space after theses OPTIMIZE TABLE : 3964.72454833984 Mb
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce or eliminate persistent connections to reduce connection usage
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Set thread_cache_size to 4 as a starting value
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
    Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/2wgkDvS
Variables to adjust:
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (=0)
    query_cache_type (=0)
    thread_cache_size (start at 4)
    key_buffer_size (> 25.5G)
    innodb_file_per_table=ON
    innodb_buffer_pool_size (>= 7G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
 possible memory usage: 663.2M (2.06% of installed RAM)
2

Hi @Tejas,

are you working on a NethServer? :slightly_smiling_face:

NethServer uses mysqltuner 1.6.0, mariadb 5.5.56 and the logfile is /var/log/mariadb/mariadb.log…

You can get the root password for mariadb with cat ~/.my.cnf but as root you shouldn’t need it.

My output on a test VM:

[root@testserver ~]# mysqltuner
 >>  MySQLTuner 1.6.0 - Major Hayden <major@mhtx.net>
 >>  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.56-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[!!] There is not basic password file list !

-------- Performance Metrics -------------------------------------------------
[--] Up for: 18m 33s (204 q [0.183 qps], 72 conn, TX: 138K, RX: 14K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Total buffers: 416.0M global + 2.8M per thread (151 max threads)
[OK] Maximum reached memory usage: 418.8M (5.37% of installed RAM)
[OK] Maximum possible memory usage: 836.0M (10.71% of installed RAM)
[OK] Slow queries: 0% (0/204)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Aborted connections: 1.39%  (1/72)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 41 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 273% (41 open / 15 opened)
[OK] Open file limit used: 4% (51/1K)
[OK] Table locks acquired immediately: 100% (48 immediate / 48 locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/99.0K
[!!] Read Key buffer hit rate: 93.3% (15 cached / 1 reads)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is disabled.
[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine

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

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

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    thread_cache_size (start at 4)
3

It is an AWS server on which i have installed linux RHEL 6.

4

I found an howto which may help you with setting up mysql and mysqltuner on RHEL 6 but it’s centos 6, a free clone.

1 Like
5

Thanks for you support.