Перенос временных файлов MySQL в память
Проверяем наличие /dev/shm:
df -h
| 1 | df-h |
Настройки размещаются в , рекомендуем указать размер, например, 1G:
none /dev/shm tmpfs defaults,size=1G 0 0
| 1 | nonedevshm tmpfs defaults,size=1G |
Если внесли изменения, то перемонтируем:
mount -o remount /dev/shm
| 1 | mount-oremountdevshm |
В конфигурационном файле указываем:
tmpdir = /dev/shm
| 1 | tmpdir=devshm |
В случае, если используется Apparmor, то внесите используемый путь (/dev/shm или /run/mysql) в конфигурационный файл /etc/apparmor.d/usr.sbin.mysqld, например:
/run/mysql/* rw,
| 1 | runmysql*rw, |
Затем перезапустите:
service apparmor restart
| 1 | service apparmor restart |
Исходные данные для настройки
Итак рассматриваем систему с установленным ISP manager на котором стоит Centos и MariaDB. Задача, оптимизировать работу Mysql и ускорить тем самым обработку запросов на сайтах. Для начала я приведу, пример своего my.cnf который находится по адресу etc/my.cnf, если у вас стоит Debian то смотреть надо в папке другой. Итак вот так выглядит настроенный файл, но иногда я все таки еще изменяю некоторые настройки, о которых расскажу ниже.
PHP
#open_files_limit = 2000
local-infile=0
innodb_file_per_table = 1
pid-file = /var/run/mysqld/mysqld.pid
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
ignore-db-dir=lost+found
max_allowed_packet = 1024M
skip-external-locking
skip-name-resolve
key_buffer = 2G
key_cache_division_limit = 70
thread_stack = 192K
tmp_table_size = 2G
max_heap_table_size = 2G
key_buffer_size = 4G
sort_buffer_size = 1G
read_buffer_size = 1G
read_rnd_buffer_size = 2G
myisam-recover = BACKUP
max_connections = 500
table-cache = 120000
table-open-cache = 120000
thread-cache-size = 500
thread-cache-size = 500
interactive-timeout = 360
query_cache_limit = 12M
query_cache_size = 4G
join_buffer_size = 512M
#log_slow_queries = /var/log/mysql/mysql-slow.log
expire_logs_days = 10
max_binlog_size = 100M
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
symbolic-links=0
bind-address = 127.0.0.1
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d
|
1 |
mysqld #open_files_limit = 2000 local-infile= innodb_file_per_table=1 pid-file=varrunmysqldmysqld.pid datadir=varlibmysql socket=varlibmysqlmysql.sock ignore-db-dir=lost+found max_allowed_packet=1024M skip-external-locking skip-name-resolve key_buffer=2G key_cache_division_limit=70 thread_stack=192K tmp_table_size=2G max_heap_table_size=2G key_buffer_size=4G sort_buffer_size =1G read_buffer_size =1G read_rnd_buffer_size=2G myisam-recover=BACKUP max_connections=500 table-cache=120000 table-open-cache=120000 thread-cache-size=500 thread-cache-size=500 interactive-timeout=360 query_cache_limit=12M query_cache_size =4G join_buffer_size=512M expire_logs_days=10 max_binlog_size =100M innodb_buffer_pool_size=4G innodb_buffer_pool_instances=4 innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT symbolic-links= bind-address=127.0.0.1 mysqld_safe log-error=varlogmariadbmariadb.log pid-file=varrunmariadbmariadb.pid !includediretcmy.cnf.d |
Набор Perl утилит для работы с MySQL
В архиве Медиа: mysql_perl_utilites.zip размещены файлы:
- access.pm — параметры доступа к MySQL для остальных скриптов.
- extract_myisam.pl — выбор MyIsam таблиц из базы и вывод в файл tables.
- convert.pl — конвертация таблиц из файла tables в InnoDb.
- drop.pl — удаление таблиц, перечисленных в файле tables.
Конвертацию можно выполнять только после включения в my.cnf поддержки InnoDb. Следует учитывать, что конвертация больших таблиц может быть очень длительной, поэтому оптимальным будет исключить из конвертации помесячные таблицы с сессиями, наработками и т.п.
Для транзакционной работы они не критичны. Достаточно, что в последующие месяцы эти таблицы также будут создаваться в формате InnoDb. Также можно предварительно отработать конвертацию таблиц на стороннем MySQL сервере.
Выберите MyIsam таблицы в файл tables с помощью скрипта extract_myisam.pl, скорректируйте файл tables, оставив только последние месячные и постоянные таблицы.
Затем выполните скрипт convert.pl.
Настройка MySQL для Windows 7
Ниже Вы можете ознакомиться с примерами файла my.ini, настроенного в соответствии с вышеописанными рекомендациями под ОС Windows 7 x32. По разным причинами, связанными в основном с обновлением программного обеспечения MySQL, некоторые переменные и методы в файле конфигурации могут вызывать конфликты при запуске службы сервера MySQL.
Стоит отметить, что начиная с версии 5.6 служба может быть занесена в реестр не как MySQL, а как MySQL56. Также, если в вашей системе остались какие-либо данные о предыдущих установках MySQL, имя службы может принять дополнительный порядковый номер, как например MySQL56_1. Присвоенные имена служб можно проверить через services.msc или диспетчер задач. Будьте внимательны, не все службы можно отследить через msconfig.exe! В случае, если служба называется не MySQL, стоит подправить строку dependencies в server.ini, который находится в установочной директории сервера BGBilling, и исправить название службы на действительное.
Итак, ниже приведены два примера my.ini. Первый использовался для запуска MySQL версии 5.1, второй — 5.6.
MySQL 5.1
port=3306 default-character-set=cp1251 default-character-set=cp1251 port=3306 basedir="C:/mysql" datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data" tmpdir = "C:/tmpserver" socket="C:/mysql/mysql.sock" user=mysql log-slow-queries=C:/mysql/log/slowquery.log skip-name-resolve # character-set-server=cp1251 default-storage-engine = INNODB default-character-set=cp1251 default-collation=cp1251_general_ci sql-mode= # max_allowed_packet=50M max_connections=800 # memlock table_cache=4096 thread_cache_size=300 thread_concurrency=2 # # InnoDb innodb_file_per_table # innodb_log_group_home_dir = "C:/mysql/lib/" innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 16M # innodb_log_files_in_group = 2 innodb_log_file_size = 128M innodb_log_buffer_size = 8M innodb_lock_wait_timeout = 120 # innodb_thread_concurrency = 2 innodb_flush_log_at_trx_commit = 2 #innodb_flush_method = O_DIRECT # # MyIsam #key_buffer_size = 1G #myisam_recover #myisam_repair_threads = 1 #myisam_data_pointer_size = 6 # # Replication #server-id=1 #log-bin=/var/lib/mysql-binlog/mysql-bin #expire_logs_days=3 #replicate-do-db=bgbilling #relay-log-space-limit = 10G # # # max_allowed_packet=50M default-character-set=cp1251
MySQL 5.6
port=3306 default-character-set=cp1251 default-character-set=cp1251 port=3306 basedir="C:/MySQL/MySQL Server 5.6" datadir="C:/ProgramData/MySQL/MySQL Server 5.6/data" tmpdir = "C:/tmpserver" socket="C:/mysql/mysql.sock" user=mysql #log-slow-queries="C:/MySQL/MySQL Server 5.6/slowquery.log" skip-name-resolve # character-set-server=cp1251 default-storage-engine = INNODB #default-character-set=cp1251 #default-collation=cp1251_general_ci sql-mode= # max_allowed_packet=50M max_connections=800 # memlock #table_cache=4096 thread_cache_size=300 thread_concurrency=2 # # InnoDb innodb_file_per_table # innodb_log_group_home_dir = "C:/MySQL/MySQL Server 5.6/lib/" innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 16M # innodb_log_files_in_group = 2 innodb_log_file_size = 128M innodb_log_buffer_size = 8M innodb_lock_wait_timeout = 120 # innodb_thread_concurrency = 2 innodb_flush_log_at_trx_commit = 2 #innodb_flush_method = O_DIRECT # # MyIsam #key_buffer_size = 1G #myisam_recover #myisam_repair_threads = 1 #myisam_data_pointer_size = 6 # # Replication #server-id=1 #log-bin=/var/lib/mysql-binlog/mysql-bin #expire_logs_days=3 #replicate-do-db=bgbilling #relay-log-space-limit = 10G # # # max_allowed_packet=50M #default-character-set=cp1251
Тюнинг базы данных Mysql варианты
Итак что я меняю и что вижу при этом. Для начала выведу основные параметры которые считаю спорными в настройке.
PHP
key_buffer = 2G
key_cache_division_limit = 70
thread_stack = 192K
tmp_table_size = 1G
max_heap_table_size = 1G
key_buffer_size = 4G
sort_buffer_size = 1G
read_buffer_size = 1G
read_rnd_buffer_size = 2G
myisam-recover = BACKUP
max_connections = 500
table-cache = 120000
table-open-cache = 120000
thread-cache-size = 500
interactive-timeout = 360
query_cache_limit = 12M
query_cache_size = 4G
join_buffer_size = 512M
|
1 |
key_buffer=2G key_cache_division_limit=70 thread_stack=192K tmp_table_size=1G max_heap_table_size=1G key_buffer_size=4G sort_buffer_size =1G read_buffer_size =1G read_rnd_buffer_size=2G myisam-recover =BACKUP max_connections =500 table-cache=120000 table-open-cache=120000 thread-cache-size=500 interactive-timeout=360 query_cache_limit=12M query_cache_size =4G join_buffer_size=512M |
Увеличение числа открытых файлов
В большинстве Linix-систем по умолчанию лимит открытия файловых дескрипторов установлен в 1024, для работы этого недостаточно.
Проверим текущие опции:
ulimit -n
| 1 | ulimit-n |
Внесем требуемые лимиты в
* hard nofile 35000
* soft nofile 35000
root hard nofile 35000
root soft nofile 35000
|
1 |
*hard nofile35000 *soft nofile35000 root hard nofile35000 root soft nofile35000 |
Динамически изменим текущие лимиты:
ulimit -n 35000
| 1 | ulimit-n35000 |
Проверим soft limit:
ulimit -Sn
| 1 | ulimit-Sn |
и hard limit
ulimit -Hn
| 1 | ulimit-Hn |
Текущие лимиты в MySQL проверим SQL-запросом:
SHOW VARIABLES LIKE ‘%open_files%’
| 1 | SHOW VARIABLES LIKE’%open_files%’ |
innodb_open_files 2048
open_files_limit 35000
|
1 |
innodb_open_files2048 open_files_limit35000 |
Сжатие и оптимизация таблиц InnoDB
Файлы ibdata1 и ib_log
Большинство проектов с таблицами InnoDB имеют проблемы с большими файлами ibdata1 и ib_log. В большинстве случаев это связано с неправильной конфигурацией MySQL/MariaDB или архитектурой БД. Вся информация из таблиц InnoDB хранится в файле ibdata1, пространство которого само не используется. Я предпочитаю хранить данные таблицы в отдельных файлах ibd*. Для этого добавьте в my.cnf следующую строку:
innodb_file_per_table
или
innodb_file_per_table = 1
Если ваш сервер настроен и у вас есть продуктивные базы данных с таблицами InnoDB, сделайте следующее:
- Сделайте резервную копию всех баз данных на вашем сервере (кроме mysql и performance_schema). Вы можете получить дамп базы данных с помощью этой команды:
- После создания резервной копии базы данных остановите сервер mysql/mariadb;
- Измените настройки в my.cfg;
- Удалите файлы ibdata1 и ib_log;
- Запустите демон mysql/mariadb;
- Восстановить все базы из резервной копии:
После этого все таблицы InnoDB будут храниться в отдельных файлах, и ibdata1 перестанет экспоненциально расти.
Сжатие таблиц InnoDB
Вы можете сжимать таблицы с текстовыми данными / данными BLOB и экономить довольно много места на диске.
У меня есть база данных innodb_test, содержащая таблицы, которые потенциально могут быть сжаты, и поэтому я могу освободить место на диске. Прежде чем что-либо делать, я рекомендую сделать резервную копию всех баз данных. Подключитесь к серверу mysql:
# mysql -u root -p
Выберите нужную базу данных в консоли mysql:
# use innodb_test;
Чтобы отобразить список таблиц и их размеры, используйте следующий запрос:
SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)" FROM information_schema.TABLES WHERE table_schema = "innodb_test" ORDER BY (data_length + index_length) DESC;
Где innodb_test — имя вашей базы данных.
Некоторые таблицы могут быть сжаты. Возьмем для примера таблицу b_crm_event_relations. Запустите этот запрос:
mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;
После его запуска вы можете увидеть, что размер таблицы уменьшился с 26 МБ до 11 МБ из-за сжатия.
Сжимая таблицы, вы можете сэкономить много дискового пространства на вашем хосте. Однако при работе со сжатыми таблицами нагрузка на процессор возрастает. Используйте сжатие для таблиц db, если у вас нет проблем с ресурсами процессора, но есть проблема с дисковым пространством.
Общие настройки
max_connections=64 — устанавливаем параметр минимальным возможным при необходимости экономить ресурсы сервера, при возникновении в логе записей вида «Too many connections…» увеличиваем значение. Не следует изменять значение этого параметра на старте. 4000 клиентов является максимумом. Можно довести максимальное количество клиентов до 7000, но для стандартных сборок 4000 является пределом.
open_files_limit = 2048 Устанавливать значение стоит опираясь на существующее количество открытых файлов MySQL:
В конфигурационном файле задается большее значение.
connect_timeout (MySQL pre-5.1.23: default 5, MySQL 5.1.23+: default 10) — количество секунд по прошествии которых сервер баз данных будет выдавать ошибку, при активном веб-сервере значение можно уменьшать чтобы увеличить скорость работы, на медленной машине — можно увеличивать. max_connect_errors (default 10) — максимальное количество единовременных соединений с сервером баз данных с хоста запрос блокируется если он прерывается запросами с того же хоста до момента окончания обработки запроса) блокируются навсегда, очистить можно только из командной оболочки MySQL:
В случае атаки на сервер нужно уменьшать (5) чтобы отсекать попытки соединения, при большой активности веб-сервера можно увеличивать max_allowed_packet (default 1M) — максимальный для буфера соединений и буфера результата при исполнении SQL инструкций. Каждый тред имеет свой буфер. Хорошим значением для начала будет 16М. tmp_table_size (system-specific default) — максимальный размер памяти выделяемой под хранение временных таблиц. 16М — довольно много.
Примеры готовых конфигураций для разных объёмов памяти можно посмотреть здесь.
Чтобы посомореть значения переменных можно воспользоваться SQL запросом:
или для конкретных переменных:
Чтобы проверить мониториг InnoDB, используте:
Чтобы узнать, не свопается ли память, используйте команду и смотрите строку swap:
Вторичная оптимизация конфига MySQL
Под вторичной оптимизацией я подразумеваю тот тюнинг, который можно произвести только зная профиль нагрузки на БД: соотношение операций чтения и записи, долгие запросы и т.п.
Тюнинг performance_schema в MySQL
Опция performance_schema производит мониторинг всей БД, на что расходуется некоторая часть ресурсов, держать эту опцию постоянно включенной в продакшене крайне не рекомендуется, т.к. может замедлять время выполнения запросов до 25%. Объем потребляемых ресурсов зависит от конфигурации схемы, которую можно посмотреть выполнив запрос:
SHOW VARIABLES LIKE 'performance%';
А если выполнить от имени администратора БД этот запрос:
SHOW ENGINE performance_schema STATUS;
С помощью этого запроса можно понять все ли данные мониторятся, или что-то пропадает:
SHOW STATUS LIKE 'performance%';
Если какой-то счетчик оказался выше ноля, то нужно увеличить соответствующий параметр.
Именно на данных из performance_schema и основана вся фишка MySQLTuner! Чем дольше собираются данные, тем точнее будут рекомендации по оптимизации MySQL и MariaDB. Стоит учесть, что данные performance_schema обнуляются после каждой перезагрузки сервера, поэтому сначала лучше выполнить первичную конфигурацию, после чего оставить сервер под боевой нагрузкой на сутки для последующего анализа.
Работа с данными performance_schema
Переходим в базу данных performance_schema :
USE performance_schema;
И смотрим какие таблицы здесь есть:
SHOW TABLES;
Обратим внимание на наблицы с префиксом setup_, например:
SELECT * FROM setup_consumers; SELECT * FROM setup_instruments;
В них содержатся настройки того, что будет мониториться. С помощью UPDATE можно менять значение колонки ENABLED с NO на YES и наоборот.
Самые горячие таблицы
С помощью этого запроса можно узнать к каким таблицам происходит наибольшее число чтений и записей:
select substring_index(file_name, '/', -1) file_name, event_name, count_read, count_write from file_summary_by_instance where COUNT_READ+COUNT_WRITE > 0 order by COUNT_READ+COUNT_WRITE desc limit 30;
А этим запросом можно узнать статистику по блокировкам:
select event_name, source, sum(timer_wait) timer_wait from events_waits_history_long where event_name not like 'wait/io/file%' group by event_name, source order by 3 desc limit 30;
Узкие места
В этом разделе описывается, как обнаруживать и отслеживать узкие места в базе данных.
Для начала, давайте включим логирование медленных запросов:
slow_query_log = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes = 1
Строки выше должны быть добавлены в конфигурацию mysql. БД будет отслеживать запросы, которые выполнялись больше чем 1 секунду, и те, которые не используют индексы.
Как только в этом логе появятся некоторые данные, вы можете проанализировать их на предмет использования индексов с помощью вышеуказанной утилиты или с помощью , которая выведет примерно такие результаты:
pt-query-digest /var/log/mysql/mysql-slow.log # 360ms user time, 20ms system time, 24.66M rss, 92.02M vsz # Current date: Thu Feb 13 22:39:29 2014 # Hostname: * # Files: mysql-slow.log # Overall: 8 total, 6 unique, 1.14 QPS, 0.00x concurrency ________________ # Time range: 2014-02-13 22:23:52 to 22:23:59 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 3ms 267us 406us 343us 403us 39us 348us # Lock time 827us 88us 125us 103us 119us 12us 98us # Rows sent 36 1 15 4.50 14.52 4.18 3.89 # Rows examine 87 4 30 10.88 28.75 7.37 7.70 # Query size 2.15k 153 296 245.11 284.79 48.90 258.32 # ==== ================== ============= ===== ====== ===== =============== # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============= ===== ====== ===== =============== # 1 0x728E539F7617C14D 0.0011 41.0% 3 0.0004 0.00 SELECT blog_article # 2 0x1290EEE0B201F3FF 0.0003 12.8% 1 0.0003 0.00 SELECT portfolio_item # 3 0x31DE4535BDBFA465 0.0003 12.6% 1 0.0003 0.00 SELECT portfolio_item # 4 0xF14E15D0F47A5742 0.0003 12.1% 1 0.0003 0.00 SELECT portfolio_category # 5 0x8F848005A09C9588 0.0003 11.8% 1 0.0003 0.00 SELECT blog_category # 6 0x55F49C753CA2ED64 0.0003 9.7% 1 0.0003 0.00 SELECT blog_article # ==== ================== ============= ===== ====== ===== =============== # Query 1: 0 QPS, 0x concurrency, ID 0x728E539F7617C14D at byte 736 ______ # Scores: V/M = 0.00 # Time range: all events occurred at 2014-02-13 22:23:52 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 37 3 # Exec time 40 1ms 352us 406us 375us 403us 22us 366us # Lock time 42 351us 103us 125us 117us 119us 9us 119us # Rows sent 25 9 1 4 3 3.89 1.37 3.89 # Rows examine 24 21 5 8 7 7.70 1.29 7.70 # Query size 47 1.02k 261 262 261.25 258.32 0 258.32 # String: # Hosts localhost # Users * # Query_time distribution # 1us # 10us # 100us ################################################################ # 1ms # 10ms # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS LIKE 'blog_article'\G # SHOW CREATE TABLE `blog_article`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT b0_.id AS id0, b0_.slug AS slug1, b0_.title AS title2, b0_.excerpt AS excerpt3, b0_.external_link AS external_link4, b0_.description AS description5, b0_.created AS created6, b0_.updated AS updated7 FROM blog_article b0_ ORDER BY b0_.created DESC LIMIT 10
Если вы предпочитаете анализировать эти логи вручную, вы можете сделать то же самое, но сначала вам нужно экспортировать лог в более анализируемый формат. Это можно сделать так:
mysqldumpslow /var/log/mysql/mysql-slow.log
С дополнительными параметрами можно отфильтровать данные, чтобы экспортировать только нужное. Например, топ-10 запросов, отсортированных по среднему времени выполнения:
mysqldumpslow -t 10 -s at /var/log/mysql/localhost-slow.log
Остальные параметры см. в документации.
Заключение
Грамотная настройка и оптимизация MySQL позволяет достичь оптимально высоких показателей работы сервера и приложений, развернутых на его основе. Этому процессу способствует запуск скриптов, которые могут быстро обнаружить проблемы, влияющие на производительность базы данных.
Автоматическую оптимизацию с помощью скрипта следует обязательно дополнять настройкой производительности в ручном режиме с помощью регулировки основных параметров СУБД. Для повышения эффективности MySQL не менее важна оптимизация работы с выборкой из нескольких объединенных таблиц.
Оцените материал:
