Operating System & Version
CENTOS 7
cPanel & WHM Version
CloudLinux v7.9.0 kvm [sydney] v100.0.11 Load Averages: 2.82 2.75 2.63

PixelDad

Member
Feb 20, 2022
9
0
1
Sydney
cPanel Access Level
Root Administrator
Hi,

MYSQL server slows down when running for more than 24 hours. I'm not sure if my SQL is getting full and why the speed is degrading\. Rebooting the server bring speed back to normal again. Below is what's showing after the last reboot which is about 8 hours.


[[email protected] ~]# mysqladmin proc status
+-------+------------------+-----------+--------------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------------+-----------+--------------------+---------+------+-------+------------------+
| 98 | root | localhost | | Sleep | 27 | | |
| 48672 | asxxer | localhost | asxxn_assess_db | Sleep | 1 | | |
| 48673 | efxxnusr | localhost | exxdb | Sleep | 0 | | |
| 48674 | root | localhost | | Query | 0 | init | show processlist |
+-------+------------------+-----------+--------------------+---------+------+-------+------------------+
Uptime: 28800 Threads: 4 Questions: 22535205 Slow queries: 0 Opens: [B]126901[/B] Flush tables: 3 Open tables: [B]4000[/B] Queries per second avg: 782.472



top - 08:03:07 up 8:38, 0 users, load average: 2.26, 2.45, 2.53
Tasks: 219 total, 2 running, 215 sleeping, 0 stopped, 2 zombie
%Cpu(s): 14.3 us, 2.8 sy, 0.3 ni, 82.5 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 32005396 total, 10531896 free, 3413288 used, 18060212 buff/cache
KiB Swap: 1048572 total, 1048572 free, 0 used. 26101896 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
16014 mysql 20 0 4263236 1.1g 15104 S 34.2 3.5 405:23.63 mysqld
232049 xxxx+ 20 0 322940 75712 13264 S 31.9 0.2 0:00.96 php-cgi
2017 root 20 0 770376 13164 10344 S 1.0 0.0 11:58.37 db_governor
2209 root 30 10 383188 63724 4508 S 1.0 0.2 2:39.24 python3
2185 root 30 10 586180 47696 2020 S 0.7 0.1 2:24.06 python3
9 root 20 0 0 0 0 S 0.3 0.0 1:09.78 rcu_sched
1280 elastic+ 20 0 8175596 585412 11028 S 0.3 1.8 2:25.16 java
8707 root 20 0 1956176 11196 6752 S 0.3 0.0 0:27.92 PassengerAgent
230745 nobody 20 0 341588 20928 4200 S 0.3 0.1 0:00.10 httpd
 

PixelDad

Member
Feb 20, 2022
9
0
1
Sydney
cPanel Access Level
Root Administrator
So having Open tables: 4000 is bad? I only restart SQL an hour ago and it's now max at 4000 already. I'm not sure if this is normal.


[[email protected] ~]# mysqladmin proc status
+------+---------------+-----------+-------------+---------+------+-----------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+---------------+-----------+-------------+---------+------+-----------+------------------------------------------------------------------------------------------------------+
| 95 | root | localhost | | Sleep | 50 | | |
| 2671 | txxser | localhost | trxxres_db | Query | 0 | executing | SELECT `main_table`.* FROM `sm_megamenu_items` AS `main_table` WHERE (main_table.parent_id = 50) AND |
| 2672 | root | localhost | | Query | 0 | init | show processlist |
+------+---------------+-----------+-------------+---------+------+-----------+------------------------------------------------------------------------------------------------------+
Uptime: 1410 Threads: 3 Questions: 970644 Slow queries: 0 Opens: 8196 Flush tables: 3 Open tables: 4000 Queries per second avg: 688.400

MySQL config. The install is standard and I only added the following into the config "skip-name-resolve", "event_scheduler=off"


[mysqld]
skip-name-resolve
performance-schema = 0
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
innodb_buffer_pool_size = 52428800
max_allowed_packet = 268435456
open_files_limit = 40000
innodb_file_per_table = 1
default-authentication-plugin = mysql_native_password
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
event_scheduler=off




mysql> SHOW GLOBAL STATUS LIKE 'Open_%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Open_files | 1609 |
| Open_streams | 0 |
| Open_table_definitions | 652 |
| Open_tables | 4000 |
| Opened_files | 1609 |
| Opened_table_definitions | 1530 |
| Opened_tables | 9874 |
+--------------------------+-------+
7 rows in set (0.00 sec)
 
Last edited:

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
10,360
1,632
363
cPanel Access Level
Root Administrator
Thanks for that. It's important to note that "opened_tables" is different from the "open" value on that page. The opened tables number, the "opens" in the "mysqladmin" output, is how many tables the MySQL server has accessed, while the "Open Tables" number is how many tables are actively opened. We can see more details about the mysqladmin command here:


The 4000 number you're seeing is the table_open_cache value on the system, which you can likely find by running this on the server:

Code:
mysql -e "show variables like '%open%';"
Can you see if that value is set to 4000 on your system?

If so, that is likely where you're running into issues as you're hitting that limit. Details on adjusting that limit, and more details on how MySQL handles tables, can be found here:


but it sounds like the system just needs some MySQL optimization in order to run its best.
 

PixelDad

Member
Feb 20, 2022
9
0
1
Sydney
cPanel Access Level
Root Administrator
Hi, yes. I noted that Open tables: 4000 is reached almost instantly after mysql restart. Is that normal?


mysql> SHOW GLOBAL STATUS LIKE 'Open_%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Open_files | 1609 |
| Open_streams | 0 |
| Open_table_definitions | 652 |
| Open_tables | 4000 |
| Opened_files | 1609 |
| Opened_table_definitions | 1530 |
| Opened_tables | 9874 |
+--------------------------+-------+
7 rows in set (0.00 sec)
 
Last edited:

PixelDad

Member
Feb 20, 2022
9
0
1
Sydney
cPanel Access Level
Root Administrator
I have 8 CPU Cores and 32GB of ram. When mysql degrading in speed I also noticed phpmyadmin was also slow at loading database tables.
But low CPU and RAM utilisation is not high.

Do I need to change:"

open_files_limit = 40000

to something higher? Like 8000?
 

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
10,360
1,632
363
cPanel Access Level
Root Administrator
PHMyAdmin would also be affected as that does use MySQL.

If the CPU and RA< utilization is low, it would be safe to bump the values. You'll want to start by changing the table_open_cache value to 8000, or possibly even higher. Since that value isn't present in your current configuration you can just add it on a new line. Please be sure to restart MySQL after that change so the new value is activated in the system.
 

PixelDad

Member
Feb 20, 2022
9
0
1
Sydney
cPanel Access Level
Root Administrator
I'm very new to this script. Anything below that you suggest adjusting as priority?

My latest config file, does it look OK? I've tuned. I have 8 CPU cores and 32GB RAM

[mysqld]
skip-name-resolve
performance-schema = 1
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
innodb_buffer_pool_size = 52428800
max_allowed_packet = 268435456
innodb_file_per_table = 1
default-authentication-plugin = mysql_native_password
sql-mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
event_scheduler = off
table_open_cache = 120000
innodb_open_files = 120000
table_definition_cache = 75000
open_files_limit = 256000



>> MySQLTuner 1.9.4
* Jean-Marie Renouard <[email protected]>
* Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[--] Performing tests on localhost:3306
[OK] Currently running supported MySQL version 8.0.28-cll-lve
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysqld.log exists
[--] Log file: /var/log/mysqld.log(122K)
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[OK] Log file /var/log/mysqld.log is readable.
[!!] /var/log/mysqld.log contains 753 warning(s).
[!!] /var/log/mysqld.log contains 2 error(s).
[--] 4 start(s) detected in /var/log/mysqld.log
[--] 1) 2022-03-05T12:37:34.802998Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
[--] 2) 2022-03-05T12:37:34.802922Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 3) 2022-03-05T12:31:53.239162Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
[--] 4) 2022-03-05T12:31:53.239105Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 1 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2022-03-05T12:37:17.661209Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve)MySQL Community Server - GPL.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 7.2G (Tables: 2270)
[--] Data in InnoDB tables: 2.9G (Tables: 3815)
[--] Data in MEMORY tables: 0B (Tables: 47)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Views Metrics -----------------------------------------------------------------------------

