官方推荐的MySQL参数设置值

2018-09-16

官方推荐的MySQL参数设置值

下面的参数设置，对系统的性能会很有帮助。但是建议大家还是结合实际情况使用。

APPLIES TO:

MySQL Server – Version 5.6 and later
Information in this document applies to any platform.

PURPOSE

Strongly recommended initial settingsfor MySQL Server when used for OLTP or benchmarking.

SCOPE

For DBAs having OLTP-like workloads ordoing benchmarking.

DETAILS

We recommend that when using MySQLServer 5.6 you include the following settings in your my.cnf or my.ini file ifyou have a transaction processing or benchmark workload. They are also a goodstarting point for other workloads. These settings replace some of our flexibleserver defaults for smaller configurations with values that are better forhigher load servers. These are starting points. For most settings the optimalvalue depends on the specific workload and you should ideally test to find outwhat settings are best for your situation. The suggestions are also likely tobe suitable for 5.7 but 5.7-specific notes and recommendations are a work inprogress.

If a support engineer advises you tochange a setting, accept that advice because it will have been given afterconsidering the data they have collected about your specific situation.

Changes to makein all cases

These improve on the defaults to improveperformance in some cases, reducing your chance of encountering trouble.

innodb_stats_persistent = 1 # Also use ANALYZE TABLE for all tables periodically
innodb_read_io_threads =16 # Check pending read requests in SHOWENGINE INNODB STATUS to see if more might be useful, if seldom more than 64 *innodb_read_io_threads, little need for more.
innodb_write_io_threads = 4
table_open_cache_instances = 16 # 5.7.8onwards defaults to 16

metadata_locks_hash_instances = 256# better hash from 5.6.15,5.7.3. Irrelevant anddeprecated from 5.7.4 due to change in metadata locking

Main settings toreview

Also make these additions and adjust asdescribed to find reasonably appropriate values:

innodb_buffer_pool_size: the single mostimportant performance setting for most workloads, see the memory section laterfor more on this and InnoDB log files. Consider increasing innodb_buffer_pool_instances (5.6 manual) from the 8 default to bufferpool size / 2GB (so 32 for 64g pool) if concurrency is high, some old benchmark results to illustrate why..

innodb_stats_persistent_sample_pages: a valuein the 100 to 1000 range will produce better statistics and is likely toproduce better query optimising for non-trivial queries. The time taken byANALYZE TABLE is proportional to this and this many dives will be done for eachindex, so use some care about setting it to very large values.

innodb_flush_neighbors = 0 if you haveSSD storage. Do not change from server default of 1 if you are using spinningdisks. Use 0 if both.

innodb_page_size: consider 4k for SSD becausethis better matches the internal sector size on older disks but be aware thatsome might use the newer 16k sector size, if so, use that. Check your drivevendor for what it uses.

innodb_io_capacity: for a few spinning disksand lower end SSD the default is OK, but 100 is probably better for a singlespinning disk. For higher end and bus-attached flash consider 1000. Use smallervalues for systems with low write loads, larger with high. Use the smallestvalue needed for flushing and purging to keep up unless you see moremodified/dirty pages than you want in the InnoDB buffer pool. Do not useextreme values like 20000 or more unless you have proved that lower values arenot sufficient for your workload. It regulates flushing rates and related diski/o. You can seriously harm performance by setting this orinnodb_io_capacity_max too high and wasting disk i/o operations with prematureflushing.

innodb_io_capacity_max: for a few spinningdisks and lower end SSD the default is OK but 200-400 is probably better for asingle spinning disk. For higher end and bus-attached flash consider 2500. Usesmaller values for systems with low write loads, larger with high. Use thesmallest value needed for flushing and purging to keep up. Twiceinnodb_io_capacity will often be a good choice and this can never be lower thaninnodb_io_capacity.

innodb_log_file_size = 2000M is a goodstarting point. Avoid making it too small, that will cause excessive adaptiveflushing of modified pages. More guidance here.

