Юникс и другие

Чем больше я работаю над оптимизацией и повышением эффективности работы MySQL, тем меньше я доверяю здравому смыслу авторов документации и стараюсь больше полагаться на собственные исследования и опыт. Совсем недавно я писал о неожиданных результатах в повышении эффективности сортировки, а сегодня обнаружил, что и выбор значения для read_buffer_size может быть не столь тривиальной проблемой.

Что обычно говорят про настройку этого параметра: если вы хотите ускорить поиск в больших таблицах, увеличьте значение этого параметра. В стандартном my.cnf для систем с большим количеством памяти это значение установлено в 1М, дефолтное значение - 128К. Некоторые администраторы, имея большое количество оперативной памяти и несколько соединений устанавливают это значение в 32М, надеясь повысить производительность. Давайте посмотрим, оправдано ли это?

Чтобы проверить это, я создал таблицу с простой структурой:

SQL:
mysql> SHOW CREATE TABLE dt2 \G
*************************** 1. row ***************************
TABLE: dt2
CREATE TABLE: CREATE TABLE `dt2` (
`grp` int(10) UNSIGNED NOT NULL,
`slack` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row IN SET (0.00 sec)

Таблица заполнена 75М строк и занимает 4GB. Система запущена на Fedora Core i686. Характеристики: 2 процессора Xeon, 2 GB RAM и два жестких диска в RAID0.

Я использовал следующие запросы для проверки поиска в таблице:

SQL:
mysql> SELECT count(*) FROM dt2 WHERE slack LIKE "a%";
+----------+
| count(*) |
+----------+
|  4705992 |
+----------+
1 row IN SET (51.77 sec)

Вот результаты, которые я получил:
read_buffer_size и эффективность поиска:

8200 байт - минимальное значение для read_buffer_size, поэтому мы начали с него.
Как вы можете видеть, результаты двольно неожиданные. Производительность действительно увеличивается на несколько процентов при увеличении значения вплоть до 128К. Но при последующем увеличении она резко падает. И продолжает снижаться по мере увеличения значения параметра до 32М.

Почему это происходит? У меня не было достаточного количества времени, для выяснения точных причин подобного поведения. Быть может, операционная система разбивает большие запросы на несколько маленьких и их обрабатывает более медленное устройство, быть может причина в чем-то другом. Но факт остается фактом - слишком большое значение параметра read_buffer_size способно снизить эффективность запросов. (Не так давно я уже писал об этом)

Давайте проведем еще один тест для небольших таблиц (умещающихся в кеше ОС):
read_buffer_size и кэш в памяти:

Как вы видите, разница в процентах уменьшилась. Теперь она составляет всего 10% между наилучшим и наихудшим значениями. Но пиковые пороги остаются теми же: 128К и 32М. Значит дело не в разбивке запроса, по крайней мере не только в этом.

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

В этом тестировании не учитывается степень распараллеливания - маленькие буферы, возможно, были бы более эффективными. Все необходимо испытывать на реальном тестировании.

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

Оригинал: http://www.mysqlperformanceblog.com/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/