MySQL server slows down 24 hours after reboot

Goku__

7/25/24, 9:11 AM

I have a MySQl server which slows down every 22-24 hours after restarting mysql. After restarting mysql it starts working properly and after about 20 hours it starts to slow down. I increased the tmp_table_size and max_heap_table_size to 256M. It hasn't solved the problem.

Here is my.cnf configuration:

max_connections = 1000
max_allowed_packet = 512M
innodb_buffer_pool_size =15G
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_instances = 8
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_method = O_DIRECT
key_buffer_size = 24M
tmp_table_size = 256M
max_heap_table_size = 256M
event_scheduler = ON
wait_timeout = 300
interactive_timeout = 30

It is a standalone VM with 32 GB RAM. No other major programs are running in that VM.

1 + 5

mysql

AlexD

7/25/24, 11:56 AM

What is the memory allocation on your server? How much is the total RAM, how much RAM is allocated to other programs?

Goku__

7/27/24, 5:59 AM

It is a standalone VM with 32 GB RAM. No other major programs are running in that VM.

Wilson Hauck

7/27/24, 2:07 PM

Additional DB information request at 20 hours of uptime, please. OS, Version? # cores, any SSD or NVME devices on MySQL Host server? Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*), sum(data_length), sum(index_length), sum(data_free) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions.

Wilson Hauck

7/27/24, 2:09 PM

Additional DB information request after running SLOW for two hours, please. Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*), sum(data_length), sum(index_length), sum(data_free) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after 2 hours of SLOW UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server SLOW workload tuning analysis to provide suggestions.

Wilson Hauck

7/27/24, 2:10 PM

Post TEXT data on justpaste.it and share the links. Additional very helpful OS information includes - please, htop 1st page, if available, TERMINATE, top -b -n 1 for most active apps, top -b -n 1 -H for details on your mysql threads memory and cpu usage, ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device & core/cpu count, df -h for Used - Free space by device, df -i for inode info by device, free -h for Used - Free Mem: and Swap:, cat /proc/meminfo includes VMallocUused, for server workload tuning analysis to provide suggestions.

Score:1

Server

Rick James

7/25/24, 2:49 PM

innodb_buffer_pool_size needs to be only 70% of available RAM.
The default 151 max_connections is likely to be adequate; if you think not, please explain why.
tmp_table_size and max_heap_table_size should be no more than 1% of RAM. If you think you need them bigger then I think we need to look at the queries and indexes that lead you to that conclusion. And then discuss "composite" indexes.

+ 2

Goku__

7/27/24, 6:27 AM

Reduced the "tmp_table_size" and "max_heap_table_size" to 1 M and set the "innodb_buffer_pool_size" to 70% of RAM. Lets see if it solves the problem.

Wilson Hauck

8/28/24, 2:37 AM

@Goku__ What is your situation at this time? Still need assistance?

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: MySQL server slows down 24 hours after reboot

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.