[PHP] Probleem met grote update query MySQL database

[.GoO]

Voor een PHP website haal ik met een scriptje ieder uur data op, dit proces duurt 1 tot 2 minuten. Nu is het probleem dat hij tijdens deze update de MySQL database achter de website lijkt te locken, waardoor de website blijft laden en even niet benaderbaar is.

Het gaat om een simpele loop die een x aantal XML feeds uitleest en bij iedere count een update uitvoert. Het is dus niet één update die 1 tot 2 minuten duurt, maar in totaal een stuk of 100. Hoe kan ik ervoor zorgen dat de bezoeker aan de voorkant hier geen last van heeft?

[gekkie]

Asynchroon doen (zoeken op ajax) ?

(en misschien nog wat indexen op je DB tabellen en query optimalisatie (batchen) zodat het hopelijk ook ruim onder de minuut kan ?)

[mcDavid]

Gaat het nou om 100 records? Of om 100 queries (met duizenden records each)?

En zijn het updates of inserts?

Kortom hoe zien de queries er precies uit?

Misschien is het ook nog handig te weten welke storage engine je gebruikt.

[Morrar]

Zijn het delta's of full loads die je naar de database stuurt?

Wellicht time stamp bijhouden en alleen nieuwe records inserten?

[Gomez12]

gekkie schreef op vrijdag 18 september 2015 @ 23:09:
Asynchroon doen (zoeken op ajax) ?

Wat heeft asynchroon in hemelsnaam te maken met een lock op je database? Asynchroon is een frontend ding en heeft totaal niets te maken met de backend, als je die gegevens wilt wijzigen wijzig je die gegevens en lock je de database. Dan maakt het in principe niet uit hoe je die gegevens aanlevert (alhoewel je een kans hebt dat je async actie 100x zo traag is waardoor er meer lucht op je database komt waardoor het tijdelijk goed kan gaan, alleen dat is uitstel van executie en geen oplossing)

(en misschien nog wat indexen op je DB tabellen en query optimalisatie (batchen) zodat het hopelijk ook ruim onder de minuut kan ?)

Indexen zijn eerder slechter dan beter bij updates (er moet meer veranderd worden en dus meer/langer gelocked).

Je kan wel naar je query's in het algemeen kijken en daarbij tot de conclusie komen dat een index performanter is, maar blind indexen toevoegen zal enkel de situatie slechter maken. Je moet er wel een noodzaak voor hebben.

[gekkie]

Gomez12 schreef op vrijdag 18 september 2015 @ 23:49:
[...]

Wat heeft asynchroon in hemelsnaam te maken met een lock op je database? Asynchroon is een frontend ding en heeft totaal niets te maken met de backend, als je die gegevens wilt wijzigen wijzig je die gegevens en lock je de database. Dan maakt het in principe niet uit hoe je die gegevens aanlevert (alhoewel je een kans hebt dat je async actie 100x zo traag is waardoor er meer lucht op je database komt waardoor het tijdelijk goed kan gaan, alleen dat is uitstel van executie en geen oplossing)


[...]

Indexen zijn eerder slechter dan beter bij updates (er moet meer veranderd worden en dus meer/langer gelocked).

Je kan wel naar je query's in het algemeen kijken en daarbij tot de conclusie komen dat een index performanter is, maar blind indexen toevoegen zal enkel de situatie slechter maken. Je moet er wel een noodzaak voor hebben.

Ah waarschijnlijk verkeerd gelezen .. mea culpa ..
ik dacht dat het ophalen sloeg op iets uit de database .. maar dat zijn kennelijk externe atom feeds ofzo en zo'n http request duurt kennelijk 1 a 2 minuten per stuk. En bij een succes update hij kennelijk iets in een mysql database .. waardoor andere requests op dezelfde database / tabel lijken te mislukken door een lock of iets anders

[mbe81]

Waarschijnlijk is je tabelstructuur MyISAM? Pas deze structuur dan aan naar InnoDB.

MyISAM gebruikt locking op tabel-level waardoor ook de select-statements gequeued worden. InnoDB zal de tabel op row-level locken waardoor het door jou beschreven probleem niet meer voor zou moeten komen.

Zie ook https://dev.mysql.com/doc...en/internal-locking.html:

Table updates are given higher priority than table retrievals. Therefore, when a lock is released, the lock is made available to the requests in the write lock queue and then to the requests in the read lock queue. This ensures that updates to a table are not “starved” even if there is heavy SELECT activity for the table. However, if you have many updates for a table, SELECT statements wait until there are no more updates.

