harmonypersechino5348

Active Member
Dec 6, 2020
41
3
8
NA
cPanel Access Level
Website Owner
MYSQL was hanging with nothing in the error log. When restarting the only messages I could see are

Code:
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

Code:
[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

Code:
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

Jurassic Moderator
Staff member
Oct 19, 2014
11,780
1,872
363
cPanel Access Level
Root Administrator
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

Active Member
Dec 6, 2020
41
3
8
NA
cPanel Access Level
Website Owner
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

Jurassic Moderator
Staff member
Oct 19, 2014
11,780
1,872
363
cPanel Access Level
Root Administrator
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.
 

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
11,780
1,872
363
cPanel Access Level
Root Administrator
@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.
 
  • Like
Reactions: ejsolutions

ejsolutions

Well-Known Member
Jan 6, 2013
77
32
68
cPanel Access Level
Root Administrator
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

Member
Jan 25, 2020
7
0
1
India
cPanel Access Level
Root Administrator
@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.
 

cybershubham

Member
Jan 25, 2020
7
0
1
India
cPanel Access Level
Root Administrator
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

Active Member
Dec 6, 2020
41
3
8
NA
cPanel Access Level
Website Owner
Any update to this maybe from cPanel support? @cPRex

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

Code:
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

Code:
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
Code:
mysqladmin proc status
seems to have a lot of "closing tables" and "starting" vs after a restart.