Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics

1 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Query Optimization With MySQL 8.0 and MariaDB 10.3: The Basics Jaime Crespo Percona Live Europe 2018 -Frankfurt, 5 Nov 2018- dbahire.com/pleu18 © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 

2 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Agenda 1. Introduction 6. Joins 2. Query Profiling 7. Subqueries 3. Access Types and Basic 8. FULLTEXT, Geodata Search Indexing Techniques and JSON support 4. Multi-Column Indexing 9. Conclusions 5. Break © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 2 

3 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics INTRODUCTION © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 3 

4 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics This is me fighting bad query performance ● Sr. Database Administrator at Wikimedia Foundation ● Used to work as a trainer for Oracle (MySQL), as a Consultant (Percona) and as a Freelance administrator (DBAHire.com) © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 4 

5 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Example Application (I) • Wiktionary (and all Wikimedia projects’ data) is licensed under the Creative Commons BY-SA-3.0 License and is Copyright its Contributors © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 5 

6 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Example Application (II) • OSM Database is licensed under the Open DataBase License and is Copyright OpenStreetMap Contributors © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 6 

7 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Install the example databases ● Downloads and instructions at: http://dbahire.com/pleu18 – Requirements: a MySQL 8.0 or MariaDB 10.3 installation – The Wiktionary and OSM extracts ● Import them by doing: $ tar xf <file> && cd <dir> && myloader -d . © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 7 

8 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Alternative: Query the Live Database ● https://quarry.wmflabs.org/ © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 8 

9 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics QUERY PROFILING © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 9 

10 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Which Queries Should I Optimize First? ● Tools: – Application-level profiling – General monitoring metrics (CPU, IO, etc.) – Aggregated PROCESSLIST – pt-query-digest – PERFORMANCE_SCHEMA ● They are not exclusive to each other © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 10 

11 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Application-Level profiling © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 11 

12 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics General Monitoring Metrics New index added at 15:30h © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 12 

13 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics pt-query-digest • It is a 3rd party tool written in Perl, originally created by Baron Schwartz • It requires activation of the slow log: Be careful with extra – SET GLOBAL slow_query_log = 1; IO and – SET long_query_time = 0; latency! • In Percona Server and MariaDB it can provide extra information: – SHOW GLOBAL VARIABLES like 'log_slow_verbosity'; © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 13 

14 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics pt-query-digest Execution (I) # 1094.7s user time, 9.4s system time, 141.22M rss, 205.21M vsz # Current date: Wed Jul 1 07:32:28 2015 # Hostname: db1018 # Files: STDIN # Overall: 4.66M total, 640 unique, 53.47 QPS, 0.02x concurrency _________ # Time range: 2015-06-30 07:00:10 to 2015-07-01 07:11:37 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 1320s 1us 3s 283us 332us 3ms 152us # Lock time 238s 0 13ms 51us 93us 39us 52us # Rows sent 5.02M 0 4.16k 1.13 1.96 8.69 0.99 # Rows examine 9.50M 0 232.93k 2.14 3.89 261.15 0.99 # Merge passes 0 0 0 0 0 0 0 # Query size 1.06G 17 67.89k 243.89 511.45 368.99 192.76 # Boolean: # Filesort 8% yes, 91% no Actual execution # Full scan 94% yes, 5% no # Priority que 3% yes, 96% no on Wikipedia # Tmp table 29% yes, 70% no production # Tmp table on 1% yes, 98% no servers servers © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 14 

15 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics pt-query-digest Execution (II) # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============== ======= ====== ===== ============ # 1 0xSANITIZED 242.2765 18.4% 691005 0.0004 0.00 SELECT revision page user # 2 0xSANITIZED 204.7052 15.5% 80863 0.0025 0.01 SELECT revision page user # 3 0xSANITIZED 162.8476 12.3% 1025179 0.0002 0.00 SELECT page # 4 0xSANITIZED 68.1164 5.2% 93928 0.0007 0.01 SELECT revision page user # 5 0xSANITIZED 66.8302 5.1% 354562 0.0002 0.00 SELECT page revision # 6 0xSANITIZED 57.0374 4.3% 211631 0.0003 0.00 SELECT page revision # 7 0xSANITIZED 44.0751 3.3% 6925 0.0064 0.07 SELECT page categorylinks category # 8 0xSANITIZED 35.0655 2.7% 9689 0.0036 0.00 SELECT text # 9 0xSANITIZED 29.4363 2.2% 152259 0.0002 0.00 SELECT page # 10 0xSANITIZED 24.1864 1.8% 176927 0.0001 0.00 SELECT msg_resource # 11 0xSANITIZED 23.7016 1.8% 144807 0.0002 0.00 SELECT page_restrictions # 12 0xSANITIZED 16.6547 1.3% 10135 0.0016 0.03 SELECT revision # 13 0xSANITIZED 15.0564 1.1% 263809 0.0001 0.00 SET © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 15 

