[MySQL 8] Performance-impact nieuwe archief-tabel

[gnoe93]

Ik heb een bestaande database voor een website die relatief klein is. Nu wil ik een nieuwe feature toevoegen waarvoor een nieuwe tabel nodig is. Deze tabel moet een archief bijhouden van mogelijks miljoenen records.

Aangezien ik relatief weinig weet over de interne werking van MySQL, stel ik mij de vraag of deze nieuwe tabel een performance-impact zou hebben op de andere tabellen, en of het de moeite is om hier een nieuwe database voor aan te maken.

Er vanuitgaande dat zowel voor de nieuwe tabel/database dezelfde fysieke server gebruikt wordt, ben ik geneigd te denken dat dit geen verschil zou mogen maken. Er gaan geen joins gedaan worden met de andere tabellen, en er gaan ook geen foreign key constraints gebruikt worden. Klopt mijn redenering hier?

[Kalentum]

Ik verwacht het niet. MySQL kan dat prima aan.

Je kan altijd nog testen door het eerst op een testomgeving uit te proberen.

[ Voor 46% gewijzigd door Kalentum op 20-04-2021 13:22 ]

[dev10]

Miljoenen records is voor een databaseserver geen issue. Het ligt er aan wat je allemaal wilt gaan opslaan in die tabel, maar uit je verhaal maak ik op dat er nu nog niet zo veel data is. Ik zou het voor nu gewoon lekker binnen dezelfde database houden. Premature optimization is geen goed plan.

[Standeman]

De omvang van een tabel doet vrijwel niks qua performance, het kost wel diskspace natuurlijk. Perfomance gaat pas spelen wanneer je er queries op af gaat vuren.
De grote vraag is dan wat je software met de tabel gaat doen? Waar moeten de indexen op staan, welke velden worden gebruikt om records te zoeken, zijn er joins, etc.

[drm]

en of het de moeite is om hier een nieuwe database voor aan te maken

Als je hiermee een "database" bedoelt als in `CREATE DATABASE`, dan: nee, dat heeft voor performance geen enkele zin, aangezien een database in MySQL eigenlijk niet veel meer is dan een tabel-prefix, en dus eigenlijk alleen voor namespacing-doeleinden zin heeft. M.a.w., alle performance problemen die je zult krijgen met alle tabellen in 1 db, ga je ook krijgen als je tabellen in verschillende db's zet.

Desondanks kan het wel nuttig zijn, bijvoorbeeld als je de backups van je logs wilt scheiden van je andere data, of bijvoorbeeld regelmatig een snapshot downloadt naar acc/test/dev. Maar zelfs dan: een ALTER TABLE voor het verplaatsen van een tabel naar een andere database (rename to [db].[table]) is, zelfs voor een tabel die in gigabytes loopt qua data en in de miljoenen qua aantal records een hele lichte operatie (subsecond) en dus ook iets wat je altijd later heel makkelijk alsnog kunt doen.

Kortom: laat 't eerst maar een probleem worden voordat je 't oplost

[gnoe93]

Mijn vraag was niet zo zeer of miljoenen records een probleem qua performance is, eerder in welke mate dat invloed qua performance op andere tabellen heeft. Het is (zoals jullie zelf zeggen) makkelijker het binnen dezelfde database te houden.

Als je hiermee een "database" bedoelt als in `CREATE DATABASE`, dan: nee, dat heeft voor performance geen enkele zin, aangezien een database in MySQL eigenlijk niet veel meer is dan een tabel-prefix, en dus eigenlijk alleen voor namespacing-doeleinden zin heeft. M.a.w., alle performance problemen die je zult krijgen met alle tabellen in 1 db, ga je ook krijgen als je tabellen in verschillende db's zet.

Dit beantwoordt grotendeels mijn vraag , al heb ik nog een aantal bijkomende vragen. Kan hetzelfde gezegd worden voor transactions (inserts/updates)? Aangezien een transactie bewerkingen over meerdere tabellen kan bevatten, lijkt het me niet vergezocht te denken dat dit op database niveau gedaan wordt (centrale transaction buffer of dergelijke)? Wat als ik een hele hoop inserts doe in slechts 1 tabel, blijft de performanceimpact dan ook beperkt tot die ene tabel?

