MySQL & mSQL
         

Настройка производительности


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

MySQL имеет три потенциальных «узких места» при любом подключении. Во-первых, это сетевое соединение клиента с сервером. Во-вторых, это время решения таких задач, как, скажем, построение индексов. И наконец, проблема может быть связана с дисковым вводом/выводом. MySQL предоставляет доступ к переменным, с помощью которых ее функционирование можно настроить в соответствии со средой приложения. Все эти переменные можно установить, используя параметр -О в команде mysqld. Например, переменная back_log принимает значение 15 в результате добавления к mysqld параметра -О backjtog=15. Ниже следует список полезных переменных.

bach_log

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

key_buffer

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

max Connections

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

table_cache

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


Структура хранения данных в MySQL

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

-rw-rw-- - 1 root root 1034155 Jun 3 17:08 mytable.ISD

-rw-rw---- 1 root root 50176 Jun 3 17:08 mytable.ISM

-rw-rw-- - 1 root root 9114 Jun 3 14:24 mytable.frm



В файле ISD хранятся фактические данные. В файле ISM хранятся данные о ключах и прочие внутренние данные, необходимые MySQL для быстрого поиска данных в файле ISD. Файл f rm содержит структуру самой таблицы.

Файл ISM наиболее важен для функционирования MySQL. Он настолько важен, что ему посвящена целая утилита isamchk. Запуск isamchk -d выводит сведения о таблице:

# isamchk -d mytable

ISAM file: mytable

Data records: 1973 Deleted blocks: 0

Recordlength: 343

Record format: Packed

table description:

Key Start Len Index Type

1 2 50 unique text packed stripped

Важное поле, которое нужно отметить, это «Deleted blocks» (удаленные блоки). Если его значение слишком велико, то файл понапрасну занимает много лишнего места. К счастью, это пространство можно освободить. В результате выполнения следующей команды таблица будет просмотрена и создана заново, при этом будут в большинстве своем устранены ошибки и высвобождено свободное пространство:

isamchk -r mytable

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

Восстановление поврежденных таблиц

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

isamchk mytable

При запуске isamchk во время работы сервера может потребоваться выполнить mysqladmin reload, чтобы сервер «увидел» исправленную таблицу.

Эта команда исправляет большинство обычных ошибок в таблице. Добавление параметров -г и -v приводит к выводу дополнительных сведений о том, что было нарушено. Использование нескольких -и увеличивает подробность вывода сведений.



isamchk -rq mytable

Эта команда осуществляет быструю проверку и при необходимости исправление только файла ISM, файл ISD при этом не проверяется.

isamchk -e mytable

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

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

Удаление и замена ключей

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

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

isamchk -rq -k0

Если вы готовы снова вставить ключи, это можно сделать командой:

isamchk -rq

Прежде чем ввести команду isamchk с параметром -r, завершите работу сервера, иначе таблица может оказаться повреждений.

Команда isamchk предоставляет столько возможностей, что вам, вероятно, станет сложно разобраться во всех них. Следует, однако, руководствоваться следующими соображениями:

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



  • Раз или два в год выполняйте команду isamchk -d. Если число удаленных блоков в ваших таблицах составляет существенную часть дискового пространства, выполните isamchk -r для перестройки таблиц с высвобождением ненужного места. Если ваше приложение таково, что приходится удалять много старых данных и вводить новые, выполняйте isamchk -d каждые две недели, а если число удаленных блоков быстро растет, может понадобиться выполнять isamchk -r регулярно - раз в месяц.

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

    Устранение неполадок

    Даже в самых совершенных программах возникают проблемы. К счастью, многие из проблем, с которыми вы можете столкнуться, уже с кем-то случались. Ниже приводится ряд часто встречавшихся проблем при администрировании MySQL.

    Изменения в таблицах доступа не действуют.

    Не забывайте выполнять команду mysqladmin reload после внесения изменений в таблицы доступа.

    При высокой загрузке MySQL отказывает в подключении.

  • Сначала уточните число соединений, допускаемых сервером. Команда mysqladmin variables покажет его значение в поле max_connec-tions. Можно увеличить это число, запустив mysqld с параметром -О max_connections=###, где ### - предел, который вы хотите установить.

  • Можно также проверить значение back_log , которое определяет размер очереди, создаваемой MySQL для входящих соединений, равное 5 по умолчанию. Версии MySQL до 3.22 позволяли увеличить это значение до 64, но в более поздних версиях его можно увеличить до 1024. Однако оно может быть ограничено до 64 вашей операционной системой.

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



    MySQL сообщает о невозможности найти файл, который явно существует, или сообщает об ошибках во время его чтения.

    В большинстве случаев эта проблема связана с числом дескрипторов файлов, о котором говорилось выше. Однако если увеличить буфер таблиц MySQL, ему не потребуется открывать так много файлов, и вы сможете избавиться от этой проблемы. По умолчанию величина буфера таблиц равна 64. Можно увеличить его через значение переменной table_cache .

    Число потоков начинает расти, и потоки не завершаются,

    В некоторых системах с установленным NFS, а также в Linux, есть проблемы с механизмом блокировки файлов. Результатом может быть замораживание потоков. Команда mysqladmin processlist поможет выявить эту проблему. Если в поле «Command» против замороженных потоков стоит «System lock», запустите mysqld с параметром --skip_ locking.


    Содержание раздела