Re: High end server and storage for a PostgreSQL OLTP system

> By now, our system has never used "stored procedures" approach,
> due to the fact that we're staying on the minimum common SQL features
> that are supported by most db engines.
> I realize though that it would provide an heavy performance boost.

I feel your pain. Well, sometimes you have to bite the bullet and do a
couple of implementation specific hacks in especially time sensitive
components.

> > You also have the parse/bind interface
>
> This is something I have already engineered in our core classes
> (that use DBI + DBD::Pg), so that switching to 8.0 should
> automatically enable the "single-prepare, multiple-execute" behavior,
> saving a lot of query planner processing, if I understand correctly.

Yes. You save the planning step (which adds up, even for trivial plans).
The 'ExexPrepared' variant of prepared statement execution also provides
substantial savings (on server cpu load and execution time) because the
statement does not have to be parsed. Oh, and network traffic is reduced
correspondingly.

I know that the perl people were pushing for certain features into the
libpq library (describing prepared statements, IIRC). I think this
stuff made it into 8.0...have no clue about DBD::pg.

If everything is working the way it's supposed to, 8.0 should be faster
than 7.1 (like, twice faster) for what you are probably trying to do.
If it isn't, something else is wrong and it's very likely a solvable
problem.

In short, in pg 8.0, statement by statement query execution is highly
optimizeable at the driver level, much more so than 7.1. Law of
Unintended Consequences aside, this will translate into direct benefits
into your app if it uses this application programming model.

Merlin

Merlin Moncure wrote:

> > [...]
> > (...DBI + DBD::Pg), so that switching to 8.0 should
> > automatically enable the "single-prepare, multiple-execute" behavior,
> > saving a lot of query planner processing, if I understand correctly.
>
> [...]
>
> I know that the perl people were pushing for certain features into the
> libpq library (describing prepared statements, IIRC). I think this
> stuff made it into 8.0...have no clue about DBD::pg.

For the record: yes, DBD::Pg in CVS (> 1.32) has support
for server prepared statements.

> If everything is working the way it's supposed to, 8.0 should be faster
> than 7.1 (like, twice faster) for what you are probably trying to do.

In the next days I will be testing the entire application with the
same database only changing the backend from 7.1 to 8.0, so this is
a somewhat perfect condition to have a "real-world" benchmark
of Pg 8.0 vs 7.1.x performances.

--
Cosimo

Cosimo Streppone wrote:

> Merlin Moncure wrote:
>
> > If everything is working the way it's supposed to, 8.0 should be faster
> > than 7.1 (like, twice faster) for what you are probably trying to do.
>
> In the next days I will be testing the entire application with the
> same database only changing the backend from 7.1 to 8.0, so this is
> a somewhat perfect condition to have a "real-world" benchmark
> of Pg 8.0 vs 7.1.x performances.

The "next days" have come. I did a complete migration to Pg 8.0.1
from 7.1.3. It was a *huge* jump.
The application is exactly the same, also the database structure
is the same. I only dumped the entire 7.1.3 db, changed the backend
version, and restored the data in the 8.0.1 db.

The performance level of Pg 8 is at least *five* times higher
(faster!) than 7.1.3 in "query-intensive" transactions,
which is absolutely astounding.

In my experience, Pg8 handles far better non-unique indexes
with low cardinality built on numeric and integer types, which
is very common in our application.

--
Cosimo

Cosimo Streppone <cosimo(at)streppone(dot)it> writes:
> The performance level of Pg 8 is at least *five* times higher
> (faster!) than 7.1.3 in "query-intensive" transactions,
> which is absolutely astounding.

Cool.

> In my experience, Pg8 handles far better non-unique indexes
> with low cardinality built on numeric and integer types, which
> is very common in our application.

Yes, we've fixed a number of places where the btree code was inefficient
with large numbers of equal keys. I'm not sure that that explains a
5x speedup all by itself, though.

regards, tom lane