Improve the performance of your MySQL database - Essential tips and best practices

Improve the performance of your MySQL database
Essential tips and best practices
Olivier Dasini
MySQL Cloud Principal Solutions Architect EMEA
[email protected]
Blogs : www.dasini.net/blog/en
: www.dasini.net/blog/fr
Linkedin: www.linkedin.com/in/olivier-dasini
Twitter : @freshdaz
May 25, 2023

View Slide

2 Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
Me, Myself & I

MySQL Geek
 Addicted to MySQL for 15+ years
 Playing with databases for 20+ years

MySQL Writer, Blogger and Speaker
 Also: DBA, Consultant, Architect, Trainer, ...

MySQL Cloud Principal Solutions Architect EMEA at Oracle

Stay up to date!
 Blog: www.dasini.net/blog/en
 Linkedin: www.linkedin.com/in/olivier-dasini/
 Twitter: @freshdaz
Olivier DASINI

View Slide

Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
Agenda
1. Source of database performance problems
2. Performance Configuration Options
3. Schema Optimization
4. Query Performance
5. MySQL HeatWave
6. MySQL Shell
3

View Slide

Source of database performance problems
4
Hardware
Schema Changes
Data Growth
Indexes
SQL
90% of
Performance Problems
Where we can improve resource efficiency
& reducing waste

View Slide

Performance Configuration Options
5
InnoDB Buffer Pool

View Slide

6
InnoDB Architecture
InnoDB is a general-purpose storage
engine that balances high reliability
and high performance.
Its DML operations follow the ACID
model, with transactions featuring
commit, rollback, and crash-recovery
capabilities to protect user data.

View Slide

InnoDB Buffer Pool - innodb_buffer_pool_size
• Size of the buffer pool, memory area where InnoDB caches table and index data
• A larger buffer pool requires less disk I/O to access the same table data more than once
• Optimal size:
1. innodb_buffer_pool_size for InnoDB workloads
2. How much memory does the host have?
3. Subtract memory required by OS and other processes
4. Subtract memory required by MySQL other then the InnoDB buffer pool
5. Choose minimum of this and the size of the “working data set”
• Since MySQL 5.7, innodb_buffer_pool_size can be changed dynamically
7

View Slide

InnoDB Buffer Pool - Checking the size of your working set
SELECT CONCAT(FORMAT(A.num * 100.0 / B.num,2),"%") BufferPoolFullPct
FROM
(
SELECT variable_value num
FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data'
) A
INNER JOIN
(
SELECT variable_value num
FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total'
) B;
8
Verify how much the InnoDB Buffer Pool is filled with data
https://lefred.be/content/mysql-and-memory-a-love-story-part-2/

View Slide

Demo 1
InnoDB Buffer Pool

View Slide

10
Schema Optimization

View Slide

11
• Design your tables to minimize their space on the disk <=> use more caching, thus minimize
disk seeks
– Reduce the amount of data written to and read from disk
– Smaller tables normally require less main memory
– Smaller indexes that can be processed faster
●
Use the most efficient (smallest) data types possible
– e.g. MEDIUMINT vs INT (MEDIUMINT uses 25% less space than INT) or BIGINT, TINYINT (UNSIGNED ?)
●
Declare columns to be NOT NULL if possible
– It makes SQL operations faster, by enabling better use of indexes & eliminating overhead for testing
whether each value is NULL
– You also save some storage space
Schema Optimization Tips 1/2
Look for the most efficient way to organize your data

View Slide

12
• The primary key of a table should be as short as possible
– This makes identification of each row easy and efficient
– For InnoDB tables, the PK columns are duplicated in each secondary index entry, so a short PK saves
considerable space if you have many secondary indexes
●
Create only the indexes that you need to improve query performance
– Indexes are good for retrieval, but slow down insert and update operations
– Indexes use disk & memory spaces
– Sometime it can be beneficial to use column prefix key parts (especially for long string column)
●
Declare columns with identical information in different tables with identical data types,
charsets & collations
– Speed up joins based on the corresponding columns
Schema Optimization Tips 2/2
Look for the most efficient way to organize your data

View Slide

Demo 2
Schema Optimization

View Slide

14
Query Performance
Make the best use of your data

