High load due to MYSQL ON 8core cpu server

Issues related to software problems.
Post Reply
tejas5555
Posts: 8
Joined: 2018/02/23 11:33:42

High load due to MYSQL ON 8core cpu server

Post by tejas5555 » 2018/04/03 06:09:32

Dear all,
I have a8 core CPU server with memory of 32GB.
Then to mysql results into heavy load, sometimes even 50 with idealtime=0.
Please find the output of mysqltuner.
>> 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.
>> 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
[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(1M)
[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 8100 warning(s).
[!!] /var/log/mysqld.log contains 365 error(s).
[--] 129 start(s) detected in /var/log/mysqld.log
[--] 1) 180402 16:29:10 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 2) 180402 16:25:24 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 3) 180402 16:18:33 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 4) 180331 15:41:15 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 5) 180331 15:40:00 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 6) 180331 15:21:01 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 7) 180331 13:42:18 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 8) 180331 12:59:17 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 9) 180331 12:13:07 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 10) 180331 12:04:55 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 126 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 180402 16:29:08 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 2) 180402 16:25:22 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 3) 180402 16:18:31 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 4) 180331 15:41:13 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 5) 180331 15:39:59 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 6) 180331 15:21:00 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 7) 180331 13:42:17 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 8) 180331 12:59:16 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 9) 180331 12:13:06 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 10) 180331 12:04:53 [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: 19h 7m 15s (10M q [158.717 qps], 147K conn, TX: 20G, RX: 1G)
[--] Reads / Writes: 7% / 93%
[--] Binary logging is disabled
[--] Physical Memory : 31.4G
[--] Max MySQL memory : 663.2M
[--] Other process memory: 3.4G
[--] 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 possible memory usage: 663.2M (2.06% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (18K/10M)
[!!] Highest connection usage: 100% (152/151)
[OK] Aborted connections: 2.85% (4200/147116)
[!!] 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: 96.2% (6M cached / 7M selects)
[!!] Query cache prunes per day: 42659
[!!] Sorts requiring temporary tables: 12% (4K temp sorts / 38K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 2% (649 on disk / 27K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 83% (400 open / 480 opened)
[OK] Open file limit used: 2% (1K/50K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M 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: 89.4% (2B cached / 232M reads)
[!!] Write Key buffer hit rate: 43.5% (19M cached / 8M 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.63% (784707841 hits/ 787660606 total)
[OK] InnoDB Write log efficiency: 94.08% (4872010 hits/ 5178773 total)
[OK] InnoDB log waits: 0.00% (0 waits / 306763 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 3963.09818267822 MB
Total freed space after theses OPTIMIZE TABLE : 3963.09818267822 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)
query_cache_size (> 80M)
sort_buffer_size (> 2M)
read_rnd_buffer_size (> 256K)
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.
[root@www home]# cat /home/tuneroutput
>> 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.
>> 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
[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(1M)
[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 8100 warning(s).
[!!] /var/log/mysqld.log contains 365 error(s).
[--] 129 start(s) detected in /var/log/mysqld.log
[--] 1) 180402 16:29:10 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 2) 180402 16:25:24 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 3) 180402 16:18:33 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 4) 180331 15:41:15 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 5) 180331 15:40:00 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 6) 180331 15:21:01 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 7) 180331 13:42:18 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 8) 180331 12:59:17 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 9) 180331 12:13:07 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 10) 180331 12:04:55 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 126 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 180402 16:29:08 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 2) 180402 16:25:22 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 3) 180402 16:18:31 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 4) 180331 15:41:13 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 5) 180331 15:39:59 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 6) 180331 15:21:00 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 7) 180331 13:42:17 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 8) 180331 12:59:16 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 9) 180331 12:13:06 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 10) 180331 12:04:53 [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: 19h 7m 15s (10M q [158.717 qps], 147K conn, TX: 20G, RX: 1G)
[--] Reads / Writes: 7% / 93%
[--] Binary logging is disabled
[--] Physical Memory : 31.4G
[--] Max MySQL memory : 663.2M
[--] Other process memory: 3.4G
[--] 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 possible memory usage: 663.2M (2.06% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (18K/10M)
[!!] Highest connection usage: 100% (152/151)
[OK] Aborted connections: 2.85% (4200/147116)
[!!] 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: 96.2% (6M cached / 7M selects)
[!!] Query cache prunes per day: 42659
[!!] Sorts requiring temporary tables: 12% (4K temp sorts / 38K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 2% (649 on disk / 27K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 83% (400 open / 480 opened)
[OK] Open file limit used: 2% (1K/50K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M 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: 89.4% (2B cached / 232M reads)
[!!] Write Key buffer hit rate: 43.5% (19M cached / 8M 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.63% (784707841 hits/ 787660606 total)
[OK] InnoDB Write log efficiency: 94.08% (4872010 hits/ 5178773 total)
[OK] InnoDB log waits: 0.00% (0 waits / 306763 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 3963.09818267822 MB
Total freed space after theses OPTIMIZE TABLE : 3963.09818267822 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)
query_cache_size (> 80M)
sort_buffer_size (> 2M)
read_rnd_buffer_size (> 256K)
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.
[root@www home]# vi /home/tuneroutput
[root@www home]# watch "du -sh /home/tuneroutput"
[root@www home]#
[root@www home]#
[root@www home]# vi /home/tuneroutput
[root@www home]# cat /home/tuneroutput
>> 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.
>> 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
[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(1M)
[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 8100 warning(s).
[!!] /var/log/mysqld.log contains 365 error(s).
[--] 129 start(s) detected in /var/log/mysqld.log
[--] 1) 180402 16:29:10 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 2) 180402 16:25:24 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 3) 180402 16:18:33 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 4) 180331 15:41:15 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 5) 180331 15:40:00 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 6) 180331 15:21:01 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 7) 180331 13:42:18 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 8) 180331 12:59:17 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 9) 180331 12:13:07 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 10) 180331 12:04:55 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
[--] 126 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 180402 16:29:08 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 2) 180402 16:25:22 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 3) 180402 16:18:31 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 4) 180331 15:41:13 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 5) 180331 15:39:59 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 6) 180331 15:21:00 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 7) 180331 13:42:17 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 8) 180331 12:59:16 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 9) 180331 12:13:06 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete
[--] 10) 180331 12:04:53 [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: 19h 7m 15s (10M q [158.717 qps], 147K conn, TX: 20G, RX: 1G)
[--] Reads / Writes: 7% / 93%
[--] Binary logging is disabled
[--] Physical Memory : 31.4G
[--] Max MySQL memory : 663.2M
[--] Other process memory: 3.4G
[--] 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 possible memory usage: 663.2M (2.06% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (18K/10M)
[!!] Highest connection usage: 100% (152/151)
[OK] Aborted connections: 2.85% (4200/147116)
[!!] 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: 96.2% (6M cached / 7M selects)
[!!] Query cache prunes per day: 42659
[!!] Sorts requiring temporary tables: 12% (4K temp sorts / 38K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 2% (649 on disk / 27K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 83% (400 open / 480 opened)
[OK] Open file limit used: 2% (1K/50K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M 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: 89.4% (2B cached / 232M reads)
[!!] Write Key buffer hit rate: 43.5% (19M cached / 8M 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.63% (784707841 hits/ 787660606 total)
[OK] InnoDB Write log efficiency: 94.08% (4872010 hits/ 5178773 total)
[OK] InnoDB log waits: 0.00% (0 waits / 306763 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 3963.09818267822 MB
Total freed space after theses OPTIMIZE TABLE : 3963.09818267822 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)
query_cache_size (> 80M)
sort_buffer_size (> 2M)
read_rnd_buffer_size (> 256K)
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.

I am new to mysql so any help will be appreciated. :)

Post Reply