MySQL - оптимизация сервера (часть 2)

Общие параметры конфигурационного файла my.cnf


max_connect_errors - максимальное кол-во ошибок, при достижении которого хост с которого они шли будет заблокирован и не сможет подключиться к серверу. Сбросить накопленное кол-во ошибок для всех хостов можно с помощью команды flush hosts.

binlog_cache_size - размер кэша, используемого для хранения SQL-операторов бинарного журнала регистраций во время транзакций. Этот кэш выделяется для каждого клиента, если сервер использует любой из транзакционных движков, например InnoBD и на сервере включена поддержка бинарного журнала регистраций.

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

sort_buffer_size - размер буфера, выделяемого на поток при выполнении операций сортировки. Для ускорения операций ORDER BY, GROUP BY рекомендуется увеличить данное значение.
Если отсортированные данные не помещаются в текущий буфер сортировки, то данные начинают использовать жесткий диск. Если вы видите большое значение sort_merge_passes в выводите show global status, то надо задуматься об увеличении sort_buffer_size.
Максимальный объем буфера сортировки 4Гб.

join_buffer_size - размер буфера использующегося при операциях объединения таблиц (при этом объединение происходит без использования индексов). Данные объединения не очень хорошо сказываются на производительности сервера и увеличив join_buffer_size мы можем снизить влияние таких объединений на производительность.
Максимальное значение 4Гб.

thread_cache_size - регулирует кол-во потоков, которые сервер должен поместить в кэш для повторного использования. После отключения клиента, потоки не удаляются а помещаются в кэш, как только MySQL серверу понадобятся новые потоки, он берет их из кэша. Это значение может быть увеличено для повышения производительности, если у вас много новых подключений.

thread_concurrency - кол-во одновременно запускаемых потоков, для Linux систем это кол-во CPU * кол-во ядер в каждом CPU.

ft_min_word_len - минимальная длина слова для включения в индекс fulltext. При изменении данного значения, необходимо заново перестроить ваши fulltext индексы, это можно сделать с помощью команды repair table tbl_name quick или с помощью утилиты mysqlrepair, так mysqlrepair -u root -p --all-databases.

tmp_table_size - максимальный размер временных таблиц, расположенных в оперативной памяти. Если таблица не помещается в рамки указанного значения tmp_table_size, то для работы с ней начинает использоваться диск. Данное ограничение применяется для одной таблицы.

Параметры относящиеся к MyISAM


read_buffer_size - размер буфера, выделяемого потоком для каждой сканируемой таблицы. Используется во время полного сканирования таблиц. Максимальное значение 2Гб.

read_rnd_buffer_size - размер буфера, выделяемого для чтения строк после сортировки, что-бы избежать повторного поиска на диске. Увеличив размер данного буфера, вы значительно улучшите производительность order by. Данный буфер выделяется для каждого клиента.

bulk_insert_buffer_size - MyISAM использует специальный древовидный кэш, чтобы быстрее выполнять массивные insert'ы (такие как INSERT ... SELECT, INSERT ... VALUES (...), (...), ... и LOAD DATA INFILE). Эта переменная ограничивает размер древовидного кэша на каждый поток. При выставлении 0 значения, полностью его отключает. Для оптимальное производительности не устанавливайте его значение больше, чем значение key_buffer_size.
Этот буфер выделяется в момент обнаружения массивной вставки.

myisam_sort_buffer_size - выделяется на поток, используется данный буфер при сортировке MyISAM индексов во время восстановлении индексов, ремонте, оптимизации таблиц или изменении их структуры с помощью оператора alter table, а так же при выполнении load data infile в пустую таблицу.

myisam_max_sort_file_size - размер временного файла, использующегося при восстановлении индексов MyISAM, при использовании операторов repair table, alter table или load data infile. Если размер необходимого файла больше значения данной переменной, то для создания индекса будет использоваться кэш ключей, что значительно медленней.

myisam_repair_threads - указывает кол-во потоков при ремонте таблицы. Увеличение этого значения имеет смысл, если у вас несколько процессоров и большое кол-во оперативной памяти.

Параметры относящиеся к InnoDB


innodb_additional_mem_pool_size - дополнительный пул памяти, который используется InnoDB для хранения метаданных, чем больше у вас таблиц, тем большее значение innodb_additional_mem_pool_size надо выставлять. Если InnoDB потребуется больше памяти, чем указано в данном параметре, то она начнет выделять память из ОС, это происходит достаточно быстро, поэтому менять данный параметр в большинстве случаев не надо.

innodb_buffer_pool_size - InnoDB в отличие от MyISAM использует буферный пул для кэширования индексов и строковых данных. При достаточном объеме памяти увеличение буфера InnoDB может снизить загруженность диска при доступе к табличным и индексным данным. На отдельно стоящем выделенном Mysql сервере можно установить этот параметр в 80% от общего объема физической памяти сервера.

innodb_file_io_threads - кол-во файловых потоков ввода/вывода для использования асинхронных операций. Для *nix систем жестко задано значение в 4, увеличение не даст эффекта.

innodb_thread_concurrency - максимальное кол-во одновременно действующих потоков для InnoDB. Как только кол-во потоков достигает значения указанного в данном параметре, все дополнительные потоки помещаются в FIFO очередь.
Правильное значение для этого параметра сильно зависит от аппаратной конфигурации сервера и рабочей нагрузки. Рекомендуемое значение это значение в два раза большее кол-ва CPU ядер, плюс кол-во жестких дисков. Начиная с версии 5.1.12 можно вообще отключить данный параметр выставив его значение в 0, это разрешит InnoDB использовать то кол-во потоков, которое ей потребуется.

innodb_flush_log_at_trx_commit - крайне желательно выставлять значение в 1. Возможные значения в 0 и 2 могут снизить нагрузку на дисковую подсистему сервера, но этого делать не рекомендуется, так как перестанет соблюдаться ACID.

innodb_log_buffer_size - размер буфера для хранения информации о каждой транзакции. После выполнения транзакции данные о ней сбрасываются на диск. Для больших транзакций, выходящих за пределы этого буфера есть смысл увеличить значение данного параметра, чтобы во время выполнения транзакции данные не сбрасывались на диск по несколько раз подряд, из-за того что они не помещаются в буфер.
Возможное значение от 1 до 8Мб.

innodb_log_group_home_dir - движок InnoDB ведет свои собственные журналы, которые создаются во время запуска сервера, если их еще не существует. По умолчанию эти журналы создаются в каталоге данных, их имена начинаются с ib_, пример файлов


-rw-rw---- 1 mysql mysql 5.0M Dec 23 02:43 /var/lib/mysql/ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Oct 31 13:23 /var/lib/mysql/ib_logfile1

и данный параметр используется для установки пути к каталогу в котором InnoDB будет записывать свои регистрационные журналы.

innodb_log_file_size - размер каждого файла-журнала в группе журналов. Когда регистрационный журнал переполняется, InnoDB сохраняет буфер на диске. Увеличение размера файла регистрационного журнала уменьшает периодичность переполнения журнала и соответственно периодичность с которой происходит обновление, тем самым снижая нагрузку на дисковую подсистему сервера. При этом, чем больше размер регистрационного журнала, тем больше и время восстановления после сбоя.

Общий размер должен быть не больше 4Гб, по умолчанию 5Мб. Общий размер считается как произведение значений innodb_log_file_size и innodb_log_files_in_group.

innodb_log_files_in_group - общее кол-во файлов журналов в группе журналов. Значение в 2-3 будет хорошим.

innodb_max_dirty_pages_pct - максимальный процент так называемых грязных страниц в буферном пуле InnoDB. Если оно будет достигнуто, то сервер попытается их сбросить, чтобы не закончились все чистые страницы. Диапазон значения от 0 до 100, по умолчанию 90.

innodb_lock_wait_timeout - время в секундах на протяжении которого транзакция InnoDB может ожидать блокировку до того, как будет произведен ее откат. InnoDB автоматически обнаруживает взаимные блокировки транзакций (deadlocks) в своей таблице блокировок и выполняет их откат. Если вы используете команду lock tables или работаете с другими движками с транзакциями, отличными от InnoDB, то в случае взаимной блокировки InnoDB не сможет ее обнаружить. По умолчанию данный параметр равен 50.

innodb_data_file_path -  InnoDB по умолчанию хранит данные в одном или нескольких файлах с данными формирующими табличное пространство. Если у вас есть один логический диск для хранения данных, то размещение одного автоматически расширяющегося файла на нем будет достаточно.
При задании объема файла можно указывать суффикс в конце, например K (килобайты), M (мегабайты), G (гигабайты). Если вообще не указывать параметр innodb_data_file_path в конфиг.файле my.cnf, то по умолчанию создастся автоматически расширяющийся 10Мб файл данных с именем ibdata1

