[Oracle/PostgreSQL] stored procedure performance

[BalusC]

Ik heb hier een Oracle 10g stored procedure (geminimaliseerd voorbeeld):

SQL:

CREATE PROCEDURE replace_into_table1(p1 NUMBER, p2 VARCHAR2, p3 VARCHAR2) AS 
    rows NUMBER;
    BEGIN
        SELECT COUNT(*) INTO rows FROM table1 WHERE column1 = p1;
        IF rows > 0 THEN 
            UPDATE table1 SET column2 = p2 AND column3 = p3 WHERE column1 = p1;
        ELSE 
            INSERT INTO table1 (column1, column2, column3) VALUES (p1, p2, p3);
        END IF;
    END;
/

Werkt prima. 150K rows worden op de devomgeving binnen 5 minuutjes gedaan.

De PostgreSQL 8.4 variant van diezelfde stored procedure ziet er als volgt uit:

SQL:

CREATE FUNCTION replace_into_table1(NUMERIC, VARCHAR, VARCHAR) RETURNS void AS $$
    DECLARE
        rows NUMERIC;
    BEGIN
        SELECT COUNT(*) INTO rows FROM table1 WHERE column1 = $1;
        IF rows > 0 THEN 
            UPDATE table1 SET column2 = $2 AND column3 = $3 WHERE column1 = $1;
        ELSE 
            INSERT INTO table1 (column1, column2, column3) VALUES ($1, $2, $3);
        END IF;
        RETURN;
    END;
$$ LANGUAGE plpgsql;

Basically identiek, maar de blauwe olifant neemt wel een friggin 50 minuten de tijd voor 150K rows In andere omgevingen (test en prod) is er wel verschil in tijd, maar dit verhoudt zich steeds hetzelfde. Oracle is rond de 10x sneller. En dit allemaal in één transactie (alles of niets) op dezelfde keys (column1=PK).

Ik voer ze uit middels JDBC, maar diezelfde statementen direct in de pgAdminIII uitvoeren is nauwelijks sneller. Scheelt misschien een seconde of 5, volkomen verwaarloosbaar.

Ik heb even zitten googlen met een of meer van de sleutelwoorden postgresql stored procedure performance, echter het gaat dan praktisch allemaal over dat je het beste een stored procedure moet gebruiken voor de beste performance. Ja d'oh

Ben ik nu de enige of herkent iemand dit? De DB's hebben beiden voldoende geheugen tot hun beschikking en de CPU's worden ten tijde van de run voor de volle 100% benut.

[DamadmOO]

Je weet dat Postgres een "merge" statement heeft dat speciaal geoptimaliseerd is voor hetgeen je wilt doen?

[BalusC]

Niet dus.

Ik zal eens kijken of dit wat is. Bedankt

[cariolive23]

Je hebt drie queries in de sp staan, ga deze 1-voor-1 met EXPLAIN ANALYZE uitpluizen. Wellicht mist er een index of is er toch niet genoeg geheugen beschikbaar waardoor de server gaat swappen. En dat is langzaaaaaaam. Hoe ziet jouw configuratie eruit? Vooral shared_buffers en work_mem zijn hier belangrijk voor het opzoeken van de records, voor het schrijven zul je moeten kijken naar de WAL-settings. Maar ga eerst met EXPLAIN aan de slag, dan krijg je een idee wat nu het probleem zou kunnen zijn.

Ps. VACUUM en ANALYZE vooraf ook even, ik kom nog regelmatig situaties tegen waarbij de autovacuum uit staat of slecht is geconfigureerd.

[justmental]

Oracle heeft ook een merge trouwens.
Voordat dat beschikbaar was deed men dit vaak door een update te doen en dan middels sql%rowcount uit te vragen of dat gelukt was. Dat scheelt je de select.
Postgres heeft vast ook zoiets.

Oracle hanteert trouwens een optimistische strategie vwb openstaande transacties, die worden dus ook echt in de tabel geschreven. Daardoor hoeft er maar op een plaats gekeken te worden of bijvoorbeeld de pk violated wordt door een insert. Als postgresql dat anders doet dan kan ik me voorstellen dat bij zoveel kleine statements in een transactie het een hels karwei wordt voor de db om alles bij te houden.

[cariolive23]

Da's een goeie, de SELECT-query is volkomen overbodig. Controleer gewoon of de UPDATE iets heeft geraakt door te kijken wat de status van FOUND is.

[BCC]

Klinkt inderdaad als een index die is vergeten bij de migratie. Wat zegt explain?

[BalusC]

De indexen zitten ongetwijfeld goed. De DDL's zijn in principe identiek voor PostgreSQL en Oracle. De pgAdminIII geeft ze ook netjes weer als de constraints van de tabel. Geen idee of het kwaad kan, maar het is wellicht goed te weten dat de postgres.conf na de setup geoptimaliseerd is mbv de zgn "EnterpriseDB Tuning Wizard".

