State of Beta 2

Beta2 is running archives.postgresql.org right now ... >4gig worth of
data, and seems to be performing pretty good, no crashes that I've been
made aware of ...

On Tue, 9 Sep 2003, Andrew Rawnsley wrote:

"AR" == Andrew Rawnsley <ronz@ravensfield.com> writes:

AR> Anyone out there using beta 2 in production situations? Comments on
AR> stability? I am rolling out a project in the next 4 weeks, and really
AR> don't want to go though an upgrade soon after its released on an
AR> Unsuspecting Client, so I would LIKE to start working with 7.4.

I'm pondering doing the same, but I'm not 100% sure there won't be any
dump/restore-required changes to it before it goes gold. From my
tuning tests I've been running on it, it appears to be extremely fast
and stable.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

On Wed, 10 Sep 2003, Vivek Khera wrote:

"AR" == Andrew Rawnsley <ronz@ravensfield.com> writes:

AR> Anyone out there using beta 2 in production situations? Comments on
AR> stability? I am rolling out a project in the next 4 weeks, and really
AR> don't want to go though an upgrade soon after its released on an
AR> Unsuspecting Client, so I would LIKE to start working with 7.4.

I'm pondering doing the same, but I'm not 100% sure there won't be any
dump/restore-required changes to it before it goes gold. From my
tuning tests I've been running on it, it appears to be extremely fast
and stable.

Yeah, right now it's looking like the only thing you'll have to do is
reindex hash indexes between beta2 and beta3.

On Wed, 10 Sep 2003, Vivek Khera wrote:

"AR" == Andrew Rawnsley <ronz@ravensfield.com> writes:

AR> Anyone out there using beta 2 in production situations?

I'm pondering doing the same, but I'm not 100% sure there won't be any
dump/restore-required changes to it before it goes gold.

As you shouldn't be ...

There's some major-league whining going on right now in the jdbc list
about the fact that "int8col = 42" isn't indexable. While we know that
solving this problem in the general case is hard, it occurred to me this
afternoon that fixing it just for int8 might not be so hard --- maybe
just taking out the int8-vs-int4 comparison operators would improve
matters. I might be willing to advocate another initdb to do that,
if it seems to help that situation without introducing other issues.
It's not well tested as yet, but stay tuned ...

regards, tom lane

"sm" == scott marlowe <scott.marlowe> writes:

I'm pondering doing the same, but I'm not 100% sure there won't be any
dump/restore-required changes to it before it goes gold. From my
tuning tests I've been running on it, it appears to be extremely fast
and stable.

sm> Yeah, right now it's looking like the only thing you'll have to do is
sm> reindex hash indexes between beta2 and beta3.

Sean had grumbled something about making pagesize 16k on FreeBSD for
7.4 but it seems unlikely. I'll just locally patch it since it does
seem to offer some improvement.

On Thu, 11 Sep 2003, Vivek Khera wrote:

"sm" == scott marlowe <scott.marlowe> writes:

I'm pondering doing the same, but I'm not 100% sure there won't be any
dump/restore-required changes to it before it goes gold. From my
tuning tests I've been running on it, it appears to be extremely fast
and stable.

sm> Yeah, right now it's looking like the only thing you'll have to do is
sm> reindex hash indexes between beta2 and beta3.

Sean had grumbled something about making pagesize 16k on FreeBSD for
7.4 but it seems unlikely. I'll just locally patch it since it does
seem to offer some improvement.

Without a fair amount of testing, especially on other platforms, it most
likely won't happen in the distribution itself ... one of the things that
was bantered around for after v7.4 is released is seeing how increasing it
on the various platforms fairs, and possibly just raising the default to
16k or 32k (Tatsuo mentioned a 15% improvement at 32k) ...

But, we'll need broader testing before that happens ...

"MGF" == Marc G Fournier <scrappy@postgresql.org> writes:

MGF> Without a fair amount of testing, especially on other platforms, it most
MGF> likely won't happen in the distribution itself ... one of the things that
MGF> was bantered around for after v7.4 is released is seeing how increasing it
MGF> on the various platforms fairs, and possibly just raising the default to
MGF> 16k or 32k (Tatsuo mentioned a 15% improvement at 32k) ...

MGF> But, we'll need broader testing before that happens ...

Well... if we had a good load generator (many threads; many small,
medium, large transactions; many inserts; many reads) I'd run it to
death on my idle server until 7.4 is released, at which point that
server won't be idle anymore.

I tried building one of the OSDL DB benchmark, but after installing
the dependencies which are only announced by the failure of configure
to run, it errored out with a C syntax error... at that point I gave
up.

I'm pondering doing the same, but I'm not 100% sure there won't
be any dump/restore-required changes to it before it goes gold.
From my tuning tests I've been running on it, it appears to be
extremely fast and stable.

sm> Yeah, right now it's looking like the only thing you'll have to do is
sm> reindex hash indexes between beta2 and beta3.

Sean had grumbled something about making pagesize 16k on FreeBSD
for 7.4 but it seems unlikely. I'll just locally patch it since
it does seem to offer some improvement.

Without a fair amount of testing, especially on other platforms, it
most likely won't happen in the distribution itself ... one of the
things that was bantered around for after v7.4 is released is seeing
how increasing it on the various platforms fairs, and possibly just
raising the default to 16k or 32k (Tatsuo mentioned a 15%
improvement at 32k) ...

But, we'll need broader testing before that happens ...

I haven't had a chance to sit down and do any exhaustive testing yet
and don't think I will for a while. That said, once 7.4 goes gold,
I'm going to provide databases/postgresql-devel with a tunable that
will allow people to choose what block size they would like (4k, 8K,
16K, 32K, or 64K) when they build the port. Hopefully people will
chime in with their results at that time. With things so close to 7.4
and Tom worried about digging up possible bugs, I'm not about to
destabilize 7.4 for FreeBSD users.

I'm personally under the gut feeling that 8K or 4K block sizes will be
a win for some loads, but bigger block sizes will result in more
efficient over all operations in cases where IO is more expensive than
CPU (which changes with hardware and workload).

In the future table spaces implementation, I think it would be a HUGE
win for DBAs if the block size could be specified on a per table
basis. I know that won't be an easy change, but I do think it would
be beneficial for different work loads and filesystems.

-sc

--
Sean Chittenden

I haven't had a chance to sit down and do any exhaustive testing yet and
don't think I will for a while. That said, once 7.4 goes gold, I'm
going to provide databases/postgresql-devel with a tunable that will
allow people to choose what block size they would like (4k, 8K, 16K,
32K, or 64K) when they build the port.

If you do this, you *have* to put in a very very big warning that
databases created with non-PostgreSQL-standard block sizes may not be
transferrable to a standard-PostgreSQL install ... that is Tom's major
problem, is cross-platform/system dump/restores may no work is the
database schema was designed with a 16k block size in mind ...

I haven't had a chance to sit down and do any exhaustive testing
yet and don't think I will for a while. That said, once 7.4 goes
gold, I'm going to provide databases/postgresql-devel with a
tunable that will allow people to choose what block size they
would like (4k, 8K, 16K, 32K, or 64K) when they build the port.

If you do this, you *have* to put in a very very big warning that
databases created with non-PostgreSQL-standard block sizes may not
be transferrable to a standard-PostgreSQL install ... that is Tom's
major problem, is cross-platform/system dump/restores may no work is
the database schema was designed with a 16k block size in mind ...

Agreed, but if anyone has a table with close to 1600 columns in a
table... is either nuts or knows what they're doing. If someone has

1600 columns, that is an issue, but isn't one that I think can be

easily fended off without the backend being able to adapt on the fly
to different block sizes, which seems like something that could be
done with a rewrite of some of this code when table spaces are
introduced.

-sc

--
Sean Chittenden

On Thu, 11 Sep 2003 00:25:53 -0400, Tom Lane <tgl@sss.pgh.pa.us>
wrote:

"int8col = 42" isn't indexable. [...] --- maybe
just taking out the int8-vs-int4 comparison operators would improve
matters. I might be willing to advocate another initdb to do that,

You mean

DELETE FROM pg_operator WHERE oid in (15, 36, 416, 417);

and possibly some more oids? Does this really require an initdb? If
we were willing to tell people who roll a 7.4Beta2 database cluster
into 7.4Beta3 (or 7.4 production) to execute this query once per
database, we could get away without increasing CATALOG_VERSION_NO.