-------- Triggers Metrics --------------------------------------------------------------------------

-------- Routines Metrics --------------------------------------------------------------------------

-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 58s (14K q [249.810 qps], 75 conn, TX: 38M, RX: 2M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 30.5G
[--] Max MySQL memory : 38.1G
[--] Other process memory: 0B
[--] Total buffers: 90.0M global + 257.9M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 3.9G (12.66% of installed RAM)
[!!] Maximum possible memory usage: 38.1G (124.87% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/14K)
[OK] Highest usage of available connections: 9% (15/151)
[OK] Aborted connections: 1.33% (1/75)
[--] Query cache have been removed in MySQL 8
[OK] Sorts requiring temporary tables: 0% (5 temp sorts / 3K sorts)
[!!] Joins performed without indexes: 52
[OK] Temporary tables created on disk: 0% (0 on disk / 220 total)
[OK] Thread cache hit rate: 80% (15 created / 75 connections)
[OK] Table cache hit rate: 98% (66K hits / 67K requests)
[OK] table_definition_cache(75000) is upper than number of tables(6462)
[OK] Open file limit used: 1% (429/40K)
[OK] Table locks acquired immediately: 99% (2K immediate / 2K locks)
[OK] Binlog cache memory access: 100.00% (14 Memory / 14 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled on last MySQL versions.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 50.0M/2.9G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (192 %): 48.0M * 2/50.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.53% (990333 hits/ 995018 total)
[!!] InnoDB Write Log efficiency: 86.39% (762 hits/ 882 total)
[OK] InnoDB log waits: 0.00% (0 waits / 120 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.

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

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

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

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Check warning line(s) in /var/log/mysqld.log file
Check error line(s) in /var/log/mysqld.log file
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
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)
innodb_buffer_pool_size (>= 2.9G) if possible.
innodb_log_file_size should be (=6M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

 
Last edited:

PixelDad

Member
Feb 20, 2022
9
0
1
Sydney
cPanel Access Level
Root Administrator
I've noticed some warning with my new config

2022-03-05T12:31:32.851957Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 19919 (requested 120000)
2022-03-05T12:31:33.030089Z 0 [Warning] [MY-012364] [InnoDB] innodb_open_files should not be greater than the open_files_limit.
2022-03-05T12:37:19.260663Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 19919 (requested 120000)
2022-03-05T12:37:19.587425Z 0 [Warning] [MY-012364] [InnoDB] innodb_open_files should not be greater than the open_files_limit.
 

PixelDad

Member
Feb 20, 2022
9
0
1
Sydney
cPanel Access Level
Root Administrator
Below is our latest result. Could you recommend a good settings please? I have about 30 sites running magneto and woocommerce


[mysqld]
skip-name-resolve
performance-schema = 1
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
innodb_buffer_pool_size = 52428800
max_allowed_packet = 268435456
innodb_file_per_table = 1
default-authentication-plugin = mysql_native_password
sql-mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
event_scheduler = off
table_open_cache = 120000
innodb_open_files = 120000
table_definition_cache = 75000
open_files_limit = 256000


>> MySQLTuner 1.9.4
* Jean-Marie Renouard <[email protected]>
* Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[--] Performing tests on localhost:3306
[OK] Currently running supported MySQL version 8.0.28-cll-lve
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysqld.log exists
[--] Log file: /var/log/mysqld.log(416K)
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[OK] Log file /var/log/mysqld.log is readable.
[!!] /var/log/mysqld.log contains 2559 warning(s).
[!!] /var/log/mysqld.log contains 4 error(s).
[--] 6 start(s) detected in /var/log/mysqld.log
[--] 1) 2022-03-06T20:16:37.359003Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
[--] 2) 2022-03-06T20:16:37.358952Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port:33060, socket: /var/run/mysqld/mysqlx.sock
[--] 3) 2022-03-05T12:37:34.802998Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
[--] 4) 2022-03-05T12:37:34.802922Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port:33060, socket: /var/run/mysqld/mysqlx.sock
[--] 5) 2022-03-05T12:31:53.239162Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
[--] 6) 2022-03-05T12:31:53.239105Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port:33060, socket: /var/run/mysqld/mysqlx.sock
[--] 2 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2022-03-06T20:16:04.924647Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve) MySQL Community Server - GPL.
[--] 2) 2022-03-05T12:37:17.661209Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve) MySQL Community Server - GPL.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 7.3G (Tables: 2270)
[--] Data in InnoDB tables: 2.9G (Tables: 3815)
[--] Data in MEMORY tables: 0B (Tables: 47)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Views Metrics -----------------------------------------------------------------------------

