Виктор Гольцман - MySQL 5.0. Библиотека программиста

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

В табл. 6.2 перечислены наиболее важные параметры, влияющие на производительность сервера.

Таблица 6.2.

Параметры работы сервера

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

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

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

• таблица используется главным образом для чтения данных;

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

• в таблице часто выполняются запросы, требующие просмотра всей таблицы (при этом построчные блокировки будут неэффективны).

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

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

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

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

LOCK TABLES <���Основная таблица> WRITE,

<���Вспомогательная таблица> WRITE;

INSERT INTO <���Основная таблица>

(SELECT * FROM <���Вспомогательная таблица>);

DELETE FROM <���Вспомогательная таблица>);

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

• Другое решение состоит в присвоении значения 2 системной переменной concurrent_insert. Такая настройка позволяет выполнять запросы и добавление строк одновременно: если таблица используется для чтения, новые строки записываются в конец таблицы. При этом пустые блоки, образующиеся при удалении строк, не заполняются новыми строками, так что таблицу полезно периодически дефрагментировать с помощью команды OPTIMIZE TABLE, о которой говорилось в разделе 6.1 «Оптимизация структуры данных».