High MySQL and server load. Optimization Request

seemans

Member
Sep 15, 2010
9
0
51
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?

Code:
---------------

# 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)
 
Last edited by a moderator:

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,913
2,202
363
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

Member
Sep 15, 2010
9
0
51
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.


Code:
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
 
Last edited by a moderator: