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, Moore's Law said: the hardware operation speed doubles every 18 months, and I/O is no longer the number one 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
By David Lin
2013-06-13
Only those who dare to fail greatly can ever achieve greatly
