Trage MySQL omgeving Disk issue?

[zonderbloem]

Recentelijk zijn we over gegaan naar een S2D opslag in een Windows Cluster. De configuratie bestaat uit 8x SAS schijven en twee NVMe SSD drives. Op de nieuwe Windows Server draaien we Hyper-V met als guest een CentOS MySQL server.
Er zijn in totaal twee MySQL servers waarvan 1 MySQL op de oude server en 1 op de nieuwe server. De oude server draait op een enkele NVMe drive van Samsung. Bij het importeren van csv bestanden naar de nieuwe database server is een grote vertraging te zien (ongeveer 6x zo traag als de oude server). De oude server/nieuwe server hebben gelijkwaardige memory/cpu.

Via ATTO Disk benchmark & Linux FIO heb ik een aantal testen uitgevoerd.

Oude server MySQL:

FIO test: "fio --randrepeat=1 --ioengine=libaio --direct=1 --gtod_reduce=1 --name=test --filename=random_read_write.fio --bs=4k --iodepth=64 --size=4G --readwrite=randrw --rwmixread=75"
Starting 1 process
Run status group 0 (all jobs):
READ: io=3071.7MB, aggrb=383163KB/s, minb=383163KB/s, maxb=383163KB/s, mint=8209msec, maxt=8209msec
WRITE: io=1024.4MB, aggrb=127776KB/s, minb=127776KB/s, maxb=127776KB/s, mint=8209msec, maxt=8209msec

Disk stats (read/write):
dm-0: ios=773349/257991, merge=0/0, ticks=162360/12796, in_queue=175368, util=96.98%, aggrios=786380/262231, aggrmerge=0/1, aggrticks=164868/12940, aggrin_queue
=177612, aggrutil=95.94%
sda: ios=786380/262231, merge=0/1, ticks=164868/12940, in_queue=177612, util=95.9

Nieuwe server MySQL S2D:

FIO test: "fio --randrepeat=1 --ioengine=libaio --direct=1 --gtod_reduce=1 --name=test --filename=random_read_write.fio --bs=4k --iodepth=64 --size=4G --readwrite=randrw --rwmixread=75"
Starting 1 process
Run status group 0 (all jobs):
READ: bw=114MiB/s (119MB/s), 114MiB/s-114MiB/s (119MB/s-119MB/s), io=3070MiB (3219MB), run=26959-26959msec
WRITE: bw=38.1MiB/s (39.9MB/s), 38.1MiB/s-38.1MiB/s (39.9MB/s-39.9MB/s), io=1026MiB (1076MB), run=26959-26959msec

Disk stats (read/write):
dm-0: ios=784089/262052, merge=0/0, ticks=1083556/509149, in_queue=1595425, util=99.68%, aggrios=785920/262656, aggrmerge=0/0, aggrticks=1085864/510455, aggrin_queue=1596129, aggrutil=99.52%
sda: ios=785920/262656, merge=0/0, ticks=1085864/510455, in_queue=1596129, util=99.52%

Als ik de conclusie kan trekken is de nieuwe server enkel snel op sequential read/writes en niet op kleinere blokken verdeeld over de SAS schijven.

[Voor 10% gewijzigd door zonderbloem op 26-09-2019 15:49]

[DexterDee]

Bij RDBMS databases (en MySQL in het bijzonder) is eigenlijk random IO performance (IOPS) het belangrijkste voor een goede performance. Sequential IO is minder van belang vanwege de (vaak) gefragmenteerde pages in de datafiles.

Ik test random IOPS performance altijd met een extreem simpel tooltje:
https://github.com/chadmayfield/seeker

Meet eens hoeveel random IOPS je op de MySQL data partitie haalt.

[GlowMouse]

Gaat het om dezelfde MySQL-versie en dezelfde configuratie? Wordt de csv-file naar een tabel met een identieke definitie (zelfde indices) geschreven, en gebeurt dat in een enkele transactie?

Om hoeveel data gaat het? Als de NVMe cache van de nieuwe server vol zit, werk je feitelijk met 8 SAS schijven.

Bij RDBMS databases (en MySQL in het bijzonder) is eigenlijk random IO performance (IOPS) het belangrijkste voor een goede performance. Sequential IO is minder van belang vanwege de (vaak) gefragmenteerde pages in de datafiles.

Dat ligt helemaal aan de workload en de configuratie. Als je genoeg geheugen hebt, is de random IO performance nagenoeg irrelevant (leesacties gaan uit RAM, schrijfacties gaan sequentieel in de redolog, en dirty pages worden ook zoveel mogelijk sequentieel weggeschreven).

[zonderbloem]

GlowMouse schreef op donderdag 26 september 2019 @ 16:08:
Gaat het om dezelfde MySQL-versie en dezelfde configuratie? Wordt de csv-file naar een tabel met een identieke definitie (zelfde indices) geschreven, en gebeurt dat in een enkele transactie?

Om hoeveel data gaat het? Als de NVMe cache van de nieuwe server vol zit, werk je feitelijk met 8 SAS schijven.

[...]

Dat ligt helemaal aan de workload en de configuratie. Als je genoeg geheugen hebt, is de random IO performance nagenoeg irrelevant (leesacties gaan uit RAM, schrijfacties gaan sequentieel in de redolog, en dirty pages worden ook zoveel mogelijk sequentieel weggeschreven).

Oude server: MySQL 5.6
Nieuwe server: MySQL 8.0
Verder zijn de tabellen en definities hetzelfde.

[GlowMouse]

Met andere woorden: ik zie dat de SAS-schijven trager zijn, de testomstandigheden zijn niet gelijk, en ik laat belangrijke informatie lekker achterwege. Tja, wat verwacht je eigenlijk van dit topic?

[DexterDee]

GlowMouse schreef op donderdag 26 september 2019 @ 16:08:
Dat ligt helemaal aan de workload en de configuratie. Als je genoeg geheugen hebt, is de random IO performance nagenoeg irrelevant (leesacties gaan uit RAM, schrijfacties gaan sequentieel in de redolog, en dirty pages worden ook zoveel mogelijk sequentieel weggeschreven).

In principe heb je daar helemaal gelijk in en zo is InnoDB ook ontworpen, maar een goed getunede MySQL (of MariaDB in mijn ervaring) met meer dan genoeg geheugen om alle data en indices in geheugen te houden performt onder hoge write en mixed load aanmerkelijk (testbaar) beter met betere random IOPS. En dat met innodb buffer pool sizes en logs op de juiste waarden, plenty vrije pageruimte in de buffer pool, binlog uit, optimale file flush setttings, log files op ramdisk en de cache waardes getuned. Waar dat aan ligt weet ik ook niet precies, maar ik weet wel dat ik het veelvuldig zelf getest heb op grote OLTP workloads.

In mijn ervaring heeft MySQL het meeste baat bij hoog geklokte CPU cores, veel geheugen en een zo hoog mogelijke (random) IOPS. Mis je een van de drie, dan gaat de performance achteruit. Ook bij een in-memory workload (behalve MEMORY tables). Ik heb overigens geen ervaring met de laatste Oracle versies van MySQL, enkel met MariaDB waar mogelijk (performance) verschillen in zitten.