MySQL 100% ЦП + медленный запрос - неправильное использование индекса

Я использую базу данных RDS от Amazon с некоторыми очень большими таблицами, и вчера я начал сталкиваться со 100% загрузкой ЦП на сервере и кучей медленных журналов запросов, которых раньше не было.

Я попытался проверить запущенные запросы и столкнулся с этим результатом команды объяснения.

+----+-------------+-------------------------------+--------+----------------------------------------------------------------------------------------------+---------------------------------------+---------+-----------------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table                         | type   | possible_keys                                                                                | key                                   | key_len | ref                                                             | rows | Extra                                        |
+----+-------------+-------------------------------+--------+----------------------------------------------------------------------------------------------+---------------------------------------+---------+-----------------------------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | businesses                    | const  | PRIMARY                                                                                      | PRIMARY                               | 4       | const                                                           |    1 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | activities_businesses         | ref    | PRIMARY,index_activities_users_on_business_id,index_tweets_users_on_tweet_id_and_business_id | index_activities_users_on_business_id | 9       | const                                                           | 2252 | Using index condition; Using where           |
|  1 | SIMPLE      | activities_b_taggings_975e9c4 | ref    | taggings_idx                                                                                 | taggings_idx                          | 782     | const,myapp_production.activities_businesses.id,const           |    1 | Using index condition; Using where           |
|  1 | SIMPLE      | activities                    | eq_ref | PRIMARY,index_activities_on_created_at                                                       | PRIMARY                               | 8       | myapp_production.activities_businesses.activity_id              |    1 | Using where                                  |
+----+-------------+-------------------------------+--------+----------------------------------------------------------------------------------------------+---------------------------------------+---------+-----------------------------------------------------------------+------+----------------------------------------------+

Также зарегистрируйтесь в списке процессов, я получил что-то вроде этого:

+----+-----------------+-------------------------------------+----------------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User            | Host                                | db                         | Command | Time | State        | Info                                                                                                 |
+----+-----------------+-------------------------------------+----------------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
|  1 | my_app          | my_ip:57152                         | my_app_production          | Sleep   |    0 |              | NULL                                                                                                 |
|  2 | my_app          | my_ip:57153                         | my_app_production          | Sleep   |    2 |              | NULL                                                                                                 |
|  3 | rdsadmin        | localhost:49441                     | NULL                       | Sleep   |    9 |              | NULL                                                                                                 |
|  6 | my_app          | my_other_ip:47802                   | my_app_production          | Sleep   |  242 |              | NULL                                                                                                 |
|  7 | my_app          | my_other_ip:47807                   | my_app_production          | Query   |  231 | Sending data | SELECT my_fields...                                                                                  |
|  8 | my_app          | my_other_ip:47809                   | my_app_production          | Query   |  231 | Sending data | SELECT my_fields...                                                                                  |
|  9 | my_app          | my_other_ip:47810                   | my_app_production          | Query   |  231 | Sending data | SELECT my_fields...                                                                                  |
| 10 | my_app          | my_other_ip:47811                   | my_app_production          | Query   |  231 | Sending data | SELECT my_fields...                                                                                  |
| 11 | my_app          | my_other_ip:47813                   | my_app_production          | Query   |  231 | Sending data | SELECT my_fields...                                                                                  |
...

Таким образом, исходя из цифр, похоже, что нет причин для медленного запроса, поскольку наихудший план выполнения — это тот, который проходит через 2 тыс. строк, что немного.

Изменить 1

Другая информация, которая может быть полезна, — это медленный query_log.

SET timestamp=1401457485;
SELECT my_query...
# User@Host: myapp[myapp] @ ip-10-195-55-233.ec2.internal [IP] Id: 435
# Query_time: 95.830497 Lock_time: 0.000178 Rows_sent: 0 Rows_examined: 1129387

Изменить 2

После профилирования получил вот такой результат. В результате получается примерно 250 строк с двумя столбцами в каждой.

