mysql hangs - Optimization required?

[harmonypersechino5348]

MYSQL was hanging with nothing in the error log. When restarting the only messages I could see are

2022-06-01T08:38:59.762398Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.29-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
2022-06-01T08:38:54.148053Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.29-cll-lve) MySQL Community Server - GPL.
2022-06-01T08:38:46.314464Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 9445 user: 'root'.

I believe it maybe something in my configuration as the performance seems to be quite poor and doesn't use as much resources as I was expecting

my.cnf

[mysqld]
disable-log-bin = 1
default-authentication-plugin = mysql_native_password
performance-schema = 0
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#innodb_buffer_pool_size = 134217728
max_allowed_packet = 268435456
#open_files_limit=49000
# LimitNOFILE=100000
# LimitMEMLOCK=100000
innodb_file_per_table = 1
mysqlx = 0

innodb_buffer_pool_size=15G
max_heap_table_size=128M
tmp_table_size=128M
max_connections=400
table_open_cache=37000
table_definition_cache=37000
thread_cache_size=200
key_buffer_size=128M
sort_buffer_size=1M
read_buffer_size=4M
read_rnd_buffer_size=512k
join_buffer_size=3M

I have also set limits of

LimitNOFILE=100000
LimitMEMLOCK=100000

What could the freeze be? and what configuration do you suggest for a powerful server (64GB RAM, Ryzen 9 5950X, NVMe) using Litespeed

 

[cPRex]

Hey there! The best way to check the problem would be to run the "mysqladmin proc status" command in real-time while the issue is happening. This will show what queries the MySQL server is handling and may point you in the right direction.

If nothing is being written to the error_log, trying to watch the issue in real-time is your best bet toward a resolution.

 

[harmonypersechino5348]

Hey there! The best way to check the problem would be to run the "mysqladmin proc status" command in real-time while the issue is happening. This will show what queries the MySQL server is handling and may point you in the right direction.

If nothing is being written to the error_log, trying to watch the issue in real-time is your best bet toward a resolution.

Not been able to do that yet as it's not come back long enough. The only thing I can see constantly running with not much else is event_scheduler should I consider disabling? Read some other users have issues with event_scheduler eating up CPU

 

[cPRex]

I suppose it's possible, but I'd hesitate to officially recommend anything with what little information we have.

Since you have root access to the machine, you're always welcome to submit a ticket to our team to have us take a look directly.

 

[cybershubham]

Not been able to do that yet as it's not come back long enough. The only thing I can see constantly running with not much else is event_scheduler should I consider disabling? Read some other users have issues with event_scheduler eating up CPU

Hey, did you manage to solve it?

I am facing a similar problem in MySQL 8.0.29 ?

 

[cPRex]

@cybershubham - it's very unlikely that one person's fix for a load or performance issue will resolve another. It's best to examine the system with the "mysqladmin proc status" command while the issue is happening so you can see what MySQL is doing in real-time, or check the MySQL error logs for more details.

 

[ejsolutions]

Useful tools if used carefully and backups of my.cnf taken: mysqltuner.pl & tuning-primer.sh
They will give some conflicting information that you'll need to make a judgement on. Go in small steps.
(max_connections=400 <--that's a lot of committed memory )

 

[cybershubham]

@cybershubham - it's very unlikely that one person's fix for a load or performance issue will resolve another. It's best to examine the system with the "mysqladmin proc status" command while the issue is happening so you can see what MySQL is doing in real-time, or check the MySQL error logs for more details.

Yeah well, I regret using MySQL 8 now.

mysqladmin proc status had nothing much except event_scheduler.

No errors in the logs.

MySQL just degrades in performance in 8-10 hours. Litespeed starts giving 503 on websites.
After a restart of MySQL, everything goes back to normal.

CPU load average is not even 10%. Memory usage is normal.

Everything was normal until 2-3 days ago.

mysqltuner.pl doesn't say anything useful either.

 

[cPRex]

Hmmmmmm, I haven't heard of similar behavior like that. Could you create a ticket with our support team so we can check the system directly?

 

[cybershubham]

Hmmmmmm, I haven't heard of similar behavior like that. Could you create a ticket with our support team so we can check the system directly?

Hi, I have created a ticket.

Id #94459054

 

[cPRex]

Thanks for that - I'm following along with that ticket on my end now.

 

[harmonypersechino5348]

Hi, I have created a ticket.

Id #94459054

No I did not find a fix. Memory usage appears to be low from what I can tell yet it's swapping out memory even with swappiness set to 0

Have you had any luck in your ticket?

 

[cybershubham]

No I did not find a fix. Memory usage appears to be low from what I can tell yet it's swapping out memory even with swappiness set to 0

Have you had any luck in your ticket?

Unfortunately, no.

First, it happened every 6 hours, now it happens every 18 hours. Clients are angry.

cPanel support couldn't help me either. Now I have to admit, MySQL 8 sucks.

Deploying a new server with MariaDB 10.3 and moving the accounts lastly.

 

[harmonypersechino5348]

Any update to this maybe from cPanel support? @cPRex

mysql threads just eventually build up and mysql hangs. A mysql restart instantly fixes it.

Uptime: 103689  Threads: 70  Questions: 86589599  Slow queries: 0  Opens: 2406161  Flush tables: 3  Open tables: 4000  Queries per second avg: 835.089

After restart

Uptime: 807  Threads: 8  Questions: 780539  Slow queries: 0  Opens: 16654  Flush tables: 3  Open tables: 3998  Queries per second avg: 967.210

The time field never goes above 60 the only thing I can see is the "State" from

mysqladmin proc status

seems to have a lot of "closing tables" and "starting" vs after a restart.

 

[cPRex]

I don't have any updates on my end, since I don't think this is an issue with the services on the machine, but a particular issue with the system. Were you able to open a ticket to have our team check this out?