En misschien wat meer off-topic, hoe verschillen andere databases als PostgresSQL en SQL Server hierin?

[ Voor 6% gewijzigd door gnoe93 op 20-04-2021 18:05 ]

[drm]

gnoe93:
Dit beantwoordt grotendeels mijn vraag , al heb ik nog een aantal bijkomende vragen. Kan hetzelfde gezegd worden voor transactions (inserts/updates)? Aangezien een transactie bewerkingen over meerdere tabellen kan bevatten, lijkt het me niet vergezocht te denken dat dit op database niveau gedaan wordt (centrale transaction buffer of dergelijke)?

OK, even aangenomen dat je InnoDB gebruikt (die aanname deed ik impliciet eerder al ) - die gebruikt 1 buffer per instance, waarbij ik met 'instance' dus een gescheiden mysqld-proces (=server) bedoel. Dus alle writes en reads hebben altijd (enige) invloed op elkaar (even afgezien van het feit dat ze natuurlijk sowieso invloed hebben op elkaar als ze op dezelfde host draaien). Je zou een andere storage engine kunnen overwegen, maar goed, dan kom je al gauw op "meten is weten" en "premature optimization"-principes uit. Het heeft weinig zin daar een algemene uitspraak over te doen, anders dan: ik zou me er pas druk om maken als het echt een issue lijkt te worden

Wat als ik een hele hoop inserts doe in slechts 1 tabel, blijft de performanceimpact dan ook beperkt tot die ene tabel?

Dat kun je eigenlijk niet goed voorspellen zonder beter de context te kennen en wat je precies als "impact" beschouwt, maar ik kan je wel vertellen dat MySQL heel veel (nee, écht heel veel) writes aankan zonder een merkbare krimp te geven, zeker als het INSERTs zijn, en helemaal als je die ook nog eens in batches doet (omdat je dan meerdere writes in 1 transactie bundelt).

Probeer dat anders gewoon eens te simuleren, dan kom je er vanzelf achter waar je je precies zorgen om moet gaan maken.

En misschien wat meer off-topic, hoe verschillen andere databases als PostgresSQL en SQL Server hierin?

Voor zover ik weet staat in zowel SQL Server als Postgres een database gelijk aan een volledig geïsoleerd proces op de server. In ieder geval is dat in SQL Server het geval. Dat maakt het iets makkelijker om geheugen en CPU over die processen te verdelen op OS-niveau, maar eerlijk gezegd zou ik dan persoonlijk al heel gauw een tweede host overwegen omdat dat de boel veel helderder isoleert.

De "databases" binnen MySQL gedragen zich wat dat betreft meer analoog aan de "schema's" in de andere engines.

[ Voor 0% gewijzigd door drm op 20-04-2021 19:32 . Reden: typo ]

[gnoe93]

Bedankt voor je uitleg, dit heeft me heel wat aangrijpingspunten gegeven om me verder te verdiepen in databases! Het is niet dat ik aan premature optimization wil doen, maar eerder dat ik wil weten wat de achterliggende redenen zijn, zodat ik een geinformeerde beslissing kan maken. Misschien is dat gezien de kleine schaal momenteel overbodig, maar nu weet ik tenminste hoe/wat/waarom.

Als ik het goed begrijp maakt het dus eigenlijk niets uit op dezelfde fysieke server, want dezelfde pool buffer wordt voor alle databases (schemas) gebruikt. En zelfs voor Postgres/SQL server maakt het niet uit, want dan worden de beschikbare resources gewoon verdeeld over meerdere buffers. Tenzij je op OS niveau meer/minder resources aan de verschillende processen begint toe te wijzen natuurlijk.

[pedorus]

