Оптимальная настройка Mysql

Опубликовано: | Автор:

Конфигурационные параметры по умолчанию в Mysql рассчитаны на небольшие базы данных, работающие под малыми нагрузками на скромном железе. Настройка некоторых параметров может повысить производительность Вашей базы данных в несколько раз.

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

Откройте файл настроек mysql, например:

sudo mcedit  /etc/mysql/my.cnf

Самые распространенные параметры, которые следует подстроить под нужды проекта и конфигурацию железа приведены ниже (красным выделены наиболее важные из них)...

Размер буфера под хранение индексов MyISAM очень легко определить — достаточно определить их размер на файловой системе. MyISAM хранит индексы в файлах с расширением .MYI, сумма размеров всех файлов индексов во всех базах даст идеальный размер буфера в памяти. Подсчитать общий размер очень просто.

Первым делом определяем каталог с базами, для этого в консоли mysql вводим:
mysql> SHOW VARIABLES LIKE ‘datadir’;

Переходим в указанный каталог и считаем общий размер всех файлов:
 # cd /var/lib/mysql/
 # du -ch */*.MYI
 …
 54M total
 Т.е. 54М будет достаточно, чтобы поместить все индексы в базу, можно взять 64 Мб с небольшим запасом на рост. 64М легко выделить почти на любой системе.

общие параметры:
datadir
каталог для хранения БД, лучше если это будет отдельный диск (RAID); высокие требования по надёжности и скорости.
tmpdir
каталог для хранения временных файлов, лучше сделать отдельным быстрым диском; невысокие требования по надёжности, высокие по скорости; можно использовать RAM диск.
socket
файловый сокет, возможность подключения консольным клиентом.
user
пользователь ОС, под которым запускается БД.
log-slow-queries
логирование запросов, выполняющихся длительное время.
skip-name-resolve
отключает DNS резолвинг для устанавливаемых соединений.
default-storage-engine
тип хранилища таблиц по-умолчанию, InnoDB — транзакционный тип хранилища.
default-character-set
кодировка по-умолчанию для вновь создаваемых таблиц.
default-collation
collation (порядок букв, используется при сортировке) по-умолчнию для вновь создаваемых таблиц.
sql_mode

запрещается установка режимов STRICT_TRANS_TABLES и STRICT_ALL_TABLES, это приведёт к неработоспособности некоторых компонентов биллинговой системы.
max_allowed_packet
Максимальный размер пакета для передачи данных. Данные между клиентом и сервером передаются пакетами. В начале создается пакет длиной net_buffer_length затем, если размер данных больше, то размер пакета увеличивается до необходимого значения, при этом его длина не может превысить значение max_allowed_packet. Если используются поля BLOB большого размера, то рекомендуется увеличить значение этого параметра. В идеале нужно присвоить этой переменной значение размера самого большого BLOB поля.
max_connections
Максимальное количество открытых соединений. Определяет, сколько клиентов одновременно могут работать с сервером. Увеличение параметра увеличивает количество используемых дескрипторов файла.
memlock  
запрет на перенос процесса mysqld в свап.

thread_cache_size

Определяет, сколько потоков должно сохраняться в кэше для повторного использования. После отключения клиента потоки клиента помещаются в кэш, если там не больше потоков, чем thread_cache_size. Все новые потоки сначала берутся из кэша, и только когда кэш становится пустым, создаются новые потоки. Значение этой переменной можно увеличить, чтобы повысить производительность, если создается много новых соединений (если потоки у вас хорошо организованы, обычно заметного улучшения производительности не наблюдается). Насколько эффективен текущий кэш потоков, можно определить по разнице между Connections и Threads_created. Если есть возможность, рекомендуется установить это значение не меньше, чем значение переменной Max_used_connections. Если значение этой переменной больше 128, рекомендуется ограничиться этим значением.

show status LIKE "Max_used_connections%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 11    |
+----------------------+-------+
1 row in set (0.00 sec)

В нашем случае, Max_used_connections = 11, поэтому установим этот параметр в 16.

myisam_sort_buffer_size

myisam_sort_buffer_size=512М – Буфер, который выделяется для сортировки индексов при выполнении команды REPAIR или для создания индексов при помощи команд CREATE INDEX или ALTER TABLE. Рекомендуется не жадничать …

net_read_timeout

net_read_timeout=12 – Количество времени в секундах, на протяжении которого ожидаются дополнительные данные от соединения, пока не будет отменено чтение. Обратите внимание, что мы не ожидаем поступления данных от соединения, время ожидания определяется по write_timeout.

net_write_timeout

net_write_timeout=15 – Время ожидания записи блока через соединение, пока запись не будет прервана (в секундах).

thread_concurrency

число одновременно работающих потоков

рекомендуется ставить 2 * количество CPU + количество дисков.
еще вариант
thread_concurrency = (кол-во процессоров)*(кол-во ядер в одном процессоре)*3

Запрещается установка опции skip-networking, т.к. Java приложение подключается к серверу с использованием TCP протокола, а не через файловый сокет.

Опции mysqld — InnoDB:
innodb_file_per_table
для возможности использования [urlspan]Backup базы с помощью snapshot'ов (Linux, LVM)[/urlspan] в ОС LINUX.
innodb_log_group_home_dir
путь к каталогу под журнал транзакций, лучше если это будет отдельный диск; высокие требования по скорости и надёжности, низкие по объёму.
innodb_buffer_pool_size
Если Вы используете только InnoDB таблицы, устанавливайте это значение максимально возможным для Вашей системы. Буфер InnoDB кэширует и данные и индексы (а кеш операционной системы не используется), поэтому значение этого ключа стоит устанавливать в 70%...80% доступной памяти.

Если Ваш сервер работает на Линуксе или Юниксе, не забудьте установить параметр innodb_flush_method в значение «O_DIRECT», что-бы избежать кеширования на уровне ОС того, что уже кэширует Mysql.
innodb_additional_mem_pool_size
параметр можно не изменять, размер буфера под доп. цели.

innodb_log_files_in_group — количество файлов журналов транзакций в группе журналов; InnoDB производит запись в файлы по круговому способу; увеличение ускоряет запись но тормозит восстановление информации в случае сбоя.
innodb_log_file_size
Размер каждого файла журнала в группе журналов (указывается в мегабайтах). Обратите внимание на этот параметр, если у Вас предусматривается большой показатель записей. Чем больше размер этого ключа, тем более эффективно будет происходить запись данных. Но учтите, что при этом увеличится время восстановления системы! Этот параметр обычно устанавливают в 64M-512M.
innodb_log_buffer_size
размер буфера, который в InnoDB используется для записи информации файлов журналов на диск.
innodb_lock_wait_timeout
время простоя (в секундах), на протяжении которого транзакция InnoDB может ожидать прекращения блокировки прежде, чем будет произведен откат.
innodb_thread_concurrency
должно совпадать с thread_concurrency; число одновременно работающих потоков, рекомендуется ставить 2 * количество CPU+ количество дисков или оставить на выбор системы указав значение 0.
innodb_flush_log_at_trx_commit
Этот параметр в значительной степени влияет на скорость работы (записи) innoDB таблиц. Значение «1» означает, что любая завершенная транзакция будет синхронно сбрасывать лог на диск. Значение «2» делает то же самое, только сбрасывает лог не на диск, а в кеш операционной системы. Это значение подойдет в большинстве случаев, т.к. не выполняет дорогой операции записи после каждой транзакции. При этом лог пишется на диск с задержкой в несколько секунд, что весьма безопасно с точки зрения сохранности данных. Значение «0» даст наибольшую производительность. В этом случае буфер будет сбрасывать в лог файл независимо от транзакций. Устанавливайте этот параметр в «0» на свой риск, т.к. в этом случае риск потери данных возрастает.
innodb_flush_method
O_DIRECT отключает двойную буферизацию (самим mysql и ОС).

Опции mysqld -MyIsam, если используется (например, для некритичных таблиц):

  • myisam_recover -восстановление битых таблиц при старте сервера.
  • myisam_repair_threads — число потоков восстановления.
  • myisam_data_pointer_size — возможность создания больших первичных ключей в таблицах.