View Slide

15
• The set of operations that the optimizer chooses to perform the most efficient query is called
the Query Execution Plan or EXPLAIN plan
• MySQL explains how it would process the statement, including information about how tables
are joined and in which order
• Returns a row of information for each table used in the statement
• Lists the tables in the output in the order that MySQL would read them while processing the
statement
– You can see where you should add indexes to tables so that the statement executes faster by using
indexes to find rows
– You can check whether the optimizer joins the tables in an optimal order
• EXPLAIN works with SELECT, TABLE, DELETE, INSERT, REPLACE, and UPDATE statements
Analyzing Queries – EXPLAIN
Provide information about how MySQL executes statements
https://dev.mysql.com/doc/refman/8.0/en/explain.html

View Slide

16
• The best way to improve the performance of SELECT operations is to create relevant
indexes on one or more of the columns that are tested in the query
• The index entries act like pointers to the table rows, allowing the query to quickly determine
which rows match a condition in the WHERE clause, and retrieve the other column values for
those rows
• All MySQL data types can be indexed
• Although it can be tempting to create an indexes for every possible column used in a query,
unnecessary indexes waste space and waste time for MySQL to determine which
indexes to use
• Indexes also add to the cost of INSERT, UPDATE & DELETE because each index must be
updated
• You must find the right balance to achieve fast queries using the optimal set of indexes
Indexes

View Slide

17
• Indexed column is used as argument to function
– SELECT last_name FROM employees WHERE YEAR(birth_date) = 1952
• Alternative:
– Use functional indexes:
– ALTER TABLE employees ADD INDEX idxf_year_birth_date((YEAR(birth_date)));
– https://dasini.net/blog/2019/03/14/mysql-functional-indexes
– https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts
Some Reasons Why Index can NOT be Used 1/4

View Slide

18
• Looking for a suffix :
– SELECT * FROM employees WHERE email LIKE '%mysql.com'
• Alternative:
– Use functional indexes:
– ALTER TABLE employees ADD INDEX idxf_rev_email((REVERSE(email)));
– SELECT * FROM employees WHERE REVERSE(email) BETWEEN 'moc.lqsym' AND 'moc.lqsym%';
– https://dasini.net/blog/2019/03/14/mysql-functional-indexes
– https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts

View Slide

19
• First column(s) of compound index NOT used
– Index defined over (first_name, last_name)
– SELECT * FROM employees WHERE last_name = 'Badr';
• Solution
– Add index on last_name
– CREATE INDEX idx_last_name ON employees(last_name);

View Slide

20
• Type mismatch
– my_int = "10"
– SELECT last_name FROM employees WHERE YEAR(birth_date) = "1952" ;
• Solution
– Pay attention to the type
– SELECT last_name FROM employees WHERE YEAR(birth_date) = 1952 ;
• Alternative
– SELECT last_name FROM employees WHERE YEAR(birth_date) = CAST("1952" AS UNSIGNED) ;

View Slide

Demo 3
Some Reasons Why Index can NOT be Used

View Slide

Demo 4
Index impact on write intensive workload

View Slide

Demo 5
Sort data by PK order before import

View Slide

Demo 6
Save network traffic

View Slide

25
• InnoDB primary key is included in all secondary indexes in order to be able to locate the actual row
– Smaller primary key gives smaller secondary indexes
– A mostly sequential primary key is in general recommended to avoid inserting rows between existing rows
– Alternative: create an AUTO_INCREMENT column as the primary key if your primary key is long, or index a
prefix of a long VARCHAR column instead of the entire column
• InnoDB automatically extends each secondary index by appending the primary key columns to it
– The optimizer takes into account the primary key columns of the extended secondary index when
determining how and whether to use that index
– This can result in more efficient query execution plans and better performance
– Could be disable with : SET optimizer_switch = 'use_index_extensions=off';
• When you use a randomly generated value as a primary key in an InnoDB table, prefix it with an
ascending value such as the current date and time if possible
– When consecutive primary values are physically stored near each other, InnoDB can insert and retrieve
them faster
• It is usually better to create a small number of concatenated indexes rather than a large number of
single-column indexes
– If an index contains all the columns needed for the result set (ie covering index), the query might be able
to avoid reading the table data at all
InnoDB Tips
https://dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html

