[database] Juiste indexen leggen*

[Verwijderd]

Hallo mensen,

ik zit eventjes te kijken naar de performance van mijn database. Het gaat om een PostgreSQL database dat een radiuslog bijhoud.

De database:

code:

radius=> \dt
      List of relations
    Name    | Type  | Owner  
------------+-------+--------
 gebruikers | table | radius
 groepen    | table | radius
 nummers    | table | radius
 radacct    | table | radius

De SQL code moet er nu voor zorgen dat er een mooi overzichtje gedraaid gaat worden voor de groepen, per gebruiker, per nummer en dan per dag. Real-time dus.

Met een gewone

code:

select count(*) from radacct

is de db al een seconde of 3 bezig.

De indices die ik nu op de tabellen heb aangelegd zijn:

code:

            List of relations
          Name           | Type  | Owner  
-------------------------+-------+--------
 gebruiker_id            | index | radius
 gebruikers_pkey         | index | radius
 groep_id                | index | radius
 groepen_pkey            | index | radius
 nummer_mapped           | index | radius
 nummers_pkey            | index | radius
 radacct_calledstationid | index | radius
 radacct_pkey            | index | radius
 radacct_test            | index | radius

Een maandoverzicht voor alle groepen met alle gebruikers, per nummer duurt al 23,8 seconden. Om nog maar niet te spreken over een specificatie per dag: 61,3 seconden.

De SQL code bestaat uit een MAX(), een COUNT(), een AVG() en een SUM() functie die uitgegroepeerd wordt naar nummer, met beperkingen in de datums (data is zo verwarrend ).

Naar mijn idee duurt dit veeeel te lang. Met goede indices lijkt mij dat een dergelijk overzicht er in een seconde of 5 moet staan...

De hardware:

code:

Dual Intel P3 933 op een intel OR840 moederbord
512 RDRAM
U160 SCSI disks
slackware 8.1, kernel 2.4.19
PostgreSQL: 7.2.2.

Iemand suggesties over het aanleggen van andere indices? Of over andere oplossingen?

[ Voor 0% gewijzigd door Verwijderd op 23-09-2002 12:08 . Reden: hardware/software config toegevoegd ]

[whoami]

Hoe zien jouw queries eruit?

Je legt het best indexen op de columns waar er op gezocht wordt, en waar er op gesorteerd wordt.

[ACM]

Postgresql heeft een _zeer_ uitgebreide explain, maak daar uitvoerig gebruik van.
Explain analyze laat je ook nog eens zien hoe snel de schatting van de uitvoer tijd is.

En je hebt, uiteraard, natuurlijk wel regelmatig de database gevacuum'ed he?
liefst volgens 'vacuum analyze' of zelfs 'vacuum full analyze'

Lees ook de postgresql documentatie eens door, hierover: http://www.postgresql.org...php?performance-tips.html
en
http://www.postgresql.org/idocs/index.php?maintenance.html

Als je al vacuum's en analyze's uitgevoerd hebt moet je eens de output van explain analyze [jequery] posten

[Verwijderd]

zo, eerst de layout maar een wat aangepast

de vacuum's zijn allemaal netjes gedraaid ik bleek mij zelfs vergist te hebben in de 23,8 seconden, dit moet 29,5 seconden zijn

De explain analyze van [mijnquery] (er zijn er trouwens meerdere nodig, afhankelijk van de hoeveelheid detail je in het overzicht van de log wil hebben):

code:

NOTICE:  QUERY PLAN:

Sort  (cost=34129.46..34129.46 rows=6998 width=50) (actual time=9339.09..9339.10 rows=31 loops=1)
  ->  Aggregate  (cost=32677.61..33552.30 rows=6998 width=50) (actual time=7802.32..9338.75 rows=31 loops=1)
        ->  Group  (cost=32677.61..32852.55 rows=69975 width=50) (actual time=7768.09..8193.42 rows=63874 loops=1)
              ->  Sort  (cost=32677.61..32677.61 rows=69975 width=50) (actual time=7768.06..7888.75 rows=63874 loops=1)
                    ->  Merge Join  (cost=24421.75..25471.90 rows=69975 width=50) (actual time=4334.93..6197.78 rows=63874 loops=1)
                          ->  Sort  (cost=28.48..28.48 rows=213 width=20) (actual time=5.13..5.35 rows=213 loops=1)
                                ->  Merge Join  (cost=16.84..20.25 rows=213 width=20) (actual time=1.95..3.17 rows=213 loops=1)
                                      ->  Sort  (cost=4.48..4.48 rows=83 width=4) (actual time=0.54..0.60 rows=83 loops=1)
                                            ->  Seq Scan on gebruikers  (cost=0.00..1.83 rows=83 width=4) (actual time=0.08..0.28 rows=83 loops=1)
                                      ->  Sort  (cost=12.37..12.37 rows=213 width=16) (actual time=1.38..1.53 rows=213 loops=1)
                                            ->  Seq Scan on nummers  (cost=0.00..4.13 rows=213 width=16) (actual time=0.08..0.69 rows=213 loops=1)
                          ->  Sort  (cost=24393.26..24393.26 rows=69975 width=30) (actual time=4328.23..4468.58 rows=68266 loops=1)
                                ->  Seq Scan on radacct  (cost=0.00..17694.28 rows=69975 width=30) (actual time=3.04..1895.68 rows=68266 loops=1)
Total runtime: 9347.07 msec

EXPLAIN

Voor een gemiddelde maand zijn er een 80.000 records in de log. De testdatabase is nu een kleine 380.000 records groot en groeit meestal uit tot een anderhalf miljoen per jaar.

De query:

code:

select
 to_char(datetime(acctstarttime),'YYYY-MM-DD') as dag,
 count(acctstarttime) as aantal,
 sum(acctsessiontime) as totaal,
 max(acctsessiontime) as maximum,
 avg(acctsessiontime) as gemiddeld

from
 gebruikers

inner join
 nummers
on
 gebruikers.gebruiker_id = nummers.gebruiker_id

inner join
 radacct
on
 nummers.nummer_mapped = radacct.calledstationid

where
 acctstarttime between '2002-01-01 00:00:00' and '2002-01-31 23:59:59'

group by
 dag

order by
 dag desc

en met een andere maand (maart):

code:

NOTICE:  QUERY PLAN:

Sort  (cost=34129.46..34129.46 rows=6998 width=50) (actual time=9339.09..9339.10 rows=31 loops=1)
  ->  Aggregate  (cost=32677.61..33552.30 rows=6998 width=50) (actual time=7802.32..9338.75 rows=31 loops=1)
        ->  Group  (cost=32677.61..32852.55 rows=69975 width=50) (actual time=7768.09..8193.42 rows=63874 loops=1)
              ->  Sort  (cost=32677.61..32677.61 rows=69975 width=50) (actual time=7768.06..7888.75 rows=63874 loops=1)
                    ->  Merge Join  (cost=24421.75..25471.90 rows=69975 width=50) (actual time=4334.93..6197.78 rows=63874 loops=1)
                          ->  Sort  (cost=28.48..28.48 rows=213 width=20) (actual time=5.13..5.35 rows=213 loops=1)
                                ->  Merge Join  (cost=16.84..20.25 rows=213 width=20) (actual time=1.95..3.17 rows=213 loops=1)
                                      ->  Sort  (cost=4.48..4.48 rows=83 width=4) (actual time=0.54..0.60 rows=83 loops=1)
                                            ->  Seq Scan on gebruikers  (cost=0.00..1.83 rows=83 width=4) (actual time=0.08..0.28 rows=83 loops=1)
                                      ->  Sort  (cost=12.37..12.37 rows=213 width=16) (actual time=1.38..1.53 rows=213 loops=1)
                                            ->  Seq Scan on nummers  (cost=0.00..4.13 rows=213 width=16) (actual time=0.08..0.69 rows=213 loops=1)
                          ->  Sort  (cost=24393.26..24393.26 rows=69975 width=30) (actual time=4328.23..4468.58 rows=68266 loops=1)
                                ->  Seq Scan on radacct  (cost=0.00..17694.28 rows=69975 width=30) (actual time=3.04..1895.68 rows=68266 loops=1)
Total runtime: 9347.07 msec

[ Voor 0% gewijzigd door Verwijderd op 23-09-2002 12:29 . Reden: query erbij ]

