|
Задавайте вопросы, мы ответим
Вы не зашли.
Страниц: 1
Здравствуйте.
Уже вторую неделю мучаемся и не знаем, что можно сделать, чтобы хоть как то ускорить выполнение запроса.
Имеем:
* Сервер: Xeon 2 x E5504, mem: 8GB, RAID10 (SAS15 x 6, BBU, 512Mb)
* MySQL: 5.0, 5.1, 5.5, 5.5-percona (пробовали все)
Простая таблица items_set, содержит 66 795 414 строк (~ 25ГБ на диске)
EXPLAIN
написал:+----+-------------+-------+---------+------------------------+------------------+----------+-------+-------+---------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+---------+------------------------+-------------------+----------+-------+-------+---------------+
| 1 | SIMPLE | i | ref | item_catalog_id | item_catalog_id | 8 | const | 32700 | Using where |
+----+-------------+-------+---------+------------------------+-------------------+----------+-------+-------+---------------+
my.cnf написал:
[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# GENERAL #
user = mysql
default_storage_engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid_file = /var/lib/mysql/mysql.pid
character-set-server=utf8
# MyISAM #
key_buffer_size = 32M
myisam_recover = FORCE,BACKUP
# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
#log_bin = /var/lib/mysql/mysql-bin
#expire_logs_days = 14
#sync_binlog = 1
# CACHES AND LIMITS #
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections = 500
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 4096
table_open_cache = 4096
# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 2G
# LOGGING #
log_error = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
Так вот, проблема в том, что с некоторыми значениями item_id запрос выполняется 5...40 секунд.
Выполняется так медленно только один раз, во второй...n раз всегда выполняется быстро.
Например если перегрузить MySQL и выполнить запрос который я привел выше, он будет выполнен за ~30 сек.
Что мы пробовали:
* Сменить железо,
* Сменить версию MySQL 5, 5.1, 5.5, 5.5-percona (и типов хранилищ InnoDB, XtraDB, MyISAM)
* Разбить на партиции по item_id.
* Оптимизировать конфиг (пробовали подкручивать INNODB и CACHES LIMITS).
* Пересобирали таблицу (optimize table, он же alter в innodb) таблица стала компактней на ФС, но скорость не выросла.
Ничего не помогло... Буду благодарен за любую помощь... Спасибо.
Отредактированно yakimov (10.01.2012 08:16:20)
Неактивен
Простой запрос
SELECT * FROM items_set i WHERE item_id = 731861 GROUP BY catalog_id;
Запрос выглядит несколько странным.
SELECT * при наличии GROUP BY будет давать непонятно что.
Вы уверены, что Вам нужен именно такой запрос?
Может быть, нужны какие-то конкретные колонки, а не все?
Выполняется так медленно только один раз, во второй...n раз всегда выполняется быстро.
Это, вероятно, из-за кэша запросов (т.е. все разы, кроме первого, запрос фактически не выполняется, а результат его берется из кэша).
Да, кстати. InnoDB - т.к. запись частая?
Неактивен
Выполняется так медленно только один раз, во второй...n раз всегда выполняется быстро.
Используйте SELECT SQL_NO_CACHE ..., чтобы исключить влияние query_cache без перезагрузки mysql.
Запрос действительно странный - для чего он требуется?
Неактивен
rgbeast написал:
Используйте SELECT SQL_NO_CACHE ..., чтобы исключить влияние query_cache без перезагрузки mysql.
Запрос действительно странный - для чего он требуется?
Да мы пробовали SQL_NO_CACHE, но влияния никакого он не оказывает. Думаю, что кеширование происходит на уровне операционной системы. Linux считывает части файла к которым было обращение и кэширует их в памяти. Как только ОС требуется память, она чистит самые старые буфера.
LazY написал:
Запрос выглядит несколько странным.
SELECT * при наличии GROUP BY будет давать непонятно что.
Вы уверены, что Вам нужен именно такой запрос?
Может быть, нужны какие-то конкретные колонки, а не все?
Прошу прощения, я немного упростил запрос, дабы не заставлять разбираться в куче строк.
Вот такой запрос генерирует ORM Doctrine который используется в одном из наших проектов.
Сейчас мы оптимизируем узкие места в нашем проекте и наткнулись на этот запрос и никак не можем его побороть.
EXPLAIN написал:
+----+-------------+-------+------+-----------------------+-------------------+----------+-------+--------+---------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------+-------------------+----------+-------+--------+---------------+
| 1 | SIMPLE | i | ref | item_catalog_id | item_catalog_id | 8 | const | 32700 | Using where |
+----+-------------+-------+------+-----------------------+-------------------+----------+-------+--------+---------------+
1 row in set (0.00 sec)
Отредактированно yakimov (10.01.2012 08:21:06)
Неактивен
LazY написал:
Простой запрос
SELECT * FROM items_set i WHERE item_id = 731861 GROUP BY catalog_id;Запрос выглядит несколько странным.
SELECT * при наличии GROUP BY будет давать непонятно что.
Вы уверены, что Вам нужен именно такой запрос?
Может быть, нужны какие-то конкретные колонки, а не все?
По вашей наводке попробовал
EXPAIN написал:
+----+-------------+-------+------+---------------------+--------------------+---------+-------+-------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------+--------------------+---------+-------+--------+-----------------------------+
| 1 | SIMPLE | i | ref | item_catalog_id | item_catalog_id | 8 | const | 34470 | Using where; Using index |
+----+-------------+-------+------+---------------------+--------------------+---------+-------+--------+-----------------------------+
1 row in set (0.00 sec)
EXPAIN написал:
+----+-------------+-------+------+---------------------+--------------------+---------+-------+-------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------+--------------------+---------+-------+--------+-----------------------------+
| 1 | SIMPLE | i | ref | item_catalog_id | item_catalog_id | 8 | const | 32700 | Using where; |
+----+-------------+-------+------+---------------------+--------------------+---------+-------+--------+-----------------------------+
1 row in set (0.00 sec)
И был удивлен результатом EXPLAIN. У меня одна догадка, виной всему первичный ключ...
Но почему так происходит я не понимаю, объясните если не сложно...
Неактивен
LazY написал:
Да, кстати. InnoDB - т.к. запись частая?
Запись только при наполнении(обновлении), раз в 3-4 месяца.
Все остальное время таблица ReadOnly 
А какие есть еще варианты? MyISAM?
Сейчас склоняемся к попробовать MongoDB, т.к. таблица которую я привел не самая большая в нашем проекте...
Неактивен
SELECT
i.id AS i__id,
i.items_set_id AS i__items_set_id,
i.catalog_id AS i__catalog_id,
i.item_id AS i__item_id,
i.original_item_code AS i__original_item_code,
i.quantity AS i__quantity,
i.mask AS i__mask,
i.description_ru AS i__description_ru,
i.description_en AS i__description_en,
i.description_ja AS i__description_ja
FROM items_set i
WHERE i.item_id = 831251
GROUP BY i.catalog_id
Все равно странный запрос..
С одной стороны, присутствует GROUP BY, с другой стороны, такое впечатление, что в результатах запроса это никак не используется, функций группировки нет.
Такая же проблема с запросом
SELECT id FROM items_set i WHERE item_id = 731861 GROUP BY catalog_id;
(в этом отношении он не отличается от SELECT * - ни там, ни там не используется группировка).
Результат этого запроса выдаст случайный (в смысле, непредсказуемый) id для каждой группы.
Использовать такой запрос на практике нельзя (т.к. результат его не определен).
Обычно бывает что-то типа
SELECT catalog_id, COUNT(*)
FROM items_set
WHERE item_id = 731861
GROUP BY catalog_id;
Ну, собственно, это не вполне к проблеме относится (я просто хотел это отметить).
И был удивлен результатом EXPLAIN. У меня одна догадка, виной всему первичный ключ...
Если Вы о том, что при SELECT id написано Using index (т.е. MySQL удается использовать только индексы, хотя колонки id в них нет), то, думаю, разгадка состоит в том, что InnoDB во всех индексах хранит в скрытом виде первичный ключ, даже если он не указан в перечне индексированных колонок (от этого размер индексов в InnoDB обычно больше, чем ожидалось), то есть ваша догадка правильная.
Но т.к. в реальной жизни Вам нужны не только id, толку от этого мало.
Запись только при наполнении(обновлении), раз в 3-4 месяца.
Все остальное время таблица ReadOnly
А какие есть еще варианты? MyISAM?
Ну да..
MyISAM в целом менее ресурсоемкий механизм хранения, чем InnoDB. Требует меньше памяти, имеет более прозрачную логику работы.
Быстрее строит индекс при обновлении (засчет этого вставка большого объема данных может проходить на порядки быстрее, чем в таблицы InnoDB).
Одна из важных особенностей MyISAM состоит в том, что он никогда не хранит данные в памяти - хранит только индексы. Отсюда два следствия.
С одной стороны, легче контролировать состояние индексов в памяти (под каждую таблицу можно выделить специальный буфер и регулировать его размер, гарантировано добиваясь таким образом полного размещения индекса в памяти), индексы не мешаются в памяти с данными и никогда не могут быть ими вытеснены.
С другой стороны, данные всегда читаются с диска (если и кэшируются - то только на уровне файловой системы), со всеми вытекающими последствиями.
В вашем случае, похоже, лимитирующая стадия - это чтение с диска, поэтому переход на MyISAM вряд ли поможет (попробовать можно разве что ради интереса).
Просто когда использование InnoDB не обусловлено какими-то необходимостями, MyISAM использовать обычно лучше.
Вообще довольно странно, что не помог partitioning и оптимизация таблиц. По идее, это должно облегчать чтение с диска..
Как диск вообще загружен, в целом? Много ли конкурирующих процессов?
Да, кстати, под конец написания идея пришла. Попробуйте на всякий случай поиграть с sort_buffer_size.
У Вас перелопачивается по 30 тыс. строк (в два мегабайта по умолчанию, они вряд ли помещаются). Прикиньте, сколько эти данные занимают места, и перед выполнением запроса сделайте SET @@sort_buffer_size = ..., а потом - обратно (включать большой буфер на весь сервер нельзя, т.к. он всегда отъедает всю память, которую ему выделили).
Вдруг поможет..
Неактивен
А давайте я тоже вклинюсь, раз уж все ответили 
1. sort_buffer помочь не должен, т.к. всё же по индексу достаем и группируем;
2. попробуйте выполнить этот запрос без группировки с какими-то доп. ограничениями.
Интересно понять, зачем ORM добавляет группировку там, где она противоречит логике.
Я бы делал как-то так:
SELECT catalog_id FROM items_set WHERE item_id = 731861 GROUP BY 1 ORDER BY COUNT(*) DESC LIMIT 1;
Этот запрос выдаст catalog_id, в котором больше всего строк собираются в одну группировкой.
После этого —
SELECT * FROM items_set WHERE item_id = 731861 AND catalog_id = $catalog_id.
И посмотреть на данные глазами. У них должно быть что-то сильно общее с точки
зрения ORM (так, что он по ним группирует, но при этом ему всё равно, значения
каких строк он получит после группировки). Подозреваю, что строки там будут
одинаковые или совсем-совсем-почти-одинаковые. Возможно, можно от каких-то
из них избавиться совсем
3. Что касается Вашего вопроса — два запроса одинаковые: выбрать значения
полей из *каких-то* строк, которые попали в группу. Оценки количества строк
могут различаться, т.к. это только оценки, а не точное количество.
4. Ну и что касается проблемы в целом — к сожалению, 25 гигабайт никогда не
влезут в два в ОЗУ, а значит — Вы будете бегать по диску. В данном случае Вы
бегаете оптимально (почти оптимально) — по индексу. Боюсь, что нужно таки
смотреть в сторону уменьшения данных или шардирования. Ну или сесть с програм-
мистом, и попытаться понять, зачем ORMу такой запрос. Как правило, никому не
нужно ни 30к строк изначальных данных, ни 1к сгруппированных. На страничке
выводится хорошо если 50.
Неактивен
paulus написал:
В данном случае Вы бегаете оптимально (почти оптимально) — по индексу.
А InnoDB не выпихивает индекс из буфера своего запрашиваемыми данными? (у индекса вообще есть приоритет какой-нибудь в буфере?)
Неактивен
Выпихивает. Ну, кроме кластерного ключа из-за того, что он кластерный.
А обращение к индексам выпихивает данные. LRU, как и любые другие кэши.
Неактивен
1. Что такое кластерный ключ?
2. Что такое LRU?
Неактивен
1. Ключ, на листьях которого лежат данные. Почитай про устройство InnoDB,
там PK — это кластерный ключ.
2. Least recently used. Вымываются странички, к которым давно не обращались.
Неактивен
Ну да, я так и понимал.
Получается, что при объемах buffer_pool меньших, чем таблица (как это бывает обычно и как это имеет место в данном случае) ключи особо не задерживаются в памяти, потому что их все время вымывают данные (если запрашиваются данные из разных частей таблиц).
Выходит, что ключи как бы даже мешаются: если данные в памяти, то их быстро прочитать и без ключа (ключ только память лишнюю занимать будет), если данные на диске, то и ключ к ним скорее всего тоже на диске, его придется вытаскивать с диска, что уже медленно.
Т.е. польза от ключей при таком подходе нивелируется.
Так? Или не совсем?
Неактивен
Как правило, нагрузка по таблице распределяется совсем не равномерно.
Какие-то странички запрашиваются чаще, и они, как правило, и живут в
памяти.
Неактивен
LazY написал:
SELECT
i.id AS i__id,
i.items_set_id AS i__items_set_id,
i.catalog_id AS i__catalog_id,
i.item_id AS i__item_id,
i.original_item_code AS i__original_item_code,
i.quantity AS i__quantity,
i.mask AS i__mask,
i.description_ru AS i__description_ru,
i.description_en AS i__description_en,
i.description_ja AS i__description_ja
FROM items_set i
WHERE i.item_id = 831251
GROUP BY i.catalog_idВсе равно странный запрос..
С одной стороны, присутствует GROUP BY, с другой стороны, такое впечатление, что в результатах запроса это никак не используется, функций группировки нет.
Запросом мы хотим узнать все каталоги в которых встречается деталь с id 831251. Если бы запрос писал человек а не ОРМ было бы как-то так:
SELECT DISTINCT catalog_id FROM items_set WHERE item_id = 831251;
LazY написал:
Да, кстати, под конец написания идея пришла. Попробуйте на всякий случай поиграть с sort_buffer_size.
У Вас перелопачивается по 30 тыс. строк (в два мегабайта по умолчанию, они вряд ли помещаются). Прикиньте, сколько эти данные занимают места, и перед выполнением запроса сделайте SET @@sort_buffer_size = ..., а потом - обратно (включать большой буфер на весь сервер нельзя, т.к. он всегда отъедает всю память, которую ему выделили).
Вдруг поможет..
Попробовал, не помогло 
Спасибо за советы.
Отредактированно yakimov (18.01.2012 07:53:07)
Неактивен
Запросом мы хотим узнать все каталоги в которых встречается деталь с id 831251. Если бы запрос писал человек а не ОРМ было бы как-то так:
SELECT DISTINCT catalog_id FROM items_set WHERE item_id = 831251;
А такой запрос сколько выполняется?
Неактивен
LazY написал:
Запросом мы хотим узнать все каталоги в которых встречается деталь с id 831251. Если бы запрос писал человек а не ОРМ было бы как-то так:
SELECT DISTINCT catalog_id FROM items_set WHERE item_id = 831251;
А такой запрос сколько выполняется?
Такой запрос на первый взгляд (10...20 id протестировал) выполняется не более секунды (без кэша). В среднем 0,2...0,3 сек.
Переписали запрос в ОРМ, понаблюдаем. О результатах сообщу.
Неактивен
Страниц: 1
