High MySQL usage - how to troubleshoot?

Tsuna

• June 22, 2019 15:59

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

  • June 25, 2019 16:28

  Generally speaking this should be helpful for troubleshooting any high load circumstance: 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 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: . Thanks!

  0
• 

  Tsuna

  • June 25, 2019 17:40

  Generally speaking this should be helpful for troubleshooting any high load circumstance: 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:

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

  0

Please sign in to leave a comment.