From: | Gerardo Herzig <gherzig(at)fmed(dot)uba(dot)ar> |
---|---|
To: | pgsql-general(at)postgresql(dot)org, pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | how to see "where" SQL is better than PLPGSQL |
Date: | 2014-09-28 18:30:15 |
Message-ID: | 796319273.990931.1411929015224.JavaMail.root@fmed.uba.ar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
Hi all. I see an entire database, with all the stored procedures writen in plpgsql. Off course, many (if not all) of that SP are simple inserts, updates, selects and so on.
So, i want to test and show the differences between doing the same function in pgpgsql vs. plain sql.
Im getting statistics (via collectd if that matters) and doing a modified version of the pgbench tests, just using pl (and sql) functions instead of the plain query:
\setrandom delta -5000 5000
BEGIN;
SELECT pgbench_accounts_upd_pl(:delta, :aid);
SELECT get_pgbench_accounts_pl(:aid);
SELECT pgbench_tellers_upd_pl(:delta, :tid);
SELECT pgbench_branches_upd_pl(:delta, :bid);
select pgbench_history_ins_pl(:tid, :bid, :aid, :delta);
END;
At first, pgbench is showing a difference between the "pl" and de "sql" versions:
(pl.scripts own the "PL" version, sql.script owns the "SQL" version of the test)
(This is a tiny netbook, with a dual core procesor)
gherzig(at)via:~> pgbench -c 2 -C -T 300 -f pl.script -U postgres test
duration: 300 s
number of transactions actually processed: 13524
tps = 45.074960 (including connections establishing)
tps = 75.260741 (excluding connections establishing)
gherzig(at)via:~> pgbench -c 2 -C -T 300 -f sql.script -U postgres test
starting vacuum...end.
duration: 300 s
number of transactions actually processed: 15125
tps = 50.412852 (including connections establishing)
tps = 92.058245 (excluding connections establishing)
So yeah, it looks like the "SQL" version is able to do a 10% more transactions.
However, i was hoping to see anothers "efects" of using sql (perhaps less load avg in the SQL version), at the OS level.
So, finnaly, the actual question:
¿Wich signals should i monitor, in order to show that PGPLSQL uses more resources than SQL?
Thanks!
Gerardo
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2014-09-28 18:49:50 | Re: [SQL] how to see "where" SQL is better than PLPGSQL |
Previous Message | Adrian Klaver | 2014-09-28 17:51:55 | Re: PANIC: could not create file "pg_xlog/xlogtemp.7884": No space left on device |
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2014-09-28 18:49:50 | Re: [SQL] how to see "where" SQL is better than PLPGSQL |
Previous Message | Dev Kumkar | 2014-09-26 08:06:22 | Re: [GENERAL] pg_multixact issues |