How to Optimize the MySQL Server For Performance

(1)

(2)

MySQL Server Performance

Tuning 101

Ligaya Turmelle

Principle Technical Support Engineer - MySQL

@lig

[email protected] https://joind.in/10284

Monday, January 27, 14

The basics of tuning some of the settings of the MySQL server. We will be covering some of the most common areas you should consider.

(3)

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,

AND TIMING OF ANY FEATURES OR FUNCTIONALITY DESCRIBED FOR ORACLE'S PRODUCTS REMAINS AT THE SOLE DISCRETION OF

ORACLE.

safe harbor - basically if I tell you about any upcoming products it is for informational purposes only. Oracle makes no guarantee.

(4)

MySQL in a Nutshell

▪ Worlds most popular open source database

▪ “M” of LAMP

▪ Main Site: mysql.com

▪ Developer Zone: dev.mysql.com OR mysql.org

▪ Downloads and Labs

▪ Manuals and Bugs

▪ Forums and Worklogs

(5)

Laying the Foundation

(6)

Step 0

(7)

Step 0

The MySQL Server ▪ Optimize the queries

▪ Database Schema

(8)

Step 0

General

▪ No easy answers

▪ Benchmark and test

▪ Under allocate

This is a soft skill with no absolute answers Change 1 thing at a time

Under allocate rather then over allocate or you swap There is such a thing as over-tuning the system

(9)

MySQL and Memory

Global Per Connection

Server Start As Needed

Large Values Small Values

Allocated Once Allocated 0-N Times

Global Memory + (Max Connections * Session Buffers) Global Memory + (Max Connections * Session Buffers)

(10)

mysql> SHOW GLOBAL VARIABLES;

+---+---| Variable_name +---+---| Value +---+---| auto_increment_increment +---+---| 1 | auto_increment_offset | 1 | autocommit | ON | automatic_sp_privileges | ON | back_log | 80 | basedir | /usr | big_tables | OFF | bind_address | * | binlog_cache_size | 32768 | binlog_checksum | CRC32 | binlog_direct_non_transactional_updates | OFF | binlog_format | STATEMENT | binlog_max_flush_queue_time | 0 | binlog_order_commits | ON

Current Settings

mysql> SHOW GLOBAL VARIABLES; Monday, January 27, 14

(11)

[client]

socket = /var/run/mysqld/mysql.sock [mysqld]

# These settings are for this specific box. server-id = 1 log-bin = /var/lib/mysql/binlogs/XXXXXXXX-BIN pid-file = /var/run/mysqld/mysql.pid socket = /var/run/mysqld/mysql.sock performance_schema max_connections = 200

# The ft_min_word_len is set to 3 instead of the default # 3 letter acronyms in the tables with full text

ft_min_word_len = 3

Current Settings

my.cnf / my.ini

(12)

Anything Else?

32 bit addressing limit

read/write heavy? short or report queries Mostly InnoDB? MyISAM? Memory? Etc.

(13)

mysql> show global status;

+---+---+ | Variable_name | Value | +---+---+ | Aborted_clients | 9363 | | Aborted_connects | 15925 | | Binlog_cache_disk_use | 15684 | | Binlog_cache_use | 272758675 | | Bytes_received | 1219816389366 | | Bytes_sent | 5227022143540 | | Com_admin_commands | 12803263 | | Com_assign_to_keycache | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 |

Current Status

mysql> SHOW GLOBAL STATUS; Monday, January 27, 14

Counters - what is done, not what we think is done.

Issued twice to find the Delta. Ok during normal operations but best during peak times.

(14)

DANGER - Math Ahead!

(15)

Finding the Delta

▪ Uptime - seconds

▪ Ex: 8249391 sec = ~2291.5 hrs = ~95.5 days

▪ Find your rate of change

(16)

Example

(17)

Time to Start

(18)

▪

GENERAL INFO

(19)

Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 19 | Com_select | 1530076294 | | Com_set_option | 18004985 | | Com_signal | 0 | | Com_show_authors | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 27498 | | Com_show_charsets | 1 | | Com_show_collations | 39 | | Com_show_contributors | 0 | | Com_show_create_db | 3 | | Com_show_create_event | 0 | | Com_show_create_func | 1120 | | Com_show_create_proc | 14499 | | Com_show_create_table | 183538 | | Com_show_create_trigger | 0 |

COM_*

▪ Counters ▪ Each command ▪ Used to calculate the Delta Monday, January 27, 14

(20)

Et Al.

▪ Connections ▪ Queries ▪ Questions ▪ Slow_queries ▪ Sort_merge_passes Monday, January 27, 14

Connections - # of attempts to connect - successful or not Queries - # of statements executed - including stored

procedures

Questions - # of statements sent to the server by clients and executed

