InnoDB Buffer Pool и его стабильность

Question

[Сергей Болдырев]

Взялись оптимизировать работу с БД, разобрались с индексами, теперь практически все запросы идут с Using where.

Проблема в том, что отрабатывают они быстро только при условии выгрузки главной таблицы в пул (нас в основном интересует одна таблица размером в 1Гб и 1млн. записей), узнаю я о том, что таблица выгружается в пул по стрекотанию харда.
Рядовой запрос с выгрузкой в пул (например, после перезапуска mysql) может выполняться и 60-70 секунд, что будет категорически неприемлемо для продакшна. Последующие разные запросы из той же таблицы уже отрабатывают за приемлемые 0.3-0.8 сек.

Проблема усугубляется тем, что периодически (вроде как после какого-то времени простоя) таблица оказывается выгруженной из пула, и читается туда снова, то есть ситуация 60-70 секунд на запрос может случаться не обязательно только при перезагрузке, а как-то случайным образом (возможно, и не случайным, тут не знаю).

Вопрос 1 — а вообще нормально, что запрос с Using where по таблице не в пуле выполняется 60-70 секунд?
Вопрос 2 — что можно сделать с проблемой выгрузки таблицы из пула?

Comments

[stavinsky]

Хочу конфиги. Размер оперативки, размер буфер пула, ну и тд

[Сергей Болдырев]

my.cnf

key_buffer_size = 1536M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
#record_rnd_buffer = 8M
myisam_sort_buffer_size = 256M
max_allowed_packet = 16M
thread_stack = 512K
tmp_table_size = 256M
max_heap_table_size = 3072M
group_concat_max_len = 1048576
read_buffer_size = 1M
join_buffer_size = 1M
max_prepared_stmt_count = 32K
lower_case_table_names = 1
ft_min_word_len = 2

connect_timeout = 30
delayed_insert_timeout = 600
innodb_lock_wait_timeout = 200
interactive_timeout = 57600
net_read_timeout = 90
net_write_timeout = 120
slave_net_timeout = 3600
wait_timeout = 57600

myisam-recover = BACKUP
max_connections = 600
table_cache = 450

query_cache_limit = 512k

innodb_flush_log_at_trx_commit=0
innodb_flush_method = O_DIRECT
transaction-isolation=READ-COMMITTED
innodb_additional_mem_pool_size = 20M
innodb_buffer_pool_size = 5120M
innodb_log_buffer_size = 16M

thread_cache = 32
thread_cache_size = 32
query_cache_size = 512M

Оперативки 8Гб.
Буфер, вот, 5120М.

[Сергей Болдырев]

Кстати, ещё вопрос — имеет ли какое-то значение общее количество InnoDB-таблиц для Buffer Pool, даже если с ними вообще ничего не происходит?

[Пума Тайланд]

Имеет

[Сергей Болдырев]

Проблема решилась, итог такой - на работу Buffer Pool сильно влияет общее количество innodb-таблиц. Оставили на машине только нужные таблицы, справочные таблицы перевели на MyISAM, в итоге буфер устаканился и больше не вываливался, всё начало работать, как часы.

Answer 1

[AxisPod]

Это специфика работы InnoDB хранилища, хотите быстрые выборки, используйте MyISAM.

Comments

[Сергей Болдырев]

А если сделать костыль с периодическим warm up таблицы по крону?

[AxisPod]

Как вариант можете попробовать переехать на MariaDB и хранилище XtraDB, присутствует обратная совместимость, проблем с переездом быть не должно.

[Сергей Болдырев]

А какие там преимущества?

[stavinsky]

вы не путаете? XtraDB — это удел Percona.
mAriaDB как раз оптимизирует MyISAM круто.

[AxisPod]

Да, разработка percona, но данное хранилище включено в MariaDB.

[vsespb]

AxisPod, что за специфика, первый раз слышу, можно пруф? Известно что MyISAM «быстрее на простых выборках», но какое это имеет отношение к топику?

[Masterme]

Это специфика работы InnoDB хранилища, хотите быстрые выборки, используйте MyISAM.

Сразу видно знатока

Answer 2

[vsespb]

Проблема в том, что отрабатывают они быстро только при условии выгрузки главной таблицы в пул (нас в основном интересует одна таблица размером в 1Гб и 1млн. записей),

Я всегда считал, что в оперативной памяти должны быть все индексы, а не вся таблица. Иначе это была бы полная глупость. Откуда уверенность что вся таблица читается? Может у Вас просто большие индексы и мало памяти?

Проблема усугубляется тем, что периодически (вроде как после какого-то времени простоя) таблица оказывается выгруженной из пула, и читается туда снова

А во время простоя запросы к другим таблицам не делаете? Есть ли таблицы с огромными блобами (типа, файлы в базе храните)?

Comments

[Сергей Болдырев]

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

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

В InnoDB всё-таки есть Buffer Pool.

А во время простоя запросы к другим таблицам не делаете?

Если только немногочисленные, но таких сравнимо больших таблиц больше нет.

Есть ли таблицы с огромными блобами

1Гб и 1млн. записей означает, что грубо говоря на одну строку приходится 1Кб данных, распределение равномерное.

[vsespb]

Buffer pool и для данных и для индексов. Большие данные выкинут индексы (поэтому я и спросил про файлы в БД).

Тем не менее для быстрой работы запросов в памяти нужны только индексы (в противном случае база будет всегда тормозить, если не влезет в память), по крайней мере у меня 30гб база нормально работает, запросы ок, хотя памяти у меня меньше 30 гб.

> Делал вычитку только индексов сразу после перезагрузки сервера.
как это делается?

> Любой следующий запрос, использующий не только покрывающие индексы
что такое покрывающие индексы?

Индекс может выкинуть из памяти какой-нибудь запрос, который читает все данные (работая без индекса), если, конечно, памяти мало.

[Сергей Болдырев]

как это делается?

SELECT COUNT(*) FROM table WHERE key_start_field = 'smth'

что такое покрывающие индексы?

dev.mysql.com/doc/innodb/1.1/en/glossary.html#glos_covering_index

Индекс может выкинуть из памяти какой-нибудь запрос, который читает все данные (работая без индекса), если, конечно, памяти мало.

Таких нет. Памяти 8Гб, буферу отдано 5Гб.

Answer 3

[Xeenon]

В MariaDB 10.0 есть очень интересная фича engine independent table statistics.

Может помочь.
Но тут сильно зависит от данных (как часто обновляются, какая сardinality) и запроса (сколько в запросе используется таблиц и т.п.).

Answer 4

[Masterme]

На случай, если проблему решить не сможете — попробуйте сделать денормализацию — разбить таблицу на две. В одной оставьте столбцы, необходимые для выполнения запроса, в другой остальные. Тогда загружаться будет не гиг, а меньше. Да, в логику приложения придётся внести изменения.

Answer 5

[Masterme]

Вероятные проблемы:

— не хватает оперативы для кэш ОС либо неоптимальное (двойное либо наоборот одинарное) кэширование файлов таблиц mysql либо просто не хватает оперативы под буфера mysql. попробуйте уменьшить innodb_buffer_pool_size до 4х или 3х гигов. ещё у вас отводится память под myisam, key_buffer_size — полтора гига. оно действительно нужно? если не используете myisam — отключите его совсем

— почему винт стрекочет? у вас наверное вся база в одном файле, а внутри этого файла ваша таблица может быть фрагментирована. попробуйте с параметром innodb_file_per_table=1. нужно будет выгрузить базу в бэкап и восстановить, чтобы файлы таблиц пересоздались. также нужно будет увеличить параметр open-files-limit в секции [mysqld]. затем можно этот файл дефрагментировать. эта мера сделает чтение гиговой таблицы более «гладким»
forums.cpanel.net/f43/innodb_file_per_table-converting-per-table-data-innodb-167942.html
stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261
dba.stackexchange.com/questions/15531/why-does-innodb-store-all-databases-in-one-file

Comments

[Masterme]

Включение innodb_file_per_table=1 можно совместить с денормализацией, тогда файл с данными для выборки можно уменьшить на порядок, что скажется на скорости чтения с диска.

[Сергей Болдырев]

key_buffer_size — полтора гига

Используем, есть совсем большая таблица полигонов, оно для этого.
Но, как я понимаю, на InnoDB это может влиять только со стороны размера памяти под этот key_buffer_size, и больше никак.

у вас наверное вся база в одном файле

Да, так.
Не делим на файлы пока только потому, что явной необходимости не было, а дамп/раздамп займёт немало времени.
О денормализации думаем, но пока нет понимания текущей ситуации, браться за такое не хотелось бы.

Answer 6

[Masterme]

И ещё добавлю. Using where означает неоптимальное построение индексов. При правильном запросе и правильном построении индексов мускулу не требуется загружать всю таблицу, достаточно загрузить только индекс. Покажите структуру таблицы и проблемный запрос.
Я бы начал с этой меры в первую очередь.

Comments

[Сергей Болдырев]

У нас около 40 полей, участвующих в поиске, плюс сложные сортировки, поэтому совсем уйти от Using where не выйдет точно (после сортировок и флажков состояний от лимита составного индекса в 16 штук остаётся не так много).
Сама ситуация нахождения таблицы в памяти, в принципе, не проблема — её должно хватать на все таблицы (правда, есть сомнение, что количество памяти, требуемое под таблицу в пуле не совсем равно размеру таблицы на диске, и если это так, неплохо бы знать формулу расчета).
Проблема уже не в конкретных запросах.

Answer 7

[Пума Тайланд]

Примеры запросов в студию, все таки засунуть все в память хорошо, но опять таки если все таблицы иннодб влезают, если их у вас больше чем буфер то понятно откуда идет вытеснение.
А прикрутить индексы не удалось к запросам?

Comments

[Сергей Болдырев]

Удалось, но см. ответ выше.

Здесь у меня как раз всплывает ещё один вопрос — habrahabr.ru/qa/49454/#comment_232158
На дев-сервере, где тестируется всё это дело, есть ещё несколько БД с InnoDB-таблицами, которые просто не используются.

[Пума Тайланд]

usage where это как раз не использование индексов это бегать по всей таблице, а при большом наборе данных это идиотизм, который выльется в стоящий колом сервер,
ну так уберите эти таблицы для тестов то своих