Re: [HACKERS] PC Week Labs benchmark results
От | Mike Mascari |
---|---|
Тема | Re: [HACKERS] PC Week Labs benchmark results |
Дата | |
Msg-id | 389A9DFB.BFAC279B@mascari.com обсуждение исходный текст |
Ответ на | PC Week Labs benchmark results (Timothy Dyck <Timothy_Dyck@zd.com>) |
Ответы |
Re: [HACKERS] PC Week Labs benchmark results
(Don Baccus <dhogaza@pacifier.com>)
|
Список | pgsql-hackers |
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 > > ************
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Karl DeBisschopДата:
Сообщение: Re: [HACKERS] how to deal with sparse/to-be populated tables