16 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics pt-query-digest Execution (III) # Query 1: 7.93 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 1553864032 # This item is included in the report because it matches –limit. # Scores: V/M = 0.00 # Time range: 2015-06-30 07:00:10 to 2015-07-01 07:11:37 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 14 691005 # Exec time 18 242s 163us 91ms 350us 348us 563us 301us # Lock time 26 63s 47us 7ms 91us 103us 14us 84us # Rows sent 12 657.18k 0 1 0.97 0.99 0.16 0.99 # Rows examine 6 657.18k 0 1 0.97 0.99 0.16 0.99 # Query size 31 345.42M 501 749 524.16 537.02 9.22 511.45 # String: # Databases itwiki (225976/32%), enwiktiona... (219461/31%)... 15 more # Hosts # Users wikiuser # Query_time distribution # 1us # 10us # 100us ################################################################ # 1ms # # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `enwiktionary` LIKE 'revision'\G # SHOW CREATE TABLE `enwiktionary`.`revision`\G # SHOW TABLE STATUS FROM `enwiktionary` LIKE 'page'\G # SHOW CREATE TABLE `enwiktionary`.`page`\G # SHOW TABLE STATUS FROM `enwiktionary` LIKE 'user'\G # SHOW CREATE TABLE `enwiktionary`.`user`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT /* Revision::fetchFromConds SANITIZED */ * FROM `revision` INNER JOIN `page` ON ((page_id = rev_page)) LEFT JOIN `user` ON ((rev_user != 0) AND (user_id = rev_user)) WHERE page_namespace = '0' AND page_title = 'SANITIZED' AND (rev_id=page_latest) LIMIT 1\G © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 16 

17 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics PERFORMANCE_SCHEMA • Monitoring schema (engine) enabled by default since MySQL 5.6 – performance_schema = 1 (it is not dynamic) • Deprecates the old query profiling • It is way more user-friendly when combined with the SYS schema/ps_helper (a set of views and stored procedures created by Mark Leith) – Included by default since 5.7.7 © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 17 

18 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Installation of the SYS Schema <5.7/MariaDB $ git clone https://github.com/MarkLeith/mysql-sys.git Cloning into 'mysql-sys'... remote: Counting objects: 926, done. remote: Compressing objects: 100% (73/73), done. remote: Total 926 (delta 35), reused 6 (delta 2) Receiving objects: 100% (926/926), 452.19 KiB | 225.00 KiB/s, done. Resolving deltas: 100% (584/584), done. $ cd mysql-sys/ $ mysql < sys_56.sql © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 18 

19 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Example Usage: Discovering Unused Indexes mysql (osm) > SELECT * FROM sys.schema_unused_indexes LIMIT 5; +---------------+-------------------+---------------------------------+ | object_schema | object_name | index_name | +---------------+-------------------+---------------------------------+ | osm | acls | acls_k_idx | | osm | changeset_tags | changeset_tags_id_idx | | osm | current_nodes | current_nodes_timestamp_idx | | osm | current_nodes | current_nodes_tile_idx | | osm | current_relations | current_relations_timestamp_idx | +---------------+-------------------+---------------------------------+ 5 rows in set (0.04 sec) mysql-5.7.8 (osm) > SELECT * FROM current_nodes WHERE tile = 100; ... mysql-5.7.8 (osm) > SELECT * FROM sys.schema_unused_indexes LIMIT 5; +---------------+-------------------+---------------------------------+ | object_schema | object_name | index_name | +---------------+-------------------+---------------------------------+ With enough | osm | acls | acls_k_idx | activity, it can | osm | changeset_tags | changeset_tags_id_idx | | osm | current_nodes | current_nodes_timestamp_idx | help us clean | osm | current_relations | current_relations_timestamp_idx | | osm | current_relations | changeset_id | up our schema +---------------+-------------------+---------------------------------+ 5 rows in set (0.03 sec) © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 19 

20 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Example Usage: Slow Queries (ordered by server time) mysql (osm) > SELECT * FROM sys.statement_analysis LIMIT 10\G *************** 1. row *************** query: SELECT `way_id` AS rows_examined: 20152155 `id` , `v` FROM `way_tags` WHERE `v` LIKE ? rows_examined_avg: 1343477 db: osm rows_affected: 0 full_scan: * rows_affected_avg: 0 exec_count: 15 tmp_tables: 0 err_count: 0 tmp_disk_tables: 0 warn_count: 0 rows_sorted: 0 total_latency: 7.83 s sort_merge_passes: 0 max_latency: 1.33 s digest: avg_latency: 521.84 ms 21f90695b1ebf20a5f4d4c1e5e860f58 lock_latency: 17.94 ms first_seen: 2014-11-01 17:04:51 rows_sent: 6779 last_seen: 2014-11-01 17:05:22 rows_sent_avg: 452 © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 20 