-rw-rw---- 1 mysql mysql 10M Dec 23 06:20 /var/lib/mysql/ibdata1

это тоже самое что и innodb_data_file_path = ibdata1:10M:autoextend

Синтаксис описания файла:

path:size
path:size:autoextended
path:size:autoextended:max:maxsize

autoextended - придает файлу свойство автоматического расширения, т.е. когда файл заполняется, InnoDB движок автоматически увеличивает его размер. По умолчанию при авторасширении файлы увеличиваются на 8Мб. Для того чтобы задать другое приращение, используется параметр innodb_autoextend_increment

maxsize - максимальный размер до которого может расширяться файл

Вышеуказанные атрибуты autoextended и maxsize могут быть использованы только для последнего файла данных в параметре innodb_data_file_path

Примеры:

innodb_data_file_path = ibdata1:50M;ibdata2:50M:autoextend - будет создан файл ibdata1 фиксированного размера в 50Мб и автоматически расширяющийся файл ibdata2 размером 50Мб

innodb_data_file_path=ibdata1:10M:autoextend:max:500M - будет создан единственный автоматически расширяющийся файл данных ibdata1 размером 10Мб, который сможет разрастись максимум до 500Мб

Ниже указан пример более интересного варианта, в каталогах /dr2 и /dr3 примонтированы два дополнительных жестких диска на считая основного. Таким образом мы разнесли наши файлы данных и файл-журналов на разные физические диски, что может дать ощутимый прирост производительности Mysql сервера. Кроме того это придает некую гибкость, например можно вынести файл-журналов на быстрый SSD-диск или поэкспериментировать со сменой файловой системы на дополнительных дисках.
Значение innodb_data_home_dir мы задали пустым для того чтобы иметь возможность использовать абсолютные пути в параметрах innodb_data_file_path и innodb_log_group_home_dir.

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
innodb_log_group_home_dir = /dr3/iblogs

и помним что InnoDB заполняет файлы данных поочередно, начиная с первого файла данных

innodb_data_home_dir - общая часть пути к каталогу всех файлов данных InnoDB. Если он не указан, то будет использоваться путь по умолчанию, а если указать пустую строку в качестве его значения, то в innodb_data_file_path можно будет использовать абсолютные пути к файлам.

MySQL - файлы таблиц

MyISAM таблицы


MyISAM таблицы представляют собой файлы .frm, .MYD, .MYI. Например таблица с именем mytable будет выглядеть в виде файлов

mytable.frm - файл описания в котором хранится структура таблицы
mytable.MYD - файл данных в котором хранится содержимое таблицы
mytable.MYI - файл индексов, который содержит информацию обо всех индексах, представленных в данной таблице

Пример с сервера:

-rw-rw---- 1 mysql mysql    0 Dec 13 05:35 ofGroupProp.MYD
-rw-rw---- 1 mysql mysql 1.0K Dec 13 05:35 ofGroupProp.MYI
-rw-rw---- 1 mysql mysql 8.5K Dec 13 05:35 ofGroupProp.frm
-rw-rw---- 1 mysql mysql    0 Dec 13 05:35 ofGroupUser.MYD
-rw-rw---- 1 mysql mysql 1.0K Dec 13 05:35 ofGroupUser.MYI
-rw-rw---- 1 mysql mysql 8.5K Dec 13 05:35 ofGroupUser.frm
-rw-rw---- 1 mysql mysql   52 Dec 13 05:35 ofID.MYD
-rw-rw---- 1 mysql mysql 2.0K Dec 13 05:36 ofID.MYI
-rw-rw---- 1 mysql mysql 8.4K Dec 13 05:35 ofID.frm

InnoDB таблицы


Уникальной привязки каждой таблицы к определенным файлам нет, кроме файла описания .frm, размещенного в каталоге, к которому принадлежит таблица.
InnoDB можно настроить на представление содержимого баз данных с помощью табличных пространств двух типов

Общее табличное пространство


Тип табличного пространства который состоит из одного или нескольких больших файлов, расположенных в каталоге данных. Объем этих файлов равен сумме размеров индивидуальных файлов. При таком режиме единственным файлом, имеющим отношение к определенной таблице, является файл с расширением .frm.
Данный режим используется InnoDB движком по умолчанию.

Индивидуальное табличное пространство


