PFZ.nl - PHP Community: MySQL "drop index" versnellen? - Databases - Forum - PFZ.nl - PHP Community

Op een tabel van 35GB zijn een groot aantal dubbele en overbodige indexen aangemaakt. Vraag me niet waarom, maar dat is ooit zo gedaan. Nu wil ik met een DROP INDEX de diverse overbodige indexen verwijderen, maar dat levert het volgende resultaat op:
- MySQL gaat een nieuwe datafile aanmaken (betreft MyISAM, dus tabel.MYD) van 35GB
- MySQL gaat een nieuwe indexfile aanmaken, het tabel.MYI bestand, van minmaal 30GB (laatste stand van zaken)
- MySQL schrijft vreselijk langzaam op een razendsnel SAN (800MB per seconde is mogelijk, MySQL doet "pieken" van maximaal 20MB/s)

Is er een mogelijkheid om dit proces een factor 1000 te versnellen? Na drie uur wachten hield ik het voor gezien, maandag is er weer een dag.

Het is duidelijk dat de keuze voor één bestand met alle indexen niet handig is in deze opzet, maar waarom in hemelsnaam een nieuw databestand aanmaken? En hoe kan ik de schrijfsnelheid van MySQL aanzienlijk verbeteren? PostgreSQL en MySQL draaien op exact dezelfde hardware met ieder zijn eigen dedicated SAN, en dezelfde RHEL 5.4 configuratie. Omzetten van MyISAM naar innoDB is geen optie, dat maakt de boel nóg veel langzamer. Voor een datawarehouse is de betrouwbaarheid van innoDB ook niet zo'n issue, er zijn backups beschikbaar voor het geval dat.

PostgreSQL doet er op dezelfde hardware enkele seconden over om een index te droppen van dezelfde omvang, voor grap nog even getest. Het schrijven met PostgreSQL gaat ook met 400/500 MB's, richting het maximaal haalbare op deze hardware.

MySQL MyISAM staat gelijk aan een tekstbestand.
Hij slaat met met gemak 35GB in één enkel bestand op, welke idioot ooit dacht dat dit een goed idee was? Als je moet één record aanpassen, word het hele bestand gelockt.

Met uitzondering van Flatfile databases doet niemand dit, juist omdat het langzaam is.
Waarom MySQL de hele datafile gaat herbouwen terwijl alleen zijn idx bestand moet aanpassen?? Geen idee, kennelijk is dat sneller dan één bestand aanpassen.

Weer een puntje voor De Lijst Vincent?
http://www.yapf.net/...L_op_een_rijtje

Edit: http://www.yapf.net/...alle_versies.29 Het is al bekend!

MySQL's ALTER TABLE is ee ramp, dat is het altijd al geweest. Eerlijk gezegd wist ik niet dat het zo erg was.
Je zou verwachten dat MySQL het datafile kopieert en tijdens het kopieren de bewust index overslaat.

Wat er echt gebeurt is gewoon eng:

http://lists.mysql.com/mysql/202489

Daar staat ook dat mysql tijdelijke tabellen maakt, kan het zijn dat hij dat op jouw systeem op een lokale disk doet ipv de SAN?

Anyhoes, de oplossing die in het artikel wordt genoemd is om een lege tabel te maken volgens dezelfde structuur, daar de indexes op aan te passen en dan de data te kopieren.

Op 28-01-2012 09:18 schreef PgVincent www.yapf.net:

MySQL's ALTER TABLE is ee ramp, dat is het altijd al geweest. Eerlijk gezegd wist ik niet dat het zo erg was.
Je zou verwachten dat MySQL het datafile kopieert en tijdens het kopieren de bewust index overslaat.

Je bedoelt vast dat de datafile wordt overgeslagen, daar verandert niets aan, en dat alleen een nieuwe indexfile wordt gemaakt.

Quote

Daar staat ook dat mysql tijdelijke tabellen maakt, kan het zijn dat hij dat op jouw systeem op een lokale disk doet ipv de SAN?

Dat gebeurt op het SAN, ik zag daar 3 nieuwe bestanden ontstaan/groeien: .frm + .MYD + .MYI

Quote

Anyhoes, de oplossing die in het artikel wordt genoemd is om een lege tabel te maken volgens dezelfde structuur, daar de indexes op aan te passen en dan de data te kopieren.

Misschien dat het wat helpt, maar MySQL blijft gewoon vreselijk langzaam schijven. We hebben niet voor niets een dedicated SAN wat tot 800MB/s kan schrijven! Dat kost een paar stuivers, maar met MySQL is het gewoon weggegooid geld. Is hier niks aan te doen?

