[mysql] performance bij veel columns & records

[T-MOB]

Mijn probleem is vrij simpel, ik heb een tabel in mijn database die middels een admin-tool kan worden uitgebreid met extra kolommen. Ze bestaat momenteel uit zo'n 300 kolommen en bij wijze van test-case zo'n 1000 records. Het records zal als de handel "echt" in gebruik is gaan oplopen tot zo'n 750.000. Echter... met de testcase met 1000 records loop ik al tegen een performance probleem aan.

Ik heb de volgende functie in PHP om de data naar een plat tekst bestand te dumpen:

PHP:

function dump_all()
{
    # dbconnection
    global $db; global $DBNAME;
    if (!mysql_select_db($DBNAME,$db)) { return FALSE; }
    
    # fetch column names
    $query = mysql_query('SHOW COLUMNS FROM pge_data');
    if ($result = mysql_fetch_assoc($query))
    { 

        do { $columns[] = $result['Field']; } 
        while ($result = mysql_fetch_assoc($query));

    } else { return FALSE; }

    #fetch data
    $query = mysql_query('SELECT * FROM pge_data', $db);
    if ($result = mysql_fetch_assoc($query))
    {  
        do { $data[] = implode ("|", $result); } 
        while ($result = mysql_fetch_assoc($query));
    
    } else { return FALSE; }

    $output  = implode ('|', $columns) ."\n" .implode("\n", $data);

    echo $output; //Ik ben nog aan het bouwen/debuggen aye

}

Ga ik deze benchmarken dan kom ik uit op zo'n 2s voor de huidige 1000 records. Laten we de implode's weg dan blijft er 1,5 seconde over voor het ophalen van de data. Aangezien een limit 100 in de query leidde tot 0,15s en limit 10 tot 0,015s ga ik er vanuit dat dit te extrapoleren valt naar 750.000 records in ongeveer 19 minuten

Mijn vraag is of er nog andere opties zijn dan een Cray aanschaffen om dit te laten werken. De huidge PC waarop het draait is een Authentieke K6III met 228MB ram en 1Mb L2 cache. (De uiteindelijke server zal een Sempron 2XXX+ worden met 256Mb DDR).

_{Zoekfunctie vinden strax allemaal plaats in tabellen met metadata over deze gegevens. Die zijn middels ID gelinkt aan de grote tabel waardoor alles wat er met het ding gebeurt neerkomt op een datadump}

[Kees]

zo te horen heb je indexes nodig

[gorgi_19]

Even voor mijn begrip:

256 MB geheugen voor 750.000 records?
En je slaat je data op in 1 kolom, waarbij je de data scheidt dmb | ?
Verder voer je steeds 2 sql statements uit, en niet 1, waarin je de kolommen benoemd?

[NMe]

Hmm, ik denk niet dat je al te veel opties hebt. Je kan hooguit proberen of je wat kan normaliseren, maar als je die kolommen allemaal aan hebt gemaakt in een admin scriptje, lijkt me dat vrij lastig. Maar een tabel van 300 kolommen is ook heel erg ranzig in de meeste gevallen, zo'n aantal duidt vaak op een slecht genormaliseerde DB.

Misschien dat je een snellere database server kan regelen? Oracle zou hier veel beter mee om moeten kunnen gaan.

[gorgi_19]

NMe84 schreef op 13 oktober 2004 @ 23:12:
Maar een tabel van 300 kolommen is ook heel erg ranzig in de meeste gevallen, zo'n aantal duidt vaak op een slecht genormaliseerde DB.

Normaliseren staat los van performance. Een gedenormaliseerde database heeft meer redunante date, maar kan vele malen sneller zijn dan een perfect genormaliseerde database

Misschien dat je een snellere database server kan regelen? Oracle zou hier veel beter mee om moeten kunnen gaan.

Zie dit forum; deze heeft vele miljoenen records en heeft toch een goede performance. Deze draait ook op MySQL Oracle is niet noodzakelijk.

[ Voor 26% gewijzigd door gorgi_19 op 13-10-2004 23:15 ]

[curry684]

Een tabel met 300 kolommen is gewoon per definitie een baggerdatamodel. Wat de n**k sla je erin op?

[T-MOB]

curry684 schreef op 13 oktober 2004 @ 23:15:
Een tabel met 300 kolommen is gewoon per definitie een baggerdatamodel. Wat de n**k sla je erin op?

Het gaat om een tabel met data van een hele hoop wetenschappelijke studies. Die zijn allemaal gestandaardiseerd naar ongveer 300 variabelen en hebben per studie een record of 1500 nodig voor alle gevallen. Er is bij mijn weten geen efficiëntere manier dan dit in 1 tabel stoppen....

