SDGNnr80 zonder adv.id

a u t e u r
Chris Schaeffer &
Joek Hondius
The elephant
never forgets
Al enige jaren leveren wij een urenverantwoordingsysteem dat draait op Oracle of Microsoft SQL Server.
Omdat alle medewerkers binnen een organisatie
gebruik maken van dit systeem dient de klant dus te
beschikken over veel userlicenties. De bedragen die aan
b.v. Oracle voor licenties betaald moeten worden, overstijgen de kosten van ons systeem verre. Om e.e.a ook
voor kleinere organisaties betaalbaar te houden gingen
we op zoek naar een goedkopere, liefst gratis database. Deze database moest zich qua stabiliteit, performance en functionaliteit kunnen meten met Oracle en
MsSQL. Na uitgebreid onderzoek kwamen wij uiteindelijk terecht bij PostgreSQL als de database die volledig
aan onze eisen voldeed.
Historie
Het PostgreSQL project werd in 1986 gestart bij de
Universiteit van California op het roemruchte Berkeley,
oorspronkelijk onder de naam Postgres en gesponsord
door het Amerikaanse leger. Via Postgres95 werd dit
uiteindelijk PostgreSQL. PostgreSQL is beschikbaar voor
bijna alle moderne Unix systemen (34 platforms bij de
laatste release) en via Cygwin ook voor Windows.
Functionaliteit
Een volledig overzicht van de functionaliteit, vergeleken
met andere systemen, kunnen we hier, wegens plaatsgebrek, niet geven. Op de MySql website (1) kun je zelf
allerlei databases met elkaar vergelijken. Wat opvalt, is
dat PostgreSQL zeer volledig de ANSI SQL standaards ondersteunt en volledig ACID compliant is. Verder heeft
PostgreSQL alles wat je van een volwassen RDBMS mag
verwachten zoals referentiële integriteit, views, triggers,
sequences, stored procedures, transacties, outer joins en
nog veel meer.
De liefst gratis database moest
zich qua stabiliteit, performance
en functionaliteit kunnen meten
met Oracle en MsSQL
Documentatie
De documentatie bij PostgreSQL is in één woord uitmuntend. Op de site van PostgreSQL (2) valt het niet vanzelf
op, maar je kunt er werkelijk alles vinden over dit systeem
12
februari 2004
tot aan de specificaties van de datafiles en het netwerkprotocol. Er is ook een aantal boeken over PostgreSQL geschreven, onder meer door één van de leading developers,
Bruce Momjian. Het boek, PostgreSQL: Introduction and
Concepts (3) is, hoe kan het ook anders, in z’n geheel
online beschikbaar.
i e tie
Lc n
PostgreSQL wordt geleverd met BSD licentie, wat erop
neerkomt dat je zonder kosten de software en documentatie mag gebruiken, wijzigen, kopiëren en verspreiden,
al dan niet tegen betaling, als je het maar onder dezelfde
voorwaarden doet en de gebruikelijke disclaimer terug te
vinden is. Het copyright blijft bij de makers. Deze licentie
is dus minder restrictief dan de beroemde GNU GPL,
waarbij het mee linken van libraries erin resulteert dat je
hele applicatie open source is geworden.
I
nstallatie
De installatie van PostgreSQL is natuurlijk afhankelijk
van het platform. Wij beperken ons tot Linux en Windows;
voor de exotischer besturingssystemen of installatie van
de source kun je beschrijvingen vinden op de PostgreSQL
site (4).
De meeste Linux distributies hebben een installatiebestand voorhanden in de directory (5) waar deze bestanden zich bevinden. Voor RedHat Linux zijn er b.v. RPM’s.
PostgreSQL is vanaf de volgende versie ‘’native” voor
Windows beschikbaar. Dat wordt naar verwachting
medio 2004. Tot die tijd draait PostgreSQL op Windows
via Cygwin, een library die een laag tussen Windows en
PostgreSQL vormt. Wel stabiel, maar trager dan absoluut
noodzakelijk, en PostgreSQL is dan gebonden aan een
maximum van 60 gelijktijdige connecties.
Voor de installatie op Windows (alleen NT4, W2000, XP)
is een eenvoudige installer beschikbaar op onze web site (6). Downloaden, starten, de bekende vragen beantwoorden, en vervolgens zijn er twee extra services
beschikbaar: postmaster en ipc-deamon.
Tools
De database benaderen kan via de commandline-client
“psql”. Dit is een krachtige tool, maar je moet er wel aan
wennen. Grafische beheerstools a la TOAD zijn er in
soorten en maten, freeware en commercieel. Aanraders
onzerzijds zijn PgAdminIII (7) en EMS PostgreSQL Manager (8).
Fig. 1 EMS PostgreSQL Manager
Dan moeten we nog wel gebruiker larry en de sterrendatabase aanmaken:
Users, Rechten en Toegang
Op elke PostgreSQL server kunnen vele databases draaien. Standaard zijn er altijd 2 aanwezig, waarvan er een
benaderbaar is: template1. Hiermee moet je in eerste instantie verbinding maken op een nieuwe server. Als je
bent ingelogd kun je users en databases aanmaken.
PostgreSQL users zijn niet dezelfde users als die van je
besturingssysteem, ze bestaan alleen binnen PostgreSQL.
Er is altijd een database superuser genaamd postgres.
De authenticatie wordt geregeld door middel van het
bestand pg_hba.conf in de datadirectory van de installatie. Vaak is dat: /var/lib/pgsql/data. Er is een veelheid
aan mogelijkheden. Per database kun je dit opgeven.
Standaard heb je vanaf localhost toegang tot template1.
Dat is te zien in de 1e regel van de tabel hieronder.
In de 2e regel is te zien dat het locale netwerk met een
versleuteld wachtwoord toegang krijgt tot template1 en
larry van overal ter wereld met wachtwoord in de sterrendatabase mag.
CREATE USER larry PASSWORD secret;
CREATE DATABASE sterren;
Door achter CREATE USER larry etc. CREATEUSER en/of
CREATEDB te zetten kunnen we larry rechten toekennen
om respectievelijk users en/of databases aan te maken.
Zoals je ziet wordt de nieuwe database aangemaakt op
basis van het sjabloon template1. Je kunt met dit laatste
commando overigens ook heel makkelijk een kopie van
een reeds draaiende database maken, inclusief data.
Elk database object heeft een OWNER. Dat is de user die
dat object gecreëerd heeft. Wijzigen kan met ALTER USER.
De owner heeft alle rechten op een object; als andere
users iets met het object willen, hebben ze hiertoe privileges nodig. Er zijn er 12: SELECT, INSERT, UPDATE, DELETE,
RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, USAGE, and ALL PRIVILEGES.
# TYPE
DATABASE
USER IP-ADDRESS IP-MASK
METHOD
Local
all
all
trust
Host
template1
all
Host
sterren
Larry 0.0.0.0
Voorbeeld van pg_hba.conf
192.168.0.0
255.255.255.0
md5
0.0.0.0
password
Back-up/Restore
Er zijn 2 manieren om een back-up van
PostgreSQL te maken, Online en Offline. Offline houdt in dat je de database server stopt,
en de datafiles kopieert. Alles zit mooi in
één map.
sdgn magazine
februari 2004
13
Online backuppn gaat zo:
pg _ dump -U username -h hostname databasename >
bakfile.sql
Je ziet dat de back-up gemaakt wordt naar standaard uit.
Het file bestaat uit SQL commando’s.
Terugzetten gaat als volgt:
psql [opties] databasenaam < bakfile.sql
Er zijn veel dingen mogelijk: terugzetten per tabel, alles
leegmaken, comprimeren, scheiden schema/data en bijvoorbeeld overzetten van de ene naar de andere server
met een commando:
pg _ dump -h host1 dbname | psql -h host2 dbname
In onze ervaring zijn de backup-bestanden die PostgreSQL
maakt erg klein, maar flexibel in vergelijking met de commerciële alternatieven.
Performance
Performance heeft twee kanten, namelijk de vergelijking
met andere databases, ofwel benchmarks, en wat je kunt
doen om de performance te verhogen, ofwel tuning. Dit
laatste komt verderop aan de orde. Helaas mogen we
volgens de licenties van de grote database leveranciers
geen benchmarks publiceren waarin hun product genoemd wordt. Daarom zien de resultaten er iets anders
uit dan je zou verwachten.
PostgreSQL heeft niet de naam een snelle database te
zijn. PostgreSQL 6.4 was ook traag (eind 1998). Intussen
(versie 7.4/2004) is er heel veel veranderd. PostgreSQL
heeft zichzelf in de vingers gesneden door standaard
zeer bescheiden instellingen toe te passen, zodat het op
elke machine draait, maar wel traag.
Over het algemeen wordt MySQL geroemd om z’n snelheid en Oracle wordt over het algemeen ook gezien als
een snelle database. Straks komen we bij de cijfers, maar
eerst is het tijd voor de gebruikelijke waarschuwing
vooraf. Hoe kun je eigenlijk MySQL vergelijken met
PostgreSQL? Een database die niet aan views, transacties,
subselects of integriteitschecks doet, moet wel sneller
zijn dan een database die dat wel kan. We zullen zien.
Oracle heeft natuurlijk het voordeel dat het meestal
goed beheerd wordt en aan hardware is in dat kamp
meestal ook geen gebrek. PostgreSQL heeft een slimme
planner en doet row level locking bij writes en geen
locking voor simultane reads. Het resultaat is een database die zelfs bij een grote wirwar van queries soepel
blijft reageren, ook met honderden connecties.
Het onderling vergelijken van databases op snelheid
moet je eigenlijk sowieso niet doen, maar omdat wij
identieke databases kunnen creëren op PostgreSQL en
Larry die zo uit een productieomgeving komen en die we
onder grote realistisch gesimuleerde druk kunnen zetten,
advertentie
konden we het toch niet laten. MySQL is echter totaal
ongeschikt voor ons database schema, Interbase ondersteunen we niet, want PostgreSQL was makkelijker te
porten en gratis.
Er zijn ook anderen die tests hebben uitgevoerd, zoals de
firma GreatBridge die enkele jaren door een onafhankelijk instituut de TPC-C hebben laten uitvoeren op Larry
en Bill, en Interbase, MySQL en PostgreSQL. PostgreSQL
kwam eruit als de grote winnaar, zij aan zij met Larry,
zowel m.b.t. performance als schaalbaarheid. Deze tests
raakten echter zeer omstreden, en de validiteit is nooit
vast komen te staan. Verder lopen de meldingen uiteen
van MySQL 3x sneller tot even snel als PostgreSQL.
Fig. 2 PGAdmin-III
Dan nu onze eigen test:
De database is een kopie van een productiedatabase van
ons client/server urenverantwoording systeem Big Ben.
Deze database kan 1 op 1 worden overgezet van PostgreSQL op Larry en vice versa. Voor het maken van de testprogrammatuur hebben we de queries die een gebruiker
in een standaardsessie naar de database verstuurt gelogd. In deze test werden hiermee na wat aanpassingen
6 concurrent users gesimuleerd die elk 200 maal achter
elkaar inloggen. Totaal 1200 sessies per database dus. Let
wel: een standaard sessie duurt normaal ongeveer 10
minuten, in deze test is dat 4 seconden! De users werden
asynchroon opgestart om enige spreiding aan te brengen. De spreiding werd overigens tijdens de test vanzelf
groter. Tussen elke gesimuleerde sessie zat een pause
van 1 sec. Er werden per gesimuleerde sessie 80 queries
afgevuurd waarvan 10% insert/update/delete, 74
(sub)selects waarvan 46 op complexe (gestapelde) views.
Door de server werden 2000 records per sessie geretourneerd, totaal 12000 x 2000 = 24.000.000 records per database over de gehele test. De testdatabases bevatten 48
tabellen met 179 indexen en 77 foreign key’s, alsmede 30
sequences met bijbehorende triggers en 138 views. In totaal bevatten de databases 211.117 records, in grote en
kleine tabellen.
De specificatie van de testservers was als volgt:
• Larry 8.0.6: Pentium 4 2.0 Ghz, 512 Mb,
SCSI - Windows 2000 Adv. Server
• PostgreSQL 7.3.2: Pentium 4 2.0 Ghz, 512 Mb,
SCSI - RedHat Linux 9
Resultaten: Beide databases komen op een gemiddelde
van 4 seconden per sessie, PostgreSQL deed over de 200
sessies ruim 14 min, Larry had ruim 16 min nodig. Hierbij
moet opgemerkt worden dat Larry een kleinere variatie
in de responstijden per sessie vertoonde dan PostgreSQL.
Larry gebruikte minder CPU maar meer geheugen dan
PostgreSQL.
sdgn magazine
februari 2004
15
Het 100mb netwerk werd maximaal 5% bezet, de testclient kwam nooit boven 75% CPU.
De resultaten van anderen:
GreatBridge
Tim Perdue
sqlite
TCP-C: PostgreSQL komt als snelste
uit de bus naast Larry, Bill, Interbase,
en MySQL. Echter via ODBC getest,
mede hierdoor omstreden.
Test met een productiedatabase voor
de SourceForge website: MySQL is 2 a
3x sneller (database was echter
geschreven voor MySQL, geen
gebruik gemaakt van views bv), koos
toch voor PostgreSQL vanwege de
stabiliteit en schaalbaarheid
Na juiste instellingen van PostgreSQL
geen snelheidsverschil met MySQL
op pure insert en update tests. Deze
resultaten hebben wij overigens niet
kunnen repliceren.
Tuning
De factoren die van invloed zijn op de performance van
een database zijn enerzijds het databaseontwerp en de
queries en anderzijds de hardware en het gebruik van
systeembronnen. Bij PostgreSQL is met betrekking tot
het laatste met een aantal eenvoudige maatregelen
grote winst te boeken, maar verder heeft PostgreSQL dan
ook weinig aandacht meer nodig. Verder kan de databasedriver ook verschil opleveren.
Ten eerste is het van groot belang om het shared memory
en de buffers aan te passen. Standaard is dit zeer laag
ingesteld. Dit geheugen wordt gebruikt om de tabellen
erin te laden. Bij kleine databases is dit dus niet zo belangrijk, bij grotere kan het zomaar een factor 10 schelen.
Om de kernel in te stellen gebruik je:
echo 268435456 > /proc/sys/kernel/shmall
echo 268435456 > /proc/sys/kernel/shmmax
Om dit permanent te maken m.b.t. het rebooten:
echo “kernel.shmall = 268435456” >> /etc/sysctl.conf
echo “kernel.shmmax = 268435456” >> /etc/sysctl.conf
Nu heeft je systeem voldoende shared memory beschikbaar en kunnen we de buffers aan PostgreSQL toekennen. PostgreSQL werkt met buffers van 8Kb, dus in bytes:
268435456 / 8192 = 32768 buffers, wat we naar beneden
afronden tot 30000. En passant passen we nog het sort
memory aan, ook van belang bij group by clausules. De
ingestelde hoeveelheid wordt voor elke query gebruikt.
Het instellen gaat in het bestand postgresql.conf. In hetzelfde bestand kun je afhankelijk van de behoefte ook het
maximum aantal connecties opgeven, en als je een UPS
hebt of veel vertrouwen in je stroomleverancier kun je de
16
februari 2004
optie fsync op false zetten, wat erg veel uitmaakt voor
INSERTS en UPDATES. Wij vullen onze standaard testdatabase dmv INSERTS. Dat duurt 2 uur met fsync=on en
6:30 minuten met fsync=off op een Compaq Proliant
320DL met SCSI. Na wijziging van postgresql.conf nog wel
even de database herstarten.
postgresql.conf:
shared _ buffers = 30000
sort _ mem = 12288 # 12Mb
max _ connections = 512
#fsync = false # uncomment this for speed.
Only use with UPS
In de tweede plaats is het van groot belang om regelmatig, bijvoorbeeld eens per dag of zelfs per uur, het SQL
commando VACUUM ANALYZE te geven. Dit ruimt niet
alleen oude records op, maar stelt ook de instellingen van
de optimizer in op het gebruik en ontwerp van jouw
database. Op de langere duur is dit goed merkbaar.
Dit kan bijvoorbeeld door middel van een cronjob of een
scheduled task.
Het is van groot belang om
regelmatig, bijvoorbeeld eens
per dag of zelfs per uur het SQL
commando VACUUM ANALYZE
te geven
Over performance by design (9) valt veel te zeggen, maar
wij houden het op de volgende hints:
1. Gebruik de juiste datatypen.
2. Zoek naar de 20% queries die het meest gebruikt worden en optimaliseer die.
3. Gebruik het explain commando om meer over een
trage query te weten te komen. De query planner van
PostgreSQL berekent het snelste plan als het aantal
mogelijke plannen laag is. Bij complexe queries met
meer tabellen wordt het aantal mogelijke oplossingen
al snel te groot, en worden geavanceerde genetische
algoritmes gebruikt om een schatting te maken van
het beste plan. Soms is dit echter helaas niet de snelst
mogelijke oplossing. Met EXPLAIN krijg je inzicht in
het plan, en door de query te herschrijven kun je het
plan beïnvloeden.
4. Zorg voor een juist gebruik van indexen.
Drivers
Hoewel ongeveer alle drivers uiteindelijk gebruik maken
van de client library die met PostgreSQL wordt meegeleverd, is in de praktijk gebleken dat de driverkeuze veel uit
kan maken op het gebied van performance. Het is overigens ook mogelijk om zelf het netwerkprotocol te implementeren, want dit is zeer nauwkeurig beschreven, maar
wij zouden het niet aanraden.
De gratis ODBC driver (10) kan, hoewel bekend als snel,
toch tegenvallende resultaten geven als je veel kleine,
losse queries afvuurt. Een ODBC driver heeft wel het nadeel dat die op alle client computers moet worden geïnstalleerd en geconfigureerd, terwijl de andere drivers in
de applicatie mee gecompileerd kunnen worden.
Zeos Delphi driver (11) voor PostgreSQL is onverslaanbaar
(t.o.v. b.v. MDAC) met grote datasets, maar relatief traag
met kleine. Zeos ondersteunt ook vele andere databases.
De commerciële Delphi/C builder driver van vitavoom (12)
voor DBX is snel, maar kan, als de lengte van een veld niet
vaststaat, enorm veel geheugen aan de client kant gaan
gebruiken, wat de zaak weer kan vertragen. Dit kan voorkomen bij selecties op stapelingen van views.
Als je de client library (13) van PostgreSQL gebruikt, heb je
alles natuurlijk zelf in de hand, maar wordt het wel meer
bare-bones programmeren.
Links
1
2
3
4
5
6
7
8
9
10
11
12
13
http://www.mysql.com/information/features.html
http://www.postgresql.org/docs/current/interactive/
http://www.postgresql.org/docs/awbook.html
http://www.postgresql.org/docs/current/static/installation.html#INSTALL-SHORT
ftp://ftp.eu.postgresql.org/pub/unix/db/postgresql/
http://www.rem.nl/postgresql/
http://www.pgadmin.org/
http://www.ems-hitech.com/pgmanager/
http://www.postgresql.org/docs/current/static/performance-tips.html
http://techdocs.postgresql.org/techdocs/pgsqldbtuning.php
ftp://ftp.eu.postgresql.org/pub/unix/db/postgresql/odbc/versions/full/
http://www.zeoslib.net/
http://vitavoom.com/
http://www.postgresql.org/docs/current/static/libpq.html
Nawoord
PostgreSQL heeft de olifant als zinnebeeld gekozen. Niet
omdat het log en traag zou zijn (wel eens een kudde boze
olifanten gezien?), maar om de stabiliteit. Ondanks een
relatief klein brein (laag geheugen gebruik) kan hij de
data moeiteloos oplepelen, ook bij veel gelijktijdige connecties. Deze olifant is zeker geen allemansvriendje, maar
onder professionele begeleiding is hij tot grootse dingen
in staat.
Chris Schaeffer, geboren in 1955, DBA, is
sinds 1996 werkzaam bij REM automatisering, Research en Development. Zijn
interesses liggen bij database ontwikkeling, user interfaces en Delphi.
Joek Hondius, geboren in 1972, studie
psychnomie/neurale netwerken, is eveneens werkzaam bij REM automatisering,
Research en Development. Zijn interesses
liggen vooral bij Java, security/encryptie,
linux en databases.
advertentie
17