21 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Example Usage: Top Queries Creating Temporary Tables mysql (osm) > SELECT * FROM sys.statements_with_temp_tables WHERE db = 'osm' LIMIT 10\G *************************** 1. row *************************** query: SELECT ? AS TYPE , `node_id` A ... gs` WHERE `k` = ? AND `v` = ? db: osm exec_count: 11 total_latency: 7.57 s memory_tmp_tables: 11 disk_tmp_tables: 0 avg_tmp_tables_per_query: 1 tmp_tables_to_disk_pct: 0 first_seen: 2014-11-01 17:33:55 last_seen: 2014-11-01 17:34:45 digest: 5e6e82799b7c7c0e5c57cfe63eb98d5d © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 21 

22 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Example Usage: Top Queries Creating Temporary Tables (cont.) mysql (osm) > SELECT DIGEST_TEXT FROM performance_schema.events_statements_summary_by_digest WHERE digest = '5e6e82799b7c7c0e5c57cfe63eb98d5d'\G *************************** 1. row *************************** DIGEST_TEXT: SELECT ? AS TYPE , `node_id` AS `id` FROM `node_tags` WHERE `k` = ? AND `v` = ? UNION SELECT ? AS TYPE , `way_id` AS `id` FROM `way_tags` WHERE `k` = ? AND `v` = ? UNION SELECT ? AS TYPE , `relation_id` AS `id` FROM `relation_tags` WHERE `k` = ? AND `v` = ? 1 row in set (0.00 sec) mysql-5.7.8 (osm) > EXPLAIN SELECT 'node' as type, node_id as id FROM node_tags WHERE k='amenity' and v='cafe' UNION SELECT 'way' as type, way_id as id FROM way_tags WHERE k='amenity' and v='cafe' UNION SELECT 'relation' as type, relation_id as id FROM relation_tags WHERE k='amenity' and v='cafe'; +----+--------------+---------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+---------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+ | 1 | PRIMARY | node_tags | NULL | ALL | NULL | NULL | NULL | NULL | 851339 | 0.00 | Using where | | 2 | UNION | way_tags | NULL | ALL | NULL | NULL | NULL | NULL | 1331016 | 0.00 | Using where | | 3 | UNION | relation_tags | NULL | ALL | NULL | NULL | NULL | NULL | 63201 | 0.00 | Using where | | NULL | UNION RESULT | <union1,2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+---------------+------------+------+---------------+------+---------+------+---------+----------+-----------------+ 4 rows in set, 1 warning (0.01 sec) © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 22 

23 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics ACCESS TYPES AND BASIC INDEXING TECHNIQUES © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 23 

24 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics EXPLAIN ● Essential to understand the execution plan of our queries – Works on SELECTs, INSERTs, UPDATEs, REPLACEs, DELETEs and connections – Fully documented on: https://dev.mysql.com/doc/refman/8.0/en/expl ain-output.html © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 24 

25 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics EXPLAIN Example MariaDB [dewiktionary]> EXPLAIN SELECT * FROM page WHERE page_title = 'German'; +------+-------------+-------+------+--------------- +------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+--------------- +------+---------+------+--------+-------------+ | 1 | SIMPLE | page | ALL | NULL | NULL | NULL | NULL | 778885 | Using where | +------+-------------+-------+------+--------------- Difficult to see +------+---------+------+--------+-------------+ something 1 row in set (0.00 sec) © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 25 

26 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics EXPLAIN Example (vertical format) MariaDB [dewiktionary]> EXPLAIN SELECT * FROM page WHERE page_title = 'German'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: page type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 778885 Use \G for Extra: Using where vertical 1 row in set (0.00 sec) formatting © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 26 

27 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics EXPLAIN Example (id) MariaDB [dewiktionary]> EXPLAIN SELECT * FROM page WHERE page_title = 'German'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE Indicates nesting level, table: page not execution order type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 778885 Extra: Using where 1 row in set (0.00 sec) © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 27 

28 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics EXPLAIN Example (select_type) MariaDB [dewiktionary]> EXPLAIN SELECT * FROM page WHERE page_title = 'German'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE Not a subquery or a table: page UNION type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 778885 Extra: Using where 1 row in set (0.00 sec) © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 28 

29 .Query Optimization with MySQL 8.0 and MariaDB 10.3: The Basics EXPLAIN Example (table) MariaDB [dewiktionary]> EXPLAIN SELECT * FROM page WHERE page_title = 'German'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: page Table scanned for type: ALL this step possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 778885 Extra: Using where 1 row in set (0.00 sec) © 2018 Jaime Crespo. http://jynus.com. License: CC-BY-SA-4.0 29