[MySQL/PHP] Snel naar tabellen schrijven.

[Onbekend]

Met enige regelmaat ( 1x in de 10 minuten ongeveer) worden van 2 MySQL tabellen 95% van alle gegevens gewijzigd.

Deze 2 tabellen bevatten nu elk 50 records met elk 16 verschillende gegevens.
Het schrijven van alle gegevens in de MySQL tabel kost veel tijd.

En dit is alleen nog maar een kleine test. Ik verwacht straks minimaal 5.000 records per tabel te hebben. Dit zou kunnen resulteren dat MySQL alleen maar wordt geupdated en andere delen van de site helemaal geen toegang meer krijgen tot de tabellen.

Wat is de snelste manier bij MySQL om zoveel gegevens in 1 keer te wijzigen ?

[NMe]

Moment... elke 10 minuten update je 100 records? Dat stelt toch niets voor? Zelfs als je MyISAM gebruikt en je indexen fout staan moet 100 updates nog steeds snel genoeg gaan...

Als je meer moet updaten dan selecten: gebruik InnoDB voor je tabellen. Moet je meer selecten dan muteren: gebruik MyISAM.

[Gomez12]

Ook onbekend schreef op zaterdag 22 juli 2006 @ 23:19:
Met enige regelmaat ( 1x in de 10 minuten ongeveer) worden van 2 MySQL tabellen 95% van alle gegevens gewijzigd.

Deze 2 tabellen bevatten nu elk 50 records met elk 16 verschillende gegevens.
Het schrijven van alle gegevens in de MySQL tabel kost veel tijd.

En dit is alleen nog maar een kleine test. Ik verwacht straks minimaal 5.000 records per tabel te hebben. Dit zou kunnen resulteren dat MySQL alleen maar wordt geupdated en andere delen van de site helemaal geen toegang meer krijgen tot de tabellen.

Wat is de snelste manier bij MySQL om zoveel gegevens in 1 keer te wijzigen ?

Is het echt nodig dat je alle gegevens verandert, kan je niet gewoon de nieuwe toevoegen.
En gaat MySQL echt onderuit of is het maar een vermoeden van jou???
Zijn deze tabellen ook muteerbaar vanuit de klant ( Anders kan je een smerige hack gebruiken door alle gegevens te updaten in aparte tabellen en daarna de aparte tabellen te moven over de oude tabellen, dan is alleen je move actie even ingrijpend ( let op alleen myisam ) )

Definieer trouwens eens veel tijd...
Want 100 records met 16 gegevens kost hier geen tijd.

Maar waar je naar kan kijken zijn :
Gebruik alleen vaste velden met correcte omschrijving dus wel : char(16), niet varchar (20) als je gegevens 16 tekens lang kunnen zijn.
Vermijd indexen zoveel mogelijk. Indexen kosten tijd met het updaten / nieuw toevoegen van gegevens.
Indien mogelijk : gooi voor het updaten je index uit, dan een mass update, dan je indexen weer aan, nu worden in 1x al je indexen opnieuw aangemaakt ipv 100x indexen bijwerken.
Zorg dat je server snel genoeg is ( kijk eens met taskmanager / top of mysql 100% cpu pakt ( nieuwe comp )/ 100% schijfbelasting pakt ( nieuwe raid config ) )
Gebruik geen innodb maar gewoon myisam ( ervanuit gaande dat als er iets beschadigd het niet erg is als je 10 min zonder gegevens zit )
Als 95% verandert kan je dit misschien ophogen naar 100%, dan kan je gewoon tabel verwijderen en opnieuw aanmaken, dit is sneller dan het updaten van 95% van de gegevens.

Maar bovenal.waar zit die laatste 5% in??? Want als die 5% gecheckt moet worden met de vorige set gegevens dan ben je daar langer mee bezig dan als je de hele tabel leeggooit en dan 100% vult, scheelt je een check.

En denk eens na of echt de gegevens gewijzigd moeten worden, of dat je niet gewoon alles kan toevoegen ( en alleen de laatste 10 minuten tonen ) en dan 1x per uur alles weggooien. Insert is goedkoper dan update enz.

[Onbekend]

Bedankt voor de uitleg. Ik zal me wat meer gaan verdiepen in MySQL. Vooral met snelheden van database bewerkingen.

Nu doet hij er zo'n 8 seconden over om de gegevens te schrijven. Ik heb een HD van 4500 rpm en die staat dan constant te rammelen.

Elke record heeft een ID. En dat ID blijft het zelfde. De rest veranderd; dus eigenlijk 93,75%.

[StevenK]

