Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MySQL Performance - the Dos and Don'ts COSCUP 2019 2019/08/17 Yoshiaki Yamasaki / 山﨑 由章 MySQL Principal Solution Engineer, Asia Pacific and Japan

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation.

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Agenda Use a new version of MySQL, Use InnoDB Tune parameters (Tune System Variables) Use slow query log & sys schema Conclusion 1 2 3 4 3

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Agenda Use a new version of MySQL, Use InnoDB Tune parameters (Tune System Variables) Use slow query log & sys schema Conclusion 1 2 3 4 4

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Use a new version of MySQL • The new version improves basic performance. 5 0 200,000 400,000 600,000 800,000 1,000,000 1,200,000 1,400,000 1,600,000 1,800,000 8 16 32 64 128 256 512 1,024 Queries per Second Connections Sysbench Benchmark: OLTP Read Only (SQL Point Selects) MySQL 5.7 MySQL 5.6 MySQL 5.5 Intel(R) Xeon(R) CPU E7-8890 v3 4 sockets x 18 cores-HT (144 CPU threads) 2.5 Ghz, 512GB RAM Linux kernel 3.16

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Use a new version of MySQL • The new version improves basic performance. 6 0 200,000 400,000 600,000 800,000 1,000,000 1,200,000 1 2 4 8 16 32 64 128 256 512 Queries per Second Users MySQL 8.0 MySQL 5.7 OS : Oracle Linux 7.4 CPU : 48cores-HT Intel Skylake 2.7Ghz (2CPU sockets, Intel(R) Xeon(R) Platinum 8168 CPU) RAM: 256GB Storage : x2 Intel Optane flash devices (Intel (R) Optane (TM) SSD P4800X Series) Sysbench Benchmark: IO Bound Read Only (Point Selects)

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Use a new version of MySQL • The new version improves basic performance. 7 0 50,000 100,000 150,000 200,000 250,000 300,000 1 2 4 8 16 32 64 128 256 512 1,024 Queries per Second Users MySQL 8.0 MySQL 5.7 OS : Oracle Linux 7.4 CPU : 48cores-HT Intel Skylake 2.7Ghz (2CPU sockets, Intel(R) Xeon(R) Platinum 8168 CPU) RAM: 256GB Storage : x2 Intel Optane flash devices (Intel (R) Optane (TM) SSD P4800X Series) Sysbench Benchmark: Read Write (update nokey)

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Use a new version of MySQL • Complex query performance is also improved due to optimizer improvements. 8 0 20 40 60 80 100 Q3 Q7 Q8 Q9 Q12 Execution time relative to 5.6 (%) 5 of 22 queries significantly improve performance due to optimizer improvements MySQL 5.6 MySQL 5.7 DBT-3 (Size Factor 10, CPU bound) ※Q8: About 25 seconds => About 3 seconds

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Use a new version of MySQL • The new version has features that help improve performance and help with tuning. • Example: – Visual EXPLAIN * Introduced in MySQL 5.6 – MTS(Multi Thread Slave) * Introduced in MySQL 5.6 (Improved in 5.7, 8.0) – Optimizer Hints * Introduced in MySQL 5.7 – Window Function & CTEs * Introduced in MySQL 8.0 – Invisible Indexes * Introduced in MySQL 8.0 – Descending Indexes * Introduced in MySQL 8.0 – Resource Groups * Introduced in MySQL 8.0 And more… 9

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Use a new version of MySQL • The new version has features that help improve performance and help with tuning. • Example: – Visual EXPLAIN * Introduced in MySQL 5.6 – MTS(Multi Thread Slave) * Introduced in MySQL 5.6 (Improved in 5.7, 8.0) – Optimizer Hints * Introduced in MySQL 5.7 – Window Function & CTEs * Introduced in MySQL 8.0 – Invisible Indexes * Introduced in MySQL 8.0 – Descending Indexes * Introduced in MySQL 8.0 – Resource Groups * Introduced in MySQL 8.0 And more… 10

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Visual EXPLAIN • Get Visual EXPLAIN via MySQL Workbench • The type of access to the object can be checked at a glance (type value can be identified by color) • We can know the JOIN order at a glance. • We can check the cost estimated by Optimizer. (MySQL 5.7 and later) ※Document: 7.5 Tutorial: Using Visual Explain to improve query performance http://dev.mysql.com/doc/workbench/en/wb-tutorial-visual-explain-dbt3.html 11

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Visual EXPLAIN(Difference in color depending on the type value) Value of “type” Color Meaning system Blue Sngle row table(System table) ※Special case of “const” const Blue Primary/Unique Key Lookup eq_ref Green JOIN with Primary/Unique Key Lookup ref Green Non-Unique Key Lookup fulltext Yellow Fulltext Index Search ref_or_null Green Non-Unique Key Lookup + Fetch NULL Values(IS NULL) index_merge Green Index Merge unique_subquery Orange Unique Key Lookup into table of subquery index_subquery Orange Non-Unique Key Lookup into table of subquery range Orange Index Range Scan index Red Full Index Scan ALL Red Full Table Scan Good Not Good 12

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MySQL Workbench • Official GUI tool of MySQL • Tools that combine functions such as SQL development, Server management, Data modeling and Data migration • Some functions are useful for tuning – VISUAL EXPLAIN – Check object definitions – Capture running SQL, etc Unified visual tool for database architects, developers, and DBAs 13

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MySQL Workbench • There are Community Edition and Commercial Edition, but most features are also available in the Community Edition http://www.mysql.com/products/workbench/features.html • Download MySQL Workbench https://dev.mysql.com/downloads/workbench/ • Document https://dev.mysql.com/doc/workbench/en/ • Blog http://mysqlworkbench.org Unified visual tool for database architects, developers, and DBAs 14

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Note: Query Statistics • From the MySQL Workbench Query Statistics, we can check the basic information that should be checked during SQL tuning. 15

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MTS(Multi Thread Slave) • When using replication, by default the slave runs in a single thread, which may cause delays – The master serializes the update processing contents and records it in the binary log – The slave receives the update processing contents in the relay log – The slave reads the contents of the relay log and reflects the update process 16 binary log Multi Thread Master Server Client Client Client Slave Server relay log Single Thread Serialize when writing

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MTS(Multi Thread Slave) • Such delays can be reduced by using multi-threaded slaves – In MySQL 5.6, multi-threaded processing is possible if the schema is separate – In MySQL 5.7, multi-threaded processing is possible with the same schema – In MySQL 8.0, multi-threaded processing on the slave even when processing with a single thread on the master 17

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MTS(Multi Thread Slave) • System variable to enable multi thread slave – slave_parallel_workers=N * N specifies the number of threads – slave_parallel_type=LOGICAL_CLOCK – slave_preserve_commit_order=ON • Note – When multi thread slave is enabled, it is necessary to set ”slave_preserve_commit_order=ON” to guarantee that the update processing order on the slave is same to that of the master. – When slave_preserve_commit_order id disabled, consistency is eventual consistency. 18

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Setting 1: Configuration to make efficient MTS • As multi thread slaves are applied to processing that has been group committed at the master, efficient group commit leads to efficient MTS • System variables for tuning group commit – binlog_group_commit_sync_delay • Setting to wait for a while without syncing immediately after COMMIT execution in order to make efficient group commit • Set waiting time in microseconds • Note: If set, COMMIT performance of single unit falls – binlog_group_commit_sync_no_delay_count • Options that can be set to not wait too much with the above parameters • If the number of transactions for COMMIT waiting specified in this parameter is accumulated, COMMIT immediately without waiting for binlog_group_commit_sync_delay 19

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Setting 2: Configuration to make efficient MTS • Parallelization by WRITESET method is also possible with MySQL 5.7.22 or later and MySQL 8.0 – Apply conflict detection mechanism used in group replication – Even for transactions for which group commit has not been performed on the master, parallelization is possible on the slave side if the transactions do not update the same row => Processing executed by a single thread on the master side can also be parallelized on the slave side – Note: There is additional overhead for writing binary logs on the master side 20

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Setting 2: Configuration to make efficient MTS • System variable to use WRITESET parallelization – transaction_write_set_extraction • Set to “XXHASH64” * Default setting is XXHASH64 for MySQL 8.0.2 or later – binlog_transaction_dependency_tracking • Parallelization is possible by setting "WRITESET" or "WRITESET_SESSION" • In the case of "WRITESET_SESSION", the transaction execution order on a session basis is secured and then parallelized • If "slave_preserve_commit_order = ON" is set, basically it is better to select "WRITESET“ 21

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Setting 2: Configuration to make efficient MTS • System variables for WRITESET parallelization – binlog_transaction_dependency_history_size • Sets the number of row hashes to hold to determine that the same row has not been updated • The default value is 25000(rows) • If the upper limit is reached, all row hashes are purged, so tuning is recommended in environments where there are many rows to update 22

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Effect of parallelization by WRITESET method 23 • Realizes high degree of parallelism on slave side even in environments with low degree of parallelism on master side 0 5000 10000 15000 20000 25000 30000 35000 40000 45000 50000 1 2 4 8 16 32 64 128 256 Updates/second Applies on the Replica Number of Clients on the Master Applier Throughput: Sysbench Update Index COMMIT_ORDER WRITESET WRITESET_SESSION

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Remarks: History of the evolution of MTS 24 The story of "Setting 1: Configuration to make efficient MTS" The story of "Setting 2: Configuration to make efficient MTS"

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Use InnoDB • The MySQL development team is currently focusing on developing InnoDB. • Use MyISAM is not good for performance. – MyISAM is designed with a single core and does not work properly with multicore CPU. – MyISAM is not actively developed. 25 * In MySQL 8.0, all system tables use InnoDB. It improves reliability and performance.

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Agenda Use a new version of MySQL, Use InnoDB Tune parameters (Tune System Variables) Use slow query log & sys schema Conclusion 1 2 3 4 26

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Tune parameters (Tune System Variables) • Some parameters should be tuned for better performance • Introducing 5 typical parameters that should be tuned 1. innodb_buffer_pool_size 2. innodb_log_file_size 3. tmp_table_size & max_heap_table_size 4. sort_buffer_size 27

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. innodb_buffer_pool_size • An area in memory that caches data and index • It is better to expand the cache to reduce disk access • The default value is 128MB 28

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. innodb_log_file_size • The size in bytes of each log file in a log group • It is better to expand the size in an environment with many update processes • Small size may cause a checkpoint wait • The default value is 48MB 29

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. tmp_table_size & max_heap_table_size • When using a temporary table and the size is small, the temporary table is stored in memory. If the size is large, a temporary table is created on the disk. • Create temporary table on disk when tmp_table_size or max_heap_table_size limit is reached 30 * You can check Created_tmp_disk_tables status variable to confirm the number of times a temporary table is created on disk

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. sort_buffer_size • When performing sort processing and the data cannot be sorted in the memory area, file sort occur. • Sort_buffer_size specifies the size of the sort area in memory • The default value is 256KB – Since MySQL 5.6, the default value was changed from 2MB to 256KB 31 * You can check Sort_merge_passes status variable to confirm the number of times a file sort occurred.

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Agenda Use a new version of MySQL, Use InnoDB Tune parameters (Tune System Variables) Use slow query log & sys schema Conclusion 1 2 3 4 32

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Use Slow query log & sys schema • Slow query log – We can find slow query using slow query log. • sys schema – sys schema is a set of objects that helps DBAs and developers understand data collected by the Performance Schema. 33 * Performance Schema is a feature for monitoring MySQL Server execution.

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Slow query log • Output queries whose execution time is longer than the specified time. • It is not output by default. (set system variable slow_query_log) • System variables – long_query_time: Specified in seconds (If 0.5 is specified, it means 500ms. The default value is 10) – log_queries_not_using_indexes: If this system variables enabled, output all queries that don’t use an index. – log_output: You can choose log output destination file or table. (Default is file) #Time: 08073101 16:25:24 #User@Host: root[root] @ localhost [127.0.0.1] #Query_time: 8 Lock_time: 0 Rows_sent: 20 Rows_examined: 243661 SELECT part_num FROM `inventory`.`parts` WHERE (`ven` = "foo") ORDER BY `delivery_datetime` DESC LIMIT 100; 34

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Slow query log: Other related system variables • slow_query_log_file * Default value is “host_name-slow.log” – Specify slow query log file name • min_examined_row_limit * Default value is “0” – Parameter to prevent queries that access a small number of rows – Queries that access fewer rows than the specified number will not be output to the slow query log • log_throttle_queries_not_using_indexes * Default value is “0” – Parameter that can prevent a large amount of queries not using an index from being output to the slow query log – Specify the number of queries that do not use an index that can be output to the slow query log per minute • log_slow_admin_statements * Default value is “OFF” – Management operations(*) are not normally output to the slow query log, but you can output such operations enabling this parameter “ON”. * ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, REPAIR TABLE 35

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. mysqldumpslow • Aggregation tool for slow query log • Useful for identifying queries that should be tuned with priority Example of use – mysqldumpslow -s at * "-s at“ is an option to sort by query time or average query time (default). 36

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. sys schema • A set of views, procedures, and functions for convenient use of the performance schema • sys schema view makes it easy to see performance information • Created by default from MySQL 5.7 Performance Schema and information schema in a simple view 37 * Performance Schema is a feature for monitoring MySQL Server execution.

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. sys schema example: Object Overview [sys]> select * from sys.schema_object_overview where db = 'sys'; +-----+---------------+-------+ | db | object_type | count | +-----+---------------+-------+ | sys | BASE TABLE | 1 | | sys | INDEX (BTREE) | 1 | | sys | TRIGGER | 2 | | sys | FUNCTION | 21 | | sys | PROCEDURE | 26 | | sys | VIEW | 100 | +-----+---------------+-------+ 38

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. sys schema example: InnoDB Buffer Memory Usage [sys]> select object_schema,object_name,allocated,data,pages,rows_cached from sys.innodb_buffer_stats_by_table order by data limit 0,5; +---------------+--------------------+------------+------------+-------+-------------+ | object_schema | object_name | allocated | data | pages | rows_cached | +---------------+--------------------+------------+------------+-------+-------------+ | sakila | staff | 16.00 KiB | 980 bytes | 1 | 2 | | sakila | address | 32.00 KiB | 9.11 KiB | 2 | 95 | | mysql | gtid_executed | 16.00 KiB | 70 bytes | 1 | 1 | | sakila | actor | 16.00 KiB | 7.33 KiB | 1 | 200 | | mysql | innodb_table_stats | 1.23 MiB | 610.75 KiB | 79 | 8406 | +---------------+--------------------+------------+------------+-------+-------------+ 39

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. sys schema example: user_summary_by_file_io_type select * from user_summary_by_file_io_type order by latency desc limit 5; +------------+---------------------------------+-------+----------+-------------+ | user | event_name | total | latency | max_latency | +------------+---------------------------------+-------+----------+-------------+ | root | wait/io/file/csv/metadata | 64 | 94.09 ms | 74.15 ms | | background | wait/io/file/sql/binlog | 25 | 9.20 ms | 7.59 ms | | repl_user | wait/io/file/sql/binlog | 12 | 70.73 us | 27.35 us | | background | wait/io/file/sql/global_ddl_log | 2 | 7.92 us | 6.45 us | | root | wait/io/file/myisam/dfile | 423 | 68.84 ms | 47.79 ms | +------------+---------------------------------+-------+----------+-------------+ 40

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. sys schema example: statement_analysis select LEFT(query,20),db,exec_count,total_latency,max_latency,avg_latency from statement_analysis limit 5; +----------------------+-------+------------+---------------+-------------+-------------+ | LEFT(query,20) | db | exec_count | total_latency | max_latency | avg_latency | +----------------------+-------+------------+---------------+-------------+-------------+ | SELECT DISTINCTROW ` | NULL | 1 | 9.77 s | 9.77 s | 9.77 s | | SELECT `information_ | sys | 3 | 6.18 s | 5.54 s | 2.06 s | | SELECT ( `UNIX_TIMES | mysql | 4461 | 5.13 s | 15.60 ms | 1.15 ms | | SELECT DISTINCTROW ` | NULL | 1 | 3.70 s | 3.70 s | 3.70 s | | SET SESSION `sql_mod | mysql | 4461 | 696.29 ms | 6.69 ms | 156.08 us | +----------------------+-------+------------+---------------+-------------+-------------+ * By default, it is sorted with the longest total execution time. 41

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. sys schema example: innodb_lock_waits mysql> select * from sys.innodb_lock_waits¥G *************************** 1. row *************************** wait_started: 2015-11-27 00:21:47 wait_age: 00:00:11 wait_age_secs: 11 locked_table: `world`.`City` locked_index: PRIMARY locked_type: RECORD waiting_trx_id: 5956 waiting_trx_started: 2015-11-27 00:21:39 waiting_trx_age: 00:00:19 waiting_trx_rows_locked: 2 waiting_trx_rows_modified: 1 waiting_pid: 15 waiting_query: update City set Population=1780000 where ID=1 waiting_lock_id: 5956:115:5:2 waiting_lock_mode: X blocking_trx_id: 5955 blocking_pid: 14 blocking_query: NULL blocking_lock_id: 5955:115:5:2 blocking_lock_mode: X blocking_trx_started: 2015-11-27 00:21:34 blocking_trx_age: 00:00:24 blocking_trx_rows_locked: 2 blocking_trx_rows_modified: 2 sql_kill_blocking_query: KILL QUERY 14 sql_kill_blocking_connection: KILL 14 1 row in set (0.00 sec) SQL waiting for lock 42

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Agenda Use a new version of MySQL, Use InnoDB Tune parameters (Tune System Variables) Use slow query log & sys Conclusion 1 2 3 4 43

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Conclusion • To use MySQL with good performance, – Use a new version of MySQL, Use InnoDB – Tune parameters (Tune System Variables) – Use Slow query log & sys schema 44

Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

No content