MySQL database optimization practice
Recently, we have compiled some of Percona, Linux, Flashcache, hardware optimized experience for everyone to share:
Hardware
1 open BBWC
RAID card has a write cache (Battery Backed Write Cache), write cache for improved IO performance is very obvious, because the power-down will lose data, it must be supported by the battery. Battery charge and discharge regularly, usually for 90 days or so, when that power is below a certain threshold, will write from a writeback cache policy set to writethrough, the equivalent of write cache is invalidated, then if the system has a large number of IO operations could clearly feel the IO slow response. Currently, the new built-in flash memory RAID card, power down the data will be written to the flash write cache, so you can ensure that data never lost, but still needs the support of the battery.
There are two solutions: an artificial trigger discharge, you can choose to do business when low, to reduce the impact of the application; (2) Set the write cache policy for the force write back, even if the batteries fail, but also maintain the write cache policy of writeback, so there is risk of data loss after power failure.
Currently, there are some hardware manufacturers to provide a capacitor-powered RAID card, no battery charge and discharge of the problem, contact your hardware vendor.
2.RAID card configuration
Close read cache: RAID card on the limited capacity of the cache, we choose to read data direct way to ignore read cache.
Closed pre-reading: RAID card read-ahead function for random IO is almost no increase, so will read-off.
Turn off disk cache: In general, if you use RAID, the system will be turned off by default disk cache, the command can also be used to force shut down.
These settings are available through the command-line RAID card to complete, such as the LSI chip RAID card megacli command.
3 on Fastpath functionality
Fastpath is LSI's new features, the RAID controller is optimized for the SSD to do, use the fastpath features can maximize the capacity of the SSD. If you do use the RAID SSD approach can open the fastpath function. About fastpath features, you can download data from the LSI's official website, and consult your hardware provider.
4.Fusionio parameter adjustment
Basically, Fusionio need to make any adjustments, the following three parameters may improve performance:
options iomemory-vsl use_workqueue = 0
For fusionio equipment, ignoring Linux IO scheduling, equivalent to using NOOP.
options iomemory-vsl disable-msi = 0
Open the MSI interrupt, if the device supports, then open.
options iomemory-vsl use_large_pcie_rx_buffer = 1
Open Large PCIE buffer, may improve performance.
Operating system
1.IO scheduling
There are four Linux IO scheduling: CFQ, Deadline, Anticipatory, and NOOP, CFQ the default IO scheduler algorithm. Completely random access environment, CFQ and Deadline, NOOP performance difference is small, but once large contiguous IO, CFQ IO may cause a small increase in response time delay, so the proposed changes to the database environment for the deadline algorithm, performance is more stable. Our deadline algorithm using a uniform environment.
Disk IO scheduling algorithm is based on the design, so reducing head movement and is one of the most important factor to consider, but the use of Flash storage device, no need to consider the problem of head movement, you can use the NOOP algorithm. NOOP means that NonOperation, means that the IO will not do any optimization, in full accordance with the request to the FIFO method to deal with IO.
Reduce the read-ahead: / sys / block / sdb / queue / read_ahead_kb, default 128, adjusted to 16
Increasing queue: / sys / block / sdb / queue / nr_requests, default 128, adjusted to 512
2.NUMA set
Stand-alone single-instance, the proposed closure of NUMA, closed in three ways: 1 hardware layer, set off in the BIOS; 2.OS kernel, boot settings numa = off; 3. Numactl command can be modified to interleave memory allocation strategy (cross), some hardware can be set in the BIOS.
Single multi-instance, see: http://www.hellodb.net/2011/06/mysql_multi_instance.html
3 file system settings
We use the XFS file system, XFS has two settings: su (stripe size) and sw (stirpe width), according to the hardware level RAID to set these two parameters, such as the 10 disk to do RAID10, a stripe size of 64K, XFS Set su = 64K, sw = 10.
xfs mount parameters: defaults, rw, noatime, nodiratime, noikeep, nobarrier, allocsize = 8M, attr2, largeio, inode64, swalloc
Database
1.Flashcache parameters
Creating flashcache: flashcache_create-b 4k cachedev / dev / sdc / dev / sdb
The block size specified flashcache Percona the page the same size.
Flashcache parameters:
flashcache.fast_remove = 1: Open the fast remove features, turn off the machine, without the cache of dirty blocks written to disk.
flashcache.reclaim_policy = 1: Brush out the dirty block strategy, 0: FIFO, 1: LRU.
flashcache.dirty_thresh_pct = 90: flashcache each hash set on a dirty block on the threshold.
flashcache.cache_all = 1: cache all the content, you can use blacklist filtering.
flashecache.write_merge = 1: Open the Write Combining to improve disk write performance.
2.Percona parameters
innodb_page_size: If fusionio, 4K the best performance; using SAS disks, set to 8K. If a lot of full table scan, you can set to 16K. Smaller page size, can improve the cache hit rate.
innodb_adaptive_checkpoint: If fusionio, set to 3, increase the refresh rate to 0.1 seconds; using SAS disks, set to 2, using the estimate dirty way to refresh the page.
innodb_io_capacity: IOPS capability to set based on the use fuionio can set more than 10,000.
innodb_flush_neighbor_pages = 0: for fusionio or SSD, because random IO is good enough, so turn off this feature.
innodb_flush_method = ALL_O_DIRECT: the public version of the MySQL database file to read and write can only be set to DirectIO, for Percona log and data files can be set to direct mode read and write. But I'm not sure the impact of this parameter for innodb_flush_log_at_trx_commit,
innodb_read_io_threads = 1: Set pre-reading the thread is set to 1, because the linear effect of pre-reading is not obvious, so no need to set more.
innodb_write_io_threads = 16: write the number of threads set to 16, to enhance the ability to write.
innodb_fast_checksum = 1: Open the Fast checksum feature.
Monitor
1.fusionio monitoring: fio-status command
Media status: Healthy; Reserves: 100.00%, warn at 10.00%
Thresholds: write-reduced: 96.00%, read-only: 94.00%
Lifetime data volumes:
Logical bytes written: 2,664,888,862,208
Logical bytes read: 171,877,629,608,448
Physical bytes written: 27,665,550,363,560
Physical bytes read: 223,382,659,085,448
2.flashcache monitoring: dmsetup status
read hit percent (99)
write hit percent (51)
dirty write hit percent (44)
-EOF-
Related Posts of MySQL database optimization practice
-
Explain the role of Hibernate3 package
hibernate3.jar: Hibernate library, there is nothing to be said, must use the jar package cglib-asm.jar: CGLIB library, Hibernate use it to achieve PO dynamic byte code generation, is the core of the library, you must use the jar package dom4j.jar: dom4j
-
Basic knowledge of learning
Learning to learn Java first line j2se To learn j2ee must first learn j2se, just beginning to learn is not recommended to use j2se first IDE, then gradually transition to the use of the IDE development, after all, why use it conveniently. J2se study recom
-
Js select Move on, down, top, home at the end of
Select the web search function to sort the code, there are a lot of, can really achieve very little set-top functions, not function not achieved, that is, the speed of Odd slow great , of course, may be its own search capability does not pass, they t ...
-
Distributed version management software mercurial
Just heard today Distributed version management software, mercurial, to learn about. I usually work in the windows, the git, mercurial these must pass an order to complete the instrument, and some inconsistent. But now, mercurial situation has improv ...
-
javascript to call the father of the neutron-type category was covered by Ways
When the sub-class methods and the same name, superclass, the subclass method overrides the superclass method of the same name. This is equivalent to the method of rewriting. Ways rewritten at the time, mainly in order to extend the functionality, ra ...
-
rails 2.2.2 Medium ym4r of bug fixes
Today at rails2.2.2 use ym4r plug-ins have the following error when Surf the Internet at , there are two solutions: 1. Modification "vendor/plugins/ym4r_gm/gm_plugin/map.rb" document, around 35 or 36 lines or so, will in Amended to , can be ...
-
Analysis of rails architecture
After a request, first of all are ActionController. ActionController are rails control center, which itself is a module, module which has a base class, all of the controller, including all ApplicationController inheritance base. base of the main func ...
-
Who is in your heart the best AJAX framework
Reproduced source: http://subject.csdn.net/ajaxframework.htm Quotations There is no doubt, Ajax as one of the most popular technology, the best framework for an endless stream. Prototype, Dwr, Dojo, JQuery, YUi ... ... are very good products. Introdu ...
-
Hibernate's FetchSize and MaxReslut
Previously EJB to do paging, EJBQL only provide FirstResult and MaxReslut, recently discovered by Hibernate its HQL also provides FetchSize, checked under the API found almost mean, and then found the next line so someone said: setFetchSize is an opt ...
-
Msxml2.XMLHTTP version problem
Projects with an import feature prototype.js of Ajax functionality to update the prompt, the code is very simple, do not have the framework of the background on a jsp to output Text, future use of timers and to update the page Ajax.request encountere ...