Есть возможность настроить InnoDB таким образом, чтобы на одну таблицу приходилось по одному табличному пространству (с помощью параметра innodb_file_per_table = 1 в конфиг.файле my.cnf, перед включением данного параметра обязательно выполните бэкап всех текущих баз данных!). В таком случае каждой таблице InnoDB будет соответствовать по два файла в каталоге базы данных. Это файл .frm и файл .ibd, содержащие данные и индексы этой таблицы.

Пример с сервера:

-rw-rw---- 1 mysql mysql 8.5K Oct 31 13:35 bugreport.frm
-rw-rw---- 1 mysql mysql  96K Oct 31 13:36 bugreport.ibd
-rw-rw---- 1 mysql mysql 8.6K Oct 31 13:35 channels.frm
-rw-rw---- 1 mysql mysql  96K Oct 31 13:36 channels.ibd

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

Так же в независимости от используемого механизма хранения и его настроек в каталоге данных каждой базы данных есть файл db.opt, этот файл содержит стандартную кодировку и порядок сортировки.

MySQL - оптимизация сервера

При оптимизации Mysql со стороны параметров сервера в первую очередь необходимо отталкиваться от того что доступ к данным, находящимся в памяти, осуществляется на порядок быстрее, чем к данным на жестком диске. Так же необходимо хранить данные находящиеся в оперативной памяти как можно дольше, тем самым мы снижаем кол-во обращений к жестким дискам сервера.


Кэш-память таблиц

Используется для хранения информации об открытых таблицах. Ее объем можно регулировать с помощью параметра table_cache в конфиг.файле Mysql сервера (начиная с MySQL версии 5.1, данный параметр называется table_open_cache).
Table_cache это максимальное кол-во одновременно открытых файлов (таблиц), которое может себе позволить Mysql сервер (так же надо помнить о максимальном кол-ве открытых файловых дескрипторов накладываемых со стороны ОС). Если мы выставили параметр max_connections в 150, то желательно и table_cache подтянуть до такого же значения в 150.

Эффективность кэш-памяти таблиц можно наблюдать через opened_tables в show status.

mysql> show status like 'opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 0     |
+---------------+-------+
1 row in set (0.00 sec)

или так

tsyrenov:~# mysqladmin status -p

Enter password:

Uptime: 480602  Threads: 5  Questions: 107079  Slow queries: 0  Opens: 118  Flush tables: 1  Open tables: 4  Queries per second avg: 0.223

Если значение opened_tables небольшое в процессе работы Mysql сервера, то как правило это хороший признак, если наоборот, то требуется и дальше подбирать параметр table_cache (как правило просто в сторону увеличения). Вообщем увеличение размера кэш-памяти таблиц позволяет уменьшить кол-во операций открытия/закрытия таблиц, что положительно сказывается на производительности.

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

Вручную очистить кэш можно с помощью команд mysqladmin refresh или flush_tables;

Ключевой буфер

Используется для хранения индексов. В работе MyISAM таблиц используется для выборок и сортировок, осуществляемых с применением индекса и для операций создания и модификации индекса. Регулируется с помощью параметра key_buffer_size (по умолчанию 8Мб, максимальное значение 4Гб). Чем больше значение, тем лучше, так как сервер сможет одновременно хранить больше индексных блоков в оперативной памяти, тем самым разгружая жесткие диски сервера, так как в процессе работы сервера, вероятность обнаружения ключевых значений в ОЗУ становится больше.
Если работа сервера упирается в производительность Mysql сервера и при этом у нас есть приличное кол-во свободной оперативной памяти, то мы первым делом увеличиваем key_buffer_size.

Для InnoDB таблиц данное значение регулируется с помощью innodb_buffer_pool_size.

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

Это позволяет выделить определенную область кэш-памяти только для работы с определенной таблицей. После загрузки ключей в таблицу мы практически исключаем необходимость в обращении к жесткому диску сервера, кроме этого, индексы никогда не будут удалены из кэш-памяти.

В данном примере мы создадим кэш-память индекса ofUserFlag_sTime_idx, таблицы ofUserFlag, базы данных Polygon. Размер индекса будет в 10Мб.

mysql> set global ofUserFlag_sTime_idx.key_buffer_size = 10240*10240;

Query OK, 0 rows affected (0.01 sec)

(для автоматического создания данного кэша при запуске MySQL сервера, надо добавить в my.cnf ofUserFlag_sTime_idx.key_buffer_size = 10М)

Привязываем кэш-память индекса целевой таблице ofUserFlag

mysql> cache index ofUserFlag in ofUserFlag_sTime_idx;

+--------------------+--------------------+----------+----------+

| Table              | Op                 | Msg_type | Msg_text |

