Performance of Parser?
Hello All,
I am using the latest 8.2 source that I compiled with Sun Studio 11 and
tested it on Solaris 10 11/06 against an app server. I find that the CPU
utilization was higher than I expected and started digging through it.
Aparently the top CPU usage comes from the following stack trace which
is roughly about 10-15% of the total the postgresql uses.
Anyway a real developer might make more sense from this than I can
libc_psr.so.1`memcpy+0x524
postgres`SearchCatCache+0x24
postgres`getBaseType+0x2c
postgres`find_coercion_pathway+0x14
postgres`can_coerce_type+0x58
postgres`func_match_argtypes+0x24
postgres`oper_select_candidate+0x14
postgres`make_op+0x1a8
postgres`transformAExprAnd+0x1c
postgres`transformWhereClause+0x18
postgres`transformUpdateStmt+0x94
postgres`transformStmt+0x1dc
postgres`do_parse_analyze+0x18
postgres`parse_analyze_varparams+0x30
postgres`exec_parse_message+0x2fc
postgres`PostgresMain+0x117c
postgres`BackendRun+0x248
postgres`BackendStartup+0xf4
postgres`ServerLoop+0x4c8
postgres`PostmasterMain+0xca0
FUNCTION COUNT PCNT
postgres`can_coerce_type 1 0.1%
postgres`find_coercion_pathway 11 0.9%
postgres`SearchCatCache 43 3.4%
libc_psr.so.1`memcpy 136 10.6%
The appserver is basically using bunch of prepared statements that the
server should be executing directly without doing the parsing again.
Since it is the parser module that invokes the catalog search, does
anybody know how to improve the can_coerce_type function in order to
reduce the similar comparisions again and again for same type of statements.
I also wanted to check if postgresql stores prepared statements at the
server level or does it parse each incoming prepared statement again?
Any insight will help here in understanding what it is attempting to do
and what can be the possible workarounds.
Regards,
Jignesh
Jignesh Shah <J.K.Shah@Sun.COM> writes:
The appserver is basically using bunch of prepared statements that the
server should be executing directly without doing the parsing again.
Better have another look at that theory, because you're clearly spending
a lot of time in parsing (operator resolution to be specific). I think
your client code is failing to re-use prepared statements the way you
think it is.
regards, tom lane
Jignesh Shah wrote:
The appserver is basically using bunch of prepared statements that the
server should be executing directly without doing the parsing again.
The first thing you need to do is turn on statement logging, if you
haven't already, to verify this statement.
cheers
andrew
On 13-Jan-07, at 7:24 PM, Tom Lane wrote:
Jignesh Shah <J.K.Shah@Sun.COM> writes:
The appserver is basically using bunch of prepared statements that
the
server should be executing directly without doing the parsing again.Better have another look at that theory, because you're clearly
spending
a lot of time in parsing (operator resolution to be specific). I
think
your client code is failing to re-use prepared statements the way you
think it is.
This is exactly what is happening. The driver needs to cache
statements for this to work.
Dave
Show quoted text
regards, tom lane
---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that
your
message can get through to the mailing list cleanly