mysql optimization. russian
DESCRIPTION
MySQL OptimizationTRANSCRIPT
![Page 1: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/1.jpg)
«тюнинг» MySQL
IBEC brainstorm
aka
Оптимизация производительности
![Page 2: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/2.jpg)
Не будем говорить о…
Железе
Академическом составляющим MySQL или то, что можно легко найти
почитать в интернете (triggers, stored procedures, views…).
Движках (Data engines)
Партиционировании (Partitioning)
Шардинге (Sharding)
![Page 3: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/3.jpg)
О чем будем говорить?
О чем должен знать каждый разработчик?
Что такое индексы и как их правильно использовать
Базовые настройки MySQL my.cnf (my.ini)
Системные переменные (System Variables) и как ими манипулировать
Мониторинг сервера БД на узкие места
Оптимизация запросов к БД
Знаем ли мы фичи наших IDE? HeidiSQL, Navicat.
Репликации
О чем нибудь да поговорим. PHP, byte code, gzip.
![Page 4: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/4.jpg)
Индексы
Почему сначала об индексах?
Что это такое вообще?
Обычно работаем с B tree индексами, о них и поговорим
![Page 5: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/5.jpg)
Принцип работы B tree
https://bubbl.us/
Отсортированы по порядку
Легко делать поиск
Левый крайний нод – min value
Правый крайний нод – max value
Левный нод не будет иметь значение больше или равно чем родитель (X).
Правый нод - минимальное значение - это Х. Будет больше или равно X
* Ключи хранятся в индекском файле упорядоченном по принципуСбаларнсированного бинарного дерева
![Page 6: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/6.jpg)
Алгоритм поиска
Linear (by sequence)
Sorted 4 binary search
![Page 7: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/7.jpg)
Поиск по индексу
Ищем id-354
354? false
48<354? true
48=354? false
![Page 8: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/8.jpg)
Поиск по индксу
Ищем id-354
354? true
![Page 9: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/9.jpg)
Пример B+ дерева, связывающего ключи 1-7 с данными d1-d7. Связи (выделены красным) позволяют быстро обходить дерево в порядке возрастания ключей.Каждый листовой узел содержит указатель на следующий для ускорения обхода.
![Page 10: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/10.jpg)
Забегаем вперед. EXPLAIN
Как посмотреть какой индекс используется? EXPLAIN <select query>
3 интересных поля: Используемый ключь Количество проверяемых столбцов. Нужно стараться минимизировать. Экстра. То что делает БД в процессе исполнения запроса.
Хорошо: Using index, Плохо: Using filesort, create tmp tbl, create tmp tbl on disk
![Page 11: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/11.jpg)
Практика, самая первая +
EXPLAIN
SELECT * FROM t WHERE t.a*=x SELECT id FROM mytest where id4=80
Query time 0.073s
Query time 0.002s
![Page 12: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/12.jpg)
Далее…
SELECT * FROM t WHERE t.a=x LIMIT 10
SELECT * FROM t WHERE t.a=x ORDER BY t.b LIMIT 10
Query time 0.060s
![Page 13: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/13.jpg)
Еще…
MySQL не знает как использовать 2 разных индекса одной таблицы.
Решение? Композиционный ключь.
Индекс(Фамилия, имя) – составной ключь. Нельзя искать сначала И, потом Ф. Ф потом И.
“Using filesort” – плохо. sort_buffer_size –2M (default )
ALTER TABLE mytest ADD INDEX ixId23(id2,id3);
Тада!
Обратная сортировка DESC не меняет сложность запроса Индекс (А,B) типо= индекс (A). А можно удалить
![Page 14: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/14.jpg)
Еще чуть об индексах
Индекс(A,B,C)позволяет:
WHERE A=10 AND B>10;
WHERE A=10 AND B=11 AND C=12;
WHERE A=10 AND B=11 ORDER BY C;
не позволяет:
WHERE B=10;
WHERE A=10 AND B>10 AND C>10
SELECT... WHERE func(idx)=20 - не будет использоватьSELECT... WHERE idx = func(20) - может использовать индекс
![Page 15: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/15.jpg)
Ах да…
Существует граничное условие, начиная с которого использование индекса начинает проигрывать последовательному сканированию всей таблицы. Происходит это тогда, когда количество выбираемых с помощью индекса записей больше определенного процента (~30% нужно найти в справочнике точную цифру) от общего количества записей в таблице.
Не нужно индексировать все подряд. MySQL все равно выберет какой-то один, но зато вы проиграетена insert, update.
![Page 16: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/16.jpg)
По-сложнее. Пагинатор
Все плохо SELECT * FROM t WHERE t.a=x LIMIT 1000,10
Handler_read_next = 1009
Может хостер не виноват? Google, Яндекс и другие десятки поисковиков.
![Page 17: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/17.jpg)
Как решить? EXPLAIN врет!
SELECT id FROM mytest WHERE id>1000 ORDER BY id LIMIT 10
Что говорит Handler_read_next? - 9!
![Page 18: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/18.jpg)
Чуть сложнее
SELECT * FROM t WHERE t.a=x AND t.b=y AND t.c>z ORDER BY t.d
3 условия WHERE и сортировка
Какой индекс будет оптимальным, Abc, bcd, abd?
Abc, abd. Индексы не любят когда над ними производятся функкции и выражения A и B можно менять местами, математические действия всегда в конце.
![Page 19: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/19.jpg)
Ладно. Highload
Умный человек сказал «Преждевременная оптимизация – корень всех зол»
Есть проект
Нагрузка выросла. Клиенты ругаются на производительность.
Что можем сделать на уровне MySQL?
![Page 20: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/20.jpg)
Лог медленных запросов
Log_slow_queries; long_query_time=1
долгие запросы
Log_queries_not_using_indexes
запросы не использующие индексы
Shell> Mysqldumpslow
Всевозможные патчи mysqlsla, mysql_parse_slow etc
http://www.mysqlperformanceblog.com
![Page 21: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/21.jpg)
Тюнинг My.cnf (my.ini)
Настройки по умолчанию – плохо Основа: Использование памяти лучше чем использование диска. Что важнее? Настройка потребления памяти Не пытайтесь тюнить все, для большинства приложении нужно изменить всего
несколько параметров
![Page 22: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/22.jpg)
Хороший тон тюнинга. Правила
Никогда не делать изменения сначала на продакшне Иметь хорошие инструменты для бенчмарка Одно изменение за одно время В голове перекиньте возможные изменения Пробуйте каждое изменение отдельно Пробуйте в комбинации 2, потом 3 изменении итд Мониторить результат
Время за один запрос, Среднее время запроса Задокументируйте
![Page 23: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/23.jpg)
Важные параметры
key_buffer_size – кэш индексов MyISAM- Используется для временных таблиц даже если все Innodb- 30% если только MyISAM, 32M если наоборот.- Временные таблицы в MyISAM- Данные MyISAM не кеширует, они в кэше ОС
table_cache – число открытых таблиц- См рост opened_tables
query_cache_size – Внутренний кэш MySQL
- Если запросы не менялись, результат берется из кэша.- Может быть полезен если нет умного кэширования.- 32М обычно достаточно.
![Page 24: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/24.jpg)
Как работает key cache
На чтение. Смотрит в буфер, если есть возвращает, если нет считывает MYI файл На запись. Пробивает индекс, убирает все ключи.
Наблюдайте за переменными состояния Key_reads и Key_read_requests, отношение Key_reads/Key_read_requests должно быть как можно меньше (< 0,01).Если это отношение велико, то размер буфера стоит увеличить.
![Page 25: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/25.jpg)
Именованные кэши
mysql> SET GLOBAL mycache.key_buffer_size = 2 * 1024 * 1024;
mysql> CACHE INDEX mytable IN mycache;
mysql> LOAD INDEX INTO CACHE mytable;
![Page 26: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/26.jpg)
Особенности кэш запросов MySQL
Не зависит от движка.
Только похожие запросы. Байт в байт.
- SELECT * from и SELECT * FROM – не одно и то же
Подзапросы не кэшируются отдельно. Нужно? Разбей запрос на несколько.
NOW(), RAND() и.т.д. мешают кэшу.
UPDATE posts SET view_count = view_count+1 WHERE id=10;Зло -Любое изменение таблицы удаляет из кэша все запросы использующие данную таблицу
Решение? – Вынеси счетчик отдельно
![Page 27: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/27.jpg)
Что замерять с key cache?
Hit ratio – эффективность использования и % заполнения буфера Misses – не попадание в кэш. Зайцев говорит полезнее смотреть сюда =)
100 - Key_reads * 100 / Key_read_requests
100 – 50529 * 100 / 58224057 = 99,91Hit ratio
MissesKey_reads / uptime
50529 / 94162 = 0,5 … 1
![Page 28: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/28.jpg)
Размер имеет значение.
innodb_buffer_pool_size
- Кэшируются и данные и индексы. Отдавай все- Сервер: 50-70% памяти. 16гб – 12-14гб- Локал: 25-50% памяти 2гб – 500мб- 2-2,5гб для 32бит
innodb_log_file_size
- 5М по умолчанию – «хаха»- Большой лог быстрее запись. Не нужно делать кусками.- Сначала записывает в лог-файл, то что будет делать, затем делает. Если падает
смотрит в лог-файл и завершает.- Чем больше значение, тем меньше время восстановления. Поэтому 256M хватит
![Page 29: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/29.jpg)
Как работает InnoDB bufer pool
innodb_buffer_pool_size
![Page 30: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/30.jpg)
InnoDB
Innodb_log_buffer_size – буфер для innodb_log_file
- Чем меньше, тем чаще идет flush на диск, соответственно медленно работаем- 4-8М обычно достаточно
innodb_flush_log_at_trx_commit=1 (default)
- InnoDB такой тормоз! Так ли это? Смотри дефолтное значение- 1 – при каждом insert, update идет запись на диск. Buffer then flush immediately
- 2 – данные пишутся в буфер и flush примерно 1 раз в секунду. Good!
Innodb_file_per_table – каждая таблица в отдельном файле
Импорт 1-2 часа 12МБ, 0,5-1минут с настройками.
![Page 31: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/31.jpg)
Как и что мониторить?
EXPLAIN. EXPLAIN + UPDATE = EXPLAIN SELECT
SHOW FULL PROCESSLIST. Попадание 9 из 10-и.
Логирование медленных запросов
long_query_time
Log_slow_queries
Log_queries_not_using_indexes
![Page 32: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/32.jpg)
Что мы должны знать?
Манипуляция c MySQL сервером осуществляется через System Variables- mysql> SHOW [GLOBAL|SESSION] VARIABLES [LIKE "string"];- SET [GLOBAL] <variable>=<value>- устанавливается через my.cnf
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
SHOW VARIABLES SHOW STATUS
SHOW STATUS [like “string”] – показывает чем вообще система занимается, занималась.http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html
![Page 33: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/33.jpg)
Show status
Если значение Opened_tables велико, возможно, что значение
переменной table_cache слишком мало.
Если значение Key_reads велико, возможно, что значение
переменной key_buffer_size слишком мало. Читает с диска.
Key_reads/Key_read_requests = Частота успешных обращений к кэшу
Если значение Created_tmp_disk_tables велико, возможно, необходимо
увеличить значение переменной tmp_table_size, чтобы временные
таблицы располагались в памяти, а не на жестком диске
http://www.mysql.ru/docs/man/SHOW_STATUS.html
![Page 34: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/34.jpg)
Наши IDE
![Page 35: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/35.jpg)
Navicat, HeidiSQL
![Page 36: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/36.jpg)
MySQL Tuning Primer
tuning-primer.sh - https://launchpad.net/mysql-tuning-primer
MAX CONNECTIONS
Current max_connections = 200
Current threads_connected = 7
Historic max_used_connections = 7
The number of used connections is 3% of the configured maximum.
You are using less than 10% of your configured max_connections.
TABLE CACHE
Current table_cache value = 256 tables
You have a total of 135 tables
You have 137 open tables.The table_cache value seems to be fine
![Page 37: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/37.jpg)
Оптимизация схемы. Size does
matter
Использование подходящих типов данных, меньше лучше. Быстрее.
Занимают меньше места на диске, в памяти, в кэше процессора.
Возраст это tinyint а не varchar
Сравнение целых чисел дешевле сравнения символов.
Зайцев говорит избегайте Null, нужно Not Null. Усложняются индексы,
статистика индексов
DATETIME vs TIMESTAMP (hollywar?) – можно хранить один и тот же тип данных.
TIMESTAMP – вдвое меньше места,
позволяет работать с часовыми поясами. Есть «–».
![Page 38: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/38.jpg)
Оптимизация схемы
VARCHAR vs CHAR
VARCHAR – переменная длинна. Столько места сколько необходимо. (V<255)? +1 : +2 (байта)
CHAR – полезен при коротких записях. 4inst: md5(). Фикс длинны - не подвержена фрагментации
VARCHAR(1), CHAR(1) – (Y,N) – 2 байта и 1 байт соответственно - в однобайтовой кодировке канеш.
ENUM – каждое значение = целое число. В действительности хранятся целые числа, а не строки.
ENUM(‘fish’,’apple’,’dog’); SELECT e+1; // 2,3,4
ENUM(‘1’,’3’,’2’) - INSERT VALUES(1,2,3) – SELECT e+1 // 2,4,3
![Page 39: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/39.jpg)
Оптимизация схемы
Как хранить IP?
IP – не строка, а 32-битное беззнаковое число. Точки лишь для удобства.
IP - VARCHAR? Bad. Храним как беззнаковое целое число.
- INET_ATON() и INET_NTOA()
и.т.д. «Кури доки» В.В.
![Page 40: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/40.jpg)
Procedure ANALYZE()
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])
![Page 41: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/41.jpg)
Партиционирование ? Разбиение больших таблиц на логические части по выбранным критериям 1 таблица, физически разные файлы. В MyISAM по 3 на каждую партицию.
SELECT * FROM orders_range WHERE order_date='2009-08-01';
Table - orders_range3
Index - p_2008
![Page 42: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/42.jpg)
Репликация
Масштабирование вертикальное; масштабирование горизонтальное.
Master – пиши, slave – читай. Binary log
1 слейв - 1 мастер, 1 мастер – 1..* реплик
Мастер пишет изменения в бинарный лог Слейв стягивает себе бинарный лог и последовательно запускает запросы
![Page 43: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/43.jpg)
Чем хороша?
Дополнительные индексы. Можно одни на мастере, другие на реплике
Разные движки. Fulltext индексы. Master – InnoDB, Slave – MyISAM
Производительность и масштабируемость
Отказоустойчивость.
- Если отказала реплика, читай с мастера
Резервное копирование данных
- Останови реплику, сделай с нее mysqldump
![Page 44: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/44.jpg)
Настройка мастера
IP мастера - 192.168.1.101, IP реплики - 192.168.1.102
Мастер:
[mysqld]:server-id = 1 #уникальный ID сервераlog-bin = /var/lib/mysql/mysql-bin #путь для бинарных логовreplicate-do-db = testdb #имя БД для репликации
Настроить сервер Задать права
mysql@master> GRANT replication slave ON "testdb".* TO "replication"@"192.168.1.102" IDENTIFIED BY "password";
restart
![Page 45: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/45.jpg)
Настройка реплики
[mysqld]:server-id = 2relay-log = /var/lib/mysql/mysql-relay-binrelay-log-index = /var/lib/mysql/mysql-relay-bin.indexreplicate-do-db = testdb
mysql@replica> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000003 ", MASTER_LOG_POS = 98;
mysql@replica> start slave;
Значения MASTER_LOG_FILE и MASTER_LOG_POS берем с мастера.
![Page 46: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/46.jpg)
Балансировка
Round Robin DNS
NGINX.
- Понятие «обратное проксирование».
- Значение proxy_pass
- Алгоритм “Round-Robin” (Привет «Вконтакте»)
- nslookup mail.ru
Модуль - ngx_http_upstream_module.
![Page 47: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/47.jpg)
upstream highload /*myCloudName*/ {server 192.168.0.1 [weight=3] [max_fails=3] [fail_timeout=60];server 192.168.0.2 [max_fails=3] [fail_timeout=60];server 192.168.0.3 [weight=2] [max_fails=3] [fail_timeout=60];
}
server {listen 89.252.34.107:80;server_name highload.uk;location / {
proxy_pass http:// highload/;proxy_next_upstream error timeout invalid_header http_500 http_503;
}}
![Page 48: MySQL Optimization. Russian](https://reader030.vdocuments.us/reader030/viewer/2022020714/5594adc41a28ab82408b458c/html5/thumbnails/48.jpg)
Что читать?
Справочное руководство. www.mysqlperformanceblog.com