Slow_queries - # of queries that took longer then long_query_time sec to run

Sort_merge_passes - # of merge passes that sort algorithm had to do

(21)

▪

INNODB BASICS

(22)

How is InnoDB doing?

▪ Space

▪ Innodb_buffer_pool_pages_data

▪ Innodb_buffer_pool_pages_total

▪ Innodb_buffer_pool_pages_free

- Pages data – number of pages containing data (clean and dirty) - Pages total – total size in pages (innodb_page_size – compiled in, default is 16KB)

- Pages_free – some are fine – but if you see a lot for a while, you over allocated

- Dirty page = a page that has changes in the buffer that has been saved to the log files

(23)

How is InnoDB doing?

▪ Efficiency

▪ Innodb_buffer_pool_read_requests

▪ Innodb_buffer_pool_reads

Formula:

1 - (Innodb_buffer_pool_read / Innodb_buffer_pool_read_requests) = buffer pool hit ratio

reads - reads from disk

(24)

innodb_buffer_pool_size

▪ Global

▪ Caches data and indexes

▪ Larger values reduces IO

▪ Self-contained

▪ 80% max

- Size in bytes

- InnoDB uses clustered indexes – remember

- Larger the value – the more it acts like an in-memory database. - Handles and buffers *everything* for itself

(25)

innodb_log_file_size

▪ Size of file on disk

▪ Larger the file, the less checkpoint activity

▪ Size for each log file

▪ Max combined log file size

▪ 4G <= 5.5

▪ 5.6 = 512GB

▪ Larger the log file ➯ longer recover time

- Less checkpoint activity means less IO

- Typically there are 2 log files in a log group (innodb_log_files_in_group)

- crash recovery code was optimized in 5.5. So the recovery time will be significantly lower in 5.5 then in 5.1.

- 5.5 Start test using the full 4G and modify as needed for your requirements and limitations.

(26)

innodb_log_buffer_size

▪ Buffer for writing to log files on disk

▪ Large transactions?

▪ Yes - increasing may help with IO

▪ Default is 8MB

- If you have large transactions, try to have them fit in here. This reduces the need to write the log to disk before the transactions commit.

(27)

innodb_file_per_table

▪ .ibd files

▪ Default: Off <= 5.6.5 > On

▪ Easier to reclaim space

▪ TRUNCATE for a table is faster

▪ Can monitor table size at the file system

▪ Can store specific tables on different storage devices

- for each newly created table the data and indexes will be

placed in a separate ibd file rather then in the shared tablespace - reclaiming space from the shared tablespace is not trivial.

- reasons for separate devices: IO Optimization, space management, backup purposes

(28)

▪

MyISAM

(29)

How is MyISAM doing?

▪ Space

▪ Key_blocks_unused

▪ Key_blocks_used

▪ High water mark

Formula:

key_buffer_size - (Key_blocks_unused * key_cache_block_size) = amount actually in use

- Block size can be found in key_cache_block_size

- Key_blocks_used indicates the max number of blocks that have ever been in use at one time.

(30)

How is MyISAM doing?

▪ Efficiency ▪ Key_read_requests ▪ Key_reads ▪ Key_write_requests ▪ Key_writes Formula:

Key_reads/Key_read_requests = key cache miss rate

- Key_read_requests – requests to read a key block

- Key_reads – number of physical reads from disk. Large value may mean your key_buffer_size is too small

* Key cache miss rate – the lower the value, the better - Key_write_requests – # of requests to write a key block - Key_writes - # of physical writes of a key block to disk

(31)

How is MyISAM doing?

▪ Locking

▪ Table_locks_immediate

▪ Table_locks_waited

- MyISAM uses table level locking. That means that every time you have to change it, the whole table will be locked for the duration. (Yes - concurrent inserts but that only works under

specific circumstances (If a MyISAM table has no holes in the data file (deleted rows in the middle), an INSERT statement can be

executed to add rows to the end of the table at the same time that SELECT statements are reading rows from the table.))

- Want to watch Table_locks_waited since this iterates every time you had to wait to get a table lock. If you have high values for this, and you do not use the MyISAM specific features, you may want to consider changing to another storage engine for better concurrency.

(32)

key_buffer_size

▪ AKA key cache

▪ Global

▪ Index blocks only

▪ 25% - maybe

- The index blocks are buffered and shared by all threads.

- MyISAM uses the OS file caching system to hold the data files. This means that we have to leave space for the OS file system

otherwise we can start swapping. And swapping can be bad with MySQL.

- For this reason, on a dedicated machine heavy on MyISAM, 25% of RAM is a place to start.

Notes:

- The maximum size is 4G for each key cache.

- You can have more then one key cache – see the manual for more information on that.

(33)

▪

QUERY CACHE