PostgreSQL gaat als de brandweer, dat is gewoon niet te vergelijken. Onze afdeling lacht zich nu slap om deze situatie, de anderen hadden MySQL gekozen vanwege de snelheid...

Bekende gedachte :) 'De database is langzaam', gooi er een buts hardware tegenaan en hoop dat het werkt.

snelheid is relatief, ja het word snel ingevoerd.
Maar ga het dan is uitlezen en aanpassen! Dan breekt de pl*res uit en lijkt het allemaal niet zo goed te werken.

Tweakers werkt met MySQL, ja maar dan wel InnoDB. MyISAM is een ramp...

Quote

Je bedoelt vast dat de datafile wordt overgeslagen, daar verandert niets aan, en dat alleen een nieuwe indexfile wordt gemaakt.

Nee, alles.

http://dev.mysql.com...lter-table.html
"In most cases, ALTER TABLE makes a temporary copy of the original table. "
...
"For some operations, an in-place ALTER TABLE is possible that does not require a temporary table:"
...
"Adding or dropping an index, for InnoDB (if InnoDB Plugin is used) and NDB."

Dus voor DROP INDEX op MyISAM gaat hij je hele 35GB aan data kopieren naar een tijdelijke tabel.

Quote

Misschien dat het wat helpt, maar MySQL blijft gewoon vreselijk langzaam schijven. We hebben niet voor niets een dedicated SAN wat tot 800MB/s kan schrijven! Dat kost een paar stuivers, maar met MySQL is het gewoon weggegooid geld. Is hier niks aan te doen?

Ik kan me zomaar voorstellen dat het herbouwen van de indexes op die server niet sneller kan dan 20MB/s, dus test eerst nog even hoe snel je b.v. een indexloze tabel van 1GB kunt kopieren.

Hoe zit de MySQL server aan de SAN gepeuterd, kan die verbinding de volle snelheid wel aan?

Op 28-01-2012 10:35 schreef PgVincent www.yapf.net:

Ik kan me zomaar voorstellen dat het herbouwen van de indexes op die server niet sneller kan dan 20MB/s, dus test eerst nog even hoe snel je b.v. een indexloze tabel van 1GB kunt kopieren.

Een file van 1GB is binnen 2 seconden gekopieerd, is eigenlijk te klein om meetbaar te zijn. Met files van 100GB (wat dus ook groter is dan de cache, 1GB past in cache) zien we pieken van 800MB/s. Wanneer je MySQL laat kopieeren, dan gaat het langzaam, met 10 tot 20MB per seconde. Je ziet dan ook ineens veel IOwait in IOstat, wat duidt op extreem veel random IO. En dat is raar, er zou nauwelijks random IO nodig hoeven zijn! Ik heb de tabel met dezelfde structuur en data in PostgreSQL gezet, was in enkele minuten klaar en geen IOwait te zien. Het lijkt erop dat PostgreSQL veel minder capaciteit vraagt van de hardware, veel beter met de resources omgaat. Daarnaast kun je met PostgreSQL per tabel/index/of wat dan ook bepalen op welke tablespace deze moet worden weggeschreven, waardoor IO problemen zijn te voorkomen en/of verhelpen.

Quote

Hoe zit de MySQL server aan de SAN gepeuterd, kan die verbinding de volle snelheid wel aan?

Dat is 2x HP 82Q 8Gb Dual Port, dat zit wel goed: met PostgreSQL werkt het tenslotte ook. Het zijn twee exact dezelfde servers met ieder exact hetzelfde SAN, zelfde OS, etc. etc. Het enige verschil is dat op de ene machine MySQL 5.1 draait en op de andere PostgreSQL 9.1 Ja, MySQL is wat ouder, maar ook de oude PostgreSQL versie 8.3 ging al veel sneller. Een upgrade van MySQL versie 5.1 naar versie 5.5 durft men niet aan, men is bang dat dit vééél te lang gaat duren en dan nog steeds niet 1000x sneller is t.o.v. de huidige situatie. En dat is wel wat men zoekt, wij hebben deze performance ook. Ze zitten er over te denken om ook over te stappen op PostgreSQL, dat werkt tenminste en met 2x dezelfde opzet is het beheer ook wat simpeler. Daarnaast krijgen ze met PostgreSQL veel meer functionaliteit en kunnen er ook complexe queries worden uitgevoerd. Ik heb het ze al aangeraden, MySQL is gewoon een hoofdpijnverhaal.

Mocht iemand nog een tip hebben voor een betere configuratie van MySQL om beter gebruik te maken van de beschikbare IO capactiteit, dan hoor ik het graag!

PostgreSQL werkt met zogenaamde pagefiles van maximaal 8k, waarmee de één record is opgedeeld in verschillende kleinere bestanden. Het voordeel is dat verwijderen veel sneller gaat dan één groot bestand aanpassen. Daarnaast wordt wanneer mogelijk gebruik gemaakt van compressie.
http://momjian.us/ma...January_20_2012 (TOAST Queries)

Plus, PostgreSQL is volledig ACID compliant waardoor er geen volledig table lock hoeft plaats te vinden om één record aan te passen. Bij MySQL is dit laatste afhankelijk van de storage engine.

Als je één groot bestand moet aanpassen krijg al snel te maken fragmentatie van de data, het lijkt misschien één bestand maar in werkelijkheid staat het verstrooid over de hele disk of nog leuker, disks (RAID 0). Dus om het bestand uit te lezen moet hij de hele disk afzoeken naar ieder stukje data!

Met meerdere kleine bestanden van 8kb heb je veel grotere kans dat één bestand minstens in het bestandscluster past en dus niet verdeeld is over de disk. Je hebt dus veel minder random reads voor één record.

Quote

En dat is raar, er zou nauwelijks random IO nodig hoeven zijn!

MySQL gaat indexes herbouwen en dus records lezen, stukje index bouwen, stukje records lezen, stukje index schrijven, het lijkt mij eigenlijk alleen maar logisch dat dat gepaard gaat met heel veel random IO.

Kort zoeken op het performanceblog leert dat dit waarschijnlijk gewon een bekent probleem van MYSQL+SAN is:
http://www.mysqlperf...san-with-mysql/

MySQL kan read/write operaties niet altijd groeperen. SAN is blijkbaar gewoon niet de oplossing voor MySQL.

Quote

PostgreSQL werkt met zogenaamde pagefiles van maximaal 8k, waarmee de één record is opgedeeld in verschillende kleinere bestanden.

Page != bestand, postgresql slaat data op in blokjes van 8k (omdat harddisks dat ook doen/deden en dat dus sneller is). Er worden wel meerdere records in hetzelfde bestand gezet, maar een tabel kan dan wel weer bestaan uit een hele rits kleinere bestanden met elk een deel van de data. (zo is er geen beperking op hoeveel data er in een tabel kan staan, ook als het OS een limiet stelt op de afmeting van bestanden).

Op 29-01-2012 11:05 schreef PgVincent www.yapf.net:

MySQL gaat indexes herbouwen en dus records lezen, stukje index bouwen, stukje records lezen, stukje index schrijven, het lijkt mij eigenlijk alleen maar logisch dat dat gepaard gaat met heel veel random IO.

Een deel van de random IO is te verklaren, maar het verschil tussen PostgreSQL en MySQL is zó enorm groot, daar kan ik met m'n boerenverstand gewoon niet bij. PostgreSQL lijkt stukken random IO te combineren tot een stuk sequential IO, waardoor het dus véél sneller gaat. Het uitlezen van de data gaat/kan sequentieel gebeuren, er is méér dan voldoende RAM beschikbaar om dit te cachen.

MySQL is vrijwel onbruikbaar met een flinke hoeveelheid data! Het is sneller om de datawarehouse taken direct op ons OLTP-systeem uit te voeren, naast de OLTP taken, dan eerst alles exporteren naar de applicatie die eigenlijk is gemaakt voor deze DW-taken met gebruik van MySQL. Het is dat ze de OLTP en OLAP niet willen combineren, maar wat performance betreft is het geen probleem voor PostgreSQL.

Quote

Kort zoeken op het performanceblog leert dat dit waarschijnlijk gewon een bekent probleem van MYSQL+SAN is:
http://www.mysqlperf...san-with-mysql/

MySQL kan read/write operaties niet altijd groeperen. SAN is blijkbaar gewoon niet de oplossing voor MySQL.

Thanks! En ja, SAN lijkt inderdaad geen performance oplossing te zijn voor MySQL, hooguit een grote bak storage. Ik zal deze link eens doorsturen, misschien dat ze met deze informatie de knoop doorhakken en afscheid nemen van MySQL. Het kost nu veel geld, tijd en frustratie en levert niks op. Alleen al de hardware voor het SAN was ruim 30k, desondanks is het DW op MySQL er nauwelijks sneller van geworden. Gewoon weggegooid geld wanneer je het voor MySQL wilt gebruiken.