Servus
Manfred

On Thu, 11 Sep 2003 14:24:25 -0700, Sean Chittenden
<sean@chittenden.org> wrote:

Agreed, but if anyone has a table with close to 1600 columns in a
table...

This 1600 column limit has nothing to do with block size. It is
caused by the fact that a heap tuple header cannot be larger than 255
bytes, so there is a limited number of bits in the null bitmap.

Servus
Manfred

Manfred Koizar wrote:

On Thu, 11 Sep 2003 14:24:25 -0700, Sean Chittenden
<sean@chittenden.org> wrote:

Agreed, but if anyone has a table with close to 1600 columns in a
table...

This 1600 column limit has nothing to do with block size. It is
caused by the fact that a heap tuple header cannot be larger than 255
bytes, so there is a limited number of bits in the null bitmap.

Are you sure. Then our max would be:

255 * 8 = 2040

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Manfred Koizar <mkoi-pg@aon.at> writes:

On Thu, 11 Sep 2003 00:25:53 -0400, Tom Lane <tgl@sss.pgh.pa.us>
wrote:

"int8col = 42" isn't indexable. [...] --- maybe
just taking out the int8-vs-int4 comparison operators would improve
matters. I might be willing to advocate another initdb to do that,

You mean
DELETE FROM pg_operator WHERE oid in (15, 36, 416, 417);
and possibly some more oids? Does this really require an initdb?

I think so. Consider for instance stored views that contain references
to those operators. In any case, I don't really want to have to ask
people who complain about 7.4 performance problems whether they've done
the above.

regards, tom lane

On Fri, 12 Sep 2003 10:22:32 -0400 (EDT), Bruce Momjian <pgman@candle.pha.pa.us>
wrote:

This 1600 column limit has nothing to do with block size. It is
caused by the fact that a heap tuple header cannot be larger than 255
bytes, so there is a limited number of bits in the null bitmap.

Are you sure.

No, never! ;-)

Sollte einer auch einst die vollkommenste Wahrheit verk�nden,
Wissen k�nnt' er das nicht: Es ist alles durchwebt von Vermutung.

For even if by chance he were to utter the final truth,
He would himself not know it: For it is but a woven web of guesses.
-- Xenophanes, translation by K. R. Popper

But in this case I have htup.h on my side:

/*
* MaxTupleAttributeNumber limits the number of (user) columns in a tuple.
* The key limit on this value is that the size of the fixed overhead for
* a tuple, plus the size of the null-values bitmap (at 1 bit per column),
* plus MAXALIGN alignment, must fit into t_hoff which is uint8. On most
* machines the upper limit without making t_hoff wider would be a little
* over 1700. We use round numbers here and for MaxHeapAttributeNumber
* so that alterations in HeapTupleHeaderData layout won't change the
* supported max number of columns.
*/
#define MaxTupleAttributeNumber 1664 /* 8 * 208 */

/*----------
* MaxHeapAttributeNumber limits the number of (user) columns in a table.
* This should be somewhat less than MaxTupleAttributeNumber. It must be
* at least one less, else we will fail to do UPDATEs on a maximal-width
* table (because UPDATE has to form working tuples that include CTID).
* In practice we want some additional daylight so that we can gracefully
* support operations that add hidden "resjunk" columns, for example
* SELECT * FROM wide_table ORDER BY foo, bar, baz.
* In any case, depending on column data types you will likely be running
* into the disk-block-based limit on overall tuple size if you have more
* than a thousand or so columns. TOAST won't help.
*----------
*/
#define MaxHeapAttributeNumber 1600 /* 8 * 200 */

Servus
Manfred

Manfred Koizar wrote:

On Fri, 12 Sep 2003 10:22:32 -0400 (EDT), Bruce Momjian <pgman@candle.pha.pa.us>
wrote:

This 1600 column limit has nothing to do with block size. It is
caused by the fact that a heap tuple header cannot be larger than 255
bytes, so there is a limited number of bits in the null bitmap.

Are you sure.

No, never! ;-)

Sollte einer auch einst die vollkommenste Wahrheit verk?nden,
Wissen k?nnt' er das nicht: Es ist alles durchwebt von Vermutung.