[ACM]

Doe er dan ajb de query nog bij

Hier gaat het al mis (je moet de explain van onder naar boven lezen )

code:

->  Sort  (cost=24393.26..24393.26 rows=69975 width=30) (actual time=4328.23..4468.58 rows=68266 loops=1)
                                ->  Seq Scan on radacct  (cost=0.00..17694.28 rows=69975 width=30) (actual time=3.04..1895.68 rows=68266 loops=1)

Een sequential scan op de tabel die dus volgens de planner al gauw de helft van de tijd kost.
Wat doe je precies op die radacct ?

Probeer trouwens ook explain's met verschillende invoerwaarden, kan zijn dat ie dan zomaar ineens een andere (betere, doordat er net iets minder records mee zijn ofzo) keuze maakt.
Vervolgens kan je de foute keuze "verbieden" (bijv SET ENABLE_MERGE_JOIN=OFF aanroepen voor je query) zodat ie alsnog een betere keus maakt.

[whoami]

Probeer eens die BETWEEN om te zetten naar >= en <= . (Misschien gebruikt between geen indexen).

Converteert het DBMS die data niet naar strings (staan tussen quotes), waardoor de indexen op die velden niet kunnen gebruikt worden?

[edit]: Liggen er geen indexen op die actstarttime columns? Dat verklaart al direct waarom er een sequential scan moet uitgevoerd worden en waardoor uw query dan zo traag is.
Als er geen index op die column ligt, zal de databank iedere rij moeten gaan controleren -> sequential scan. Als je er een index oplegt, dan moet hij dat niet doen en kan hij 'knippen'.

[cameodski]

whoami schreef op 23 september 2002 @ 12:52:
Probeer eens die BETWEEN om te zetten naar >= en <= . (Misschien gebruikt between geen indexen).

Ik mag toch hopen dat PostgreSQL bij BETWEEN gewoon indexen gebruikt indien aanwezig.

Het beste kun je een gecombineerde index leggen op acctstarttime en acctsessiontime (die stonden toch in dezelfde tabel?).
Er hoeft dan alleen maar een stukje van de index gebruikt te worden om de query uit te voeren. (Covered index schijnen ze dat dan te noemen.)

[ACM]

whoami schreef op 23 september 2002 @ 12:52:
Probeer eens die BETWEEN om te zetten naar >= en <= . (Misschien gebruikt between geen indexen).

Doet ie wel

Converteert het DBMS die data niet naar strings (staan tussen quotes), waardoor de indexen op die velden niet kunnen gebruikt worden?

Doet ie goed

Als er geen index op die column ligt, zal de databank iedere rij moeten gaan controleren -> sequential scan. Als je er een index oplegt, dan moet hij dat niet doen en kan hij 'knippen'.

Soms pakt postgres toch de seq-scan omdat ie "bijna alle" blocken in moet lezen.

[Verwijderd]

Ik heb alle opmerkingen maar eens meegenomen in het testen. Het lijkt erop dat geen enkele index een verandering in de performance te weeg brengt...

[cameodski]

Verwijderd schreef op 23 september 2002 @ 11:48:
De SQL code moet er nu voor zorgen dat er een mooi overzichtje gedraaid gaat worden voor de groepen, per gebruiker, per nummer en dan per dag. Real-time dus.

Als je dit resultaat wilt krijgen, moet je natuurlijk wel een wat andere query uitvoeren, want anders gaat dat niet lukken.

Verder doe je een GROUP BY op dag, wat een tekstveld is, bepaald aan de hand van een datum. In zo'n geval kan het gebruik van een index tbv de GROUP BY wel een beetje moeilijk worden.

[jochemd]

Hier gaat het al mis (je moet de explain van onder naar boven lezen )

code:

->  Sort  (cost=24393.26..24393.26 rows=69975 width=30) (actual time=4328.23..4468.58 rows=68266 loops=1)
                                ->  Seq Scan on radacct  (cost=0.00..17694.28 rows=69975 width=30) (actual time=3.04..1895.68 rows=68266 loops=1)

Een sequential scan op de tabel die dus volgens de planner al gauw de helft van de tijd kost.
Wat doe je precies op die radacct ?