Ask if people understand how the query cache works. If they do not understand - explain it.

Also explain that this can become a bottle neck for performance with high concurrency since it is single threaded.

(34)

How is the QC doing?

▪ Qcache_total_blocks

▪ Qcache_free_blocks

▪ Not what you think

▪ Qcache_lowmem_prunes

- Free_blocks – think this would be a good thing, but isn’t. The higher the value , the greater the fragmentation in the query

cache.

* As Qcache_free_blocks approaches Qcache_total_blocks/2 – the more severe the fragmentation

* To defragment the query cache use the FLUSH QUERY CACHE statement

- lowmem prunes – number of queries deleted from the QC because of low memory

(35)

How is the QC doing?

▪ Qcache_hits

▪ Qcache_inserts

▪ Qcache_not_cached

▪ Qcache_queries_in_cache

(36)

QC Data into Info

▪ Hit rate

▪ Higher the better

▪ Invalidating queries

▪ Bigger the diff - the better

Formula:

Qcache_hits / (Qcache_hits + Com_select) = Query Cache Hit Rate

Comparison:

Qcache_inserts << Com_select

(37)

query_cache_size

▪ Global

▪ Allocated all at once

▪ Default is disabled

▪ Default size 0 < 5.6.8 => 1M

- The default of 0 disables the query cache. Generally speaking it is recommended that you start with the query cache off, and only if you think it can help you, do you turn it on. Be sure to check the performance of the system when you turn it on to verify that it does increase. There is potential for substantial performance improvement, but do not assume that it will happen. With some query cache configurations or server workloads, it is possible to see a performance decrease. Before 5.6.8 default size was 0 - now 1M with

query_cache_type=0.

- Minor side note - http://bugs.mysql.com/bug.php?id=38551 (5.1) – Query cache mutex still acquired even with query cache size set to 0

(38)

query_cache_type

▪ Global - but can be session

▪ 3 options ▪ 0 “Off” ▪ 1 “On” ▪ 2 “On Demand” ▪ Default: 1 < 5.6.8 => 0 Monday, January 27, 14

0 - Do not cache results or pull results from the QC

1 - Cache all you can unless told not to with SQL_NO_CACHE 2 - Cache only those queries that have SQL_CACHE

Turning it Off (0) - now saves a significant amount of overhead since it will no longer try to acquire the query cache mutex at all. However that means you can not turn on the query cache at

(39)

Gotcha!

The query cache size will still be allocated when the server starts up – even if you have the type set to 0.

(40)

▪

THREADS AND TABLE

CACHE

(41)

Threads

▪ Threads == Connection ▪ Thread Cache ▪ Threads_cached ▪ Threads_connected ▪ Threads_created Formula:

Threads_created /Connections = Thread cache miss rate

This is more for those using an older system. - In regards to this discussion – that is.

- Thread cache

* threads can be expensive to create and destroy for every connection.

