High MySQL and server load. Optimization Request

[seemans]

My server shows extremely high load with high CPU usage of mysqld, although there is only one site on the server, the server has Good CPU, great disk(SSD)and enough memory, I think.

To reduce the server load and cpu usage of mysqld, what is the best way to do that?

1) Optimize my.cnf values ? and how? Please advise.

2) Change the current CPU(single processer, 4 cores, 8 HT) to Dual processer with more cores like Intel Xeon E5-2620 2.0GHz (dual processors, 12 cores, 24 HT)
Do you think this will work?

---------------

# Only 1 site on the server.
# Database: MariaDB 10.0
# CPU: E3-1276v3 3.60 GHz  (4 cored, 8HT)
# Memory: 16GB
# Disk: 240GB Intel S3500 Enterprise SSD

# The output of TOP command.  The CPU usage of mysqld is over 700%.

load average: 44.39, 39.49, 32.58

Tasks: 451 total,  3 running, 247 sleeping,  0 stopped,  1 zombie

Cpu0  :  90.6%us,  1.0%sy,  0.0%ni, 89.4%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu1  :  90.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu2  : 100.0%us,  0.3%sy,  0.0%ni, 99.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu3  :  99.3%us,  0.0%sy,  0.0%ni, 99.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu4  :  84.3%us,  1.3%sy,  0.0%ni, 84.4%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu5  : 100.0%us,  0.0%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu6  :  97.0%us,  0.7%sy,  0.0%ni, 92.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu7  : 100.0%us,  0.3%sy,  0.3%ni, 99.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st

Mem:  16290984k total, 14994580k used,  1296404k free,  1480720k buffers
Swap:  2097148k total,  123264k used,  1973884k free,  8578072k cached

#USER:mysql  #%CPU: 731.4  (very high!!!)



# My current my.cnf  :  /etc/my.cnf  (default cpanel server)

[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000

# mysqltuner result.

./mysqltuner.pl
>>  MySQLTuner 1.6.1 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at MySQLTuner-perl by major
>>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.0.21-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
[--] Data in MyISAM tables: 179M (Tables: 131)
[--] Data in InnoDB tables: 480K (Tables: 14)
[!!] Total fragmented tables: 16

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[--] There is 605 basic passwords in the list.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 33d 9h 57m 47s (259M q [89.882 qps], 2M conn, TX: 1045B, RX: 47B)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is disabled
[--] Total buffers: 416.0M global + 2.8M per thread (151 max threads)
[OK] Maximum reached memory usage: 838.8M (5.27% of installed RAM)
[OK] Maximum possible memory usage: 836.0M (5.25% of installed RAM)
[OK] Slow queries: 0% (90K/259M)
[!!] Highest connection usage: 100%  (152/151)
[OK] Aborted connections: 0.26%  (5323/2030757)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 2% (248K temp sorts / 8M sorts)
[!!] Joins performed without indexes: 2545695
[OK] Temporary tables created on disk: 25% (1M on disk / 4M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (400 open / 150K opened)
[OK] Open file limit used: 4% (493/10K)
[OK] Table locks acquired immediately: 99% (358M immediate / 359M locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 24.1% (32M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/31.5M
[OK] Read Key buffer hit rate: 100.0% (339B cached / 757K reads)
[!!] Write Key buffer hit rate: 54.3% (7M cached / 3M writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/480.0K
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[!!] InnoDB Used buffer: 3.86% (316 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 99.26% (42164 hits/ 42480 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 11 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
  Run OPTIMIZE TABLE to defragment tables for better performance
  Reduce or eliminate persistent connections to reduce connection usage
  Adjust your join queries to always utilize indexes
  Set thread_cache_size to 4 as a starting value
  Increase table_open_cache gradually to avoid file descriptor limits
  Read this before increasing table_open_cache over 64: table_cache negative scalability - MySQL Performance Blog
  Beware that open_files_limit (10000) variable
  should be greater than table_open_cache ( 400)
Variables to adjust:
  max_connections (> 151)
  wait_timeout (< 28800)
  interactive_timeout (< 28800)
  query_cache_size (>= 8M)
  join_buffer_size (> 128.0K, or always use indexes with joins)
  thread_cache_size (start at 4)
  table_open_cache (> 400)
  innodb_buffer_pool_instances (=1)

 

[cPanelMichael]

Hello

You may also want to run a command such as "mysqladmin processlist" to see which databases are using up the most CPU when the server load is high.

Thank you.

 

[seemans]

Hello

You may also want to run a command such as "mysqladmin processlist" to see which databases are using up the most CPU when the server load is high.

Thank you.

Hi, There is one site on the server , and only one database there.

 

[cPanelMichael]

The following thread may help if you want to ensure it's MySQL resulting in the high load:

Troubleshooting high server loads on Linux servers

Otherwise, you may want to consult with a qualified system administrator if you don't receive additional user-feedback to this thread.

Thank you.

 

[seemans]

I found lots of `Waiting for table level lock` by checking `SHOW FULL PROCESSLIST;

and this causes stop responding new mysql query, and the number of processes of the server goes spike.

Changing any values on my.cnf in any way didn`t solve this. It still comes up and keep there for a while suddenly again and again and kill my server.

Please advise what to do.. how to prevent this lock status.. I don`t know what causes this.

MariaDB [(none)]> SHOW FULL PROCESSLIST;

Waiting for table level lock | update products set products_ordered = products_ordered + 1 where products_id = '11174'

Waiting for table level lock | select distinct p.products_id, pd.products_name, p.products_image from products p, products_description pd left join products_details pdt on pd.products_id = pdt.products_id where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '4' order by p.products_ordered desc, pd.products_name limit 100

Waiting for table level lock | select p.products_id, pd.products_name, p.products_model, p.products_price, p.products_weight, p.products_tax_class_id from products p, products_description pd where p.products_id='17406' and pd.products_id = p.products_id and pd.language_id = '4'

Waiting for table level lock | select p.products_id, pd.products_name, p.products_model, p.products_price, p.products_weight, p.products_tax_class_id from products p, products_description pd where p.products_id='20115' and pd.products_id = p.products_id and pd.language_id = '4'

Waiting for table level lock | select distinct p.products_id, pd.products_name, p.products_image from products p, products_description pd left join products_details pdt on pd.products_id = pdt.products_id where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '4' order by p.products_ordered desc, pd.products_name limit 100


# current value of :  /etc/my.cnf  (some lines added to default cpanel setting)

[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
max_connections=300
thread_cache_size=256
tmp_table_size=256M
max_heap_table_size=256M
table_open_cache=2500

 

[cPanelMichael]

You may want to try repairing and optimizing the database to see if that makes a difference:

mysqlcheck

Thank you.