View Slide

26
MySQL HeatWave

View Slide

MySQL Heatwave for read OLAP workloads
27
• Single MySQL database for both OLTP, OLAP/Analytics and ML applications
(No need to ETL)
• All existing applications will work without any changes – incl. Tableau, Qlik, Looker
• Enables running analytics and ML also for data stored on-premises using MySQL Replication
• No need to learn new language or ML packages
• Extreme performance: Accelerates MySQL by orders of magnitude
Available on major cloud platforms: OCI, Azure, AWS

View Slide

Demo 7
MySQL HeatWave

View Slide

29
MySQL Shell

View Slide

MySQL Shell: Dump & Load Utilities
• MySQL Shell 8.0.21+ Logical Dump and Load Utilities
• util.dumpInstance() , util.dumpSchemas() , util.dumpTables() , util.loadDump()
• Utilities to create a logical dump and do a logical restore
• With a focus on ease of use, performance & integration
• Allow to restore logical dumps of MySQL databases
o in parallel
o with zstd or gzip
o compression
o optionally store them in an OCI Object Storage bucket
• It also has features designed to help smoothly copy data from MySQL 5.7 / 5.6 and MySQL 8.0 servers to
the new MySQL HeatWave Database Cloud Service
• Details:
o https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
o https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html
30

View Slide

MySQL Shell: Dump & Load Utilities Benchmarks
31
https://dev.mysql.com/blog-archive/mysql-shell-dump-load-part-2-benchmarks/

View Slide

InnoDB Configuration – Disabling Redo Logging
32
• As of MySQL 8.0.21, you can disable redo logging using
– ALTER INSTANCE DISABLE INNODB REDO_LOG
• This functionality is intended for loading data into a new MySQL instance
• Disabling redo logging speeds up data loading by avoiding redo log writes and doublewrite
buffering
• This feature is intended only for loading data into a new MySQL instance
• Do not disable redo logging on a production system
• Details:
– https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html#innodb-disable-redo-logging
https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html#innodb-disable-redo-logging

View Slide

Demo 8
MySQL Shell - Dump & Load Utilities

View Slide

Follow us on Social Media

View Slide

Merci!
Q&R
Olivier Dasini
MySQL Cloud Principal Solutions Architect EMEA
[email protected]
Blogs : www.dasini.net/blog/en
: www.dasini.net/blog/fr
Linkedin: www.linkedin.com/in/olivier-dasini
Twitter : @freshdaz

View Slide

Resources
● How to optimize MySQL performance?
✔
https://dev.mysql.com/doc/refman/8.0/en/optimization.html
● The InnoDB Storage Engine
✔
https://dev.mysql.com/doc/refman/8.0/en/innodb-storage-engine.html
● MySQL Shell
✔
https://dev.mysql.com/doc/mysql-shell/8.0/en/
● MySQL HeatWave User Guide
✔
https://dev.mysql.com/doc/heatwave/en/
● lefred's blog: tribulations of a MySQL Evangelist
✔
https://lefred.be/
● DimitriK's (dim) Weblog
✔
http://dimitrik.free.fr/blog/
● dasini.net – Diary of a MySQL expert
✔
https://dasini.net/blog/en/
36

View Slide


View Slide

38
If you’re still reading… :)
Some additional information

View Slide

39
Dimitri Kravtchuk
MySQL Performance Architect
https://dimitrik.free.fr/blog/
The MySQL Performance Best Practice #1
is… ???
USE YOUR BRAIN !!! ;-)

View Slide

Performance Tuning - Guidelines 1/2
 Think – consider what you are doing!
 Monitor your system
– There is no control without measurement! - MySQL Enterprise Monitor is your best ally
– https://www.mysql.com/products/enterprise/monitor.html
 Be wary about “best practices”
– No two systems are the same
– What was true earlier may no longer hold
 Ensure you test your changes before deploying to production
– The testing must reflect your production usage
 Make incremental changes
– One change at a time
– Relatively small change
40

View Slide

Performance Tuning - Guidelines 2/2
 Be mindful of your requirements
