MySQL hardware acceleration: Flash storage technology bitsCN.com
MySQL hardware acceleration: Flash storage technology
I am afraid I/O has contributed the most to database bottlenecks,
I/O is the best practice for the optimization theory on the market and on the network.
However, with the rapid development of hardware technology, I/O is no longer the top killer of database bottlenecks.
It's time for the well-regulated DBAs to change their minds. don't fall asleep.
Look at MySQL I/O analysis.
① InnoDB redo & binlog
Write data to the underlying disk synchronously, with a small IO
IO response time requirement <2 ms
② Data Files
Data File write: asynchronous IO, background write, random small IO
Random read of data files: index query, single read, random small IO
Sequential read of data files: Full table scan, multiple reads, continuous large IO
IO response time requirement <5 ms
I/O response requirements are still demanding. it is no wonder that the bottleneck comes from I/O when the hardware quality is low in the early days.
However, do not worry, in terms of storage, the sky is a loud bang, Flash debut, immediately swept the IT industry, blood wash I/O
(I) Flash storage technology overview
① Type
● NAND, NOR
● SLC and MLC
② Interface type
● SATA
● SAS
● FC
● PCIE
③ Manufacturer
● Intel and Fusionio
● STEC and SandForce
● HUAWEI
(Ii) performance data competition
Flash
① IOPS: random read 10000-60000, random write 5000-60000
② Throughput: continuous read of 200 M-600 M, continuous write of 200 M-600 M
③ Latency: 30us
Disk
① IOPS: random read 160, random write 160
② Throughput: continuous read of 170 M, continuous write of 130 M
③ Latency: 6 ms
(Iii) Flash storage principles
(1) Flash needs to be erased
● Allow direct writing and update not allowed
● Update = Read + WRITE + erase
● High erasure cost, 2 ms latency
● The Write unit is inconsistent with the erased unit and unit
● The number of erases is limited.
(2) Firmware functions
● FTL: physical logical address ing
● Reclamation: reclaim policy, asynchronous erasure
● Wear leveling: balanced write Wear to improve service life
● Reserved space: reduces write amplification and improves write performance
(Iv) use of Flash technology
(1) Flash Disk
① High IOPS, low capacity
② Avoid excessive page erasure and write amplification
③ Flash is not suitable for storing redo
(2) Flash Cache
① Writeback & writethrough
② Accelerated reading performance, suitable for website-type OLTP
③ Pay attention to memory consumption
(V) database I/O adjustment
(1) MySQL parameters
Innodb_flush_log_at_trx_commit
Innodb_flush_method
Innodb_io_capacity
(2) block device, file system
Linux IO policy NOOP, CFQ, DEADLINE
/Proc/sys/vm/swappiness
Mount-o noatime, nodiratime, barrier = 0
(3) RAID card cache control
BBWC enabled
Pre-read cache and write cache writeback
BitsCN.com