MySQL hardware acceleration: Flash Storage Technology

MySQL hardware acceleration: the Flash storage technology contributes the most to the database bottleneck. I am afraid I/O is the best choice. I/O optimization theories on the market and on the network are all about I/O. With the hardware technology the rapid development of MySQL, I/O is no longer the number one killer of database bottlenecks. It's time for DBAs to change their minds, wake up, don't fall asleep. Big guy, let's take a look at MySQL I/ O analysis ① InnoDB redo & binlog are synchronously written to the underlying disk, small order IO response time requirements <2 ms ② data file write: asynchronous IO, background write, random small IO data file random read: Index query, single block read, sequential reading of random small IO data files: full table scan, multiple reads, large IO response time requirements <5 ms I/O response requirements are still demanding, it is no wonder that the bottleneck in the early stage of low hardware quality comes from I/O, not urgent, in the storage aspect: the sky is loud, Flash is on the stage, and it is popular immediately. I/O (I/O) Flash storage technology overview ① type ● NAND, NOR ● SLC, MLC ② interface type ● SATA ● SAS ● FC ● PCIE ③ manufacturer ● Intel, Fusionio ● STEC, SandForce ● HUAWEI (ii) performance data competition Flash ① IOPS: random read 10000-60000, random write 5000-60000 ② Throughput: continuous read 200 M-600 M, continuous write 200 M-600 M ③ Latency: 30us Disk ① IOPS: random read 160, random write 160 ② Throughput: continuous read 170 M, continuous write 130 M ③ Latency: 6 ms (3) Flash storage principle (1) Flash needs to be erased ● allow direct write, update Not Allowed ● update = read + write + erase ● high erasure cost, 2 ms latency ● The write unit is inconsistent with the erase unit and unit ● The number of erases is limited (2) the Firmware function ● FTL: Physical logical address ing ● Reclamation: reclaim policy, asynchronous erasure ● Wear leveling: balanced write Wear, improved life ● Reserved space: reduced write amplification, improved 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 ② Flash Cache ① Writeback & writethrough ② to accelerate read performance, suitable for website OLTP ③ pay attention to memory consumption (v) database I/O adjustment (1) MySQL parameter innodb_flush_log_at_trx_commit innodb_flush_method innodb_io_capacity (2) Block devices, file system Linux IO policy NOOP, CFQ, DEADLINE/proc/sys/vm/swappiness mount-o noatime, nodiratime, barrier = 0 (3) RAID card Cache Control BBWC enable pre-read cache, write cache writeback