Оптимизация работы сервера MySQL с памятью.

🕛 08.02.2008, 18:55

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

1. Кey_buffer является общим для всех потоков. Все остальные буфера выделяются по мере
необходимости.
2. Каждое соединение использует некоторое количество памяти. Это память для стека
(thread_stack), буфер соединения (net_buffer_length) и буфер результата
(net_buffer_length)
3. Для каждого запроса, требующего последовательно сканировать таблицу, выделяется
буфер чтения (record_buffer).
4. Все JOIN запросы выполняются в один проход и для большинства таких запросов нет
необходимости применять вспомогательные таблицы. В основном вспомогательные
таблицы формируются в памяти, но если такая таблица имеет слишком большой размер
записи или используется тип BLOB, то она сохраняется на диске. Если размер
вспомогательной таблицы, которая хранится в памяти, превысит tmp_table_size, то будет
возвращена ошибка table_name is full. Для избежания такой ошибки нужно или увеличить
значение tmp_table_size, или включить опцию SQL_BIG_TABLES (это можно сделать либо
запросом SET SQL_BIG_TABLES=1, либо запускать mysqld с опцией -big-tables). При
включенной опции SQL_BIG_TABLES все вспомогательные таблицы формируются не в
памяти, а на диске.
5. Запросы с сортировкой выделяют в памяти буфер для сортировки (sort_buffer) и
используют один или два временных файла.
6. Таблицы с данными открываются каждым конкурирующим потоком. Индексные файлы
открываются всего один раз, не зависимо от количества потоков, использующих эти файлы.
7. Для таблиц с BLOB-полями буфер автоматически увеличивается до размера самого
большого BLOB-поля.
8. Дескрипторы всех открытых таблиц хранятся в кэше, который работает по принципу FIFO.
Размер кэша определяется переменной table_cache. Если несколько потоков открывают
одну и туже таблицу, то для каждого потока выделяется свой дескриптор таблицы.
9. Команда mysqladmin flash-tables закрывает все таблицы, которые не используются в данный
момент, а все используемые таблицы помечает для закрытия. Такая операция позволяет
освободить неиспользуемую память.

Как работать с таблицами для достижения большей производительности.

1. По возможности все поля декларировать как NOT NULL. Это сделает работу с таблицами
более быстрой и сохранит 1 бит на каждое такое поле.
2. Применять значения по умолчанию (DEFAULT). При вызове запроса INSERT в таблицу
будут записываться только те поля, значения которых отличаются от DEFAULT.
3. Используйте настолько малые типы INT, насколько это возможно. Например, применять
MEDIUMINT намного лучше, чем обычный INT.
4. Если у вас нет записей с переменной длиной (нет ни одного поля с типом VARCHAR,
BLOB или TEXT), то таблица сохраняется в формате «с постоянной длиной записи». Это
несколько расходует память, но намного повышает скорость работы.
5. При использовании нескольких последовательных INSERT запросов, лучше все данные
указать в одном INSERT, чем делать несколько INSERT.
6. При загрузке данных в таблицу лучше использовать LOAD DATA INFILE, чем INSERT,
такой метод в 20 раз быстрее.
7. Для увеличения скорости LOAD DATA INFILE и INSERT нужно увеличить значение
переменной key_buffer.
8. Если ожидается много запросов INSERT или UPDATE, работающих одновременно, то для
большей скорости рекомендуется приметь LOCK TABLES.
9. Время от времени нужно дефрагметировать таблицы. Это делается утилитой isamchk с
опциями -evi.

Форматы таблиц в MySQL

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

Таблицы с фиксированной длиной строки.
Этот формат применяется по умолчанию, если в таблице нет полей с типом VARCHAR, BLOB или TEXT.
Все поля типа CHAR, NUMERIC и DECIMAL дополняются в конце пробелами.
Высокая скорость работы. Легко кэшируются. Легко восстановить после краха, так как все строки имеют постоянную длину. Не требуют реорганизации (с помощью isamchk), до тех пор, пока не будет удалено очень много записей, и вы захотите освободить место на диске. Обычно такие таблицы занимают больше места, чем таблицы с динамической длиной строки.

