Оптимизация

Обслуживание таблиц

Можно ускорить выполнение запросов, если хранить таблицы и индексы в упорядоченном виде. Инструкция OPTIMIZE TABLE улучшает таблицу тремя способами. Во-первых, она устраняет пустые промежутки, оставшиеся после удаления записей. Для таблиц MyISAM это означает возможность одновременного выполнения инструкций INSERT и SELECT. Во-вторых, она соединяет распределенные фрагменты таблиц с динамическими записями. И, наконец, она сортирует индексы.

Инструкция ALTER TABLE позволяет отсортировать записи таблицы. Это тоже способствует ускорению некоторых запросов, хотя и не устраняет потребность в индексах. Если таблица меняется редко, а дисковое пространство ограничено, имеет смысл сжать таблицу с помощью утилиты myismpack. После этого таблица будет доступна только для чтения. Ее индексы необходимо перестроить, вызвав утилиту myismchk. Данная методика позволяет уменьшить размер таблицы на 40-70%, в зависимости от формата ее содержимого. В листинге 10.9 показан процесс сжатия таблицы, содержащей названия штатов США.

# myisampack. exe state
Compressing state. MYD: (50 records)
- Calculating statistic
- Compressing file
32.42%
Remember to run myisamchk – rq on compressing tables
# myisamchk – rq state
- check key delete - chain
- check records delete – chain
- recovering (with sort) MyISAM – table ‘state.MYI’
Data records: 50
- Fixing index 1
- Fixing index 2

Настройка конфигурации сервера

Когда речь заходит об объеме оперативной памяти сервера, совет всегда один: чем больше — тем лучше. Увеличение объема памяти способствует ускорению работы программы MySQL, так как в оперативной памяти она хранит свои временные таблицы и буферы записей. В подкаталоге support files дистрибутива содержатся образцы конфигурационных файлов с различными вариантами настроек, касающихся использования памяти. Выберите тот вариант, который соответствует исходным параметрам сервера. Поработав с сервером какое-то время, можно будет оценить, какие из настроек требуют корректировки.

В листинге 10.10 показана конфигурация сервера, располагающего как минимум 1 Гбайт ОЗУ, четырьмя жесткими дисками и четырьмя центральными процессорами. Обратите внимание на важность индексного буфера. В данной конфигурации предполагается, что сервер хранит табличные данные на первом диске, а временные файлы — на втором. Таблицы InnoDB находятся на третьем диске, а журналы InnoDB — на четвертом.

[mysqld]
set – variable = key buffer = 384M
set – variable = max allowed packet = 1M 
set – variable = table cache = 512
set – variable = sort buffer = 2M
set – variable = record buffer = 2M
set – variable = thread cache size = 8 
set – variable = thread concurrency = 8
set – variable = myisam sort buffer size = 64M
log – bin      = 
server – id    = 1
tmpdir         = /disk2/tmp/

# Таблицы BDB
set – variable = bdb cache size = 384M
set – variable = bdb max lock = 100000

# Таблицы InnoDB
innodb data home dir = /disk3/
innodb log group home dir = /gisk4/
innodb log arch dir = /disk4/
innodb data file path = ibdata1: 250M; ibdata2: 500M; ibdata3: 1000M
set – variable = innodb mirrored log groups = 1
set – variable = innodb log files in groups = 3
set – variable = innodb log files size = 5M
set – variable = innodb log buffer size = 8M
innodb flush log at trx commit = 1
innodb log archive = 0
set – variable = innodb buffer pool size = 16M
set – variable = innodb additional mem pool size = 2M
set – variable = innodb file io threads = 4
set – variable = innodb lock wait timeout = 50

Когда сервер проработает какое-то время, выполните инструкцию SHOW STATUS, чтобы узнать его производительность. Сравните значения показателей Key reads и Key_read_requests. Их соотношение будет очень низким, если программа MySQL часто пользуется индексным буфером. В случае необходимости попытайтесь повысить размер буфера.

Проследите изменение показателя Open tables, сравнивая его со значением серверной переменной table cache, которое можно узнать с помощью инструкции SHOW VARIABLES. Когда табличный буфер заполняется, программа MySQL вынуждена закрывать одни таблицы, чтобы открывать другие. Показатель Opened tables отражает число таблиц, открывавшихся с момента запуска сервера. Сравните его с общим числом запросов (показатель Questions ). Чем больше будет размер табличного буфера, тем реже придется открывать и закрывать таблицы.

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

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

Перекомпиляция MySQL

Команда разработчиков MySQL прилагает огромные усилия для оптимизации исполняемых файлов программы. Лучше всего пользоваться бинарными дистрибутивами, которые доступны на Web-узле MySQL. Вряд ли вам удастся получить более качественный исполняемый файл. Например, в дистрибутивы Linux зачастую включаются нестабильные версии компиляторов и библиотек. Разработчики MySQL всегда применяют самые стабильные версии в сочетании с оптимальными опциями компиляции.

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

На Web-узле MySQL приведена информация о том, как компилировать программу на различных платформах. Не поленитесь просмотреть рекомендации специалистов, поскольку здесь есть много "подводных камней", особенно в случае старых операционных систем.

Перед началом компиляции убедитесь в наличии утилит qzip и qnutar Они необходимы для извлечения файлов из tar-архива. Учтите, что версия утилиты tar для Solaris содержит ошибку, которая не позволяет распаковывать некоторые архивы, поэтому желательно иметь GNU-версию утилиты.

Нужен также компилятор языка C++. Вполне подойдет eqcs. He забудьте и об утилите make.

Те, кто имеют опыт компиляции программ с открытыми кодами, должны быть знакомы со сценариями конфигурации, создаваемыми утилитой autoconf. Саму ее запускать не нужно. Файл Makefile создается сценарием configure. В листинге 10.11 показан вызов этого сценария с установками, которые рекомендованы разработчиками MySQL. Сценарий configure должен запускаться из каталога, содержащего исходные коды программы.

CFLAGS = "-03" \
CXX = gcc \
CXXFLAGS = "-03 –felide–constructors–fno–exceptions–fno-rtti" \
./configure –prefix = /usr/local/mysql \
--enable-assembler \\
--with-mysql-ldflags=-all-atatic

Параметры сценария configure можно получить, вызвав сценарий с опцией –help. Если нужно включить поддержку таблиц Berkeley DB или InnoDB, не забудьте указать соответствующие опции. В исходные дистрибутивы MySQL входят все необходимые для этого файлы, по этому путь к библиотекам Berkeley DB задается только в том случае, когда требуется использовать их альтернативные версии.