-------- Triggers Metrics --------------------------------------------------------------------------

-------- Routines Metrics --------------------------------------------------------------------------

-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 3h 45m 25s (57M q [571.947 qps], 163K conn, TX: 160G, RX: 10G)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 30.5G
[--] Max MySQL memory : 38.1G
[--] Other process memory: 0B
[--] Total buffers: 90.0M global + 257.9M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 22.5G (73.72% of installed RAM)
[!!] Maximum possible memory usage: 38.1G (124.87% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/57M)
[OK] Highest usage of available connections: 58% (89/151)
[OK] Aborted connections: 0.04% (60/163316)
[--] Query cache have been removed in MySQL 8
[OK] Sorts requiring temporary tables: 0% (5K temp sorts / 16M sorts)
[!!] Joins performed without indexes: 120758
[OK] Temporary tables created on disk: 0% (360 on disk / 2M total)
[OK] Thread cache hit rate: 96% (5K created / 163K connections)
[OK] Table cache hit rate: 99% (67M hits / 67M requests)
[OK] table_definition_cache(75000) is upper than number of tables(6462)
[OK] Open file limit used: 23% (9K/40K)
[OK] Table locks acquired immediately: 99% (28M immediate / 28M locks)
[OK] Binlog cache memory access: 99.96% (193118 Memory / 193194 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled on last MySQL versions.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 50.0M/2.9G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (192 %): 48.0M * 2/50.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.72% (2552515335 hits/ 2559632732 total)
[!!] InnoDB Write Log efficiency: 71.33% (2172059 hits/ 3044976 total)
[OK] InnoDB log waits: 0.00% (0 waits / 872917 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.

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

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

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

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Check warning line(s) in /var/log/mysqld.log file
Check error line(s) in /var/log/mysqld.log file
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
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)
innodb_buffer_pool_size (>= 2.9G) if possible.
innodb_log_file_size should be (=6M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
 

PixelDad

Member
Feb 20, 2022
9
0
1
Sydney
cPanel Access Level
Root Administrator
MYSQL slowed down again today and the query would not process or have a long delay. I feel like something is getting full.

So the MSQL CPU starts from 27% then over 2 days it slowly go upto 100% CPU which then I need to restart mysql. Do you know why my MYSQL CPU goes up?

I'm thinking of setting pool instant to 20 and pool size to 20GB. I have 32GB and 8 CPU. What do you think?

innodb_buffer_pool_instances = 20 # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size = 20G # Use up to 70-80% of RAM

Anything below that you can tell? Below is after about 5 hours of mysql reset.


mysql> SHOW ENGINE INNODB STATUS;


| InnoDB | |
=====================================
2022-03-10 18:10:58 140503145830144 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 49 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4441 srv_active, 0 srv_shutdown, 14747 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1942
OS WAIT ARRAY INFO: signal count 10897
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 5266688
Purge done for trx's n:o < 5266687 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421986883538136, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421986883537328, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421986883536520, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 18
38257 OS file reads, 290562 OS file writes, 74018 OS fsyncs
0.06 reads/s, 16384 avg bytes/read, 33.70 writes/s, 9.29 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 48, seg size 50, 164 merges
merged operations:
insert 165, delete mark 7, delete 6
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 1859467, node heap has 216 buffer(s)
Hash table size 1859467, node heap has 107 buffer(s)
Hash table size 1859467, node heap has 177 buffer(s)
Hash table size 1859467, node heap has 59 buffer(s)
Hash table size 1859467, node heap has 33 buffer(s)
Hash table size 1859467, node heap has 1827 buffer(s)
Hash table size 1859467, node heap has 1655 buffer(s)
Hash table size 1859467, node heap has 213 buffer(s)
1599.07 hash searches/s, 794.21 non-hash searches/s
---
LOG
---
Log sequence number 14174488557
Log buffer assigned up to 14174488557
Log buffer completed up to 14174488557
Log written up to 14174488557
Log flushed up to 14174488557
Added dirty pages up to 14174488557
Pages flushed up to 14174488557
Last checkpoint at 14174488557
102713 log i/o's done, 1.14 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 11121288
Buffer pool size 458712
Free buffers 416670
Database pages 37755
Old database pages 14081
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 48165, not young 1099968
2.25 youngs/s, 0.12 non-youngs/s
Pages read 37008, created 251468, written 139490
0.06 reads/s, 61.69 creates/s, 26.52 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 37755, unzip_LRU len: 0
I/O sum[10600]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 57337
Free buffers 52515
Database pages 4287
Old database pages 1601
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4988, not young 118677
0.02 youngs/s, 0.00 non-youngs/s
Pages read 4246, created 31419, written 18150
0.00 reads/s, 7.72 creates/s, 2.91 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4287, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 57341
Free buffers 52030
Database pages 4773
Old database pages 1781
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4887, not young 154239
0.00 youngs/s, 0.04 non-youngs/s
Pages read 4687, created 31449, written 10899
0.02 reads/s, 7.72 creates/s, 2.13 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4773, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 57339
Free buffers 51722
Database pages 5075
Old database pages 1892
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 15466, not young 120714
2.03 youngs/s, 0.00 non-youngs/s
Pages read 4963, created 31098, written 18110
0.00 reads/s, 7.62 creates/s, 3.43 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5075, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 57338
Free buffers 52091
Database pages 4713
Old database pages 1759
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5388, not young 91894
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4569, created 31564, written 15680
0.00 reads/s, 7.72 creates/s, 3.81 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4713, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 57339
Free buffers 52767
Database pages 4041
Old database pages 1509
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3919, not young 78335
0.06 youngs/s, 0.04 non-youngs/s
Pages read 4011, created 31445, written 12165
0.02 reads/s, 7.72 creates/s, 1.67 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4041, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 57338
Free buffers 51357
Database pages 5443
Old database pages 2026
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4639, not young 168080
0.06 youngs/s, 0.00 non-youngs/s
Pages read 5309, created 31550, written 14784
0.00 reads/s, 7.72 creates/s, 2.87 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5443, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 57343
Free buffers 51961
Database pages 4852
Old database pages 1808
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4702, not young 177824
0.04 youngs/s, 0.00 non-youngs/s
Pages read 4692, created 31550, written 23287
0.00 reads/s, 7.72 creates/s, 4.29 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4852, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 57337
Free buffers 52227
Database pages 4571
Old database pages 1705
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4176, not young 190205
0.04 youngs/s, 0.04 non-youngs/s
Pages read 4531, created 31393, written 26415
0.02 reads/s, 7.72 creates/s, 5.42 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4571, unzip_LRU len: 0
I/O sum[1325]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=960283, Main thread ID=140503154222848 , state=sleeping
Number of rows inserted 2294966, updated 22627, deleted 9048, read 386680615
567.05 inserts/s, 0.14 updates/s, 0.00 deletes/s, 5342.50 reads/s
Number of system rows inserted 971, updated 1277, deleted 966, read 1704723
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 27.82 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
 
Last edited: