[DB-SIG] Python/PostgreSQL API performance comparison (update)

Marcos Sánchez Provencio msanchez at grupoburke.com
Sun May 25 23:34:31 EDT 2003


Chris Cogdon wrote:

> It was suggested to me that PgSQL would perform better if it was 
> configured to return a simple list of tuples rather than a PgResultSet 
> (which has some cool abilities, like being able to do 
> cur.fetchone()['columnname'], for example). So, I've updated my 
> performance comparison code ( 
> http//onca.catsden.net/~chris/testtimes.py ) to include the new case, 
> and the current results are like this:
>
> method [ user time, system time, child user time, child system time, 
> real time ]
> pg [ 0.120, 0.020, 0.000, 0.000, 29.580 ]
> pgdb [ 4.490, 0.010, 0.000, 0.000, 33.250 ]
> PgSQL [ 3.650, 0.010, 0.000, 0.000, 41.150 ]
> PgSQL (nocursor) [ 3.620, 0.010, 0.000, 0.000, 32.320 ]
> PgSQL (nocursor,list) [ 3.550, 0.010, 0.000, 0.000, 32.360 ]
> PoPy [ 0.120, 0.000, 0.000, 0.000, 28.990 ]
>
> There is a very very small performance gain by using straight lists in 
> pyPgSQL.
>
> Something I forgot to mention last time. The user/system times above 
> are for the 'application' process only. Obviously, there is a lot of 
> work being dome by the postmaster which is only factored into the 
> above times as part of the 'real time' it takes to do the full query.
>
> The performance loss of using DECLARE cursors (shown in the PgSQL 
> non-nocursor above) is probably extra work being done by the DMBS 
> causing the longer response time. In short, DECLARE cursors probably 
> save memory on the application side at the expense of extra work at 
> the DMBS. If the application and DMBS reside on the same machine 
> (pretty common for web applications), then there's probably no point 
> using DECLARE cursors.
>
> Is this kind of thing useful to folk, or would you prefer me shut up 
> about it? :)
>
FWIW, I am very interested in these kind of results.

One note for multithreaded appserver (webware, zope...). Due to the 
global lock, the DBMS and the app act funny regarding balance of load. 
If the app is too heavy, it can suck up one CPU while the other is idle. 
In this case, it would be worth to pass some load to the DBMS.




More information about the DB-SIG mailing list