pgstats_initstats() cost

----- Original Message -----
From: "Gavin Sherry" <swm@linuxworld.com.au>

I am still researching ways of increasing performance of yacc parsers --
there is a very small amount of information on the Web concerning this --

I know some people who will tell you that the best way of improving
performance in this area is not to use yacc (or bison) parsers ...

OTOH we need to understand exactly what you were profiling - if it is 1
dynamic sql statement per insert then it might not be too close to the real
world - a high volume program is likely to require 1 parse per many many
executions, isn't it?

cheers

andrew

On Mon, 11 Aug 2003, Andrew Dunstan wrote:

----- Original Message -----
From: "Gavin Sherry" <swm@linuxworld.com.au>

I am still researching ways of increasing performance of yacc parsers --
there is a very small amount of information on the Web concerning this --

I know some people who will tell you that the best way of improving
performance in this area is not to use yacc (or bison) parsers ...

Yes. Cost of maintenance vs. performance cost...

OTOH we need to understand exactly what you were profiling - if it is 1
dynamic sql statement per insert then it might not be too close to the real
world - a high volume program is likely to require 1 parse per many many
executions, isn't it?

I wasn't interested in measuring the performance of yacc -- since I know
it is bad. It was a basic test which wasn't even meant to be real
world. It just seemed interesting that the numbers were three times slower
than other databases I ran it on. Here is the script which generates the
SQL:

echo "create table abc(t text);"
echo "begin;"
c=0
while [ $c -lt 100000 ]
do
echo "insert into abc values('thread1');";
c=$[$c+1]
done
echo "commit;"

Thanks,

Gavin

world. It just seemed interesting that the numbers were three times slower
than other databases I ran it on. Here is the script which generates the

You were comparing against databases with similar safety nets to
guarantee against dataloss?

On Tue, 12 Aug 2003, Rod Taylor wrote:

world. It just seemed interesting that the numbers were three times slower
than other databases I ran it on. Here is the script which generates the

You were comparing against databases with similar safety nets to
guarantee against dataloss?

I am in the process of reading through the logging/versioning code of them
and the others definately do. My main interest is in determining how to
reduce the cost of pgstats_initstats().

Thanks,

Gavin

Gavin Sherry <swm@linuxworld.com.au> writes:

but pgstat_initstats() caught my eye. This gets called about 6 times per
insert (I did 100000 inserts) and the major cost appears to relate to the
linear pgStatTabstatMessages. The comparative performance of
hash_search() suggests that pgStatTabstatMessages may benefit from use of
a hash. However, it seems unreasonable that we're doing work at all in
pgstat_initstats() if the user is not interested in query/block/tuple
stats.

The coding in the search loop could perhaps be tightened a little, but
I'd think the last point should be addressed by dropping out via the
"no_stats" exit if stats aren't being gathered.

I doubt a hash is worth maintaining, because the active tabstat entries
should only be for tables that are being touched in the current command
(thus, there are not more than six in your example). I'm not sure why
it takes so much time to look through six entries though ...

regards, tom lane

On Tue, 12 Aug 2003, Tom Lane wrote:

Gavin Sherry <swm@linuxworld.com.au> writes:

but pgstat_initstats() caught my eye. This gets called about 6 times per
insert (I did 100000 inserts) and the major cost appears to relate to the
linear pgStatTabstatMessages. The comparative performance of
hash_search() suggests that pgStatTabstatMessages may benefit from use of
a hash. However, it seems unreasonable that we're doing work at all in
pgstat_initstats() if the user is not interested in query/block/tuple
stats.

The coding in the search loop could perhaps be tightened a little, but
I'd think the last point should be addressed by dropping out via the
"no_stats" exit if stats aren't being gathered.

I doubt a hash is worth maintaining, because the active tabstat entries
should only be for tables that are being touched in the current command
(thus, there are not more than six in your example). I'm not sure why
it takes so much time to look through six entries though ...

Neither. I might look into it further later, but here's a patch to exit
out of pgstat_initstats() if we're not collecting stats (attached).

Thanks,

Gavin

I said:

I doubt a hash is worth maintaining, because the active tabstat entries
should only be for tables that are being touched in the current command
(thus, there are not more than six in your example). I'm not sure why
it takes so much time to look through six entries though ...

I replicated your example, and soon found that in fact there were
forty-three active tabstat slots, which makes pgstat_initstats a little
bit more credible as a time-waster.

The reason why there were forty-three, in a statement that's only
touching one relation, is that with the present coding of pgstats,
if you have stats gathering off then there will be an active entry
for every relation that's been touched since backend launch.
pgstat_report_tabstat() should have flushed the entries, but
*if reporting is disabled then it fails to do so*.

This is clearly a bug. Will fix.

regards, tom lane

Hi everybody

It's my first post here, so be indulgent ;)

Just to confirm : if i do

$sql1='insert into "Enfant" ("NomEnfant","PrenomEnfant") VALUES
('.$NomEnfant.','.$PrenomEnfant.')

$sql2='insert into "IndividuEnfant" ("IdIndividu","IdEnfant") VALUES
('.$IdIndividu.',currval(\'"Enfant_Seq"\'));

$sql=$sql1.$sql2;

$Res=pg_query($sql);

so 2 query concatenation a a only one, send to postgress database : is it
EXACTLY the same thing than a classic transaction with a begin and a commit
or rollback ?

Tcks, it's just i dont want to write everything again in 6 months.....

S.L.

PS : why limitation to 8 patrameters in stored procedures ??????

Gavin Sherry <swm@linuxworld.com.au> writes:

Neither. I might look into it further later, but here's a patch to exit
out of pgstat_initstats() if we're not collecting stats (attached).

Applied along with other fixes.

regards, tom lane

Sla,

PS : why limitation to 8 patrameters in stored procedures ??????

What version of PostgreSQL are you using?

The limit is 16 parameters for 7.1 and 7.2, raised to 32 parameters in 7.3 and
after. Further, you can raise the limit yourself at compile-time, although
I understand it incurrs a penalty in index efficiency.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Gavin Sherry <swm@linuxworld.com.au> writes:

I wasn't interested in measuring the performance of yacc -- since I know
it is bad. It was a basic test which wasn't even meant to be real
world. It just seemed interesting that the numbers were three times slower
than other databases I ran it on. Here is the script which generates the
SQL:

echo "create table abc(t text);"
echo "begin;"
c=0
while [ $c -lt 100000 ]
do
echo "insert into abc values('thread1');";
c=$[$c+1]
done
echo "commit;"

Of course the obvious way of getting rid of the parser overhead is not
to parse everytime --- viz, to use prepared statements.

I have just finished running some experiments that compared a series of
INSERTs issued via PQexec() versus preparing an INSERT command and then
issuing new-FE-protocol Bind and Execute commands against the prepared
statement. With a test case like the above (one target column and a
prepared statement like "insert into abc values($1)"), I saw about a 30%
speedup. (Or at least I did after fixing a couple of bottlenecks in the
backend's per-client-message loop.)

Of course, the amount of work needed to parse this INSERT command is
pretty trivial. With just a slightly more complex test case:
create table abc (f1 text, f2 int, f3 float8);
and a prepared statement like
PREPARE mystmt(text,int,float8) AS insert into abc values($1,$2,$3)
there was a factor of two difference in the speed.

This leaves us with a bit of a problem, though, because there isn't any
libpq API that allows access to this speedup. I put in a routine to
support Parse/Bind/Execute so that people could use out-of-line
parameters for safety reasons --- but there's no function to do
Bind/Execute against a pre-existing prepared statement. (I had to make
a hacked version of libpq to do the above testing.)

I'm beginning to think that was a serious omission. I'm tempted to fix
it, even though we're past feature freeze for 7.4. Comments?

regards, tom lane

On Tue, 2003-08-12 at 15:36, Tom Lane wrote:

Gavin Sherry <swm@linuxworld.com.au> writes:

I wasn't interested in measuring the performance of yacc -- since I know
it is bad. It was a basic test which wasn't even meant to be real
world. It just seemed interesting that the numbers were three times slower
than other databases I ran it on. Here is the script which generates the
SQL:

echo "create table abc(t text);"
echo "begin;"
c=0
while [ $c -lt 100000 ]
do
echo "insert into abc values('thread1');";
c=$[$c+1]
done
echo "commit;"

Of course the obvious way of getting rid of the parser overhead is not
to parse everytime --- viz, to use prepared statements.

I have just finished running some experiments that compared a series of
INSERTs issued via PQexec() versus preparing an INSERT command and then
issuing new-FE-protocol Bind and Execute commands against the prepared
statement. With a test case like the above (one target column and a
prepared statement like "insert into abc values($1)"), I saw about a 30%
speedup. (Or at least I did after fixing a couple of bottlenecks in the
backend's per-client-message loop.)

Of course, the amount of work needed to parse this INSERT command is
pretty trivial. With just a slightly more complex test case:
create table abc (f1 text, f2 int, f3 float8);
and a prepared statement like
PREPARE mystmt(text,int,float8) AS insert into abc values($1,$2,$3)
there was a factor of two difference in the speed.

Do you happen to have any numbers comparing prepared inserts in a single
transaction against copy?

On Tue, 12 Aug 2003, Tom Lane wrote:

I have just finished running some experiments that compared a series of
INSERTs issued via PQexec() versus preparing an INSERT command and then
issuing new-FE-protocol Bind and Execute commands against the prepared
statement. With a test case like the above (one target column and a
prepared statement like "insert into abc values($1)"), I saw about a 30%
speedup. (Or at least I did after fixing a couple of bottlenecks in the
backend's per-client-message loop.)

[snip]

This leaves us with a bit of a problem, though, because there isn't any
libpq API that allows access to this speedup. I put in a routine to
support Parse/Bind/Execute so that people could use out-of-line
parameters for safety reasons --- but there's no function to do
Bind/Execute against a pre-existing prepared statement. (I had to make
a hacked version of libpq to do the above testing.)

I'm beginning to think that was a serious omission. I'm tempted to fix
it, even though we're past feature freeze for 7.4. Comments?

I think it would be well worth waiting for this feature. I often bind
parameters in Perl's DBD::Pg, looking to the future when that doesn't just
handle quoting, but also uses faster cached prepared queries. It'd be
great to see that in 7.4.

Jon

This leaves us with a bit of a problem, though, because there isn't
any libpq API that allows access to this speedup. I put in a
routine to support Parse/Bind/Execute so that people could use
out-of-line parameters for safety reasons --- but there's no
function to do Bind/Execute against a pre-existing prepared
statement. (I had to make a hacked version of libpq to do the above
testing.)

I'm beginning to think that was a serious omission. I'm tempted to
fix it, even though we're past feature freeze for 7.4. Comments?

On a quasi-similar note (and unless I've missed how to do this), you
can't create a cursor from a prepared statement, which I found
frustrating. On frequently used queries, I've gotten in the habbit of
preparing the queries at connect time and then executing the query,
but with larger queries, it's problematic to not be able to use a
cursor in addition to the prepared statement.

-sc

--
Sean Chittenden

On Tue, Aug 12, 2003 at 03:36:07PM -0400, Tom Lane wrote:

This leaves us with a bit of a problem, though, because there isn't any
libpq API that allows access to this speedup. I put in a routine to
support Parse/Bind/Execute so that people could use out-of-line
parameters for safety reasons --- but there's no function to do
Bind/Execute against a pre-existing prepared statement. (I had to make
a hacked version of libpq to do the above testing.)

I'm beginning to think that was a serious omission. I'm tempted to fix
it, even though we're past feature freeze for 7.4. Comments?

Please do. It could be argued that the feature is already there, and that
the fact that there's no way to use it through libpq is actually a bug.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Hay que recordar que la existencia en el cosmos, y particularmente la
elaboraci���n de civilizaciones dentre de ���l no son, por desgracia,
nada id���licas" (Ijon Tichy)

Rod Taylor <rbt@rbt.ca> writes:

Do you happen to have any numbers comparing prepared inserts in a single
transaction against copy?

COPY is about a factor of 6 faster, it appears. I got 11.8 versus
1.87 seconds for loading the same amount of data (with the 3-column
test table). So COPY's not gonna go away any time soon.

regards, tom lane

Sean Chittenden <sean@chittenden.org> writes:

I'm beginning to think that was a serious omission. I'm tempted to
fix it, even though we're past feature freeze for 7.4. Comments?

On a quasi-similar note (and unless I've missed how to do this), you
can't create a cursor from a prepared statement, which I found
frustrating.

Hmm. I'd be willing to see that put on TODO, but it strikes me as a
rather large change for post-feature-freeze; it'd require fooling around
in substantial parts of the backend. (What I was suggesting above only
requires adding one or two routines to libpq, which is a lot less
invasive...)

regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Of course the obvious way of getting rid of the parser overhead is not
to parse everytime --- viz, to use prepared statements.

I think this would be nice to have too... On a similar note (I think
anyway) I wasn't able to find any functions for bulk dumps into memory
space from a select statement. This would probably be alot more work
but I find it very useful with other databases I work on (mainly
Oracle).

You probably know but I'll quickly outline it to point out the
differences, as I see them, from the 'COPY' ability. Basically the user
defines their own C structure and then malloc's an array of them. The
user then tells the database the type, offset from start of structure
and the skip (size of structure) for each column returned by the select
statement. The user can then do 'bulk' grabs with a single command into
the memory space allocated, doing more than one and changing the offsets
inbetween if more is returned than was initially allocated for. The
user can realloc or allocate new segments and do their own handling of
the segments if they choose.

The same is true for 'insert' statements, in reverse, of course. This
avoids alot of unnecssary parsing and type conversion (where possible).
This does give more work to the library since it has to be able to do
type conversions in some cases where the database type and the user
requested type differ.

Thanks,
Stephen

Tom Lane wrote:

I'm beginning to think that was a serious omission. I'm tempted to fix
it, even though we're past feature freeze for 7.4. Comments?

Seems pretty well isolated. If you're tallying votes, count me as a "yay".

Joe