+--------------------+--------------------+----------+----------+

| polygon.ofUserFlag | assign_to_keycache | status   | OK       |

+--------------------+--------------------+----------+----------+

1 row in set (0.00 sec)

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

mysql> load index into cache ofUserFlag;

+--------------------+--------------+----------+----------+

| Table              | Op           | Msg_type | Msg_text |

+--------------------+--------------+----------+----------+

| polygon.ofUserFlag | preload_keys | status   | OK       |

+--------------------+--------------+----------+----------+

1 row in set (0.00 sec)

Кэш-память запросов

Кэш-память запросов (параметры query_cache_type, query_cache_size, query_cache_limit) используется для повторной обработки select запросов, предназначена для ускорения данных запросов.

После первой обработки оператора select сервер запоминает текст запроса и возвращаемый им результат. В следующий раз сервер анализирует запрос не выполняя его повторно, вместо этого сервер выбирает результат такого запроса непосредственно из кэш-памяти запросов и возвращает его клиенту.

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

При обновлении таблицы любые закэшированные запросы в которых сделана ссылка на эту таблицу, автоматически удаляются. Таким образом избегается возвращение устаревших данных.

Query_cache_type - определяет режим работы кэш-памяти запросов, ниже указаны ее возможные значения

0 - не кэшировать результаты выполнения запросов

1 - кэшировать все запросы, за исключением таблицы, которые начинаются с select sql_no_cache

2 - кэшировать по запросу только те запросы, которые начинаются с select sql_cache

Query_cache_size - определяет размер кэш-памяти запросов

Query_cache_limit - определяет максимальный кэширующий размер результата запросов, если размер превышен, то данный результат запроса кэшироваться не будет

Кэширование запросов в общем положительная функция, но если целевая таблица модифицируется очень часто, то есть смысл отключить кэширование запросов для нее с помощью sql_no_cache в query_cache_type. Так как при частом изменении данных в этой таблице, все закэшированные запросы для нее быстро теряют актуальность.

MySQL - Explain & Procedure analyse

EXPLAIN

Оператор Explain позволяет получить подробную информацию о том как будет выполняться тот или иной SQL запрос, он выведет описание индексов, которые будут использованы при сканирования таблицы, типы используемых объединений, данные о кол-ве сканируемых строк и т.д. При этом сам SQL запрос реально выполнен не будет.

В примере будем использовать Mysql базу данных PowerDNS с тестовыми доменами.

mysql> desc records;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| domain_id   | int(11)      | YES  | MUL | NULL    |                |
| name        | varchar(255) | YES  | MUL | NULL    |                |
| type        | varchar(6)   | YES  |     | NULL    |                |
| content     | varchar(255) | YES  |     | NULL    |                |
| ttl         | int(11)      | YES  |     | NULL    |                |
| prio        | int(11)      | YES  |     | NULL    |                |
| change_date | int(11)      | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql> show index from records;

+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table   | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| records |          0 | PRIMARY        |            1 | id          | A         |         106 |     NULL | NULL   |      | BTREE      |         |

| records |          1 | rec_name_index |            1 | name        | A         |         106 |     NULL | NULL   | YES  | BTREE      |         |

| records |          1 | nametype_index |            1 | name        | A         |         106 |     NULL | NULL   | YES  | BTREE      |         |

| records |          1 | nametype_index |            2 | type        | A         |         106 |     NULL | NULL   | YES  | BTREE      |         |

| records |          1 | domain_id      |            1 | domain_id   | A         |         106 |     NULL | NULL   | YES  | BTREE      |         |

+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

5 rows in set (0.00 sec)

Выведем все записи закрепленные в базе за domain_id 26, через Explain, чтобы увидеть какие именно индексы будут использоваться при выполнении выборки

mysql> explain select * from records where domain_id = '26';

+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+

| id | select_type | table   | type | possible_keys | key       | key_len | ref   | rows | Extra       |

+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+

|  1 | SIMPLE      | records | ref  | domain_id     | domain_id | 5       | const |   13 | Using where |

+----+-------------+---------+------+---------------+-----------+---------+-------+------+-------------+

1 row in set (0.00 sec)

Type - бывает значений all (обозначает просмотр всех строк, соответственно индекс использоваться не будет), ref, range

Possible_keys, key - отображает столбец, который может использовать индекс

Rows - кол-во строк, которые требуется просмотреть (чем меньше, тем лучше)

Таким образом при добавлении в таблицу новых индексов с помощью оператора Explain можно проверять эффективность индексов.