innodb_lru_scan_depth: Reduce if possible.Uses disk i/o and can be a CPU and disk contention source. This multipliedby innodb_buffer_pool_instances sets howmuch work the page cleaner thread does each second, attempting to make thatmany pages free.Increase or decrease this to keep the result of multiplying thetwo about the same whenever you change innodb_buffer_pool_instances, unless youare deliberately trying to tune the LRU scan depth. Adjust up or down sothat there are almost never no free pages but do not set it much larger thanneeded because the scans have a significant performance cost. A smaller valuethan the default is probably suitable for most workloads, give 100 a tryinstead of the default if you just want a lower starting point for your tuning,then adjust upwards to keep some free pages most of the time. Increase innodb_page_cleaners to lower of CPUcount or buffer pools if it cannot keep up, there are limits to how muchwriting one thread can get done; 4 is a useful change for 5.6, 5.7 default isalready 4. The replication SQL thread(s) can be seriously delayed if there arenot usually free pages, since they have to wait for one to be made free. Errorlog Messages like “Log Messages: page_cleaner: 1000ms intended loop took8120ms. The settings might not be optimal.” usually indicate that you havethe page cleaner told to do more work than is possible in one second, so reducethe scan depth, or that there is disk contention to fix. Page cleaner has highthread priority in 5.7, particularly important not to tell it to do too much work,helps it to keep up. Document 2014477.1 has details of related settingsand measurements that can help to tune this.

innodb_checksum_algorithm=strict_crc32 if anew installation, else crc32 for backwards compatibility. 5.5 and earliercannot read tablespaces created with crc32. Crc32 is faster and particularlydesirable for those using very fast storage systems like bus-attached flashsuch as Fusion-IO with high write rates.

innodb_log_compressed_pages = 0 if usingcompression. This avoids saving two copies of changes to the InnoDB log, onecompressed, one not, so reduces InnoDB log writing amounts. Particularlysignificant if the log files are on SSD or bus-attached flash, something thatshould often be avoided if practical though it can help with commit rates ifyou do not have a write caching disk controller, at the cost of probably quiteshortened SSD lifetime.

binlog_row_image = minimal assuming alltables have primary key, unsafe if not, it would prevent applying the binarylogs or replication from working. Saves binary log space. Particularlysignificant if the binary logs are on SSD or flash, something that should oftenbe avoided.

table_definition_cache: Set to the typicalnumber of actively used tables within MySQL. Use SHOW GLOBAL STATUS and verifythat Opened_table_definitions is not increasing by more than a few per minute.Increase until that is true or the value becomes 30000 or more, if thathappens, evaluate needs and possibly increase further. Critical: seePerformance Schema memory notes. Do not set to values that are much larger thanrequired or you will greatly increase the RAM needs of PS in 5.6, much less ofan issue in 5.7. Note that in 5.6 801 can cause four times the PS RAM usage of800 by switching to large server calculation rules and 400 can be about halfthat of 401 if no other setting causes large rules.

table_open_cache: set no smaller thantable_definition_cache, usually twice that is a good starting value. Use SHOWGLOBAL STATUS and verify that Opened_tables is not increasing by more than afew per minute. Increase until that is true or the value becomes 30000 or more,if that happens, evaluate needs and possibly increase further. Critical:see Performance Schema memory notes. Do not set to values that are muchlarger than required in 5.6, much less of an issue in 5.7, or you will greatlyincrease the RAM needs of PS. Note that in 5.6 4001 can cause four timesthe PS RAM usage of 4000 by switching to large server calculation rules and2000 can be about half that of 2001 if no other setting causes large rules.

max_connections: This is also used forautosizing Performance Schema. Do not set it to values that are far higher thanreally required or you will greatly increase the memory usage of PS. If youmust have a large value here because you are using a connection cache, considerusing a thread cache as well to reduce the number of connections to the MySQLserver. Critical: see Performance Schema memory notes. Do not set tovalues that are much larger than required or you will greatly increase the RAMneeds of PS. Note that 303 can cause four times the PS RAM usage of 302 byswitching to large server calculation rules and 151 can be about half that of302 if no other setting causes large rules.

open_files_limit: This is also used forautosizing Performance Schema. Do not set it to values that are far higher thanreally required in 5.6, less of an issue in 5.7.

sort_buffer_size = 32k is likely to befaster for OLTP, change to that from the server default of 256k. Use SHOWGLOBAL STATUS to check Sort_merge_passes. It the count is 0 or increasing by upto 10-20 per second you can decrease this and probably get a performanceincrease. If the count is increasing by less than 100 per second that is alsoprobably good and smaller sort_buffer_size may be better. Use care with largesizes, setting this to 2M can reduce throughput for some workloads by 30% ormore. If you see high values for Sort_merge_passes, identify the queries thatare performing the sorts and either improve indexing or set the session valueof sort_buffer_size to a larger value just for those queries.

innodb_adaptive_hash_index (5.6 manual) Try both 0 and 1, 0 may showimprovement if you do a lot of index scans, particularly in very heavyread-only or read-mostly workloads. Some people prefer always 0 but that missessome workloads where 1 helps. There’s an improvement in concurrency from 5.7.8 to use multiplepartitions and the option innodb_adaptive_hash_index_parts wasadded, this may change the best setting from 0 to 1 for some workloads, at thecost of slower DBT3 benchmark result with a single thread only. More workplanned for 5.8.

innodb_doublewrite (5.6 manual) consider 0/off instead of thedefault 1/on if you can afford the data protection loss for high write loadworkloads. This has gradually changed from neutral to positive in 5.5 to morenegative for performance in 5.6 and now 5.7.

Where there is a recommendation to check SHOW GLOBAL STATUS output you shoulddo that after the server has been running for some time under load and hasstabilised. Many values take some time to reach their steady state levels orrates.

SSD-specificsettings

Ensure that trim support is enabled inyour operating system, it usually is.

Set innodb_page_size=4k unless you want a largersize to try to increase compression efficiency or have an SSD with 16k sectors.Use innodb_flush_neighbors=0 .

Memory usage andInnoDB buffer pool

For the common case where InnoDB isstoring most data, setting innodb_buffer_pool_size to a suitably large value isthe key to good performance. Expect to use most of the RAM in the server forthis, likely at least 50% on a dedicated database server.

The Performance Schema can be a far moresubstantial user of RAM than in previous versions, particularly in 5.6, less ofan issue in 5.7. You should check the amount of RAM allocated for it using SHOWENGINE PERFORMANCE_SCHEMA STATUS . Any increase of max_connections, open_files_limit,table_open_cache or table_definition_cache above the defaults causes PS toswitch to allocating more RAM to allow faster or more extensive monitoring.For this reason in 5.6 in particular you should use great care not to set thosevalues larger than required or should adjust PS memory allocation settingsdirectly. You may need to make PS settings directly to lower values if youhave tens of thousands of infrequently accessed tables. Or you can set this toa lower value in my.cnf and change to a higher value in the server init file. It is vital to considerthe PS memory allocations in the RAM budget of the server. See On configuring the Performance Schema formore details on how to get started with tuning it. If all of max_connections,table_definition_cache and table_open_cache are the same as or lower than their151, 400 and 2000 defaults small sizing rules will be used. If all are no morethan twice the defaults medium will be used at about twice the small memoryconsumption (eg. 98 megabytes instead of 52 megabytes). If any is more thantwice the default, large rules will be used and the memory usage can be abouteight times the small consumption (eg. 400 megabytes). For this reason, avoidgoing just over the 302, 800 and 4000 values for these settings if PS is beingused, or use direct settings for PS sizes. The size examples are with littledata and all other settings default, production servers may see significantlylarger allocations. From 5.7 the PS uses more dynamic allocations on demand sothese settings are less likely to be troublesome and memory usage will varymore with demand than startup settings.

Very frequent and unnecessarily largememory allocations are costly and per-connection allocations can be more costlyand also can greatly increase the RAM usage of the server. Please takeparticular care to avoid over-large settings for: read_buffer_size,read_rnd_buffer_size, join_buffer_size, sort_buffer_size, binlog_cache_size andnet_buffer_length. For OLTP work the defaults or smaller values are likely tobe best. Bigger is not usually better for these workloads. Use caution withlarger values, increasing sort_buffer_size from the default 256k to 4M wasenough to cut OLTP performance by about 30% in 5.6. If you need bigger valuesfor some of these, do it only in the session running the query that needssomething different.

The operating system is likely to cachethe total size of log files configured with innodb_log_file_size. Be sure toallow for this in your memory budget.

Thread_stack is also a session settingbut it is set to the minimum safe value for using stored procedures, do notreduce it if using those. A maximum reduction of 32k might work for otherworkloads but remember that the server will crash effectively randomly if youget it wrong. It’s not worth touching unless you are both desperate and anexpert. We increase this as and only when our tests show that the stack size istoo small for safe operation. There is no need for you to increase it. Best notto touch this setting.

Operatingsystems

CPU affinity: if you are limiting thenumber of CPU cores, use CPU affinity to use the smallest possible number ofphysical CPUs to get that core count, to reduce CPU to CPU hardware consistencyoverhead. On Linux use commands like taskset -c 1-4 pid of mysqld or in windows START /AFFINITY orthe Task Manager affinity control options.

Linux

Memory allocator: we ship built to uselibc which is OK up to about 8-16 concurrent threads. From there switch tousing TCMalloc using the mysqld_safe —malloc-lib option or LD_PRELOAD orexperiment with the similar and possibly slightly faster jemalloc, which mightdo better with memory fragmentation, though we greatly reduced potentialfragmentation in MySQL 5.6. TCMalloc 1.4 was shipped with many MySQL versionsuntil 5.6.31 and 5.7.13. A the time of writing TCMalloc 2.5 is the latestversion so you may want to experiment with that and jemalloc to see which worksbest for your workload and system.

IO scheduler: use noop or deadline. Inrare cases CFQ can work better, perhaps on SAN systems, but usually it issignificantly slower. echo noop >/sys/block/{DEVICE-NAME}/queue/scheduler .

nice: using nice -10 in mysqld_safe canmake a small performance difference on dedicate servers, sometimes larger onhighly contended servers. nice -20 can be used but you may find it hard toconnect interactively if mysqld is overloaded and -10 is usually sufficient. Ifyou really want -20, use -19 so you can still set the client mysql to -20 to get in and kill a rogue query.

Use cat “/proc/pgrep -n mysqld/limits to check the ulimit values for arunning process. May need ulimit -n to set maximum open filesper process and ulimit -u for a user. The MySQLopen_files_limit setting should set this but verify and adjust directly ifneeded.

It is often suggested to use “numactl–interleave all” to prevent heavy swapping when a single large InnoDB bufferpool is all allocated on one CPU. Two alternatives exist, using multiple InnoDBbuffer pools to try to prevent the allocations all going on one CPU is primary.In addition, check using SHOW VARIABLES whether your version has been builtwith support for the setting innodb_numa_interleave . If the settingis present, turn it on, setting to 1. It changes to interleaved mode(MPOL_INTERLEAVE) before allocating the buffer pool(s) then back to standard(MPOL_DEFAULT) after. The setting is present on builds compiled on a NUMAsystem from 5.6.27 onwards.

Set vm.swappiness=1 in /etc/sysctl.conf. It is often suggested to use 0 to swap only an out of memory situation but 1will allow minimal swapping before that and is probably sufficient. Usewhatever works for you but please use caution with 0. Higher values can have atendency to try to swap out the InnoDB buffer pool to increase the OS diskcache size, a really bad idea for a dedicated database server that is doing itsown write caching. If using a NUMA system, get NUMA settings in place beforeblaming swapping, on swappiness. It is known that a large single buffer poolcan trigger very high swapping levels if NUMA settings aren’t right, the fix isto adjust the NUMA settings, not swappiness.

Do not set the setting in this paragraphby default. You must test to see if it is worth doing, getting it wrong canharm performance. Default IO queue size is 128, higher or lower can be useful,you might try experimenting with echo 1000 >/sys/block/[DEVICE]/queue/nr_requests . Not likely to be useful for singlespinning disk systems, more likely on RAID setups.

Do not set the setting in this paragraphby default. You must test to see if it is worth doing, getting it wrong canharm performance. The VM subsystem dirty ratios can be adjusted from thedefaults of 10 and 20. To set a temporary value for testing maybe use echo5 > /proc/sys/vm/dirty_background_ratio and echo 60 >/proc/sys/vm/dirty_ratio . After proving what works best you can add theseparameters to the /etc/sysctl.conf : vm.dirty_background_ratio = 5vm.dirty_ratio = 60 . Please do follow the instruction to test, itis vital not to just change this and 5 and 60 are just examples.

Tools to monitorvarious parts of a linux system.

LinuxFilesystems

We recommend that you use ext4 mountedwith (rw,noatime,nodiratime,nobarrier,data=ordered) unless ultimate speed isrequired, because ext4 is somewhat easier to work with. If you do not have abattery backed up write caching disk controller you can probably improve yourwrite performance by as much as 50% by using the ext4 option data=journal andthen the MySQL option skip–innodb_doublewrite.The ext4 option provides the protection against torn pages that the doublewritebuffer provides but with less overhead. The benefit with a write cachingcontroller is likely to be minimal.

XFS is likely to be faster than ext4,perhaps for fsync speed, but it is more difficult to work with. Use mountoptions (rw,noatime,nodiratime,nobarrier,logbufs=8,logbsize=32k).

ext3 isn’t too bad but ext4 is better.Avoid ext2, it has significant limits. Best to avoid these two.

NFS in homebrew setups has morereliability problems than NFS in professional SAN or other storage systemswhich works well but may be slower than directly attached SSD or bus-attachedSSD. It’s a balance of features and performance, with SAN performance possiblybeing boosted by large caches and drive arrays. Most common issue is lockedInnoDB log files after a power outage, time or switching log files solves this.Incidence of problems has declined over the last ten years and as of 2016 isnow low. If possible use NFSv4 or later protocol for its improved lockinghandling. If concerned about out of order application of changes, not a problemnormally observed in practice, consider using TCP and hard,intr mount option.

Solaris

Use LD_PRELOAD for one of themulti-threaded oriented mallocs, either mtmalloc or umem.

Use UFS/forcedirectio

Use ZFS.

Windows

To support more connections orconnection rates higher than about 32 per second you may need to setMaxUserPort higher and TcpTimedWaitDelay for TCP/IP, particularly for WindowsServer 2003 and earlier. The defaults are likely to be no more than 4000 portsand TIME_WAIT of 120 seconds. See Settings that can be Modified to Improve NetworkPerformance. Settings of 32768 ports and between 30 and 5 secondstimeout are likely to be appropriate for server usage. The symptom of incorrectsettings is likely to be a sudden failure to connect after the port limit isreached, resuming at a slow rate as the timeout slowly frees ports.

Hardware

Battery-backed write-caching diskcontrollers are useful for all spinning disk setups and also for SSD. SSD aloneis a cheaper way to get faster transaction commits than spinning disks, forlower load systems. Do not trust that the controller disables the hard drivewrite buffers, test with real power outages. You will probably lose data evenwith a battery if the controller has not disabled the hard drive write buffers.

It is best to split files across disktypes in these general groups:

SSD: data, InnoDB undo logs, maybetemporary tables if not using tmpfs or other RAM-based storage for them.

