Приветствую
Как безболезненно провести оптимизацию DB , если если там под 100 GB данных.
в основном таблицы rot_gallery_stats( и продолжают расти). Кнопка Optimize DB и остановка кронов не помогли, уходит в тайм аут. Может есть способ как то поэтапно или частями зачистить таблицы? База
250к и 20 слейвов. Mysql Tunning произведён.
Оптимизация DB
Re: Оптимизация DB
Приветствую !
1. остановить кроны
2. сделать руками в базе
OPTIMIZE TABLE ....
1. остановить кроны
2. сделать руками в базе
OPTIMIZE TABLE ....
Don't forget to run script update
Re: Оптимизация DB
Привет. Спрошу здесь.
Админы пишут: У нас на сервере тормозит mysql сервер. Тяжелые запросы однонотипны, вот основные два запроса, которые укладывают сервер, выполняются по 30 и более секунд. Так как на сервере несколько доменов, то эти запросы выполняются для каждого домена и соответсвенно mysql-ю грустно.Помоги соптимизировать эти запросы, или скажи, как можно их ускорить?
Вывод из show full processlist;
| 6431062 | domen1_tcms | localhost | domen1_tcms | Query | 49 | Sending data | SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi
.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gi.activation_date, gi.added_da
te, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats5 as gs JOIN rot_gallery_info as gi on gi.galler
y_id = gs.gallery_id
JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.best_thumb = 'yes' and gs.group_id IN (149) and gs.gallery_id NO
T IN ( SELECT gallery_id FROM rot_gallery_stats5 WHERE group_id IN (256,210,204,211,255,154,229,198,155,265,156,208,226,157,225,158,67,268,185,258,264,259,267,230,228,12
3,195,269,232,162,165,214,213,223,220,221,218,217,219,215,216,222,262,257)) ORDER BY tested_thumb DESC, gs.total_ctr DESC LIMIT 0, 10
explain: этого же запроса
+------+--------------+--------------------+--------+-----------------------------------------------------------------------+--------------------+---------+----------------------------
-----+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+--------------------+--------+-----------------------------------------------------------------------+--------------------+---------+---------------------------------+--------+----------------------------------------------+
| 1 | PRIMARY | gi | ref | PRIMARY,sponsor_id,gallery_type,gallery_status | gallery_type | 1 | const | 106251 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | gd | eq_ref | PRIMARY | PRIMARY | 4 | domen1_tcms.gi.gallery_id | 1 | |
| 1 | PRIMARY | gs | ref | unique_ids,best_thumb,gallery_id,thumb_id,tested_thumb_index,group_id | unique_ids | 6 | domen1_tcms.gi.gallery_id,c
onst | 2 | Using where |
| 1 | PRIMARY | gt | eq_ref | PRIMARY | PRIMARY | 4 | domen1_tcms.gs.thumb_id | 1 | |
| 2 | MATERIALIZED | rot_gallery_stats5 | range | unique_ids,best_thumb,gallery_id,tested_thumb_index,group_id | tested_thumb_index | 2 | NULL | 1887 | Using where; Using index |
+------+--------------+--------------------+--------+-----------------------------------------------------------------------+--------------------+---------+----------------------------
-----+--------+----------------------------------------------+
| 6479202 | domen2_tcms | localhost | domen2_tcms | Query | 23 | Sending data | SELECT SQL_CALC_FOUND_ROWS gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gi.activation_date, gi.added_da
te, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats2 as gs FORCE INDEX (activation_date) JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id
JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.best_thumb = 'yes' and gs.group_id IN (32) and gs.gallery_id NOT IN ( SELECT gallery_id FROM rot_gallery_stats2 WHERE group_id IN (157,186,166,180,107,194,110)) ORDER BY gs.activation_date DESC LIMIT 200, 100
explain: этого же запроса
+------+--------------+--------------------+--------+--------------------------------------------------------------+----------+---------+---------------------------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+--------------------+--------+--------------------------------------------------------------+----------+---------+---------------------------+----------+-----------------------------+
| 1 | PRIMARY | gs | ALL | NULL | NULL | NULL | NULL | 13721524 | Using where; Using filesort |
| 1 | PRIMARY | gd | eq_ref | PRIMARY | PRIMARY | 4 | domen1_tcms.gs.gallery_id | 1 | |
| 1 | PRIMARY | gt | eq_ref | PRIMARY | PRIMARY | 4 | domen1_tcms.gs.thumb_id | 1 | |
| 1 | PRIMARY | gi | eq_ref | PRIMARY,sponsor_id,gallery_type,gallery_status | PRIMARY | 4 | domen1_tcms.gs.gallery_id | 1 | Using where |
| 2 | MATERIALIZED | rot_gallery_stats2 | range | unique_ids,best_thumb,gallery_id,tested_thumb_index,group_id | group_id | 2 | NULL | 566444 | Using where; Using index |
+------+--------------+--------------------+--------+--------------------------------------------------------------+----------+---------+---------------------------+----------+-----------------------------+
Админы пишут: У нас на сервере тормозит mysql сервер. Тяжелые запросы однонотипны, вот основные два запроса, которые укладывают сервер, выполняются по 30 и более секунд. Так как на сервере несколько доменов, то эти запросы выполняются для каждого домена и соответсвенно mysql-ю грустно.Помоги соптимизировать эти запросы, или скажи, как можно их ускорить?
Вывод из show full processlist;
| 6431062 | domen1_tcms | localhost | domen1_tcms | Query | 49 | Sending data | SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi
.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gi.activation_date, gi.added_da
te, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats5 as gs JOIN rot_gallery_info as gi on gi.galler
y_id = gs.gallery_id
JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.best_thumb = 'yes' and gs.group_id IN (149) and gs.gallery_id NO
T IN ( SELECT gallery_id FROM rot_gallery_stats5 WHERE group_id IN (256,210,204,211,255,154,229,198,155,265,156,208,226,157,225,158,67,268,185,258,264,259,267,230,228,12
3,195,269,232,162,165,214,213,223,220,221,218,217,219,215,216,222,262,257)) ORDER BY tested_thumb DESC, gs.total_ctr DESC LIMIT 0, 10
explain: этого же запроса
+------+--------------+--------------------+--------+-----------------------------------------------------------------------+--------------------+---------+----------------------------
-----+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+--------------------+--------+-----------------------------------------------------------------------+--------------------+---------+---------------------------------+--------+----------------------------------------------+
| 1 | PRIMARY | gi | ref | PRIMARY,sponsor_id,gallery_type,gallery_status | gallery_type | 1 | const | 106251 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | gd | eq_ref | PRIMARY | PRIMARY | 4 | domen1_tcms.gi.gallery_id | 1 | |
| 1 | PRIMARY | gs | ref | unique_ids,best_thumb,gallery_id,thumb_id,tested_thumb_index,group_id | unique_ids | 6 | domen1_tcms.gi.gallery_id,c
onst | 2 | Using where |
| 1 | PRIMARY | gt | eq_ref | PRIMARY | PRIMARY | 4 | domen1_tcms.gs.thumb_id | 1 | |
| 2 | MATERIALIZED | rot_gallery_stats5 | range | unique_ids,best_thumb,gallery_id,tested_thumb_index,group_id | tested_thumb_index | 2 | NULL | 1887 | Using where; Using index |
+------+--------------+--------------------+--------+-----------------------------------------------------------------------+--------------------+---------+----------------------------
-----+--------+----------------------------------------------+
| 6479202 | domen2_tcms | localhost | domen2_tcms | Query | 23 | Sending data | SELECT SQL_CALC_FOUND_ROWS gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gi.activation_date, gi.added_da
te, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats2 as gs FORCE INDEX (activation_date) JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id
JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.best_thumb = 'yes' and gs.group_id IN (32) and gs.gallery_id NOT IN ( SELECT gallery_id FROM rot_gallery_stats2 WHERE group_id IN (157,186,166,180,107,194,110)) ORDER BY gs.activation_date DESC LIMIT 200, 100
explain: этого же запроса
+------+--------------+--------------------+--------+--------------------------------------------------------------+----------+---------+---------------------------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+--------------------+--------+--------------------------------------------------------------+----------+---------+---------------------------+----------+-----------------------------+
| 1 | PRIMARY | gs | ALL | NULL | NULL | NULL | NULL | 13721524 | Using where; Using filesort |
| 1 | PRIMARY | gd | eq_ref | PRIMARY | PRIMARY | 4 | domen1_tcms.gs.gallery_id | 1 | |
| 1 | PRIMARY | gt | eq_ref | PRIMARY | PRIMARY | 4 | domen1_tcms.gs.thumb_id | 1 | |
| 1 | PRIMARY | gi | eq_ref | PRIMARY,sponsor_id,gallery_type,gallery_status | PRIMARY | 4 | domen1_tcms.gs.gallery_id | 1 | Using where |
| 2 | MATERIALIZED | rot_gallery_stats2 | range | unique_ids,best_thumb,gallery_id,tested_thumb_index,group_id | group_id | 2 | NULL | 566444 | Using where; Using index |
+------+--------------+--------------------+--------+--------------------------------------------------------------+----------+---------+---------------------------+----------+-----------------------------+
Re: Оптимизация DB
256,210,204,211,255,154,229,198,155,265,156,208,226,157,225,158,67,268,185,258,264,259,267,230,228,12
3,195,269,232,162,165,214,213,223,220,221,218,217,219,215,216,222,262,257))
это как я понимаю список выключеннх групп, а почему их так много?
3,195,269,232,162,165,214,213,223,220,221,218,217,219,215,216,222,262,257))
это как я понимаю список выключеннх групп, а почему их так много?
Don't forget to run script update
Re: Оптимизация DB
просто они не нужны. тем более в них практически нет тумб.
могу удалить. если в них проблема
могу удалить. если в них проблема
Re: Оптимизация DB
Удалили лишние выключенные группы.
Некоторые запросы стали отрабатывать быстрее.
Но вот такого типа запросы все еще тормозят
| 6771341 | domen1_tcms | 194.187.97.187:40123 | domen1_tcms | Query | 40 | Sending data | SELECT SQL_CALC_FOUND_ROWS gs.gallery_id, gs.thumb_id, gi.sponsor_id
, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gi.activation_date, gi.added_date, gi.dura
tion, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats27 as gs FORCE INDEX (activation_date) JOIN rot_gallery_info as gi on gi.
gallery_id = gs.gallery_id
JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.best_thumb = 'yes' and gs.group_id IN (200) and gs.gallery_id NOT IN ( SELECT g
allery_id FROM rot_gallery_stats27 WHERE group_id IN (226,67,264,267,269,249)) AND gs.gallery_id NOT IN ('187635','223857','222433','244003','110074','116','50800','233402','224569','
116402','193011','220009','183462','186610','232738','27390','7641','164300','119235','241976','311242','36307','241292','164696','300270','183409','79249','190483','243879','63162')
Некоторые запросы стали отрабатывать быстрее.
Но вот такого типа запросы все еще тормозят
| 6771341 | domen1_tcms | 194.187.97.187:40123 | domen1_tcms | Query | 40 | Sending data | SELECT SQL_CALC_FOUND_ROWS gs.gallery_id, gs.thumb_id, gi.sponsor_id
, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gi.activation_date, gi.added_date, gi.dura
tion, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats27 as gs FORCE INDEX (activation_date) JOIN rot_gallery_info as gi on gi.
gallery_id = gs.gallery_id
JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.best_thumb = 'yes' and gs.group_id IN (200) and gs.gallery_id NOT IN ( SELECT g
allery_id FROM rot_gallery_stats27 WHERE group_id IN (226,67,264,267,269,249)) AND gs.gallery_id NOT IN ('187635','223857','222433','244003','110074','116','50800','233402','224569','
116402','193011','220009','183462','186610','232738','27390','7641','164300','119235','241976','311242','36307','241292','164696','300270','183409','79249','190483','243879','63162')