Een eigen database doet niet zoveel, in de huidige standaardconfiguratie heb je al aparte bestanden per tabel. Mocht het om veel (>10M) rijen gaan met redelijk wat data per rij, overweeg dan partitions over de tijd. Dat maakt het weggooien van oude data ook makkelijker. (Ik weet trouwens niet zeker of de defaults van mysql inmiddels ook goed zijn, ik heb meer ervaring met mariadb als opvolger daarvan.)

Mocht het om een erg ongelijke workload gaan en die ene tabel de boel omver helpen, dan heb je vaak ook een aparte database-server instantie nodig. (En in een cloud-achtige omgeving is zo'n extra databaseserver toch snel aangemaakt.)

En mocht je echt vele miljarden rijen hebben, overweeg dan of mysql wel de goede keuze is. Op zich gaat zelfs een paar miljard grote rijen ook nog wel, maar het aanpassen van de tabeldefinitie is dan vrij lastig.

Gezien het verhaal vermoed ik dus eigenlijk dat je je geen zorgen hoeft te maken. De juiste indexes daar zal het om gaan (niet te veel, maar net wel genoeg voor queries die vaker voorkomen). Queries die full table scans nodig hebben kunnen vrijwel iedere databaseserver onder load verpesten, naast dat ze traag zijn.

[ACM]

gnoe93 schreef op dinsdag 20 april 2021 @ 21:42:
Als ik het goed begrijp maakt het dus eigenlijk niets uit op dezelfde fysieke server, want dezelfde pool buffer wordt voor alle databases (schemas) gebruikt. En zelfs voor Postgres/SQL server maakt het niet uit, want dan worden de beschikbare resources gewoon verdeeld over meerdere buffers. Tenzij je op OS niveau meer/minder resources aan de verschillende processen begint toe te wijzen natuurlijk.

Meerdere database-instanties op één server draaien betekent sowieso dat ze resources gaan moeten delen. En dat heeft niet per se een positief effect op het geheel ivt één instantie die alle resources kan gebruiken (overigens ook niet per se een negatief effect). Uiteindelijk is er altijd wel ergens een bottleneck, maar welke dat gaat zijn is niet te voorspellen.

Ik zou in ieder geval niet te veel moeite doen voor losse databases op een host, tenzij je voor je archief een ander type database wilt gebruiken dan mysql.

Wat mijns inziens wel belangrijk is, is dat je je tabel de juiste structuur geeft. Het komt wel eens voor dat men een tabel een primary key, een index en een unique key op dezelfde set (en volgorde) kolommen geeft... dan ben je domweg resources aan het verspillen met effectief drie identieke indexes waarvan er twee nooit gebruikt zullen worden.

Kortom, kijk goed naar je indexen en of je kolommen van het juiste type en niet onnodig "breed" zijn (let ook op het verschil tussen char en varchar). Voor kleine tabelletjes maakt dat doorgaans allemaal niet zoveel uit, maar als je miljoenen records gaat wegschrijven kan dat wel flink in je tabelgrootte schelen. En uiteraard daardoor ook in hoeveel 'moeite' het kost om een record weg te schrijven of te lezen.

[BramV]

Zolang je niet verteld of indicatie kan geven wat er in een row komt te staan dan blijft het koffie dik kijken.

Ik denk ook dat, zolang er geen echte query's zijn dat het van weinig invloed zal zijn op de rest van de database.. Als je een select * where id=primary key doen heeft Mysql daar ook geen moeite mee. Die zoekopdracht is dan alleen op de index.

Miljoenen records zijn geen enkel probleem voor Mysql. Maar er zou wel een probleem kunnen onstaan als je een select * doet :-).... Maar in eerste instantie kun je natuurlijk meer geheugen toevoegen voor cache en zo zijn er in eerste instantie nog wel 100 optimalisatie's te verzinnen...

Wat je zou kunnen doen is 'gewoon' een dummy database maken... Een paar miljoen dummy records zijn zo toegevoegd...

[ Voor 21% gewijzigd door BramV op 23-04-2021 18:42 ]