MySQL 8.0 bijzonder traag

[vmsw]

Mijn vraag
Sinds de upgrade van MySQL 5.7 naar 8.0 is MySQL bijzonder traag met het maken van backups. Hoe kan ik dit oplossen?

Relevante software en hardware die ik gebruik
- TransIP VPS
- 2 cores (ik snap: beperkt, maar met MySQL 5.7 ging dit prima)
- 4GB geheugen
- Ubuntu 16.04.5 LTS
- mysql-server 8.0.12
- mysqldump 8.0.12

Server functioneert als slave en heeft géén (regulier) MySQL-verkeer.

Wat ik al gevonden of geprobeerd heb
- Backupscript & MySQL-replicatie uitschakelen: vrijwel 0 belasting (dus hij doet normaal 'niks' zoals verwacht)
- MySQL-replicatie uitschakelen: geen (significant) verschil met back-up snelheid.
- mysqldump (onderdeel van back-up script) los gebruiken: 1 core is in dan 100% bezig en de response volgt, maar traag. Bottleneck lijkt CPU dus.
- Wat (minder gestructureerd) wijzigen van MySQL-instellingen, zonder significante gevolgen. De instellingen waren in beginsel overigens vergelijkbaar en niet perse heel uitgebreid.
- Getest op meerdere momenten op de dag: het is consequent en geldt voor (alle) databases: groot en klein.

- Als ik tegelijkertijd een mysql-client sessie open heb staan, en regelmatig show full processes aanroep, krijg ik vaak te zien "checking permissions"
- User-tabel bevat 57 gebruikers
- Er zijn geen tabel-specifieke rechten, alleen de (nieuwe / automatisch aangemaakte) MySQL 8.0 rollen en (voor sommige users, maar niet de user waar het nu om gaat) rechten op een specifieke database

Praktijkwaarden
Huidige waarden:

code:

time mysqldump <database van 500mb> > /dev/null

real    1m21.048s
user    0m6.340s
sys     0m0.612s

Op identieke server, maar dan MySQL 5.7.23:

code:

time mysqldump <sterk vergelijkbare database van 500mb> > /dev/null

real    0m38.420s
user    0m4.724s
sys     0m0.692s

Om een gevoel van de permissies te geven, dit zou uitsluitend het volgende moeten zijn:

code:

mysql> show grants for 'gebruikersnaam'@'localhost';
+------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for gebruikersnaam@localhost                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, SHOW DATABASES, SUPER, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO `gebruikersnaam`@`localhost` |
| GRANT BACKUP_ADMIN,RESOURCE_GROUP_ADMIN,XA_RECOVER_ADMIN ON *.* TO `gebruikersnaam`@`localhost`                                                |
+------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

[ Voor 21% gewijzigd door vmsw op 01-08-2018 12:07 ]

[Hero of Time]

Heb je ook alle stappen ondernomen die gepaard gaan met een upgrade van de server software? Simpelweg 'apt update mysql' is niet voldoende, er zijn ook wat database interne stappen die je moet ondernemen. Staat als het goed is allemaal beschreven op de site.

Dan moet je ook nog denken aan zaken als Spectre/Meltdown mitigation. Heeft je Mysql 5.7 daar updates voor in zitten? Dat heeft namelijk ook een negatieve invloed op de performance.

Als laatste vraag ik mij af waarom je met Mysql verder bent gegaan. Voor zover ik weet is MariaDB de standaard in Ubuntu 16.04. Als je naar Mysql 8.0 gaat, zal je dat vast via een aparte repositorie hebben gedaan. Je kan daarmee net zo goed naar MariaDB gaan.

Om de verschillen wat makkelijker te testen kan je natuurlijk ook op je eigen lokale systeem een VM opzetten en daar je backup in laden om daar weer een dump van te maken. En zo kijken wat de verschillende versies doen. Kale installatie van Ubuntu 16.04, daar maak je een snapshot van (met je backup bestand erin), dan installeer je de standaard Mysql 5.7 en test je de performance. Terug naar punt van het maken van de snapshot en dan Mysql 8.0 testen. Als laatste herhaal je het met MariaDB. Time dan zowel het inladen van de dump als het opnieuw maken ervan. Let ook goed op schijfgebruik. Als je veel IOWAIT hebt, is het niet de CPU wat de beperkende factor is.

[vmsw]

Hero of Time schreef op woensdag 1 augustus 2018 @ 12:07:
Heb je ook alle stappen ondernomen die gepaard gaan met een upgrade van de server software?

Zover mij bekend wel. Ik heb dit gevolgd (dus o.a. diverse checks, het verwijderen van 2 niet meer bestaande sql_modus-opties, default-authenticatie op mysql_native_password zetten omdat sommige client-software benodigde libraries mist. Mijn inziens geen spectaculaire wijzigingen hoeven doen.)

