High MySQL usage - how to troubleshoot?

[Tsuna]

We are facing really high MySQL usage on the server

my.cf file had

[mysqld]
performance-schema=0
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=100000
query_cache_size =128M
query_cache_limit=4M
join_buffer_size=3M
tmp_table_size=256M
max_heap_table_size=256M
innodb_buffer_pool_size=3G
table_open_cache=32768
table_definition_cache=32768
sort_buffer_size=1M
read_buffer_size=4M
read_rnd_buffer_size=1M

Also, read around and ran mysqltuner and it only gave the following

[!!] Log file /var/lib/mysql/myserverhere.err is bigger than 32 Mb

The rig is

Intel Dual Xeon E5620
Memory 32 GB DDR3 ECC
Bandwidth 1 Gbps
Primary Storage Drive 240 GB SSD
Second Storage Drive 2 TB SATA
Operating System CentOS 7 (64 bits)
Control Panel cPanel/WHM (Linux)

I'm fairly new to this so I'm not completely sure of things, some guidance on how to make the most out of my rig would be really helpful.

 

[cPanelLauren]

Generally speaking this should be helpful for troubleshooting any high load circumstance:

Tutorial - Troubleshooting high server loads on Linux servers

You also want to keep in mind some of the following:

1. Determine if the usage is legitimate or not, seeing the processlist (which is explained in the document I linked) should help with this.
2. See where your bottlenecks are - enabling the slow-query log may be beneficial for this MySQL :: MySQL 5.7 Reference Manual :: 5.4.5 The Slow Query Log
3. determine if other factors like IO wait or misbehaving scripts are to blame.

This can get complicated and before making any changes I'd suggest deferring to a qualified system or database administrator. If you don't have one you might find one here: System Administration Services | cPanel Forums.


Thanks!

 

[Tsuna]

Generally speaking this should be helpful for troubleshooting any high load circumstance:

Tutorial - Troubleshooting high server loads on Linux servers

You also want to keep in mind some of the following:

1. Determine if the usage is legitimate or not, seeing the processlist (which is explained in the document I linked) should help with this.
2. See where your bottlenecks are - enabling the slow-query log may be beneficial for this MySQL :: MySQL 5.7 Reference Manual :: 5.4.5 The Slow Query Log
3. determine if other factors like IO wait or misbehaving scripts are to blame.

This can get complicated and before making any changes I'd suggest deferring to a qualified system or database administrator. If you don't have one you might find one here: System Administration Services | cPanel Forums.


Thanks!

Thank you - will surely look into these.

The usage seem not legitimate - im not sure what some users ran but it was done by a few ips ( i noticed a lot of error pages being thrown error logs that leads to pages like

https://mysite/503.shtml

blocking the ip addresses seemed to kill the issue - but this seems to be a problem should it happen when I'm not around.