PostgreSQL daarentegen heeft vleugels gekregen, gaat nu zowel voor OLTP als DW toepassingen met Mach 3 door het door het datacenter. Nu zijn de processors ineens de beperkende snelheidsfactor en niet meer de IO :-p

Quote

Page != bestand, postgresql slaat data op in blokjes van 8k (omdat harddisks dat ook doen/deden en dat dus sneller is). Er worden wel meerdere records in hetzelfde bestand gezet, maar een tabel kan dan wel weer bestaan uit een hele rits kleinere bestanden met elk een deel van de data. (zo is er geen beperking op hoeveel data er in een tabel kan staan, ook als het OS een limiet stelt op de afmeting van bestanden).

Een enkele file kan nog steeds meerdere GB's groot worden, een enkele tabel of index kan uit meerdere files bestaan. Zie --with-segsize die opgeeft bij de installatie van PostgreSQL, de default is 1GB.
http://www.postgresq...-procedure.html

Quote

PostgreSQL lijkt stukken random IO te combineren tot een stuk sequential IO, waardoor het dus véél sneller gaat.

Excact, PgSQL kan io operaties schedulen waardoor de hogere latency van een SAN minder effect heeft. MySQL boert zijn IO gewoon de bus op en komt daardoor bij elke operatie in aanraking met de latency.

Quote

Het uitlezen van de data gaat/kan sequentieel gebeuren, er is méér dan voldoende RAM beschikbaar om dit te cachen.

Cache kun je pas benutten als het gevuld is, en als MySQL z'n cache vult via random-io....

Quote

Het kost nu veel geld, tijd en frustratie en levert niks op. Alleen al de hardware voor het SAN was ruim 30k, desondanks is het DW op MySQL er nauwelijks sneller van geworden.

Tsja, dan hadden ze maar beter moeten onderzoeken of een SAN wel een geschikte oplossing is. Elke DBA weet dat de manier van opslag een enorme invloed heeft op databases en als er een investering van 30k mee gemoeid is dan lijkt het mij logisch dat men navraag gaat doen om te zien of het wel gaat werken.

Op 30-01-2012 08:11 schreef PgVincent www.yapf.net:

Excact, PgSQL kan io operaties schedulen waardoor de hogere latency van een SAN minder effect heeft. MySQL boert zijn IO gewoon de bus op en komt daardoor bij elke operatie in aanraking met de latency.

Ik heb er geen tests op losgelaten, maar ik merk met PostgreSQL in elk geval niets van latency. De oude situatie was een RAID10 van 6 15k schijven, dit SAN is echt vele vele malen sneller. Behalve met MySQL, daar is het nauwelijks sneller.

Quote

Cache kun je pas benutten als het gevuld is, en als MySQL z'n cache vult via random-io....

Tegen zoveel onkunde valt weinig te doen...

Quote

Tsja, dan hadden ze maar beter moeten onderzoeken of een SAN wel een geschikte oplossing is. Elke DBA weet dat de manier van opslag een enorme invloed heeft op databases en als er een investering van 30k mee gemoeid is dan lijkt het mij logisch dat men navraag gaat doen om te zien of het wel gaat werken.

Mee eens, ik denk dat ik het SAN dan maar inpik voor onze nieuwe server waar de replica op komt te staan. Hebben we er toch plezier van :-)

Ps. Het heeft uiteindelijk 5 uur gekost om de complete actie uit te voeren... PostgreSQL doet dit in enkele seconden.

Quote

De oude situatie was een RAID10 van 6 15k schijven,

Met een BBU?

Quote

Tegen zoveel onkunde valt weinig te doen..

Tell me about it. :-)

Op 30-01-2012 17:31 schreef PgVincent www.yapf.net:

Met een BBU?

Ja, 512MB groot. Werkt goed, maar zodra deze vol zit, ga je vol op je plaat en is het over en uit met de performance.

Cache is leuk, maar uiteindelijk moet de data toch op disk terecht komen. En dus moeten de schijven wel in staat zijn om snel genoeg de cache te kunnen legen, anders sta je alsnog te wachten. Met veel schijven, op dit moment 48 stuks in ons SAN, kun je gewoon vééél meer writes doen dan dat je met 6 schijven kan. Lezen hebben we niet zoveel problemen mee, dat gaat al snel vanaf schijf maar wordt door het OS goed in RAM gecached, er hoeft dus niet zo heel veel data vers vanaf schijf te komen. Bij ons is het schrijven altijd een probleem geweest en dat hebben we (voor PostgreSQL) perfect kunnen oplossen met dit SAN. En het is eenvoudig schaalbaar, we kunnen er nog 4x24 schijven bijzetten.