– Some options give the choice between performance or data safety – what do you need?
 Often the default value is the best value - we’ve made huge improvement in MySQL 8.0
 Ensure all tables have a PRIMARY KEY - very important for InnoDB
 InnoDB organizes the data according to the PRIMARY KEY:
– The PRIMARY KEY is included in all secondary indexes in order to be able to locate the actual row
– Smaller PRIMARY KEY gives smaller secondary indexes
– A mostly sequential PRIMARY KEY is in general recommended to avoid inserting rows between existing rows
41

View Slide

SET PERSIST: Persisted System Variables
• Persistent runtime configuration changes written in mysqld-auto.cnf file (JSON format)
o SET PERSIST variable_name = value; (RESET PERSIST removes persisted settings from mysqld-auto.cnf)
o Any GLOBAL DYNAMIC configuration variable can be modified
o New tables:
 performance_schema.persisted_variables: SQL interface to the mysqld-auto.cnf file that stores persisted global system variable settings
 performance_schema.variables_info: shows, for each system variable, the source from which it was most recently set, and its range of
values
42
https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html
MySQL> SET PERSIST innodb_buffer_pool_size=268435456;
MySQL> SET PERSIST innodb_redo_log_capacity=209715200;
MySQL> SELECT * FROM performance_schema.persisted_variables;
+--------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+--------------------------+----------------+
| innodb_redo_log_capacity | 209715200 |
| innodb_buffer_pool_size | 268435456 |
+--------------------------+----------------+
Easier changes without server restart

View Slide

Saving and Restoring the Buffer Pool State
• To reduce the warmup period after restarting the server, InnoDB saves a percentage of the
most recently used pages for each buffer pool at server shutdown and restores these pages
at server startup
– innodb_buffer_pool_dump_at_shutdown - default: ON
– innodb_buffer_pool_dump_pct - default: 25 (percentage of the most recently used pages)
– Page loading happens in the background, and does not delay database startup
• You can also save and restore the buffer pool state at any time, while the server is running
– SET GLOBAL innodb_buffer_pool_dump_now=ON; -- Save the state of the buffer pool
– SET GLOBAL innodb_buffer_pool_load_now=ON; -- Restore the buffer pool state
– SET GLOBAL innodb_buffer_pool_load_abort=ON; -- Abort a buffer pool load operation
– SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'; -- Display progress when saving the
buffer pool state
– SHOW STATUS LIKE 'Innodb_buffer_pool_load_status'; -- Display progress when loading the
buffer pool
43
https://dev.mysql.com/doc/refman/8.0/en/innodb-preload-buffer-pool.html

View Slide

Automatically configure InnoDB - innodb_dedicated_server
●
When innodb_dedicated_server is enabled, InnoDB automatically configures the following
variables:
– innodb_buffer_pool_size
– innodb_redo_log_capacity
– innodb_flush_method
●
Explicit settings can always be set
– Explicit configuration of one option does not prevent the automatic configuration of other options
●
Only consider enabling innodb_dedicated_server if the MySQL instance resides on a
dedicated server where it can use all available system resources
– e.g. Running MySQL Server in a Docker container or dedicated VM that only runs MySQL
●
Enabling innodb_dedicated_server is not recommended if the MySQL instance shares
system resources with other applications
●
Automatically configured settings are evaluated and reconfigured if necessary each time
the MySQL server is started
44
Enabling Automatic Configuration for a Dedicated MySQL Server
https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html

View Slide

More about indexing & MySQL 8
• Indexes can now be built simultaneously instead of serially
o innodb_ddl_threads/innodb_ddl_buffer_size/innodb_parallel_read_threads
o possibility to build multiple indexes for a table in the same alter statement in parallel
• Descending Indexes
o Support for ASC, DESC in index definitions
mysql> CREATE TABLE t1 (a INT,b INT,INDEX a_desc_b_asc (a DESC, b ASC));
o Multiple-column indexes with ascending order for some columns and descending order for others
• New Index-Level Optimizer hints
o FORCE INDEX and IGNORE INDEX (FOR GROUP/ORDER BY)
have been deprecated
• Replaced by equivalent INDEX/NO_INDEX,
GROUP_INDEX, NO_GROUP_INDEX, ORDER_INDEX, etc.
o More possibilities of combining index-level optimizer hints
for more fine-grained control
45