Ik heb even de explain analyze erop losgegooid nadat ik de sp heb geoptimaliseerd mbv de 'FOUND'.

SQL:

CREATE FUNCTION replace_into_table1(NUMERIC, VARCHAR, VARCHAR) RETURNS void AS $$
    BEGIN
        UPDATE table1 SET column2 = $2 AND column3 = $3 WHERE column1 = $1;
        IF FOUND THEN RETURN; END IF;
        INSERT INTO table1 (column1, column2, column3) VALUES ($1, $2, $3);
        RETURN;
    END;
$$ LANGUAGE plpgsql;

Alles ziet er normaal uit bij de individuele queries van de stored procedure. De update neemt rond de 0,085ms in beslag:

code:

"Index Scan using table1_pk on table1  (cost=0.00..7.28 rows=1 width=10) (actual time=0.025..0.028 rows=1 loops=1)"
"  Index Cond: (column1 = 1)"
"Total runtime: 0.084 ms"

De insert neemt rond de 0,135ms in beslag:

code:

"Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.005..0.007 rows=1 loops=1)"
"Trigger for constraint table1_column2_fk: time=0.060 calls=1"
"Total runtime: 0.133 ms"

Echter de hele sp zelf neemt 40~45ms in beslag. De explain analyze zegt helaas niet veel over de sp zélf, ik krijg dan zoiets terug:

code:

"Result  (cost=0.00..0.27 rows=1 width=0) (actual time=43.540..43.542 rows=1 loops=1)"
"Total runtime: 43.565 ms"

Het lijkt erop dat de DB moeite heeft met het vinden/uitvoeren van de hele sp?

Ik ga nu even verder kijken of de MERGE statement daadwerkelijk wat is.

[cariolive23]

Gebruik de volgende procedure eens, die geeft ook wat informatie weer, de tijdstippen waarop een bepaald punt wordt gepasseerd:

SQL:

CREATE FUNCTION replace_into_table1(NUMERIC, VARCHAR, VARCHAR) RETURNS void AS $$ 
    BEGIN 
RAISE INFO '1:', clock_timestamp(); -- om knelpunten te achterhalen, kan later weg
        UPDATE table1 SET column2 = $2 AND column3 = $3 WHERE column1 = $1; 
RAISE INFO '2:', clock_timestamp(); -- om knelpunten te achterhalen, kan later weg
        IF NOT FOUND THEN
RAISE INFO '3:', clock_timestamp(); -- om knelpunten te achterhalen, kan later weg
                INSERT INTO table1 (column1, column2, column3) VALUES ($1, $2, $3); 
RAISE INFO '4:', clock_timestamp(); -- om knelpunten te achterhalen, kan later weg
        END IF; 
RAISE INFO '5:', clock_timestamp(); -- om knelpunten te achterhalen, kan later weg
        RETURN; 
    END; 
$$ LANGUAGE plpgsql;

Tevens is deze iets logischer opgezet, er wordt maar op één plek een RETURN uitgevoerd, aan het einde van de procedure. De IF is nu een IF NOT geworden, alleen in dan wordt de INSERT uitgevoerd.

Welke resultaten leveren de RAISE INFO's op? Zie pgAdmin nadat je de sp hebt aangeroepen, die geeft ze keurig weer.

Wanneer alles werkt zoals het moet werken, gooi dan de RAISE INFO's eruit, heb je niet meer nodig.

[leuk_he]

Het eerste wat me te binnen schiet is dat count heel slecht performed in pestgre:

http://wiki.postgresql.org/wiki/Slow_Counting

maar die heb je nu geelimineerd.....

[BalusC]

Ik ben weer thuis, ik zal dus morgen pas cariolive's suggestie gaan uitwerken (werk en prive gescheiden houden ), maar ik wilde wel even kwijt dat de geoptimaliseerde SP de verwerktijd terugbracht van 50 naar 35 minuten. Een heel verschil, maar nog steeds te lang, daar niet van.

Overigens wordt de MERGE statement niet herkend op PostgreSQL 8.3 (het is dus niet 8.4, zoals ik abusievelijk in de topicstart meldde, daarvoor excuus) en kan ik de hele functie niet in de documentatie terugvinden.

edit:

Whatever werk/prive, ik zit lekker buiten met mijn werklaptop Ik heb dus een testje gedaan met die RAISE INFO erin verwerkt (die overigens wel de % placeholders misten in jouw voorbeeld, cariolive):

Insert (47ms)

code:

INFO:  1: 2009-10-21 17:42:18.272-04:30
INFO:  2: 2009-10-21 17:42:18.319-04:30
INFO:  3: 2009-10-21 17:42:18.319-04:30
INFO:  4: 2009-10-21 17:42:18.319-04:30
INFO:  5: 2009-10-21 17:42:18.319-04:30

Update(46ms)

code:

