MariaDB 10.4 - что нового

"MariaDB 10.4 - что нового" Слайды с open source database meetup, СПб, июнь 2019

Transcript

1. MariaDB 10.4 Сергей Петруня разработчик MariaDB что нового Open Source Database Meetup июнь 2019 Санкт-Петербург
2. 2 Немного истории • MariaDB 10.4 – 18 июня 2019 • MariaDB 10.3 – 25 мая 2018 • MariaDB 10.2 – 23 мая 2017 • MySQL 8.0 – 19 апреля 2018 • MySQL 5.7 – 21 октября 2015 • Percona Server for MySQL 8.0 – 21 декабря 2018 • Percona Server for MySQL 5.7 – 23 февраля 2016
3. 3 Новые фичи в MariaDB 10.4 • Оптимизатор • InnoDB • Galera 4 • Application time для версионированных таблиц
4. 4 Оптимизатор
5. 5 MariaDB 10.4 - Оптимизатор • Optimizer trace • Улучшенное построение гистограмм – новые установки по умолчанию • Condition pushdown: – Из HAVING во WHERE – В IN-подзапрподзапросы • Primary Key Filters
6. 6 Optimizer trace • Появился в MySQL 5.6 mysql> set optimizer_trace=1; mysql> <query>; mysql> select * from -> information_schema.optimizer_trace; "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `t1`.`col1` AS `col1`,`t1`.`col2` AS `col2` from `t1` where (`t1`.`col1` < 4)" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`t1`.`col1` < 4)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`t1`.`col1` < 4)" }, { "transformation": "constant_propagation", "resulting_condition": "(`t1`.`col1` < 4)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`t1`.`col1` < 4)" } ] } }, • Теперь, похожая фича в MariaDB EXPLAIN ANALYZE Optimizer trace Optimization Query Plan SQL Execution
7. 7 Цель – понимать действия оптимизатора • “Почему план запроса Х не был выбран?” – Оптимизатор его рассматривал вообще? – Какая была его стоимость (Неправильная статистика?) • Какие преобразования делались – Сгенерированные запросы часто имеют “бессмысленные” части • WHERE col=123 ORDER BY col – Делаются ли преобразования вида “WHERE colX=10 AND func(colX)” -подзапр> “func(10)” ? • Разница между двумя версиями или машинами – diff /tmp/trace_from_host1.json /tmp/trace_from_host2.json • Какое-подзапрто автоматическое использование?
8. 8 Пример 1: range optimizer • Сложное WHERE и многокомпонентный индекс create table some_events ( start_date DATE, end_date DATE, ... KEY (start_date, end_date) ); EXPLAIN: { "query_block": { "select_id": 1, "table": { "table_name": "TBL", "access_type": "range", "possible_keys": ["start_date"], "key": "start_date", "key_length": "8", "used_key_parts": ["start_date", "end_date"], "rows": 4503, "filtered": 100, "index_condition": "TBL.start_date >= '2019-06-24' and TBL.end_date < '2019-06-28'" } } } select ... from some_events as TBL where start_date >= '2019-06-24' and end_date <= '2019-06-28'
9. 9 Пример 1: range optimizer • Сложное WHERE и многокомпонентный индекс create table some_events ( start_date DATE, end_date DATE, ... KEY (start_date, end_date) ); select * from information_schema.optimizer_trace; ... "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "start_date", "ranges": ["(2019-06-24,NULL) < (start_date,end_date)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 4503, "cost": 5638.8, "chosen": true } ], ... select ... from some_events as TBL where start_date >= '2019-06-24' and end_date <= '2019-06-28'
10. 10 Пример 2: VIEW, слияние перестало работать • SELECT с большим количеством вложенных VIEW с algorithm=merge • Резкое замедление после небольших изменений в одном из VIEW – EXPLAIN показывает, что algorithm=merge больше не используется • Сначала подозревали LEFT JOIN’ы ы "view": { "table": "view_name_8", "select_id": 9, "algorithm": "merged" } "view": { "table": "view_name_8", "select_id": 9, "algorithm": "materialized", "cause": "Not enough table bits to merge subquery" } • (из-подзапрза Table Elimination, EXPLAIN показывал <64 таблицы и до, и после)
11. 11 Пример 3: Материализация не работает • Некоррелированый, тяжелый подзапрос +------+--------------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where | | 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 1000000 | Using where | +------+--------------------+-------+------+---------------+------+---------+------+---------+-------------+ "join_preparation": { "select_id": 2, "steps": [ { "transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "materialization", "possible": false, "cause": "types mismatch" } • Материализация не применима, если типы данных t1.col и t2.col “не подходят” • Неочевидное ограничение – Поняли только когда полезли с отладчиком. select * from t1 where t1.col in (select t2.col from t2 ...) or ...
12. 12 Optimizer trace - структура TRACE: steps: { join_preparation+, join_optimization+, (join_explain | join_execution)+ } join_optimization : steps { condition_processing, substitute_generated_columns, table_dependencies, ref_optimizer_key_uses, rows_estimation, considered_execution_plans, attaching_conditions_to_tables, refine_plan, } join_preparation : { expanded_query } join_preparation : { expanded_query } rows_estimation: { analyzing_range_alternatives : { ... } selectivity_for_indexes, selectivity_for_columns, cond_selectivity: 0.nnnn }
13. 13 Optimizer trace – выводы • Показывает – какие делаются преобразования – какие рассматриваются [фрагменты] планов запросов • Анализировать надо самому • Также полезно прикладывать к bug reports • Сейчас печатает самое главное – Дальше больше
14. 14 Улучшения в гистограммах
15. 15 Гистограммы / статистика • Используется оптимизатором для выбора планов • Оптимизатор MySQL/MariaDB использует индекс, как большую гистограмму (records_in_range) – Без этого – данных о selectivity нет. • Информация о распределении данных • Главным образом, condition selectivity select ... from ... where orders.ship_date BETWEEN '2019-06-01' AND '2019-06-10' and ... user.country = 'Russia' and...
16. 16 Гистограммы / статистика в MariaDB 10.0 analyze table t persistent for ... set optimizer_use_stat_tables=4; set use_stat_tables='preferably'; <сложный запрос>; • Гистограммы появились в MariaDB 10.0 • Сбор статистики дорогой – Читает всю таблицу (и все индексы) – Строит гистограмму из всех данных (это дорого) • Сборку и использование надо включать вручную • => Распространены меньше, чем хотелось бы
17. 17 Гистограммы в MySQL 8.0 analyze table t update histogram on ... <сложный запрос>; • Похожи на MariaDB: • Хранятся как JSON, можно сделать больше buckets, ... • Построение тоже недешево: – На основе @@histogram_generation_max_mem_size определяем, сколько % данных участвует в гистограмме – Читаем всю таблицу – Bernoulli sampling (кидаем монетку для каждой записи)
18. 18 Гистограммы в MariaDB 10.4 • Использование включено по умолчанию – use_stat_tables=’preferably_for_queries’ – optimizer_use_condition_selectivity=... • Сборка по-подзапрпрежнему читает всю таблицу – И Bernoulli sampling (кидаем монетку для каждой записи) • Построение гистограммы: @@analyze_sample_percentage – default=100 -подзапр из всех данных – 0 -подзапр “автоматический размер выборки” – n% -подзапр использовать n% данных.
19. 19 Проблема – коррелированные условия • Сколько записей удовлетворяет всем условиям? – MIN(1/n, 1/m) – (1/n) * (1/m) – 0? select ... from order_items where shipdate='2015-12-15' AND item_name='christmas light' 'swimsuit'
20. 20 Коррелированных условий бывает много • Точность одной гистограммы уже не важна :-подзапр( • Требует консерватизма в сборке гистограмм • Проблема сложная – PostgreSQL: очень ограниченное решение в последней версии) select ... from cars where manufacturer='Ford' and model='Focus 3' and year between 2012 and 2014 and engine_hp between 120 and 150 and embedded_gps = 'Yes' and ...
21. 21 Гистограммы в MariaDB 10.4 - выводы • Были и раньше • Помогают оптимизатору выбрать хороший план • Собирать надо вручную, теперь это делают одной командой: analyze table t persistent for columns(...) indexes (...) analyze table t persistent for all; – полный просмотр таблицы – set @@analyze_sample_percentage=0 • Аккуратно с корреляцией колонок и большим числом условий – гистограммы только по одной
22. 22 Condition pushdown
23. 23 Condition pushdown • Non-подзапрmergeable подзапрос (или VIEW) (или derived table) (или CTE) (select customer_id, SUM(amount) as TOTAL_AMT from orders where order_date BETWEEN '2017-10-01' and '2017-10-31' group by customer_id) create view oct_totals as
24. 24 Condition pushdown (2) • Основному запросу требуются только несколько клиентов (select customer_id, SUM(amount) as TOTAL_AMT from orders where order_date BETWEEN '2017 group by customer_id) create view oct_totals as select * from oct_totals where customer_id IN (1,2) ... having customer_id IN (1,2) select ... from customer C, oct_totals where C.name_uniq_key='Ivan' and C.customer_id = oct_totals.customer_id
25. 25 Condition pushdown • MariaDB 10.2: Condition pushdown в derived tables, views, нерекурсивные CTEs. • MariaDB 10.3: Condition pushdown через PARTITION BY в window functions • MariaDB 10.4 – Condition pushdown из HAVING во WHERE – Condition pushdown в IN (SELECT ...) подзапросы во WHERE • MySQL: не поддерживает • PostgreSQL: поддерживает*, кроме нерекурсивных CTEs (исправят в PG12)
26. 26 InnoDB в MariaDB 10.4
27. 27 Мгновенный ALTER TABLE • Некоторые виды ALTER TABLE можно выполнить мгновенно alter table t1 add column col1 int; ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. alter table t1 ... ,algorithm=instant; • MariaDB 10.3, MySQL 8.0: instant ADD COLUMN • Более ранние версии – INPLACE преобразования
28. 28 Мгновенный ALTER TABLE в MariaDB 10.4 • ADD COLUMN, добавить колонку не в конец • ALTER TABLE ... DROP COLUMN • Увеличение длины VARCHAR(n), INT -подзапр> BIGINT • Многие “расширяющие” изменения character set – utf8 -подзапр> utf8mb4 • Для не-подзаприндексных колонок – смена collation • Для колонок в индексах – смена collation на тот, у которого сортировка “покрывает” текущую
29. 29 Galera 4
30. 30 Galera 4 • Galera 4 является частью MariaDB 10.4 • Новые возможности – Streaming replication (Huge transactions) – Реплицированные транзакции участвуют в Group Commit – Backup locks
31. 31 Версионированные таблицы
32. 32 Версионирование таблиц • SQL:2011 включает версионирование таблиц – System-подзапрversioned tables – Application-подзапрtime periods – “Bitemporal” таблицы • Реализации – Oracle 12C – SQL Server 2016 – DB2 – MariaDB :-подзапр)
33. 33 System-versioned tables • Таблица может поддерживать версионирование: CREATE TABLE t( x INT, start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START, end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp) ) WITH SYSTEM VERSIONING; CREATE TABLE t( x INT, ) WITH SYSTEM VERSIONING; • Таблица может поддерживать версионирование: • Колонки для версий можно объявить явно:
34. 34 System-versioned tables (2) • По-подзапрумолчанию видны только последние данные SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06'; SELECT * FROM t; • Можно смотреть в прошлое • Или изучать период в прошлом: SELECT * FROM t FOR SYSTEM_TIME FROM '2016-01-01 00:00:00' TO '2017-01-01 00:00:00'; SET @@system_versioning_asof= TIMESTAMP'2016-10-09 08:07:06'
35. 35 System-versioned tables (3) • История “пишется” автоматически – Удалить нельзя • Подоходит для – Аудита – Логирования всех действий – Возможности восстановления данных • Еще там есть partitioning, привязка к номеру транзакции, ... • Доступно в MariaDB 10.3
36. 36 Application-time periods • System versioning – История изменений в БД, а не той области, про которую данные – период применимости данных= время внесения изменений в БД – Редактировать историю нельзя • Application-подзапрtime: – Приложение в явном виде управляет start_time/end_time – Возможность вносить данные до/после события – Редактирование истории, будущего и тд.
37. 37 Application-time periods • Пример CREATE TABLE emp( emp_name VARCHAR(100), ... start_date DATE, end_date DATE, PERIOD FOR emp_period(start_date, end_date) ); – Автоматически заводит constraint, start_date<=end_date
38. 38 Application-time periods (2) • Другой синтаксис: CREATE TABLE emp( emp_name VARCHAR(100), ... start_date DATE, end_date DATE, PERIOD FOR emp_period(start_date, end_date) ); • Даты можно указывать любые insert into emp values ('John', ..., DATE'2015-01-01', DATE'2020-12-31'); – Автоматически заводит constraint, start_date<=end_date
39. 39 Application-time periods (3) • DELETE/UPDATE поддерживают FOR PORTION OF: DELETE FROM emp FOR PORTION OF emp_period FROM DATE '2019-06-01' TO DATE '2019-07-01' WHERE emp_name='John'; • Вносят изменение во временной диапазон SELECT * FROM emp; +----------+------------+------------+ | emp_name | start_date | end_date | +----------+------------+------------+ | John | 2015-01-01 | 2019-06-01 | | John | 2019-07-01 | 2020-12-31 | +----------+------------+------------+
40. 40 Application-time periods (4) • Битемпоральная таблица – таблица с system versioning и с application time period одновременно.
41. 41 Ничего не забыли?
42. 42 Authentication в MariaDB 10.4 • Несколько authentication plugins для каждого пользователя • root@localhost может использовать unix socket plugin – Пользователю root пароль не будет нужен – Остальных не пустят – => Нету беспарольного root@localhost после установки (и debian-подзапрsys-подзапрmaint тоже) • Изменения в системных таблицах – mysql.user -подзапр> mysql.global_priv • Истечение паролей
43. 43 Итого
44. 44 Выводы – MariaDB 10.4 • Оптимизатор – optimizer trace – улучшенные гистограммы – улучшенный Condition pushdown • InnoDB – больше покрытие мгновенных ALTER TABLE • Galera 4 • Application time for system-подзапрversioned tables • и много чего еще!
45. 45 Спасибо за внимание!