[.GoO]

Tabelstructuur is al InnoDB, vreemd dat hij dan toch locked.

Ik vraag een XML op en het record insert of update ik, afhankelijk van of het record zich al in de database bevindt. Kort idee van de code:

code:

$get_records = database::query("SELECT * FROM records WHERE status = 'Active'");

while ($record = database::fetch_array($get_records)) {

 <kies implementatie> 

 [..informatie uit XML ophalen en in variabelen stoppen, mutaties op uitvoeren, etc..]

 $get_record = database::query("SELECT * FROM record WHERE path = '$path'");

  if ($num_results > 0)

    [update in de database]

  else 

    [insert in de database]

    [insert bijbehorende images in een andere tabel in de database]

}

Het gaat dus om kleine insert of updates, maar wel om een stuk of 150 per update cyclus. Dus hij moet deze loop ongeveer 150 keer doorlopen, dat duurt 1 tot 2 minuten.

Het proces kan efficiënter en dat ga ik nog wel finetunen, maar dan zal hij misschien 1 minuut locken in plaats van 2 minuten. Ik wil die hele lock niet Is er geen with nolock functie voor MySQL?

[gekkie]

.GoO schreef op zaterdag 19 september 2015 @ 16:56:
Tabelstructuur is al InnoDB, vreemd dat hij dan toch locked.

Ik vraag een XML op en het record insert of update ik, afhankelijk van of het record zich al in de database bevindt. Kort idee van de code:

code:

$get_records = database::query("SELECT * FROM records WHERE status = 'Active'");

while ($record = database::fetch_array($get_records)) {

 <kies implementatie> 

 [..informatie uit XML ophalen en in variabelen stoppen, mutaties op uitvoeren, etc..]

 $get_record = database::query("SELECT * FROM record WHERE path = '$path'");

  if ($num_results > 0)

    [update in de database]

  else 

    [insert in de database]

    [insert bijbehorende images in een andere tabel in de database]

}

Het gaat dus om kleine insert of updates, maar wel om een stuk of 150 per update cyclus. Dus hij moet deze loop ongeveer 150 keer doorlopen, dat duurt 1 tot 2 minuten.

Het proces kan efficiënter en dat ga ik nog wel finetunen, maar dan zal hij misschien 1 minuut locken in plaats van 2 minuten. Ik wil die hele lock niet Is er geen with nolock functie voor MySQL?

Hoe denk je dan te merken dat er iets "gelocked" is, dat mis ik nog in je beschrijving ?

[.GoO]

gekkie schreef op zaterdag 19 september 2015 @ 17:30:
[...]

Hoe denk je dan te merken dat er iets "gelocked" is, dat mis ik nog in je beschrijving ?

Omdat ik de website niet op kan vragen zolang het update proces loopt. Hij wacht met laden tot de update klaar is.

[gekkie]

.GoO schreef op zaterdag 19 september 2015 @ 17:31:
[...]
Omdat ik de website niet op kan vragen zolang het update proces loopt. Hij wacht met laden tot de update klaar is.

En je hebt gechecked dat dat request:
- op de server aankomt
- het opgevraagde script loopt
- maar gaat wachten op de database ?

(en het script dat je opvraagd wat voor query zit daar in .. op dezelfde tabel ?)

En als optimalisatie zou ik de updates/inserts eerst in een variabele zetten, en nadat alle data geprocessed is .. dat allemaal in een keer naar de DB flushen .. er vanuit gaande dat het opvragen van de xml data relatief veel tijd kost.

En als laatste nog innotop .. kan er wellicht nog wat licht op schijnen als het inderdaad een locking issue blijkt te zijn:
http://www.xaprb.com/blog...-debug-innodb-lock-waits/

[ Voor 32% gewijzigd door gekkie op 19-09-2015 17:46 ]

[Damic]

gekkie schreef op zaterdag 19 september 2015 @ 17:35:
[...]

En je hebt gechecked dat dat request:
- op de server aankomt
- het opgevraagde script loopt
- maar gaat wachten op de database ?

(en het script dat je opvraagd wat voor query zit daar in .. op dezelfde tabel ?)

En als optimalisatie zou ik de updates/inserts eerst in een variabele zetten, en nadat alle data geprocessed is .. dat allemaal in een keer naar de DB flushen .. er vanuit gaande dat het opvragen van de xml data relatief veel tijd kost.

En als laatste nog innotop .. kan er wellicht nog wat licht op schijnen als het inderdaad een locking issue blijkt te zijn:
http://www.xaprb.com/blog...-debug-innodb-lock-waits/