INFO:  1: 2009-10-21 17:42:51.817-04:30
INFO:  2: 2009-10-21 17:42:51.863-04:30
INFO:  5: 2009-10-21 17:42:51.863-04:30

Schijnbaar wordt bínnen de SP geen gebruik gemaakt van de index in de cache?

FWIW: ik heb diezelfde vraag zojuist in de pgsql-performance mailinglist gepost omdat ik toch wel denk dat het probleem dieper zit.

[ Voor 72% gewijzigd door BalusC op 22-10-2009 00:36 ]

[cariolive23]

BalusC schreef op woensdag 21 oktober 2009 @ 23:31:
(die overigens wel de % placeholders misten in jouw voorbeeld, cariolive):

Sorry, even over het hoofd gezien en geen db bij de hand om e.e.a. te testen.

Ik vermoed dat je nu tegen configuratie-problemen aanloopt, te weinig RAM toegekend, problemen met (auto-) vacuum, ongelukkige WAL settings of iets wat daar op lijkt. Wanneer je 150K records één voor één gaat updaten/inserten, zal dat voor veel schrijf acties zorgen, daar moet de configuratie dus wel op zijn berekend.

Kun je de config hier eens plaatsen?

[BalusC]

Dit zijn de relevante settings (grotendeels automatisch gezet door EnterpriseDB tuning):

code:

shared_buffers = 1024
temp_buffers = 8MB
work_mem = 1024
maintenance_work_mem = 16384
max_fsm_pages = 20000
max_fsm_relations = 1000
wal_buffers = 256
autovacuum = true

[ Voor 11% gewijzigd door BalusC op 22-10-2009 01:25 ]

[cariolive23]

Oeps! Dit is zééér minimaal en totaal onbruikbaar op een beetje productieserver. Of je moet een server hebben van 10 jaar oud met 64MB RAM, dan kan ik me er iets bij voorstellen.

shared_buffers => mag je 25% van de door PostgreSQL te gebruiken hoeveelheid RAM toekennen. Dus wanneer PostgreSQL maximaal 1GB RAM mag gebruiken (rest van je RAM voor andere processen), dan 256MB.

work_mem => 12 MB is toch wel het minimum, maar zal voor bovenstaand probleem waarschijnlijk niet veel uitmaken.

maintenance_work_mem => Flink omhoog, hier zal de (auto-) vacuum enorm van profiteren, 64MB is wel het minimum, 128 tot 256 MB lijken mij betere settings.

wal_buffers => mag je 1MB van maken, is een goed uitgangspunt

checkpoint_segments zal standaard op 3 staan, een hogere setting zal (bij voldoende RAM) betere performance geven.

effective_cache_size noem je niet, maar mag je op 75% van de door PostgreSQL te gebruiken hoeveelheid RAM toekennen. Dus wanneer PostgreSQL maximaal 1GB RAM mag gebruiken (rest van je RAM voor andere processen), dan 768MB.

Dit is even uit de losse pols zonder jouw applicatie en database gebruik te kennen, maar zal hoogst waarschijnlijk betere resultaten opleveren. Met meer kennis over jouw systeem en het gebruik, zal er nog veel meer performance uit te slepen zijn.

Hou ook in de gaten dat je per sp diverse configuratie settings kunt opgeven die alleen voor deze sp van toepassing zijn. Zo kun je een default config opstellen die goed werkt voor 80% van het gebruik, de resterende 20% van het gebruik kun je dan gaan configureren door de sp's en views de juiste configuratie mee te geven.

Een betere config kan de boel al zeker 100 keer beter laten draaien en ik heb nog wel extremere verbeteringen gezien.

En jouw huidige configuratie, functioneel goed maar wat performance betreft volkomen waardeloos.

[The Eagle]

Heb zoiets wel eens ooit binnen Oracle aan de hand gehad. Ik weet niet hoe het bij PostgreSQL zit, maar oracle heeft zijn logging meelopen - en dat gaat meestal wel goed - maar soms zit je met heel veel rijen en updates en whatever en lopen zijn logs vol. In dat geval geldt er een round robin prncipe en moet er dus continue gewacht worden. Wellicht is dat bij PostgreSQL ook het geval.

De oplossing is in dit geval simpel: een loopje in je procedure bouwen dat een teller mee laat lopen die telkens met 1 opgehoogd wordt. Noem het rowcount of zoiets. Als rowcount = 1k (kan ook 10k of anders, is vanzelfsprekend afhankelijk van aantal rijen dat je gemiddeld verwacht), dan een commit en de counter resetten. En natuurlijk aan het einde van de procedure ook nog een commit om af te vangen dat ie niet commit wanneer er minder dan 1k lijnen geinsert worden

[BalusC]

Hmm, volgens mij is die tuning wizard nogal buggy De waarden zijn hier en daar inderdaad een duizendfactor te weinig aangezien er KB of MB suffixes missen. Ik zal even wat rechtzetten en nogmaals testen. Thanks

[cariolive23]

BalusC schreef op donderdag 22 oktober 2009 @ 02:02:aangezien er KB of MB suffixes missen

Die zijn niet verplicht, je kunt gewoon een integer opgeven voor het aantal bytes dat je wilt toekennen. Dat is alleen niet bijzonder duidelijk, KB of MB heeft bij mij dus de voorkeur.

Met een groot aantal updates is het wenselijk om ook de autovacuum goed te gaan afstemmen op dit aantal. Doe je dat niet, is je database veel te druk met vacuum en dus langzaam. Dit kun je ook per tabel instellen, dus ook hier weer eerst 80% in de config opnemen en de resterende 20% uitzonderingen gaan plaatsen bij de eigenschappen van de tabellen. Vaak is het handig om dit pas te gaan doen nadat je de statistieken hebt uitgeplozen en ziet welke tabellen grote hoeveelheden (mislukte) inserts (dus rollbacks), updates en deletes voor hun kiezen krijgen. Zorg dus eerst voor een werkende applicatie en ga dan pas dit soort zaken configureren.

[BalusC]

OK, ik heb de DB scherper afgesteld met 1GB RAM in achterhoofd.

code:

shared_buffers = 256MB
temp_buffers = 8MB
work_mem = 16MB
maintenance_work_mem = 128MB
wal_buffers = 1MB
checkpoint_segments = 8
effective_cache_size = 768MB

Het is gemiddeld genomen 5ms sneller. Dus ipv rond de 45-48ms duurt het 40-43ms. Geen schokkend verschil. Het probleem zit schijnbaar ergens anders. De DB is uiteraard herstart.

Edit: overigens. ik zat via de pgAdminIII de configbestand te bekijken .. In de 2e kolom zie je de ingestelde waarde en in de 3e kolom de zgn huidige waarde. Voor de *_buffers instellingen staan ze onder de ingestelde waarde. Mag ik aannemen dat dit gewoon flexibele waarden zijn en dat je eigelijk de buffer limiet instelt? In ieder geval, de aangegeven huidige waardes zijn:

code:

shared_buffers = 32768
temp_buffers = 1024
wal_buffers = 128

En dan nog, waarom presteren een individele insert en update steeds uitstekend naar behoren?

[ Voor 40% gewijzigd door BalusC op 22-10-2009 02:59 ]

[cariolive23]

Even voor mijn beeldvorming: Hoelang duurt het om 1x de sp aan te roepen? Dus om 1 record te updaten of te inserten.

ik wilde wel even kwijt dat de geoptimaliseerde SP de verwerktijd terugbracht van 50 naar 35 minuten

Wat is hier de "verwerktijd" ? Is dat 1 keer de sp aanroepen in pgAdmin of 150.000 keer via jouw applicatie de sp aanroepen? Er kan natuurlijk ook iets "fout" zitten in de manier waarop de applicatie met de database kletst, denk bv. eens aan de situatie waarbij iedere keer opnieuw een db-connectie via SSL wordt opgebouwd. Dat kost uiteraard veel tijd. En ja, ik ben deze fout eens tegengekomen, vandaar dat ik er over begin.

Wat is de belasting van de database? Kun je zien of deze heel erg druk is wanneer je de boel aan het verwerken bent? Wordt er veel processorcapaciteit gebruikt, veel RAM of juist veel I/O ? Het kan ook goed zijn dat de database vooral bezig is met wachten op data van jouw applicatie.

Wanneer je 150k records wilt updaten of inserten, is het wellicht sneller om eerst alle data in een temp-table te zetten (met COPY, dat gaat lekker snel) en dan in één keer de complete temp-table door de sp heen te halen:

SQL:

SELECT replace_into_table1(col1, col2, col3) FROM temptable;

[user109731]

Misschien obvious, maar je zou de SP eens kunnen vervangen door een hele simpele (die enkel een input waarde teruggeeft). Dan weet je iig of het de genoemde sp/connectie overhead is.

[BalusC]

cariolive23 schreef op donderdag 22 oktober 2009 @ 12:11:
Even voor mijn beeldvorming: Hoelang duurt het om 1x de sp aan te roepen? Dus om 1 record te updaten of te inserten.

40-43ms momenteel en dat terwijl een individuele update rond 0,085ms in beslag neemt en een individuele insert rond 0,135ms.

Wat is hier de "verwerktijd" ? Is dat 1 keer de sp aanroepen in pgAdmin of 150.000 keer via jouw applicatie de sp aanroepen? Er kan natuurlijk ook iets "fout" zitten in de manier waarop de applicatie met de database kletst, denk bv. eens aan de situatie waarbij iedere keer opnieuw een db-connectie via SSL wordt opgebouwd. Dat kost uiteraard veel tijd. En ja, ik ben deze fout eens tegengekomen, vandaar dat ik er over begin.

150K statements wordt in 35 minuten gedaan i.t.t. de 5 minuten in Oracle. De tijd is dezelfde in zowel de applicatie als de pgAdminIII. Alles geschiedt in 1 transactie (dus ook 1 connectie).

Wat is de belasting van de database? Kun je zien of deze heel erg druk is wanneer je de boel aan het verwerken bent? Wordt er veel processorcapaciteit gebruikt, veel RAM of juist veel I/O ? Het kan ook goed zijn dat de database vooral bezig is met wachten op data van jouw applicatie.

De CPU wordt ten volle benut, het geheugen lijkt mee te vallen en over IO kan ik momenteel weinig kwijt.

Wanneer je 150k records wilt updaten of inserten, is het wellicht sneller om eerst alle data in een temp-table te zetten (met COPY, dat gaat lekker snel) en dan in één keer de complete temp-table door de sp heen te halen:

SQL:

SELECT replace_into_table1(col1, col2, col3) FROM temptable;

Daar moet ik even naar kijken.

JanDM schreef op donderdag 22 oktober 2009 @ 12:16:
Misschien obvious, maar je zou de SP eens kunnen vervangen door een hele simpele (die enkel een input waarde teruggeeft). Dan weet je iig of het de genoemde sp/connectie overhead is.

Nope, dat is het ook niet. Een "lege" sp wordt snel zat uitgevoerd. Rond de 0.075ms. Zodra er ook maar een select (perform), insert of een update in voorkomt, dan gaat de verwerktijd naar minstens 40ms.

[ Voor 19% gewijzigd door BalusC op 22-10-2009 13:29 ]

[Niemand_Anders]

Staan Oracle en Postgres op dezelfde machine?

Ik ben ooit gigantisch de fout in gegaan door te veronderstellen dat al onze development servers van hetzelfde type (specificaties) waren. Dat was ook zo, behalve net die ene dev database server. Die had namelijk een IDE schrijf, terwijl alle andere servers allemaal RAID10 disk arrays hadden..

[BalusC]

Ik ben 1 stap verder gekomen: het lijkt te maken te hebben met het invullen van de sp parameters .. Wanneer ik de parameters binnen de SP vervang door hardcoded waarden, gaat het ineens wel snel: 0,1 ~ 0,2ms per sp call.

[cariolive23]

Ga de interne werking eens explainen:

SQL:

CREATE FUNCTION replace_into_table1(NUMERIC, VARCHAR, VARCHAR) RETURNS void AS $$  
    DECLARE
        row       record;
        query    text;
    BEGIN  
        query := 'UPDATE table1 SET column2 = ' || $2 || ' AND column3 = ' || $3 || ' WHERE column1 = ' || $1;  

        FOR row IN EXECUTE 'EXPLAIN ANALYZE ' || query LOOP
            RAISE INFO '%', row;
        END LOOP;
        
        IF NOT FOUND THEN 
                query := 'INSERT INTO table1 (column1, column2, column3) VALUES ('|| $1 ||',' || $2 ||',' || $3 || ');';  

                FOR row IN EXECUTE 'EXPLAIN ANALYZE ' || query LOOP
                    RAISE INFO '%', row;
                END LOOP;
        END IF;  
        RETURN;  
    END;  
$$ LANGUAGE plpgsql;

Niet getest, er kan dus nog wel een bugje in zitten.

[user109731]

BalusC schreef op donderdag 22 oktober 2009 @ 14:03:
Ik ben 1 stap verder gekomen: het lijkt te maken te hebben met het invullen van de sp parameters .. Wanneer ik de parameters binnen de SP vervang door hardcoded waarden, gaat het ineens wel snel: 0,1 ~ 0,2ms per sp call.

De SP argumenten hebben hetzelfde type als de kolommen? Misschien zit ergens een langzame conversie tussen, dat zou verklaren waarom hard-coded values wel snel gaan.

[leuk_he]

Er staat me iets bij van parametrized vs parametrized queries.,..

http://archives.postgresq...-sql/2008-09/msg00016.php

Echter de oplossing weet ik niet meer, optimizer hints werken niet in postgresql. (Wel lijkt me duidelijk dat de analyze van cariolive23 dit niet boven water krijgt, wellicht moet je dan prepared statements analyzen)

[BalusC]

En ik ben weer een stapje verder gekomen. Setten van varchar (characterbased) parameters is de duurste stap. Integers als parameters setten kost niks. Zodra er een varchar parameter wordt gebruikt in een query, dan gaat het als stroop. Er lijkt iets mis te gaan tijdens het checken/parsen/escapen van een varchar in een sp.

JanDM schreef op donderdag 22 oktober 2009 @ 14:42:
De SP argumenten hebben hetzelfde type als de kolommen? Misschien zit ergens een langzame conversie tussen, dat zou verklaren waarom hard-coded values wel snel gaan.

Kolom2 is in dit specifieke voorbeeld een varchar(3) en kolom3 is een varchar(100).

[ Voor 36% gewijzigd door BalusC op 22-10-2009 14:50 ]

[leuk_he]

code:

CREATE FUNCTION replace_into_table1(NUMERIC, VARCHAR, VARCHAR) RETURNS void AS $$
 DECLARE
        tempvar1 Varchar(3);
        tempvar2 Varchar(100);
    BEGIN
        tempvar1=$2;
        tempvar2=$3;
        UPDATE table1 SET column2 = tempvar1 AND column3 = tempvar3 WHERE column1 = $1;
        IF FOUND THEN RETURN; END IF;
        INSERT INTO table1 (column1, column2, column3) VALUES ($1, tempvar1, tempvar2);
        RETURN;
    END;
$$ LANGUAGE plpgsql;

Kun je dit proberen? Ik kan me voorstellen dat hij by undefined lenght varchar als Text ziet waar je geen index op los kunt laten... of is de assignment tempvar1=$2 nu traag?

[BalusC]

Geen verschil.

Assignen van parameters aan variables kost overigens niks. De timestamps voor en na tempvar1=$2; tempvar2=$3; zijn gelijk. Zetten van de variable in de statement kost wel weer veel tijd.

[BalusC]

Oh f*k ik heb iets verkeerd getest en ben nu eindelijk tot de conclusie gekomen dat het aan de NUMERIC type ligt, niet aan de VARCHAR.

Pas zodra ik NUMERIC vervang door INT gaat het weer als de spreekwoordelijke speer

PostgreSQL was in 1 minuut klaar

Iedereen hartstikke bedankt voor het meedenken, het aanleren van tips/tricks en het sturen in de goede richting om het oorzaak te vinden

[ Voor 29% gewijzigd door BalusC op 22-10-2009 17:04 ]

[user109731]

BalusC schreef op donderdag 22 oktober 2009 @ 17:03:
PostgreSQL was in 1 minuut klaar.

Nice Is dat dezelfde data waar Oracle 5 minuten over doet? (Ik weet dat de COUNT(*) eruit is, desalniettemin erg nice)

[BalusC]

1 minuut is eigenlijk inclusief het transformeren van XML naar SQL mbv XSLT in Java (circa 5 tellen).

Ik zal mijn Oracle collega eens hinten over de FOUND.

[ Voor 5% gewijzigd door BalusC op 22-10-2009 17:11 ]

[cariolive23]

Gefeliciteerd! En natuurlijk graag gedaan.

Altijd mooi om te zien dat het weer sneller kan (1 minuut in PostgreSQL) dan wat je eigenlijk had verwacht (5 minuten in Oracle). Het kan vast nog wel wat sneller (met een betere config), maar die verbeteringen vallen in het niets bij de verbeteringen die je nu al hebt aangebracht.

Succes met de rest!

[BalusC]

Ja, 't is geweldig Zojuist nog 1x gedaan om te kijken of het nog steeds echt vlot gaat

C:\webapp\scripts>import
Importer started at Thu Oct 22 13:09:26 BOT 2009
Loading JDBC driver.. Succeed!
Transforming sa_product_line_20091021145450.xml to SQL using XSL.. Succeed!
Connecting database.. Succeed!
Executing 151178 sql statements.. Succeed!
Importer finished at Thu Oct 22 13:10:25 BOT 2009

C:\webapp\scripts>

[Olaf van der Spek]

BalusC schreef op donderdag 22 oktober 2009 @ 17:03:
Pas zodra ik NUMERIC vervang door INT gaat het weer als de spreekwoordelijke speer

Wat is het probleem met numeric dan?

[BalusC]

Wellicht omdat het te "breed" is. Zo kan het ook decimalen bevatten en precisie aangeven. Zie het maar als het gebruiken van BigDecimal ipv int in Java. Stom natuurlijk

Grappig is trouwens dat PostgreSQL tijdens de CREATE TABLE de non-decimal NUMERIC datatypes optimaliseert naar de INTEGER datatype. Wanneer ik de DDL van zo'n tabel bekijk, krijg ik INTEGER terug, dit zette me ook aan om INTEGER (INT) in de sp te gebruiken en hiermee was het probleem opgelost.

[ Voor 13% gewijzigd door BalusC op 22-10-2009 19:23 ]

[user109731]

Was het probleem niet dat NUMERIC op een INT kolom een full table scan doet in plaats van via de index te gaan? Dat zou het grote verschil in performance kunnen verklaren.

Anyway, PostgreSQL FTW

[DamadmOO]

Hoe snel is het volgende bij jou config?

SQL:

UPDATE table1
SET column2 = p2
, column3 = p3
FROM table1
JOIN TempTable ON table1.column1 = TempTable.p1

INSERT INTO table1 (column1, column2, column3)
SELECT p1, p2, p3
FROM TempTable
LEFT JOIN table1 ON TempTable.p1 = table1.column1
WHERE table1.column1 IS NULL

nadat je alle data eerst in een temp table gooit.

