Обсуждение: PC Week Labs benchmark results

Hi everybody, I'm done my tests of PostgreSQL and Interbase.

I concentrated on two tests, an OLTP Single Read Test, where we read a
single row out of a 200K row indexed table, and the OLTP Read Mix Test,
which is a mix of about 30 queries, about half single table selects and
the other half joins of various complexity (up to four way). For both of
these tests, InterBase was about 2x to 2.5x as fast as PostgreSQL. In
multiuser tests (up to 100 users), the situation was reversed, with
PostgreSQL close to 3 times faster at peak throughput (which was at 50
concurrent users). The reason why is that InterBase on Linux has a
process-per-connection architecture without a shared cache. As such, I had
to really limit cache sizes to allow 100 users to connect, and that really
hurt InterBase's performance.

I ran both PostgreSQL and InterBase with syncs turned off, and used a
cache of 65536 4KB pages and 4000K of sort buffer.

Here's a list of things about PostgreSQL I had problems with:

1. "Null" is not accepted keyword on "create table" ("not null" is ok)
2. copy command 'with null as' option not functional
3. try to create an index on a numeric and "no operator class for
'numeric' data type" error message results. Numerics not indexable?
4. no outer join -- I had to drop one query because of this
5. no alter table add constraint
6. select count(distinct *) from a view gives a parser error on distinct
-- distinct keyword not supported here?
7. one query (dss_select_05) has an avg on a numeric field. I got an
overflow error (is there a cast to a longer type?). When the avg on
numeric field is removed, the query consumes memory rapidly and doesn't
terminate. I dropped this query.
8. Can't start postmaster with more than 65536 buffers as I get a "FATAL
1:  couldn't
initialize shared buffer pool Hash Tbl". Variable overflow?

Thanks for the tuning suggestions I received from various people.

Also, is PostgreSQL 7 expected to be SQL-92 compliant? It's pretty close
now.

I'll be posting complete scripts and C code when the story goes to print
on Feb. 14.

Regards,
Tim Dyck
Senior Analyst
PC Week Labs

Timothy Dyck wrote:
> 
> Hi everybody, I'm done my tests of PostgreSQL and Interbase.
> 
> I concentrated on two tests, an OLTP Single Read Test, where we read a
> single row out of a 200K row indexed table, and the OLTP Read Mix Test,
> which is a mix of about 30 queries, about half single table selects and
> the other half joins of various complexity (up to four way). For both of
> these tests, InterBase was about 2x to 2.5x as fast as PostgreSQL. In
> multiuser tests (up to 100 users), the situation was reversed, with
> PostgreSQL close to 3 times faster at peak throughput (which was at 50
> concurrent users). The reason why is that InterBase on Linux has a
> process-per-connection architecture without a shared cache. As such, I had
> to really limit cache sizes to allow 100 users to connect, and that really
> hurt InterBase's performance.
> 
> I ran both PostgreSQL and InterBase with syncs turned off, and used a
> cache of 65536 4KB pages and 4000K of sort buffer.
> 
> Here's a list of things about PostgreSQL I had problems with:
> 
> 1. "Null" is not accepted keyword on "create table" ("not null" is ok)

There was some discussion of this in the lists in the past:

http://www.postgresql.org/mhonarc/pgsql-hackers/1998-12/msg00546.html

: : : Now that we have the syntax problem straightened out:
I'm still : confused
: : : about the semantics.  Does a "NULL" constraint say
that the field
: : : *must* be null, or only that it *can* be null (in
which case NULL is
: : : just a noise word, since that's the default
condition)?  I had assumed
: : : the former, but Bruce seemed to think the latter...
: : 
: : Can be null.  Noise word.  At least that is what I
rememeber Thomas
: : saying, and because it was noise, we removed it.  In
fact, it doesn't
: : look like the standard accepts it, but there is no
reason we can't.

: This NULL clause is not part of constraints it is a
default option and
: we already support it,
: there's nothing like: 
: CREATE TABLE table1 (field1 type NULL) in SQL92.

: but the following is SQL92 and it works on PostgreSQL:
: prova=> CREATE TABLE table1 (field1 INTEGER DEFAULT NULL);
: CREATE

> 2. copy command 'with null as' option not functional
> 3. try to create an index on a numeric and "no operator class for
> 'numeric' data type" error message results. Numerics not indexable?

That's fixed in current sources...its too bad you aren't
reviewing this a couple of months from now -- but I bet you
hear a lot of that...

> 4. no outer join -- I had to drop one query because of this

That's always been annoying, although it can be simulated
easily with:

SELECT t1.x, t2.y 
FROM t1, t2
WHERE t1.x = t2.x
UNION
SELECT t1.x, NULL
FROM t1 WHERE NOT EXISTS ( SELECT t2.x FROM t2 WHERE t1.x =
t2.x );

> 5. no alter table add constraint
> 6. select count(distinct *) from a view gives a parser error on distinct
> -- distinct keyword not supported here?
> 7. one query (dss_select_05) has an avg on a numeric field. I got an
> overflow error (is there a cast to a longer type?). When the avg on
> numeric field is removed, the query consumes memory rapidly and doesn't
> terminate. I dropped this query.
> 8. Can't start postmaster with more than 65536 buffers as I get a "FATAL
> 1:  couldn't
> initialize shared buffer pool Hash Tbl". Variable overflow?

If you are referring to the -B option of the postmaster,
each "buffer" is 8K in size. So, for example -B 256 would be
2 megs of buffers. How much RAM was on the test machine? -B
65536 is a 1/2 gig...

> 
> Thanks for the tuning suggestions I received from various people.
> 
> Also, is PostgreSQL 7 expected to be SQL-92 compliant? It's pretty close
> now.
> 
> I'll be posting complete scripts and C code when the story goes to print
> on Feb. 14.
> 
> Regards,
> Tim Dyck
> Senior Analyst
> PC Week Labs
> 
> ************

> Hi everybody, I'm done my tests of PostgreSQL and Interbase.
> I concentrated on two tests, an OLTP Single Read Test, where we read a
> single row out of a 200K row indexed table, and the OLTP Read Mix Test,
> which is a mix of about 30 queries, about half single table selects and
> the other half joins of various complexity (up to four way). For both of
> these tests, InterBase was about 2x to 2.5x as fast as PostgreSQL. In
> multiuser tests (up to 100 users), the situation was reversed, with
> PostgreSQL close to 3 times faster at peak throughput (which was at 50
> concurrent users). The reason why is that InterBase on Linux has a
> process-per-connection architecture without a shared cache. As such, I had
> to really limit cache sizes to allow 100 users to connect, and that really
> hurt InterBase's performance.

So, we scale better. Nice.

> I ran both PostgreSQL and InterBase with syncs turned off, and used a
> cache of 65536 4KB pages and 4000K of sort buffer.
> Here's a list of things about PostgreSQL I had problems with:
> 
> 1. "Null" is not accepted keyword on "create table" ("not null" is ok)

