[PHP/MySQL] insert query extreem langzaam

Pagina: 1
Acties:

Onderwerpen


Acties:
  • 0 Henk 'm!

  • enveekaa
  • Registratie: September 2003
  • Laatst online: 21-09 06:09
Ik ben bezig met een eenvoudig mailinglist script en loop tegen het volgende probleem aan.
Tijdens een naar mijn mening eenvoudige SQL query krijg ik een timeout. Nu kan ik natuurlijk met set_time_limit de timeout opschroeven, maar dat is geen oplossing.

Bij de eerste select trek ik er dus 600 email adressen uit, welke ik in een while loop toevoeg aan de queue tabel. Ik haal nu ongeveer 19 inserts per seconde, wat me uitzonderlijk weining lijkt? ;)

PHP:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
databaseConnect();

// Haal 600 email adressen op..
$query = "SELECT email FROM subscribers";
$result_subscribers = mysql_query($query) or die(errorShow(mysql_error()));

// Mik die 600 email adressen in de queue tabel met wat additionele info    rmatie..
while ($row_subscribers = mysql_fetch_array($result_subscribers)) {
    $query = "
    INSERT INTO queue (`queueid`,`added`,`mailingid`,`recipient`,`sent`) 
    VALUES (NULL,'".time()."','".$_GET['mailingid']."','".$row_subscribers['email']."','FALSE')"; 
    $result = mysql_query($query) or die(errorShow(mysql_error()));
} 

// Update de mailings tabel dat mailing X in de queue staat..
$query = "
UPDATE quandasmailer.mailings 
SET queued = 'TRUE' WHERE mailingid = '".$_GET['mailingid']."'";

$result = mysql_query($query) or die(errorShow(mysql_error()));
    
header("Location: ?mod=mailings");

[ Voor 4% gewijzigd door enveekaa op 09-10-2008 11:31 ]


Acties:
  • 0 Henk 'm!

  • MueR
  • Registratie: Januari 2004
  • Laatst online: 21-09 14:53

MueR

Admin Tweakers Discord

is niet lief

Je wil dan ook niet in een while loop 600 inserts gaan doen. Dit wil je bufferen en per 100 ofzo gaan inserten.

Anyone who gets in between me and my morning coffee should be insecure.


Acties:
  • 0 Henk 'm!

  • sky-
  • Registratie: November 2005
  • Niet online

sky-

qn nna 👌

Zoals MueR zegt, bufferen en dan runnen.

Maar, 'TRUE' stop je natuurlijk niet in je database lijkt me, true lijkt me beter. (dus zonder haakjes) Booleans gebruiken dus ;)

don't be afraid of machines, be afraid of the people who build and train them.


Acties:
  • 0 Henk 'm!

  • enveekaa
  • Registratie: September 2003
  • Laatst online: 21-09 06:09
Ik had een helder moment, de tabel queue had InnoDB als storage engine. Dit heb ik aangepast naar MyISAM en dan gaat het binnen een seconde :)

Maar ik ben wel geintresseerd in je tip, wat is een goede constructie om de inserts te bufferen naar 100 per keer?

'TRUE' is inderdaad een beetje stom :) Heb de quotes weggehaald en nu wordt het 1. Thanks.

[ Voor 15% gewijzigd door enveekaa op 09-10-2008 11:40 ]


Acties:
  • 0 Henk 'm!

  • Exception
  • Registratie: Augustus 2006
  • Laatst online: 17-09 11:10
Precies, zet eerst alle query's achter elkaar in een variabele. Pas daarna, stuur je de query naar de database met mysql_query(..)

Acties:
  • 0 Henk 'm!

  • leuk_he
  • Registratie: Augustus 2000
  • Laatst online: 15-07 15:35

leuk_he

1. Controleer de kabel!

Afgezien ^^^muer kun je statemnet ook "preparen" zodat hij niet elke keer het insert statement hoeft te parsen. Verder de vraag hoe "queue" gedefinieerd is.(en "gewoon" is geen goed antwoord)

Need more data. We want your specs. Ik ben ook maar dom. anders: forum, ff reggen, ff topic maken
En als je een oplossing hebt gevonden laat het ook ujb ff in dit topic horen.


Acties:
  • 0 Henk 'm!

  • bigbeng
  • Registratie: Augustus 2000
  • Laatst online: 26-11-2021
Waarom bufferen en/of loopen als het ook in 1 query kan:

SQL:
1
2
3
4
INSERT INTO queue
(queueid, added, mailingid, recipient, sent)
SELECT null, <time>, <mailingid>, email, 'FALSE'
FROM subscribers


In je php code even de <time> en <mailingid> vervangen door de juiste waarde en klaar is klara.

Zie ook: http://dev.mysql.com/doc/refman/5.0/en/insert.html

Acties:
  • 0 Henk 'm!

  • enveekaa
  • Registratie: September 2003
  • Laatst online: 21-09 06:09
He dat is helemaal mooi :)

Acties:
  • 0 Henk 'm!

  • iH8
  • Registratie: December 2001
  • Laatst online: 17-06-2024

iH8

waarom gooi je al die output van je select niet in een array en genereer zo'n query:

SQL:
1
2
3
4
5
6
7
8
INSERT INTO
    table (col1,col2,col3,col4,col5)
VALUES
    (value1,value2,value3,value4,value5),
    (value1,value2,value3,value4,value5),
    (value1,value2,value3,value4,value5),
    (value1,value2,value3,value4,value5),
    etc...


of je gebruikt multi:

PHP:
1
2
3
4
5
6
7
$query  = "INSERT INTO table (col1,col2,col3,col4,col5) VALUES (value1,value2,value3,value4,value5);";
$query  .= "INSERT INTO table (col1,col2,col3,col4,col5) VALUES (value1,value2,value3,value4,value5);";
$query  .= "INSERT INTO table (col1,col2,col3,col4,col5) VALUES (value1,value2,value3,value4,value5);";
$query  .= "INSERT INTO table (col1,col2,col3,col4,col5) VALUES (value1,value2,value3,value4,value5);";
etc...

$mysqli->multi_query($query));
idd :) dan heb ik denk ik niets gezegd ;):D

[ Voor 10% gewijzigd door iH8 op 09-10-2008 12:01 ]

Aunt bunny is coming to get me!


Acties:
  • 0 Henk 'm!

  • enveekaa
  • Registratie: September 2003
  • Laatst online: 21-09 06:09
Hmm die query van bigbeng ziet er leuk uit maar ik krijg het niet 1 2 3 voor elkaar :)
Volgens mij mis ik ook het eea.

PHP:
1
2
3
4
5
$query = "
INSERT INTO queue queueid, added, mailingid, recipient, sent
SELECT null,'".time()."','".$_GET['mailingid']."', email, false
FROM subscribers
";


In de insert naar de tabel queue heb ik alle entries nodig in de tabel subscribers, plus was extra informatie.

[ Voor 16% gewijzigd door enveekaa op 09-10-2008 13:02 ]


Acties:
  • 0 Henk 'm!

  • Voutloos
  • Registratie: Januari 2002
  • Niet online
Dan debug je dat. INSERT INTO SELECT debuggen is simpel: Hou het eerst gewoon een select query en pas als alles goed is plemp je er insert into ... voor. :)

(en ga eerst maar het eea lezen inderdaad, met als extra topics tabel structuur, indexes, insert syntax, insert optimalisatie en sql injection... ;) )

[ Voor 33% gewijzigd door Voutloos op 09-10-2008 13:23 ]

{signature}


Acties:
  • 0 Henk 'm!

  • MueR
  • Registratie: Januari 2004
  • Laatst online: 21-09 14:53

MueR

Admin Tweakers Discord

is niet lief

Je mist niks, je hebt te veel meuk. Die link naar de docs is misschien nuttig voor je. Lees eerst even over de syntax voordat je gaat lopen prutsen, dat scheelt je 95% van het werk.

Anyone who gets in between me and my morning coffee should be insecure.


Acties:
  • 0 Henk 'm!

  • enveekaa
  • Registratie: September 2003
  • Laatst online: 21-09 06:09
Ik ken dit principe nog niet, en snap het niet :)
RTFM

Acties:
  • 0 Henk 'm!

  • XWB
  • Registratie: Januari 2002
  • Niet online

XWB

Devver
INSERT kan met één query.

PHP:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
$query = 'SELECT email FROM subscribers';
$result_subscribers = mysql_query ( $query );
$result = array ();

while ( $row =  mysql_fetch_array ( $result_subscribers ) )
    $result[] = $row['email'];

$qryData = array ();

foreach ( $query as $email )
    $qryData[] = sprintf ( '(NULL, %u, %u, "%s", "FALSE")', time (), $_GET['mailingid'], $email );

$prepare = sprintf ( 'INSERT INTO queue (queueid, added, mailingid, recipient, sent) VALUES %s', implode ( ',', $qryData ) );
mysql_query ( $prepare );


Of je kijkt naar bigbeng zijn oplossing ;)

Overigens is je script erg onveilig. GET en POST vars moet je escapen, en ook checken of ze bestaat.

[ Voor 4% gewijzigd door XWB op 09-10-2008 13:41 ]

March of the Eagles


Acties:
  • 0 Henk 'm!

  • MueR
  • Registratie: Januari 2004
  • Laatst online: 21-09 14:53

MueR

Admin Tweakers Discord

is niet lief

Jij kent je prepared statements niet Hacku.

Anyone who gets in between me and my morning coffee should be insecure.


Acties:
  • 0 Henk 'm!

  • XWB
  • Registratie: Januari 2002
  • Niet online

XWB

Devver
Waar gebruik ik dan prepared statements :? Kijk nog eens heel goed ;)

[ Voor 24% gewijzigd door XWB op 09-10-2008 14:09 ]

March of the Eagles


Acties:
  • 0 Henk 'm!

  • enveekaa
  • Registratie: September 2003
  • Laatst online: 21-09 06:09
Ik heb het inmiddels voor elkaar met één insert query.
In de eerste instantie zat ik veelste ingewikkeld te denken, maar het is kinderlijk eenvoudig.

Bedankt voor de tips :) Erg leerzaam.

PHP:
1
2
3
4
$query = "
INSERT INTO queue (queueid, mailingid, added, recipient, sent)
SELECT null,'".$_GET['mailingid']."','".time()."', email, false
FROM subscribers";

Acties:
  • 0 Henk 'm!

  • leuk_he
  • Registratie: Augustus 2000
  • Laatst online: 15-07 15:35

leuk_he

1. Controleer de kabel!

enveekaa schreef op donderdag 09 oktober 2008 @ 14:03:
PHP:
1
2
3
4
$query = "
INSERT INTO queue (queueid, mailingid, added, recipient, sent)
SELECT null,'".$_GET['mailingid']."','".time()."', email, false
FROM subscribers";
Inderdaad veel simpeler... Moet je die GET nou nog escapen?

Need more data. We want your specs. Ik ben ook maar dom. anders: forum, ff reggen, ff topic maken
En als je een oplossing hebt gevonden laat het ook ujb ff in dit topic horen.


Acties:
  • 0 Henk 'm!

  • pedorus
  • Registratie: Januari 2008
  • Niet online
enveekaa schreef op donderdag 09 oktober 2008 @ 14:03:
Ik heb het inmiddels voor elkaar met één insert query.
In de eerste instantie zat ik veelste ingewikkeld te denken, maar het is kinderlijk eenvoudig.

Bedankt voor de tips :) Erg leerzaam.

PHP:
1
2
3
4
$query = "
INSERT INTO queue (queueid, mailingid, added, recipient, sent)
SELECT null,'".$_GET['mailingid']."','".time()."', email, false
FROM subscribers";
Kijk dan ook eens naar sql injection en kijk eens naar bijvoorbeeld dit bericht over prepared statements.

Vitamine D tekorten in Nederland | Dodelijk coronaforum gesloten


Acties:
  • 0 Henk 'm!

  • paulh
  • Registratie: Juli 1999
  • Laatst online: 18-09 20:05
enveekaa schreef op donderdag 09 oktober 2008 @ 11:39:
Ik had een helder moment, de tabel queue had InnoDB als storage engine. Dit heb ik aangepast naar MyISAM en dan gaat het binnen een seconde :)
Is dat wel zo verstandig? Moet die tabel misschien niet ergens anders ook nog transacties ondersteunen? Opzich is er niks mis met InnoDB. Je kan beter kijken naar commit opties, misschien moet je autocommit uitzetten. Of juist de tabel vullen binnen een transactie.

[ZwareMetalen.com] - [Kom in aktie tegen de CO2 maffia]


Acties:
  • 0 Henk 'm!

  • osorkon!
  • Registratie: September 2006
  • Laatst online: 10-01 18:56
Hacku schreef op donderdag 09 oktober 2008 @ 14:02:
Waar gebruik ik dan prepared statements :? Kijk nog eens heel goed ;)
Bedoeld waarschijnlijk dat je een prepared statement kan gebruiken ipv van sprintf.. :)

Acties:
  • 0 Henk 'm!

  • MueR
  • Registratie: Januari 2004
  • Laatst online: 21-09 14:53

MueR

Admin Tweakers Discord

is niet lief

Hacku schreef op donderdag 09 oktober 2008 @ 14:02:
Waar gebruik ik dan prepared statements :? Kijk nog eens heel goed ;)
Klopt, dat is het hem nu juist. Nu moet MySQL alsnog 600 keer een query parsen. Voor dit soort dingen gebruik je bulk queries of prepared statements, waarbij de eerste de minste queries kost, de laatste minder processortijd voor de query parser.

Anyone who gets in between me and my morning coffee should be insecure.


Acties:
  • 0 Henk 'm!

  • Voutloos
  • Registratie: Januari 2002
  • Niet online
Vergeleken met de cost van de inserts zelf is het besparen op de query parse tijd een micro-optimalisatie. ;) Grootste besparing is iig om minstens bulk inserts te doen in plaats van idioot veel losse inserts.

{signature}


Acties:
  • 0 Henk 'm!

  • Kettrick
  • Registratie: Augustus 2000
  • Laatst online: 04:24

Kettrick

Rantmeister!

paulh schreef op donderdag 09 oktober 2008 @ 19:57:
[...]
Is dat wel zo verstandig? Moet die tabel misschien niet ergens anders ook nog transacties ondersteunen? Opzich is er niks mis met InnoDB. Je kan beter kijken naar commit opties, misschien moet je autocommit uitzetten. Of juist de tabel vullen binnen een transactie.
Je zou iig alle inserts binnen een transactie moeten doen, ik vermoed dat mysql nu voor elke insert een transactie start ( ken mysql niet erg goed, maar dat zou logisch zijn).

Acties:
  • 0 Henk 'm!

  • MueR
  • Registratie: Januari 2004
  • Laatst online: 21-09 14:53

MueR

Admin Tweakers Discord

is niet lief

Daar zijn de prepared statements van mysql nou zo handig voor, die doen alles binnen 1 transactie.

Anyone who gets in between me and my morning coffee should be insecure.

Pagina: 1