+----------------------+----------+
| state                | duration |
+----------------------+----------+
| Sending data         |      272 |
| removing tmp table   |        0 |
| optimizing           |        0 |
| Creating sort index  |        0 |
| init                 |        0 |
| cleaning up          |        0 |
| executing            |        0 |
| checking permissions |        0 |
| freeing items        |        0 |
| Creating tmp table   |        0 |
| query end            |        0 |
| statistics           |        0 |
| end                  |        0 |
| System lock          |        0 |
| Opening tables       |        0 |
| logging slow query   |        0 |
| Sorting result       |        0 |
| starting             |        0 |
| closing tables       |        0 |
| preparing            |        0 |
+----------------------+----------+

Изменить 3

Добавление запроса по запросу

SELECT activities.share_count, 
       activities.created_at 
FROM   `activities_businesses` 
       INNER JOIN `businesses` 
               ON `businesses`.`id` = `activities_businesses`.`business_id` 
       INNER JOIN `activities` 
               ON `activities`.`id` = `activities_businesses`.`activity_id` 
       JOIN taggings activities_b_taggings_975e9c4 
         ON activities_b_taggings_975e9c4.taggable_id = activities_businesses.id 
            AND activities_b_taggings_975e9c4.taggable_type = 
                'ActivitiesBusiness' 
            AND activities_b_taggings_975e9c4.tag_id = 104 
            AND activities_b_taggings_975e9c4.created_at >= 
                '2014-04-30 13:36:44' 
WHERE  ( businesses.id = 1 ) 
       AND ( activities.created_at > '2014-04-30 13:36:44' ) 
       AND ( activities.created_at < '2014-05-30 12:27:03' ) 
ORDER  BY activities.created_at;

Изменить 4

Может быть вероятность того, что индексы не применяются из-за различий в типах столбцов между тегами и activity_businesses в столбце tagged_id.

mysql> SHOW COLUMNS FROM activities_businesses;
+-------------+------------+------+-----+---------+----------------+
| Field       | Type       | Null | Key | Default | Extra          |
+-------------+------------+------+-----+---------+----------------+
| id          | int(11)    | NO   | PRI | NULL    | auto_increment |
| activity_id | bigint(20) | YES  | MUL | NULL    |                |
| business_id | bigint(20) | YES  | MUL | NULL    |                |
+-------------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> SHOW COLUMNS FROM taggings;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| tag_id        | int(11)      | YES  | MUL | NULL    |                |
| taggable_id   | bigint(20)   | YES  |     | NULL    |                |
| taggable_type | varchar(255) | YES  |     | NULL    |                |
| tagger_id     | int(11)      | YES  |     | NULL    |                |
| tagger_type   | varchar(255) | YES  |     | NULL    |                |
| context       | varchar(128) | YES  |     | NULL    |                |
| created_at    | datetime     | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

Таким образом, он проверяет гораздо больше строк, чем показывает в запросе объяснения, вероятно, потому, что некоторые индексы не применяются.

Вы, ребята, можете мне помочь с этим?

felipeclopes 30.05.2014 источник

comment

Почему не публикуете запрос? - ericpap 30.05.2014

comment

Любые мысли после последнего редактирования, добавляющего запрос? - felipeclopes 30.05.2014

comment

Моей первой попыткой было бы переместить это И activity_b_taggeds_975e9c4.taggable_type = 'ActivitiesBusiness' И activity_b_taggeds_975e9c4.tag_id = 104 AND activity_b_taggeds_975e9c4.created_at ›= '2014-04-30 13:36:44' в WHERE вместо предложения ON. Есть разница? - ericpap 30.05.2014

comment

Также есть ли у вас какие-либо инструменты, чтобы увидеть, правильно ли используется индекс или MYSQL выполняет сканирование таблицы? - ericpap 30.05.2014

comment