Spinning disks: Binary logs, InnoDB redologs, bulk data. Also, large SATA drives are cheap and useful for working andarchival space as well as the biggest of bulk data sets.

Bus-attached SSD: the tables with the veryhighest change rates i the most highly loaded systems only.

You can put individual InnoDB tables ondifferent drives, allowing use of SSD for fast storage and SATA for bulk.

Hyperthreading on is likely to be a good choice in mostcases. MySQL 5.6 scales up to somewhere in the range of 32-48 coreswith InnoDB and hyperthreading counts as an extra core for this purpose. For5.5 that would be about 16 and before that about 8 cores. If you have morephysical cores either without hyperthreading or more when it is enabled,experiment to determine the optimal number to use for MySQL. There is no fixedanswer because it depends on workload properties.

Thread pool

Use the thread pool if you routinely runwith more than about 128 concurrently active connections. Use it to keep theserver at the optimal number of concurrently running operations, which istypically in the range between 32 and 48 threads on high core count servers inMySQL 5.6. If not using the thread pool, use innodb_thread_concurrency if yousee that your server has trouble with a build-up of queries above about 128 orso concurrently running operations inside InnoDB. InnoDB shows positivescalability up to an optimal number of running jobs, then negative scalabilitybut innodb_thread_concurrency = 0 has lower overhead when that regulatingis not needed, so there is some trade off in throughput stability vs rawperformance. The value for peak throughput depends on the application andhardware. If you see a benchmark that compares MySQL with a thread pool toMySQL without, but which does not set innodb_thread_concurrency, that is anindication that you should not trust the benchmark result: no production 5.6server should be run with thousands of concurrently running threads and nolimit to InnoDB concurrency.

Background

Here are more details of why some ofthese changes should be made.

innodb_stats_persistent = 1

Enables persistent statistics in InnoDB,producing more stable and usually better query optimiser decisions. Verystrongly recommended for all servers. With persistent statistics you should runANALYZE TABLE periodically to update the statistics. Once a week or month isprobably sufficient for tables that have fairly stable or gradually changingsizes. For tables that are small or have very rapidly changing contents morefrequent will be beneficial. There are minimal possible disadvantages, mainlythe need for ANALYZE TABLE sometimes.

innodb_read_io_threads = 16,innodb_write_io_threads = 4

Increases the number of threads used forsome types of InnoDB operation, though not the foreground query processingwork. That can help the server to keep up with heavy workloads. No significantnegative effects for most workloads, though sometimes contention for diskresources between these threads and foreground threads might be an issue ifdisk utilisation is near 100%.

table_open_cache_instances = 16

Improves the speed of operationsinvolving tables at higher concurrency levels, important for reducing thecontention in this area to an insignificant level. No significantdisadvantages. This is unlikely to be a bottleneck until 24 cores are in fulluse but given the lack of cost it is best to set it high enough and never worryabout it.

metadata_locks_hash_instances = 256

Reduces the effects of locking duringthe metadata locking that is used mainly for consistency around DDL. This hasbeen an important bottleneck. As well as the general performance benefit, thehashing algorithm used has been shown to be non-ideal for some situations andthat also makes it desirable to increase this value above the default, toreduce the chance of encountering that issue. We’re addressing that hash alsobut this will still be a useful setting with no significant negatives.

innodb_flush_neighbors = 0

When set to 1 InnoDB will look to flushnearby data pages as an optimisation for spinning disks. That optimisation isharmful for SSDs because it increase the number of writes. Set to 0 data onSSDs, 1 for spinning disks. If mixed, 0 is probably best.

innodb_log_file_size = 2000M

