[PostgreSQL] Server optimalisatie

[Xudonax]

Ik heb op werk een niet al te krappe PostgreSQL server welke gebruikt word als database voor een nieuwe website. Het database schema hiervan is verre van optimaal, maar hier is op dit moment helaas niets meer aan aan te passen. Het probleem waar ik tegenaan loop is dat ik ongeveer 15 minuten nodig heb (inclusief processing in Python) om een zogenaamde "matching" te draaien. Deze matching zoekt een student bij een vacature.

Dit alles gaat nog relatief snel (gezien de vorige versie hiervan), maar eigenlijk moet dit in onder de 5 minuten gaan draaien. De code is al een stuk geoptimaliseerd, en de profiler van Python laat zien dat er ongeveer 80% van de tijd word besteed aan database queries, en dan voornamelijk INSERT queries. Wat me wel opvalt is dat er erg veel database queries gedaan worden, uiteindelijk ongeveer 141000.

Toch wil ik eerst kijken naar het optimaliseren van de database, omdat er in het aantal queries niet heel erg veel meer te optimaliseren valt. En zeker omdat ongeveer de 40 tot 50% hiervan steeds dezelfde is zou ik verwachten dat dit tot op zekere hoogte door PostgreSQL gecached zou worden. Met iotop krijg ik te zien dat er vrijwel geen reads worden gedaan vanaf de schijf, maar wel een hoop (trage) writes met ongeveer 100kByte/s als piek. Dit terwijl top laat zien dat er één core voor ~70% bezig word gehouden door PostgreSQL.

Ik heb wat zitten spelen met de PostgreSQL configuratie, maar ik krijg de database gewoon niet sneller. Zijn er punten welke ik kan aanpassen waardoor de database "ineens" een stuk sneller word? Bijvoorbeeld een flink stuk van de data in RAM te laden, want daarvan hebben we méér dan genoeg.

De hardware waar het allemaal op draait:
CPU: Intel Xeon L5520 @ 2.27GHz, 2x 4 cores (16 threads)
RAM: 3x 16GB RDIMM
HDD: 2x 146GB SAS 15k RPM
Nu weet ik ook wel dat er waarschijnlijk een gigantische snelheidswinst haalbaar zou zijn door er 1 of 2 SSDs in te zetten voor de database, maar dat ga ik er niet doorkrijgen.

EDIT: Als dit topic op de verkeerde plek staat hoor ik het graag, ik kon niet kiezen wat nu de juiste plek was

[Paul]

Hmm, back to the drawing board is geen optie? Honderd één en veertig duizend INSERT queries voor één opdracht die in wezen een SELECT is?

Ik weet niet op wat voor controller die 2 schijven zitten (RAID1?) maar je kunt overwegen schrijfcache aan te zetten als je een BBU op de raidcontroller hebt zitten?

Ik heb zelf wel eens een opdracht die uren kostte en uit ~500 inserts en ~100.000 updates bestond sneller gekregen door die inserts en updates op een array uit te voeren ipv in de database, waardoor het geheel terug werd gebracht tot ~500 inserts, maar of dat hier ook mogelijk is weet ik natuurlijk niet

[Xudonax]

Dat heb ik inderdaad verkeerd opgeschreven, het zijn natuurlijk niet allemaal INSERTs, foutje van mijn kant. ongeveer 30k daarvan zijn INSERTs (1 vacature word in dit specifieke geval gematched aan ~30k studenten). Om dit gedaan te krijgen worden een aantal many-to-many relaties afgewandeld wat voor de overige 110k queries zorgt. Dit zijn dus relatief simpele SELECT queries welke een lijst met Django objecten oplevert. De INSERT queries zijn omdat we de resultaten willen bewaren in de database zodat we dit niet bij iedere pagerequest hoeven te bepalen (zou nogal dodelijk zijn voor de performance ).

Dus, kleine recap (ook wel bekend als tl;dr):
141.000 queries bestaat uit ongeveer 30.000 INSERTs, de rest zijn SELECTs.

[MadGazelle]

Ik heb laatst ook een dergelijke zoektocht naar PostgreSQL perfomance tips ondernomen en toen kwam ik deze zeer handige presentatie tegen

[Wolfboy]

Het zou toch nog steeds heel zinnig zijn om het aantal queries terug te brengen.

Maar de inserts kan je in ieder geval mogelijk versnellen door ze in 1 transactie te doen. Verder zou ik je min_duration_statement eens op 100ms zetten zodat je alle trage queries snel kan vinden. Mogelijk mis je nog ergens een index ofzo...

Maar verder, 141000 queries / 900 seconden = 157 queries per seconde, ook weer niet heel beroerd
Lang verhaal kort... fix de code zodat je minder queries doet, ik kan me niet voorstellen dat je er niet voor kan zorgen dat je minder queries doet eigenlijk...
Al zal je wel om Django heen moeten werken waarschijnlijk.