Опции mysqld — репликации, если используется:

  • server-id — идентификатор сервера.
  • log-bin — место хранение bin-логов; лучше если это будет отдельный диск, высокие требования по скорости и надёжности, малые по объёму.
  • expire_logs_days — автоматическое удаление старых bin-логов.
  • replicate-do-db — реплицируемая БД.
  • relay-log-space-limit — ограничение на объём bin-логов.

Опции mysqld_safe:

  • open-files-limit — лимит количества открытых файлов для ОС Linux.
  • log-error — файл для логирования ошибок.
  • pid-file — PID файл процесса.

Опции mysqldump:

  • max_allowed_packet — установка маскимально возможного размера пакета при снятии дампов утилитой mysqldump.
  • default-character-set — кодировка по-умолчанию при снятии дампов БД.

Опции client:

  • default-character-set — кодировка по-умолчанию при подключении консольным клиентом.

table_cache
Этот ключ определяет память, выделяемую для хранения открытых таблиц. Если у Вас несколько сотен таблиц, устанавливайте это значение в 1024. Если же у Вас огромное количество соединений, увеличивайте постепенно это значение, т.к. для каждого соединения храниться отдельная запись.MySQL необходимо 2 дескриптора для каждой открытой таблицы.

show status LIKE "Opened_tables%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 560   |
+---------------+-------+
1 row in set (0.00 sec)

Opened_tables характеризует число таблиц, открытых в обход кэша, желательно, чтобы ее значение стремилось к 0.

thread_cache_size

Этот параметр помогает избежать операций создания/уничтожения потоков при соединении к серверу. Установите этот параметр в 16 и наращивайте по мере потребности. Проверяйте показатель «Threads_created», идеально он должен быть равным нулю:

mysql> show status like 'threads_created';
+-----------------+--------+
| Variable_name   | Value  |
+-----------------+--------+
| Threads_created | 423312 |
+-----------------+--------+

query_cache_size
Значение этого параметра определяет сколько памяти стоит использовать под кеш запросов. Не увлекайтесь установкой огромных значений. Кеш запросов не должен быть большим, т.к. mysql будет съедать ресурсы на управление данными в кеше. Начните с 32М...128М, и увеличивайте по мере необходимости.

record_buffer

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

Обычно рекомендуется принять его в 4-6 раз меньшим чем sort_buffer.

sort_buffer

Каждый поток, который осуществляет сортировку данных (ORDER BY или GROUP BY), выделяет память размером sort_buffer. Для повышения быстродействия запросов с ORDER BY или GROUP BY это значение необходимо увеличить.“Увлекаться” большим значением не стоит, а посчитать его можно исходя из среднего значения открытых потоков (Threads_running) и кол-ва ОЗУ сервера.

show status LIKE "Threads_running%";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_running | 2     |
+-----------------+-------+
1 row in set (0.00 sec)

Текущее значение Threads_running равно 2, таким образом на буферы сортировки у нас выделяется в среднем 1024/2=512Mb.

query_cache_limit=2M – Результаты, превышающие это значение, не кэшируются (по умолчанию – 1Мб). Зависит от типа извлекаемых данных из mysql. Если запросов много и в то же время преимущественно извлекается небольшое количество данных (1 Mb), то данное значение лучше уменьшить.

back_log

этот параметр показывает, сколько одновременно может быть невыполненных запросов на соединение (connection requests). Параметр имеет большое значение в тех случаях, когда к MySQL поступает ОЧЕНЬ много запросов на соединение в малый промежуток времени. Когда к MySQL поступает connect-запрос, производятся следующие действия — проверяется, разрешен ли доступ к серверу, и если разрешен, то порождается новый процесс. Все это занимает достаточно мало времени. Однако если за это время поступит еще один connect-запрос, то он заносится в очередь. Параметр back_logопределяет длину этой очереди. Если количество запросов превысит данное значение, то все непомещающиеся запросы будут игнорироваться. По умолчанию значение back_log равно 5, что вполне достаточно для большинства серверов. Максимально значение back_log ограничено операционной системой.
connect_timeout
количество секунд, которое сервер ждет connect-пакета, по истечении этого времени будет выдан пакет «Плохое соединение». Для более детальных разъяснений см. описание TCP/IP протокола.
delayed_insert_timeout
как долго поток INSERT DELAYED будет ожидать данных для INSERT. Более подробно значение слова DELAYED расписано в описании INSERT запроса.
delayed_insert_limit
INSERT DELAYED вставив количество записей, равное delayed_insert_limit, проверяет, есть ли SELECT-запрос к этой же таблице. Если есть, то выполняется SELECT, и только после этого продолжается INSERT.