View Slide

EXPLAIN output changes in MySQL 8.0
• EXPLAIN FORMAT=JSON and TREE provide add details on
Used indexes, Pushed index conditions, Cost estimates,
Data estimates
47
mysql> EXPLAIN FORMAT=JSON SELECT city.name,
city.population, country.name AS country
FROM city, country
WHERE city.CountryCode=country.Code
AND city.name='Roma';
+-------------------------------------+
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "550.97"
},
"nested_loop": [
{
"table": {
"table_name": "city",
"access_type": "ALL",
"possible_keys": [
"CountryCode"
],
"rows_examined_per_scan": 4035,
"rows_produced_per_join": 403,
"filtered": "10.00",
"cost_info": {
"read_cost": "369.40",
"eval_cost": "40.35",
"prefix_cost": "409.75",
"data_read_per_join": "97K"
},
...
mysql> EXPLAIN FORMAT=TREE SELECT city.name, city.population, country.name AS country
FROM city, country
WHERE city.CountryCode=country.Code AND city.name='Roma';
+---------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=550.98 rows=404)
-> Filter: (city.`Name` = 'Roma') (cost=409.75 rows=404)
-> Table scan on city (cost=409.75 rows=4035)
-> Single-row index lookup on country using PRIMARY (Code=city.CountryCode) (cost=0.25 rows=1)
|
+---------------------------------------------------------------------------------------------------+
mysql> EXPLAIN SELECT city.name, city.population, country.name AS country FROM city, country WHERE city.CountryCode=country.Code AND city.name='Roma';
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | ALL | CountryCode | NULL | NULL | NULL | 4035 | 10 | Using where |
| 1 | SIMPLE | country | NULL | eq_ref | PRIMARY | PRIMARY | 12 | world.city.CountryCode | 1 | 100 | NULL |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+

View Slide

EXPLAIN FORMAT=TREE/ANALYZE
• EXPLAIN ANALYZE is built on FORMAT=TREE and adds
o Time (in ms) to first row
o Time (in ms) to last row
o Number of rows
o Number of loops
mysql> EXPLAIN ANALYZE SELECT city.name, city.population, country.name AS country
FROM city, country WHERE city.CountryCode=country.Code AND city.name='Roma';
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=550.98 rows=404) (actual time=0.833..2.131 rows=1 loops=1)
-> Filter: (city.`Name` = 'Roma') (cost=409.75 rows=404) (actual time=0.818..2.116 rows=1 loops=1)
-> Table scan on city (cost=409.75 rows=4035) (actual time=0.037..1.333 rows=4079 loops=1)
-> Single-row index lookup on country using PRIMARY (Code=city.CountryCode) (cost=0.25 rows=1)
(actual time=0.014..0.014 rows=1 loops=1)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------+
mysql> SELECT city.name, city.population, country.name AS country
FROM city,country WHERE city.CountryCode=country.Code AND city.name='Roma';
+------+------------+---------+
| name | population | country |
+------+------------+---------+
| Roma | 2643581 | Italy |
+------+------------+---------+
1 row in set (0.0022 sec)
48
TimingIterator
TimingIterator
TimingIterator
HashJoinIterator
TableScanIterator TableScanIterator
t1 t2
EXPLAIN ANALYZE
SELECT * FROM t1 JOIN t2 ON t1.a = t2.a;

View Slide

JSON improvements in MySQL 8.0
• New JSON functions added
o JSON_ARRAYAGG(), JSON_OBJECTAGG(), etc..
• Ranges in JSON Path expressions:
SELECT doc->>"$[last]" AS last from t1;
SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
• Optimizer Support for in-place update
• Multi-value indexes
• Performance Improvements
• Partial Update of JSON values:
o When apps frequently update small portions of large JSON documents
JSON_SET(), JSON_REPLACE(), JSON_REMOVE()
o Only write the change back to the database
o Only replicate the change
• Efficient Replication of JSON Documents:
Replicate only changed fields of documents
49
Entire JSON Partial JSON
0
10000
20000
30000
40000
50000
60000
Binary Log Space Per Transaction
FULL MINIMAL
Bytes per transaction
• In this benchmark:
– Tables have 10 JSON fields
– Each transaction modifies around 10% of the
data

View Slide

Better Handling of Hot Rows
SELECT ... FOR UPDATE SKIP LOCKED
Common problem:
• Hot row contention, multiple worker
threads accessing the same rows
Solution 1:
• Only read rows that are not locked
• InnoDB skips a locked row, and the next
one goes to the result set
Example:
• Booking system: Skip orders that are
pending
SELECT… FOR UPDATE NOWAIT
Common problem:
• Hot row contention, multiple worker
threads accessing the same rows
Solution 2:
• If any of the rows are already locked, the
statement should fail immediately
• Without NOWAIT, have to wait for
innodb_lock_wait_timeout (default: 50
sec) while trying to acquire lock
50
START TRANSACTION;
SELECT * FROM seats WHERE seat_no
BETWEEN 2 AND 3 AND booked = 'NO‘
FOR UPDATE SKIP LOCKED;
START TRANSACTION;
SELECT * FROM seats WHERE seat_no
BETWEEN 2 AND 3 AND booked = 'NO‘
FOR UPDATE NOWAIT;
ERROR 3572 (HY000):
Statement aborted because lock(s) could not be acquired …

View Slide

Resource Groups
• Finally possible to manage the mapping
between incoming MySQL threads and CPU.
• Resource Group is a logical grouping which will
be invoked by incoming threads
• Assign a priority to a resource group
• Pin a resource group to specific CPUs
• Combine resource group priority with CPU
“binding”
• Optimizer hints for resource groups are
available
• Resource group information stored in
INFORMATION_SCHEMA.RESOURCE_GROUPS
• On Linux enable CAP_SYS_NICE for mysqld
service
Create resource groups:
mysql> CREATE RESOURCE GROUP Reporting
TYPE=USER VCPU=5-6 THREAD_PRIORITY=10;
Set resource group:
mysql> SET RESOURCE GROUP Reporting FOR
;
mysql> SELECT /*+ RESOURCE_GROUP(Reporting) */
Monitor resource groups:
mysql> SELECT * FROM
INFORMATION_SCHEMA.RESOURCE_GROUPS;
52
0
100,000
200,000
Select
Update
Queries per Second
System Configuration :
Oracle Linux 7,
Intel(R) Xeon(R) CPU E7-4860 2.27GHz
40 cores-HT

View Slide

53
• Rewrite problematic queries without the need to make application changes
– Add hints
– Modify join order...
• Statements subject to rewriting: SELECT, INSERT, REPLACE, UPDATE & DELETE
• Standalone statements and prepared statements are subject to rewriting
• Statements occurring within view definitions or stored programs are not subject to rewriting
• e.g. Adding Rewrite Rules:
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement ) VALUES
("SELECT sum(salary) FROM employees.salaries WHERE from_date BETWEEN ? AND ?",
"SELECT /*+ INDEX(salaries idx_from_date) */ sum(salary) FROM employees.salaries
WHERE from_date BETWEEN ? AND ?");
• Information:
– https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin.html
– https://dasini.net/blog/2016/02/25/30-mins-with-mysql-query-rewriter/
Query Rewrite Plugin
https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin.html

View Slide

Some Linux optimization
• Set file/process limits using ulimit
o ulimit –n, limits the number of file handles (connections, open tables, …)
o ulimit –u, limits the number of threads (connections, InnoDB background threads, event scheduler, …)
• Depending on OS, workload and MySQL version consider using alternative malloc library
• Be aware of NUMA, set innodb_numa_interleave to 1 on dedicated MySQL Servers
• Swappiness, recommended setting=1 (1-6)
o sysctl -w vn.swappinness=1
o Do not set to 0 if you do not prefer to have OOM killer killing MySQL over using the SWAP
• For InnoDB avoid the FS cache for data stored in buffer pool:
o set innodb_flush_method=O_DIRECT
o Do not disable the FS cache, this is used by other parts (logs) of MySQL
54

View Slide

Improve the performance of your MySQL database - Essential tips and best practices

Improve the performance of your MySQL database - Essential tips and best practices

Olivier DASINI

More Decks by Olivier DASINI

Other Decks in Programming

Featured

Transcript