MySQL глазами обычного программиста

обзорный доклад, по внутреннему устройству и эксплуатации MySQL

Transcript

1. НАЗВАНИЕ СЕКЦИИ MYSQL ГЛАЗАМИ ОБЫЧНОГО ПРОГРАММИСТА КЛИМОВ ЕВГЕНИЙ ОБЫЧНЫЙ ПРОГРАММИСТ
2. По каким граблям ходят разработчики всех СУБД ;) RUM теорема R (read) U (update) M (memory) overheads
3. Под какой тип нагрузки оптимизирован MySQL? Изначально для OLTP - т.е. «часто» пишем и «часто читаем», «малыми порциями» данных https://ru.wikipedia.org/wiki/OLTP Есть некоторые подвижки сторону OLAP («тяжелые» выборки с GROUP BY, ORDER BY и JOIN) https://mariadb.com/kb/en/library/mariadb-columnstore/ Быстрая batch вставка через LOAD DATA https://dev.mysql.com/doc/en/load-data.html
4. Вопросы залу Чем thread отличается от process? Чем LEFT JOIN отличается от INNER JOIN? Чем sharding отличается от partioning?
5. Как MySQL «хранит» данные в InnoDB? • Данные разбиты на «страницы» 16Kb • Внутри страниц «строки» целиком • Отдельные страницы для «вторичных индексов» и для «больших полей (BLOB, JSON, TEXT) • Страницы с данными строки «упорядочены» по clustered index (primary key, first unique key или internal 6 bytes index) Подробнее https://dev.mysql.com/doc/refman/5.7/en/innodb- physical-record.html
6. Как MySQL «пишет» данные в InnoDB? • Любые изменения сначала делаются в «странице» памяти см. innodb_buffer_pool, чтобы сделать delete или update страницу надо прочитать (см. innodb_read_io_threads) • Параллельно «изменения» формируются в log buffer (в памяти) • Log buffer скидывается на диск в ib_logdata при каждом commit, либо при заполнении, либо 1 раз в секунду см. innodb_flush_at_trx_commit • Параллельно в фоне через «thread pool» (размер ограничен innodb_write_io_threads) + O_DIRECT (чтобы) делается «сброс» в <table_name>.ibd (ibdata) в double write buffer
7. Как MySQL «читает» данные? • Из текста запроса SQL определяются индексы и таблицы из которых будет произведено чтение • Чтение идет с диска через O_DIRECT «страницами» (16kb) и кешируется в buffer pool (если страница уже в памяти, то нет смысла читать ее второй раз) • Чтение из clustered\secondary индекса «по значению» = O(log n) дисковых операций где n общее кол-во «страниц» • Для «страниц данных» даже если вам нужно 1 поле из 100, прочитается вся строка
9. Как MySQL обрабатывает много запросов «одновременно»? • Отдельный Thread на каждый коннект, можно оптимизировать через thread_cache_size и back_log • Innodb\Xtradb storage engine использует свой thread pool для «чтения» и «записи» настраивается через innodb_io_write_threads и innodb_io_read_threads (пропорционально возможностям диска и кол-ву ядер) • Есть отдельный механизм thread pools в Mariadb и Percona, для узких специализированных видов нагрузки
10. Как «мониторить» MySQL? • https://www.percona.com/downloads/percona-monitoring- plugins/LATEST/ • https://www.percona.com/software/database-tools/percona-monitoring- and-management DISASTER триггеры - репликация running status, replication lag - 90% connection usage - low innodb cache hit rate - deadlocks - high CPU\Memory usage - high iowait, high disk utilization
11. Как «отлаживать» MySQL «на живую»? • SHOW FULL PROCESSLIST; • SELECT * FROM threads WHERE PROCESSLIST_COMMAND=‘Query’ ORDER BY PROCESSLIST_TIME DESC; • tcpdump -i any -w mysql.pcap port 3306 pt-query-digest –type tcpdump mysql.pcap • slow_log=1 long_query_time=1 log-queries-not-using-indexes=1 pt-query-digest –type slowlog /var/log/mysql/slowlog.log • https://gist.github.com/benders/114299 - Unused indexes • Query response time distribution https://www.percona.com/doc/percona- server/5.7/diagnostics/response_time_distribution.html
12. Как обновлять «схему данных» в MySQL? • https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html • https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html triggers+INSERT INTO SELECT, плохо работает если таблица сильно нагружена, прерывание в середине процесса опасно, нельзя использовать foreign keys и triggers, написано на perl • https://github.com/github/gh-ost ROW binary log (меньше нагрузка), но foreign key и triggers все равно нельзя (может сделают), вместо RENAME – LOCK TABLE, нельзя NULL в индексах, не поддерживается JSON • https://github.com/facebookincubator/OnlineSchemaChange triggers + SELECT INTO OUTFILE\LOAD DATA, запускается на мастере, python – можно попробовать использовать как библиотеку для миграций • https://github.com/skeema/skeema используется в связке с pt-online-schema-change, тоже куча ограничений • https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqldbcompare.html мало кто вспоминает, но может даже INSERT данных
13. Как обновлять версию MySQL • Downgrade – очень больно, оно вам точно нужно? ;) • Основной алгоритм – логическая репликация + mysql_upgrade + failover proxy • https://www.slideshare.net/freshdaz/upgrade-to-mysql-56-without- downtime • https://www.percona.com/doc/percona-toolkit/LATEST/pt- upgrade.html DEMO stand • https://bitbucket.org/production_databases/mysql_live_upgrade
14. Как делать backup в MySQL? xtrabackup • https://github.com/ShahriyarR/MySQL-AutoXtraBackup • https://github.com/dotmanila/pyxbackup • https://blog.pythian.com/mysql-encrypted-streaming-backups-directly- aws-s3/ если хочется stream+s3/google cloud через pipes mysqldump • https://github.com/sixhop/AutoMySQLBackup Tokudb • https://github.com/markwardt-pythian/tokubackup_wrapper LVM • http://www.lenzg.net/mylvmbackup/
15. Как делать «отказоустойчивость» в MySQL? Master-slave • https://vitess.io/user-guide/introduction/ • https://github.com/github/orchestrator • https://github.com/signal18/replication-manager Master-Master • http://galeracluster.com/ • https://www.percona.com/doc/percona-xtradb-cluster/LATEST/manual/ • https://dev.mysql.com/doc/refman/5.7/en/group-replication.html Master-slave failover «ручками» • https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqlrpladmin.html
16. Как делать «sharding» в MySQL? Лучше всего делать руками в приложении (особенно решардинг) • https://vitess.io/user-guide/sharding/ • https://www.percona.com/blog/2016/08/30/mysql-sharding-with- proxysql/ • https://mariadb.com/resources/blog/schema-sharding-mariadb- maxscale-21-part-1 - платно после 3х серверов ;) • https://github.com/flike/kingshard (golang+китайцы) • https://github.com/actiontech/dble (java)
17. Вопросы из зала? Со мной можно связаться bloodjazman@gmail.com https://t.me/bloodjazman