UploadLanguage (EN)Support
BusinessMobileSocial MediaMarketingTechnologyArt & PhotosCareerDesignEducationPresentations & Public SpeakingGovernment & NonprofitHealthcareInternetLawLeadership & ManagementAutomotiveEngineeringSoftwareRecruiting & HRRetailSalesServicesScienceSmall Business & EntrepreneurshipFoodEnvironmentEconomy & FinanceData & AnalyticsInvestor RelationsSportsSpiritualNews & PoliticsTravelSelf ImprovementReal EstateEntertainment & HumorHealth & MedicineDevices & HardwareLifestyle
Upload
Sign in
Sveta Smirnova, profile picture
Uploaded bySveta Smirnova
602 views

Современному хайлоду - современные решения: MySQL 8.0 и улучшения Percona

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

Software◦
Download to read offline
1 / 120
2 / 120
3 / 120
4 / 120
5 / 120
6 / 120
7 / 120
8 / 120
9 / 120
10 / 120
11 / 120
12 / 120
13 / 120
14 / 120
15 / 120
16 / 120
17 / 120
18 / 120
19 / 120
20 / 120
MySQL 8.0 и Улучшения Percona Современному Хайлоаду — Современные Решения 10-13 августа 2020 Света Смирнова
• Инженер тех. поддержки MySQL • Автор • MySQL Troubleshooting • JSON UDF функции • FILTER clause для MySQL • Докладчик • Percona Live, OOW, Fosdem, DevConf, HighLoad... Света Смирнова 2
•Как Работать при Высокой Нагрузке •Улучшения в MySQL 8.0 Новый Словарь Оптимизатор Запросов Другие Улучшения Содержание 3
1995 Первый релиз MySQL AB MySQL 4
1995 Первый релиз MySQL AB 2006 Percona Server for MySQL MySQL 4
1995 Первый релиз MySQL AB 2006 Percona Server for MySQL 2010 Oracle купил MySQL MySQL 4
1995 Первый релиз MySQL AB 2006 Percona Server for MySQL 2010 Oracle купил MySQL MySQL для целей этого доклада MySQL 4
• Высокая нагрузка • Много соединений • Много данных О чём Этот Доклад 5
• Высокая нагрузка • Много соединений • Много данных • Другие улучшения О чём Этот Доклад 5
Как Работать при Высокой Нагрузке
Connectors: C, JDBC, ODBC, Python, ... Connection Pool: Authentication, Caches SQL interface Parser Optimizer Caches and Buffers: Global Engine-specific Storage engines: InnoDB, TokuDB, ... File system: Data, Index, logs, other files • mysqld и его файлы • Коннекторы • Оптимизатор • Кэши • Табличные движки • Управление Архитектура MySQL 7
Server Storage Engine Поток Соединения и Движка 8
? <= ядер процессора? Что Происходит с Потоками 9
? <= ядер процессора? Да Выполняются одновременно Что Происходит с Потоками 9
? <= ядер процессора? Да Выполняются одновременно Нет Ждут в очереди Что Происходит с Потоками 9
? <= ядер процессора? Да Выполняются одновременно Нет Ждут в очереди ? Может диск писать параллельно? Что Происходит с Потоками 9
? <= ядер процессора? Да Выполняются одновременно Нет Ждут в очереди ? Может диск писать параллельно? Да Запись происходит Что Происходит с Потоками 9
? <= ядер процессора? Да Выполняются одновременно Нет Ждут в очереди ? Может диск писать параллельно? Да Запись происходит Нет Ждут в очереди Что Происходит с Потоками 9
• Обычно большинство неактивны • Threads_running Много Соединений? 10
• Обычно большинство неактивны • Threads_running • Много активных? Percona Thread Pool Plugin Thread Pool in MariaDB MySQL Enterprise Thread Pool Много Соединений? 10
Основная работа ведётся в коде движка InnoDB Много Соединений и Данных 11
• Транзакционный • ACID • В стандартной дистрибуции • Подходит для большинства сценариев Почему InnoDB? 12
• 5.7 Geir Hoydalsvik @ OOW 2015 Как Производительность Росла с Версиями 13
• 8.0 Dimitri KRAVTCHUK @ PL 2018 Как Производительность Росла с Версиями 13
5.5 Несколько Buffer Pool Что Менялось в InnoDB 14
5.6 Несколько purge threads 5.6 Хранимая Optimizer Statistics 5.6 Сохранение InnoDB Buffer Pool • Percona Server с версии 5.1 Что Менялось в InnoDB 14
5.6 Несколько purge threads 5.6 Хранимая Optimizer Statistics 5.6 Сохранение InnoDB Buffer Pool 5.6 Быстрый checksum алгоритм CRC32 5.6 Undo log в отдельном tablespace 5.6 Полнотекстовые индексы 5.6 Перемещаемые tablespaces 5.6 Read only транзакции Что Менялось в InnoDB 14
5.7 Убран mutex contention для read view 5.7 SELECTs: read only по умолчанию 5.7 Не присвается ID read-only транзакциям 5.7 SELECT COUNT(*) читает clustered index 5.7 Нет redo log для временных таблиц 5.7 Temporary tablespace 5.7 Несколько потоков page cleaner 5.7 Native partitioning Что Менялось в InnoDB 14
5.7 AHI partitioned 5.7 innodb_log_checksum_algorithm 5.7 Page-level компрессия 5.7 General tablespaces Что Менялось в InnoDB 14
8.0 Системные таблицы в InnoDB 8.0 Descending индексы 8.0 MySQL data dictionary вместо собственного 8.0 Многопоточная инициализация Buffer Pool 8.0 Redo log оптимизация 8.0 CATS Что Менялось в InnoDB 14
Улучшения в MySQL 8.0
Улучшения в MySQL 8.0 Новый Словарь
• Transactional • InnoDB • Убраны *frm, *TRG и др. файлы • Atomic DDL • Crash safe DDL MySQL Data Dictionary 17
ACID online hot backup ibdata1 ib_logfile1 ib_logfile2 db1 ————– t1.ibd t2.ibd db2 ————– t1.ibd t2.ibd db1 ————– t1.frm t2.frm db2 ————– t1.frm t2.frm Другое: binary, error, audit log files База MySQL до 8.0 18
ACID online hot backup ibdata1 ib_logfile1 ib_logfile2 mysql.ibd db1 ————– t1.ibd t2.ibd db2 ————– t1.ibd t2.ibd Другое: binary, error, audit log files База MySQL в 8.0 19
Улучшения в MySQL 8.0 Оптимизатор Запросов
• Histograms Optimizer 21
• Histograms • EXPLAIN ANALYZE Optimizer 21
• Histograms • EXPLAIN ANALYZE • HASH JOIN • Для соединений НЕ по индексу! Optimizer 21
• Histograms • EXPLAIN ANALYZE • HASH JOIN • Для соединений НЕ по индексу! • NOWAIT и SKIP LOCKED Optimizer 21
1 2 3 4 5 6 7 8 9 10 0 200 400 600 800 Индексы: Данные с Одинаковыми Значениями 22
1 2 3 4 5 6 7 8 9 10 0 200 400 600 800 Индексы: Cardinality 23
1 2 3 4 5 6 7 8 9 10 0 200 400 600 800 Histograms: Данные с Одинаковыми Значениями 24
1 2 3 4 5 6 7 8 9 10 0 0.2 0.4 0.6 0.8 1 Histograms: Данные в Histogram 25
mysql> explain format=tree select * from ol where thread_id=10432 and site_id != 9939 -> order by id limit 3G *************************** 1. row *************************** EXPLAIN: -> Limit: 3 row(s) -> Filter: ((ol.thread_id = 10432) and (ol.site_id <> 9939)) (cost=0.06 rows=3) -> Index scan on ol using PRIMARY (cost=0.06 rows=33) Пример по мотивам MySQL bug #78651 EXPLAIN ANALYZE 26
mysql> explain format=tree select * from ol where thread_id=10432 and site_id != 9939 -> order by id limit 3G *************************** 1. row *************************** EXPLAIN: -> Limit: 3 row(s) -> Filter: ((ol.thread_id = 10432) and (ol.site_id <> 9939)) (cost=0.06 rows=3) -> Index scan on ol using PRIMARY (cost=0.06 rows=33) Пример по мотивам MySQL bug #78651 mysql> explain analyze select * from ol where thread_id=10432 and site_id != 9939 -> order by id limit 3G *************************** 1. row *************************** EXPLAIN: -> Limit: 3 row(s) (actual time=364.792..364.792 rows=0 loops=1) -> Filter: ((ol.thread_id = 10432) and (ol.site_id <> 9939)) (cost=0.06 rows=3) (actual time=364.789..364.789 rows=0 loops=1) -> Index scan on ol using PRIMARY (cost=0.06 rows=33) (actual time=0.417..337.585 rows=100000 loops=1) EXPLAIN ANALYZE 26
Nested-Loop Join Algorithm for each row in t1 matching range { for each row in t2 matching reference key { for each row in t3 { if row satisfies join conditions, send to client } } } Before 8.0: Block Nested Loop 27
Nested-Loop Join Algorithm for each row in t1 matching range { for each row in t2 matching reference key { for each row in t3 { if row satisfies join conditions, send to client } } } • Неэффективный! Циклы! Before 8.0: Block Nested Loop 27
Hash join в MySQL 8 • select first_name, last_name, dept_no from employees straight_join emp_dept using(emp_no) where last_name=’Neiman’ and to_date=’9999-01-01’; Hash Joins 28
Hash join в MySQL 8 • Фаза build employees emp_dept Hash table в памяти By JOIN condition hash Hash Joins 28
Hash join в MySQL 8 • Фаза probe employees emp_dept Hash table в памяти hash Client Hash Joins 28
• Обычное сканирование 1 2 3 4 5 Locked 1 2 NOWAIT и SKIP LOCKED 29
• SKIP LOCKED 1 2 3 4 5 Locked 1 2 4 5 NOWAIT и SKIP LOCKED 29
• NOWAIT 1 2 3 4 5 Locked 1 2 Error NOWAIT и SKIP LOCKED 29
• Невидимые Индексы 30
• Невидимые mysql> explain format=tree select first_name, last_name, dept_name from employees -> join dept_emp using(emp_no) join departments using(dept_no)G *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=426808.36 rows=354369) -> Nested loop inner join (cost=37002.05 rows=354369) -> Index scan on departments using dept_name (cost=1.90 rows=9) -> Index lookup on dept_emp using dept_no (dept_no=departments.dept_no) (cost=611.18 rows=39374) -> Single-row index lookup on employees using PRIMARY (emp_no=dept_emp.emp_no) (cost=1.00 rows=1) Индексы 30
• Невидимые mysql> alter table dept_emp alter index dept_no invisible; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain format=tree select first_name, last_name, dept_name from employees -> join dept_emp using(emp_no) join departments using(dept_no)G *************************** 1. row *************************** EXPLAIN: -> Nested loop inner join (cost=359050.19 rows=354369) -> Inner hash join (dept_emp.dept_no = departments.dept_no) (cost=284238.88 rows=354369) -> Index scan on dept_emp using PRIMARY (cost=519.88 rows=314995) -> Hash -> Index scan on departments using dept_name (cost=1.90 rows=9) -> Single-row index lookup on employees using PRIMARY (emp_no=dept_emp.emp_no) (cost=0.11 rows=1) Индексы 30
• По убыванию Индексы 30
• По убыванию mysql> alter table salaries add index(from_date, to_date); mysql> explain select * from salaries order by from_date, to_date desc limit 10G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: salaries partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2632946 filtered: 100.00 Extra: Using filesort Индексы 30
• По убыванию mysql> alter table salaries drop index from_date; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table salaries add index(from_date asc, to_date desc); Query OK, 0 rows affected (1 min 5.45 sec) Records: 0 Duplicates: 0 Warnings: 0 Индексы 30
• По убыванию mysql> explain select * from salaries order by from_date, to_date desc limit 10G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: salaries partitions: NULL type: index possible_keys: NULL key: from_date key_len: 6 ref: NULL rows: 10 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) Индексы 30
• Функциональные Индексы 30
• Функциональные mysql> explain select year(hire_date), count(*) as hired from employees -> group by year(hire_date)G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees partitions: NULL type: ALL possible_keys: hire_date key: NULL key_len: NULL ref: NULL rows: 299645 filtered: 100.00 Extra: Using temporary Индексы 30
• Функциональные mysql> alter table employees add index hire_date_year((year(hire_date))); mysql> explain select year(hire_date), count(gender) as hired from employees -> group by year(hire_date)G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees partitions: NULL type: index possible_keys: hire_date_year key: hire_date_year key_len: 5 ref: NULL rows: 299468 filtered: 100.00 Extra: NULL Индексы 30
• Index skip scan • Многоколоночные индексы key_part_1, key_part_2 • ... WHERE key_part_2 > N • Запросы к одной таблице • Обращается только к колонкам индекса Индексы 30
• Index skip scan mysql> show create table salariesG *************************** 1. row *************************** Table: salaries Create Table: CREATE TABLE ‘salaries‘ ( ‘emp_no‘ int NOT NULL, ‘salary‘ int NOT NULL, ‘from_date‘ date NOT NULL, ‘to_date‘ date NOT NULL, PRIMARY KEY (‘emp_no‘,‘from_date‘), KEY ‘from_date‘ (‘from_date‘,‘to_date‘), CONSTRAINT ‘salaries_ibfk_1‘ FOREIGN KEY (‘emp_no‘) REFERENCES ‘employees‘ (‘emp_no‘) ON DELETE CASCADE ) /*!50100 TABLESPACE ‘innodb_system‘ */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) Индексы 30
• Index skip scan mysql> explain select emp_no, from_date, to_date, salary from salaries -> where to_date > ’1999-01-01’G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: salaries partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2632946 filtered: 33.33 Extra: Using where Индексы 30
• Index skip scan mysql> explain select emp_no, from_date, to_date from salaries -> where to_date > ’1999-01-01’G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: salaries partitions: NULL type: range possible_keys: from_date key: from_date key_len: 6 ref: NULL rows: 877560 filtered: 100.00 Extra: Using where; Using index for skip scan Индексы 30
• CTEs • Нерекурсивные mysql> with -> dept_data as -> (select emp_no, dept_name from dept_emp join departments using (dept_no)) -> select first_name, last_name, dept_name -> from employees join dept_data using(emp_no) -> order by hire_date desc limit 3; +––––––––––––+–––––––––––+––––––––––––––––––––+ | first_name | last_name | dept_name | +––––––––––––+–––––––––––+––––––––––––––––––––+ | Bikash | Covnot | Quality Management | | Yucai | Gerlach | Production | | Hideyuki | Delgrande | Development | +––––––––––––+–––––––––––+––––––––––––––––––––+ 3 rows in set (0.00 sec) SQL DML 31
• CTEs • Рекурсивные mysql> with recursive rand_generator(id, rand_value) as -> (select 1, rand() union all select id+1, rand() from rand_generator where id < 5) -> select * from rand_generator; +––––––+–––––––––––––––––––––+ | id | rand_value | +––––––+–––––––––––––––––––––+ | 1 | 0.5599308382346582 | | 2 | 0.2151867702744778 | | 3 | 0.39614136740205935 | | 4 | 0.33514655692050843 | | 5 | 0.4873087131300091 | +––––––+–––––––––––––––––––––+ 5 rows in set (0.00 sec) SQL DML 31
• Window functions mysql> select -> row_number() over win as id, dept_no, dept_name from departments -> window win -> as (order by dept_no); +––––+–––––––––+––––––––––––––––––––+ | id | dept_no | dept_name | +––––+–––––––––+––––––––––––––––––––+ | 1 | d001 | Marketing | | 2 | d002 | Finance | | 3 | d003 | Human Resources | | 4 | d004 | Production | | 5 | d005 | Development | | 6 | d006 | Quality Management | | 7 | d007 | Sales | | 8 | d008 | Research | | 9 | d009 | Customer Service | +––––+–––––––––+––––––––––––––––––––+ SQL DML 31
• ORDER BY и DISTINCT поддерживают ROLLUP • Только для запросов с GROUP BY SQL DML 31
• LATERAL derived tables mysql> select first_name, last_name, emp_no, s.max_salary, s.from_date from employees e, -> lateral (select salary as max_salary, from_date from salaries -> where emp_no=e.emp_no order by salary desc limit 1) s order by from_date limit 10; +––––––––––––+––––––––––––+––––––––+––––––––––––+––––––––––––+ | first_name | last_name | emp_no | max_salary | from_date | +––––––––––––+––––––––––––+––––––––+––––––––––––+––––––––––––+ | Chuanyi | Tokunaga | 415039 | 46099 | 1985-02-04 | | Mori | Swiss | 35249 | 40000 | 1985-02-04 | | Mariusz | Pokrovskii | 96485 | 56664 | 1985-02-05 | | Aimee | Baja | 229120 | 67400 | 1985-02-06 | | Miyeon | Burnard | 242314 | 45492 | 1985-02-07 | | Duri | Genther | 232968 | 47505 | 1985-02-09 | | Rasikan | Khalil | 474918 | 40000 | 1985-02-10 | | Bernt | Perz | 34059 | 54776 | 1985-02-10 | | Krassimir | Raczkowsky | 467309 | 79945 | 1985-02-12 | | Jixiang | Colorni | 83117 | 65799 | 1985-02-15 | +––––––––––––+––––––––––––+––––––––+––––––––––––+––––––––––––+ SQL DML 31
• Ссылки на внешние таблицы в derived tables SQL DML 31
Улучшения в MySQL 8.0 Другие Улучшения
• Копирует базу при помощи одной команды • Быстрое создание реплики CLONE INSTANCE 33
• Проверка кастомных правил • Валидация JSON schema CHECK constraint 34
• Проверка кастомных правил • Валидация JSON schema mysql> create table even (even_value int check(even_value % 2 = 0)) engine=innodb; Query OK, 0 rows affected (0.05 sec) mysql> insert into even value(2); Query OK, 1 row affected (0.01 sec) mysql> insert into even value(1); ERROR 3819 (HY000): Check constraint ’even_chk_1’ is violated. CHECK constraint 34
5.7+ Тип данных JSON • ВалидацияJSON schema • Индексы для массивов JSON • Обновления "на месте" В том числе на реплике JSON 35
• SET PERSIST mysql> set persist innodb_file_per_table=1; Query OK, 0 rows affected (0.00 sec) mysql> select * from performance_schema.persisted_variables -> where variable_name=’innodb_file_per_table’G *************************** 1. row *************************** VARIABLE_NAME: innodb_file_per_table VARIABLE_VALUE: ON 1 row in set (0.00 sec) Variables 36
• SET PERSIST • Новая диагностика • В Performance Schema • Где установлена переменная mysql> select variable_name, variable_source -> from performance_schema.variables_info group by variable_source; +––––––––––––––––––––––––––––––––––––––––––––––+–––––––––––––––––+ | variable_name | variable_source | +––––––––––––––––––––––––––––––––––––––––––––––+–––––––––––––––––+ | activate_all_roles_on_login | COMPILED | | binlog_cache_size | COMMAND_LINE | | caching_sha2_password_auto_generate_rsa_keys | EXPLICIT | | foreign_key_checks | DYNAMIC | +––––––––––––––––––––––––––––––––––––––––––––––+–––––––––––––––––+ 4 rows in set (0.01 sec) Variables 36
• Представления из dictionary tables • Более производительная Information Schema 37
• Индексы • Более быстрая Performance Schema 38
• Индексы • Более быстрая • Новые инструменты • Ошибки сервера • Statement latency histograms • Блокировки данных Performance Schema 38
• Индексы • Более быстрая • Новые инструменты • Ошибки сервера • Statement latency histograms • Блокировки данных • Pluggable таблицы Performance Schema 38
• Традиционные объединения • events_errors_summary_by_account_by_error • events_errors_summary_by_host_by_error • events_errors_summary_by_thread_by_error • events_errors_summary_by_user_by_error • events_errors_summary_global_by_error Performance Schema Errors Summary Tables 39
• Традиционные объединения • У всех таблиц одинаковая структура mysql> DESC events_errors_summary_global_by_error; +–––––––––––––––––––+–––––––––––––––––––––+––––––+–––––+–––––––––––––––––––––+ | Field | Type | Null | Key | Default | +–––––––––––––––––––+–––––––––––––––––––––+––––––+–––––+–––––––––––––––––––––+ | ERROR_NUMBER | int(11) | YES | UNI | NULL | | ERROR_NAME | varchar(64) | YES | | NULL | | SQL_STATE | varchar(5) | YES | | NULL | | SUM_ERROR_RAISED | bigint(20) unsigned | NO | | NULL | | SUM_ERROR_HANDLED | bigint(20) unsigned | NO | | NULL | | FIRST_SEEN | timestamp | YES | | 0000-00-00 00:00:00 | | LAST_SEEN | timestamp | YES | | 0000-00-00 00:00:00 | +–––––––––––––––––––+–––––––––––––––––––––+––––––+–––––+–––––––––––––––––––––+ 7 rows in set (0,03 sec) Performance Schema Errors Summary Tables 39
• Несколько адресов для –bind-address Сеть 40
• Несколько адресов для –bind-address • Admin port • В Percona Server с версии 5.5 • extra_port Сеть 40
• Несколько адресов для –bind-address • Admin port • В Percona Server с версии 5.5 • extra_port • Убран mutex bottleneck для connect/disconnect Сеть 40
• Несколько адресов для –bind-address • Admin port • В Percona Server с версии 5.5 • extra_port • Убран mutex bottleneck для connect/disconnect • Хосты длиннее 60 знаков Сеть 40
• Более понятные Сообщения об Ошибках 41
• Более понятные • Severity, error code, subsystem 2020-07-09T11:26:23.927824Z 23 [Note] [MY-010914] [Server] Aborted connection 23 to db: ’sbtest’ user: ’root’ host: ’localhost’ (Got an error reading communication packets). Сообщения об Ошибках 41
• Более понятные • Severity, error code, subsystem • В формате JSON { "prio" : 3, "err_code" : 10914, "source_line" : 1046, "source_file" : "sql_connect.cc", "function" : "end_connection", "msg" : "Aborted connection 32 to db: ’sbtest’ user: ’root’ host: ’localhost’ (Got an error reading communication packets).", "time" : "2020-07-09T11:39:28.905387Z", "thread" : 32, "err_symbol" : "ER_ABORTING_USER_CONNECTION", "SQL_state" : "HY000", "subsystem" : "Server", "label" : "Note" } Сообщения об Ошибках 41
• Более понятные • Severity, error code, subsystem • В формате JSON • Фильтры Сообщения об Ошибках 41
• Более понятные • Severity, error code, subsystem • В формате JSON • Фильтры • Неспецифические ошибки в error log Сообщения об Ошибках 41
• Фильтры per channel Репликация 42
• Фильтры per channel • Write-set dependency tracking • Быстрый multi-thread Репликация 42
• Фильтры per channel • Write-set dependency tracking • Быстрый multi-thread • Уменьшена contention между receiver и applier Репликация 42
• Фильтры per channel • Write-set dependency tracking • Быстрый multi-thread • Уменьшена contention между receiver и applier • Можно изменять GTID_PURGED при непустой GTID_EXECUTED Репликация 42