FUNC_MAX_ARGS benchmarks

Thanks. That looks acceptable to me, and a good test.

---------------------------------------------------------------------------

Neil Conway wrote:

Ok, here are some crude benchmarks to attempt to measure the effect of
changing FUNC_MAX_ARGS. The benchmark script executed:

CREATE FUNCTION test_func(int, int, int, int, int, int, int, int)
RETURNS INTEGER AS 'SELECT $1 + $2 + $3 + $4 + $5 + $6 + $7 + $8'
LANGUAGE 'sql' VOLATILE;

Followed by 30,000 calls of:

SELECT test_func(i, i, i, i, i, i, i, i);

(Where i was the iteration number)

I ran the test several times and averaged the results -- the wall-clock
time remained very consistent throughout the runs. Each execution of the
script took about 30 seconds. The machine was otherwise idle, and all
other PostgreSQL settings were at their default values.

With FUNC_MAX_ARGS=16:

28.832
28.609
28.726
28.680

(average = 28.6 seconds)

With FUNC_MAX_ARGS=32:

29.097
29.337
29.138
28.985
29.231

(average = 29.15 seconds)

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

With FUNC_MAX_ARGS=16:
(average = 28.6 seconds)
With FUNC_MAX_ARGS=32:
(average = 29.15 seconds)

That is almost a 2 percent cost. Shall we challenge someone to get us
back 2 percent from somewhere before the 7.3 release? Optimizing a hot
spot might do it...

- Thomas

On Thu, 1 Aug 2002, Thomas Lockhart wrote:

With FUNC_MAX_ARGS=16:
(average = 28.6 seconds)
With FUNC_MAX_ARGS=32:
(average = 29.15 seconds)

That is almost a 2 percent cost. Shall we challenge someone to get us
back 2 percent from somewhere before the 7.3 release? Optimizing a hot
spot might do it...

The other side of the coin ... have you, in the past, gained enough
performance to allow us a 2% slip for v7.3?

Someone mentioned that the SQL spec called for a 128byte NAMELENTH ... is
there similar for FUNC_MAX_ARGS that we aren't adhering to? Or is that
one semi-arbitrary?

On Fri, 2002-08-02 at 09:28, Marc G. Fournier wrote:

On Fri, 2 Aug 2002, Andrew Sullivan wrote:

On Fri, Aug 02, 2002 at 10:39:47AM -0400, Tom Lane wrote:

Actually, plpgsql is pretty expensive too. The thing to be benchmarking
is applications of plain old built-in-C functions and operators.

I thought part of the justification for this was for the OpenACS
guys; don't they write everything in TCL?

Nope, the OpenACS stuff relies on plpgsql functions ... the 'TCL' is the
web pages themselves, vs using something like PHP ... I may be wrong, but
I do not believe any of the functions are in TCL ...

Nope, some are written in Tcl. Most are in plpgsql, mainly I believe so
that the port between Oracle and PG is easier.

--brett

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Brett Schwarz
brett_schwarz AT yahoo.com

Perhaps I'm not remembering correctly, but don't SQL functions still
have an abnormally high cost of execution compared to plpgsql?

Want to try the same thing with a plpgsql function?

Rod Taylor <rbt@zort.ca> writes:

Perhaps I'm not remembering correctly, but don't SQL functions still
have an abnormally high cost of execution compared to plpgsql?

Want to try the same thing with a plpgsql function?

Actually, plpgsql is pretty expensive too. The thing to be benchmarking
is applications of plain old built-in-C functions and operators.

Also, there are two components that I'd be worried about: one is the
parser's costs of operator/function lookup, and the other is runtime
overhead. Runtime overhead is most likely concentrated in the fmgr.c
interface functions, which tend to do MemSets to zero out function
call records. I had had a todo item to eliminate the memset in favor
of just zeroing what needs to be zeroed, at least in the one- and two-
argument cases which are the most heavily trod code paths. This will
become significantly more important if FUNC_MAX_ARGS increases.

regards, tom lane

On Fri, Aug 02, 2002 at 10:39:47AM -0400, Tom Lane wrote:

Actually, plpgsql is pretty expensive too. The thing to be benchmarking
is applications of plain old built-in-C functions and operators.

I thought part of the justification for this was for the OpenACS
guys; don't they write everything in TCL?

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

On Fri, 2 Aug 2002, Andrew Sullivan wrote:

On Fri, Aug 02, 2002 at 10:39:47AM -0400, Tom Lane wrote:

Actually, plpgsql is pretty expensive too. The thing to be benchmarking
is applications of plain old built-in-C functions and operators.

I thought part of the justification for this was for the OpenACS
guys; don't they write everything in TCL?

Nope, the OpenACS stuff relies on plpgsql functions ... the 'TCL' is the
web pages themselves, vs using something like PHP ... I may be wrong, but
I do not believe any of the functions are in TCL ...

"Marc" == Marc G Fournier <scrappy@hub.org> writes:

Marc> On Fri, 2 Aug 2002, Andrew Sullivan wrote:

On Fri, Aug 02, 2002 at 10:39:47AM -0400, Tom Lane wrote: > >
Actually, plpgsql is pretty expensive too. The thing to be
benchmarking > is applications of plain old built-in-C
functions and operators.

I thought part of the justification for this was for the
OpenACS guys; don't they write everything in TCL?

Marc> Nope, the OpenACS stuff relies on plpgsql functions ... the
Marc> 'TCL' is the web pages themselves, vs using something like
Marc> PHP ... I may be wrong, but I do not believe any of the
Marc> functions are in TCL ...

That's true. We have intentionally avoided adding pl/tcl functions
into the db. The postgresql db stuff relies extensively on plpgsql,
while the oracle db stuff relies on pl/sql to provide equivalent
functionality.

On the other hand, all of the web server stuff is implemented on Aolserver
which uses Tcl as a scripting language.

Regards,

Dan

On Fri, Aug 02, 2002 at 12:35:10PM -0400, Daniel Wickstrom wrote:

On the other hand, all of the web server stuff is implemented on Aolserver
which uses Tcl as a scripting language.

I think this is why I was confused. Thanks, all.

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Andrew Sullivan <andrew@libertyrms.info> writes:

On Fri, Aug 02, 2002 at 10:39:47AM -0400, Tom Lane wrote:

Actually, plpgsql is pretty expensive too. The thing to be benchmarking
is applications of plain old built-in-C functions and operators.

I thought part of the justification for this was for the OpenACS
guys; don't they write everything in TCL?

Not relevant. The concern about increasing FUNC_MAX_ARGS is the
overhead it might add to existing functions that don't need any
more arguments. Worst case for that (percentagewise) will be
small built-in functions, like say int4add.

regards, tom lane

Thomas Lockhart wrote:

With FUNC_MAX_ARGS=16:
(average = 28.6 seconds)
With FUNC_MAX_ARGS=32:
(average = 29.15 seconds)

That is almost a 2 percent cost. Shall we challenge someone to get us
back 2 percent from somewhere before the 7.3 release? Optimizing a hot
spot might do it...

I wasn't terribly concerned because this wasn't a 2% on normal workload
test, it was a 2% bang on function calls as fast as you can test.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

...

I wasn't terribly concerned because this wasn't a 2% on normal workload
test, it was a 2% bang on function calls as fast as you can test.

Yeah, good point. But if we can get it back somehow that would be even
better :)

- Thomas

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I wasn't terribly concerned because this wasn't a 2% on normal workload
test, it was a 2% bang on function calls as fast as you can test.

No, it was a 2% hit on rather slow functions with only one call made
per query issued by the client. This is not much of a stress test.

A more impressive comparison would be

select 2+2+2+2+2+2+ ... (iterate 10000 times or so)

and see how much that slows down.