Таблицы с динамической длиной строки.
Этот формат применяется, если в таблице есть поля с типом VARCHAR, BLOB или TEXT.
Все строки динамические (CHAR хранятся как VARCHAR, кроме тех у которых длина меньше 4). Каждое поле имеет дополнительный бит, который устанавливается, если строковое поле равно «» (пустая строка), или если числовое поле равно 0 (это не то же самое, когда поле может иметь значение NULL). Непустые строки хранятся в виде {ДЛИНА_СТРОКИ} {СОДЕРЖАНИЕ_СТРОКИ} Обычно такие таблицы занимают намного меньше места, чем таблицы с фиксированной длиной. Ожидаемая длина строки вычисляется по формуле: 3+(количество полей + 7)/8+(количество полей типа CHAR)+(размер числовых типов в бинарном виде)+(длина всех строк)+(количество NULL-полей + 7)/8.

Сжатые таблицы.
Таблицы «только-для-чтения», их можно получить с помощью утилиты pack_isam. Эту утилиту получают все покупатели, которые приобрели расширенную поддержку MySQL. Основная характеристика - занимают мало места.

Использование индексов в MySQL.

Все индексы (PRIMARY, UNIQUE и INDEX) хранятся в B-дереве. В строковых типах автоматически происходит сжатие начальных и конечных пробелов. Индексы используются для:
Быстрого поиска записей по условию WHERE;
Для объединения таблиц с посредством JOIN;
Поиска MAX() и MIN() значений для ключевых полей;
Для сортировки и группировки таблиц (директивы ORDER BY и GROUP BY);
Для извлечения данных не из таблицы с данными, а из индексного файла. Это возможно
только в некоторых случаях, например, когда все извлекаемые поля проиндексированы.

Рассмотрим следующий запрос SELECT:

SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

Если таблица имеет множественный индекс (col1,col2), то соответствующие записи будут
выбраны напрямую. Если существуют только одиночные индексы для col1 и col2, то оптимизатор
сначала решит, при использовании какого индекса, количество возвращаемых записей будет
меньше, а затем из этих записей будет произведена выборка по другому условию.
Если таблица имеет множественный индекс, то любой «левый префикс» этого индекса
может использоваться для оптимизации запроса. Например, если есть индекс (col1, col2, col3), то
можно считать, что существуют индексы (col1); (col1,col2); (col1,col2,col3).

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

mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

Если есть индекс (col1,col2,col3), то только в первом запросе будет использоваться индекс.
Хотя второй и третий запросы содержат столбцы, которые присутствуют в индексе, но (col2) и
(col2,col3) не являются левыми частями множественного индекса, и поэтому при выполнении этих
запросов индекс применятся не будет.

MySQL также использует индексы для LIKE операций, если аргумент LIKE является строковой константой и при этом не начинается с символа шаблона (% или _). Например, следующие SELECT запросы используют индекс для key_col:

mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

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

mysql> select * from tbl_name where key_col LIKE "%Patrick%";
mysql> select * from tbl_name where key_col LIKE other_col;

В первом из этих запросов аргумент после LIKE начинается c символа шаблона, а во
втором аргумент не является константой.

Общие рекомендации по повышению производительности.

1. Запускать mysqld с правильно подобранными опциями.
2. Для ускорения SELECT запросов построить индексы для тех полей, которые участвуют в
условии WHERE.
3. Оптимизировать типы полей. По возможности использовать NOT NULL.
4. В MySQL применяется два способа блокировки таблиц (lock table) - внутренняя и внешняя
блокировки. Внутренняя блокировка позволяет делать операции по изменению/извлечению
данных атомарными (не конфликтующими с другими пользователями). Внешняя блокировка
применяется для одновременного доступа нескольких MySQL серверов к одним и тем же базам
данных, а также внешняя блокировка позволяет запускать isamchk без остановки MySQL. Чтобы
запретить использование внешней блокировки нужно запускать mysqld с опцией -skip-locking.
Запрет внешней блокировки существенно повысит скорость работы, но при этом перед
запуском isamchk нужно предварительно сбросить все данные на диск командой mysqladmin
flush-tables. Также при запрете внешней блокировки нельзя будет использовать несколько
серверов для работы с теми же базами данных.
5. Задание прав доступа на конкретную таблицу или поле снижает производительность.