Re: [QUESTIONS] Business cases

On Sat, 17 Jan 1998, The Hermit Hacker wrote:

On Sat, 17 Jan 1998, Tom wrote:

How are large users handling the vacuum problem? vaccuum locks other
users out of tables too long. I don't need a lot performance (a few per
minutes), but I need to be handle queries non-stop).

Not sure, but this one is about the only major thing that is continuing
to bother me :( Is there any method of improving this?

vacuum seems to do a _lot_ of stuff. It seems that crash recovery
features, and maintenance features should be separated. I believe the
only required maintenance features are recovering space used by deleted
tuples and updating stats? Both of these shouldn't need to lock the
database for long periods of time.

Also, how are people handling tables with lots of rows? The 8k tuple
size can waste a lot of space. I need to be able to handle a 2 million
row table, which will eat up 16GB, plus more for indexes.

This oen is improved upon in v6.3, where at compile time you can stipulate
the tuple size. We are looking into making this an 'initdb' option instead,
so that you can have the same binary for multiple "servers", but any database
created under a particular server will be constrained by that tuple size.

That might help a bit, but same tables may have big rows and some not.
For example, my 2 million row table requires only requires two date
fields, and 7 integer fields. That isn't very much data. However, I'd
like to be able to join against another table with much larger rows.

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

Tom

Also, how are people handling tables with lots of rows? The 8k tuple
size can waste a lot of space. I need to be able to handle a 2 million
row table, which will eat up 16GB, plus more for indexes.

16GB?!? Not unless your tuples are 8k. The 8k is/was the max _tuple_ size,
but more than one tuple can be stored per block. :)

Try the formula in the FAQ to get a reasonable estimate for the table's size.

This oen is improved upon in v6.3, where at compile time you can stipulate
the tuple size. We are looking into making this an 'initdb' option instead,
so that you can have the same binary for multiple "servers", but any database
created under a particular server will be constrained by that tuple size.

If the patch I sent to PATCHES is applied, then it will be a compile-time setting
and you'll need a postmaster for each database w/differing block sizes. Not the
greatest solution, but it would work.

I almost have the "-k" option working today. Two files left to do...

backend/access/nbtree/nbtsort.c
backend/utils/adt/chunk.c

I'm being careful about pfree'ing all the stuff that I'm going to have to palloc.

Tiggers definitely do _not_ like memory leaks.

darrenk

Also, how are people handling tables with lots of rows? The 8k tuple
size can waste a lot of space. I need to be able to handle a 2 million
row table, which will eat up 16GB, plus more for indexes.

This oen is improved upon in v6.3, where at compile time you can stipulate
the tuple size. We are looking into making this an 'initdb' option instead,
so that you can have the same binary for multiple "servers", but any database
created under a particular server will be constrained by that tuple size.

That might help a bit, but same tables may have big rows and some not.
For example, my 2 million row table requires only requires two date
fields, and 7 integer fields. That isn't very much data. However, I'd
like to be able to join against another table with much larger rows.

Two dates and 7 integers would make tuple of 90-some bytes, call it 100 max.
So you would prolly get 80 tuples per 8k page, so 25000 pages would use a
file of 200 meg.

The block size parameter will be database-specific, not table-specific, and
since you can't join tables from different _databases_, 2nd issue is moot.

If I could get around to the tablespace concept again, then maybe a different
block size per tablespace would be useful. But, that is putting the cart
a couple of light-years ahead of the proverbial horse...

Darren aka darrenk@insightdist.com

On Sat, 17 Jan 1998, Darren King wrote:

16GB?!? Not unless your tuples are 8k. The 8k is/was the max _tuple_ size,
but more than one tuple can be stored per block. :)

Try the formula in the FAQ to get a reasonable estimate for the table's size.

The sentence "Tuples do not cross 8k boundaries so a 5k tuple will
require 8k of storage" in 3.8 of the FAQ confused me. I did not realize
that multiple tuples could be stored in a page. So I took it to mean
that one tuple was stored in page. I didn't even even see 3.26, because I
thought that 3.8 answered my question :(

Tom

Also, how are people handling tables with lots of rows? The 8k tuple
size can waste a lot of space. I need to be able to handle a 2 million
row table, which will eat up 16GB, plus more for indexes.

16GB?!? Not unless your tuples are 8k. The 8k is/was the max _tuple_ size,
but more than one tuple can be stored per block. :)

Try the formula in the FAQ to get a reasonable estimate for the table's size.

The FAQ copy on the web page has it. The FAQ in the 6.2.1 distribution
does not.

--
Bruce Momjian
maillist@candle.pha.pa.us

Tom wrote:

