Содержание


MySQL и PostgreSQL. Часть 2. Конфигурация MySQL

Comments

Серия контента:

Этот контент является частью # из серии # статей:

Следите за выходом новых статей этой серии.

Этот контент является частью серии:

Следите за выходом новых статей этой серии.

1. Как конфигурировать MySQL

Конфигурировать MySQLможно как из командной строки при запуске, так и в конфиг-файле – как правило, это /etc/my.cnf. Чтобы проверить это наверняка, запустите команду:

  # /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'

Конфиг-файл разбит на секции, каждая секция заключена в квадратные скобки. Параметры имеют три области видимости:

  • глобальные;
  • сессионные;
  • для конкретного обьекта.

Некоторые параметры могут входить сразу в несколько зон видимости, например sort_buffer_size может быть сессионным параметром.

Некоторые параметры являются динамическими, т.е. их можно менять, не останавливая сервер, хотя нужно помнить, что динамическая настройка может привести к неожиданным результатам.

2. Настройка памяти

Вначале нужно определить, сколько MySQLвообще может потреблять памяти в системе. Операционной системе тоже нужно оставить память для нормальной работы. Для 32-битного линуксового ядра, вообще говоря, одному процессу, в нашем случае MySQL-серверу, может быть отдано не более 2 ГБ памяти. Многие ограничения в этом плане будут сняты в версии 5.1.

Память в MySQL более всего нужна для кеширования запросов, и для разных движков (engine) она настраивается по-разному, в частности:

  1. кеширование данных для движка MyISAM: данные берутся из .myd-файлов и кешируются операционной системой, а не самим движком, и не настраиваются в конфиге;
  2. кеширование индексов для движка MyISAM (key_buffer) – сюда нужно отдавать до 50% общей памяти, выделенной на весь кеш. По умолчанию в конфиге стоит один key_fuffer, но можно создать свой: key_buffer_1.key_buffer_size = 1G. Можно дать приказ на привязку конкретных таблиц (t1 и t2) к созданному буферу:
             mysql> CACHE INDEX t1, t2 IN key_buffer_1;

    Более того, в конфиг можно добавить опцию init_file = , куда поместить аналогичные команды, и этот sql-файл будет загружен при старте сервера. Мониторить использование этого кеша можно с помощью команд SHOW STATUS и SHOW VARIABLES;

  3. кеш для движка InnoDB (innodb_buffer_pool_size): работает иначе, нежели кеш в MyISAM – сюда, кроме индексов, кешируются данные, буфер вставки, блокировки и другие структуры. Он также используется для кеширования записи. Сюда можно отдать до 80% всей физической памяти.
  4. табличный кеш (table_cache): в нем кешируются данные из файлов .frm, при этом уменьшается число обращений к диску. Здесь нужно найти баланс между числом открытых таблиц и размером самого кеша. В основном это относится к MyISAM.

В исходниках MySQL, в подкаталоге support-files, лежат примеры конфигов для разных моделей памяти: my-huge.cnf.sh, my-large.cnf.sh, my-small.cnf.sh. Фрагменты этих конфигов:

huge:
key_buffer = 384M   
innodb_buffer_pool_size = 384M
table_cache = 512

large:
key_buffer = 256M
innodb_buffer_pool_size = 256M
table_cache = 256

small:
key_buffer = 16K
innodb_buffer_pool_size = 16M
table_cache = 4

3. Настройка дисковой системы

Эти настройки могут существенно повлиять на производительность. Тут сразу возникает дилемма между скоростью записи и надежностью. MyISAM: этот движок по умолчанию сбрасывает закешированные индексы на диск каждый раз, когда происходит операция записи. Если в таблице идет много изменений, имеет смысл сгруппировать их. Это можно сделать с помощью блокировки таблиц (LOCK TABLES), которая временно останавливает запись до разблокировки. Временную блокировку записи можно реализовать также с помощью параметра delay_key_write.

Для базы с небольшими таблицами рекомендуется установить параметр myisam_recover_options – для поиска и восстановления ошибок в случае аварии. Проверка параметра делается из командной строки:

mysql> SHOW VARIABLES LIKE 'myisam_recover_options';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| myisam_recover_options | OFF   | 
+------------------------+-------+

InnoDB: этот движок имеет более эффективную возможность в случае аварий автоматически запускать процесс восстановления при следующем запуске. У него иной механизм работы: вместо постоянного сброса на диск он ведет специальный транзакционный лог. Этот лог имеет фиксированный размер, и при достижении конца изменения сбрасываются на диск – это делает специальный фоновый поток-диспетчер. Размер этого лога контролируется параметром innodb_log_file_size, по умолчанию он равен 5 MБ. Предел равен 4 ГБ, оптимальное значение для экстремальной производительности – 256 MБ. Сброс изменений в лог происходит с помощью буфера, размер которого устанавливается параметром innodb_log_buffer_size, его диапазон желательно варьировать в пределах от 1 до 8 MБ. Сброс буфера в лог и сброс самого лога на диск – это две разных операции. Сброс лога на диск может сопровождаться тем, что операционная система сама может закешировать эту запись, что крайне нежелательно; для предотвращения этого необходимо устанавливать параметр innodb_flush_log_at_trx_commit = 1. В противном случае нужно использовать RAID-контроллер.