regards, tom lane

Tom Lane wrote:

No, it was a 2% hit on rather slow functions with only one call made
per query issued by the client. This is not much of a stress test.

A more impressive comparison would be

select 2+2+2+2+2+2+ ... (iterate 10000 times or so)

and see how much that slows down.

I ran a crude test as follows (using a PHP script on the same machine.
Nothing else going on at the same time):

do 100 times
select 2+2+2+2+2+2+ ... iterated 9901 times

#define INDEX_MAX_KEYS 16, 32, 64, & 128
#define FUNC_MAX_ARGS INDEX_MAX_KEYS
make all
make install
initdb

The results were as follows:
INDEX_MAX_KEYS 16 32 64 128
-----+-------+------+--------
Time in seconds 48 49 51 55

Joe

Joe Conway <mail@joeconway.com> writes:

I ran a crude test as follows (using a PHP script on the same machine.
Nothing else going on at the same time):

do 100 times
select 2+2+2+2+2+2+ ... iterated 9901 times

The results were as follows:
INDEX_MAX_KEYS 16 32 64 128
-----+-------+------+--------
Time in seconds 48 49 51 55

Okay, that seems like a good basic test.

Did you happen to make any notes about the disk space occupied by the
database? One thing I was worried about was the bloat that'd occur
in pg_proc, pg_index, and pg_proc_proname_args_nsp_index. Aside from
costing disk space, this would indirectly slow things down due to more
I/O to read these tables --- an effect that probably your test couldn't
measure, since it wasn't touching very many entries in any of those
tables.

Looks like we could go for 32 without much problem, though.

regards, tom lane

Tom Lane wrote:

Did you happen to make any notes about the disk space occupied by the
database? One thing I was worried about was the bloat that'd occur
in pg_proc, pg_index, and pg_proc_proname_args_nsp_index. Aside from
costing disk space, this would indirectly slow things down due to more
I/O to read these tables --- an effect that probably your test couldn't
measure, since it wasn't touching very many entries in any of those
tables.

No, but it's easy enough to repeat. I'll do that today sometime.

Joe

Hannu Krosing <hannu@tm.ee> writes:

How hard would it be to change pg_proc.proargtypes from oidvector to _oid

Lack of btree index support for _oid would be the first hurdle.

Even if we wanted to do that work, there'd be some serious breakage
of client queries because of the historical differences in output format
and subscripting. (oidvector indexes from 0, _oid from 1. Which is
pretty bogus, but if the regression tests are anything to judge by there
are probably a lot of queries out there that know this.)

This could also get the requested 2% speedup,

I'm not convinced that _oid would be faster.

All in all, it doesn't seem worth the trouble compared to just kicking
FUNC_MAX_ARGS up a notch. At least not right now. I think we've
created quite enough system-catalog changes for one release cycle ;-)

regards, tom lane

On Sat, 2002-08-03 at 18:41, Tom Lane wrote:

Joe Conway <mail@joeconway.com> writes:

I ran a crude test as follows (using a PHP script on the same machine.
Nothing else going on at the same time):

do 100 times
select 2+2+2+2+2+2+ ... iterated 9901 times

The results were as follows:
INDEX_MAX_KEYS 16 32 64 128
-----+-------+------+--------
Time in seconds 48 49 51 55

Okay, that seems like a good basic test.

Did you happen to make any notes about the disk space occupied by the
database? One thing I was worried about was the bloat that'd occur
in pg_proc, pg_index, and pg_proc_proname_args_nsp_index. Aside from
costing disk space, this would indirectly slow things down due to more
I/O to read these tables --- an effect that probably your test couldn't
measure, since it wasn't touching very many entries in any of those
tables.

How hard would it be to change pg_proc.proargtypes from oidvector to _oid and hope
that toasting will take care of the rest ?

This could also get the requested 2% speedup, not to mention the
potential for up to 64K arguments ;)

---------------
Hannu