1

I have two machine have two master-master mysql replicate, only problem i have that i got massive queries like "sorting result" queries and i don't know how to fix these queries . mysql process cpu usage 2000% from server and i got load over 100

This is My mysql Configuration

[mysqld]
skip-name-resolve
server-id=2
log-bin=/var/log/mysql/mysql-bin.log
relay-log=/var/log/mysql/relay-bin.log
binlog_do_db           = user_ooo
log-slave-updates
slave_net_timeout = 60
slave-skip-errors=1062
sync_binlog = 1
expire_logs_days=1

#auto_increment_increment = 10
#auto_increment_offset = 2

default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=60480



max_connections = 512
max_user_connections=150

query_cache_type=1
query_cache_size=128M
query_cache_limit=65M

table_open_cache=10000
table_definition_cache=10000
thread_cache_size = 32

key_buffer_size = 128M
read_buffer_size = 8M  
join_buffer_size = 256k

Mysql full process list

http://pastebin.centos.org/46701/

mysql> show create table ooo_video_serie;
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ooo_video_serie | CREATE TABLE `ooo_video_serie` (
  `id_v` int(11) NOT NULL AUTO_INCREMENT,
  `titre` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  `code_c` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `type_v` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `id_episode` int(11) NOT NULL,
  `urls` varchar(1000) COLLATE utf8_unicode_ci NOT NULL,
  `date` int(11) NOT NULL,
  `active` int(11) NOT NULL,
  `langue` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `qualite` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `lorde` int(11) NOT NULL,
  `id_user` int(11) NOT NULL,
  `type_de_video` int(11) NOT NULL,
  PRIMARY KEY (`id_v`)
) ENGINE=MyISAM AUTO_INCREMENT=3287677 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM ooo_video_serie WHERE id_episode='96652' AND active=1 ORDER BY langue,lorde ASC;
+----+-------------+-----------------+------+---------------+------+---------+------+---------+-----------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows    | Extra                       |
+----+-------------+-----------------+------+---------------+------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | ooo_video_serie | ALL  | NULL          | NULL | NULL    | NULL | 1162976 | Using where; Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+------+---------+-----------------------------+
1 row in set (3.76 sec)

slow queries 

SET timestamp=1465755635;
SELECT titre FROM ooo_serie WHERE titre LIKE '%l\\\'%' and active=1 LIMIT 7 UNION SELECT titre FROM ooo_film WHERE titre LIKE '%l\\\'%' and active=1 LIMIT 7 UNION SELECT titre FROM ooo_anime WHERE titre LIKE '%l\\\'%' and active=1 LIMIT 6;
# User@Host: user_ooo[user_ooo] @ localhost []
# Query_time: 0.027900  Lock_time: 0.000182 Rows_sent: 6  Rows_examined: 8766
SET timestamp=1465755635;
SELECT titre FROM ooo_serie WHERE titre LIKE '%harry po%' and active=1 LIMIT 7 UNION SELECT titre FROM ooo_film WHERE titre LIKE '%harry po%' and active=1 LIMIT 7 UNION SELECT titre FROM ooo_anime WHERE titre LIKE '%harry po%' and active=1 LIMIT 6;
# User@Host: user_ooo[user_ooo] @ localhost []
# Query_time: 0.098434  Lock_time: 0.000189 Rows_sent: 1  Rows_examined: 22913
SET timestamp=1465755635;
SELECT titre, poster, urle, post_type FROM ooo_serie WHERE titre LIKE '%Pretty little liars%' and active=1 UNION SELECT titre, poster, urle, post_type FROM ooo_anime WHERE titre LIKE '%Pretty little liars%' and active=1 UNION SELECT titre, poster, urle, post_type FROM ooo_film WHERE (titre like '%Pretty little liars%' or clen_title like '%Pretty little liars%' or tags like '%Pretty little liars%' or tags2 like '%Pretty little liars%' or autre_titre like '%Pretty little liars%') and active=1 LIMIT 28;
# User@Host: user_ooo[user_ooo] @ localhost []
# Query_time: 5.468921  Lock_time: 0.392831 Rows_sent: 20  Rows_examined: 1162985
SET timestamp=1465755635;
SELECT * FROM  ooo_video_serie WHERE id_episode='7870' AND active=1 ORDER BY langue,lorde ASC;
# User@Host: user_ooo[user_ooo] @ localhost []
# Query_time: 9.150496  Lock_time: 4.037870 Rows_sent: 23  Rows_examined: 1162988
SET timestamp=1465755635;
SELECT * FROM  ooo_video_serie WHERE id_episode='7472' AND active=1 ORDER BY langue,lorde ASC;
# User@Host: user_ooo[user_ooo] @ localhost []
# Query_time: 0.075659  Lock_time: 0.000226 Rows_sent: 6  Rows_examined: 22918
SET timestamp=1465755635;
SELECT titre FROM ooo_serie WHERE titre LIKE '%game of%' and active=1 LIMIT 7 UNION SELECT titre FROM ooo_film WHERE titre LIKE '%game of%' and active=1 LIMIT 7 UNION SELECT titre FROM ooo_anime WHERE titre LIKE '%game of%' and active=1 LIMIT 6;
# User@Host: user_ooo[user_ooo] @ localhost []
# Query_time: 9.369396  Lock_time: 4.246392 Rows_sent: 25  Rows_examined: 1162990
SET timestamp=1465755635;
SELECT * FROM  ooo_video_serie WHERE id_episode='6240' AND active=1 ORDER BY langue,lorde ASC;
# User@Host: user_ooo[user_ooo] @ localhost []
# Query_time: 5.771850  Lock_time: 0.682373 Rows_sent: 22  Rows_examined: 1162987
SET timestamp=1465755635;
SELECT * FROM  ooo_video_serie WHERE id_episode='2016' AND active=1 ORDER BY langue,lorde ASC;
# User@Host: user_ooo[user_ooo] @ localhost []
# Query_time: 6.103035  Lock_time: 1.005098 Rows_sent: 23  Rows_examined: 1162988
SET timestamp=1465755635;
SELECT * FROM  ooo_video_serie WHERE id_episode='100774' AND active=1 ORDER BY langue,lorde ASC;
# User@Host: user_ooo[user_ooo] @ localhost []
# Query_time: 0.017908  Lock_time: 0.000095 Rows_sent: 1  Rows_examined: 36421
SET timestamp=1465755635;
SELECT count(id) as cpt,id_anime FROM ooo_ep_anime WHERE slug like 'one-piece' and slug2 like 'episode-336-56374';
# User@Host: user_ooo[user_ooo] @ localhost []
# Query_time: 0.014134  Lock_time: 0.000080 Rows_sent: 6  Rows_examined: 2804
SET timestamp=1465755635;
SELECT titre FROM ooo_serie WHERE titre LIKE '%the f%' and active=1 LIMIT 7 UNION SELECT titre FROM ooo_film WHERE titre LIKE '%the f%' and active=1 LIMIT 7 UNION SELECT titre FROM ooo_anime WHERE titre LIKE '%the f%' and active=1 LIMIT 6;
# User@Host: user_ooo[user_ooo] @ localhost []
# Query_time: 0.078186  Lock_time: 0.000139 Rows_sent: 1  Rows_examined: 22913
SET timestamp=1465755635;

