MySQL Performance - the Dos and Don'ts

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

View Slide

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.

View Slide

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

View Slide

Agenda
Conclusion
1
2
3
4
4

View Slide

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

View Slide

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)

View Slide

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)

View Slide

• 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

View Slide

• 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

View Slide

• 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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

Note: Query Statistics
• From the MySQL Workbench Query Statistics, we can check the basic
information that should be checked during SQL tuning.
15

View Slide

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

View Slide

• 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

View Slide

• 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

View Slide

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

View Slide

• 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

View Slide

• 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

View Slide

• 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

View Slide

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

View Slide

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"

View Slide

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.

View Slide

Agenda
Conclusion
1
2
3
4
26

View Slide

• 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

View Slide

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

View Slide

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

View Slide

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

View Slide

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.

View Slide

Agenda
Conclusion
1
2
3
4
32

View Slide

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.

View Slide

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

View Slide

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

View Slide

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

View Slide

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.

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

Agenda
Use slow query log & sys
Conclusion
1
2
3
4
43

View Slide

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

View Slide


View Slide

MySQL Performance - the Dos and Don'ts

MySQL Performance - the Dos and Don'ts

YoshiakiYamasaki

More Decks by YoshiakiYamasaki

Other Decks in Technology

Featured

Transcript