PROCEDURE ANALYSE

Функция Procedure analyse выводит предложения по оптимальным типам столбцов таблицы

Например в этой же базе данных PowerDNS есть столбец type, который хранит тип ресурсной записи DNS, проверим его с помощью данной функции

mysql> select type from records procedure analyse()\G

*************************** 1. row ***************************

             Field_name: pdns.records.type

              Min_value: A

              Max_value: TXT

             Min_length: 1

             Max_length: 4

       Empties_or_zeros: 0

                  Nulls: 0

Avg_value_or_avg_length: 1.8302

                    Std: NULL

      Optimal_fieldtype: ENUM('A','AAAA','MX','NS','PTR','SOA','TXT') NOT NULL

1 row in set (0.00 sec)

Мы видим что procedure analyse проанализировав содержимое данных в столбце type предлагает нам для данного столбца использовать тип ENUM (это тип, строки которого имеют ограниченный набор строго определенных значений). Данные такого типа обрабатываются очень и очень быстро.

Смотрим текущий тип столбца type, он сейчас соответствует varchar(6).

mysql> desc records;

+-------------+--------------+------+-----+---------+----------------+

| Field       | Type         | Null | Key | Default | Extra          |

+-------------+--------------+------+-----+---------+----------------+

| id          | int(11)      | NO   | PRI | NULL    | auto_increment |

| domain_id   | int(11)      | YES  | MUL | NULL    |                |

| name        | varchar(255) | YES  | MUL | NULL    |                |

| type        | varchar(6)   | YES  |     | NULL    |                |

| content     | varchar(255) | YES  |     | NULL    |                |

| ttl         | int(11)      | YES  |     | NULL    |                |

| prio        | int(11)      | YES  |     | NULL    |                |

| change_date | int(11)      | YES  |     | NULL    |                |

+-------------+--------------+------+-----+---------+----------------+

8 rows in set (0.00 sec)

и если предположить что у нас все ресурсные DNS записи, которые будут добавляться в таблицу будут семи самых распространенных значений ('A','AAAA','MX','NS','PTR','SOA','TXT'), то можно смело переводить столбец type в ENUM, что даст прирост производительности.

MySQL индексирование таблиц

При низкой производительности MySQL базы данных нет никакого смысла менять параметры в конфиге Mysql сервера, настраивать репликации, переносить файлы базы на размещение в разделах с более быстрой файловой системой или на более быстрый RAID массив и т.д., если в таблицах базы не проставлены индексы. Добавление индексов это первое что требуется выполнить для улучшения производительности базы и только потом все остальное.

Общая информация по индексам

1. Индексы можно проставлять по одному столбцу или по комбинации столбцов
2. Режим содержания для индекса это или режим повторяющихся значений, или режим только уникальных значений
3. Одна таблица может иметь более одного индекса, использующего различные столбцы таблицы
4. Строковый тип, кроме ENUM и SET можно индексировать по первым некотором кол-ве символов слева. Нельзя создавать индексы по столбцам типа BLOB и TEXT, пока не задана длина префикса. Индексирование только префикса столбца позволяет уменьшить индекс и ускорить доступ к нему
5. Индекс таблиц типа InnoDB так же может строиться и на внешних ключах, т.е. значения в индексе должны соответствовать значениям, представленным в другой таблице. MyISAM таблицы не поддерживают внешние ключи, зато можно использовать индекс FULLTEXT, которые нельзя использовать в InnoDB таблицах

Примеры добавления индексов

Добавление обычного индекса (дублирование записей в обычном индексе разрешено):

ALTER TABLE tbl_name ADD INDEX index_name (index_columns);

где tbl_name - имя таблицы, index_name - имя индекса (указывать не обязательно), index_columns - список индексируемых столбцов

Пример добавления обычного индекса к уже имеющейся таблице:

mysql> alter table users add index index_for_name (`name`);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from users;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          1 | index_for_name |            1 | name        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

Добавление уникального индекса (соответственно дублирование запрещено):

ALTER TABLE tbl_name ADD UNIQUE index_name (index_columns);
ALTER TABLE tbl_name ADD PRIMARY KEY index_name (index_columns);

Индекс типа PRIMARY KEY не может содержать пустых значений, а индекс UNIQUE пару пустых значений содержать может. Это все связано с тем что нельзя сравнивать между собой два пустых значения.

Пример добавления PRIMARY KEY к уже созданной таблице:

mysql> ALTER TABLE account_access ADD PRIMARY KEY (`id`, `RealmID`);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from account_access;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| account_access |          0 | PRIMARY  |            1 | id          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| account_access |          0 | PRIMARY  |            2 | RealmID     | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

Добавление индексов по префиксу столбца строкового типа

Пример добавления индекса по префиксу при создании таблицы:

mysql> create table users (name char(50) not null, address binary(100) not null, index index_for_name (name(20)), index index_for_address (address(25)));
Query OK, 0 rows affected (0.01 sec)

mysql> show index from users;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          1 | index_for_name    |            1 | name        | A         |        NULL |       20 | NULL   |      | BTREE      |         |
| users |          1 | index_for_address |            1 | address     | A         |        NULL |       25 | NULL   |      | BTREE      |         |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

В итоге создался индекс по первым 20 символам столбца CHAR и первым 25 байтам столбца BINARY

Пример добавления индекса по префиксу к уже созданной таблице:

mysql> alter table users add index index_for_name (`name`(20));
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from users;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          1 | index_for_name |            1 | name        | A         |        NULL |       20 | NULL   |      | BTREE      |         |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

Примеры удаления индексов

Удаление индексов

ALTER TABLE tbl_name DROP INDEX index_name;

Пример удаления обычного индекса

mysql> show index from users;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          1 | index_for_name    |            1 | name        | A         |        NULL |       20 | NULL   |      | BTREE      |         |
| users |          1 | index_for_address |            1 | address     | A         |        NULL |       25 | NULL   |      | BTREE      |         |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

mysql> alter table users drop index index_for_address;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from users;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          1 | index_for_name |            1 | name        | A         |        NULL |       20 | NULL   |      | BTREE      |         |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

ALTER TABLE tbl_name DROP PRIMARY KEY; 

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

mysql> show index from account_access;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| account_access |          0 | PRIMARY  |            1 | id          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| account_access |          0 | PRIMARY  |            2 | RealmID     | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

mysql> alter table account_access drop primary key;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from account_access;
Empty set (0.00 sec)

Так же надо помнить что при удалении столбца из таблицы, столбец удалится и из индекса. Удалив все индексируемые столбцы из таблицы удалится и весь индекс.

Использование raw раздела для InnoDB

Преимущество использования raw раздела:

• Табличное пространство может занимать все пространство в разделе, без ограничений накладываемых в виде максимального размера файла в ОС
• Снижение фрагментации
• Снижение нагрузки из-за отсутствия уровня управления ФС

(ОС: Debian 6.0 x64)

Создаем целевой раздел, например логический том объемом в 984Мб

lvcreate -L984M -n innodb_data main

Выполняем бэкап всех баз данных, удаляем все БД с InnoDB таблицами и прописываем в конфиг Mysql сервера следующее

[mysqld]

innodb_data_home_dir = 

innodb_data_file_path = /dev/main/innodb_data:984Mnewraw

добавляем системного пользователя mysql в группу disk, чтобы Mysql сервер мог получить доступ к raw разделу

gpasswd -a mysql disk

останавливаем сервер, удаляем все файлы ibdata и ib_logfile, запускаем Mysql сервер, при запуске сервера раздел /dev/main/innodb_data проинициализируется, после чего обратно выключаем Mysql сервер

во время инициализации в логах будет что то подобное (init скрипт при запуске Mysql может вернуть ошибку, но на это не надо обращать внимания, так как сервер все равно запуститься и будет подготавливать себе raw раздел, немного подскочит нагрузка на дисковую подсистему, ждем пока завершится инициализация)

Oct 31 12:55:46 srv mysqld: InnoDB: The first specified data file /dev/main/innodb_data did not exist:

Oct 31 12:55:46 srv mysqld: InnoDB: a new database to be created!

Oct 31 12:55:46 srv mysqld: 111031 12:55:46  InnoDB: Setting file /dev/main/innodb_data size to 984 MB

Oct 31 12:55:46 srv mysqld: InnoDB: Database physically writes the file full: wait...

Oct 31 12:56:18 srv mysqld: InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900

Открываем конфиг и меняем newraw на raw

[mysqld]

innodb_data_home_dir = 

innodb_data_file_path = /dev/main/innodb_data:984Mraw

Запускаем Mysql сервер и заливаем наши базы обратно.