- explain how the thread cache works. (starts out empty and as new connections are made it checks the cache to see if one is

there. If so – it uses it. If not it makes a new thread. When done it puts the threads back into the thread cache until it has its max value.

Watch Threads_created – if large consider increasing the thread_cache

(42)

thread_cache_size

▪ Global but grows as needed

▪ Number to be cached until needed

▪ May increase performance with lots of new connections

▪ or may not

Formula:

100 - ((Threads_created/Connections) * 100) = Thread cache efficiency

Because of modern thread implementations be sure to test to see if you get any improvement.

(43)

Table_cache

▪ table_cache <= 5.1.3 > table_open_cache

▪ Expands as needed

▪ Number of open tables for all connections

▪ Watch Opened_tables

- Similar to OS level file descriptors. Increasing this value may mean you have to increase the number of file descriptors MySQL requires.

- If Opened_tables is constantly increasing and you are *not* using FLUSH TABLES often – consider increasing this.

- related to max_connections (For example, for 200 concurrent running connections, specify a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some

extra file descriptors for temporary tables and files.)

(44)

▪

INNODB NOT SO BASICS

InnoDB takes care of most things automatically. So usually you do not need to tune these setting until there is a problem.

Unfortunately working with these settings is not as

straightforward as watching the output of SHOW GLOBAL

STATUS. Instead you would need to watch the output of SHOW ENGINE INNODB STATUS. To be perfectly honest - reading and understanding the output of SHOW ENGINE INNODB STATUS is a complex enough subject for a talk all by itself and I just do not have the time to cover it.

That being said, I still think you should know about these.

If you want to know more about the INNODB STATUS output be sure to check out the manual and the mysql performance blog post. A very good explanation is also available in the the book High Performance MySQL.

(45)

innodb_buffer_pool_instances

▪ 5.6

▪ Problem: contention for the buffer pool resources

▪ For systems with multi-GB buffer pools

▪ Total buffer pool size divided between this many instances

▪ Each instance

▪ manages its own resources

▪ has its own mutex

- Problem seen in INNODB STATUS in the SEMAPHORE section - Generally each instance should not be less then 1G

- Allows for parallelism of the buffer pool resources

(46)

innodb_stats_persistent

▪ 5.6

▪ Problem: changing execution plans

▪ Index statistics from ANALYZE TABLE stored on disk

▪ Results in stabler and usually better execution plans

▪ Periodic ANALYZE TABLE should be run if set

- Turn on if you have queries that change to suboptimal execution plans

- statistics stay consistent until next ANALYZE TABLE

- Once a week or month is probably sufficient for tables that

have fairly stable or gradually changing sizes. For tables that are small or have very rapidly changing contents more frequent will be beneficial.

(47)

innodb_thread_concurrency

▪ Max number of OS threads concurrently inside InnoDB

▪ 5.5+: Default 0 - unlimited

▪ Problem: InnoDB thread “Log Jam”

▪ Test a range of values to see what works best

- Additional threads placed into wait state in FIFO queue - Think about that - unlimited number of threads trying to

access the same finite number or resources. Now add in if you have long running queries holding locks... Eventually even the small fast queries will slow down as they all fight for the

resources they need! This is a concurrency issue.

- Basic Signs: increasing number of running transactions in

InnoDB and server performance degrades. Never stops - but runs really slow

(48)

innodb_concurrency_tickets

▪ Works with innodb_thread_concurrency

▪ Number of “free tickets” given to a ticket

▪ While tickets != 0 enter and exit InnoDB freely

▪ No tickets - back to innodb_thread_concurrency check

▪ Long running queries blocking short faster queries

▪ Consider increasing

- can help with the “log jam”

- By allowing long running queries extended time in InnoDB, it is more likely they will finish rather then having to go back into the thread_concurrency FIFO queue (while still holding its locks).

(49)

▪

SESSION LEVEL

- These values should in the global scope be set to a small value since they are used for each session. Only if you have a session that is doing something out of the ordinary should you consider dynamically changing these values. Keep in mind that each

query can potentially have 1 – N instances of a session level buffer as well (think multiple temp tables), so if you increase

these values for a specific session you have to be sure the space is available.

(50)

max_heap_table_size

▪ Dynamic

▪ Sets maximum size of all MEMORY tables

Temporary Tables

tmp_table_size

▪ Dynamic

▪ In-memory - MEMORY table

▪ Max size of internal in-memory temp tables

▪ Becomes on-disk MyISAM table

- Other reasons temp tables can automatically go to disk: * BLOB

* column in a GROUP BY or DISTINCT clause larger than 512 bytes

* any column larger than 512 bytes in the SELECT list, if UNION or UNION ALL is used

(51)

sort_buffer_size

▪ Dynamic

▪ Doing a sort - allocates a buffer this size

▪ Helps with ORDER BY and/or GROUP BY

▪ Watch Sort_merge_passes

(52)

read_buffer_size

▪ Dynamic

▪ Sequential scan - allocates this size for each table scanned

▪ caching results of nested queries

▪ caching index for sorting with ORDER BY

▪ Max: 2GB

▪ not normally higher then 8M though

- For each thread that does a sequential scan on a MyISAM table…

- Sequential scan includes full table scans and ranges

This option is also used in the following context for all search engines:

For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.

For bulk insert into partitions.

For caching results of nested queries.

and in one other storage engine-specific way: to determine the memory block size for MEMORY tables.

(53)

read_rnd_buffer_size

▪ Dynamic

▪ Reading MyISAM rows in arbitrary sequence

▪ Large value can potentially improve ORDER BY

▪ Max: 2 GB

▪ not normally higher then 8M

- to avoid disk seeks

- Example of usage – reading rows in a sorted order after the key sorting

(54)

bulk_insert_buffer_size

▪ Dynamic

▪ Cache to assist with MyISAM bulk inserts

▪ Set to 0 to disable

- Bulk inserts == INSERT … SELECT, INSERT … VALUES (…), (…), (…)…, LOAD DATA INFILE when adding data to non-empty

(55)

join_buffer_size

▪ Use with care

▪ Used for

▪ plain index scan

▪ range index scan

▪ joins that do not use indexes - full table scan

▪ One buffer for EACH full join between tables

- I am always hesitant to show this slide. I do not want people to think this can be used to help poorly optimized queries. Should be used as a band – aid or if adding the appropriate indexes is not possible.

Make it very large - and you may have to wait for the the allocation of the memory

(56)

Questions?

(57)

MySQL Server Performance

Tuning 101

Ligaya Turmelle

Principle Technical Support Engineer - MySQL

@lig

[email protected] https://joind.in/10284

(58)