This is a critical setting for workloadsthat do lots of data modification and severe adverse performance will result ifit is set too small. You must check the amount of log space used and ensurethat it never reaches 75%. You must also consider the effect of your adaptiveflushing settings and ensure that the percentage of the log space used does notcause excessive flushing. You can do that by using larger log files or havingadaptive flushing start at a higher percentage. There is a trade off in thissize because the total amount of log file space will usually be cached inoperating system caches due to the nature of the read-modify-write operationsperformed. You must allow for this in the memory budget of the server to ensurethat swapping does not occur. On SSD systems you can significantly extend thelife of the drive by ensuring that this is set to a suitably high value toallow lots of dirty page caching and write combining before pages are flushedto disk.

table_definition_cache

Reduces the need to open tables to getdictionary information about the table structures. If set too low this can havea severe negative performance effect. There is little negative effect for thesize range given on the table definition cache itself. See the PerformanceSchema portion of the memory notes above for critical memory usageconsiderations.

table_open_cache

Reduces the need to open tables toaccess data. If set too low this can have severe negative performance effects.There is little negative effect for the size range given on the table opencache itself. See the Performance Schema portion of the memory notes above forcritical memory usage considerations.

sort_buffer_size = 32k

The key cost here is reduced serverspeed from setting this too high. Many common recommendations to use severalmegabytes or more have been made in a wide range of published sources and theseare harmful for OLTP workloads. that normally benefit most from 32k or othersmall values. Do not set this to significantly larger values such as above 256kunless you see very excessive numbers of Sort_merge_passes – many hundreds orthousands per second on busy servers. Even then, it is far better to adjust thesetting only in the connection of the few queries that will benefit from thelarger size. In cases where it is impossible to adjust settings at the sessionlevel and when the workload is mixed it can be useful to use higher than idealOLTP values to address the needs of the mixture of queries.

Otherobservations

Query cache

The query cache is effectively asingle-threaded bottleneck. It can help performance at low query rates andconcurrency, perhaps up to 4 cores routinely used. Above that it is likely tobecome a serious bottleneck. Leave this off unless you want to test it withyour workload, and have measurements that will tell you if it is helping orhurting. Ensure that Qcache_free_blocks in global status is not above 10,000.5,000 is a good action level. Above these levels the CPU time used in scans ofthe free list can be an issue, check with FLUSH QUERY CACHE, which defragmentsthe free list, the change in CPU use is the cost of the free list size you had.Reducing the size is the most effective way to manage the free list size.Remember that the query cache was designed for sizes of up to a few tens ofmegabytes, if you’re using hundreds of megabytes you should check performancewith great care, it’s well outside of its design limitations. Also check forwaits with:

SELECT EVENT_NAME AS nm, COUNT_STAR AScnt, sum_timer_wait, CONCAT(ROUND( sum_timer_wait / 1000000000000, 2), ‘ s’) ASsec
FROM performance_schema.events_stages_summary_global_by_event_name WHERECOUNT_STAR > 0 ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;

Also see MySQL Query Cache Fragmentation Slows Down the Server(Doc ID 1308051.1).

Sync_binlog andinnodb_flush_log_at_trx_commit

The 1 setting for these causes one fsynceach at every transaction commit in 5.5 and earlier. From 5.6 concurrent commitsupport helps greatly to reduce that but you should still use care with thesesettings. Sync_binlog=1 can be expected to cause perhaps a 20% throughput drop with concurrent commit and30 connections trying to commit, an effect that reduces as actively workingconnections count increases through to a peak throughput at about 100 workingconnections. To check the effect, just set sync_binlog to 0 and observe, thenset innodb_flush_log_at_trx_commit = 0 and observer. Tryinnodb_flush_log_at_trx-commit = 2 also, it has less overhead than 1 and morethan 0. Finally try both at 0. The speed increase from the 0 settings effectwill be greatest on spinning disks with low concurrency and lowest at higherconcurrency on fast SSD or with write caching disk controllers.

Note that it is mandatory to use innodb_flush_log_at_trx_commit=1to get full durability guarantees. Write caching disk controllers with batterbackup are the typical way that full durability combined with low performancepenalty is achieved.

Bugs that affectupgrades and usage for 5.6 compared to 5.5 and earlier

