[PostgreSQL] Performance probleem weg door kopieren tabel

[dev10]

Vorige week was ik op mijn werk bezig met een nieuw zoeksysteem te maken op onze website. Ons oude zoeksysteem was geschreven in Perl, niet door mij, en haalde zijn data uit een tekstbestand. Dat systeem was razendsnel.

Al onze producten stonden allemaal al in een database omdat daar het ordersysteem gekoppeld zat. (Het updaten van de producten in de database gebeurde door een bestand uit ons voorraadbeheer pakket te exporteren en dat vervolgens inlezen in de database. ) Het idee was daarom om een PHP script te maken wat netjes opzocht in de database en de producten netjes per 20 weergaf. Daar zat het probleem niet.

Hier onder een overzicht hoe de tabel products is opgebouwd (Nee, er is inderdaad geen veld met een auto_increment of sequence. Dit deed het voorraadbeheer pakket):


Deze tabel had deze index:

SQL:

CREATE INDEX id_products_key ON products USING btree (id)

Toen ik bezig was met het schrijven van het ordersysteem kwam ik echter een performance probleem tegen. Het opzoeken van data in de database duurde nogal lang. Een simpele query als

SQL:

SELECT id FROM products WHERE title ~* 'Zoekstring' OR description ~* 'Zoekstring' LIMIT 20 OFFSET 0

kostte al 700 milliseconden.

Omdat ik nog wat velden wilde toevoegen aan de tabel en de data een aantal keer opnieuw moest inlezen in de tabel had ik een kopie gemaakt om daarin te doen wat ik wilde. Deze tabel heb ik aangemaakt met:

SQL:

CREATE TABLE products_test AS SELECT * FROM products

Vervolgens heb ik de index aangemaakt en wat schetst mijn verbazing? De query uitvoeren kostte nog maar 70 milliseconden.

Uiteindelijk heb ik besloten voor het paardenmiddel. De tabel products kopieren, droppen en weer terugzetten. Indexen aanmaken en klaar. Zo gezegd, zo gedaan.

SQL:

BEGIN;

-- Tabel kopieren
CREATE TABLE products_backup AS SELECT * FROM products;

-- Andere tabel droppen
DROP TABLE products;

-- Nieuwe tabel aanmaken aan de hand van back-up tabel
CREATE TABLE products AS SELECT * FROM products_backup;

-- Index aanmaken
CREATE INDEX id_products_key ON products USING BTREE(id);

-- Backup tabel verwijderen
DROP TABLE products_backup;

COMMIT;

En inderdaad, toen werkte de andere tabel ook snel. Nu draait het nieuwe zoeksysteem al twee dagen en nog steeds geen performance problemen gezien.

Alleen, ik zit nog steeds met een vraag. Hoe kan het dat een tabel in PostgreSQL sneller wordt als er een kopie van aangemaakt wordt? De tabellen waren exact hetzelfde, alleen de tabel products was ongeveer 10 keer trager dan products_backup.

Heeft er iemand een idee?

[Alain]

Ik denk omdat met de backup de indexen gebalanceerd worden.

[dev10]

AlainS schreef op dinsdag 15 juli 2008 @ 19:50:
Ik denk omdat met de backup de indexen gebalanceerd worden.

Dat is dus iets wat ik al vaker gehoord heb. Ik had juist een paar dagen ervoor de enige index die er op zit (id_products_key) opnieuw geindexeerd.

Of zitten er meer indices op die tabel die je niet kunt zien?

[whoami]

't ja, dat kan van verschillende factoren afhangen:
- gebalanceerde indexen,
- fragmentatie van de tabellen
- ...

In SQL Server kan je maintenance plans maken voor databases, waarmee je dus kunt zorgen dat je indexen op gezette tijdstippen opnieuw gebouwd worden bv, je tabellen gedefragmenteerd worden etc...
Wellicht heb je in PostgreSQL ook zoiets ?

[ACM]

Als je veel updates (met name update- en delete-statements) uitvoert op je tabel, dan kan er flink wat ongebruikte data in ontstaan. Die data wordt met 'vacuum' vrijgegeven en met 'vacuum full' ook opgeruimd. Als je dat niet recentelijk of automatisch hebt laten doen, dan is het verstandig om dat wel te gaan doen.
Tegenwoordig (sinds 8.1 oid) bestaat daar de interne daemon 'auto_vacuum' voor, die je wdbt het werk uit handen neemt. Sinds 8.2 staat die daemon ook standaard aan. Eventueel moet je iets vergelijkbaars voor de indexen doen met 'reindex'.