If there any other information needs for more diagnosis this problem i can update my question with it

Improve this question
3
  • Enable slow query log and identify the the specific query which utilizing high CPU power.
    – Rathish Kumar B
    Commented Jun 10, 2016 at 11:34
  • Your processlist shows most sorts on ooo_video_serie, please run show create table ooo_video_serie; and add the results to your question. Then run EXPLAIN SELECT * FROM ooo_video_serie WHERE id_episode='96652' AND active=1 ORDER BY langue,lorde ASC; and add the output too.
    – jkavalik
    Commented Jun 10, 2016 at 20:42
  • @jkavalik i update my question with your needs
    – iLinux85
    Commented Jun 12, 2016 at 19:23

2 Answers 2

Reset to default
1

From the EXPLAIN and table structure it can be seen that you are missing some indexes so some queries need to check million rows to return ~20.

SELECT * FROM ooo_video_serie WHERE id_episode='96652' AND active=1 ORDER BY langue,lorde ASC;

this would benefit from index on (id_episode, active) or even (id_episode, active, langue, lorde) but if expected number of rows to return is around 20 then the latter index might be unnecessary.

ALTER TABLE ooo_video_serie ADD INDEX episode_active(id_episode, active);

It might take some time to execute, maybe even half an hour, so plan accordingly and try on test first.

After fixing this one, others might crop up which were overshadowed until now. Analyze the slow log periodically (pt-query-digest is a good help) and fix the worst queries. Often just adding the right index is enough but sometime some rewrite or at least slight modification of the query might be needed.

Other notes:

  • max_connections = 512 - too much, if you get so many clients with slower queries, the server is overloaded and slows to crawl for everyone
  • query_cache_size=128M - too big imho, slow to invalidate; set it to 32MB or even better disable it and just optimize your queries
  • key_buffer_size = 128M - depending on whats the DB size and your available RAM, you might want to make that a bit bigger to better uzilize the indexes - say 10-20% of your available RAM?
  • what about using InnoDB instead of MyISAM?
  • SELECT * is evil, try to list only the columns you really need
Improve this answer
0

How much RAM? key_buffer_size, if using only MyISAM, should be about 20% of available RAM.

Switch to InnoDB and change some of the settings.

Are you never using AUTO_INCREMENT? You seem to have commented out auto_increment* settings.

slave-skip-errors=1062 -- Do you understand how fast your Masters can be corrupted?

table_open_cache=10000 table_definition_cache=10000 -- Either these are too high, or you have too many tables.

Learn about indexing

WHERE id_episode='96652' AND active=1 ORDER BY langue,lorde ASC;

begs for INDEX(id_episode, active, lang, lorde) -- that query will run much faster.

LIKE '%harry po%' -- leading wild cards can't use indexes. Look into using FULLTEXT.

SELECT titre FROM ooo_serie WHERE titre LIKE '%harry po%' and active=1 LIMIT 7

would partially benefit from INDEX(active, titre) (in either order) because it is 'covering'.

FULLTEXT is likely to be your best bet.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.