@ericpap Я не думаю, что перемещение WHERE вместо On окажет какое-либо влияние. Кроме того, это создано не мной, а самим жемчужиной Rails, поэтому я не хочу идти в этом направлении с самого начала. - felipeclopes 30.05.2014

comment

@ericpap Да, неплохо проверить, выполняет ли он сканирование таблицы вместо индексации, есть ли у вас какие-либо мысли о том, как это сделать? - felipeclopes 30.05.2014

comment

Ну зависит от вашей СУБД. В уверенном MYSQL это, кажется, анализатор запросов mysql, вызывающий продукт, для этого. Также вы должны сами поиграть с запросом (неважно, как его сгенерировать), чтобы увидеть, где проблема с производительностью. - ericpap 30.05.2014

comment

Сколько строк возвращается? - Marcus Adams 30.05.2014

Ответы (2)

arrow_upward
0
arrow_downward

Как я и ожидал, в 4-м редактировании была вводящая в заблуждение информация, которую MySQL предоставил с помощью команды DESCRIBE.

Несмотря на то, что план выполнения, предоставленный командой, говорил, что он будет следующим:

+----+-------------+-------------------------------+--------+----------------------------------------------------------------------------------------------+---------------------------------------+---------+-----------------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table                         | type   | possible_keys                                                                                | key                                   | key_len | ref                                                             | rows | Extra                                        |
+----+-------------+-------------------------------+--------+----------------------------------------------------------------------------------------------+---------------------------------------+---------+-----------------------------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | businesses                    | const  | PRIMARY                                                                                      | PRIMARY                               | 4       | const                                                           |    1 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | activities_businesses         | ref    | PRIMARY,index_activities_users_on_business_id,index_tweets_users_on_tweet_id_and_business_id | index_activities_users_on_business_id | 9       | const                                                           | 2252 | Using index condition; Using where           |
|  1 | SIMPLE      | activities_b_taggings_975e9c4 | ref    | taggings_idx                                                                                 | taggings_idx                          | 782     | const,myapp_production.activities_businesses.id,const           |    1 | Using index condition; Using where           |
|  1 | SIMPLE      | activities                    | eq_ref | PRIMARY,index_activities_on_created_at                                                       | PRIMARY                               | 8       | myapp_production.activities_businesses.activity_id              |    1 | Using where                                  |
+----+-------------+-------------------------------+--------+----------------------------------------------------------------------------------------------+---------------------------------------+---------+-----------------------------------------------------------------+------+----------------------------------------------+

Ясно, что это было не то, что происходило, поскольку в медленном запросе мы могли видеть, что количество проверенных строк было намного больше.

# Query_time: 95.830497 Lock_time: 0.000178 Rows_sent: 0 Rows_examined: 1129387

Имея эту информацию, можно было проверить соединения и обнаружить, что идентификаторы в таблицах не используют один и тот же тип:

mysql> SHOW COLUMNS FROM activities_businesses;
+-------------+------------+------+-----+---------+----------------+
| Field       | Type       | Null | Key | Default | Extra          |
+-------------+------------+------+-----+---------+----------------+
| id          | int(11)    | NO   | PRI | NULL    | auto_increment |
3 rows in set (0.01 sec)

mysql> SHOW COLUMNS FROM taggings;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |

Немного изучив тему, было легко заявить, что MySQL не индексирует столбцы разных типов или, по крайней мере, неправильно, хотя команда EXPLAIN говорит об этом.

Соединение столбцов разного типа?

Производительность JOIN с использованием столбцов различных числовых типов< /а>

Итак, наконец, после внесения изменений, чтобы превратить оба столбца в один и тот же тип, запросы стали выполняться быстрее, чем 50 мс, что достаточно для моего сценария.

felipeclopes 31.05.2014

arrow_upward
-1
arrow_downward