Btw, welke Postgres versie hebben we het hier over? Writable CTE's kunnen mogelijk nog helpen om het geheel terug te brengen naar een handvol queries.


Wat betreft die manytomany's, probeer prefetch related eens: https://docs.djangoprojec...erysets/#prefetch-related


Je zou overigens ook nog pgbouncer kunnen gebruiken, kan in dit geval het boeltje misschien ook nog wel iets versnellen (tenzij je de verbinding al hergebruikt)

[ Voor 16% gewijzigd door Wolfboy op 04-07-2012 16:04 ]

[cariolive23]

HDD: 2x 146GB SAS 15k RPM

Wanneer er geen sprake is van cache in een raid controler, is de maximale random write performance zéér beperkt. Een enkele 15k toeren schijf heeft een theoretisch maximum van 250 commits per seconde, dus 250 losse INSERT-queries per seconde. Grote kans dat hier jouw probleem zit, te weinig random I/O tot je beschikking. Wat is de iowait?

Wat kun je er aan doen? Ga met transacties werken, meerdere schrijfacties in één transactie bundelen.

code:

INSERT ....;

is vrijwel net zo snel als:

code:

BEGIN;
 INSERT ....;
 INSERT ....;
 INSERT ....;
 INSERT ....;
COMMIT;

De tweede optie schrijft alleen wel 4x zoveel data weg!

Je kunt ook synchronous_commit uit zetten, er wordt dan niet gewacht op een fsync maar je kunt wel data kwijt raken wanneer de server crashed. De database zal niet corrupt raken, je bent alleen de data kwijt die nog niet was gefsynced.

Ik neem aan dat je PostgreSQL verder al wel hebt geconfigureerd? De default configuratie is net goed genoeg om op te starten, niet om productie op te draaien. Een bekend probleem is bv. auto_vacuum die niet goed is geconfigureerd, ook dit kan een negatieve impact hebben op de beschikbare I/O

[Xudonax]

Sorry, dit topic enigszins uit het oog verloren o.o

Transacties is een ietwat lastig, want dat moet per functie gebeuren in Django. In verband met DRY (Don't Repeat Yourself) hebben we één algemene matching functie welke een student met een vacature matched, wat gewoon goed snel gaat. Echter moet er ook de optie zijn om een vacature met alle studenten matches. Dit doen we dus door de enkele functie uit te voeren voor iedere mogelijke student. Deze opbouw is natuurlijk heerlijk DRY, maar maakt volgens mij een hoop optimalisaties in ieder geval lastiger.

Ik had het een en ander aan PostgreSQL tuning gedaan, maar dat heb ik met wat trial-and-error ongedaan moeten maken omdat de server fysiek gecrashed was en ik niet meer kon herinneren wat ik aan Ubuntu had aangepast om het te laten werken ~_~' Nooit eraan gedacht om dat in een rc.local oid te zetten natuurlijk... Nu betwijfel ik ook hoe goed PostgreSQL getuned was, want ik snapte niet heel veel van de documentatie. Ik ga de presentatie van MuseFan dan ook maar eens doornemen, en een beetje testen (meten = weten enzo ).

En inderdaad WolfBoy, een krappe 160 queries per seconde is heel niet verkeerd, en eigenlijk ben ik nog steeds aan het proberen om een SSD (al dan niet in RAID1) in die bak te krijgen om het nog sneller te maken. Want ik kan me niet voorstellen dat ze de RAID controller hebben voorzien van enige vorm van caching eigenlijk, staat in ieder geval niet in de specs die we hebben gekregen van de hosting provider. Maandag even bellen voor de zekerheid

EDIT: En we draaien de laatste versie van PostgreSQL in Ubuntu, dus dit is in ieder geval versie 9.x.apt-cache meld mij dat het versie 9.1+129 is.

[ Voor 4% gewijzigd door Xudonax op 19-08-2012 00:02 ]

[cariolive23]

Heb je al eens testrun gedaan met synchronous_commit off? Dit kun je ook per database sessie instellen:

SQL:

SET synchronous_commit TO 'off';

Wanneer je veel schrijft, en daar lijkt het bij jou op, dan moet je ook de configuratie settings voor het schrijven van WAL segmenten optimaliseren.

Een SSD gaat waarschijnlijk een enorm verschil maken, die doen het héél veel beter met random I/O. Zorg er wel voor dat een betrouwbare SSD krijgt, consumentenspul laat je ernstig in de steek wanneer de server crashed. Check de PostgreSQL mailing lists eens, er zijn diverse topics over SSD's welke wel en niet betrouwbaar zijn.