Ik heb zelf alleen SQL Server geinstalleerd staan en die deed 250k rijen in een tabel met 2M rijen binnen 30 seconden, en die 250k rijen waren eerlijk verdeeld tussen insert en update.

[BalusC]

Geen idee, maar ik wil wel even kwijt dat de minuut was getimed op de devmachine, een Latitude E5500 met C2D P8400, 4GB RAM en Seagate Momentus 7200.3. Productie heb ik nog niet getimed, is een Dell Quadcore Xeon machine, 8GB geheugen en SAS RAID10.

[cariolive23]

2x een query die met EXPLAIN ANALYZE VERBOSE wordt uitgevoerd:

SQL:

explain analyze verbose
SELECT true FROM (values(1::int),(2::int),(3::int) ) AS x(y)
WHERE
y = 1.0::numeric;

explain analyze verbose
SELECT true FROM (values(1::int),(2::int),(3::int) ) AS x(y)
WHERE
y = 1::int

En dan de resultaten:

'Values Scan on "*VALUES*" (cost=0.00..0.05 rows=1 width=0) (actual time=0.012..0.015 rows=1 loops=1)'
' Output: true'
' Filter: ((column1)::numeric = 1.0)'
'Total runtime: 0.048 ms'

'Values Scan on "*VALUES*" (cost=0.00..0.04 rows=1 width=0) (actual time=0.009..0.011 rows=1 loops=1)'
' Output: true'
' Filter: (column1 = 1)'
'Total runtime: 0.040 ms'

Het grootste verschil is het filter, bij de eerste query wordt de integer gecast naar een numeric om zo een vergelijking te kunnen maken. Dat is dus een extra stap en die kost hoe dan ook extra tijd.

Dit simpele voorbeeldje toont een verschil van 0.008ms, geen idee of dat representatief is, een vps op je laptop geeft op verschillende momenten zeer verschillende resultaten. Ken daar even niet al teveel waarde aan toe, je zou het op een echte server met een fatsoenlijke dataset moeten testen om hier echt iets over te kunnen roepen. Ik beperk me nu tot het filter waar een extra cast is waar te nemen.

Ps. Versie 8.4 gebruikt

[cariolive23]

BalusC schreef op donderdag 22 oktober 2009 @ 20:16:
Productie heb ik nog niet getimed, is een Dell Quadcore Xeon machine, 8GB geheugen en SAS RAID10.

Wanneer je de workload kan spreiden over 4 database connecties (je hebt tenslotte 4 cores tot je beschikking) gaat de boel ongeveer 4x zo snel. Knelpunt kan de I/O nog zijn, die heeft uiteraard ook een limiet.

Het zal ook nog uitmaken of je alles binnen één transactie uitvoert of dat iedere query zijn eigen transactie vormt. Een prepared statement voor de database call kan misschien ook nog iets verbeteren aan de performance, mocht je dat nog niet gebruiken.

[Zoijar]

Trouwens... je queries zijn volgens mij niet thread-safe. Een transactie wordt wel als geheel wel of niet uitgevoerd, maar de verschillende onderdelen daarin kunnen op elk moment uitgevoerd worden tussen transacties. Het is mogelijk dat er twee concurrent transactions om en om allebei eerst die update doen, zien dat er geen rows affected zijn, en vervolgens om en om een row proberen te inserten. De eerste zal goed gaan, maar de tweede geeft dan een duplicate key error oid.

Ik heb eenzelfde probleem (select or insert ipv update or insert) ooit zo opgelost in postgresql. Ik kan me ook nog herinneren dat ik daadwerkelijk race condities waarnam als ik op jouw manier die stored procedure vaak tegelijk aanriep vanuit verschillende threads.

SQL:

CREATE FUNCTION insert_host(character varying) RETURNS integer
    AS $_$
DECLARE
    rslt integer;
BEGIN
-- double checked locking to allow concurrent simple selects
    SELECT hostid INTO rslt FROM hosts WHERE name = $1;
    IF NOT FOUND THEN
        LOCK TABLE hosts IN SHARE ROW EXCLUSIVE MODE;
        SELECT hostid INTO rslt FROM hosts WHERE name = $1;
        IF NOT FOUND THEN
            INSERT INTO hosts(hostid, name, updated, last_request, request_interval) 
            VALUES(DEFAULT, $1, NULL, NULL, DEFAULT) 
            RETURNING hostid INTO rslt;
        END IF;
    END IF;
    return rslt;
END;
$_$
    LANGUAGE plpgsql;

[cariolive23]

Zoijar schreef op donderdag 22 oktober 2009 @ 20:55:
De eerste zal goed gaan, maar de tweede geeft dan een duplicate key error oid.

Die kun je binnen de sp afvangen en eventueel negeren, daar hebben ze exceptions voor uitgevonden. Kun je eenvoudig een mislukte insert n.a.v. een constraint violation gaan negeren.