Als je een COUNT() daar op doet heb je altijd een seqscan toch (MVCC)?

[Creepy]

je doet een where op acctstarttime, had je daar al een index op?

[ACM]

Nog een aantal varianten die je kan testen:

code:

select
 to_char(datetime(acctstarttime),'YYYY-MM-DD') as dag,
 count(acctstarttime) as aantal,
 sum(acctsessiontime) as totaal,
 max(acctsessiontime) as maximum,
 avg(acctsessiontime) as gemiddeld
from
 gebruikers, nummers,  radacct
where
 acctstarttime between '2002-01-01 00:00:00' and '2002-01-31 23:59:59'
and
 gebruikers.gebruiker_id = nummers.gebruiker_id
and
 nummers.nummer_mapped = radacct.calledstationid
group by
 dag
order by
 dag desc

code:

select
 acctstarttime as dag,
 count(acctstarttime) as aantal,
 sum(acctsessiontime) as totaal,
 max(acctsessiontime) as maximum,
 avg(acctsessiontime) as gemiddeld
from
 gebruikers
inner join
 nummers
on
 gebruikers.gebruiker_id = nummers.gebruiker_id
inner join
 radacct
on
 nummers.nummer_mapped = radacct.calledstationid
where
 acctstarttime between '2002-01-01 00:00:00' and '2002-01-31 23:59:59'
group by
 dag
order by
 dag desc

Als je een development server hebt zou je ook kunnen overwegen daar postgresql 7.3beta1 op te zetten, die heeft een nog betere explain waar onder andere ook de join voorwaarden bij genoemd worden.

[ACM]

jochemd schreef op 23 september 2002 @ 15:37:
Als je een COUNT() daar op doet heb je altijd een seqscan toch (MVCC)?

Hangt helemaal van de query af

Het is zeker niet per definitie zo dat er maar gelijk een seq_scan wordt uitgevoerd.
Wat straks met postgresql7.3 ook flink uit kan maken is gebruik maken van het cluster commando, daarmee worden de tabellen a.d.h.v. een bepaalde index uitgelijnd.

Ow nog iets, hoe heb je postgresql ingesteld?

oa kwa cacheing etc?
(zie postgresql.conf)

[Verwijderd]

qua instellingen heb ik niets aan postgres veranderd...

De zaken draaien eigenlijk al op een development server. Dit omdat er ook een tree is met mysql 3.23.49 waarmee met dezelfde gegevens gewerkt wordt.

Ik had veel verhalen gehoord dat PostgreSQL met grote hoeveelheden gegevens sneller was dan MySQL (buiten het feit dat PostgreSQL een veiligere database is m.b.t. het ACID rijtje...)

Ter vergelijking. Eenzelfde overzicht (per groep, gebruiker en nummer) in MySQL met indices neemt 25 seconden in beslag.

Ohja, opmerking m.b.t. het overzicht. Dit is een van de queries die het overzicht rijk is:

code:

ls -l
total 32
totaal.sql
totaal_gebruiker.sql
totaal_gebruiker_overzicht.sql
totaal_groep.sql
totaal_groep_overzicht.sql
totaal_nummer.sql
totaal_nummer_overzicht.sql
totaal_overzicht.sql

De hoeveelheid SQL code hangt dus af van de detailniveau's van de statistieken...

[ACM]

Verwijderd schreef op 23 september 2002 @ 19:08:
qua instellingen heb ik niets aan postgres veranderd...

Zou ik dan maar es wat aan gaan wijzigen
Misschien heb je hier nog wat aan: http://www.argudo.org/postgresql/soft-tuning.html
en
http://www.ca.postgresql.org/docs/momjian/hw_performance/
Let vooral op de cacheing-parameters

De zaken draaien eigenlijk al op een development server. Dit omdat er ook een tree is met mysql 3.23.49 waarmee met dezelfde gegevens gewerkt wordt.

Probeer de 7.3beta ook eens dan als het mogelijk is, die heeft weer wat performance optimalisaties extra

Ik had veel verhalen gehoord dat PostgreSQL met grote hoeveelheden gegevens sneller was dan MySQL (buiten het feit dat PostgreSQL een veiligere database is m.b.t. het ACID rijtje...)

Het is idd vrij snel, zegt nog niet dat het perse altijd sneller is dan mysql
ACID kent het iig wel volledig.

Ter vergelijking. Eenzelfde overzicht (per groep, gebruiker en nummer) in MySQL met indices neemt 25 seconden in beslag.

25 vs 29 seconden valt me wel mee

[Verwijderd]

Er zijn een paar dingen die de door jouw genoemde query loodzwaar maken:
1) GROUP BY en ORDER BY op een gegenereerde kolom: misschien is er een manier om efficienter te groupen, want dit zal een aanslag zijn op je performance, omdat indexen niet gebruikt zullen worden. Misschien zal het met een datumfunctie (bijv. date_trunc('day', acctstarttime) wel lukken, maar ik vraag het me af.
2) aggregate functions SUM, MAX en AVG moeten altijd door alle resultaten lopen, waardoor je diskaccess zwaar belast zal worden

Toch lijken de tijden die jij opgeeft wel erg lang. Misschien is er een probleem met je disk access.

Verder denk ik dat PostgreSQL betrouwbaarder en sneller is met veel concurrent gebruikers, maar zal MySQL met slechts 1 gebruiker vaak sneller zijn.

[Verwijderd]

Het groupen is uberhaupt een noodzakelijk kwaad in de query... de sql resultsets worden verwerkt tot een xml tree die geparsed wordt naar willekeurig formaat. Een test met het ophalen van de gegevens en ze uit te laten rekenen duurt langer .

De aggegrate functions maken niet zoveel uit, de db servers zullen zeker in de normale omgeving genoeg disk i/o capaciteiten hebben. Een disk access probleem lijkt mij op mijn development server stug... Maar toch. Hoe kan ik vaststellen dat mijn disk i/o in dit soort situaties de bottleneck is?

Lijkt mij dus, dat er in deze weinig meer zal slagen. Een apache bench zal uitkomst moeten bieden in de concurrent gebruikers (hetgeen ook een issue is) .

[Verwijderd]

Verwijderd schreef op 23 september 2002 @ 20:44:
Het groupen is uberhaupt een noodzakelijk kwaad in de query... de sql resultsets worden verwerkt tot een xml tree die geparsed wordt naar willekeurig formaat. Een test met het ophalen van de gegevens en ze uit te laten rekenen duurt langer .

Snap ik ... ik vraag me alleen af of er niet een methode is om dat efficienter (mbv index) te doen. Op sommige databases kan dat, data groupen op een dag, maand en jaar met behoud van index, maar ik weet niet of dat voor PostgreSQL zo is.

De aggegrate functions maken niet zoveel uit, de db servers zullen zeker in de normale omgeving genoeg disk i/o capaciteiten hebben. Een disk access probleem lijkt mij op mijn development server stug... Maar toch. Hoe kan ik vaststellen dat mijn disk i/o in dit soort situaties de bottleneck is?

Geen idee ... ik ben geen PostgreSQL of Linux expert helaas ...

Lijkt mij dus, dat er in deze weinig meer zal slagen. Een apache bench zal uitkomst moeten bieden in de concurrent gebruikers (hetgeen ook een issue is) .

Het lijkt me dat je misschien beter de XML output gescheduled per dag/maand kunt laten genereren, om vervolgens die te tonen, ipv voor iedere aanvraag de XML te laten genereren. Zeker als de scedule dat 's nachts doet zal het aantal concurrent users laag zijn.

HTH

[ACM]

Verwijderd schreef op 23 september 2002 @ 20:44:
De aggegrate functions maken niet zoveel uit, de db servers zullen zeker in de normale omgeving genoeg disk i/o capaciteiten hebben. Een disk access probleem lijkt mij op mijn development server stug... Maar toch. Hoe kan ik vaststellen dat mijn disk i/o in dit soort situaties de bottleneck is?

Dan kom je in de buurt van systeem administratie, tools als vmstat, top, etc kunnen een hoop info leveren.
Volg ajb eerst mijn tips over het vergroten van de caches op, dan scheelt het io-gebruik iig behoorlijk.

Er staat me zelfs vaagjes bij dat die url's die ik geef ook aangeven hoe je kan zien dat de grootste issue de IO is.