MySQL & High Server Load
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.
Here is the output of mysqltuner:
[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:
>> MySQLTuner 1.2.0 - Major Hayden
>> Bug reports, feature requests, and downloads at -
I have just changed the key_buffer_size to 768M and join_buffer_size to 8M according to mysqltuner recommendations. 0 -
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 0 -
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: [QUOTE] 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. 0 -
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 ? 0 -
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. .vB 0 -
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? [QUOTE] 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) 0 -
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? 0 -
This is the correct one. .vB 0 -
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 0 -
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. .vB 0 -
Your mysql settings are fine, the script (queries) and database structure needs optimizations Please generate slow log once again 0 -
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. 0 -
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 0 -
Hope I got it right: Here is how my.cnf looks right now: [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=2G0 -
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 0 -
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! 0 -
Q2: explain extended SELECT x.id, x.betgame_id, x.coupon_code, x.home_team, x.visit_team, x.code, x.start_timestamp, d.start_date, d.start_time, d.bet_field, d.from_value, d.to_value, d.status, d.status_game FROM kouponidb_prognosi_xml x LEFT JOIN kouponidb_prognosi_xml_diff d ON x.coupon_code = d.coupon_code AND x.betgame_id = d.betgame_id AND x.code = d.code WHERE x.start_date >= '2013-11-27' AND (d.status IS NOT NULL OR d.status_game IS NOT NULL OR d.start_date IS NOT NULL) ORDER BY d.id ASC Q3: explain extended select k.`short_tournament`, k.`home_team`, k.`visit_team`, k.`status` as st1, k.`start_date` as or_start_date, k.`start_time` as or_start_time, d.* from kouponi_xml_diff d inner join kouponi_xml k on k.betgame_id=d.betgame_id where d.`full_id`=589 and (k.`status`='active' || k.`status`='blocked') order by d.`rec_time` desc, d.`id` desc Q4: explain extended SELECT x.id, x.home_team, x.visit_team, x.code, x.start_timestamp, d.bet_field, d.from_value, d.to_value, d.status, d.status_game FROM kouponidb_prognosi_xml x LEFT JOIN kouponidb_prognosi_xml_diff d ON x.coupon_code = d.coupon_code AND x.betgame_id = d.betgame_id AND x.code = d.code WHERE x.start_date >= '2013-11-23' AND d.bet_field IS NOT NULL AND d.to_value IS NOT NULL GROUP BY d.bet_field, d.betgame_id, d.coupon_code, d.code ORDER BY id DESC See the thing is that your slow query log is geenrated from 2013-09-25 09:15:52 to 2013-11-28 13:22:57 when you posted your topic 11-25-2013, 04:53 AM so it would be good to generate slow log only from last 24h so remove current slow log /var/log/mysql/mysql-slow.log and run mysql flush logs; to recreate it again for 24 h 0 -
Ok I got it. Q2 + Q4: Q3 says: "ERROR 1146 (42S02): Table 'masterbe_app.kouponi_xml_diff' doesn't exist". I flushed slow.log as well. Q3 was in another database sorry.. mysql> explain extended select k.`short_tournament`, k.`home_team`, k.`visit_team`, k.`status` as st1, k.`start_date` as or_start_date, k.`start_time` as or_start_time, d.* from kouponi_xml_diff d inner join kouponi_xml k on k.betgame_id=d.betgame_id where d.`full_id`=589 and (k.`status`='active' || k.`status`='blocked') order by d.`rec_time` desc, d.`id` desc; +----+-------------+-------+------+---------------+------+---------+------+--------+----------+----------+ | id |select_type |table |type |possible_keys |key |key_len |ref |rows |filtered |Extra | +----+-------------+-------+------+---------------+------+---------+------+--------+----------+----------+ |1|SIMPLE|k |ALL| NULL |NULL |NULL |NULL |13286 |100.00 |Using where; Using temporary; Using filesort | |1 |SIMPLE|d |ALL |NULL| NULL |NULL |NULL |866238 |100.00 |Using where; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+--------+----------+----------+ 2 rows in set, 1 warning (0.00 sec)0 -
ok, please also run to show current index scheme show indexes from kouponidb_kino_wins_xls; show indexes from kouponidb_prognosi_xml; show indexes from kouponidb_prognosi_xml_diff; show indexes from kouponi_xml; show indexes from kouponi_xml_diff; show indexes from kouponidb_kino_delaysrepeats; please also describe which of those tables are MyiSAM or which InnoDB edit: Q3 is not using indexes at all, its very expensive, based on stats 95% of those queries are executing 57s here is simple way to improve it kouponi_xml_diff, add index, name betgame_id_full_id, and 2 fields there: betgame_id, full_id kouponi_xml, add index, name betgame_id_status, and 2 fields: betgame_id, status also add individual indexes on kouponi_xml_diff id field and rec_time field, so mysql optimizer can choose which one is better after than please rerun explain extended select k.`short_tournament`, k.`home_team`, k.`visit_team`, k.`status` as st1, k.`start_date` as or_start_date, k.`start_time` as or_start_time, d.* from kouponi_xml_diff d inner join kouponi_xml k on k.betgame_id=d.betgame_id where d.`full_id`=589 and (k.`status`='active' || k.`status`='blocked') order by d.`rec_time` desc, d.`id` desc; it would be good to do those changes after you generate 24h slow log, so we could compare the results based on slow log, if you dont want to wait please run select SQL_NO_CACHE k.`short_tournament`, k.`home_team`, k.`visit_team`, k.`status` as st1, k.`start_date` as or_start_date, k.`start_time` as or_start_time, d.* from kouponi_xml_diff d inner join kouponi_xml k on k.betgame_id=d.betgame_id where d.`full_id`=589 and (k.`status`='active' || k.`status`='blocked') order by d.`rec_time` desc, d.`id` desc; few times, during normal load from phpmyadmin, save its execution time on paper then add indexes and run the query again, saving execution times for comparision 0 -
Innodb tables: kouponidb_kino_wins_xls, kouponidb_prognosi_xml, kouponidb_prognosi_xml_diff, kouponi_xml, kouponi_xml_diff MyiSAM table: kouponidb_kino_delaysrepeats .vB 0 -
I am going to wait 24 hours as you suggested before adding those indexes. Is there anything else I can do until then in order to decrease cpu consumption by mysql? Did you get any useful info from Indexees.doc? 0 -
Based on index structure of the tables I can say that the person who made it, doesn't understand indexes at all :) When you have new slowlog ready we will discuss the rest 0 -
After 24 hours, here are slow.txt and the explain_extended output you requested: .vB .vB 0 -
Is there anything we can do? 0 -
Hi!Could you please assist me with the following problem? I have this query: SELECT x.id, x.home_team, x.visit_team, x.code, x.start_timestamp, d.rec_time, d.bet_field, d.from_value, d.to_value, d.status, d.status_game FROM kouponidb_prognosi_xml x LEFT JOIN kouponidb_prognosi_xml_diff d ON x.coupon_code = d.coupon_code AND x.betgame_id = d.betgame_id AND x.code = d.code WHERE x.start_date >= '2013-12-23' AND d.bet_field IS NOT NULL AND d.to_value IS NOT NULL AND (x.id, d.rec_time, d.bet_field) in (select x.id, max(d.rec_time) rec_time, d.bet_field FROM kouponidb_prognosi_xml x LEFT JOIN kouponidb_prognosi_xml_diff d ON x.coupon_code = d.coupon_code AND x.betgame_id = d.betgame_id AND x.code = d.code WHERE x.start_date >= '2013-12-23' AND d.bet_field IS NOT NULL AND d.to_value IS NOT NULL group by x.id, d.bet_field order by bet_field) GROUP BY d.bet_field, d.betgame_id, d.coupon_code, d.code ORDER BY id DESC;
Although I have index full_id_bet_game with fields full_id, betgame_id, coupon_code and code, the explain extended output shows that kouponidb_prognosi_xml_diff table has no indexees.+----+--------------------+-------+------+---------------------------+-------------------+---------+---------------------------+--------+ |id |select_type |table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------+---------------------------+-------------------+---------+---------------------------+--------+ | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 570965 | 100.00 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | x | ref | Index_2,betgame_id_status | betgame_id_status | 4 | masterbe_app.d.betgame_id | 1 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | d | ALL | NULL | NULL | NULL | NULL | 570965 | 100.00 | Using where; Using temporary; Using filesort | | 2 | DEPENDENT SUBQUERY | x | ref | Index_2,betgame_id_status | betgame_id_status | 4 | masterbe_app.d.betgame_id | 1 | 100.00 | Using where | +----+--------------------+-------+------+---------------------------+-------------------+---------+---------------------------+--------+ 4 rows in set, 1 warning (0.00 sec)0 -
Ok i removed full_id from the indexes and now i have Index_2,betgame_id_status as possible_keys but key field still remains null in explain extended output. 0 -
Sorry for late reply, but its very time consuming to help you witht this via forums, it would be much faster to get access to it and work directly I don't really have time to explain it all here, so I will refer to last post If you are a programmer of this, you don't much understand the idea of indexes and how they work in MySQL From the show indexes command you posted on table kouponidb_prognosi_xml_diff, you got there 2 indexes 1 - PRIMARY of id column 2 - full_id of 13 columns Please remove this 2 index, couse its useless like that kouponidb_prognosi_xml 1 - PRIMARY of id column 2 - Index_2 of coupon_code column 3 - full_id of 10 columns same, delete 3 index, its useless when its designed out of 10 columns but I see from explain that you got tehre in kouponidb_prognosi_xml also index betgame_id_status which Ive suggested before betgame_id_status from 2 fields: betgame_id, status, good since status is not used in WHERE NOR JOIN, its only using betgame_id from this index so you can have better index there for this particular query ON x.coupon_code = d.coupon_code AND x.betgame_id = d.betgame_id AND x.code = d.code for both tables you can create an index based of 3 elements: coupon_code, betgame_id, code so add there indexes to both tables: coupon_betgame_code that consists of 3 coupon_code, betgame_id, code in that order in kouponidb_prognosi_xml, you can even use 4 columns in this index, as last one add start_date to make use of WHERE x.start_date >= '2013-12-23' so it would be index coupon_betgame_code_startdate of 4 columns coupon_code, betgame_id, code, start_date in that order btw. if I understand the query logic correctly it can be written like this: without subqueries you should review it, since your subquery doesnt much make sense SELECT x.id, x.home_team, x.visit_team, x.code, x.start_timestamp, max(d.rec_time) rec_time, d.bet_field, d.from_value, d.to_value, d.status, d.status_game FROM kouponidb_prognosi_xml x LEFT JOIN kouponidb_prognosi_xml_diff d ON x.coupon_code = d.coupon_code AND x.betgame_id = d.betgame_id AND x.code = d.code WHERE x.start_date >= '2013-12-23' AND d.bet_field IS NOT NULL AND d.to_value IS NOT NULL GROUP BY d.bet_field, d.betgame_id, d.coupon_code, d.code ORDER BY id DESC;
you can run both and compare results and please also run explain extended on both and show us the result and before you should run your old query few times to measure execution time just add SQL_NO_CACHE after select like this SELECT x.id, to SELECT SQL_NO_CACHE x.id, so mysql wouldnt cache results during execution for speed check and my suggested query, then after adding indexes, run it again, to measure execution time just remember to put there SQL_NO_CACHE during tests0
Please sign in to leave a comment.
Comments
38 comments