Обсуждение: 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 > > 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 >