Aangezien je aangeeft flink data te hebben geinsert en weer verwijderd, zou het me niks verbazen als je hier last van had.

Als bovenstaande niet aan de orde is, kan het zijn dat de statistische informatie over je tabel niet up-to-date is. PostgreSQL vernieuwt dat niet automatisch, dus ook dat moet je handmatig (of dmv auto_vacuum laten) doen. Handmatig doe je dat dmv het 'analyze'-commando.

Mocht ook dat geregeld, en iig na elke grote wijziging in de hoeveelheid data, gebeuren (bijv omdat je auto_vacuum aan hebt staan), dan wordt het lastiger.

Kijk sowieso altijd waar de tijd in zit (explain analyze select ...), als blijkt dat de geschatte hoeveelheden rows heel erg van de werkelijkheid af zit, of dat je nieuwe index helemaal niet gebruikt wordt, dan kan je kijken of de statistics target wel ruim genoeg staat. Normaliter pakt PostgreSQL maar een samenvatting van 10 waarden om de inhoud van een kolom te representeren. Als je dat te weinig vindt voor een specifieke kolom, dan kan je dat verhogen (alter tabel ... set statistics ... oid), het bereik is 10 - 1000, maar als je denkt dat je te weinig data krijgt zou ik beginnen met 50 of 100.

In dit geval kan dat niet echt uitmaken, hoewel het kan ook zijn dat de ene analyze net gunstigere waarden te pakken krijgt dan een andere. Daar helpt dat verhogen in ieder geval tegen.

Dat zijn vziw de enige zaken die echt invloed hebben op hoe PostgreSQL omgaat met identieke data in verschillende tabellen. Als je een query nog steeds te traag vindt, zal je wederom aan de slag moeten met EXPLAIN ANALYZE om na te gaan waar je query zijn tijd aan besteed. Maar dat is een ander hoofdstuk en nu in principe niet aan de orde

Owja, en voor de volgende keer is het nuttig om de PostgreSQL-versie te noemen. De beste performance haal je in principe met de laatste versie (8.3.3 atm).

[ Voor 7% gewijzigd door ACM op 16-07-2008 08:07 ]

[ikke007]

* VACUUM ANALYSE --> analyseer je indexen opnieuw
* EXPLAIN ANALYSE -> kijk waar de query lang over doet en of hij gebruik maakt van indexen
* Maak meer indexen aan op keyfields die je gebruikt om te zoeken (icm bovenstaande tip)

[ACM]

ikke007 schreef op woensdag 16 juli 2008 @ 09:17:
* VACUUM ANALYSE --> analyseer je indexen opnieuw

Dat doet wel iets meer dan alleen je indexen analyzeren. Je vacuum'ed je tabel of database daarmee en dat kan behoorlijk lang duren. En de analyze gaat over de kolommen, ook degenen waar geen index op zit (ook daar maakt het bij uit namelijk).
Als je enkel snel je kolomstatistieken wilt bijwerken zou ik toch echt enkel 'ANALYZE' nemen en het gaat niet om enkel de indexen.

* Maak meer indexen aan op keyfields die je gebruikt om te zoeken (icm bovenstaande tip)

Als de topicstarter genoegen neemt met die 70ms, dan is vooral de vraag waarom het ineens versnelde van belang

[dev10]

Als de topicstarter genoegen neemt met die 70ms, dan is vooral de vraag waarom het ineens versnelde van belang

Die 70 ms is een uiterste. Vaak schommelt het rond de 15 ms. Het compleet aanmaken van een pagina, het aanroepen van de templates en dergelijke, duurt ongeveer 0.46 seconde. Performance is op dit moment geen probleem. (De websites hebben ongeveer 1500 unieke bezoekers per dag)

Owja, en voor de volgende keer is het nuttig om de PostgreSQL-versie te noemen. De beste performance haal je in principe met de laatste versie (8.3.3 atm).

De versie die nu gebruikt wordt is 7.4.7. De persoon die de server beheerd, vind het niet nodig om te gaan updaten. (Al vind ik dat hij dat wel mag doen, maar ik ben ook maar de simpele webprogrammeur. )