steliosd

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
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
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
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

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
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

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
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

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
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.

slow_query.png
 

steliosd

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
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

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
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

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
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

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
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?
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
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
 
Last edited:

steliosd

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
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
 
Last edited:

steliosd

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
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.
 

steliosd

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
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

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
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

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
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
 
Last edited:

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
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

Active Member
Nov 25, 2013
27
0
1
cPanel Access Level
Root Administrator
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!
 
Last edited: