[mysql]Delete query erg traag

[DaOverclocker]

Wanneer ik een simpele delete query uit wil voeren moet ik daar erg lang op wachten. Ik vraag mij af of dit niet sneller kan.

Ik heb de volgende table:
'sample_data', type InnoDB, Collation: latin1_swedish_ci:

setID	mediumint(4)	UNSIGNED
sampleID	mediumint(6)	UNSIGNED
peakNR	int(6)	UNSIGNED
amplitude_measured	float(8,2)	UNSIGNED
noise_measured	smallint(4)	UNSIGNED
mass_measured	int(12)	UNSIGNED
ret_measured	int(9)	UNSIGNED

De lengte van de variabelen kunnen niet kleiner, ze zijn nu optimaal(kleiner geeft data verlies). Ik heb een INDEX op setID gemaakt.

Nu heb ik 1 set in de database staan met ID=1. De table is nu gevuld met 7.000.000 records. Totaal ongeveer 700MB aan data.
Nu voer ik een delete statement uit: (in dit geval gaat de hele database dus leeg)
DELETE FROM `sample_data` WHERE setID=1


Ik heb deze delete query op verschillende plekken uitgevoerd, console, phpmyadmin. Volgens phpmyadmin doet hij er zo lang over:
Deleted rows: 7036350 (Query took 516.6415 sec)

Ik heb geen ssh access tot de server, kan dus ook geen load bekijken, en ook niet of er veel I/O is.
De server is volgens mij een snelle bak, denk dual core opteron ~2ghz 4Gb ram. De Server wordt op dit moment niet door anderen gebruikt.
Overigens het inladen van de data gebeurd door het commando:
Query LOAD DATA LOCAL INFILE
Het in te laden bestand is circa 200MB groot en duurt 7 minuten om te laden. Ook vrij lang als je het mij vraagt.

Mogelijk antwoorden:
- logisch, je moet 7M records verwijderen
- dit duurt wel lang normaal gesproken, maar dit is erg overdreven
- dit duurt veels te lang, je bent iets vergeten
- dit duurt veels te lang, je config van dje mysql is niet goed.
- we kunnen er niets overzeggen, we hebben meer info van jou en/of over de server nodig.

[MueR]

7 miljoen records verwijderen kan inderdaad even duren. Die server moet wel even flink wat pages gaan verwijderen. Zet er eens een limit van bijv 1000 op en kijk wat ie doet.

[ Voor 20% gewijzigd door MueR op 23-01-2009 15:47 ]

[drm]

Voor grote, niet complexe datasets is het vziw lonend om tabellen in MyISAM ipv InnoDB op te slaan
Zet een index op je setID kolom. Vaak is het zelfs sneller om, mocht de index vertragend werken omdat je veel update en insert queries doet, eerst een index te setten, dan de delete uit te voeren en dan de index weer te verwijderen.
Tot slot is het misschien het overwegen waard de "set" entiteit iets verder te normaliseren en tot een integer primary key te komen in plaats van een (var)char primary key. int columns worden sowieso beter geindexeerd dan (var)char velden.

[cariolive23]

Wanneer je de hele tabel wilt legen, kijk dan eens naar TRUNCATE.

De LOAD DATA duurt inderdaad erg lang, ik heb soortgelijke bestanden in een 30 tot 40 seconden geladen. Dat gebeurde wel op een server die redelijk geconfigureerd was (gebruikte ook echt het geheugen dat beschikbaar was) en met een snelle RAID-array. Ga eens uitzoeken hoe e.e.a. is geconfigureerd en welke hardware je nu daadwerkelijk tot je beschikking hebt.

[DaOverclocker]

MueR schreef op vrijdag 23 januari 2009 @ 15:46:
7 miljoen records verwijderen kan inderdaad even duren. Die server moet wel even flink wat pages gaan verwijderen. Zet er eens een limit van bijv 1000 op en kijk wat ie doet.

ja 1000 verwijderen gaat uitereraard een stuk sneller.

Voor grote, niet complexe datasets is het vziw lonend om tabellen in MyISAM ipv InnoDB op te slaan
Zet een index op je setID kolom. Vaak is het zelfs sneller om, mocht de index vertragend werken omdat je veel update en insert queries doet, eerst een index te setten, dan de delete uit te voeren en dan de index weer te verwijderen.
Tot slot is het misschien het overwegen waard de "set" entiteit iets verder te normaliseren en tot een integer primary key te komen in plaats van een (var)char primary key. int columns worden sowieso beter geindexeerd dan (var)char velden.

MyISAM kan ik proberen:
omgezet naar MyISAM
No shit, 60 sec om 7M records in te laden en 40 seconden voor een delete van 7M.
Dat is gewoon een factor 10 sneller..!

Index zit al op setID kolom, de set entiteit is al een mediumint, dus de primary key is al een int.

Truncate kan niet want ik wil 1 set verwijderen. In mijn voorbeeld zit er maar 1 in de database, maar dat kunnen er meer zijn. Maar als je hem leeg wilt gooien heb je gelijk, truncate is dan veel sneller.

[ Voor 7% gewijzigd door DaOverclocker op 23-01-2009 16:32 ]

[Knutselsmurf]

InnoDB heeft als eigenschap dat in principe iedere query in zijn eigen transactie zit, tenzij anders aangegeven. Verder wordt er pas verder gegaan, als de transactie volledig op schijf geschreven is. In de praktijk betekent dit, dat het aantal transacties per seconden beperkt wordt door de access-tijd van de harde schijf, waarop de database staat. Het inladen is dus sterk te versnellen door de database-dump aan te passen en alle queries in 1 transactie te stoppen.

[Hydra]

cariolive23 schreef op vrijdag 23 januari 2009 @ 16:19:
Wanneer je de hele tabel wilt legen, kijk dan eens naar TRUNCATE.

Als je de hele tabel wilt legen is een drop/create gewoon 't snelst. Dat doen sommige systemen ook als je een delete zonder 'where' doet. Maar hij wil hier aan de hand van een ID een x aantal rows verwijderen.

[justmental]

Je hebt een index op een kolom waar alle waarden hetzelfde zijn?

[Hydra]

justmental schreef op vrijdag 23 januari 2009 @ 16:54:
Je hebt een index op een kolom waar alle waarden hetzelfde zijn?

Euh, alleen in de testcase?

[Voutloos]

Ok, maar dan is dit dus ook geen representatieve benchmark.

[DamadmOO]

Voutloos schreef op vrijdag 23 januari 2009 @ 17:15:
Ok, maar dan is dit dus ook geen representatieve benchmark.

En maakt dat op dit moment wat uit? De TS vond gewoon dat het naar zijn mening veel te lang duurde om 7M records te verwijderen. Dat er later een veelvoud van dit aantal records in de tabel staat en het daardoor nog slomer gaat maakt dan echt helemaal niks uit.

[justmental]

DamadmOO schreef op vrijdag 23 januari 2009 @ 19:06:
En maakt dat op dit moment wat uit? De TS vond gewoon dat het naar zijn mening veel te lang duurde om 7M records te verwijderen. Dat er later een veelvoud van dit aantal records in de tabel staat en het daardoor nog slomer gaat maakt dan echt helemaal niks uit.

Wellicht wordt door de index de delete in nested loops gedaan aan de hand van index lookups.
Wat je dan meet is afhankelijk van of je index in het geheugen past en of de blokken ver uit elkaar staan op schijf.
Het heeft iig. weinig met normale delete performance te maken.

Dat de delete met een isam structuur sneller is wil ook niet zeggen dat dat een handige keuze is, als de applicatie multi user wordt kan dat wel weer de langzaamste optie zijn.

[DaOverclocker]

En maakt dat op dit moment wat uit? De TS vond gewoon dat het naar zijn mening veel te lang duurde om 7M records te verwijderen. Dat er later een veelvoud van dit aantal records in de tabel staat en het daardoor nog slomer gaat maakt dan echt helemaal niks uit.

klopt, als er meer setID's in staan wordt de boel alleen maar langzamer en wil ik nog steeds data per setID kunnen weggooien.

Het heeft iig. weinig met normale delete performance te maken.

Nou ja, in mijn geval wel de end-user performance. Hoe dat intern geregeld is maakt mij dus niet uit.

Dat de delete met een isam structuur sneller is wil ook niet zeggen dat dat een handige keuze is, als de applicatie multi user wordt kan dat wel weer de langzaamste optie zijn.

In mijn geval zal het zelden voor komen dat meerdere users tegelijk het systeem gebruiken. Hooguit toevallig 2 of 3 tegelijk.

InnoDB heeft als eigenschap dat in principe iedere query in zijn eigen transactie zit, tenzij anders aangegeven. Verder wordt er pas verder gegaan, als de transactie volledig op schijf geschreven is.

Ik weet niet hoe het zit met het LOAD DATA INFILE commando, maar dat klinkt voor mij als 1 query. Volgens mij is dit toch wel de snelste manier om een bulk data in je database te stoppen.

De select queries gaan nog steeds met dezelfde snelheid, maar daar had ik ook geen klachten over.
In mijn geval hielp deze transformatie dus een hoop, iets wat ik lastig terug kan vinden op internet. Ik liep al weken met dit probleem rond, ten einde raad maar zelf een topic gestart, en met goed afloop!

[Eddy Dean]

Deletes zijn altijd relatief duur, en het is mi ook niet iets wat je moet doen op het moment dat de user het vraagt. Wat ik zelf wel eens doe (als het dus om meerdere deletes gaan die echt tijd kosten) is een kolom 'deleted' maken, die dan aangeeft of dat record actief is of niet. Het grote voordeel is dat het ongedaan maken makkelijk is (en je 'weet je het zeker?' dus kunt gaan vervangen door 'wil je het ongedaan maken?'). Het daadwerkelijk verwijderen doe je dan op een rustig moment. Nadeel is dat alle queries een WHERE erbij krijgen, die natuurlijk ook tijd kost.