Naast deze tabel bestaan er een aantal tabellen met metadata over de grote, daar gebeurt het zoekwerk mee. De grote dient alleen maar als container en hoeft alleen maar data te dumpen op basis van id's (incluis index idd ).

Het dumpen duurt alleen een beetje lang....

[Verwijderd]

Leuke aantallen maar het zegt verder weinig over je datamodel an sich, je zegt dat je al een tabel hebt om in te zoeken? daar krijg je id of wat dan ook uit en daarmee ga je zoeken in de grote tabel? En het zoeken in die tabel duurt zo lang?

/me volgt het niet helemaal

[T-MOB]

Verwijderd schreef op 13 oktober 2004 @ 23:33:
Leuke aantallen maar het zegt verder weinig over je datamodel an sich, je zegt dat je al een tabel hebt om in te zoeken? daar krijg je id of wat dan ook uit en daarmee ga je zoeken in de grote tabel? En het zoeken in die tabel duurt zo lang?

/me volgt het niet helemaal

De vraag heeft ook weinig te maken met het datamodel an sich... De vraag is of er een snellere manier is om

code:

SELECT * FROM table

uit te voeren, behalve snellere hardware. Dat is basically wat de functie in de TS doet...
_{zal het datamodel wel even overzichtelijk in een plaatje zetten...}

[ Voor 7% gewijzigd door T-MOB op 13-10-2004 23:40 ]

[ripexx]

Maar welk type data gebruik je? Welke velden, ga je nog selecteren op velden? Gebruik je alleen maar tekst velden of integers?

Oja SELECT * is trager dan SELECT col_name1, col_name2 maar of dat bij 300 kolommen nog steeds zo is? Zit er dan helemaal geen dubbelde data in? Kan je echt niet verder normaliseren?

Maar is preformance een issue bij een volledig export van de data? Als het een eenmalige export is dan kan dat toch best even duren toch?

[T-MOB]

ripexx schreef op 13 oktober 2004 @ 23:46:
Maar welk type data gebruik je? Welke velden, ga je nog selecteren op velden? Gebruik je alleen maar tekst velden of integers?

(Bijna) alle velden zijn varchar(4), uitzondering zijn velden die zeker (int) zijn.

Oja SELECT * is trager dan SELECT col_name1, col_name2 maar of dat bij 300 kolommen nog steeds zo is? Zit er dan helemaal geen dubbelde data in? Kan je echt niet verder normaliseren?

SELECT * was sneller dan SELECT implode(',', $columns) iig. Er zit geen dubbele data in. De data die in deze tabel staat is zegmaar een enquete met 300 vragen die aan heeel veel mensen is gesteld. Verder geen dubbele data (iig voorspelbare dubbele data...).

Maar is preformance een issue bij een volledig export van de data? Als het een eenmalige export is dan kan dat toch best even duren toch?

Op zich bij de export van data natuurlijk niet, maar als er straks in de database wordt gezocht en men heeft de helft van alle records nodig. Dan is 10 minuten wachten niet helemaal de bedoeling...

offtopic:
Dit is een globaal overzicht van de gebruikte tabellen, in de tabel data (with lots of columns) speelt het probleem... pdf

[ Voor 1% gewijzigd door T-MOB op 14-10-2004 00:12 . Reden: link was idd dood.... ]

[gorgi_19]

Sloop voor de grap voor de test eens die implode er uit; ik gok dat de performance dan beter is.

[Verwijderd]

Link = dood

edit:

Werkende link

[ Voor 84% gewijzigd door Verwijderd op 14-10-2004 00:09 ]

[Verwijderd]

T-MOB schreef op 14 oktober 2004 @ 00:05:
SELECT * was sneller dan SELECT implode(',', $columns) iig. Er zit geen dubbele data in. De data die in deze tabel staat is zegmaar een enquete met 300 vragen die aan heeel veel mensen is gesteld. Verder geen dubbele data (iig voorspelbare dubbele data...).

Weet je zeker dat je performance probleem bij je sql ligt?

Als je 'zware' functies als implode ook uit de rest van je code kunt halen, zou het zo maar eens een stuk sneller kunnen zijn.

[ripexx]

Je kan eens op de commandline kijken wat het verschil is tussen een query in PHP en direct op de Server. Dan kan je de preformance iets beter inschatten. Immers kan je ook een met commandline tools temp files creeren. Als het alleen gaat om het ophalen van data.

Verder is voor ene DB server van belang dat er een snelle toegang is tot de disken en dat je genoeg geheugen hebt om de resultsets in op te slaan. Processor power is minder relevant.

[T-MOB]

gorgi_19 schreef op 14 oktober 2004 @ 00:08:
Sloop voor de grap voor de test eens die implode er uit; ik gok dat de performance dan beter is.

jep.. met implodes duut het +/- 2s zonder implodes en output echi duurt het +/- 1,5s (met 1000 records)... Die had ik al getest, het ophalen van de kolomnamen gaat ook redelijk rap: dat duurt iets van 0.02seconden.

Overigens doet het geneuzel over mijn datamodel me wel denken. Alle s wat ik uit de tabel nodig heb is namelijk platte data met zo'n 1500 records per id. Aangezien ik altijd op ID zal selecteren kan ik dus haast net zo goed platte tekst files per id opslaan en die samenvoegen als output.

Het enige probleem dat ik hiermee zie (en waarom ik het dus in een tabel heb gestopt). Is dat het aantal kolommen uitbreidbaar is. Als er een kolom bijkomt moet er gewoon een lege cel worden gemaakt in de tekstfile. Tools die tekst-based data importeren vinden een ongelijk aantal "kolommen" doorgaans niet prettig => corrupte data...

[T-MOB]

benchmark

PHP:

<?php
if (!$CONFIG) { include 'config.inc.php'; } // dependency

function dump_all()
{
    $start = getmicrotime();
    
    # dbconnection
    global $db; global $DBNAME;
    if (!mysql_select_db($DBNAME,$db)) { return FALSE; }
    
    # fetch column names
    $query = mysql_query('SHOW COLUMNS FROM pge_data');
    if ($result = mysql_fetch_assoc($query))
    { 
      do { /* $columns[] = $result['Field']; */ } 
          while ($result = mysql_fetch_assoc($query));
    } else {
        return FALSE;
    }

    $fetched = getmicrotime();
    $time = $fetched - $start;
    echo 'cols fetched: ' .$time ."\n";

    #fetch data
    $query = mysql_query('SELECT * FROM pge_data', $db);
    if ($result = mysql_fetch_assoc($query))
    {  
      do { /* $data[] = implode ("|", $result); */} 
          while ($result = mysql_fetch_assoc($query));
    }

    /* $output  = implode ('|', $columns) ."\n" .implode("\n", $data); */

    
    $datafetched = getmicrotime();
    $time = $datafetched - $fetched;
    echo 'data fetched: ' .$time ."\n";
    $time = $datafetched - $start;
    echo 'total: ' .$time ."\n";



    echo $output;

}
header('content-type: text/plain');
dump_all();

?>

levert:

code:

cols fetched: 0.0347819328308
data fetched: 1.43099498749
total: 1.46577692032

[ Voor 10% gewijzigd door T-MOB op 14-10-2004 00:32 . Reden: layout ]

[ripexx]

preformance richt zich altijd op op invoer of uitvoer. Veel indexen zijn traag bij inserten maar maken selecties sneller. als je tenminste een index op de kolom legt waar je op selecteerd (where) .

Verder zijn er zat text file like opslag systemen maar is dat wel wat je wilt. Als je het alleen als dataware house gebruikt (opslag) en verder niets met de data gaat doen kan je het beste een zo''n plat mogelijk systeem nemen. Dus text file - mysql - oracle => snel - redelijk - traag Oracle heeft te veel overhead hiervoor itt mysql bijvoorbeeld.

Maar het uitbreiden van een text file op de juiste manier zal weer lastig en traag zijn. Verder is PHP niet de meest handige oplossing voor het ophalen van veel data. immers moet PHP ook alles cachen. Ik zou wel willen weten wat de command line client van MySQL aan preformance heeft in dit geval dus wat doet mysql> SELECT * FROM `Table`;

[ripexx]

Trouwens een uitbreiding van een mysql tabel zorgt ook voor veel overhead, net zoals een alter tabel. MySQL doet dat niet echt handig, vraag maar aan ACM en Kees

* ripexx zit niet op te letten

[ Voor 10% gewijzigd door ripexx op 14-10-2004 00:39 ]

[Verwijderd]

T-MOB schreef op 13 oktober 2004 @ 23:07:

PHP:

    # fetch column names
    $query = mysql_query('SHOW COLUMNS FROM pge_data');
    if ($result = mysql_fetch_assoc($query))
    { 

        do { $columns[] = $result['Field']; } 
        while ($result = mysql_fetch_assoc($query));

    } else { return FALSE; }

Even terzijde, ik zou dit vervangen door iets als:

PHP:

    # fetch column names
    $query = mysql_query('SHOW COLUMNS FROM pge_data');
    if(intval(mysql_num_rows($query)) > 0){
        return FALSE;
    }
    while($result = mysql_fetch_assoc($query))
    { 
        $columns[] = $result['Field']; } 
    }

[ripexx]

Ik denk niet dat dat nu zo heel relevant is voor de TS, het gaat om de preformance van met name MySQL bij zeer grote tabellen. Zowel qua aantal records als het aantal kolommen. Verder is do .. while niet veel trager dan bijvoorbeeld while, zou kunnen maar is niet te verwachten, maar het blijft PHP

[T-MOB]

Sow. duurde ff voordat ik SQL van de commandline weer 's voor mekaar kreeg. Maar het resultaat was

code:

1005 rows in set (0.38s)

. Wel sneller dus. Maar ik vrees dat verwerken via de commandline dat voordeel weer teniet doet.

Anyway, ik ga zo slapen. Morgen (mocht ik tijd hebben... familiefeest van het 'dinnetje ) denk ik maar nadenken over een handigere oplossing. Uit andere tabellen weet ik welke kolommen bestaan. Een iets snellere methode moet dus mogelijk zijn...

[Verwijderd]

T-MOB schreef op 14 oktober 2004 @ 00:05:
[...]

(Bijna) alle velden zijn varchar(4), uitzondering zijn velden die zeker (int) zijn.


[...]


SELECT * was sneller dan SELECT implode(',', $columns) iig. Er zit geen dubbele data in. De data die in deze tabel staat is zegmaar een enquete met 300 vragen die aan heeel veel mensen is gesteld. Verder geen dubbele data (iig voorspelbare dubbele data...).

Begrijp ik nou goed dat jij per kolom een vraag hebt staan? En dat elke rij een referentie is naar een persoon die de vragenlijst heeft ingevuld?

Zo ja, dan kan je het design beter splitsen in een aantal tabellen; vragen, enquete, enquete_vragen.

[NMe]

Verwijderd schreef op 14 oktober 2004 @ 01:19:
Begrijp ik nou goed dat jij per kolom een vraag hebt staan? En dat elke rij een referentie is naar een persoon die de vragenlijst heeft ingevuld?

Zo ja, dan kan je het design beter splitsen in een aantal tabellen; vragen, enquete, enquete_vragen.

Wat ons weer terugbrengt naar het normaliseren wat ik hier al eerder aanhaalde, wat weer zorgt voor 3 kleine tabellen (qua aantal kolommen), met een iets groter aantal records, wat bij mijn weten de tabel kleiner maakt, en de zooi in dit geval sneller moet maken. Ik kan me niet voorstellen dat MySQL 300 velden fijn vindt, en ik verwacht dat een genormaliseerde database beter resultaat levert.

[whoami]

gorgi_19 schreef op 13 oktober 2004 @ 23:14:
[...]

Normaliseren staat los van performance. Een gedenormaliseerde database heeft meer redunante date, maar kan vele malen sneller zijn dan een perfect genormaliseerde database

Afhankelijk waarvoor je 'm gebruikt.
Gebruik je een gedenormaliseerde DB voor een OLTP database, dan zal je problemen hebben, een gedenormileseerde DB gebruiken voor een datawarehouse kan dan weer wel perfect.

[ACM]

Misschien wist je het nog niet, maar als je die select * from tabel in mysql uitvoert, wordt de totale dataset standaard in het geheugen van de client geladen... Zeker met een doos die al krap in het geheugen zit wordt dat natuurlijk helemaal een performancedrama. Als je perse alle records af wilt gaan (misschien moet je daar wat aan doen), neem dan mysql_unbuffered_query ipv mysql_query als executie-functie. Die slaat de boel niet op in het geheugen (mysql_num_rows werkt dan niet geloof ik, maar die check daarop is gewoon zinloos, want je doet hetzelfde al met de eerste mysql_fetch_assoc)

[T-MOB]

Verwijderd schreef op 14 oktober 2004 @ 01:19:
[...]
Begrijp ik nou goed dat jij per kolom een vraag hebt staan? En dat elke rij een referentie is naar een persoon die de vragenlijst heeft ingevuld?

Zo ja, dan kan je het design beter splitsen in een aantal tabellen; vragen, enquete, enquete_vragen.

Ergens klinkt dat wel heel logisch ja, de tabellen 'enquete' en 'vragen' bestaan al, dus zo ingrijpend wordt dit niet eens.

NMe84 schreef op 14 oktober 2004 @ 01:28:
[...]
Wat ons weer terugbrengt naar het normaliseren wat ik hier al eerder aanhaalde, wat weer zorgt voor 3 kleine tabellen (qua aantal kolommen), met een iets groter aantal records, wat bij mijn weten de tabel kleiner maakt

Nou ja... iets groter aantal kolommen . De tabel zal volgens mij juist groter worden aangezien ik per cel nu twee extra cellen nodig heb om aan te geven welke enquete / vraag erbij hoort. Nu zal niet elke enquete alle vragen hebben dus uiteindelijk zal het wel ongeveer even groot worden.

In ieder geval maar even proberen. Benchmarks zullen het uitwijzen.

[T-MOB]

OK, tests gedraaid. Ik heb voor de test de volgende tabellen bijgemaakt:

code:

respondents:
 - respid
 - datasetid

data_flat
 - respid
 - variableid
 - value

(hierbij geldt dataset ~ "enquete" en een variable ~ "vraag").

Een SELECT * op dataflat duurt ongeveer 2s tegen 1,5s op de oude lompe tabel. Normaliseren helpt in dit geval dus weinig...

[-=bas=-]

Je zou alle data in 1 kolom kunnen stoppen zoals je volgens mij zelf al suggereert.
Het ophalen van data uit 1 kolom a.h.v een id is peanuts vergeleken met 300 kolommen.
Het expanderen van data uit 1 kolom kan misschien wel veel sneller als je geen PHP gebruikt maar bv een Perl script of een C geschreven CGI-script.
Expanderen van data kost vrijwel geen tijd als het efficient gebeurd.

Je zou eens naar Cache (kasjee) kunnen kijken omdat die een veel betere performance heeft dan Oracle of Mysql. (http://www.intersystems.com/cache/)
Maar dat is wel een erg rigoreuze oplossing.

Misschien zit het probleem niet echt bij jou maar meer in de manier waarop de data opgeslagen is. Een platte DB met 300 kolommen klinkt natuurlijk wel erg gemakkelijk.

[ACM]

T-MOB schreef op 15 oktober 2004 @ 02:46:
Een SELECT * op dataflat duurt ongeveer 2s tegen 1,5s op de oude lompe tabel. Normaliseren helpt in dit geval dus weinig...

Zolang je er op blijft hameren om alle data in je client te laden, boeit het inderdaad niet hoe je het opslaat en zal het altijd traag blijven. Dat heb ik al eerder uitgelegd in deze thread. Het enige waar je wat winst mee kunt halen is die mysql_unbuffered_query.

Als je dus al die data gewoon nodig hebt, dan zal je je er bij neer moeten leggen dat er geen grote winsten in te behalen zijn. Als je al die data ophaalt om er daarna in je client-app nog allerlei filtering overheen te halen, dan ben je natuurlijk niet zo handig bezig, want dat is nou net een van de sterke punten van de database.

[T-MOB]

ACM schreef op 15 oktober 2004 @ 09:50:
[...]
Als je al die data ophaalt om er daarna in je client-app nog allerlei filtering overheen te halen, dan ben je natuurlijk niet zo handig bezig, want dat is nou net een van de sterke punten van de database.

Ok, die tip was reuze handig, aangezien ik de data altijd als regels nodig heb waarin cellen gescheiden worden door *een* speciaal teken even gekeken naar een MySQL functie die dat doet. De query opbouwen met CONCAT gaat zo'n 4 maal zo snel .

De volgende functie geeft dezelfde output als de functie in TS (alleen sneller):

PHP:

function dump_new()
{
    # dbconnection
    global $db; global $DBNAME;
    if (!mysql_select_db($DBNAME,$db)) { return FALSE; }
    
    # fetch column names
    $query = mysql_query('SHOW COLUMNS FROM pge_data');
    if ($result = mysql_fetch_assoc($query))
    { 
      do { $columns[] = $result['Field']; } 
          while ($result = mysql_fetch_assoc($query));
    } else {
        return FALSE;
    }

    $CONCAT = '(`' .implode("`,'|',`", $columns) .'`)';

    #fetch data
    $query = mysql_query('SELECT CONCAT' .$CONCAT .' AS line FROM pge_data', $db);
    if ($result = mysql_fetch_assoc($query))
    {  
      do { $data[] = $result['line']; } 
          while ($result = mysql_fetch_assoc($query));
    }
    
    $output  = implode ('|', $columns) ."\n" .implode("\n", $data);

    echo $output;
}

Eenieder bedankt voor het meedenken! Verdere optimalisatie is immer welkom.

[ Voor 7% gewijzigd door T-MOB op 15-10-2004 15:45 . Reden: layout... ]