http://bugs.mysql.com/bug.php?id=69174

Innodb_max_dirty_pages_pct iseffectively broken at present, only working when the server has been idle for asecond or more. There are a range of implications:

1. In past versions the limit oninnodb_log_file_size sometimes made it necessary to use this setting to avoidhitting 75% of log space use and having a production disruption incidentdue to hitting async flushing at 75%. The much more gentle flushing batchesfrom innodb_max_dirty_pages_pct were normally acceptable and it wasn’t uncommonfor systems with large buffer pools and high needs to haveinnodb_max_dirty_pages_pct set to values in the 2-5% range just for thisreason. In 5.6 you have two possibilities that should work better:

1a. You can use larger values forinnodb_log_file_size. That will let you use more of your buffer pool for writecombining and reduce total io operations, instead of being forced to do lots ofavoidable ones just to avoid reaching 75% of the log file use. Be sure youallow for the RAM your OS will use for buffering the log files, assume as muchRAM use as the total log file space you set. This should greatly increase thevalue of larger buffer pools for high write load workloads.

1b. You can setinnodb_adaptive_flushing_lwm to avoid reaching 75% of log space use. Thehighest permitted value is 70%, so adaptive flushing will start to increaseflushing rate before the server gets to 75% of the log file use. 70% is a goodsetting for systems with low write rates or very fast disk systems that caneasily handle a burst of writes. For others you should adjust to whatever lowervalue it takes to produce a nice and smooth transition from innodb_io_capacity basedlevel flushing to adaptive flushing. 10% is the default but that is probablytoo low for most production systems, just what we need for a default that hasto handle a wide range of possible cases.

2. You can’t effectively use the normalpractice of gradually reducing innodb_max_dirty_pages_pct before a shutdown, toreduce outage duration. The best workaround at present is to setinnodb_io_capacity to high values so it will cause more flushing.

3. You can’t useinnodb_max_dirty_pages_pct to manage crash recovery time, something it could dowith less disruptive writing than the alternative of letting the server hitasync flushing at 75% of log file space use, after deliberately settinginnodb_log_file_size too low. The workarounds are to use higher than desirableinnodb_io_capacity and smaller than desirable innodb_log_file_size. Both causeunnecessary flushing compared to using innodb_max_dirty_pages_pct for thistask. Before using a too small innodb_log_file_size, experiment withinnodb_io_capacity and innodb_adaptive_flushing_lwm. Also ensure thatinnodb_io_capacity_max is set to around twice innodb_io_capacity, rarely up tofour or more times. This may eliminate the issue with less redundant io thanvery constrained log file sizes because adaptive flushing will increase thewriting rate as the percentage of log space used increases, so you should beable to reach almost any target recovery time limit, though still at the costof more io than using innodb_max_dirty_pages_pct to do it only when a hard capis reached.

4. You can’t useinnodb_max_dirty_pages_pct to effectively regulate the maximum percentage ofdirty pages in the buffer pool, constraining them to a target value. This islikely to be of particular significance during data loading and with well cachedworkloads where you want to control the split between pages used for cachingmodified data and pages used for caching data used purely for reads.

The workaround for this is to regardinnodb_adaptive_flushing_lwm as equivalent to the use of innodb_max_dirty_pages_pctfor normal production and set it to something like 60% with a suitable value ofinnodb_io_capacity for the times when the workload hasn’t reached that amountof log file usage. Start low like 100 and gradually increase so that at mediumload times it just about keeps up. Have innodb_io_capacity_max set to arelatively high value so that as soon as the low water mark is passed, lots ofextra IO will be done to cap the dirty pages/log space use.

You may then be able to reduce the sizeof your InnoDB log files if you find that you don’t reach 60% of log space usewhen you have reached a suitable percentage of dirty pages for the pageread/write balance for your server. If you can you should do this because youcan reallocate the OS RAM used for caching the bigger log files to the InnoDBbuffer pool or other uses.

REFERENCES