"NULL" is *not* SQL92 standard (well, at least it wasn't in the draft
standard available on DEC's web site) presumably since including it
generally leads to parsing problems with a one-token-lookahead parser
such as yacc.

Also, since it is the default behavior for column creation, it seems
to be pretty much a useless noise word in this context.

But it will be in the next release for the typical case; I implemented
it a month or so ago but have some other developments I've been
working on and haven't yet committed this one to the source tree. Will
likely be there this weekend.

> 2. copy command 'with null as' option not functional

This was added 1999/12/14 to the development tree. Will be in the next
release.

> 3. try to create an index on a numeric and "no operator class for
> 'numeric' data type" error message results. Numerics not indexable?

Not yet. Should be there for the next release (1-2 months).

> 4. no outer join -- I had to drop one query because of this
> 5. no alter table add constraint
> 6. select count(distinct *) from a view gives a parser error on distinct
> -- distinct keyword not supported here?

These are all high on the ToDo list, but I'm not sure they will be in
the next release.

> 7. one query (dss_select_05) has an avg on a numeric field. I got an
> overflow error (is there a cast to a longer type?). When the avg on
> numeric field is removed, the query consumes memory rapidly and doesn't
> terminate. I dropped this query.
> 8. Can't start postmaster with more than 65536 buffers as I get a "FATAL
> 1:  couldn't
> initialize shared buffer pool Hash Tbl". Variable overflow?

Just guessing, but this is more likely a system resource problem. That
is a lot of buffers!

> Also, is PostgreSQL 7 expected to be SQL-92 compliant? It's pretty close
> now.

What feature do you feel is lacking for Postgres to be SQL92
compliant? As you know, SQL92 defines three levels of compliance, and
although virtually all databases claim compliance it is almost always
to the lowest, most basic level.

Things like outer joins are not required for the basic compliance,
which is how, for example, Oracle gets to claim compliance without
supporting SQL92 outer join syntax.

> I'll be posting complete scripts and C code when the story goes to print
> on Feb. 14.

Great.

Regards.
                  - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Timothy Dyck <Timothy_Dyck@zd.com> writes:
> Here's a list of things about PostgreSQL I had problems with:

> 1. "Null" is not accepted keyword on "create table" ("not null" is ok)

AFAICT from the SQL92 spec, NULL is not a legal column constraint.
I know some DBMSs accept it anyway, but we don't because it creates
grammatical ambiguities.

> 2. copy command 'with null as' option not functional

It looks like this has been added for 7.0 ... I haven't tried it
but I see the syntax is there.

> 3. try to create an index on a numeric and "no operator class for
> 'numeric' data type" error message results. Numerics not indexable?

Oversight in 6.5.* ... fixed for 7.0.

> 4. no outer join -- I had to drop one query because of this

Thomas is working on outer joins, but I'm not sure if it'll be ready
for 7.0.  7.1 for sure though; this is our most-requested missing SQL92
feature.

> 5. no alter table add constraint

Not there yet (but Peter E. was working on it when last seen...)

> 6. select count(distinct *) from a view gives a parser error on distinct
> -- distinct keyword not supported here?

No, but it is for 7.0.

> 7. one query (dss_select_05) has an avg on a numeric field. I got an
> overflow error (is there a cast to a longer type?). When the avg on
> numeric field is removed, the query consumes memory rapidly and doesn't
> terminate. I dropped this query.

Bug.  I posted a patch for this and a couple of other NUMERIC problems
a few weeks ago; it'll be in 7.0 of course, and you can get the patch
off the pgsql-patches list archives if you need it to work in 6.5.*.

> 8. Can't start postmaster with more than 65536 buffers as I get a "FATAL
> 1:  couldn't initialize shared buffer pool Hash Tbl". Variable overflow?

Probably.  Hadn't occurred to me that we need to check for a sane upper
bound on the number of buffers, but I guess we do.  (You do realize that
would be half a gig of in-memory buffers, right?  If you've actually got
that much RAM, it's probably better to let the OS use it for general-
purpose disk buffers instead of dedicating it all to Postgres.)

> Also, is PostgreSQL 7 expected to be SQL-92 compliant? It's pretty close
> now.

We're getting closer all the time, but I wouldn't want to promise that
we'll ever have everything that's in SQL92.

Thanks for the report!  I don't suppose you'd be interested in rerunning
your tests on current (pre-beta-7.0) sources?
        regards, tom lane

Mike Mascari wrote:
> 
> Timothy Dyck wrote:
> >
> > Hi everybody, I'm done my tests of PostgreSQL and Interbase.
> >
> > I concentrated on two tests, an OLTP Single Read Test, where we read a
> > single row out of a 200K row indexed table, and the OLTP Read Mix Test,
> > which is a mix of about 30 queries, about half single table selects and
> > the other half joins of various complexity (up to four way). For both of
> > these tests, InterBase was about 2x to 2.5x as fast as PostgreSQL. In
> > multiuser tests (up to 100 users), the situation was reversed, with
> > PostgreSQL close to 3 times faster at peak throughput (which was at 50
> > concurrent users). The reason why is that InterBase on Linux has a
> > process-per-connection architecture without a shared cache. As such, I had
> > to really limit cache sizes to allow 100 users to connect, and that really
> > hurt InterBase's performance.
> >
> > I ran both PostgreSQL and InterBase with syncs turned off, and used a
> > cache of 65536 4KB pages and 4000K of sort buffer.

> If you are referring to the -B option of the postmaster,
> each "buffer" is 8K in size. So, for example -B 256 would be
> 2 megs of buffers. How much RAM was on the test machine? -B
> 65536 is a 1/2 gig...

I should have read your post more carefully. You say you
used 65536 4KB pages, so I assume you built PostgreSQL with
a BLCKSZ of 4 instead of 8, running with 256M of in-memory
buffers...

Mike Mascari

At 04:38 AM 2/4/00 -0500, Mike Mascari wrote:

>That's always been annoying, although it can be simulated
>easily with:
>
>SELECT t1.x, t2.y 
>FROM t1, t2
>WHERE t1.x = t2.x
>UNION
>SELECT t1.x, NULL
>FROM t1 WHERE NOT EXISTS ( SELECT t2.x FROM t2 WHERE t1.x =
>t2.x );

SOME - but not all - outer joins can be simulated with this
trick.  Others require subselects in the target list, etc.

And the union form gets really messy when a query includes more
than one outer join.




- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

I was disappointed this benchmark did not include database recovery
and reliability measurements.  Benchmarks ought to include the most
important characteristics of an RDBMS, and recovery/reliability is
certainly one of them.  People tend to try to "measure up" against
accepted benchmarks; as one currently suffering from apparent
reliability issues, the thought of decreased focus on reliability irks
me. 

Cheers,
Ed Loehr


Timothy Dyck wrote:
> 
> Hi everybody, I'm done my tests of PostgreSQL and Interbase.

I wrote:
> Timothy Dyck <Timothy_Dyck@zd.com> writes:
>> 8. Can't start postmaster with more than 65536 buffers as I get a "FATAL
>> 1:  couldn't initialize shared buffer pool Hash Tbl". Variable overflow?

> Probably.  Hadn't occurred to me that we need to check for a sane upper
> bound on the number of buffers, but I guess we do.  (You do realize that
> would be half a gig of in-memory buffers, right?  If you've actually got
> that much RAM, it's probably better to let the OS use it for general-
> purpose disk buffers instead of dedicating it all to Postgres.)

Just FYI, this is now fixed for 7.0.  Turns out there was a bogus
hard-wired assumption about the maximum size of the hashtable for
shared buffers.

I still doubt that anyone really *needs* more than 64K buffers ;-)
... but it will work if you have the RAM.
        regards, tom lane