Op het vet gedrukte: daar was ik ook al aan het denken, al die updates en inserts in 1 grote update/insert query zwieren.

[kwaakvaak_v2]

Damic schreef op zaterdag 19 september 2015 @ 17:47:
[...]
Op het vet gedrukte: daar was ik ook al aan het denken, al die updates en inserts in 1 grote update/insert query zwieren.

Dat of alle queries in een string op slaan en dan in 1x naar de DB sturen. Dat gaat het in in ieder geval los van je pre-processor qua tijd.

En mocht je heel veel indexen en/of foreign key constraints hebben kun je overwegen om die bij zo'n grote flush uit te zetten. Maar dan moet je heel goed het risico van je data consistentie overwegen. Lees ik doe dat laatste eigenlijk nooit met een draaiende live omgeving tenzij het een pre-fill vanuit een bestand is waarvan ik 100% zeker weet dat het klopt.

[itons]

Mogelijk kan je ook nog een roundtrip naar de database winnen door niet te checken of het record al bestaat en ipv daarvan een INSERT...ON DUPLICATE KEY UPDATE te doen?

[mcDavid]

.GoO schreef op zaterdag 19 september 2015 @ 16:56:
Tabelstructuur is al InnoDB, vreemd dat hij dan toch locked.

Ik vraag een XML op en het record insert of update ik, afhankelijk van of het record zich al in de database bevindt. Kort idee van de code:

code:

$get_records = database::query("SELECT * FROM records WHERE status = 'Active'");

while ($record = database::fetch_array($get_records)) {

 <kies implementatie> 

 [..informatie uit XML ophalen en in variabelen stoppen, mutaties op uitvoeren, etc..]

 $get_record = database::query("SELECT * FROM record WHERE path = '$path'");

  if ($num_results > 0)

    [update in de database]

  else 

    [insert in de database]

    [insert bijbehorende images in een andere tabel in de database]

}

Het gaat dus om kleine insert of updates, maar wel om een stuk of 150 per update cyclus. Dus hij moet deze loop ongeveer 150 keer doorlopen, dat duurt 1 tot 2 minuten.

Het proces kan efficiënter en dat ga ik nog wel finetunen, maar dan zal hij misschien 1 minuut locken in plaats van 2 minuten. Ik wil die hele lock niet Is er geen with nolock functie voor MySQL?

Zo'n loop is idd inherent traag. Ik zou je adviseren in de loop een lange querystring op te bouwen en die in één DB call uit te voeren. Als het echt veel records zijn evt per 1000 ofzo één query doen. Dit hoeft (voor 150 records) echt niet langer dan een paar milliseconden te duren.

Ik zou ook niet checken of records al bestaan en indien mogelijk ook geen on duplicate key update gebruiken. INSERT IGNORE en REPLACE zijn de queries die je wilt gebruiken.

[.GoO]

mcDavid schreef op zondag 20 september 2015 @ 09:05:
[...]

Zo'n loop is idd inherent traag. Ik zou je adviseren in de loop een lange querystring op te bouwen en die in één DB call uit te voeren. Als het echt veel records zijn evt per 1000 ofzo één query doen. Dit hoeft (voor 150 records) echt niet langer dan een paar milliseconden te duren.

Ik zou ook niet checken of records al bestaan en indien mogelijk ook geen on duplicate key update gebruiken. INSERT IGNORE en REPLACE zijn de queries die je wilt gebruiken.

Bedankt voor de tips, ik ga ermee aan de slag!

[RobIII]

Iets anders: is je sessie niet gelocked? Dat zou ook niet voor 't eerst zijn...

[BlueZero]

RobIII schreef op zondag 20 september 2015 @ 14:49:
Iets anders: is je sessie niet gelocked? Dat zou ook niet voor 't eerst zijn...

Dit dus, ik kan me niet voorstellen dat je database gelocked wordt als het innoDB is, probeer eens een nieuwe request in een andere browser op je website uit te voeren. Dit lijkt me eerder een PHP restrictie dan een database restrictie.

[ACM]

RobIII schreef op zondag 20 september 2015 @ 14:49:
Iets anders: is je sessie niet gelocked? Dat zou ook niet voor 't eerst zijn...

't Lijkt me inderdaad verstandig te kijken welke queries gelocked zijn, en dan dus ook die van de websitekant. Een andere optie is bijvoorbeeld queries die kijken naar de tabel-structuur, hoewel die doorgaans ook niet al te lang gelocked zouden mogen blijven (wellicht wel lang als er een transactie wordt gestart).