А Б В Г Д Е Ж З И К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Э Ю Я
0-9 A B C D I F G H IJ K L M N O P Q R S TU V WX Y Z #


Чтение книги "MySQL 5.0. Библиотека программиста" (страница 31)

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

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

   SHOW VARIABLES;

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

   SET GLOBAL <Имя переменной>=<Значение>;

   однако установленное таким образом значение будет действовать только до момента перезапуска сервера MySQL. Поэтому лучше указать нужные значения переменных непосредственно при запуске сервера MySQL.
   • Если вы запускаете сервер вручную из командной строки (об этом говорилось в подразделе «Запуск и остановка сервера MySQL из командной строки» главы 1), укажите в команде запуска сервера параметр – <Имя переменной> = <Значение>, например

   mysqld-nt –table-cache=256

   • Если сервер MySQL был сконфигурирован как сервис Windows и запускается или автоматически, или с помощью панели управления, или с помощью MySQL Administrator (см. подразделы «Запуск и остановка сервера MySQL с помощью MySQL Administrators «Запуск и остановка сервера MySQL с панели управления» главы 1), откройте конфигурационный файл my.ini, находящийся в папке, где установлена программа MySQL, и добавьте параметр <Имя переменной> = <Значение> в раздел [mysqld] (см. рис. 5.9). Сохраните файл my.ini. Новые значения переменных вступят в действие после перезапуска сервера.
...
   Примечание
   В корневой папке MySQL вы найдете примеры конфигурационных файлов с различными вариантами настроек системных переменных: my-small.ini, my-medium.ini, my-large.ini, my-huge-ini и my-innodb-heavy-4G.ini.
   Установив новые значения переменных, проверьте, как изменилось время выполнения соответствующих операций. Опытным путем можно подобрать размеры буферов, оптимальные для вашей системы.
   В табл. 6.2 перечислены наиболее важные параметры, влияющие на производительность сервера.
Таблица 6.2. Параметры работы сервера


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

   6.4. Проблемы, связанные с блокировками

   В этом разделе мы рассмотрим только внутренние блокировки, используемые сервером MySQL при совместной работе нескольких потоков с одними и теми же данными, и не коснемся внешних блокировок, обеспечивающих координацию работы сервера MySQL и других программ.
   В главах 1 и 2 было отмечено, что в таблицах с типом InnoDB блокировка выполняется на уровне отдельных строк. Для таблиц MyISAM построчная блокировка не поддерживается, и при необходимости таблица блокируется целиком. Преимуществом построчной блокировки является возможность параллельного редактирования данных множеством пользователей. Однако табличная блокировка выполняется быстрее и требует меньшего количества оперативной памяти. Табличные блокировки оказываются предпочтительными в следующих случаях:
   • таблица используется главным образом для чтения данных;
   • основными операциями в таблице являются запросы и добавление строк, а изменение и удаление данных происходит редко; в этом случае можно выполнять чтение и запись параллельно (об этом будет подробно рассказано ниже);
   • в таблице часто выполняются запросы, требующие просмотра всей таблицы (при этом построчные блокировки будут неэффективны).
   Рассмотрим подробнее механизм табличных блокировок. В зависимости от типа выполняемой SQL-команды к таблице применяется либо блокировка чтения, либо блокировка записи. Блокировка чтения разрешается, если в данный момент не наложена блокировка записи; несколько блокировок чтения могут применяться одновременно. Блокировка записи разрешается, если в данный момент нет никаких других блокировок. Если блокировка не может быть применена немедленно, она помещается в очередь. После снятия текущей блокировки применяется следующая блокировка из очереди блокировок записи, а если эта очередь пуста, то применяется следующая блокировка из очереди блокировок чтения.
   Блокировки записи, с одной стороны, требуют исключительного доступа к таблице, с другой – имеют приоритет перед блокировками чтения. Если множество запросов и операций обновления данных конкурируют за доступ к таблице, то все запросы будут отложены до тех пор, пока не будут выполнены все обновления. Кроме того, может возникнуть ситуация, когда выполняется медленный запрос к таблице, а наличие блокировок записи, ожидающих в очереди, препятствует выполнению последующих запросов (которые в противном случае могли бы выполняться одновременно с текущим запросом).
   Избежать проблем, связанных с табличными блокировками в таблице с типом MyISAM, можно следующими способами.
   • Если все операции записи представляют собой добавление строк в таблицу, можно создать вспомогательную таблицу с такой же структурой и вносить в нее все новые строки. Накопленные во вспомогательной таблице строки необходимо время от времени переносить в основную таблицу с помощью последовательности команд

   LOCK TABLES <Основная таблица> WRITE,
   <Вспомогательная таблица> WRITE;
   INSERT INTO <Основная таблица>
   (SELECT * FROM <Вспомогательная таблица>);
   DELETE FROM <Вспомогательная таблица>);
   UNLOCK TABLES;

   Этот способ ускорения работы основан на том, что группа обновлений в рамках единой блокировки выполняется намного быстрее, чем те же самые обновления по отдельности.
   • Другое решение состоит в присвоении значения 2 системной переменной concurrent_insert. Такая настройка позволяет выполнять запросы и добавление строк одновременно: если таблица используется для чтения, новые строки записываются в конец таблицы. При этом пустые блоки, образующиеся при удалении строк, не заполняются новыми строками, так что таблицу полезно периодически дефрагментировать с помощью команды OPTIMIZE TABLE, о которой говорилось в разделе 6.1 «Оптимизация структуры данных».
   • Уменьшите значение переменной max_write_lock_count, чтобы разрешить чередование блокировок записи и чтения. Данная переменная определяет количество блокировок записи, после применения которых допускается выполнение блокировок чтения, несмотря на наличие ожидающих своей очереди блокировок записи (значение по умолчанию – 102 4).
   • Снизьте приоритет всех операций записи, установив значение ON переменной low-priority-updates. При таком значении к таблице в первую очередь будут применяться блокировки чтения, и только в их отсутствие – блокировки записи.
   • Снизьте приоритет отдельных операций записи, включив в SQL-команду параметр LOW_PRIORITY. Например, выполнение команды

   UPDATE LOW_PRIORITY Customers
   SET phone='444-25-27' WHERE id=536;

   будет отложено до тех пор, пока не будут выполнены все операции чтения для данной таблицы.
   • Вставку строк можно выполнять в отложенном режиме, задав в команде INSERT и REPLACE параметр DELAYED. При этом клиентское приложение сразу же получает сообщение о результате выполнения операции, однако добавляемая строка записывается не непосредственно в таблицу, а в очередь отложенных добавлений (общую для всех клиентских приложений). Как только таблица освобождается от предыдущей блокировки, в нее единой группой вносятся строки, накопленные в очереди. Если очередь слишком велика, то операция вставки строк в таблицу периодически прерывается для выполнения ожидающих своей очереди запросов. Таким образом, отложенный режим добавления строк замедляет выполнение запросов в меньшей степени, чем обычный режим.
   • Если необходимо сочетать массовое удаление строк и выполнение запросов, рекомендуется удалять строки поэтапно, используя параметр LIMIT команды DELETE (см. раздел 2.6 «Изменение данных»).
   • Повысим приоритет отдельных запросов, включив в текст запроса параметр HIGH_PRIORITY.
   Например, запрос

   SELECT HIGH_PRIORITY * FROM Customers WHERE rating>1000;

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

   6.5. Резюме

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



1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 [31]

Навигация по сайту


Читательские рекомендации

Информация