How are large users handling the vacuum problem? vaccuum locks other
users out of tables too long. I don't need a lot performance (a few per
minutes), but I need to be handle queries non-stop).

Not sure, but this one is about the only major thing that is continuing
to bother me :( Is there any method of improving this?

vacuum seems to do a _lot_ of stuff. It seems that crash recovery
features, and maintenance features should be separated. I believe the
only required maintenance features are recovering space used by deleted
tuples and updating stats? Both of these shouldn't need to lock the
database for long periods of time.

Would it be possible to add an option to VACUUM, like a max number
of blocks to sweep? Or is this impossible because of the way PG works?

Would it be possible to (for example) compact data from the front of
the file to make one block free somewhere near the beginning of the
file and then move rows from the last block to this new, empty block?

-- To limit the number of rows to compact:
psql=> VACUUM MoveMax 1000; -- move max 1000 rows

-- To limit the time used for vacuuming:
psql=> VACUUM MaxSweep 1000; -- Sweep max 1000 blocks

Could this work with the current method of updating statistics?

*** Btw, why doesn't PG update statistics when inserting/updating?

/* m */

Also, how are people handling tables with lots of rows? The 8k tuple
size can waste a lot of space. I need to be able to handle a 2 million
row table, which will eat up 16GB, plus more for indexes.

This oen is improved upon in v6.3, where at compile time you can stipulate
the tuple size. We are looking into making this an 'initdb' option instead,
so that you can have the same binary for multiple "servers", but any database
created under a particular server will be constrained by that tuple size.

Tom's "problem" is probably not a bad as he thinks. The 8k tuple size limit is a
result of the current 8k page size limit, but multiple tuples are allowed on a page.
They are just not allowed to span pages. So, there is some wasted space (as is true
for many, most, or all commercial dbs also) but it is on average only the size of
half a tuple, and can be easily predicted once the tuple sizes are known.

- Tom (T2?)

Could this work with the current method of updating statistics?

*** Btw, why doesn't PG update statistics when inserting/updating?

Too slow to do at that time. You need to span all the data to get an
accurate figure.

--
Bruce Momjian
maillist@candle.pha.pa.us

Bruce Momjian wrote:

*** Btw, why doesn't PG update statistics when inserting/updating?

Too slow to do at that time. You need to span all the data to get an
accurate figure.

Is it not possible to take the current statistics and the latest
changes and calculate new statistics from that?

What information is kept in these statistics? How are they used?
Obviously this is more than just number of rows, but what exactly?

/* m */

Bruce Momjian wrote:

*** Btw, why doesn't PG update statistics when inserting/updating?

Too slow to do at that time. You need to span all the data to get an
accurate figure.

Is it not possible to take the current statistics and the latest
changes and calculate new statistics from that?

What information is kept in these statistics? How are they used?
Obviously this is more than just number of rows, but what exactly?

Look in commands/vacuum.c. It measures the spread-ness of the data, and
there is no way to get this figure on-the-fly unless you maintain
buckets for each range of data values and decrement/increment as values
are added-subtraced. Seeing a the MySQL optimizer is a single file, and
so is the executor, I doubt that is what it is doing. Probably just
keeps a count of how many rows in the table.

--
Bruce Momjian
maillist@candle.pha.pa.us

In all tests crash_me crashed backend server - postgres but postmaster
still run and allow to work with all dbs. I run crash_me several times
with postmaster started once upon boot time.

Is there any indication as to why the crash?

I can tell indirect only using limits/pg.cfg where crash_me logs all
results.
Every time it runs it checks this file to set limits in the test to avoid
crash.
I think the best way to look PGSQL source code and set up checkups
for these limits. So :

1. output:
query size: Broken pipe

pg.cfg:
query_size=4096 # query size

2. output:
constant string size in where:
Fatal error: Can't check 'constant string size in where' for limit=1
error: PQexec() -- There is no connection to the backend.

pg.cfg:
where_string_size=4062 # constant string size in where

3. output:
tables in join:
Fatal error: Can't check 'tables in join' for limit=1
error: PQexec() -- There is no connection to the backend.

pg.cfg:
join_tables=0 # tables in join

4. output:
table name length:
Fatal error: Can't check 'table name length' for limit=1
error: PQexec() -- There is no connection to the backend.

pg.cfg:
max_table_name=31 # table name length

5. output:
max table row length (without blobs):
Fatal error: Can't check 'max table row length (without blobs)' for limit=1
error: PQexec() -- There is no connection to the backend.

pg.cfg:
max_row_length=7969 # max table row length (without blobs)

When I run tests first time I crashed at "column name length: 31" also.
Now it runs OK - 8-[.

And I cann't indicate couse of the problem with "index length" - it run out
of memory.

Igor Sysoev

Moved to pgsql-hackers@postgresql.org, where it should have been moved
*ages* ago

On Wed, 21 Jan 1998, Igor Sysoev wrote:

The result you're seeing is, IMHO, *correct*.

The first row in the table, when the update is undertaken, produces a
duplicate key. So you are getting a complaint which you SHOULD receive,
unless I'm misunderstanding how this is supposed to actually work.

The "update" statement, if it is behaving as an atomic thing, effectively

"snapshots" the table and then performs the update. Since the first
attempted update is on the first row it "finds", and adding one to it
produces "3", which is already on file, I believe it should bitch -
and it does.

I'm not SQL guru and cannot tell how it must be.
But it seems that Oracle and Solid allows update primary keys such way.

Connected to:
Oracle7 Server Release 7.3.3.0.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.3.3.0.0 - Production

SQL> create table one ( a integer primary key not null );

Table created.

SQL> insert into one values (2);

1 row created.

SQL> insert into one values (3);

1 row created.

SQL> insert into one values (1);

1 row created.

SQL> select * from one;

A
----------
2
3
1

SQL> update one set a=a+1;

3 rows updated.

SQL> select * from one;

A
----------
3
4
2

SQL>

Moved to pgsql-hackers@postgresql.org, where it should have been moved
*ages* ago

Connected to:
Oracle7 Server Release 7.3.3.0.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.3.3.0.0 - Production

SQL> create table one ( a integer primary key not null );

Table created.

SQL> insert into one values (2);

1 row created.

SQL> insert into one values (3);

1 row created.

SQL> insert into one values (1);

1 row created.

SQL> select * from one;

A
----------
2
3
1

SQL> update one set a=a+1;

3 rows updated.

SQL> select * from one;

A
----------
3
4
2

Man, how do you implement that behavior? No wonder MySQL fails on it
too.

--
Bruce Momjian
maillist@candle.pha.pa.us

On Wed, 21 Jan 1998, Bruce Momjian wrote:

Moved to pgsql-hackers@postgresql.org, where it should have been moved
*ages* ago

Connected to:
Oracle7 Server Release 7.3.3.0.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.3.3.0.0 - Production

SQL> create table one ( a integer primary key not null );

Table created.

SQL> insert into one values (2);

1 row created.

SQL> insert into one values (3);

1 row created.

SQL> insert into one values (1);

1 row created.

SQL> select * from one;

A
----------
2
3
1

SQL> update one set a=a+1;

3 rows updated.

SQL> select * from one;

A
----------
3
4
2

Man, how do you implement that behavior? No wonder MySQL fails on it
too.

I don't know...the one suggestion that was made seemed to make
about the most sense...

If update is atomic, then it should allow you to change all the
resultant fields and then try to commit it. After all the fields are
changed, then it becomes 3,4,2 instead of 2,3,1, and, therefore, is all
unique...

The Hermit Hacker wrote:

Moved to pgsql-hackers@postgresql.org, where it should have been moved
*ages* ago

On Wed, 21 Jan 1998, Igor Sysoev wrote:

The result you're seeing is, IMHO, *correct*.

The first row in the table, when the update is undertaken, produces a
duplicate key. So you are getting a complaint which you SHOULD receive,
unless I'm misunderstanding how this is supposed to actually work.

The "update" statement, if it is behaving as an atomic thing, effectively

"snapshots" the table and then performs the update. Since the first
attempted update is on the first row it "finds", and adding one to it
produces "3", which is already on file, I believe it should bitch -
and it does.

I'm not SQL guru and cannot tell how it must be.
But it seems that Oracle and Solid allows update primary keys such way.

Connected to:
Oracle7 Server Release 7.3.3.0.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.3.3.0.0 - Production

SQL> create table one ( a integer primary key not null );

Table created.

SQL> insert into one values (2);

1 row created.

SQL> insert into one values (3);

1 row created.

SQL> insert into one values (1);

1 row created.

SQL> select * from one;

A
----------
2
3
1

SQL> update one set a=a+1;

3 rows updated.

SQL> select * from one;

A
----------
3
4
2

SQL>

I have been "lurking" on the pgsql-hackers list for a couple of days,
but thought I'd help where I can. I tried your above example on
Sybase, and got the same results. The only difference was that the
items were always returned from the table "one" in sorted order rather
than in insertion order.

I also tried a slight modification to your query:

update one set a=a+1 where a<3;

This produces an error as would be expected:

Attempt to insert duplicate key row in object 'one' with unique index
'one_a_8473420831'
Command has been aborted.
(0 rows affected)

Ocie Mitchell