Database usage creating high CPU load and crashing

[supportech]

Hi guys,

The database usage in our shared server is always going up and the server crashes too often . Could you please advise out a solution to optimize the server , like doing something in the tweak settings, installing any software to limit the usage of database for any particular user, or editing the my.cnf file. Due to some of the users, the server load is going up every time . This has been a headache for us and our main job is to always monitor this server because of the load and try our best to keep the load low .

Please check the settings in the my.cnf file
-------------------------------------------------------------------------------------
[mysqld]
#set-variable = max_connections=500
safe-show-database
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
local-infile = 0
skip-locking
skip-bdb
#key_buffer = 128M
key_buffer = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
table_cache = 1024
record_buffer = 1M
thread_cache_size = 32
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
thread_concurrency = 8
max_connections = 500
max_user_connections = 25
interactive_timeout = 30
wait_timeout = 30
connect_timeout = 10

#newly added
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

#[safe_mysqld]
log_slow_queries = ON
#log=/var/log/mysql/mysqld.log
log_error=/var/log/mysql/mysqld.log
long_query_time=2

innodb_buffer_pool_size=2G
innodb_additional_mem_pool_size=20M
#innodb_log_file_size = 32M
innodb_log_buffer_size = 32M
========================================================================
Please check the usage also.
--------------------------------------------------------------------------------------------------------------
| Id | User | Host | db | Command | Time | State | Info |
+--------+----------------+-----------+-------------------+---------+------+-------+------------------+
| 198272 | user | localhost | user_whmcs | Sleep | 351 | |
=========================================================================

Hoping for a solution soon from you.
Thank you.

[LinuxTechie]

Hello,

You always need to tweak mysql in a shared server. The below given URL will guide you on this.

http://forums.cpanel.net/f189/need-h...on-189271.html

In the logs there is only one process and it is sleep state. Sleep state won't cause any high resource usage. I really hopes proper tweaking will resolve the issue.

[supportech]

Hello

Thank you very much for your reply.

Please have a look at the mysqltuner.pl script output that. Could you please provide some suggestions about the changes that we have to do in my.cnf.

>> MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.92-community
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 17677)
[--] Data in InnoDB tables: 2G (Tables: 1715)
[--] Data in MEMORY tables: 1M (Tables: 57)
[!!] Total fragmented tables: 1270

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 3h 35m 10s (8M q [87.094 qps], 217K conn, TX: 29B, RX: 1B)
[--] Reads / Writes: 78% / 22%
[--] Total buffers: 490.0M global + 6.2M per thread (500 max threads)
[OK] Maximum possible memory usage: 3.5G (60% of installed RAM)
[OK] Slow queries: 0% (546/8M)
[OK] Highest usage of available connections: 23% (118/500)
[OK] Key buffer size / total MyISAM indexes: 384.0M/686.5M
[OK] Key buffer hit rate: 99.3% (202M cached / 1M reads)
[OK] Query cache efficiency: 75.2% (5M cached / 7M selects)
[!!] Query cache prunes per day: 792869
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 595K sorts)
[!!] Joins performed without indexes: 4794
[!!] Temporary tables created on disk: 34% (151K on disk / 435K total)
[OK] Thread cache hit rate: 99% (264 created / 217K connections)
[!!] Table cache hit rate: 0% (1K open / 451K opened)
[OK] Open file limit used: 76% (1K/2K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[!!] InnoDB data size / buffer pool: 2.3G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 32M)
join_buffer_size (> 1.0M, or always use indexes with joins)
tmp_table_size (> 64M)
max_heap_table_size (> 64M)
table_cache (> 1024)
innodb_buffer_pool_size (>= 2G)


Thanks again!!

[cPanelTristan]

Hello,

Here is the revised /etc/my.cnf file with the settings removed that were commented out, those that don't exist or are the defaults removed and those that were under the entirely wrong section moved up to the right section. Since you are using MySQL 5.0, you also need to create the slow query log to get it logging, which I've added the entry into /etc/my.cnf but you'll have to then run the commands I provide after the /etc/my.cnf contents to get it working to log.

[mysqld]
local-infile = 0
skip-bdb
key_buffer_size = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
table_cache = 2048
record_buffer = 1M
thread_cache_size = 32
tmp_table_size = 80M
max_heap_table_size = 80M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
max_connections = 500
max_user_connections = 25
interactive_timeout = 30
wait_timeout = 30
connect_timeout = 10
long_query_time = 2
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 20M
innodb_log_buffer_size = 32M
log-slow-queries = /var/lib/mysql/slow.log

[myisamchk]
key_buffer_size=64M
sort_buffer_size=64M
read_buffer_size=16M
write_buffer_size=16M

cd /var/lib/mysql
touch slow.log
chmod 660 slow.log
chown mysql:mysql slow.log
/etc/init.d/mysql restart

Thanks.

[supportech]

Hello,

Thank you for the reply. I made the changes exactly as you suggested. But now getting the result like this.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6s (75 q [12.500 qps], 10 conn, TX: 205K, RX: 10K)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 2.5G global + 8.2M per thread (500 max threads)
[!!] Maximum possible memory usage: 6.6G (112% of installed RAM) ===> having issues here.
[OK] Slow queries: 0% (0/75)
[OK] Highest usage of available connections: 0% (2/500)
[!!] Key buffer size / total MyISAM indexes: 384.0M/687.7M
[!!] Key buffer hit rate: 72.6% (288 cached / 79 reads)
[!!] Query cache efficiency: 4.3% (2 cached / 46 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5 sorts)
[OK] Temporary tables created on disk: 20% (1 on disk / 5 total)
[OK] Thread cache hit rate: 80% (2 created / 10 connections)
[OK] Table cache hit rate: 86% (37 open / 43 opened)
[OK] Open file limit used: 2% (74/2K)
[OK] Table locks acquired immediately: 100% (71 immediate / 71 locks)
[!!] InnoDB data size / buffer pool: 2.3G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
key_buffer_size (> 687.7M)
query_cache_limit (> 1M, or use smaller result sets)
innodb_buffer_pool_size (>= 2G)