Er zijn in PostgreSQL twee verschillende soorten transactie levels, Read Committed en Serializable, neem het level dat het beste bij jouw gebruik past. Een tablelock zou ik niet zo 1-2-3 toepassen, dat kan andere processen weer in de weg zitten. PostgreSQL kent een goed MVCC model, laat dit dan ook voor jou werken.

[Zoijar]

cariolive23 schreef op donderdag 22 oktober 2009 @ 21:09:
Die kun je binnen de sp afvangen en eventueel negeren, daar hebben ze exceptions voor uitgevonden. Kun je eenvoudig een mislukte insert n.a.v. een constraint violation gaan negeren.

Ja, ok, maar dan moet je wel consequent checken of je transacties falen en ze dan opnieuw proberen, anders heb je al snel een "lost update".

Er zijn in PostgreSQL twee verschillende soorten transactie levels, Read Committed en Serializable, neem het level dat het beste bij jouw gebruik past. Een tablelock zou ik niet zo 1-2-3 toepassen, dat kan andere processen weer in de weg zitten. PostgreSQL kent een goed MVCC model, laat dit dan ook voor jou werken.

Serializable maakt niet uit, dan zie je nog steeds allebei een snapshot waar de rij niet bestaat en je hem vervolgens gaat inserten.

[Confusion]

Zoijar schreef op donderdag 22 oktober 2009 @ 21:34:
[...]
Serializable maakt niet uit, dan zie je nog steeds allebei een snapshot waar de rij niet bestaat en je hem vervolgens gaat inserten.

Serializable hoort toch te beschermen tegen phantom rows? Een parallel lopende transactie die een rij insert die aan een WHERE clause in een van de queries in jouw serializable transactie voldoet, die hoort dan toch in de wacht gezet te worden?

[cariolive23]

Zoijar schreef op donderdag 22 oktober 2009 @ 21:34:
[...]

Ja, ok, maar dan moet je wel consequent checken of je transacties falen en ze dan opnieuw proberen, anders heb je al snel een "lost update".

Dat snap ik niet, jouw transactie zal niet falen omdat je binnen de sp met een exception een fout afvangt. Integendeel! Je bouwt juist een exception in om te voorkomen dat bij bekende mogelijke fouten waar je een prima alternatief voor hebt, de transactie faalt.

Maar wellicht bedoel je iets anders.

[BalusC]

Zoijar schreef op donderdag 22 oktober 2009 @ 20:55:
Trouwens... je queries zijn volgens mij niet thread-safe. Een transactie wordt wel als geheel wel of niet uitgevoerd, maar de verschillende onderdelen daarin kunnen op elk moment uitgevoerd worden tussen transacties. Het is mogelijk dat er twee concurrent transactions om en om allebei eerst die update doen, zien dat er geen rows affected zijn, en vervolgens om en om een row proberen te inserten. De eerste zal goed gaan, maar de tweede geeft dan een duplicate key error oid.

Het gaat om static data die hooguit eenmaal per dag bijgewerkt kan worden, dus daar hoef ik me geen zorgen over te maken.

[Zoijar]

Confusion schreef op donderdag 22 oktober 2009 @ 21:55:
Serializable hoort toch te beschermen tegen phantom rows? Een parallel lopende transactie die een rij insert die aan een WHERE clause in een van de queries in jouw serializable transactie voldoet, die hoort dan toch in de wacht gezet te worden?

Maar dan insert hij alsnog later een duplicate row. De beslissing om te inserten is dan al gemaakt op basis van een snapshot waar die rij nog niet bestaat. Welke foutmelding je dan precies krijgt hangt ervan af, kan iets met die row zijn, of een 'could not serialize', maar het feit blijft dat een van die twee transacties faalt. Serializable is niet dat de transacties als een block na elkaar worden uitgevoerd; ze worden nog steeds concurrent uitgevoerd in losse delen. Ok, het is niet echt een race conditie, want het is controleerbaar, maar je moet wel rekening houden met het opvangen van errors en je transacties dan eventueel opnieuw uitvoeren.

cariolive23 schreef op donderdag 22 oktober 2009 @ 22:28:
Dat snap ik niet, jouw transactie zal niet falen omdat je binnen de sp met een exception een fout afvangt. Integendeel! Je bouwt juist een exception in om te voorkomen dat bij bekende mogelijke fouten waar je een prima alternatief voor hebt, de transactie faalt.

Maar wellicht bedoel je iets anders.

Oh, zo, ja dat kan ook. Maar dat stond niet in de sp als gepost

[ Voor 19% gewijzigd door Zoijar op 22-10-2009 22:35 ]

[cariolive23]

Zoijar schreef op donderdag 22 oktober 2009 @ 22:33:
Oh, zo, ja dat kan ook. Maar dat stond niet in de sp als gepost

Klopt, maar ik noemde het wel in mijn reactie en de hyperlink wijst ook naar de uitleg over het gebruik van exceptions binnen plpgsql.

Die kun je binnen de sp afvangen