MySQL hardware acceleration: Flash storage technology _ MySQL

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