Original
Перевод: Сгибнев Михаил
Примечание: Это мой первый опыт перевода статей, касающихся СУБД, могут быть огрехи в
специфических терминах, все замечания - в
форум.
Резюме: Вы можете повысить быстродействие MySQL на платформе Solaris проведя более тонкую оптимизацию
сервера баз данных и операционной системы. В этой статье мы рассмотрим настройку необходимых параметров.
Оглавление
- Введение
- Пользовательские нити InnoDB
- Использование Optimized Time Library для минимизации системного вызова time(2)
- Использование Solaris OS mtmalloc для управления памятью в MySQL
- Использование 64-bit версии MySQL в Solaris
- Сборка MySQL программным обеспечением Sun Studio 11
- Оптимизация файловой системы
- Данные InnoDB и размер Index Cache
- Режим сброса лога транзакций
- Размер буфера лога
- Установка контрольных точек
- Размер кэша запросов
- Заключение
- Ресурсы
- Об авторе
Введение
MySQL является в настоящее время одной из самых популярных систем управления базами данных.
Загадка популярности MySQL в превосходной работе и масшабируемости, что делает ее незаменимой
в обслуживании web-сайтов, центров обработки данных и других приложений, интенсивно работающих с данными на
предприятиях.
Для развертывания и работы MySQL на Solaris очень важна как конфигурация сервера баз данных, так и настройка
самой операционной системы.
Однако, универсальных параметров MySQL, приводящих к повышению быстрдействия на любой платформе, не существует, необходимо
настраибать сервер под отдельно взятую ОС и конкретную задачу. В этой статье мы постараемся помочь вам
определить и настроить эти параметры.
MySQL включает в себя несколько механизмов хранения данных, таких как MyISAM, InnoDB, HEAP и Berkeley DB (BDB).
Хранилища InnoDB и BDB поддерживают Atomic, Consistent, Isolation и Durable (ACID) транзакции, откат и
возможности восстановления после сбоя. Только хранилище InnoDB поддерживает блокировки на уровне строки с
запросами, по умолчанию выполняющими как неблокирующее, непротиворечивое чтение.
Хранилище InnoDB поддерживает все четыре уровня изоляции: read uncommitted, read committed, repeatable read и serializable.
InnoDB имет также такую возможность, как справочная целостность с поддержкой удаленных ключей,
позволяющую осуществлять очень быстрый просмотр записей для запросов, используя первичный ключ.
Благодаря этой и другим, не менее полезных, функциям, InnoDB используется в больших, сильно нагруженных системах.
В этой статье мы рассмотрим различные пути повышения эффективности использования CPU, памяти и дисков в
ОС Solaris 10. Это включает использование оптимизированных библиотек, компиляцию 64-bit MySQL с помощью Sun Studio 11,
настройку файловой системы UFS, конфигурацию сервера MySQL и оптимизацию хранилища InnoDB под Solaris.
Пользовательские нити InnoDB
MySQL представляет собой однопроцессное, многонитевое приложение.
Одна, главная, нить имеет самый высокий приоритет среди всех остальных и используется для
управления сервером. Главная нить большую часть времени находится в состоянии простоя и "просыпается"
каждые 300 миллисекунд для проверки необходимости выполнения каких-либо действий, например, сброса
блоков в буфер.
Помимо нее существует некоторое число выделенных пользовательских нитей, запущенных с нормальным приоритетом,
объединяемым в пул нитей дляобработки нескольких одновременных пользовательских запросов.
Для каждого запроса клиента создается одна единственная нить, обрабатывающая запрос и посылающая
пользователю его результат. Также есть одна пользовательская нить, ожидающая ввода с консоли и несколько
нитей, запущенных с низким приоритетом для обслуживания фоновых задач.
В настоящий момент MySQL плохо масштабируется относительно пользовательских нитей, обрабатывающих
клиентские запросы. Эффективность работы повышается с каждой новой пользовательской нитью, пока не
достигается пик производительности. Дальнейшее увеличение пользовательских нитей приведет к падению
производительности из-за конфликтов доступа к данным.
Для приложений, где число пользовательских подключений заранее известно, вы должны определить
(для различных рабочих нагрузок) оптимальное число пользовательских подключений.
Мы выполнили тесты CPU
SysBench
(1M-row могут быть представлены как данные InnoDB и индекс кэша) на сервере, построенном
на базе 4-way UltraSPARC IV. Пик производительности MySQL был достигнут при 16
пользовательских подключениях и начала снижаться с 32 подключений, что показано на Рис.1
(внимание, данные могут отличаться):
Этот тест показал, что пик производительности MySQL на процессорах UltraSPARC IV
достигается при числе пользовательских подключений, примерно равных 4*CPU при 1-4 процессорных
системах. Пик работы MySQL практически линейно растет до 4-х процессоров и отношение
масшабируемости начинает понижаться от 8 процессоров. На Рис.2 показан пик производительности
на 1-24 процессорных UltraSPARC IV серверах (внимание, данные могут отличаться):
Для приложений, где число пользовательских подключений настроить нельзя,
для установки максимального числа конкурирующих нитей в InnoDB
можно сконфигурировать параметр innodb_thread_concurrency.
Вы должны увеличить это значение, если видите много запросов в очереди на, просматривая
статус innodb. Установка этого значения в 1000 отключит механизм проверки конфликтов,
таким образом мы можем получить столько одновременных нитей, сколько необходимо для обработки
всех задач сервера. В некоторых задачах, решаемых на платформе Solaris, когда вы видите
большое число пользовательских блокировок (LCK в выводе команды prstat -mL), вы можете
уменьшить этот параметр для повышения эффективности использования процессора. Настройка этого параметра
может сильно повлиять на производительность системы.
Использование Optimized Time Library для минимизации системного вызова time(2)
На платформе Solaris, системный вызов time(2) посылает трап ядру, для вызова
gethrestime(), что является продолжительной и трудоемкой операцией.
Когда MySQL выполняет каждый запрос, то вызов time(2) выполняется в начале и конце запроса,
для определения времени выполнения запроса.
При выполнении некоторых задач, MySQL может потратить до 30% времени на вызов time(2),
потребляя большое количество системных циклов центрального процессора, как показано ниже:
# truss -c -p 385
syscall seconds calls errors
read 28.286 450958 3248
write 19.516 231648
open .000 2
close .000 2
time 45.247 848307
lseek .329 6878 6800
alarm .140 2218
fdsync 1.140 5520
fcntl .364 6510
lwp_park 12.288 187383
lwp_unpark 11.134 187381
poll 4.535 67263
sigprocmask 2.072 36030
sigtimedwait .381 2506
yield .741 9829
lwp_kill .201 2512
pread .000 2
pwrite 1.040 5527
-------- ------ ----
sys totals: 127.447 2051007 10048
usr time: 101.288
elapsed: 123.750
На платформе Solaris мы можем оптимизировать системный вызов
time(2), вызывая более быстрый системный вызов gethrtime(3C), вместо gethrestime()
в ядре. Если ваша задача тратит много времени на вызов time(2), то
может иметь смысл повторно собрать MySQL DB линкуя с оптимизированной библиотекой,
описанной в
A Performance Optimization for C/C++ Systems That Employ Time-Stamping.
Для этого добавьте параметр -lfasttime в файле конфигурации в дереве исходных текстов MySQL.
Мы запускали OSDL Database Test 2 (DBT2) и получили повышение производительности MySQL почти на 7%
при использовании оптимизированного time(2) в тесте с 10-warehouse на 8-way UltraSPARC системе.
Использование Solaris OS mtmalloc для управления памятью в MySQL
На платформе Solaris, подпрограмма malloc довольно хорошо распределяет память, однако по умолчанию,
однонитевая malloc в libc обрабатывает параллельные запросы распределения памяти один за другим,
замедляя работу в многонитевых приложениях. Solaris предлагает несколько реализаций malloc, включая
mtmalloc, libumem и hoard, чтобы улучшить работу распределения памяти для многонитевых приложений.
Однако, может оказаться трудным выбрать, какая реализация malloc является лучшей для различных приложений,
поскольку это зависит от шаблона распределения памяти
приложенем и как различные алгоритмы в различной реализации malloc соответствуют
этому шаблону.
MySQL активно использует malloc() и free() тобы распределить память для неожиданно больших строк.
Запросы malloc блокируют нити mysqld для HEAP. Заменяя malloc на mtmalloc можно значительно
повысить быстродействие MySQL DB. Мы получили прирост до 65% на тесте SysBench для
системы 8-way dual-core UltraSPARC IV с MySQL 5.0.7.
тобы использовать mtmalloc вы можете загрузить библиотеку
mtmalloc с установленными переменными окружения LD_PRELOAD или LD_PRELOAD_64 в
сценарии запуска MySQL, при этом нет необходимости в пересборке.
Для 32-bit MySQL загрузка mtmalloc с установленным LD_PRELOAD:
LD_PRELOAD=/usr/lib/libmtmalloc.so (x86)
LD_PRELOAD=/usr/lib/libmtmalloc.so(sparc)
В случае 64-bit MySQL, установите LD_PRELOAD_64:
LD_PRELOAD_64=/usr/lib/amd64/libmtmalloc.so(x64)
LD_PRELOAD_64=/usr/lib/sparcv9/libmtmalloc.so(64-bit sparc)
Использование 64-bit версии MySQL в Solaris
Solaris является полностью 64-разрядной ОС, позволяющей выполнять 32-разрядные приложения
в режиме совместимости. Поэтому мы можем запустить и 64-bit и 32-bit версии MySQL.
По сравнению с 32-bit MySQL, 64-bit версия MySQL способна обращаться к большему объему памяти
для кэша данных, кэша кода и кэша метаданных для того, чтобы уменьшить число обращений к дискам, и
благодаря использованию расширенных 64-разрядных операций, 64-bit MySQL работает быстрее 32-bit MySQL.
На Рис.3 показаны результаты теста SysBench (1M-row data can be filled in the InnoDB data
and index cache buffer) на 8-way UltraSPARC IV сервере.
Сборка MySQL программным обеспечением Sun Studio 11
Для сборки 64-bit версии MySQL на Solaris с помощью Sun Studio 11,
необходимо использовать следущие флаги и опции:
Solaris OS для платформы x64
CC=cc CFLAGS="-xO3 -mt -fsimple=1 -ftrap=%none -nofstore
-xbuiltin=%all -xlibmil -xlibmopt -xtarget=opteron
-xarch=amd64 -xregs=no%frameptr"
CXX=CC CXXFLAGS="-xO3 -mt -fsimple=1 -ftrap=%none -nofstore
-xbuiltin=%all -xlibmil -xlibmopt -xtarget=opteron
-xarch=amd64 -xregs=no%frameptr"
LDFLAGS="-xtarget=opteron -xarch=amd64"
./configure --prefix=/usr/local/mysql
--localstatedir=/usr/local/mysql/data
--libexecdir=/usr/local/mysql/bin --with-extra-charsets=complex
--with-server-suffix=-standard --enable-thread-safe-client
--enable-local-infile --with-named-curses=-lcurses
--with-big-tables --disable-shared --with-readline
--with-archive-storage-engine --with-innodb
Solaris OS для платформы SPARC:
CC=cc CFLAGS="-mt -fsimple=1 -ftrap=%none
-xbuiltin=%all -xlibmil -xlibmopt -xstrconst -xarch=v9"
CXX=CC CXXFLAGS="-noex -mt -fsimple=1
-ftrap=%none -xbuiltin=%all -xlibmil -xlibmopt
-xarch=v9"
./configure --prefix=/usr/local/mysql
--localstatedir=/usr/local/mysql/data
--libexecdir=/usr/local/mysql/bin
--with-extra-charsets=complex
--with-server-suffix=-standard
--enable-thread-safe-client --enable-local-infile
--with-named-z-libs=no --with-big-tables
--disable-shared --with-readline
--with-archive-storage-engine --with-innodb
Для сравнения работы СУБД MySQL, собранной разными компиляторами (Sun Studio 10 and Sun Studio 11),
мы использовали набор тестов DBT2. Рабочая нагрузка представлена работой базы оптового поставщика,
имеющего несколько складов. Задачи представляют собой смесь "только чтение" и "часто обновляемых"
транзакций входа, оформления заказов, записей платежей и проверки уровня продаж складов.
Девять таблиц включают в себя Warehouse, District, Item, Stock, Customer, Order, New order, Order-line и History,
соответственно по числу складов (за исключением таблицы Item).
Мы выполняли тест DBT2, используя базу данных с 10-ю складами на Solaris 10 OS.
В качестве сервера использовался Sun Fire V40z на базе двухядерного процессора 2200-MHz AMD Opteron.
В ходе этого теста большинство запросов сохранялось в буфере innodb. Большая часть времени CPU было
потрачено на обработку вопросов, таким образом, его производительность сильно влияла на результаты теста.
В качестве оценочного использовался параметр количества транзакций оформления нового заказа в секунду.
Как видно из результатов, MySQL собранный Sun Studio 11 показывает увеличение
производительности примерон на 13% по сравнению со сборкой Sun Studio 10.
В дополнение к повышению производительности MySQL, полученной с помощью Sun Studio 11, по
сравнению с Sun Studio 10 на платформе x64, Sun Studio 11 также дает multicore и chip multithreading (CMT) оптимизацию
на процессорах UltraSPARC. Sun Studio 11 также включает в свой состав расширенный графический редактор, который
облегчает установку точек прерывания и исследования переменных.
Оптимизация файловой системы
Размер кластера файловой системы может иметь большое значение для производительности системы,
собенно когда MySQL работает с размером базы данных, намного превышающей оперативную память.
В Solaris, размер кластера файловой системы UFS (параметр maxcontig), по умодчанию
установлен в 128. Размер блока файловой системы на платформе SPARC равен 8 Kbytes, а на
Solaris OS для платформы x86/x64 размер блока равен 4 Kbytes.
Получить значения maxcontig и bsiz можно используя команды mkfs -F или fstyp -v.
Мы имеем опрережающее чтение кластера (128*8 килобайтов или 128*4 килобайта), даже при случайном вводе - выводе,
что может нагрузить диск и значительно ухудшить работу.
Одно из решений этой проблемы состоит в том, что мы уменьшаем значение параметра maxcontig таким образом, что
размер блока, читаемый с диска, соответствовал блоку данных.
Вы можете изменить значение maxcontig с помощью команды tunefs -a maxcontig#.
Недостаток этого решения заключается в том, что можно воздействовать на работу других приложений, работающих с
операциями ввода/вывода.
Другое решение состоит в том, чтобы разрешить Direct I/O, монтируя файловую систему с опцией --forcedirectio,
что автоматически отключит опережающее чтение. Дополнительно, поскольку у MySQLесть собственные кэши, мы можем
сэкономить ресурсы процессора, избежав двойного кэширования.
На следующем рисунке показана производительность сервера Sun Fire V65x, замеренная с помощью теста SysBench I/O
(100M-row data cannot fit in the InnoDB data and index cache buffer).
Результаты сравниваются с производительностью, полученной при значении maxcontig по умолчанию и
maxcontig равной 5 (размер передаваемого блока равен 5*4 Kbytes).
Данные InnoDB и размер Index Cache
MySQL не получает доступ к диску непосредственно, вместо этого он читает данные во внутренний буферный кэш,
читает и пишет блоки, а затем сбрасывает на диск изменения.
Если сервер просит данные,находящиеся в кэше, то данные могут быть обработаны сразу же, в противном случае,
данные запрашиваются с диска. Чем больше размер кэша, тем большего количества обращений к диску можно избежать.
Значение по умолчанию, равное 8 Мб, в большинстве случаев слишком мало.
Вам необходимо увеличить это число, когда вы видите, что %b (процент использования диска) больше 60,
svc_t (время ответа) превышает 35 msec в выводе команды iostat -xnt 5 и в выводе статистики
innodb отображается большое число операций FILE IO.
Однако чрезмерное увеличение параметра innodb_buffer_pool_size может привести к нехватке памяти для других
приложений, что приведет к замедлению работы.
Для систем, где MySQL является единственным выполняемым приложением, значение innodb_buffer_pool_size
должно составлять примерно 70%-80% имеющейся памяти, в то время как самому процессу MySQL будет достаточно
только 2-3 Меб памяти.
Режим сброса лога транзакций
InnoDB сбрасывает лог транзакций примерно раз в секунду, осуществляя эту задачу в фоновом режиме.
По умолчанию, лог сбрасывается на диск каждую транзакцию.
Самый лучший способ избежать потери данных заключается в том, чтобы использовать режим innodb_flush_log_at_trx_commit = 1.
При работе с большим числом маленьких транзакций вы можете выставить другое значение innodb_flush_log_at_trx_commit.
В случае, если этот параметр выставлен в 0, сброса лога транзакций при каждой транзакции не происходит, это уменьшит количество
дисковых операций, но увеличит риск потери данных при аварии.
В случае, если этот параметр выставлен в 2, логи сбрасываются в кэш ОС, вместо диска при каждой транзакции. Это может также уменьшить
количество обращений к диску. Хотя быстродействие и немного падает, по сравнению со значением 0, потери данных при
ошибке сервера MySQL не произойдет, хотя такой риск остается при аппаратной ошибке или ошибке ОС.
Размер буфера лога
Для больших транзакций, вместо того, чтобы сбрасывать буфер регистрации событий на диск, после совершения транзакции,
и если innodb_flush_log_at_trx_commit установлен в 1, его можно продолжать держать в памяти, что
уменьшит количество операций ввода/вывода.
Если вы видите большое количество операций ввода/вывода просматривая статус innodb, то возможно стоит
увеличить значение параметра innodb_log_buffer_size.
Для большинства рабочих нагрузок без больших транзакций, нет необходимости тратить
память впустую, устанавливая большое значение для буфера регистрации событий. Обычно бывает достаточно от 8 до 64 Мб.
Установка контрольных точек
Подсистема управления восстановлением InnoDB сбрасывает на диск страницы базы данных и журналы транзакций
для обеспечения восстановления и создания резервных копий.
Создание контрольных точек происходит в "нечетком" режиме, когда модифицированные страницы базы данных
сбрасываются из буферов маленькими пакетами.
InnoDB пишет журналы циклически, поэтому при достижении значения innodb_log_file_size
операция контрольной точки выполняется сразу, чтобы сбросить на диск измененные страницы базы данных.
Это делается для того, чтобы убедиться, что модифицированные страницы будут доступны для восстановления.
Размер каждого журнала должен быть выбран таким образом, чтобы избежать слишком частого выполнения
операции создании контрольной точки. Большой размер приводит к более редкому периоду создания
контрольных точек, что уменьшает количество операций ввода/вывода.
Вам необходимо увеличить размер журнала, если вы видите много страниц на запись в разделе
BUFFER POOL AND MEMORY вывода статуса innodb.
Однако, большой размер журнала увеличивает время восстановления базы данных после сбоя.
Размер кэша запросов
В дополнение к данным и кэшу индексного буфера, версии 4.0.1 MySQL и позже имеет
хорошую особенность, названную кэшем запроса, который хранит идентичные запросы SELECT,
выполняемые клиентами к СУБД.
Это позволяет осуществлять запросы без повторного парсинга базы. MySQL также сохраняет
результаты запросов в кэше запросов, что позволяет уменьшить число физических и логических
операций ввода/вывода при осуществлении сложных запросов.
Для некоторых приложений, выполняющих одни и те же запросы от пользователей,
кэш запроса может значительно уменьшить время ответа.
Параметр query_cache_size используется для выделения памяти под кэширование
часто выполняемых запросов, в результате чего клиенту возвращается результат без реального выполнения запроса.
Параметр query_cache_type используется для включения или отключения кэша запросов в различных ситуациях.
Для того, чтобы решить какие значения присвоить этим параметрам, вам необходимо проверить значения параметров
qcache_inserts, qcache_hits и qcache_free_memory.
qcache_inserts показывает число запросов, добавляемых в кэш, qcache_hits показывает, сколько запросов совпало
с имеющимися в кэше и qcache_free_memory показывает количество свободной памяти в кэше.
Если вы видите высокое значение для qcache_hits по сравнению с общим числом запросов, или маленькое
значение qcache_free_memory, то стоит увеличить значение query_cache_size.
В противном случае, параметр query_cache_size может быть уменьшен, чтобы освободить память для
других буферов. Если qcache_hit во время работы выставить в 0, то кэш запросов будет отключен,
так же как и при одновременном выставлении параметров query_cache_type в 0 и query_cache_size as 0.
Параметр query_cache_limit устанавливает максимальное число результатов, сохраняемых в кэше запросов.
Низкое соотношение qcache_hits к qcache_insert во время работы может быть
вызвано слишком низким значением параметра query_cache_limit.
Заключение
Выдающаяся производительность и масшабируемость MySQL могут быть еще более расширены путем
более точной настройкой параметров хранилищ MySQL под конкретные задачи.
В то время как есть много переменных, которые
воздействуют на производительность и много параметров для того, чтобы произвести настройку для каждой
конкретной задачи, в этой статье мы постарались привестиь общие рекомендации и несколько практических
советов, чтобы помочь вам оптимизировать работу InnoDB на платформе Solaris.
Мы будем рады вашим письмам с рассказами о повышении производительности MySQL на Sun.
Ресурсы
Об авторе
Luojia Chen is a software engineer in Sun's Market Development Engineering organization,
in the open source team. She is currently responsible for MySQL adoption and migration for
Sun's latest technologies, and she is focused on making
MySQL run and scale well on the Solaris platform. She can be reached at luojia.chen@sun.com
