[MySQL, PHP] Enorme query opsplitsen?

[T-MOB]

Ik heb een webapplicatie waarin inderzoeksgegevens naartoe worden geupload. Deze worden opgeslagen in een MySQL database waarna er allemaal spannende dingen mee gedaan kunnen worden. Onderdeel van het upload-process is een bestand met de daadwerkelijke gegevens, een tab-seperated textfile. Deze lees ik regel voor regel uit in PHP, na controle en wat conversies sla ik elke regel op een array $DataColumnsInsert. Vervolgens bouw ik op de volgende manier mijn query op:

PHP:

$query = 'INSERT INTO data ' .$Columns .' VALUES ' .implode(',', $DataInsertCases);

Dit functioneert in principe prima. Totdat ik vandaag testte met een huge datafile (2mb data), bestaande uit zo'n 13.000 regels. "MySQL has gone away" was de melding waar ik het mee moest doen. Even googlen leverde op dat dit betekent dat mijn query waarschijnlijk te groot is tov de standaard ingestelde 1Mb. Verschillende oplossingen kwamen in me op:

1. De max-query grootte aanpassen. Lekker simpel maar dat doe ik liever niet omdat dit een server-breedde aanpassing is, en omdat het script dan niet meer kan draaien op een server waar je geen invloed hebt op de MySQL configuratie.
2. Door het hele array heenloopen. Ook lekker simpel maar het betekent wel dat er 13.000 queries naar de server worden gestuurd ipv 1. Niet echt een fijne oplossing lijkt me zo...
3. Een wat intelligentere oplossing. Wat ingewikkelder, een slimme manier om de query op te splitsen in meerdere queries. Liefst van zo'n 1Mb groot. Een eerste aanzet tot deze mogelijkheid kwam uit op zoiets:

PHP:

foreach ($DataInsertCases as $case) 
{
  if (!isset($tmpquery))
  {
      $tmpquery = 'INSERT INTO pge_data ' .$DataColumnsInsert .' VALUES ' .$case;
  } else {
      $tmpquery .= ',' .$case;
  }
 
  if (strlen($tmpquery) > 900*1024) 
  {
      $QUERY[] = $tmpquery;
      unset($tmpquery);
  }
}

Deze laatste oplossingsrichting lijkt mij ook niet de meest efficiënte, maar tot nog toe wel de meest wenselijke. Hoe zouden jullie dit oplossen??

[Terranca]

Weet niet zeker of dit gaat werken, maar je weet maar nooit:
Heb je al iets met INSERT DELAYED geprobeerd? Volgens mij is dat een beetje voor dit soort dingen uitgevonden. Als het niet lukt zal je idd je $DataInsertCases array in meerdere kleinere arrays kunnen stoppen. Je hebt vast wel een idee hoe groot die maximaal kunnen zijn, dus kan je een redelijke schatting maken van hoe groot de kleinere arrays moeten zijn.

[ACM]

T-MOB schreef op 25 oktober 2004 @ 19:21:
Dit functioneert in principe prima. Totdat ik vandaag testte met een huge datafile (2mb data), bestaande uit zo'n 13.000 regels. "MySQL has gone away" was de melding waar ik het mee moest doen. Even googlen leverde op dat dit betekent dat mijn query waarschijnlijk te groot is tov de standaard ingestelde 1Mb. Verschillende oplossingen kwamen in me op:

Voor zover ik weet betekent het niet perse dat de query te groot was, maar wel dat je te lang geen verkeer over de verbinding had.

1. De max-query grootte aanpassen. Lekker simpel maar dat doe ik liever niet omdat dit een server-breedde aanpassing is, en omdat het script dan niet meer kan draaien op een server waar je geen invloed hebt op de MySQL configuratie.

Groter dan 16MB kan het (met oudere versies?) niet, dus echt schaalbaar is het nou ook weer niet.

2. Door het hele array heenloopen. Ook lekker simpel maar het betekent wel dat er 13.000 queries naar de server worden gestuurd ipv 1. Niet echt een fijne oplossing lijkt me zo...

Hoevaak gebeurt het dat er zoveel zijn dan? En heb je al getest hoe lang het duurt?

3. Een wat intelligentere oplossing. Wat ingewikkelder, een slimme manier om de query op te splitsen in meerdere queries. Liefst van zo'n 1Mb groot. Een eerste aanzet tot deze mogelijkheid kwam uit op zoiets:

Die zou ik niet zo gauw doen, alleen maar een hoop extra werk voor iets dat wellicht nauwelijks gebruikt wordt?

Deze laatste oplossingsrichting lijkt mij ook niet de meest efficiënte, maar tot nog toe wel de meest wenselijke. Hoe zouden jullie dit oplossen??

Kijk eens naar de LOAD DATA-commando's, dat is het meest efficiente en waarschijnlijk het meest schaalbare. Niet perse het handigste overigens

[T-MOB]

ACM schreef op 25 oktober 2004 @ 19:43:
[...]
Voor zover ik weet betekent het niet perse dat de query te groot was, maar wel dat je te lang geen verkeer over de verbinding had.

In het MySQL manual kwam ik dit tegen:

You can also get these [MySQL server has gone away, (edit)] errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB.

Aangezien het probleem met een kleine file er niet was, maar wel toen ik een text-file van 2mb aan het invoeren was leek dit me 99,9% zeker de oorzaak.

[...]
Groter dan 16MB kan het (met oudere versies?) niet, dus echt schaalbaar is het nou ook weer niet.

[...]

Hoevaak gebeurt het dat er zoveel zijn dan? En heb je al getest hoe lang het duurt?

Niet zo heel vaak gelukkig, de wens voor een nette oplossing is er deels omdat ik graag wil leren hoe je zoiets goed doet. Mijn ervaring met mysql tot nog toe heb ik opgedaan met kleine hobby-bob sites met weinig bezoekers waarbij indexen bij wijze van spreke nog overbodige luxe waren. Als alles groter wordt kom je nog wel eens wat grenzen tegen...
Een benchmarkje heb ik nog niet gedaan, maar het is wel een plan. (to be updated)

[...]
Die zou ik niet zo gauw doen, alleen maar een hoop extra werk voor iets dat wellicht nauwelijks gebruikt wordt?

[...]

Kijk eens naar de LOAD DATA-commando's, dat is het meest efficiente en waarschijnlijk het meest schaalbare. Niet perse het handigste overigens

Dat zal ik zeker "even" doen. Op eerste gezicht zie ik echter wat probleempjes aangezien de aangeleverde textfiles niet noodzakelijk alle kolommen in de tabel bevatten. Dan zal ik dus met PHP een text-file moeten maken die geschikt is voor LOAD DATA. Daarnaast mag ik hopen dat aan de 'data' tabel geen kolom wordt toegevoegd tussen het definiëren van het formaat van de text-file en het daadwerkelijke inlezen. De kans is (kleiner dan) nihil, maar daar was ene wet van Murphy over...

[chem]

Als het domweg te lang duurde houdt mysql het ook voor gezien; een PING(); maakt de verbinding weer wakker.

Verder heeft insert delayed geen enkele zin deze context, aangezien het verwerken het probleem (nog) niet is.

Ik zou ze gewoon per x mb oid doorsturen. Uit m'n hoofd kan unbuffered query wellicht nog helpen (niet zeker of die ook voor INSERT extra nut biedt).

[T-MOB]

chem schreef op 25 oktober 2004 @ 21:17:
Als het domweg te lang duurde houdt mysql het ook voor gezien; een PING(); maakt de verbinding weer wakker.

Verder heeft insert delayed geen enkele zin deze context, aangezien het verwerken het probleem (nog) niet is.

Ik zou ze gewoon per x mb oid doorsturen. Uit m'n hoofd kan unbuffered query wellicht nog helpen (niet zeker of die ook voor INSERT extra nut biedt).

Dat het te lang duurt lijkt me vrijwel uitgesloten, MySQL draait nl. op dezelfde bak als de webserver. De queries erna worden wel gewoon uitgevoerd (wat overigens niet zo handig is omdat er eigenlijk alleen maar complete gegevens ingevoerd mogen worden. Daar moet ik dus ook nog wat voor bedenken ).

Qua insert_delayed had ik al mijn twijfels (het duurt al lang genoeg )

Over de unbuffered query heb ik me laten vertellen dat ie sneller is, maar een aantal "features" mist als mysql_num_rows bij een SELECT. Als de boel werkt zal ik wel testen of het hierdoor sneller gaat. Ik ben er echter vrijwel zeker van dat de grootte van de query het probleem is en niet de tijd van de connectie (zie mijn vorige post).