СУБД. Лекция 8

Стоимость типовых операций

Конфигурирование

• /etc/my.cnf
• /etc/mysql/my.cnf

$ which mysqld
/usr/sbin/mysqld
$ /usr/sbin/mysqld --verbose --help | grep -A 1 ‘Default options’
Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf

Конфигурирование

В Debian/Ubuntu крайне не рекомендуется править файл /etc/mysql/my.cnf, так как он распространяется вместе с пакетом MySQL.

В конце файла есть строки:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

Специфические настройки следует держать в файлах вида: /etc/mysql/conf.d/custom.cnf (суфикс файла должен быть .cnf).

Это сильно упрощает администрирование MySQL.

Область видимости

• Переменная query_cache_size имеет глобальную область видимости.
• Переменная sort_buffer_size имеет глобальное значение по умолчанию, но может быть изменена на уровне сеанса.
• Переменная join_buffer_size имеет глобальное значение по умолчанию, может быть изменена на уровне сеанса, но, кроме того, для каждого запроса, в котором соединяется несколько таблиц, можно выделить по одному буферу на операцию соединения, то есть для одного запроса может существовать несколько буферов соединения.

Область видимости

• SET sort_buffer_size = <value>;
• SET GLOBAL sort_buffer_size = <value>;
• SET @@sort_buffer_size := <value>;
• SET @@session.sort_buffer_size := <value>;
• SET @@global.sort_buffer_size := <value>;

Настройка использования памяти

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

Кэш MyISAM

key_buffer_size

25 – 50 % от общего объема памяти, зарезервированного для MyISAM кэшей.

key_buffer_1.key_buffer_size = 1G
key_buffer_2.key_buffer_size = 1G

CACHE INDEX t1, t2 IN key_buffer_1;
LOAD INDEX INTO CACHE t1, t2;

Эту SQL-команду можно поместить в файл, выполняемый MySQL на этапе запуска. Имя файла задается с помощью параметра init_file.

В нем может быть несколько SQL-команд, каждая в отдельной строке.

Кэш MyISAM

Коэффициент попаданий в кэш:

`100 - ("key_reads" * 100)/("key_read_requests")`

Коэффициент заполненности буфера:

`100 - ("key_blocks_unused" * "key_cache_block_size" * 100)/("key_buffer_size")`

Количество непопаданий в кэш за секунду:

`"key_reads" / "uptime"`

Кэш InnoDB

innodb_buffer_pool_size

В отличие от кэша ключей MyISAM, в пуле буферов InnoDB кэшируются не только индексы, там также хранятся сами данные, буфер вставок, блокировки и другие внутренние структуры. В InnoDB пулбуферов используется также для реализации отложенных операций записи и позволяет объединить несколько таких процедур, чтобы затем выполнить их последовательно.
Рекомендуется выставить до 80% физической памяти.

innodb_max_dirty_pages_pct

Говорит InnoDB о допустимом количестве «грязных» (модифицированных) страниц в пуле буферов.

Кэш InnoDB

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

SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;
SET GLOBAL innodb_buffer_pool_dump_now = ON;
SET GLOBAL innodb_buffer_pool_load_now = ON;

SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

Кеш потоков, кеш таблиц

thread_cache_size

Определяет максимальное количество потоков в кэше.

SHOW STATUS LIKE 'Threads_created';
SHOW STATUS LIKE 'Threads_connected';

table_open_cache

Кэш открытых таблиц.

table_definition_cache

Кэш определений таблиц.

Ввод / вывод в MyISAM

delay_key_write

Определяет, когда будут сбрасываться данные на диск.

OFF

MyISAM сбрасывает измененные блоки из буфера ключей после каждой записи, если только таблица не блокирована командой LOCK TABLES.

ON

Включен режим отложенной записи ключей, но только для таблиц, созданных с параметром DELAY_KEY_WRITE.

ALL

Для всех таблиц типа MyISAM используется отложенная запись ключей.

ALTER TABLE sometable DELAY_KEY_WRITE = 1;

Ввод / вывод в MyISAM

• Если сервер аварийно завершает работу, а блоки не были сброшены на диск, то индекс будет испорчен.
• Если было отложено много операций записи, то MySQL потратит больше времени на закрытие таблицы, поскольку вынуждена ждать завершения записи буферов на диск.
• По тем же причинам команда FLUSH TABLES может занимать много времени.
• Не сброшенные «грязные» блоки в буфере ключей могут не оставить места для новых блоков, считываемых с диска. В таком случае выполнение запроса будет приостановлено на время, пока MyISAM не освободит достаточно места в буфере ключей.

Ввод / вывод в InnoDB

Ввод / вывод в InnoDB