Ook onbekend schreef op zondag 23 juli 2006 @ 08:33:
Nu doet hij er zo'n 8 seconden over om de gegevens te schrijven. Ik heb een HD van 4500 rpm en die staat dan constant te rammelen.

Hoe schrijf je dan die gegevens naar de database ?

[Swerfer]

Ook onbekend schreef op zondag 23 juli 2006 @ 08:33:
Nu doet hij er zo'n 8 seconden over om de gegevens te schrijven. Ik heb een HD van 4500 rpm en die staat dan constant te rammelen.

Er zit iets behoorlijk fout met jouw MySql of de manier van updaten. 8 seconden is idioot lang.

Heb je wel genoeg geheugen in je PC? Het lijkt erop dat de updates direct op de HD plaats vind, en niet eerst in het geheugen.

Wat voor velden zitten er in je tabellen? Zitten daar bijvoorbeeld afbeeldingen in of andere binaries?

[Onbekend]

Ik zit hier op een laptop met 368 MB RAM en een 1GHz processor.

Ik heb een functie geschreven waarmee ik dynamische tabellen compleet kan schrijven:

PHP:

function CreateDatabaseConnection()
    {
        global $DBConnectionLink;
        
        if ( !($DBConnectionLink = @mysql_connect(dbhost, dbuser, dbpasswd) ))  // Open connection
        {
            print _ConnectionFail;   
            exit;
        }
    
        if ( !mysql_select_db(dbname) ) // Open database
        {
            print _DatabaseFail;     
            exit;
        }       
    }
    
    function SQLQuery($CurrentTable)
    {
        global $SQLquery;
        if ( !($SQLquery = mysql_query( "SELECT * FROM $CurrentTable" )) ) // Run SQL query
        {
            print (_SQLQueryFail);
            exit;
        }
    }
    
    function CloseDatabaseConnection()
    {
        global $SQLquery, $DBConnectionLink;
        
        if (!mysql_free_result($SQLquery)) // Set result free
        {
            print (_SQLQueryCloseFail);
            exit;
        }       
        
        if (!mysql_close($DBConnectionLink)) // Close connection
        {
            print (_ConnectionCloseFail);
            exit;
        }       
    }

    function WriteTable($CurrentTable,&$TableArray,&$TableArrayIndex)
    {
        global $SQLquery;

        for( $Teller1 = 0 ; $Teller1 < Count( $TableArray ) ; $Teller1++ )
        {   
            for( $Teller2 = 1 ; $Teller2 < Count( $TableArray[0] ) ; $Teller2++ ) // Locatie 0 is de ID.
            {   
                
                if (!mysql_query("UPDATE ".$CurrentTable." SET ".$TableArrayIndex[0][$Teller2]."='".$TableArray[$Teller1][$Teller2]."' WHERE ID='".$TableArray[$Teller1][0]."' "))
                {
                    echo mysql_error();
                    exit;
                }               
            }
        }
    }

En dit voer ik uit:

PHP:

    CreateDatabaseConnection(); 
    SQLQuery(tabelnaam);
    WriteTable(tabelnaam, &$completetabel, &$kolomnamen);
    CloseDatabaseConnection();

Ik heb ook al regel voor regel geprobeert te schrijven i.p.v. de cellen afzonderlijk, maar dat maakt geen verschil in tijd. Ruim 8 seconden staat gewoon m'n HD te rammelen!

[Sybr_E-N]

Je gaat me niet vertellen dat ze dat zo in productie gaat zetten (met die hardware)! Of is het een hobbydingetje?

Ik zie dat je in een loopje loopt te updaten, kun je niet beter je queries in een batch updaten? Dat lijkt me sneller gaan dan elke keer weer op en neer naar de database. (PS, je doet overal print() behalve in WriteTable als iets fout gaat. Het lijkt me handig, om als het fout gaat, te weten welk statement fout ging)

[ Voor 6% gewijzigd door Sybr_E-N op 23-07-2006 13:56 ]

[Onbekend]

Met m'n laptop kan ik lekker lokaal testen. Uiteindelijk komt alles op een echte server te staan.

In een batch updaten zou mogelijk moeten zijn, maar daar heb ik nog geen ervaring mee. Maar zou dat dan véél sneller zijn ?


Als iets in WriteTable fout gaat, wordt de melding van mysql_error() weergegeven.
In de tellers kàn het nooit fout gaan. Die contoles zitten elders in het programma.
Ik gebruik deze manier omdat ik nog kolommen wil toevoegen en verwijderen. En omdat de positie van elke kolom kan veranderen geef ik hem een array met kolomnamen mee.

[Verwijderd]

EEKS!!!
Kijk 's naar die WriteTable function! Je doet nu een update van ieder veld afzonderlijk. Stel dat je tabel 20 velden heeft, dan zijn dat nu 19 update queries teveel. Logisch dat 't dan 8 seconden kan gaan duren...

[Verwijderd]

Is het niet sneller om je tabel leeg te maken en dan met een INSERT query de tabel weer vol te zetten? Als je toch alleen je IDs wil behouden...

[NMe]

Verwijderd schreef op zondag 23 juli 2006 @ 14:41:
Is het niet sneller om je tabel leeg te maken en dan met een INSERT query de tabel weer vol te zetten? Als je toch alleen je IDs wil behouden...

Waarom zou dat sneller gaan dan simpelweg één update-query per record? Wat hier fout gaat is die loop, waardoor er 14 queries per record teveel uitgevoerd worden.

Verder zijn er nog wat optimalisaties mogelijk door zeker te zijn dat de indexen goed staan, en op betere hardware draait het sowieso een stuk sneller.

[Soultaker]

Je moet ook alles in één transactie stoppen. Nu moet de databaseserver na elke wijziging de boel comitten; dat is ook traag, zeker aangezien je voor elke afzondelijke cel een query hebt.

Verder is het inderdaad zaak om het aantal queries wat in te perken. Op z'n minst hele regels schrijven dus. Bovendien kunnen prepared statements helpen (zie die mysqli module van PHP), maar dat bespaart alleen processortijd.

En erm, de hardware is echt het probleem niet. Ik draai zelf een databaseserver op een Celeron 300 MHz met evenveel geheugen, en hoewel ik er GoT niet op zou draaien, kan 'ie echt wel een paar duizend regels updaten in een paar seconden.

[ Voor 29% gewijzigd door Soultaker op 23-07-2006 14:57 ]

[Verwijderd]

-NMe- schreef op zondag 23 juli 2006 @ 14:47:
[...]

Waarom zou dat sneller gaan dan simpelweg één update-query per record? Wat hier fout gaat is die loop, waardoor er 14 queries per record teveel uitgevoerd worden.

Verder zijn er nog wat optimalisaties mogelijk door zeker te zijn dat de indexen goed staan, en op betere hardware draait het sowieso een stuk sneller.

Je hebt gelijk, mijn fout. Ik heb het even gebenchmarked en UPDATE is ongeveer 3 keer zo snel als TRUNCATE+INSERT (zowel afzonderlijke inserts als een grote insert).

Test gedaan op MyISAM tabel met 4-byte ID veld en 15x 200 byte VARCHAR velden, elke kolom bevat dezelfde gegevens (die wel iedere run anders is). 10000 rijen:
TRUNCATE+INSERTs klokt 3.519 seconden
UPDATE klokt 1.205 seconden

[ Voor 3% gewijzigd door Verwijderd op 23-07-2006 18:23 ]

[pietje63]

Verwijderd schreef op zondag 23 juli 2006 @ 14:24:
EEKS!!!
Kijk 's naar die WriteTable function! Je doet nu een update van ieder veld afzonderlijk. Stel dat je tabel 20 velden heeft, dan zijn dat nu 19 update queries teveel. Logisch dat 't dan 8 seconden kan gaan duren...

Volgens mij is dit de meest nuttige reactie in het topic.. maar er wordt niet echt op gereageerd.. Als je dit aanpast verwacht ik dat het 15x zo snel gaat, dus zit je op een halve seconde (nog steeds erg veel).

Ter vergelijking een update van in totaal 6400 records die ik voor een website heb gaat normaal gesproken binnen een seconde (inclusief uitlezen csv bestanden en alles). (en dit is ook via een ranzige, minder snelle, truncate+insert)

[ Voor 7% gewijzigd door pietje63 op 23-07-2006 21:07 ]

[Onbekend]

Ik heb de cel-voor-cel code vervangen door rij-voor-rij code.

PHP:

    function WriteTable($CurrentTable,&$TableArray,&$TableArrayIndex)
    {
        global $SQLquery;
    
        for ( $Teller2 = 0 ; $Teller2 < Count( $TableArray ); $Teller2++)
        {
            $TextString = 'UPDATE `'.$CurrentTable.'` SET ';
            
            for ( $Teller1 = 1 ; $Teller1 < Count( $TableArrayIndex[0] ); $Teller1++)
            {
                if ( $Teller1 != 1 ) $TextString = $TextString.',';
                $TextString = $TextString.' `'.$TableArrayIndex[0][$Teller1].'`=\''.$TableArray[$Teller2][$Teller1].'\' ';
            }
            
            $TextString = $TextString.' WHERE `ID`='.$TableArray[$Teller2][0].';';
            //echo $TextString.'<br>';
            if (!mysql_query($TextString))
            {
                echo mysql_error();
                exit;
            }           
        }       
    
            
    }

Dit gaat inderdaad een stuk sneller. Maar zoals hierboven gezegd doe ik hier gemiddeld nu 0,45 seconden over. Hoewel dit sneller is dan de vorige code denk ik dat dat nog sneller kan.
Op welke verwerkingstijd zou ik ongeveer uit moeten komen ?

De tabel bestaat trouwens uit 1x Text, 1 Binair ,4x Int en 12x BigInt cellen.

[Soultaker]

Nogmaals: stop die update eens in één transactie!

Mensen roepen hier altijd direct dat afzonderlijke queries traag zijn en dat je dus het aantal queries moet minimaliseren. Dat is maar ten dele waar. In auto-commit mode levert het reduceren van het aantal update statements wel snelheidswinst op, maar dat komt vooral doordat er simpelweg minder vaak gecommit wordt.

[bigbeng]

MyISAM ondersteunt toch helemaal geen transacties? Maakt dat die autocommit vlag niet automatisch tot een dummy?

[DexterDee]

Misschien nog een andere tip, als die gegevens niet kritiek zijn en opnieuw opgebouwd / gecalculeerd kunnen worden, dan kun je een memory table overwegen. Deze bestaan alleen in het geheugen van MySQL en niet op disk en zijn (op blackhole na ) het allersnelste voor een hoog volume aan queries. Met circa 5000 records is dat een peulenschil, gezien het 'datamodel' nog geen 1 Mb aan geheugenruimte. Als MySQL herstart of stopt zijn de gegevens wel weg uiteraard en zul je eerst een PHP script moeten aanroepen om de gegevens in te laden.

[Sybr_E-N]

bigbeng schreef op maandag 24 juli 2006 @ 09:03:
MyISAM ondersteunt toch helemaal geen transacties? Maakt dat die autocommit vlag niet automatisch tot een dummy?

Als ik deze pagina bekijk klopt het dat MyISAM geen transacties ondersteund. Wil je dat wel dan moet je overschakelen op bijvoorbeeld innodb.

[BCC]

MySQL ondersteund ook meedere insert queries in één query. Google hier eens op. Als je in PHP die dingen per 100 gaat groeperen en dan 1x inserten, gaat het een STUK sneller..

[ Voor 4% gewijzigd door BCC op 24-07-2006 13:10 ]

[Onbekend]

BCC schreef op maandag 24 juli 2006 @ 12:25:
MySQL ondersteund ook meedere insert queries in één query. Google hier eens op. Als je in PHP die dingen per 100 gaat groeperen en dan 1x inserten, gaat het een STUK sneller..

Ik vind wel resultaten over het selecteren van tabellen en updaten in meerdere tabellen, maar niet de update query.

[BCC]

Ook onbekend schreef op maandag 24 juli 2006 @ 20:44:
[...]
Ik vind wel resultaten over het selecteren van tabellen en updaten in meerdere tabellen, maar niet de update query.

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

code:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

[XWB]

Een trucje dat ik ooit van iemand geleerd had om heel veel records in 1x te inserten:

PHP:

$insert = array();
$data = array(1, 2, 3, 4, 5); //array met je data

foreach ($data as $value)
{
    array_push($insert, '("' . $value . '")');
}

$insert = implode(',', $insert);

mysql_query('INSERT INTO table (field) VALUES ' . $insert);

[BCC]

Hacku schreef op maandag 24 juli 2006 @ 21:06:
Een trucje dat ik ooit van iemand geleerd had om heel veel records in 1x te inserten:

Trucje? Dat is toch exact hetzelfde als ik uit de manual voorstelde ? Het ({expr | DEFAULT},...),(...),... stukje

[ Voor 9% gewijzigd door BCC op 24-07-2006 22:30 ]

[Onbekend]

Het is 't me eindelijk gelukt. Helaas niet door de update query wat ik eerst wilde.
Maar ik leeg de tabel eerst en vul hem daarna compleet in.


Stel dat meerdere gebruikers tegelijk de zelfde pagina opvragen. Wordt elke aanvraag achter elkaar uitgevoerd ? Of tegelijkertijd ?

Als dat tegelijkertijd is, dan moet ik een paar beveiligingen inbouwen om te voorkomen dat er een lege tabel wordt ingelezen.