InnoDB хранит свое табличное пространство с помощью виртуальной файловой системы, например:

  innodb_data_home_dir = /var/lib/mysql/
  innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G

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

В InnoDB есть так называемый специальный буфер повторной записи – doublewrite buffer, который используется при ошибках записи на диск. Это специальная область в табличном пространстве, представляющая собой копию последних записанных страниц. Иными словами, когда происходит запись данных на диск, они сначала пишутся в этот буфер, а потом уже повторно в основные таблицы. Смысл в том, что в случае ошибки правильные данные в любом случае окажутся либо там, либо там – это проверяется по контрольным суммам. Выключить повторный буфер можно с помощью параметра innodb_doublewrite = 0.

Данные полей, имеющих тип BLOB или TEXT, пишутся сразу на диск без буферизации во временные таблицы, каталог для них настраивается параметром tmpdir. Если есть возможность, эти поля можно сконвертировать в тип VARCHAR. Другой вариант – использовать для хранения BLOB файловую систему tmpfs, в этом случае уже сама операционная система будет буферизовать дисковые операции.

4. Настройка параллелизма (concurrency)

MyISAM: этот движок при удалении записи ее фактически не удаляет, а помечает как удаленную, не создавая дыр, а потом вставляет на ее место новую. Даже если таблица заблокирована, движок может остановить ее чтение и добавить новую запись в конец таблицы. Для вставки новых записей в конфиге есть параметр concurrent_insert. Если он равен 0, при вставке таблица будет блокироваться. Если он равен 1, запись будет добавляться в ''дыру'' таблицы, блокировки не будет. Если он равен 2, запись будет добавляться в конец таблицы без блокировки. С помощью параметра low_priority_updates можно понизить приоритет для INSERT, REPLACE, DELETE, UPDATE по отношению к операции SELECT.

InnoDB: этот движок имеет так называемый планировщик потоков, который с помощью параметра innodb_thread_concurrency может ограничить количество работающих потоков. По умолчанию он равен 0, т.е. нет ограничений. Движок имеет двухфазный механизм распараллеливания потоков, поток может быть переведен в ''спячку'' на innodb_thread_sleep_delay микросекунд (по умолчанию он равен 10000 микросекунд), при этом снижается нагрузка на общесистемный планировщик. Каждый работающий поток имеет определенное число так называемых тикетов, определяющих, сколько ему можно работать. Тикеты настраиваются опцией innodb_concurrency_tickets. Параметр innodb_commit_concurrency определяет число потоков, которые могут коммитить одновременно.

5. SHOW GLOBAL STATUS

Для выяснения узких мест полезно выполнять команду SHOW GLOBAL STATUS:

mysql>  SHOW GLOBAL STATUS;
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Aborted_clients                   | 0        | 
| Aborted_connects                  | 2        | 
| Binlog_cache_disk_use             | 0        | 
| Binlog_cache_use                  | 0        | 
| Bytes_received                    | 658      | 
| Bytes_sent                        | 12538    | 
........

Aborted_clients – зависит от сетевых настроек, в частности, от параметра max_allowed_packet.

Binlog_cache_disk_use и Binlog_cache_use – если их значение велико, нужно увеличить параметр binlog_cache_size.

Bytes_received и Bytes_sent – проблемы могут быть во входящем или исходящем трафике.

Connections – общее число всех коннектов.

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

Key_blocks_used – если он намного меньше, чем параметр конфига key_buffer_size, значит, последний нужно уменьшать.

Open_tables и Opened_tables – их большое значение указывает на то, что нужно увеличить параметр конфига table_cache.

Table_locks_waited – высокое значение этого параметра указывает на проблемы с параллелизмом.

Варианты решения: использование InnoDB, партицирование таблиц, оптимизация запросов.

Заключение

Подводя итог, можно сказать, что MySQL имеет механизм первоначальной базовой настройки на базе конфигурационных файлов. Знание особенностей конфигурации позволяет оптимизировать узкие и далеко не очевидные места. Кеширование запросов является ключом к оптимизации использования памяти. Для эффективной работы дисковой системы необходима правильная буферизация записываемых данных. Для настройки параллелизма нужно найти правильный приоритет между селектами и апдейтами. Все это можно протестировать с помощью команды SHOW GLOBAL STATUS. Конфигурация MySQL – это творческий процесс, от которого зависит дальнейшая жизнь самого сервера.

В статье рассматривались особенности настройки MySQL версий 5.0 и 5.1.

Далее мы перейдем к настройке PostgreSQL.


Ресурсы для скачивания


Комментарии

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

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Open source, Linux
ArticleID=503091
ArticleTitle=MySQL и PostgreSQL. Часть 2. Конфигурация MySQL
publish-date=07272010