Performance issue | MySQL Consumes more than 80%+ memory and 2000%+ CPU

Question

Everyday during the peak time my server getting slow or down. Our hosting provider insisting us to upgrade the server but I think some performance tuning issue is there.

Adding the process information, server configuration and my.cnf parameters below.

Process Information

 PID  USER       PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                  
 60848 mysql     20   0 34.8g  23g 6416 S 2196.2 82.1  16027:29 mysqld 

Dedicated Server Configuration

Size:   'Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz', 2600MHz, 6 Core, Sockets: 2    
Image:  CentOS 6 64-bit with cPanel Fully-managed   
CPU:    Intel Dual Xeon E5-2620 v3
Speed:  2600MHz 
RAM:    32067MB 
CPUs:   2 Physical CPUs
Cores:  12 
Total Cores RAID:   Level 10
Disks:  4
Size:   917GB   
Type:   SSD 

MySQL Configuration

[mysqld]
slow_query_log = 1
#long_query_time = 2
long_query_time = 2
slow_query_log_file = /var/lib/mysql/vps-slow.log
performance-schema=0
max_connections = 250
max_allowed_packet=128M
#skip-external-locking
key_buffer_size = 256M
open_files_limit = 50000
table_open_cache = 15000
sort_buffer_size = 6M
join_buffer_size = 6M
read_buffer_size = 1M
read_rnd_buffer_size = 15M
myisam_sort_buffer_size = 64M
query_cache_type=1
query_cache_limit=2M
query_cache_size=128M
max_heap_table_size = 96M
tmp_table_size = 96M
#low_priority_updates=1

thread_cache_size = 32
wait_timeout = 300
connect_timeout=15
interactive_timeout = 800

innodb_buffer_pool_size=4096M
innodb_buffer_pool_instances = 4

group_concat_max_len=1000000

default-storage-engine=MyISAM
innodb_file_per_table=1

[mysqldump]
quick

Please help me if there is anything wrong with the configuration ?

Or

Do I really need to upgrade the server ?

PS: Hosted web application in the same server. Also WHM enabled for easy management.

Answer 1

It would be helpful to have SHOW CREATE TABLE for the first few tables in the slowlog. It would also be helpful if you always tagged each column with the alias (or table) that it belongs to. Meanwhile, I will make some guesses.

Indexes (or PRIMARY KEYs) needed:

user_registration:  (usr_username)
user_type:  (usr_type_id)
company_master:  (comp_id)
company_master:  (status, comp_id)

If my suggestions don't help enough, then provide EXPLAIN SELECT ... for the worst few queries.

Because of the implementation of information_schema (prior to version 8.0), SELECT data_free... cannot be sped up. But why are you doing the query? There may be a workaround.

Including a test on status or del_status (etc) sometimes makes optimization more difficult. There is not a one-size-fits-all remedy.

A tip about SELECT U.*, ...: If * includes some TEXT or BLOB columns that you don't really need, there may be a performance improvement by spelling out only the columns you do need.

Those indexes should help the first few queries. Since they comprise 99.9%+ of the activity, you should see the CPU go down significantly.

If you need another go at this, change to long_query_time = 1, clear the slowlog, and run for another day.