Mysql server getting slower

[charliecres]

here is my my.cnf


[mysqld]
sql_mode = "NO_ENGINE_SUBSTITUTION"
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=80000
innodb_file_per_table = 1
mysqlx = 0




and here is MySQL process

Id	User	Host	db	Command	Time	State	Info
5	event_scheduler	localhost	NULL	Daemon	111	Waiting on empty queue	NULL
94	sgxy_surapp	localhost	sgxy_surapp	Sleep	91		NULL
110	root	localhost	NULL	Sleep	27		NULL
341	weon_Da1	localhost	weon_Da1	Sleep	9		NULL
395	root	localhost	NULL	Query	0	init	SHOW PROCESSLIST

suddenly MySQL server getting too much slow. how can I fix this permanently? is there anything wrong or need to add at my.cnf?

 

[charliecres]

and right now here MySQL® Processes !!

Id	User	Host	db	Command	Time	State	Info
5	event_scheduler	localhost	NULL	Daemon	7290	Waiting on empty queue	NULL
72	root	localhost	NULL	Sleep	17		NULL
45766	bes_steo	localhost	bes_steo	Sleep	1		NULL
50445	bes_steo	localhost	bes_steo	Execute	293	Waiting for table metadata lock	insert into `tblemails` (`userid`, `date`, `to`, `cc`, `bcc`, `subject`, `message`, `pending`, `fail
50988	bes_seo	localhost	bes_seo	Execute	245	Waiting for table metadata lock	select * from `tblemails` where `pending` = 1 limit 25
51153	babu_w61	localhost	babu_w61	Sleep	226		NULL
51676	mad_host	localhost	mad_host	Sleep	130		NULL
52013	ytpa_best	localhost	ytpa_best	Sleep	2		NULL
52226	wek_Use1	localhost	wek_Use1	Sleep	29		NULL
52349	root	localhost	NULL	Query	0	init	SHOW PROCESSLIST

 

[andrew.n]

slower compared to what?

 

[charliecres]

slower compared to what?

compared to regular speed.

 

[kernow]

While its not perfect, the "Edit SQL Configuration" in WHM can give you a reasonable set of variables to use.
Note! don't use if your MySQL server has been up for < 48 hours.

 

[ffeingol]

The issue does not appear to be MySQL itself, it's something with the bes account and the tblemails table. Something (which is odd that it's not showing) is holding a lock on that table, so the other processes can't process. That's typically not a config issue, it's a coding to database design issue.

Can you run "describe tblemails;" so we can see the table structure?

 

[charliecres]

The issue does not appear to be MySQL itself, it's something with the bes account and the tblemails table. Something (which is odd that it's not showing) is holding a lock on that table, so the other processes can't process. That's typically not a config issue, it's a coding to database design issue.

Can you run "describe tblemails;" so we can see the table structure?

"bash: describe: command not found"


and the problem is MySQL server performance getting too much slower every morning!!

 

[andrew.n]

Is it happening constantly or just in the morning?
what version of MySQL do you have?

 

[charliecres]

Is it happening constantly or just in the morning?
what version of MySQL do you have?

it’s happening morning to evening!
mysql v8.0.32!

i am using cloudlinux
MySQL Governor
Mode - abusers
Governor ver. - 1.2-88

 

[mtindor]

That table looks like it is probably a WHMCS table that holds / retains emails sent by WHMCS. And if your WHMCS has a lot of accounts, has been active for years, and/or has a lot of activity in it, that table can get pretty large (as well as a couple of other tables).

I would recommend checking the size of your tblemails table. It's probably pretty large. And you can prune it within WHMCS if indeed it is a WHMCS table.

WHMCS --> Utilities --> System --> System Cleanup --> Advanced --> Prune Saved Emails

M