Sample Header Ad - 728x90

MySQL abnormal connection peak and page cleaner warnings

2 votes
1 answer
204 views
I'm running MySql 5.7.42 on Ubuntu 18.04 on VM with 12 Core, 64GB Ram and SSD storage. This server is dedicated as DB server, some scripts and maintenance (backups) runs nightly but no other services are provided. Recently, as data keep growing, I start to see a lot messages like:
2024-01-12T07:00:24.883652Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 8686ms. The settings might not be optimal. (flushed=998 and evicted=0, during the time.)
2024-01-12T08:02:03.366693Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6949ms. The settings might not be optimal. (flushed=6118 and evicted=0, during the time.)
2024-01-12T08:22:43.747484Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6208ms. The settings might not be optimal. (flushed=10013 and evicted=0, during the time.)
and randomly I see strange connection peaks made by 60/80 simultaneous connections. Tipically simultaneous were from 5 to 15 so I left 151 standard values. One time it happened that all 151 connection were used (few seconds) so no other connection were allowed. First of all I investigate for a request flood from frontend server but I didn't found anything strange. I monitorated the situation for a week so I'm pretty sure, Indeed, I have seen that the problem occurs even in times of relative low traffic! mysql conf
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
key_buffer_size		= 1400M
max_allowed_packet	= 64M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP

log_error = /var/log/mysql/error.log

slow_query_log		= 1
slow_query_log_file	= /var/log/mysql/mysql-slow.log
long_query_time = 1

expire_logs_days	= 10
max_binlog_size   = 100M

innodb_buffer_pool_size = 42G # (adjust value here, 50%-70% of total RAM)
innodb_log_file_size = 2000M
innodb_log_buffer_size = 512M
innodb_flush_log_at_trx_commit = 2 
innodb_flush_method = O_DIRECT
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_buffer_pool_instances=36
query_cache_limit       = 32M
query_cache_size        = 0
query_cache_type        = 0
innodb_read_io_threads = 16
innodb_write_io_threads = 16

innodb_lru_scan_depth=256

tmp_table_size = 64M
max_heap_table_size = 64M
innodb_autoinc_lock_mode =2
sync_binlog=0 
bulk_insert_buffer_size=512M

join_buffer_size = 3M 
sort_buffer_size = 512K
table_open_cache=4000
innodb_open_files=4000

max_connections=151
wait_timeout=7200
interactive_timeout=7200
mysqltuner.pl:
>>  MySQLTuner 2.5.0
	 * Jean-Marie Renouard 
	 * Major Hayden 
 >>  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
[OK] Logged in using credentials from Debian maintenance account.
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 55.7G (Tables: 259)
[--] Data in MyISAM tables: 17.2G (Tables: 1)
[OK] Total fragmented tables: 0
 
[OK] Currently running supported MySQL version 5.7.33-0ubuntu0.18.04.1-log
 
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql/error.log exists
[--] Log file: /var/log/mysql/error.log (174B)
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 MB
[OK] Log file /var/log/mysql/error.log is readable.
[OK] /var/log/mysql/error.log doesn't contain any warning.
[OK] /var/log/mysql/error.log doesn't contain any error.
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Views Metrics -----------------------------------------------------------------------------
 
-------- Triggers Metrics --------------------------------------------------------------------------
 
-------- Routines Metrics --------------------------------------------------------------------------
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] No Role user detected
[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: 11d 20h 17m 50s (346M q [338.255 qps], 7M conn, TX: 858G, RX: 101G)
[--] Reads / Writes: 57% / 43%
[--] Binary logging is disabled
[--] Physical Memory     : 64.9G
[--] Max MySQL memory    : 47.7G
[--] Other process memory: 0B
[--] Total buffers: 46.9G global + 4.1M per thread (151 max threads)
[--] Performance_schema Max memory usage: 172M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 47.7G (73.55% of installed RAM)
[OK] Maximum possible memory usage: 47.7G (73.54% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (8K/346M)
[!!] Highest connection usage: 100% (152/151)
[OK] Aborted connections: 0.00% (32/7732702)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (144K temp sorts / 18M sorts)
[!!] Joins performed without indexes: 248445
[OK] Temporary tables created on disk: 18% (2M on disk / 14M total)
[OK] Thread cache hit rate: 98% (88K created / 7M connections)
[OK] Table cache hit rate: 99% (397M hits / 398M requests)
[OK] table_definition_cache (1609) is greater than number of tables (540)
[OK] Open file limit used: 0% (3/5K)
[OK] Table locks acquired immediately: 100% (45K immediate / 45K locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 172.6M
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Consider migrating 1 following tables to InnoDB:
[--] * InnoDB migration request for gilog.gestionale_events_log Table: ALTER TABLE gilog.gestionale_events_log ENGINE=InnoDB;
[--] General MyIsam metrics:
[--]  +-- Total MyISAM Tables  : 1
[--]  +-- Total MyISAM indexes : 3.5G
[--]  +-- KB Size :1.4G
[--]  +-- KB Used Size :264.4M
[--]  +-- KB used :18.9%
[--]  +-- Read KB hit rate: 84.4% (7K cached / 1K reads)
[--]  +-- Write KB hit rate: 0% (0 cached / 0 writes)
[!!] Key buffer used: 18.9% (264.4M used / 1.4G cache)
[!!] Key buffer size / total MyISAM indexes: 1.4G/3.5G
[!!] Read Key buffer hit rate: 84.4% (7K cached / 1K reads)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 45.0G ) under limit for 64 bits architecture: (17179869184.0G )
[!!] InnoDB buffer pool / data size: 45.0G / 55.7G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (8.68055555555556%): 2.0G * 2 / 45.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 36
[--] Number of InnoDB Buffer Pool Chunk: 360 for 36 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: 100.00% (114108975883 hits / 114111666996 total)
[!!] InnoDB Write Log efficiency: 27% (23815946 hits / 88191482 total)
[OK] InnoDB log waits: 0.00% (0 waits / 64375536 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:
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_join_buffer_size 
    MyISAM engine is deprecated, consider migrating to InnoDB
    Be careful, increasing innodb_log_file_size / innodb_log_files_in_group means higher crash recovery mean time
Variables to adjust:
    max_connections (> 151)
    wait_timeout ( 3.0M, or always use indexes with JOINs)
    key_buffer_size (~ 277M)
    key_buffer_size (> 3.5G)
    innodb_buffer_pool_size (>= 55.7G) if possible.
    innodb_log_file_size should be (=5G) if possible, so InnoDB total log file size equals 25% of buffer pool size.
    innodb_buffer_pool_instances(=45)
    innodb_log_buffer_size (> 512M)
server params:
ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 265423
max locked memory       (kbytes, -l) 16384
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 265423
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
I guess first thigs is solving InnoDB: page_cleaner warning because it appears many times in a day. Param innodb_lru_scan_depth is set to 256, I don't think any lower value could help. So basically 2 ways remains: 1. cleaning database from older unused records 2. increases resource as mysqltuner suggests Any advice? Thanks
Asked by Sbraaa (21 rep)
Jan 12, 2024, 09:33 AM
Last activity: Jun 18, 2025, 06:05 PM