innodb_log_file_size

Общий максимальный размер файла логов транзакций.

innodb_log_files_in_group

Количество файлов в группе.

innodb_log_buffer_size

Размер буфера лога транзакций.

Ввод / вывод в InnoDB

mysql> pager grep sequence
PAGER SET TO 'grep sequence'

mysql> SHOW engine innodb STATUS\G SELECT sleep(60); SHOW engine innodb STATUS\G
Log sequence number 84 3836410803
 1 row IN SET (0.06 sec)
 1 row IN SET (1 min 0.00 sec)
Log sequence number 84 3838334638
 1 row IN SET (0.05 sec)

mysql> SELECT (3838334638 - 3836410803) / 1024 / 1024 AS MB_per_min;

+------------+
| MB_per_min |
+------------+
| 1.83471203 |
+------------+

Размер лога выбираем примерно на 10-60 минут работы сервера.

Ввод / вывод в InnoDB

innodb_flush_log_at_trx_commit

0

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

1

Писать буфер в файл журнала и сбрасывать его на устройство постоянного хранения при каждой фиксации транзакции.

2

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

Табличное пространство

Расположение табличного пространства:

innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G

Чтобы табличное пространство могло расти, когда место заканчивается, можно сделать последний файл автоматически расширяемым:

...ibdata3:1G:autoextend
...ibdata3:1G:autoextend:max:2G

innodb_file_per_table

Отдельный файл на каждую таблицу.

Оптимизация файловой сортировки

max_length_for_sort_data

Двухпроходный алгоритм применяется, если суммарная длина всех столбцов, отбираемых запросом, плюс длина столбцов, упоминаемых во фразе ORDER BY, превышает max_length_for_sort_data байтов.

max_sort_length

При сортировке по столбцам типа BLOB или TEXT MySQL принимает во внимание только префикс, а остаток значения игнорирует. Длина такого префикса задается параметром max_sort_length.

Переменные состояния

SHOW GLOBAL STATUS;

mysqladmin extended -u root -p -r -i60

Aborted_clients

Если эта переменная со временем растет, проверьте, корректно ли закрываются соединения. Если нет, обратите внимание на производительность сети, а также на конфигурационную переменную max_allowed_packet.

Aborted_connects

Значение этой переменной должно быть близко к нулю. Если это не так, то, возможно, имеют место проблемы с сетью.

Переменные состояния

Binlog_cache_disk_use и Binlog_cache_use

Если отношение Binlog_cache_disk_use к Binlog_cache_use велико, попробуйте увеличить значение binlog_cache_size. Самый лучший подход – увеличить параметр binlog_cache_size и посмотреть, уменьшится ли число непопаданий в кэш.

Bytes_received и Bytes_sent

Эти значения помогают понять, не слишком ли велик трафик в направлении к серверу или от него. Возможно, причина таится где-то в вашем коде.

Переменные состояния

Created_tmp_disk_tables

Если это значение велико, то возможно одно из двух: либо запросы создают временные таблицы в результате выборки столбцов типа BLOB или TEXT, либо недостаточно велики значения конфигурационных параметров tmp_table_size и/или max_heap_table_size.

Handler_read_rnd_next

Отношение Handler_read_rnd_next / Handler_read_rnd дает приблизительную оценку среднего размера полного сканирования таблиц. Если оно велико, то, возможно, следует оптимизировать схему, индексы или запросы.

Переменные состояния

Key_blocks_used

Если величина Key_blocks_used * key_cache_block_size гораздо меньше, чем параметр key_buffer_size на прогретом сервере, то размер буфера ключей (key_buffer_size) больше необходимого, и вы только впустую растрачиваете память.

Key_reads

Понаблюдайте за количеством операций чтения в секунду и посмотрите, насколько близко это значение приближается к предельным показателям подсистемы ввода/вывода.

Переменные состояния

Open_tables и Opened_tables

Сравните это значение с величиной параметра table_cache. Если количество открываемых таблиц (Opened_tables) в секунду велико, то, вероятно, размер кэша таблиц (table_cache) недостаточен.

Select_full_join

Полное соединение ― это соединение без индексов, такая операция может очень сильно «посадить» производительность. Лучше, чтобы их вовсе не было, даже одного в минуту может быть много. Обнаружив соединение без индексов, примите все меры к оптимизации запросов.

Переменные состояния

Sort_merge_passes

Большое значение этой переменной означает, что надо бы увеличить размер буфера сортировки (sort_buffer_size), быть может, только ради некоторых запросов. Проверьте запросы и найдите среди них те, которые приводят к сортировке (filesort). Возможно, их удастся оптимизировать.

Threads_created

Если это значение велико или растет, то, возможно, стоит увеличить параметр thread_cache_size. Переменная Threads_cached показывает, сколько потоков уже находится в кэше.

Репликация

Распространение данных

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

Балансировка нагрузки

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

Репликация

Резервное копирование

Репликация ― это ценное подспорье для резервного копирования. Однако подчиненный сервер все же не может использоваться в качестве резервной копии и не является заменой настоящему резервному копированию.

Аварийное переключение на резервный сервер (failover)

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

Репликация

Тестирование новых версий MySQL

Очень часто на подчиненный сервер устанавливают новую версию MySQL и перед тем как ставить ее на промышленные серверы, проверяют, что все запросы работают нормально.

Передача изменений в приложение (libslave)

Все изменения данных могут передаваться для анализа в стороннее приложение непосредственно после фиксации в базе.

Репликация

• Главный сервер записывает изменения данных в двоичный журнал. Эти записи называются событиями двоичного журнала.
• Подчиненный сервер копирует события двоичного журнала в свой журнал ретрансляции (relay log).
• Подчиненный сервер воспроизводит события из журнала ретрансляции, применяя изменения к собственным данным.

Варианты взаимодействия

Мастер-слейв

Подчиненный сервер повторяет состояние главного и не может изменять данные самостоятельно.

Мастер-мастер

Оба сервера равнозначны и могут обрабатываь запросы как на чтение, так и на изменение данных.

Варианты реализации

Физическая

Передаётся информация о физическом изменении страниц базы данных.

Логическая

Передаётся информация об измененни записей базы данных.

Передача запросов

Передаётся информация о выполненных запросах.

Гарантии репликации

Синхронная

Мастер-сервер не подтверждает транзакцию до того, как реплика не подтвердит получение данных.

Асинхронная

Мастер-сервер не ждёт подтвержения получения данных от реплики.

Семисинхронная

Мастер-сервер не подтверждает транзакцию до того, как "живые" реплики не подтвердят получение данных.

Настройка репликации

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

Настройка репликации

Завести учетные записи репликации на каждом сервере.

GRANT
	REPLICATION SLAVE,
	REPLICATION CLIENT
ON *.* TO repl@’192.168.0.%’
IDENTIFIED BY ‘p4ssword’;

Настройка репликации

Сконфигурировать главный и подчиненный сервера.

• Сконфигурировать главный сервер.

  log_bin = mysql-bin
  server_id = 10
  

  SHOW MASTER STATUS;
  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  | mysql-bin.000001 |       98 |              |                  |
  

• Сконфигурировать подчиненный сервер.

  log_bin = mysql-bin
  server_id = 2
  relay_log = mysql-relay-bin
  log_slave_updates = 1
  read_only = 1
  

Настройка репликации

Сказать подчиненному серверу, чтобы он соединился с главным и начал реплицировать данные с него.

CHANGE MASTER TO
	MASTER_HOST = 'server1',
	MASTER_USER = 'repl',
	MASTER_PASSWORD = 'p4ssword',
	MASTER_LOG_FILE = 'mysql-bin.000001',
	MASTER_LOG_POS = 0;

SHOW SLAVE STATUS;

START SLAVE;

Синхронизация репликации

• Мгновенный снимок данных главного сервера в некоторый момент времени.
• Текущий файл журнала главного сервера и смещение от начала этого файла в точности на тот момент времени, когда был сделан мгновенный снимок. Вместе они называются координатами репликации, так как однозначно идентифицируют позицию в двоичном журнале. Найти координаты репликации вам поможет команда SHOW MASTER STATUS.
• Файлы двоичных журналов главного сервера с момента мгновенного снимка до текущего момента.

Синхронизация репликации

Холодная копия

Остановить сервер, который впоследствии станет главным, и скопировать файлы с него на подчиненный сервер. Недостаток такого решения очевиден: в течение всего времени копирования главный сервер должен быть остановлен.

Горячая копия

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

Использование mysqldump

Если все таблицы имеют тип InnoDB, то можно воспользоваться такой командой:

mysqldump --single-transaction --all-databases --master-data=1 \
--host=server1 | mysql --host=server2

С помощью мгновенного снимка LVM или резервной копии

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

На основе другого подчиненного сервера

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

Правила репликации

• У каждого подчиненного сервера MySQL может быть только один главный.
• У каждого подчиненного сервера должен быть уникальный идентификатор.
• Один главный сервер может иметь много подчиненных (иными словами, у подчиненного сервера может быть много «братьев»).
• Подчиненный сервер может распространять полученные от главного изменения далее, то есть выступать в роли главного сервера для своих подчиненных; для этого следует включить режим log_slave_updates.