delayed_queue_size

Для выполнения INSERT DELAYED будет выделятся очередь длиной в delayed_queue_size строк. Когда очередь заполниться все остальные конкурирующие INSERT DELAYED запросы будут ждать, пока не освободиться место в этой очереди.

flush_time

Если значение больше нуля, то каждые flush_time секунд все таблицы будут закрываться. Это позволит освобождать неиспользуемые ресурсы и синхронизировать данные на диске.

join_buffer

Величина буфера, который используется для полных JOIN запросов (т.е. для полного объединения двух таблиц без использования индексов). Память под такой буфер выделяется один раз для каждого запроса. Увеличение параметра ускорит выполнение таких запросов. Более естественный путь ускорить полные JOIN запросы — использовать индексы.

long_query_time

long_query_time=30 – Если обработка запроса отнимает больше указанного промежутка времени (в секундах), значение счетчика Slow_queries будет увеличено. Если используется параметр –log-slow-queries, запрос будет записан в журнал медленных запросов.
Значение этого параметра должно быть примерно равно time_limit скрипта php или временно лимиту операции выдачи, т.к. часто получаются ситуации когда PHP-скрипт уже вылетел по time_limit, а бендненькое умирающее животное MySQL все еще корчится в конвульсиях над запросом по группировке 10 млн записей.

Если время выполнения запроса превысит данное значение (в сек.), то внутренний счетчик slow_queries будет увеличен на 1. Посмотреть значение счетчика можно командой
mysql>status.

max_connect_errors

Если в процессе общения с клиентом произошел обрыв соединения (interrupt connection), то счетчик ошибок для хоста клиента увеличивается на 1. Когда значение этого счетчика достигнет max_connect_errors, то все последующие соединения с данного хоста будут игнорироваться. Для обнуления счетчиков использовать команду FLUSH HOSTS.

max_delayed_threads

Максимальное количество потоков, которые выполняют INSERT DELAYED. Если будет вызван запрос INSERT DELAYED, а при этом достигнуто значение max_delayed_threads, то такой запрос будет выполнен как обычный INSERT (без опции DELAYED).

max_join_size

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

max_sort_length

При сортировке BLOB или TEXT полей из каждого поля берутся только первые max_sort_length байт, а остальные отбрасываются и при сортировке не учитываются.

max_tmp_tables

Максимальное количество временных таблиц, которые клиент может сохранять открытыми одновременно.
net_buffer_length
Размер пакета для передачи данных (см. max_allowed_packet) Обычно этот параметр не нужно изменять, но если у вас очень мало памяти, то его можно уменьшить до ожидаемого размера результата запроса.

tmp_table_size

Максимальный размер памяти для временных таблиц, создаваемых MySQL, которые «хранятся» в оперативной памяти. Если размер временной таблицы превышает указанный, тогда таблица будет «создана» на диске. При превышении этого размера возвращается ошибка table tbl_name is full. При использовании сложных GROUP BY запросов значение нужно увеличить.

Понаблюдайте также за состоянием created_tmp_disk_tables, ее значение должно стремиться к 0.

Для этого нужно выполнить запрос в консоли mysql:

SHOW STATUS LIKE 'Created_tmp_disk_tables';

Если значение created_tmp_disk_tables гораздо больше нуля, попробуйте увеличить параметр tmp_table_size

max_heap_table_size

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

Значений в 32М более чем достаточно. Указать нужно именно две переменные, так как размер временной таблицы в памяти определяется меньшей из max_heap_table_size и tmp_table_size. Для создания временных таблиц в памяти mysql использует движок MEMORY, как раз максимальный размер MEMORY таблиц и определяется max_heap_table_size. tmp_table_size используется при определении движка, который будет использоваться для временных таблиц. Если таблица помещается в tmp_table_size и меньше max_heap_table_size — будет использоваться MEMORY, если нет — будет использоваться MYISAM на жестком диске