Could you please give some suggestions on this. Please have a look at this also:

# free -m
total used free shared buffers cached
Mem: 5955 5630 324 0 300 3447
-/+ buffers/cache: 1882 4072
Swap: 4094 208 3886

[cPanelTristan]

First of all, here is what I see:

MySQL started within last 24 hours - recommendations may be inaccurate

It appears that you did not wait 24 hours to run the report. Please let us know after you have. The way the report works, the variables cannot be changed and then immediately re-run mysqltuner.pl to get any type of accurate gauge. You do need to wait 24 hours to re-run it.

Next, the MySQL memory utilization that it can have is high, not your actual RAM usage. Per free -m, your system has your used memory as 1882 and free as 4072, so that isn't high at all. To properly read free -m, it is the buffers/cache field that matters as Linux typically loads memory into buffer or cache. In that cached memory, 4072 is free, which is pretty dang good.

Instead for the MySQL memory usage, it means to take out some of the variables that you've configured to reduce the memory footprint. I won't say what to change until you've waited the 24 hours to run the report, since there is not much point until then to determine what to revise.

[supportech]

Hello,

We have a Linux open VZ based VPS node which has 30 VPSs. The server has 16GB RAM. Recently we have seen that a single VPS's database usage causes very high load on the server. We had asked him to optimize his database, but it is still causing a very high load. The /etc/my.cnf file has been shown below :
============
[mysqld]
local-infile=0
skip-networking
============

Please do advise and provide tweaks and methods or scripts and crons to decrease this database usage and so other users do not get affected.

Thank you.

[cPanelTristan]

Is this an entirely new machine or the previous machine, because it previously had different variables. Also, did mysqltuner.pl get run on the machine after 24 hours time had passed to provide accurate results? If so, what did it suggest?

[supportech]

Hello Tristan,

Thanks for your help and replies. The server that I mentioned was a new one.

In the previous server where we made the changes in my.cnf, we were unable to sustain it as the load became extremely high and we had to reset them to previous configuration to make the load back to normal. But the databse issue came once again.

Here is the new my.cnf and mysqltuner.pl outputs.
============================================================
[mysqld]
#set-variable = max_connections=500
safe-show-database
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
local-infile = 0
skip-locking
skip-bdb
#key_buffer = 128M
key_buffer = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 3M
read_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
table_cache = 2048
record_buffer = 1M
thread_cache_size = 32
tmp_table_size = 70M
max_heap_table_size = 70M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 50M
query_cache_type = 1
thread_concurrency = 8
max_connections = 500
max_user_connections = 25
interactive_timeout = 30
wait_timeout = 30
connect_timeout = 10
#[safe_mysqld]
log_slow_queries = ON
#log=/var/log/mysql/mysqld.log
log_error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_queries_not_using_indexes = On
log_slow_queries=/var/log/mysql/mysqld.slow.log
long_query_time=2

#newly added
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

innodb_buffer_pool_size=2G
innodb_additional_mem_pool_size=20M
#innodb_log_file_size = 32M
innodb_log_buffer_size = 32M


============================================================
# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

>> MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.92-community-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 17957)
[--] Data in InnoDB tables: 2G (Tables: 1715)
[--] Data in MEMORY tables: 1M (Tables: 57)
[!!] Total fragmented tables: 1304

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 4m 21s (475K q [123.241 qps], 8K conn, TX: 1B, RX: 67M)
[--] Reads / Writes: 86% / 14%
[--] Total buffers: 514.0M global + 8.2M per thread (500 max threads)
[OK] Maximum possible memory usage: 4.5G (77% of installed RAM)
[OK] Slow queries: 0% (932/475K)
[OK] Highest usage of available connections: 18% (94/500)
[OK] Key buffer size / total MyISAM indexes: 384.0M/713.7M
[OK] Key buffer hit rate: 99.3% (5M cached / 39K reads)
[OK] Query cache efficiency: 71.0% (295K cached / 416K selects)
[!!] Query cache prunes per day: 524106
[OK] Sorts requiring temporary tables: 0% (135 temp sorts / 61K sorts)
[!!] Joins performed without indexes: 247
[!!] Temporary tables created on disk: 36% (7K on disk / 20K total)
[OK] Thread cache hit rate: 98% (94 created / 8K connections)
[OK] Table cache hit rate: 34% (2K open / 5K opened)
[OK] Open file limit used: 84% (3K/4K)
[OK] Table locks acquired immediately: 99% (156K immediate / 156K locks)
[!!] InnoDB data size / buffer pool: 2.3G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_size (> 50M)
join_buffer_size (> 3.0M, or always use indexes with joins)
tmp_table_size (> 70M)
max_heap_table_size (> 70M)
innodb_buffer_pool_size (>= 2G)

=================================================

I could see that there are issues with some databases from the slow query log, but not sure. Could you please advice how should I manage the database like optimize/repair based on the slow query log.

Thanks again!!!