MySQL Performance Tuning

Sumi Ryu
Senior Sales Consultant

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

 Program Agenda

 Basics: Hardware, Storage Engines and Versions

 Server Tuning
 Index, Query and Schema Optimization
 MySQL Performance Schema Introduction
 MySQL Enterprise Monitor and Query Analyzer

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

 Choosing Hardware

 Up to 64 CPU cores (MySQL 5.6 and above)

 RAM
 Linux, Solaris, Windows http://www.mysql.com/support
 Disks
– Fast HD (10-15k RPM SATA)
– RAID 10, Battery Backed Write Cache (RAID controller)
– SSD (for higher throughput) -- MySQL 5.6
 Redundant Network and Power
 Slaves = Master
3 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
 MySQL Storage Engines

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

 MySQL Engines
Tuning Decision

Pluggable Storage Engines

Memory, Index and Storage Management
InnoDB MyISAM NDB

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

 InnoDB

 Transactional and fully ACID compliant

– Crash Recovery
– Multi-version Concurrency Control (MVCC)
– Row-level Locking
 Data and Index in Memory
 In 5.6, InnoDB Provides
– Equivalent Read Performance
– Full-Text Search Indexes
– Improved Partitioning for Load Speeds

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

 MyISAM
 MyISAM Traditional Use Case:
– High Reads
– No Transactions or No Crash Recovery
– Table-level Locking
– Geospatial Support (RTREE Indexes)

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

 MySQL Versions

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

 MySQL Version – A Tuning Decision

2008 2009 2010 2012

Up to 4 Cores Up to 16 Cores Up to 32 Cores 48 + Cores

MySQL 5.0 (Sun Micro) (Oracle) MySQL 5.6
MySQL 5.1 MySQL 5.5
(InnoDB Plugin)

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

 MySQL 5.6: Scalability

 Users can fully utilize latest generations of hardware and OS

 Scales as data volumes and users grow
10 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
 Server Tuning

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

 Tuning Rules
 Never make a change in production first
 Have a good benchmark or reliable load
 Start with a good baseline
 Only change 1 thing at a time

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

 Tuning Rules -- continued

 Monitor the results

– Query performance - query analyzer, slow query log, etc.
 throughput
 single query time
 average query time
– CPU - top, vmstat
– IO - iostat, top, vmstat, bonnie++
 Document and save the results

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

 Benchmarks
 Make your own
– Can use general query log output
– JMeter, LoadRunner, Visual Studio

 mysqlslap http://dev.mysql.com/doc/refman/5.6/en/mysqlslap.html
 supersmack http://vegan.net/tony/supersmack/
 mybench http://jeremy.zawodny.com/mysql/mybench/

 SysBench http://sysbench.sourceforge.net/
 DBT2 http://osdldbt.sourceforge.net/

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

 MySQL VARIABLES
SYSTEM VARIABLES STATUS VARIABLES
 SYSTEM: datadir aborted_clients
– my.cnf/my.ini general-log connections
– Some Dynamic
innodb_buffer_pool_size created_tmp_disk_tables
– Some Session/Global
max_connections threads_created
 STATUS:
port uptime
– Session/Global
… …

 http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
 http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html

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

 MySQL Status
Status Variables WATCH
 max_used_connections

 TUNE: System Variables

 MONITOR: Status Variables

 SHOW [GLOBAL|SESSION] STATUS

mysql>SHOW global status like ‘max_used_connections’
 “WATCH” box identifies status variables

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

 Defaults and Configuration Files

 5.6
– Updated Defaults for Modern Systems
– Auto-sized Variables
 Prior to 5.6
– Out-of-date Configuration File Samples
 example: my-innodb-heavy-4G.cnf
 Advice:
– Consider 5.6 Defaults
– Re-evaluate older config file entries

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

 InnoDB Tuning
WATCH
 Innodb_buffer_pool_reads
 Innodb_buffer_pool_read_requests
 innodb_buffer_pool_size
 80% of Available Memory
 mysql>show status like 'Innodb_buffer%' ;

 innodb_log_file_size = ~512MB 5.5+

 recovery time vs. performance
 high writes

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

 InnoDB Tuning -- next-level
Depends on Your Workload

 innodb_flush_log_at_trx_commit ( caution )
 1 sync to file (fsync) on each commit
 0/2 may lose 1 second of data
 innodb_flush_method=O_Direct
– depends on workload and hardware
 innodb_buffer_pool_instances = 8
