Downloaded 35 times
![How to view your MySQL configuration
16
• Configuration meta data:
– View my.cnf and mysqld-auto.cnf for “static” configuration.
– SHOW [SESSION|GLOBAL] VARIABLES
– Filter out specific variables like: SHOW VARIABLES LIKE ’%expr%’
– performance_schema.global_variables
– performance_schema.session_variables
– performance_schema.variables_by_thread;
• Mapping connections to thread_id in PERFORMANCE_SCHEMA.threads](https://image.slidesharecdn.com/03mysqlperformancetw-201125122353/75/MySQL-Performance-Best-practices-16-2048.jpg)
Uploaded byTed Wennmark
MySQL Performance - Best practices
The document is a comprehensive guide on MySQL performance best practices, detailing the author's extensive experience with MySQL, particularly in the context of distributed databases. It covers critical aspects of performance tuning, including hardware and operating system configurations, MySQL settings, and query optimization techniques. Key recommendations include proper memory allocation, utilizing high-performance hardware, and effective index management for improved database operations.
More Related Content
Similar to MySQL Performance - Best practices
MySQL Performance - Best practices
- 1. Ted Wennmark MySQL SolutionArchitect EMEA Blog : http://mysql-nordic.blogspot.com/ Git projects : https://github.com/wwwted LinkedIn : https://www.linkedin.com/in/tedwennmark/ MySQL Performance Best practices 1
- 2. Safe harbor statement Thefollowing 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. 2
- 3. • Long timeMySQL user – Started developing apps using MySQL over 20 years back – Worked as MySQL DBA, Trainer and consultant the past • MySQL Prinicipal Solution Architect at Oracle • Work with normal MySQL but have focuse on distributed databases with NDB Cluster. • My workshops at Github: https://github.com/wwwted/ • Let’s stay in touch: – https://www.linkedin.com/in/tedwennmark/ • Join us on slack: https://lefred.be/mysql-community-on-slack/ Ted Wennmark 3
- 4.
- 5. MySQL is themost popular open-source database! DB-Engines 2020 Database Ranking 5
- 6. MySQL is themost popular database! Stack Overflow Developer Survey 2020 Developer Survey Results 6
- 7. - 3x BetterPerformance - Replication Enhancements - Optimizer Cost Model - JSON Support - Improved Security - Sys & Performance Schema - GIS MySQL 5.7 MySQL HA Cluster - MySQL Group Replication - MySQL ReplicaSet - MySQL Router - MySQL Shell MySQL 8.0 - 2x Better Performance - NoSQL Document Store - JSON - CTEs - Window Functions - Data Dictionary - InnoDB - Roles - Unicode - GIS 2 Years in Development 400+ Worklogs 5000+ Bugs Fixed 500 New Tests GA MySQL Innovation: 5.7 -> 8.0 7
- 8. Performance improvements inMySQL 8.0 2x Faster than MySQL 5.7 for RO and RW @scale • Scaling Read/Write Loads – Re-designing how InnoDB writes to Redo log • Utilizing IO Capacity – Removing file system mutex • High Contention Loads – Contention Aware Transaction Scheduling – CATS over FIFO • Resource Groups – Thread–CPU mapping can be managed • New tables that expose data locks in PS • UTF8MB4 • Partial JSON/BLOB update • Information Schema • Performance Schema • Cost Model (mem/disk aware) 8
- 9.
- 10. Memory • Make sureyour working set of data fits into the InnoDB buffer pool (RAM). • 70-80% of available memory can be assigned to InnoDB buffer pool. – On serves with less then 10GB of RAM use max 60-70% • MySQL connections will take memory, there are some MySQL buffers that are per connection. • Add extra RAM for: – OS – FS cache – RAM disk (tmpfs or ramfs) for temporary tables 10 https://lefred.be/content/mysql-and-memory-a-love-story-part-1/
- 11. CPU • Use fastand multi-core processors. • Standard servers today have 24-48 cores. • Enable Hyper-Threading. • In general: Faster cores are better than more but slower cores. • Remember that older versions of MySQL does not scale that well: – MySQL 5.1 scales to ~4 cores – MySQL 5.5 scale to ~16 – MySQL 5.6 scales to ~36 threads (cores) – MySQL 5.7 scales to ~64 threads (32 Core-HT) – MySQL 8.0 scales to ~100 threads (test on 48 Cores-HT intel Skylake) 11
- 12. Disk • Use highquality low-latency SSD or NVMe disks. – innodb_page_size = 4K – Innodb_flush_neighbors = 0 • If you want to RAID disks use RAID 1+0 for performance. • Spinning disks can still be used for “logs“ (Sequential IO). • InnoDB datadir, tmp files and undo logs are all Random IO. • Use FBWC/BBWC (Flash/Battery-BackedWriteCache) – Many storage solutions provide this option, use it. – It will speed up write operations and it’s crash-safe. – Same effect can be achieved by using batching changes or by setting innodb_flush_log_at_trx_commit to 0. 12
- 13. OS • L ofLAMP is Linux ;) • Good on Unix, Mac and Windows. • For pure performance, favor Linux. – If possible use Linux distribution with fresh Kernel for MySQL 8.0 – Use default file system, both ext4 and xfs are good. – Taskset can be used on shared OS to remove CPU contention. – Try the deadline or noop IO scheduler. 13
- 14. OS - Linux •Set file/process limits using ulimit: – ulimit –n, limits the number of file handles (connections, open tables, …) – 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-6): sysctl -w vn.swappinness=1 – 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: – set innodb_flush_method=O_DIRECT – Do not disable the FS cache, this is used by other parts (logs) of MySQL. 14 https://lefred.be/content/mysql-and-memory-a-love-story-part-1/
- 15.
- 16. How to viewyour MySQL configuration 16 • Configuration meta data: – View my.cnf and mysqld-auto.cnf for “static” configuration. – SHOW [SESSION|GLOBAL] VARIABLES – Filter out specific variables like: SHOW VARIABLES LIKE ’%expr%’ – performance_schema.global_variables – performance_schema.session_variables – performance_schema.variables_by_thread; • Mapping connections to thread_id in PERFORMANCE_SCHEMA.threads
- 17. How to viewyour MySQL configuration 17 mysql> SELECT t1.VARIABLE_NAME, t1.VARIABLE_SOURCE, t1.VARIABLE_PATH, t1.SET_TIME, t1.SET_USER, t1.SET_HOST, t2.VARIABLE_VALUE FROM performance_schema.variables_info t1 JOIN performance_schema.global_variables t2 ON t2.VARIABLE_NAME=t1.VARIABLE_NAME WHERE t1.VARIABLE_SOURCE not like "COMPILED"; +--------------------------------+-----------------+-----------------------------------------+----------------------------+----------+-----------+-------------------------------------------------------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | SET_TIME | SET_USER | SET_HOST | VARIABLE_VALUE | +--------------------------------+-----------------+-----------------------------------------+----------------------------+----------+-----------+-------------------------------------------------------+ | basedir | COMMAND_LINE | | NULL | NULL | NULL | /home/ted/src/mysql-8.0.21-linux-glibc2.12-x86_64/ | | datadir | COMMAND_LINE | | NULL | NULL | NULL | /home/ted/sandboxes/MySQL-HOWTOs/mysqldata/ | | default_authentication_plugin | EXPLICIT | /home/ted/sandboxes/MySQL-HOWTOs/my.cnf | NULL | NULL | NULL | mysql_native_password | | foreign_key_checks | DYNAMIC | | 2020-11-18 08:17:26.019090 | NULL | NULL | ON | | innodb_buffer_pool_size | EXPLICIT | /home/ted/sandboxes/MySQL-HOWTOs/my.cnf | NULL | NULL | NULL | 2147483648 | | innodb_directories | EXPLICIT | /home/ted/sandboxes/MySQL-HOWTOs/my.cnf | NULL | NULL | NULL | /home/ted/sandboxes/MySQL-HOWTOs/slabb2/ | | innodb_flush_log_at_trx_commit | DYNAMIC | | 2020-11-18 08:57:12.479082 | ted | localhost | 1 | | log_error | COMMAND_LINE | | NULL | NULL | NULL | ./speedy.err | | pid_file | COMMAND_LINE | | NULL | NULL | NULL | speedy.pid | | plugin_dir | COMMAND_LINE | | NULL | NULL | NULL | /home/ted/sandboxes/MySQL-HOWTOs/mysqlsrc/lib/plugin/ | | port | COMMAND_LINE | | NULL | NULL | NULL | 3306 | | secure_file_priv | EXPLICIT | /home/ted/sandboxes/MySQL-HOWTOs/my.cnf | NULL | NULL | NULL | | | socket | COMMAND_LINE | | NULL | NULL | NULL | /tmp/mysql.sock | +--------------------------------+-----------------+-----------------------------------------+----------------------------+----------+-----------+-------------------------------------------------------+ • When was the configuration changed and by who? – SELECT t1.*, VARIABLE_VALUE FROM performance_schema.variables_info t1 JOIN performance_schema.global_variables t2 ON t2.VARIABLE_NAME=t1.VARIABLE_NAME WHERE t1.VARIABLE_SOURCE not like "COMPILED";
- 18. InnoDB buffer pooland redo logs 18 • innodb_buffer_pool_size – Amount of memory for storing db pages in memory. – Default value is to log, for production 80% of available memory on dedicated database server. – Since MySQL 5.7, innodb_buffer_pool_size can be changed dynamically. • innodb_log_file_size – Size of redo logs. Will impact write speed vs time to recover. – Default value is to low, for production min 512MB is suggested. – Total redo log capacity decided by innodb_log_files_in_group (default value 2).
- 19. Transaction isolation level(ACID) 19 • How much isolation and read consistency do you want between transactions. • Stricter isolation typically means less concurrency as more comprehensive locks are taken in the database to isolate transactions. This can effect performance at scale. • Consider using READ-COMMITED isolation level. • transaction_isolation – READ-UNCOMMITTED – READ-COMMITTED (Oracle default) – REPEATABLE-READ (MySQL default) – SERIALIZABLE
- 20. Trading performance overconsistency (ACID) 20 • When should InnoDB flush/sync committed truncations. • innodb_flush_log_at_trx_commit: – 0 Transaction are written to redo logs once per second. – 1 (Default value) Fully ACID compliance. Redo-logs are written and flushed to disk at transaction commit. – 2 Transactions are written to redo logs at commit, redo logs are flushed ~ once per second. • I always recommend using default (1 for consistency) setting unless: – You are bulk loading data, set session variable to 2 during load or if you are on latest mysql 8.0 versions you can also disable redo-logging completely. – You are experiencing a unforeseen peak in workload (hitting your disk-system) and need to survive until you can solve problem. – It’s okay to loose some data….
- 21. Buffers that areper client connections 21 • Some buffers are per connection (max_connections ): – read_buffer_size – read_rnd_buffer_size – join_buffer_size – sort_buffer_size – binlog_cache_size (if binary logging is enabled) – net_buffer_length • Make sure you reserve some memory for these buffers if you have many connections.
- 22. Enabling Automatic Configurationfor a Dedicated MySQL Server 22 • Setting innodb_dedicated_server, InnoDB automatically configures the following variables: – innodb_buffer_pool_size – innodb_log_file_size – innodb_log_files_in_group – innodb_flush_method • Enabling innodb_dedicated_server is not recommended if the MySQL instance shares system resources with other applications.
- 23.
- 24. Schema Changes Data Growth Indexes SQL 90%of Performance problems Source of Database Performance Problems Hardware/configuration
- 25. 1 2 34 • Identify Slow Queries • Correlation Graphs • Query Response Time index (QRTi) • Execution Statistics • Tune Queries • Add Indexes • Add optimizer hints • Change configuration • MySQL Explain Plan • Profiling query Better Performance Query tuning from 10.000m
- 26. Monitoring 26 • Real-time performancemonitoring → What is happening right now – Take the right decision at the right time • Go back in time → What has happened (compare to base-line) – Useful to investigate performance issues • Predict the future → Allows you to proactively handle potential issues – Capacity planning • Visual Query Analysis – Examine the query execution plan, see sample queries, see the details of the query execution... • Helps you to enforce best practices
- 27. Monitoring cont. 27 • Applicationmetrics like response times • Slow Query Log • MySQL Enterprise Monitor “MEM” - Query Analyzer • performance_schema.events_statements_summary_by_digest • sys.statements_with_runtimes_in_95th_percentile • SHOW FULL PROCESSLIST, current queries
- 28. Monitoring tools • MySQLEnterprise Monitor – Includes Query Analyzer – Best practice performance advisors – Developed by the engineers of MySQL/InnoDB • Grafana • Cacti/Nagios • Zabbix • Prometheus 28
- 29. Query Tuning “MEM”Demo!
- 30. Analyzing queries 30 • EXPLAIN(show you execution plan from optimizer) • EXPLAIN ANALYZE (added in 8.0.19) • SET profiling=1 enables profiling. (SHOW PROFILES, SHOW PROFILE FOR QUERY X) – This information is as of MySQL 8.0 also available via performance_schma. • Optimizer trace (shows all possible optimizer plans) • Very important: talk to developers and ask them also explain what they are trying to achieve with the statement, sometime re-writing a statement is the best option. – For example subqueries can be rewritten as joins or statements using OR operator can rewritten to use UNION. • Also study the MySQL optimizer: – https://dev.mysql.com/doc/refman/8.0/en/optimization.html
- 31. Explain output 31 • EXPLAINColumns: – select_type Type SIMPLE if no Union, JOIN or Subquery – table The table for the output row – partitions Matching partitions – type Join Type, most common ref or eq_ref, worst is ALL – possible Keys The possible indexes to choose – key The index actually chosen – ref The columns compared to the index – rows Estimated number of rows to be examined – filtered percentage of rows filtered by table condition – extra Additional information from optimizer • Get total “cost” of query by multiply all row counts in output.
- 32. Solving the problem! 32 •Understand what statement is trying to achieve, can we re-write the statement? • Adding the appropriate indexes. • Update index statistics: ANALYZE TABLE • Optimizer Hints - can be specified within individual statements: – Join-Order Optimizer Hints – Table-Level Optimizer Hints – Index-Level Optimizer Hints and more • Switchable Optimizations: – optimizer_switch flags: enable control over optimizer behavior for all statements. • Use the query re-write plugin for 3rd party applications.
- 33.
- 34.
- 35. Database Design -Generic 35 • All tables should (must) have a user defined PK • Choose the correct datatypes, do not make all column VARCHAR(255) – Use UNSIGNED when possible to get full range of integers – Use JSON data type for JSON data, it’s much quicker that storing JSON in TEXT • INT(1) does not mean you are storing 1 byte, this is a 4 byte integer, (1) is the display width of the column. • Pick the correct character-set, latin1 is for most cases quicker than UTF8. • Always use column definition “NOT NULL” unless there are good reasons. • Batch loading data is always quicker compared to loading row-by-row.
- 36. Database Design -InnoDB 36 • Use only InnoDB • Keep your PK as small as possible, all secondary indexes will contain PK. – If you have no natural PK use auto_increment. • Foreign keys will slowdown your database: – All changes need to be verified towards the FK. – Foreign key checks now also take meta-data locks. • Avoid long running transactions, will build up lock queue of rowlocks and meta-data locks. – MySQL can stop long running statements with max_execution_time. – Setup replica server for complex long running operations. – Separate OLTP and OLAP workloads.
- 37. Database Design -Indexing 37 • Too many indexes will slow down inserts/update/delete operations. • Don’t duplicate leading parts of compound keys: – index key123 (col1,col2,col3) – index key12 (col1,col2) <- Not needed! – index key1 (col1) <-- Not needed! • Do not use functions/expressions on columns in WHERE clause. • The optimizer will in most cases only use one index for table filtering – If you want to filter on user_id and some timestamp you need a combined index on (user_id,timestamp) • InnoDB supports covering indexes: – “SELECT (a,b) FROM some_table where a=50;“ – Create index on (a,b) to avoid second lookup in PK B-Tree
- 38. Do not letyou database/tables grow out of control! • Large MySQL instances/schemas/tables will come at some cost (large > 2TB) – As your tables grow the index structure becomes deeper/wider (larger) and slows down all SQL operations. – Do not save old/historic data just because, if data is not needed remove or archive it. – Make sure you have retention policies in place for all fast growing tables. – Use partitioning to remove old data automatically and with no stress to the database. – Huge tables (100’s of millions or rows) can be partitioned for stable insert performance. – Large instances/schemas/tables are not just bad for performance it’s also a pain to handle backups and most other tasks. • InnnoDB compression can help temporarily but long term you need to implement proper pruning processes. 38
- 39. Complex SQL • ComplexSQL can be hard to read and understand and is very often slow. • Avoid to long SQL statements, anything longer than 50 rows - think twice. • Do not put complex logic into the database. • Sometime de-normalization is the solution. • Views can not be persisted or indexed in MySQL. 39 SELECT if(ASCII(concat( @w:=180, @h:=72, @max_iter:=20, @xmin:=-2.025, @xmax:=0.625, @ymin:=-1.25, @ymax:=1.25 ))+(@cm:=(4/@max_iter))+(@xs:=(@xmax-@xmin)/@w)+(@ys:=(@ymax- @ymin)/@h)+(@cnt:=0)+(@y0:=@ymin)+(@run:=1)+ASCII(@r:=SPACE(0))+(SELECT count(if(@run,if(mod(@cnt,@w),0,(@y0:=@y0+@ys)+if(@cnt,ASCII(@r:=concat(@r,0x 0d0a)),0))+(@x0:=@xmin+mod(@cnt,@w)*@xs)+(@i:=@x:=@y:=0)+(@runi:=1)+(SELE CT count(if(@runi,+(@xt:=@x*@x- @y*@y+@x0)+(@y:=2*@x*@y+@y0)+(@x:=@xt)+(@i:=@i+1)+if(@i=@max_iter or (@x*@x+@y*@y)>4,@runi:=0,0),0)) from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) a CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) b CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) c)+(@c:=ceil(@i*@cm))+(@v:=(CASE when @c=4 then char(0xE29688 using utf8) when @c=3 then char(0xE29693 using utf8) when @c=2 then char(0xE29692 using utf8) else char(0xE29691 using utf8) END))+ASCII(@r:=concat(@r,@v))+(@cnt:=@cnt+1)+if(@cnt=@w*@h,(@run:=0),0),0)) FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) a CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) b CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) c CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) d CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) e CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) f CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) g),@r,0)G
- 40.
- 41. Performance Top 10tips!
- 42. Tip 1: Monitoryour databases Without monitoring you are blind. Monitor the database and OS. Monitor over time, monitor query statistics.
- 43. Tip 2: Makesmall incremental changes Change one parameter, test/monitor, change the next one. Document changes in behaviour.
- 44. Tip 3: UseInnoDB MyISAM have table level locks. InnoDB is ACID. InnoDB scales on modern HW. Online DDL
- 45. Tip 4: Understand“explain” Query tuning is the holy grail of performance tuning and explain is the most important tool for query tuning.
- 46. Tip 5: Delete/archiveold data Large instances/tables are one of the most common problems for slow queries.
- 47. Tip 6: UseSSD/NVMe drives If your working-set of data does not fit into memory make sure you have a fast disk system (for random IO operations).
- 48. Tip 7: UseMySQL 8.0 MySQL 8.0 scales better than older versions of MySQL, the optimizer have also been improved with hash-joins and histograms.
- 49. Tip 8: Avoidlong running transactions Huge risk of locking contention, separate your OLTP and OLAP functionality on separate MySQL instances.
- 50. Tip 9: Useforeign keys with care Foreign keys are good for consistency but in general bad for performance and takes more locks.
- 51. Tip 10: Avoidcomplex SQL Hard to understand, hard to optimize and in many cases slow.
- 52. We can helpyou!!
- 53. Why MySQL EnterpriseEdition? Insure Your Deployments Get the Best Results Delight Customers Improve Performance & Scalability Enhance Agility & Productivity Reduce TCO Mitigate Risks Get Immediate Help if/when Needed Increase Customer Satisfaction 53
- 54. Management Tools Advanced Features Support •Scalability •HighAvailability •Security •Encryption + TDE •Data Masking •Firewall •Monitoring •Backup •Development •Administration •Migration • Technical Support • Consultative Support • Oracle Certifications MySQL Enterprise Edition 54
- 55. MySQL Enterprise Support •Largest MySQL engineering and support organization • Backed by the MySQL developers • World-class support, in 29 languages • Hot fixes & maintenance releases • 24x7x365 • Unlimited incidents • Consultative support • Global scale and reach Get immediate help for any MySQL issue, plus expert advice 55
- 56. MySQL Enterprise ConsultativeSupport • Remote troubleshooting • Replication review • Partitioning review • Schema review • Query review • Performance tuning • ...and more Get immediate help for any MySQL issue, plus expert advice 56
- 57. More information • https://dev.mysql.com/doc/refman/8.0/en/optimization.html •https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb.html • http://dimitrik.free.fr/blog/ • http://mysqlserverteam.com/ • https://lefred.be/ • http://mysqlhighavailability.com/ • https://dev.mysql.com/doc/refman/8.0/en/ 57
- 58. Thank you Ted Wennmark MySQLSolution Architect EMEA MySQL Business Unit 58