For even if by chance he were to utter the final truth,
He would himself not know it: For it is but a woven web of guesses.
-- Xenophanes, translation by K. R. Popper

But in this case I have htup.h on my side:

/*
* MaxTupleAttributeNumber limits the number of (user) columns in a tuple.
* The key limit on this value is that the size of the fixed overhead for
* a tuple, plus the size of the null-values bitmap (at 1 bit per column),
* plus MAXALIGN alignment, must fit into t_hoff which is uint8. On most
* machines the upper limit without making t_hoff wider would be a little
* over 1700. We use round numbers here and for MaxHeapAttributeNumber
* so that alterations in HeapTupleHeaderData layout won't change the
* supported max number of columns.
*/
#define MaxTupleAttributeNumber 1664 /* 8 * 208 */

/*----------
* MaxHeapAttributeNumber limits the number of (user) columns in a table.
* This should be somewhat less than MaxTupleAttributeNumber. It must be
* at least one less, else we will fail to do UPDATEs on a maximal-width
* table (because UPDATE has to form working tuples that include CTID).
* In practice we want some additional daylight so that we can gracefully
* support operations that add hidden "resjunk" columns, for example
* SELECT * FROM wide_table ORDER BY foo, bar, baz.
* In any case, depending on column data types you will likely be running
* into the disk-block-based limit on overall tuple size if you have more
* than a thousand or so columns. TOAST won't help.
*----------
*/
#define MaxHeapAttributeNumber 1600 /* 8 * 200 */

Oh, interesting. I thought it was based on the maximum number of
columns we could pack into a block. I realize that our limit could be
much less than 1600 if you pick wide columns like TEXT.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Manfred Koizar <mkoi-pg@aon.at> writes:

On Thu, 11 Sep 2003 14:24:25 -0700, Sean Chittenden
<sean@chittenden.org> wrote:

Agreed, but if anyone has a table with close to 1600 columns in a
table...

This 1600 column limit has nothing to do with block size. It is
caused by the fact that a heap tuple header cannot be larger than 255
bytes, so there is a limited number of bits in the null bitmap.

Right, but that's not the only limit on number of columns. A tuple has
to be able to fit into a page. If all your columns are toastable types,
and you toast every one of them, then the toast pointers are 20 bytes
each, so with 8K block size the maximum usable number of columns is
somewhere around 400. If the columns were all int8 or float8 the limit
would be about 1000 columns; etc. But raise the page size, and these
limits increase, possibly allowing the 1600 number to become the actual
limiting factor.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Manfred Koizar wrote:

This 1600 column limit has nothing to do with block size. It is
caused by the fact that a heap tuple header cannot be larger than 255
bytes, so there is a limited number of bits in the null bitmap.

Are you sure. Then our max would be:
255 * 8 = 2040

I assure you, Manfred knows heap tuple headers inside and out ;-)
See the comments at the top of src/include/access/htup.h.

regards, tom lane

Small soapbox moment here...

ANYTHING that can be done to eliminate having to do an initdb on
version changes would make a lot of people do cartwheels. 'Do a
dump/reload' sometimes comes across a bit casually on the lists (my
apologies if it isn't meant to be), but it can be be incredibly onerous
to do on a large production system. That's probably why you run across
people running stupid-old versions.

I am, of course, speaking from near-complete ignorance about what it
takes to avoid the whole problem.

On Friday, September 12, 2003, at 10:37 AM, Tom Lane wrote:

Manfred Koizar <mkoi-pg@aon.at> writes:

On Thu, 11 Sep 2003 00:25:53 -0400, Tom Lane <tgl@sss.pgh.pa.us>
wrote:

"int8col = 42" isn't indexable. [...] --- maybe
just taking out the int8-vs-int4 comparison operators would improve
matters. I might be willing to advocate another initdb to do that,

You mean
DELETE FROM pg_operator WHERE oid in (15, 36, 416, 417);
and possibly some more oids? Does this really require an initdb?

I think so. Consider for instance stored views that contain references
to those operators. In any case, I don't really want to have to ask
people who complain about 7.4 performance problems whether they've done
the above.

regards, tom lane

---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com