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

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

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

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

reserved. Performance Configuration Options 5 InnoDB Buffer Pool

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.

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

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/

Demo 1 InnoDB Buffer Pool

10 Schema Optimization

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

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

Demo 2 Schema Optimization

14 Query Performance Make the best use of your data

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

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

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

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 Some Reasons Why Index can NOT be Used 2/4

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); Some Reasons Why Index can NOT be Used 3/4

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) ; Some Reasons Why Index can NOT be Used 4/4

Demo 3 Some Reasons Why Index can NOT be Used

Demo 4 Index impact on write intensive workload

Demo 5 Sort data by PK order before import

Demo 6 Save network traffic

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

26 MySQL HeatWave

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

Demo 7 MySQL HeatWave

29 MySQL Shell

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

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

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

Demo 8 MySQL Shell - Dump & Load Utilities

reserved. Follow us on Social Media

reserved. 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

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

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

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

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

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

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

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

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

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

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'; +----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+ | 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 | +----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+

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;

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

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 …

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 <thread>; 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

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

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

Improve the performance of your MySQL database ...

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

More Decks by Olivier DASINI

Other Decks in Programming

Featured

Transcript