Какое значение оптимально для read_buffer_size?

Чем больше я работаю над оптимизацией и повышением эффективности работы 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 и эффективность поиска:

read_buffer_size Time (sec)
8200 45.2
16K 44.8
32K 45.6
64K 43.4
128K 43.0
256K 51.9
512K 60.8
2M 65.2
8M 66.8
32M 67.2

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

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

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

read_buffer_size Time (sec)
8200 4.15
16K 4.15
32K 4.12
64K 4.11
128K 4.11
256K 4.12
512K 4.25
2M 4.49
8M 4.54
32M 4.58

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

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

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

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

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


 

Система Orphus

 


 

Comments: 4

  1. \')/* April 19th, 2009 at 8:26 am

    ekibastos…

    ekibastos…

  2. dan1005 June 10th, 2009 at 12:52 pm

    А если провести проверку не одним запросом, а потоком запросов, типа:


    SQL:
    mysql> SELECT n1,n2,n3.. FROM dt2 WHERE n1='x' and n2='y' and n3='z'...;

    Как тогда отразится размер буфера на производительность?

  3. James June 17th, 2009 at 2:53 pm

    Почитаешь……и на душе становится тепло и солнечно…

  4. Timothy July 17th, 2009 at 10:55 am

    Детальнее некуда расписыват, то что надо…

Add a Comment