Quote

Met veel schijven, op dit moment 48 stuks in ons SAN, kun je gewoon vééél meer writes doen dan dat je met 6 schijven kan.

Dat is niet helemaal waar. Meer spindles betekent meer concurrent writes door de controller, maar dat betekent niet dat je al die writes ook daadwerkelijk vanaf je server kunt benutten. Zo zal de SAN ook een behoorlijke bups paritydata moeten schrijven omdat alle schijven redundant moeten zijn. Met 48 disks kun je bepaald niet 48 concurrent writes vanaf je server benutten.

Afijn, zo heeft elke plossing zijn voor- en nadelen en daarmee is elke oplossing geschikt voor een bepaald doel.
Aan de beheerder de schone, als ietwat frustrerende taak, om uit te zoeken wat het best waarbij hoort :-)

Op 31-01-2012 07:52 schreef PgVincent www.yapf.net:

Dat is niet helemaal waar. Meer spindles betekent meer concurrent writes door de controller, maar dat betekent niet dat je al die writes ook daadwerkelijk vanaf je server kunt benutten. Zo zal de SAN ook een behoorlijke bups paritydata moeten schrijven omdat alle schijven redundant moeten zijn. Met 48 disks kun je bepaald niet 48 concurrent writes vanaf je server benutten.

Paritydata is toch iets van RAID 5 en niet van RAID10?

http://en.wikipedia....ional_Databases

RAID 5 heeft ook nog als nadeel dat het herstellen van een raid array na uitval van 1 disk veel langer duurt, iets wat voor ons onacceptabel is. Met RAID 6 heb je dat minder, maar met RAID 10 is dat probleem er helemaal niet meer. Het kost wat meer, maar in ons geval vallen deze kosten weg tegen de baten.

Quote

Paritydata is toch iets van RAID 5 en niet van RAID10?

Klopt, maar je SAN draait geen RAID-10 hoop ik.

Quote

maar met RAID 10 is dat probleem er helemaal niet meer.

Tsja dat is een afweging. Met RAID-10 heb je twee keer RAID-0, dus twee arrays die niet fouttolerant zijn. Zodra een disk uitvalt in een van de twee raid-0 arrays draait je hele onderneming op één raid-0 array die dan ineens n-maal zo gevoelig is voor diskuitval. Bij RAID-5 kun je in de afmeting nog zo kiezen dat je twee disks kwijt kunt raken voordat je array uitvalt.

Afijn, dat is wikken en wegen en heel erg afhankelijk van hardware, eisen vannuit de gebruikers etc. Er is geen gouden RAID level wat "het beste" is.

Op 02-02-2012 08:07 schreef PgVincent www.yapf.net:

Klopt, maar je SAN draait geen RAID-10 hoop ik.

Waarom hoop je dat? Daar is toch niks mis mee? En volgens mij is het RAID 10.

Quote

Tsja dat is een afweging. Met RAID-10 heb je twee keer RAID-0, dus twee arrays die niet fouttolerant zijn. Zodra een disk uitvalt in een van de twee raid-0 arrays draait je hele onderneming op één raid-0 array die dan ineens n-maal zo gevoelig is voor diskuitval. Bij RAID-5 kun je in de afmeting nog zo kiezen dat je twee disks kwijt kunt raken voordat je array uitvalt.

Je haalt RAID 1+0 (RAID 10) en RAID 0+1 door elkaar, dat zijn twee totáál verschillende dingen. Een RAID 10 opstelling is zeer veilig, er kan per RAID1 setje (en dat kunnen er tientallen zijn) precies 1 disk uitvallen. En wanneer je binnen je SAN nog één of meerdere spares hebt zitten, wat voor de hand ligt, is een rebuild ook zo gebeurd. Met RAID 5 mag er precies 1 disk uitvallen, zodra de 2e uitvalt voordat je een rebuild hebt gedaan, ben je álle data kwijt. Met RAID6 is dat risico kleiner, maar ook dan kost een rebuild nog steeds veel tijd.

Quote

Afijn, dat is wikken en wegen en heel erg afhankelijk van hardware, eisen vannuit de gebruikers etc. Er is geen gouden RAID level wat "het beste" is.

Dat klopt als een bus. Maar je moet wel weten wat nu precies welke RAID opstelling doet en hoe veilig het is, voordat je een keuze gaat maken.

Quote

Je haalt RAID 1+0 (RAID 10) en RAID 0+1 door elkaar

Klopt.