Не забываем что параметр innodb_file_per_table = 1 в конфиг.файле my.cnf не должен использоваться, чтобы данные базы не выносились в .ibd файлы в каталог базы данных /var/lib/mysql/<имя_базы>/ (путь используемый MySQL сервером по умолчанию). Иначе мы не получим ощутимого эффекта от использования raw раздела.

Переключение с MyISAM на InnoDB

Пример:

Смотрим текущий используемый движок для таблиц базы db2

root@django:~# mysql -u root -p -e 'select table_schema, table_name, engine from information_schema.tables where table_schema = "db2";'

+--------------+----------------------------+--------+
| table_schema | table_name                 | engine |
+--------------+----------------------------+--------+
| db2          | auth_group                 | MyISAM |
| db2          | auth_group_permissions     | MyISAM |
| db2          | auth_message               | MyISAM |
| db2          | auth_permission            | MyISAM |
| db2          | auth_user                  | MyISAM |
| db2          | auth_user_groups           | MyISAM |
| db2          | auth_user_user_permissions | MyISAM |
| db2          | django_admin_log           | MyISAM |
| db2          | django_content_type        | MyISAM |
| db2          | django_session             | MyISAM |
| db2          | django_site                | MyISAM |
+--------------+----------------------------+--------+

Переводим на InnoDB

root@django:~# mysql --skip-column-names -u root -ppassword -e 'select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables where table_schema="db2"' | mysql -u root -p

Проверяем результат

root@django:~# mysql -u root -p -e 'select table_schema, table_name, engine from information_schema.tables where table_schema = "db2";'                                                            
+--------------+----------------------------+--------+
| table_schema | table_name                 | engine |
+--------------+----------------------------+--------+
| db2          | auth_group                 | InnoDB |
| db2          | auth_group_permissions     | InnoDB |
| db2          | auth_message               | InnoDB |
| db2          | auth_permission            | InnoDB |
| db2          | auth_user                  | InnoDB |
| db2          | auth_user_groups           | InnoDB |
| db2          | auth_user_user_permissions | InnoDB |
| db2          | django_admin_log           | InnoDB |
| db2          | django_content_type        | InnoDB |
| db2          | django_session             | InnoDB |
| db2          | django_site                | InnoDB |
+--------------+----------------------------+--------+

Соответственно поправив SQL запрос можно будет переключиться и в обратную сторону, т.е. с InnoDB на MyISAM

MySQL настройка репликации

Пример быстрой настройки репликации.

На master сервере создаем mysql пользователя специально для репликации с соответствующими правами

mysql> grant replication slave on *.* to 'repl_user'@'%' identified by 'secret';

выполняем первоначальную синхронизацию master и slave серверов, копируем базы данных с master на slave. Это можно сделать просто через бэкап всех баз на master'e и разворачивании этого бэкапа на slave сервере. После чего выключаем mysql сервер

на том же master сервере вносим в главный конфигурационный файл my.cnf

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
bind-address = 0.0.0.0

запускаем mysql сервер и выполняем очистку существующих бинарных логов на нем

mysql> reset master;

выключаем slave сервер, если он включен, вносим в my.cnf и обратно включаем

server-id = 2

master-host = 192.168.1.100

master-user = repl_user

master-password = secret

на Debian потребуется перенос файла /etc/mysql/debian.cnf на slave сервер, так как в нем хранятся данные для доступа скриптов ОС к mysql серверу

Теперь проверяем состояние и работоспособность настроенной репликации

на master сервере

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 |      191 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

на slave сервере

mysql> show slave status;

часть вывода

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.1.100

                  Master_User: repl_user

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000003

          Read_Master_Log_Pos: 191

               Relay_Log_File: mysqld-relay-bin.000006

                Relay_Log_Pos: 336

        Relay_Master_Log_File: mysql-bin.000003

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

MySQL ограничение ресурсов пользователя

Выполняем команду


mysql -u root -p mysql -e "desc user;"


вывод части описания таблицы MySQL пользователей касающейся ограничения ресурсов

...
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+

max_questions - количество запросов в час
max_updates - количество апдейтов в час, т.е. любая команда которая изменяет данные в таблице или базе данных
max_connections - количество коннектов сделанных за час, т.е. все новые соединения открытые за час
max_user_connections - количество одновременных подключений к серверу

выставить ограничение можно таким запросом

update user set max_connections = '150' where user = 'test';

при превышении одного из ограничений будет возвращаться соответствующая ошибка, например


ERROR 1226 (42000): User 'test' has exceeded the 'max_connections_per_hour' resource (current value: 150)


Сбросить счетчики можно с помощью команды flush user_resources;