временные таблицы – в памяти

tmpdir = /dev/shm

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

wait_timeout

wait_timeout=30 – Время в секундах, на протяжении которого сервер ожидает активности соединения прежде, чем закрыть его.

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

interactive_timeout

interactive_timeout=600 – Количество времени в секундах, на протяжении которого сервер ожидает активности со стороны интерактивного соединения, прежде чем закрыть его. Интерактивный клиент – это клиент, который использует параметр CLIENT_INTERACTIVE для mysql_real_connect (). См. также информацию по wait_timeout.

Время, которое поток ждет повторного обращения. Если за это время к потоку не было ни одного обращения, то поток убивается.

Параметры table_cache, max_connections и max_tmp_tables определяют, как много файлов сервер будет держать открытыми. Максимально количество открытых файлов для каждого процесса ограничивается операционной системой. На многих ОП это количество можно увеличить. Для более детальной информации см. руководство по вашей ОП.

table_cache зависит от max_connections.

Например, если у вас 200 открытых соединений, то вам может понадобиться до 200*n открытых таблиц, где n — количество таблиц, участвующих в запросах.

Кэширование запросов в MYSQL

Как MySQL работает с памятью.

Здесь приведено описание того, как 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. Основная характеристика — занимают мало места.

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

Все индексы (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. Задание прав доступа на конкретную таблицу или поле снижает производительность.

####################################################

Большое значение Created_tmp_disk_tables

В процессе работы MySQL для каждого соединения постоянно создаются временные таблицы, большое значение Created_tmp_disk_tables (у меня было 45% от общего числа создаваемых) означает, что временные таблицы создаются не в памяти, а в временных файлах на диске, что замедляет работу MySQL. За выделение памяти для временных таблиц отвечают параметры tmp_table_size и max_heap_table_size в конфиге. Я увеличивал значения этих параметров, но количество создаваемых таблиц на диске не уменьшилось.

Проблема оказалось была вот в чём: если в таблицах используются поля типов TEXT (TEXT, TINYTEXT, MEDIUMTEXT …) или BLOB, то таблица не может быть размещена в памяти, а только на диске. Эти типы полей могут содержать в каждом поле большой объём текста или данных, и в MySQL данные физически не хранятся в самой таблице, а в поле содержится ссылка на сами данные.

Раньше для текстовых данных с длиной поля больше 255 символов альтернативы особой не было, всегда приходилось использовать поле типа TEXT. Но, начиная с версии 5.0.3 тип поля VARCHAR может хранить в себе 65535 байт (до этого только 255 байт). Для всех видов SQL операций, работа с полем VARCHAR происходит быстрее, чем с полями типа *TEXT, но самое главное, что временные таблицы с полями VARCHAR хранятся в памяти.

После того, как я сконвертировал все поля типов TEXT в VARCHAR, значение created_time_disc_tables уменьшилось, и теперь таблицы создаваемые на диске составляют 8% от общего количества, т.е. улучшение в 5 с половиной раз.

Правда, тут есть небольшое ограничение. Одна строка данных (сумма размеров всех полей) в MySQL таблице не может быть больше 65535 байт, т.е. нельзя создать в таблице два поля VARCHAR размером 65000 байт, например. Можно создать два поля размером 32767 байта, или одно поле 32767 и два по 16388.

Кэш запросов и высокое значение Qcache_lowmem_prunes

Кэш запросов может неплохо помочь в случае слабо оптимизированных скриптов, когда часто выполняются одни и те же выборки SELECT. Если запрос уже находится в кэше, то MySQL не обращается к таблицам, а берёт его прямо из кэша, что очень быстро (т.к. кэш в памяти). У меня в моих базах данных из кэша берутся 90% всех запросов.

Qcache_lowmem_prunes — показывает количество запросов которые были удалены из кэша из-за нехватки памяти. Если не хватает размера кэша, то MySQL оставляет в памяти только самые популярные запросы, а редкие из него удаляет. Чтобы уменьшить большое количество Qcache_lowmem_prunes я сначала пытался постепенно увеличивать размер кэша запросов, доведя его до 768 мегабайт. Это не помогло, Qcache_lowmem_prunes не уменьшилось, процент эффективности кеша также не вырос.

Затем я более подробно изучил, как работает кэш, и поменял тактику. Дело в том, что если таблица меняется (происходит INSERT, DELETE или UPDATE), то все запросы из кэша для этой таблицы удаляются. Если кэш большого размера, то на его обслуживание нужно тоже время (т.к. память не может быть освобождена моментально). Часто обновляемые таблицы, по сути, только замедляют работу с кэшем, т.к. запросы почти сразу же удаляются и только фрагментируют кэш.

Для всех таблиц которые часто обновляются, я прописал директиву SQL_NO_CACHE (например SELECT SQL_NO_CACHE * FROM table), принудительно ограничивающую кэширование. Также для больших таблиц, везде где возможно, я прописал названия выбираемых полей в SELECT (например SELECT SQL_NO_CACHE id,name FROM table), что уменьшило размер возвращаемых данных, и увеличило количество запросов, которые могут поместиться в кэше. Несмотря на то, что сейчас размер кеша 256 Мб, количество Qcache_lowmem_prunes резко уменьшилось.

Борьба с Table_locks_waited и перевод некоторых таблиц на InnoDB

Table_locks_waited — показывает количество событий, когда запрос на блокировку таблиц был выполнен через определенный период ожидания. MyISAM — основной движок таблиц в MySQL имеет один большой недостаток, когда происходит изменение таблицы (кроме INSERT в конец таблицы), то она блокируется на чтение, а когда происходит чтение из таблицы, то она блокируется на запись. Другими словами запросы SELECT и UPDATE, DELETE, INSERT не могут проходить одновременно, и когда они пытаются делать это одновременно, какому то запросу приходиться ожидать и возникает Table_locks_waited событие.

Для небольших таблиц блокировка занимает доли секунды и практически не заметна, но ситуация меняется, если в таблице сотни тысяч или миллионы записей. Каждое обновление занимает заметное время, и в случае большой нагрузки происходит лавинообразный рост блокировок, в логе медленных запросов появляется много безобидных SELECT’ов, выполняющихся по несколько секунд, хотя обычно они выполняются за тысячные доли секунды, а некоторые страницы сайтов начинают медленно отдаваться.

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

Первый способ, кардинальный, переделать логику работы с базой данной так, чтобы она обновлялась как можно реже, либо выборки SELECT шли из одной таблицы, а обновления INSERT, UPDATE, DELETE в другую, и например, раз в сутки они синхронизировались.

Второй способ, для таблиц MyISAM есть неплохое решение (если в таблицу происходят только частые вставки INSERT) использовать оператор DELAYED (Например INSERT DELAYED INTO table VALUES (…) ). При таких вставках они не выполняются в данный момент времени, а вставляются отложено с низким приоритетом, т.е. MySQL ждёт пока выполнятся все SELECT запросы и таблица освободится для записи. Такая вставка происходит реже, MySQL собирает сразу несколько таких запросов и вставляет их все вместе, что снижает нагрузку. Большой минус — для очень загруженных таблиц, запрос вставки может вообще никогда не произойти (если таблица не освободится), также в случае падения MySQL сервера все ожидающие запросы пропадут.

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

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

Плюсы InnoDB:

  • Поддержка транзакций (если не нужны, то не является плюсом)
  • Меньше вероятность потерять данные при падении сервера (только если используются бинарные логи)
  • Блокировки на уровне строки (а не всей таблицы, как для MyISAM)
  • Выдерживает большую нагрузку при больших размерах таблиц
  • Показывает большую производительность при одновременных запросах на чтение и запись

Минусы InnoDB:

  • Индексы в 2-3 раза большего размера, чем для MyISAM
  • Нельзя восстановить таблицу из файла (без бинарного лога), нужно регулярно делать бэкапы, чтобы не потерять данные
  • Для быстрой работы InnoDB нужно существенно больше оперативной памяти (переменная innodb_buffer_pool_size)
  • Точное количество элементов в таблице, в определённый момент времени можно узнать только примерно (запросы COUNT (*) для всей таблицы очень медленно выполняются)
  • Время выполнения вставки/обновления больше чем для таблиц MyISAM.

InnoDB или MyISAM это тема для большого холивара, в интернете я так и не нашёл единого мнения что лучше, у кого то серьёзные аргументы за MyISAM, у кого то за InnoDB .  Эти движки таблиц имееют свои различия, плюсы и минусы, и выбор больше зависит от архитектуры построенной базы данных. Для себя я вывел правило: если таблица редко обновляется, то лучше использовать MyISAM, если таблица имеет большой размер и часто обновляется, то лучше использовать InnoDB. Конечно, блокировки случаются и в InnoDB — но для моих данных это случается на несколько порядков реже, а если и случаются то время блокировки незначительное (несколько миллисекунд).

Для быстрой работы InnoDB самое главное это размер буфера innodb_buffer_pool_size, т.к. InnoDB сильно кеширует индексы и данные в памяти. Размер памяти для этой переменной зависит от размера таблиц, я у себя выделил 1 гигабайт памяти (у меня InnoDB таблицы вместе с индексами занимают 600-700 мегабайт).

 

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

Handler_read_rnd, Handler_read_rnd_next, Select_full_join. Красные значения этих переменных говорят о том, что таблицы баз данных MySQL не проиндексированы, либо что ваши сайты, при запросах к таблицам, не используют возможности индексов. Решение этой проблемы будет рассмотрено в следующих статьях.

Slow_launch_threads. Количество потоков, которые создавались очень медленно. Решение этой проблемы будет рассмотрено в следующих статьях.

Table_locks_waited. Указывает на проблемы с SQL-запросами, которые совершают ваши сайты. Чтобы решить эти проблемы, следует оптимизировать SQL-запросы.

Slow_queries. Количество запросов, которые выполнялись очень медленно. Для решения данной проблемы следует включить ведение лога медленных запросов, после чего оптимизировать все SQL-запросы, которые будут занесены в лог-файл. Эти действия в данной статье рассмотрены не будут.

Created_tmp_disk_tables. Увеличьте значение переменной tmp_table_size (размер буфера в байтах). Фактически, значение данного буфера определяет максимальный размер временной таблицы, которая будет храниться в оперативной памяти. То есть, если временная таблица имеет больший размер, чем tmp_table_size, она будет располагаться на жестком диске.

Sort_merge_passes. Увеличьте значение переменной sort_buffer_size (буфер для выполнения сортировки результатов запроса).

Opened_tables. Увеличьте значение переменной table_cache. В данном буфере хранятся дескрипторы всех открытых сервером MySQL таблиц.

Threads_created. Увеличьте значение переменной thread_cache_size.

Key_reads. Увеличьте значение переменной key_buffer_size. Данный буфер является общим для всех работающих сайтов. Рекомендуется устанавливать размер данного буфера, равный 25-35% от общего количества оперативной памяти на сервере.

key_buffer_size Блоки индексов буферизированы и доступ к ним разрешен всем потокам. key_buffer – размер буфера, используемого для блоков индексов. Чтобы улучшить обработку индексов (для всех операций чтения и записи нескольких элементов), необходимо увеличить это значение настолько, насколько возможно.

Производительность буфера ключей можно проверить, выполнив команду show status LIKE «Key%»; и проверив значения переменных Key_read_requests, Key_reads, Key_write_requests и Key_writes. Отношение значений Key_reads/Key_read_request обычно должно быть < 0,01.

Пример:

show status LIKE "Key%";
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| Key_blocks_not_flushed | 0        |
| Key_blocks_unused      | 46865    |
| Key_blocks_used        | 6720     |
| Key_read_requests      | 31965395 |
| Key_reads              | 6568     |
| Key_write_requests     | 168405   |
| Key_writes             | 91739    |
+------------------------+----------+
7 rows in set (0.00 sec)

#####################################################

Ссылки:

Читайте другие интересные статьи

Понравилась статья, расскажи о ней друзьям, нажми кнопку!