я подозреваю, что порядок по пункту является виновником. Попробуйте пару вещей. Добавьте индекс в столбец created_at. Если это сработает, отлично, не читайте дальше! В противном случае (или если это сработало, и вы хотите еще более быстрый запрос) Запустите этот запрос, посмотрите, не займет ли он больше времени для выполнения:

SELECT activities.share_count, 
       activities.created_at 
FROM   `activities`  
       JOIN `businesses` 
               ON `businesses`.`id` = `activities_businesses`.`business_id` 
       JOIN `activities_businesses`
               ON `activities`.`id` = `activities_businesses`.`activity_id` 
       JOIN taggings activities_b_taggings_975e9c4 
         ON activities_b_taggings_975e9c4.taggable_id = activities_businesses.id 
            AND activities_b_taggings_975e9c4.taggable_type = 
                'ActivitiesBusiness' 
            AND activities_b_taggings_975e9c4.tag_id = 104 
            AND activities_b_taggings_975e9c4.created_at >= 
                '2014-04-30 13:36:44' 
WHERE  ( businesses.id = 1 ) 
       AND ( activities.created_at > '2014-04-30 13:36:44' ) 
       AND ( activities.created_at < '2014-05-30 12:27:03' ) 
ORDER  BY activities.created_at;

Если это решит проблему, отлично!

В противном случае (или если предыдущие предложения сработали, и вы хотите получить еще более быстрый запрос), выполните следующий двухэтапный процесс: 1. запустите тот же запрос без предложения order by:

SELECT activities.share_count, 
       activities.created_at 
FROM   `activities`  
       JOIN `businesses` 
               ON `businesses`.`id` = `activities_businesses`.`business_id` 
       JOIN `activities_businesses`
               ON `activities`.`id` = `activities_businesses`.`activity_id` 
       JOIN taggings activities_b_taggings_975e9c4 
         ON activities_b_taggings_975e9c4.taggable_id = activities_businesses.id 
            AND activities_b_taggings_975e9c4.taggable_type = 
                'ActivitiesBusiness' 
            AND activities_b_taggings_975e9c4.tag_id = 104 
            AND activities_b_taggings_975e9c4.created_at >= 
                '2014-04-30 13:36:44' 
WHERE  ( businesses.id = 1 ) 
       AND ( activities.created_at > '2014-04-30 13:36:44' ) 
       AND ( activities.created_at < '2014-05-30 12:27:03' )

Если это выполняется супер-пупер быстро, отлично. Сбросить результат этого во временную таблицу и отсортировать по ней. Это разбивает вашу дорогостоящую операцию на более мелкие, которые легче выполнять серверу, т.е.

CREATE TEMPORARY TABLE temp_activities (INDEX(created_at))
SELECT activities.share_count, 
           activities.created_at 
    FROM   `activities`  
           JOIN `businesses` 
                   ON `businesses`.`id` = `activities_businesses`.`business_id` 
           JOIN `activities_businesses`
                   ON `activities`.`id` = `activities_businesses`.`activity_id` 
           JOIN taggings activities_b_taggings_975e9c4 
             ON activities_b_taggings_975e9c4.taggable_id = activities_businesses.id 
                AND activities_b_taggings_975e9c4.taggable_type = 
                    'ActivitiesBusiness' 
                AND activities_b_taggings_975e9c4.tag_id = 104 
                AND activities_b_taggings_975e9c4.created_at >= 
                    '2014-04-30 13:36:44' 
    WHERE  ( businesses.id = 1 ) 
           AND ( activities.created_at > '2014-04-30 13:36:44' ) 
           AND ( activities.created_at < '2014-05-30 12:27:03' );
SELECT * FROM temp_activities ORDER BY created_at;

Haleemur Ali 31.05.2014

comment

Ясно, что проблема была не в запросе, так как план выполнения был правильно проиндексирован командой EXPLAIN. - felipeclopes; 31.05.2014

MySQL 100% ЦП + медленный запрос - неправильное использование индекса

Ответы (2)

Вопросы по теме