Hero of Time schreef op woensdag 1 augustus 2018 @ 12:07:
Dan moet je ook nog denken aan zaken als Spectre/Meltdown mitigation. Heeft je Mysql 5.7 daar updates voor in zitten?

Behalve reguliere updates geen specifieke wijzigingen gedaan. Naast MySQL-package zijn er géén andere updates geweest. Overigens vermoed ik dat zo'n patch niet dermate ingrijpend is (?)

Hero of Time schreef op woensdag 1 augustus 2018 @ 12:07:
Als laatste vraag ik mij af waarom je met Mysql verder bent gegaan.

Ik volg je, maar lijkt me (voor nu) even andere discussie

Hero of Time schreef op woensdag 1 augustus 2018 @ 12:07:
Om de verschillen wat makkelijker te testen kan je natuurlijk ook op je eigen lokale systeem een VM opzetten en daar je backup in laden om daar weer een dump van te maken.

Ik heb 4 exact gelijke VM's waar ik snapshots heen/terug op zet. Ik heb op basis hiervan deze tests gedraaid. MariaDB heb ik niet getest (lijkt me irrelevant). iowait-aandeel is aanwezig (niet alle databases kunnen tegelijk in geheugen, maar is beperkt: schommelt tussen 1 en 4%)

[ Voor 7% gewijzigd door vmsw op 01-08-2018 12:25 ]

[Hero of Time]

vmsw schreef op woensdag 1 augustus 2018 @ 12:19:
[...]

Zover mij bekend wel. Ik heb dit gevolgd (dus o.a. diverse checks, het verwijderen van 2 niet meer bestaande sql_modus-opties, default-authenticatie op mysql_native_password zetten omdat sommige client-software benodigde libraries mist. Mijn inziens geen spectaculaire wijzigingen hoeven doen.)

Top. En toch kan er dus iets zijn wat ogenschijnlijk weinig doet, maar grote gevolgen kan hebben. Maar we laten dit even voor wat het is.

[...]

Behalve reguliere updates geen specifieke wijzigingen gedaan. Naast MySQL-package zijn er géén andere updates geweest. Overigens vermoed ik dat zo'n patch niet dermate ingrijpend is (?)

Databases krijgen een redelijke performance hit te verduren hierdoor. De kernel van Ubuntu 16.04 zal de update wel hebben, maar ook software zelf kan patches krijgen hiervoor. Dit vind je terug in de changelog van het package (versienummer zegt niet alles, sommige zaken worden gebackport namelijk, versienummer verandert dan feitelijk niet, maar een bug wordt wel opgelost).

Er zijn benchmarks beschikbaar om te zien wat voor impact de CPU bug patches hebben. Hier is Postgresql bijvoorbeeld redelijk hard door geraakt, bij bepaalde acties. Performance impact is in dat soort gevallen tot zelfs 30% lager. Dit was echter alleen met de kernel fixes, niet in Postgresql zelf (en weet ook niet of die er iets mee hebben gedaan).

[...]

Ik volg je, maar lijkt me (voor nu) even andere discussie

[...]

Ik heb 4 exact gelijke VM's waar ik snapshots heen/terug op zet. Ik heb op basis hiervan deze tests gedraaid. MariaDB heb ik niet getest (lijkt me irrelevant). iowait-aandeel is aanwezig (niet alle databases kunnen tegelijk in geheugen, maar is beperkt: schommelt tussen 1 en 4%)

Het lijkt mij wel relevant om MariaDB te testen. Want dan weet je of de geestelijke voortzetting van de oorspronkelijke ontwikkelaars het wel of niet heeft en of het dus interessant is om alsnog over te stappen.

Dat de IO wait zo beperkt is, lijkt er idd op dat het geen issue is met de schijf. Dan moet er onder water, in de database of software zelf, wat zijn verandert qua werking.

Was er een reden om naar Mysql 8.0 te gaan ipv bij 5.7 te blijven?

[HollowGamer]

@vmsw Ik wilde tochnog even reageren, want ik heb gelukkig geen enkele problemen ervaren met de upgrade, enkel dan het volgende commando uitvoeren:

code:

mysql_upgrade --password='..' upgrade-system-tables

Zie Google voor het oplossen van de mysql native password authenticatie.

@Hero of Time MariaDB heeft nog altijd veel functies niet en mist veel op noSQL gebied, bijvoorbeeld native JSON zit er pas recentelijk in (en nog altijd niet volledig). Tevens kiezen veel ontwikkelaars enkel MySQL te ondersteunen (o.a. Laravel en Nextcloud), helaas is het dus niet zo eenvoudig te kiezen voor MariaDB uit eigen ervaring.

[Kees]

/laat: Zet ssl eens uit op je mysqldump (--ssl-mode=DISABLED).

[ Voor 5% gewijzigd door Kees op 19-08-2018 15:33 ]