– 5.5 and 5.6 only

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html

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

 MyISAM Tuning WATCH
 Key_read_requests
 Key_reads
 Key_buffer_size

 Caches
– key_buffer_cache – 25% of Available Memory
– System Cache – 75% of Available Memory
 Multiple Key Buffers
 Pre-load Key Buffers
 Details:
– http://dev.mysql.com/doc/refman/5.6/en/myisam-key-cache.html

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

 General Server System Variables
Commonly Tuned WATCH
 %opened%
 %thread%
 table_open_cache  Threads_created

– 5.6 changed default from 400-2000

 thread_cache_size
– goal Threads_created ~ thread_cache_size

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

 General Server System Variables
Query Cache WATCH
 Only Use If  qcache_hits
 qcache_inserts
– Identical Queries and Data  qcache_not_cached
– Very Few Inserts/Updates/Deletes  qcache_total_blocks
 qcache_free_memory
 Caches Query and ResultSet
– 0 or OFF
– 1 or ON Cache all unless SELECT SQL_NO_CACHE
– 2 or DEMAND cache none unless SELECT SQL_CACHE

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

 General Server System Variables
Temporary Tables – Caution  RAM WATCH
 created_tmp_tables

 tmp_table_size  created_tmp_disk_tables

– Maximum size for “in memory” tables

– Memory vs. MyISAM (on disk)
 If temporary table >
– tmp_table_size or max_heap_table_size or
– BLOB/TEXT
Converts to MyISAM table on disk

http://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html

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

 System Variables -- Caution
Depends on Workload or Query WATCH
Bigger is Not Always Better  %opened%
Uses Memory Per Thread or JOIN  %thread%
 Threads_created
 soft_buffer_size
– sorting for group by and order by
– If 100M = 100M of RAM per sort
– mixed results in lab
– 2M -> 256K in 5.6
 Advice
– leave default or thoroughly test
– set dynamically

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

 System Variables – Caution -- Continued
Depends on Workload or Query WATCH
Bigger is Not Always Better  Select_full_join
Uses Memory Per Thread or JOIN

 join_buffer_size
– joins that don’t use indexes
– minimum allocated per join per thread
 Advice
– leave default
– set dynamically
– benchmark
– tune query

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

 Summary
Definitely Tune: Caution
 InnoDB Buffer Pool  sort_buffer_size

 Key Buffer Cache (MyISAM)  join_buffer_size

Tune and Evaluate:  read_buffer_size (MyISAM)

 innodb_log_file_size  read_rnd_buffer_size

 innodb_flush_log_at_trx_commit
 innodb_flush_method
 innodb_buffer_pool_instances (5.5, 5.6+)
 table_open_cache
 thread_cache_size
 query cache (turn off?)
 tmp_table_size (per session)

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

 Summary – 5.6 Defaults
Less Tuning Required 5.5->5.6
Definitely Tune: Caution
 InnoDB Buffer Pool  sort_buffer_size 2MB->256K

 Key Buffer Cache (MyISAM)  join_buffer_size 128K->256K

Tune and Evaluate:  read_buffer_size (MyISAM)

 innodb_log_file_size 5M->48M  read_rnd_buffer_size

 innodb_flush_log_at_trx_commit
 innodb_flush_method
 innodb_buffer_pool_instances 1->8
 table_open_cache 400->2000 – https://blogs.oracle.com/supporting
 thread_cache_size 0->8+max_con/100 mysql/entry/server_defaults_chang
es_in_mysql
 query cache
 tmp_table_size
27 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
 Indexes, Queries and
Schemas

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

 InnoDB vs. MyISAM Indexes

 InnoDB “Clustered” Indexes

– Primary Key Includes Data
– Secondary Keys Append Primary Key
 Data Retrieved From Primary Key
 MyISAM
– Primay Key Points to Physical Data
– Secondary Key Points to Physical Data

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

 Implications

 InnoDB
– Fast Primary Key Lookups and Range Scans
– Specify a Primary Key
– Keep Primary Keys Small
– Auto-Increment
– Covering Index (All Data to Satisfy Query Is in Index)
 MyISAM
– Covering Index

SELECT fname, lname FROM customer WHERE lname=‘Jones’;

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

 Index Best Practices

 Avoid Unnecessary Indexes

mysql > SHOW CREATE TABLE tablename
 Avoid Duplication
– index key123 (col1,col2,col3)
– index key12 (col1,col2) <- Not needed!
– index key1 (col1) <-- Not needed!
 Indexes should be 16 bytes/chars or less
 Large Strings or URL
– Separate Column with MySQL MD5 to Create Hash Key Column

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

 Schemas
 Smaller is Better
– Don’t set VARCHAR to 255 by Default
– Temp Tables and Caches Expand to Full Size
 Use VARCHAR instead of BLOB
– MEMORY engine for GROUP BY and ORDER BY
 PROCEDURE ANALYSE()
– http://dev.mysql.com/doc/refman/5.6/en/procedure-analyse.html
 InnoDB Primary Keys

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

 Queries
 The IN clause in MySQL is very fast!
– Select ... Where idx IN(1,23,345,456)
 Keep column alone on left side of condition
 Select ... Where func(idx) = 20 [index ignored]
 Select .. Where idx = otherfunc(20) [may use index]
 Avoid % at the start of LIKE on an index
– Select ... Where idx LIKE(‘ABC%’) can use index
– Select ... Where idx LIKE(‘%XYZ’) must do full table scan

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

 Queries -- Continued WATCH
 select_scan (full table scan)
 select_full_join (joins w/o Indexes)

 Enable Slow Query Log

– Use: log_queries_not_using_indexes
 Use mysqldumpslow :

 http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
 http://dev.mysql.com/doc/refman/5.6/en/mysqldumpslow.html

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

 Explain Plan Can Help with Tuning

• Order that the tables are accessed

• Indexes used

• Estimated number of rows accessed per table

EXPLAIN SELECT * FROM …

EXPLAIN FORMAT = JSON SELECT * FROM …

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

 Explain Plan
• Cost: 239 * 4145 * 1 = 990655

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

 Explain – Workbench and JSON

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

 Add Index

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

 • The original cost was 239 * 4145 * 1 =
Optimized 990,655
• The new cost is 1 * 1 * 1 = 1

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

 Type Column
Access or Join Types

Positive Possible Issue

 eq ref – unique key/primary to reference  ALL table scan (depends on table size)
value  INDEX (unless “using Index in EXTRA
 const, system –turn part of query into column”
constant
 Null – table or index not even accessed
 ref – match single value, non-unique
index, ref_or_null = possible extra step
 range – WHERE .. BETWEEN, >

http://dev.mysql.com/doc/refman/5.6/en/explain-output.htm

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

 Extra Column

Positive Possible Issue

 Using Index  Using temporary
 Using index for group by  Using filesort
 Using Where
 Good – Using Index

http://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain-extra-information

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

 MySQL Performance
Schema

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

 Performance Schema -- Configuration

 Enabling/Disabling Performance Schema

– Within my.cnf add:
[mysqld]
performance_schema=on

 Enable individual Instruments:

– Within my.cnf add:
[mysqld]
--performance_schema_instrument='wait/synch/cond/%=counted’
– off/false/0 = Disabled
– on/true/1 = Enabled & Timed
– counted = Enabled & Counted, rather than Timed
http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html

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

 Most Common Queries

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

 Last 10 Statements

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

 Files by File I/O

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

 Statements with Temporary Tables

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

 MySQL Enterprise Monitor

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

 Global Tuning Advisor

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

 Automated Rules

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

 Query Analyzer

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

 Specific Tuning Advice

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

 Correlation to Queries

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

 Enterprise Monitor Architecture
Agent

Service Manager

Enterprise
Dashboard

Repository

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

 Summary

 Basics: Hardware, Storage Engines and Versions

 Server Tuning
 Index, Query and Schema Optimization
 MySQL Performance Schema Introduction
 MySQL Enterprise Monitor and Query Analyzer

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

 Resources
 MySQL Training Course – MySQL Performance Tuning
http://education.oracle.com/pls/web_prod-plq-
dad/ou_product_category.getPage?p_cat_id=159
 View Performance Tuning Webinars
– http://www.mysql.com/news-and-events/on-demand-webinars/
 MySQL Performance Forum
– http://forums.mysql.com/list.php?24
 Download MySQL 5.6
– http://www.mysql.com/downloads/mysql/
 Try MySQL Enterprise Monitor:
– http://www.mysql.com/trials/
56 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
© 2011
57 Oracle Corporation
Copyright – Proprietary
© 2013, Oracle and/or and
its affiliates. All Confidential
rights reserved.
 The presentation 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, and timing of any features or
functionality described for Oracle’s products remains at the sole
discretion of Oracle.

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