Samstag, 28. Januar 2017

PCI/SSD/NVM performance challenge begins! PART-I ( This is not a a regular post, it is more or less "Thinking loudly" or blogly? )

Assuming we are trying to build a system with maximum bandwidth for the MySQL applications.
Our Databases are not optimized for the normal usage so we have lot of full table scans. Some of the tables are really huge- multi-terrabyte monster databases.

In the sharded mode we see ~600MB/s load on the single shard. The single node has a local 8x4TB- SATA HGST enterprise disks on RAID6 with LSI-9271(BBU included)  with host memory 65GB RAM.

Let us begin with some optimizations on the single node.
On the single node we following options:
1) Boost the RAM?-actually it make no sense if we don't benefit of MySQL cache
2) speedup CPU?- currently most of the load is IO based not the calculations in MySQL.
3) Speed up RAID/DISK IO?- looks good lets us start here.

Theory

The single spinning disk can bring about 100-120MB/s in streaming mode.

The PCI-E3 Total Bandwidth: (x16 link): 32GB/s (or 8.0GT/s,or 1000MB/s per lane )

Single DB file test on XFS shows only about 563MB/s

dd if=/store/TestDatabase.MYD bs=4k | /tmp/pv >/dev/null
 205GB 0:06:13 [ 562MB/s] [                                                <=>                                             ]53674008+1 records in
53674008+1 records out

219848739110 bytes (220 GB) copied, 373.182 s, 589 MB/s
For full table scan queries ~373s is quite a bug performance penalty.
This is probably due to the RAID-6 with 8 Disks. This cannot be bigger than (8-2)*100MB/s~600MB/s .

Also we are not benefiting from Cache-Vault from controller or single disk(64MB).
Some note that the files are not fragmented:
 filefrag /store/TestDatabase.MYD
/store/TestDatabase.MYD: 1 extent found

 The full table scans are evil :)

 What about boosting FS for speedup?

XFS vs ZFS- maybe NVMe-cache ?

Keine Kommentare: