MySQL & High Server Load

steliosd · Nov 25, 2013

Hi,
I have a hexacore dedicated server, with 64GB RAM, running CentOS 6.4.

Vendor: GenuineIntel
Name: Intel(R) Core(TM) i7-3930K CPU @ 3.20GHz
Speed: 1200.000 MHz
Cache: 12288 KB

The problem I am dealing with is that mysql consumes up to 600% of my cpu (server load up to 70!) every five minutes when the record are taking place.This situation last about 1-2 minutes before the server load comes back to normal at 4-5.
I need some help with the configuration of my.cnf file.

Code:

[mysqld]
max_connections=250
max_user_connections=150

query_cache_type=1
query_cache_size=256M
query_cache_limit=70M

tmp_table_size=8M
max_heap_table_size=8M

thread_cache_size=64
table_open_cache=1024

wait_timeout=300
interactive_timeout=300

innodb_file_per_table=1
innodb_buffer_pool_size=4G
innodb_log_file_size=512M

default-storage-engine=MyISAM
local-infile=0
max_allowed_packet=64M
log-slow-queries=/var/lib/mysql/slow.log
open_files_limit=2846

table_cache=4096
join_buffer_size=4M

key_buffer_size=2M

Here is the output of mysqltuner:

Code:

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
 >>  Run with '--help' for additional options and output filtering

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 348M (Tables: 319)
[--] Data in InnoDB tables: 615M (Tables: 389)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 8)
[!!] Total fragmented tables: 35

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 19h 41m 29s (185M q [562.959 qps], 2M conn, TX: 1909B, RX: 62B)
[--] Reads / Writes: 53% / 47%
[--] Total buffers: 4.3G global + 6.6M per thread (250 max threads)
[OK] Maximum possible memory usage: 5.9G (9% of installed RAM)
[OK] Slow queries: 0% (40K/185M)
[OK] Highest usage of available connections: 61% (154/250)
[!!] Key buffer size / total MyISAM indexes: 2.0M/530.2M
[!!] Key buffer hit rate: 92.5% (39M cached / 2M reads)
[OK] Query cache efficiency: 90.1% (139M cached / 155M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (13 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 6374
[OK] Temporary tables created on disk: 24% (11K on disk / 45K total)
[OK] Thread cache hit rate: 97% (56K created / 2M connections)
[OK] Table cache hit rate: 99% (2K open / 2K opened)
[OK] Open file limit used: 11% (943/8K)
[OK] Table locks acquired immediately: 99% (30M immediate / 30M locks)
[OK] InnoDB data size / buffer pool: 615.2M/4.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
Variables to adjust:
    key_buffer_size (> 530.2M)
    join_buffer_size (> 4.0M, or always use indexes with joins)

Thanks in advance

steliosd · Nov 25, 2013

I have just changed the key_buffer_size to 768M and join_buffer_size to 8M according to mysqltuner recommendations.

thinkbot · Nov 25, 2013

key_buffer_size increase will make huge difference if those queries run on MyISAM tables

join_buffer_size lower to 1M
8M there wont make any difference, it will only take more memory

You can also adjust
tmp_table_size=50M
max_heap_table_size=50M

query_cache_type=1
query_cache_size=75M
query_cache_limit=1M

long_query_time=0.1

the rest you can do is review slow queries, and optimize them
especially if you have some custom code, or CSM with third party plugins

steliosd · Nov 26, 2013

Thanks a lot thinkbot for your help!
I have made the changes you suggested and I will post the results tomorrow.
Are there any other optimizations I can do for innodb tables?I had to remove innodb_log_file_size=512M because it crashed my website.
In /var/lib/mysql/*.err file the only error that comes up repeatedly is this:

131126 11:21:30 InnoDB: ERROR: the age of the last checkpoint is 9436451,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

thinkbot · Nov 26, 2013

If you want to increase innodb_log_file_size, you need to remove old logfiles first like this

rm -rf /var/lib/mysql/ib_logfile*

and then restart mysql

but before for innodb you can set this:
innodb_log_file_size = 300M
innodb_log_buffer_size = 20M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_old_blocks_time = 1000

Your main used tables are Myisam or Innodb ?

steliosd · Nov 27, 2013

Thanks for the update!I have applied the changes and I am waiting.
My main used tables are Innodb.
Actually there is only one Innodb table that uses slow queries which take up to 25 sec to complete.

steliosd · Nov 27, 2013

On a second thought, there is the Innodb table I mentioned before with 4 million rows and a MyISAM table with 9,7 million rows.
Both of them are important (especially the first one) and data are being recorded to them every 5 minutes.
Shouldn't I set innodb_log_file_size = 600M according to this post?

mysql> pager grep sequence
PAGER set to 'grep sequence'
mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 696331245228
1 row in set (0.00 sec)

1 row in set (59.99 sec)

Log sequence number 696349449267
1 row in set (0.00 sec)

thinkbot · Nov 27, 2013

it wont make a difference much

Please add index on drawDate column in kouponidb_kino_wins_xls table
This will speed up the slow query a lot

you can also review the rest of slow queries like that:

cd /root/install
wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest /var/lib/mysql/slow.log > slow.txt

and copy slow.txt here

steliosd · Nov 28, 2013

The slow.txt is 5MB large and I can't post it here.Can you pm me your email so I can send it to you?
Here is my new mysqltuner output:

Code:

root@host [/]# ./mysqltuner.pl 

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
 >>  Run with '--help' for additional options and output filtering

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 349M (Tables: 319)
[--] Data in InnoDB tables: 574M (Tables: 389)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 8)
[!!] Total fragmented tables: 35

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 0h 2m 40s (49M q [568.306 qps], 766K conn, TX: 527B, RX: 23B)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 6.2G global + 3.6M per thread (250 max threads)
[OK] Maximum possible memory usage: 7.0G (11% of installed RAM)
[OK] Slow queries: 0% (83K/49M)
[OK] Highest usage of available connections: 60% (152/250)
[OK] Key buffer size / total MyISAM indexes: 2.0G/533.3M
[OK] Key buffer hit rate: 99.2% (6M cached / 52K reads)
[OK] Query cache efficiency: 88.2% (35M cached / 40M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 695K sorts)
[!!] Joins performed without indexes: 338
[!!] Temporary tables created on disk: 32% (3K on disk / 9K total)
[OK] Thread cache hit rate: 98% (11K created / 766K connections)
[OK] Table cache hit rate: 89% (1K open / 2K opened)
[OK] Open file limit used: 11% (941/8K)
[OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)
[OK] InnoDB data size / buffer pool: 574.5M/4.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    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:
    join_buffer_size (> 1.0M, or always use indexes with joins)
    tmp_table_size (> 50M)
    max_heap_table_size (> 50M)

I have added the index you suggested and I am going to increase tmp_table_size and max_heap_table_size to 100M.

Server load has decreased a lot but I still get a 27-30 sometimes.On the other hand, mysql process doesn't hit 700% of the cpu anymore.

steliosd · Nov 28, 2013

Forget about the last thing I wrote.It still hits 600% of cpu sometimes.
I don't understand how come when in top processes mysql reaches 600% of cpu, server load is at 5-7 and when server load is at 25-30, mysql reaches only 20% of cpu?

steliosd · Nov 28, 2013

This is the correct one.
View attachment slow.txt.zip

thinkbot · Nov 28, 2013

As you see here

# 1 0x656F8265D013DC88 8647502.2474 89.9% 349356 24.7527 2.04 SELECT kouponidb_kino_wins_xls
# 2 0xAA2D53B32C14D11C 540788.7260 5.6% 13809 39.1620 37.49 SELECT kouponidb_prognosi_xml kouponidb_prognosi_xml_diff
# 3 0x5F621334B41BA193 317440.8073 3.3% 9640 32.9295 5.68 SELECT kouponi_xml_diff kouponi_xml
# 6 0xAB1809319AF87956 13279.4833 0.1% 847 15.6783 9.25 SELECT kouponidb_prognosi_xml kouponidb_prognosi_xml_diff

almost 90% of mysql time from slow mysql queries is taken by 1 query, its executed 349356 times

It's SELECT * FROM kouponidb_kino_wins_xls WHERE drawDate = '2013-11-27'
from database masterbe_app

On median each execution returns over 10 rows, 95% 246 rows, just returning those rows to client takes some time, not to mention selecting them from median 4,06 M rows, 95% 16 M rows
Median time of execution of this query is 24s, and 95% is 118s

Once you added the index, selection would be much faster; difference should be huge, but sending rows to client still takes some time.

Please check in code what this query is supposed to do,
from my experience, it might be used to return number of rows, if so, use SELECT count(*) as total FROM instead, this wont need any rows to be returned, and query will execute immediately

If this query is used not to return count of rows, but some actuall rows, you can limit columns that needs to be returned
like. SELECT id, date FROM kouponidb_kino_wins_xls

to send only those columns that are actually used by the script

Second thing, check in code logic, if its really needed to run this queries this many times, maybe it doesn't change often, and it can be cached

Next queries are more complicated and explaining how to optimize them on forums would take too long time; the most CPU is coused by the 1 one

"Forget about the last thing I wrote.It still hits 600% of cpu sometimes.
I don't understand how come when in top processes mysql reaches 600% of cpu, server load is at 5-7 and when server load is at 25-30, mysql reaches only 20% of cpu?"

when cpu is used at 600% becouse of mysql, this in your case probably means tons of CPU intensive rows scanning
second, load 25-30, and mysql cpu only 20% - in your case its probably becouse too high I/O on HDD, you can put here result of top command when load goes to 25-30 re more

steliosd · Nov 29, 2013

The drawDate index made things a lot faster indeed.Does it matter that this array has 4 more columns as indexes?
I checked the query and I need all of the columns of the array kouponidb_kino_wins_xls.The query also need to run every 5 minutes so I don't think it can be cached.
Now the situation of the server is this.Every 5 minutes that information is available and the query is being executed, server load goes up to 30 max for about a minute.The rest of the time server load is below 10.
Is there any other option for the my.cnf file?

Here are the screenshots for top command.
View attachment Archive.zip

steliosd · Dec 11, 2013

Hi everybody,
my server load problem continues and I would appreciate some more help.Here are mysqltuner and my.cnf output:

Code:

root@host [/]# ./mysqltuner.pl 

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
 >>  Run with '--help' for additional options and output filtering

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 357M (Tables: 336)
[--] Data in InnoDB tables: 615M (Tables: 390)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 8)
[!!] Total fragmented tables: 42

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4d 19h 47m 1s (244M q [585.613 qps], 3M conn, TX: 2567B, RX: 116B)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 6.7G global + 4.6M per thread (250 max threads)
[OK] Maximum possible memory usage: 7.8G (12% of installed RAM)
[OK] Slow queries: 0% (174K/244M)
[OK] Highest usage of available connections: 60% (152/250)
[OK] Key buffer size / total MyISAM indexes: 2.0G/549.9M
[OK] Key buffer hit rate: 99.9% (40M cached / 55K reads)
[OK] Query cache efficiency: 88.3% (177M cached / 201M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (29 temp sorts / 3M sorts)
[!!] Joins performed without indexes: 6856
[OK] Temporary tables created on disk: 19% (41K on disk / 217K total)
[OK] Thread cache hit rate: 98% (48K created / 3M connections)
[!!] Table cache hit rate: 0% (2K open / 1M opened)
[OK] Open file limit used: 5% (996/16K)
[OK] Table locks acquired immediately: 99% (45M immediate / 45M locks)
[OK] InnoDB data size / buffer pool: 615.7M/4.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    join_buffer_size (> 2.0M, or always use indexes with joins)
    table_cache (> 8192)

Code:

[mysqld]
max_connections=250
max_user_connections=150

query_cache_type=1
query_cache_size=150M
query_cache_limit=1M

long_query_time=0.1

tmp_table_size=512M
max_heap_table_size=512M

thread_cache_size=64
table_open_cache=1024

wait_timeout=300
interactive_timeout=300

innodb_file_per_table=1
innodb_buffer_pool_size=4G

innodb_log_file_size = 600M
innodb_log_buffer_size = 30M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_old_blocks_time = 1000

default-storage-engine=MyISAM
local-infile=0
max_allowed_packet=64M
log-slow-queries=/var/lib/mysql/slow.log
open_files_limit=3800

table_cache=16384
join_buffer_size=2M

key_buffer_size=2G

Thanks in advance.

thinkbot · Dec 11, 2013

Your mysql settings are fine, the script (queries) and database structure needs optimizations

Please generate slow log once again

steliosd · Dec 16, 2013

Nothing has changed.
I noticed that table_cache_hit_rate is now 0% ( [!!] Table cache hit rate: 0% (2K open / 1M opened) )!
I increased table_cache to 16384 but it didn't help.
If the logged in users are about 60-70 everything works fine.But most time of the day logged in users are about 100 so the server load hits 40/12.

thinkbot · Dec 16, 2013

Did you optimize those queries that I wrote ? I mean indexes

Your queries used temporary tables, some of them on disk, and that doesn't scale, some used row by row scan or expensive filesort on big number of rows, thats all couses high I/O, and won't scale when visitors count grows

This is why I asked for new slow log to see the difference, you can also run

explain extended query
like
explain extended SELECT * FROM kouponidb_kino_wins_xls WHERE drawDate = '2013-11-27'

for top queries in slow log, and copy it here so I can review

steliosd · Dec 16, 2013

Hope I got it right:

Here is how my.cnf looks right now:

Code:

[mysqld]
max_connections=250
max_user_connections=150

query_cache_type=1
query_cache_size=150M
query_cache_limit=1M

long_query_time=0.1

tmp_table_size=512M
max_heap_table_size=512M

thread_cache_size=64
table_open_cache=4096

wait_timeout=300
interactive_timeout=300

innodb_file_per_table=1
innodb_buffer_pool_size=4G

innodb_log_file_size = 600M
innodb_log_buffer_size = 30M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_old_blocks_time = 1000

default-storage-engine=MyISAM
local-infile=0
max_allowed_packet=64M
log-slow-queries=/var/lib/mysql/slow.log
open_files_limit=3800

table_cache=4096
join_buffer_size=8M

key_buffer_size=2G

thinkbot · Dec 16, 2013

Yes, this one is correct, index added properly, query returns 16988 rows, ok
But what about the rest of the queries that Ive mentioned ? can you run explain extended on them too ?

btw. you can remove table_cache from my.cnf its the same as table_open_cache

steliosd · Dec 16, 2013

Ok I removed it.
I thought that only this one was the query causing so much trouble.In which other queries do you refer?

Mysql process is always at 700-1000% of cpu now!

Thread starter	Similar threads	Forum	Replies	Date
	MySQL Database crashed/CPU higher on server to down	Workarounds and Optimization	10	Oct 21, 2017
S	High MySQL and server load. Optimization Request	Workarounds and Optimization	5	Oct 14, 2015
F	Server Load High and Slowing my Website Down as Well as Killing my mySQL	Workarounds and Optimization	2	Jul 8, 2014
J	High Server Load - mysqld	Workarounds and Optimization	6	Dec 18, 2011
S	my server get high load from mysql server	Workarounds and Optimization	7	Aug 4, 2011

MySQL & High Server Load

Active Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Active Member

Well-Known Member

Active Member

Active Member

Active Member

Well-Known Member

Active Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member