[GENERAL] Table lock while adding a column and clients are logged in

[Sven Haag]

hello pg fans,

we have an application that communicates via ODBC directly to the postgres 
database.

if i'm trying to add an additional column to a table in pgadmin while clients 
are logged in, pgadmin hangs. only if all cients are logged out it returns to 
the normal state. according to our consultant of the application this behavior 
doesn't appear in oracle or sql-server.

how can i avoid this?

cheers sven
-- 
Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir
belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table lock while adding a column and clients are logged in

[Guillaume Lelarge]

Le 02/04/2011 11:09, Sven Haag a écrit :
> hello pg fans,
> 
> we have an application that communicates via ODBC directly to the postgres 
> database.
> 
> if i'm trying to add an additional column to a table in pgadmin while clients 
> are logged in, pgadmin hangs. only if all cients are logged out it returns to 
> the normal state. according to our consultant of the application this 
> behavior doesn't appear in oracle or sql-server.
> 
> how can i avoid this?
> 

When you add a new column to a table, the session needs an
AccessExclusiveLock to this table, which means no one can have a lock on
the object while you add the column. IOW, pgAdmin (and any other tool)
will hang until no one works on the table. If it's a heavily used table,
there's not much you can do about it, but wait.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table lock while adding a column and clients are logged in

[Sven Haag]

yes i saw that in the documentation too. i guess it doesn't matter what kind of 
lock level the odbc driver is using (row-lock)?

but how can it be, that in oracle and sqlserver this is not happening?



 Original-Nachricht 
> Datum: Sat, 02 Apr 2011 11:38:29 +0200
> Von: Guillaume Lelarge 
> An: Sven Haag 
> CC: pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] Table lock while adding a column and clients are 
> logged in

> Le 02/04/2011 11:09, Sven Haag a écrit :
> > hello pg fans,
> > 
> > we have an application that communicates via ODBC directly to the
> postgres database.
> > 
> > if i'm trying to add an additional column to a table in pgadmin while
> clients are logged in, pgadmin hangs. only if all cients are logged out it
> returns to the normal state. according to our consultant of the application
> this behavior doesn't appear in oracle or sql-server.
> > 
> > how can i avoid this?
> > 
> 
> When you add a new column to a table, the session needs an
> AccessExclusiveLock to this table, which means no one can have a lock on
> the object while you add the column. IOW, pgAdmin (and any other tool)
> will hang until no one works on the table. If it's a heavily used table,
> there's not much you can do about it, but wait.
> 
> 
> -- 
> Guillaume
>  http://www.postgresql.fr
>  http://dalibo.com

-- 
NEU: FreePhone - kostenlos mobil telefonieren und surfen!   
Jetzt informieren: http://www.gmx.net/de/go/freephone

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table lock while adding a column and clients are logged in

[Thomas Kellerer]

Sven Haag wrote on 02.04.2011 12:13:

if i'm trying to add an additional column to a table in pgadmin
while

clients are logged in, pgadmin hangs. only if all cients are logged
out it returns to the normal state. according to our consultant of
the application this behavior doesn't appear in oracle or
sql-server.


how can i avoid this?



When you add a new column to a table, the session needs an
AccessExclusiveLock to this table, which means no one can have a
lock on the object while you add the column. IOW, pgAdmin (and any
other tool) will hang until no one works on the table. If it's a
heavily used table, there's not much you can do about it, but
wait.



yes i saw that in the documentation too. i guess it doesn't matter
what kind of lock level the odbc driver is using (row-lock)?

but how can it be, that in oracle and sqlserver this is not
happening?


Actually SQL Server is even more prone to these kind of locks. And it will 
happen in Oracle just as well.

PostgreSQL is more "sensible" when it comes to transactions that are not properly closed 
(Oracle is a bit more "forgiving" there - especially with SELECT statements).

I bet you see a  lot of "IDLE in transaction" entries in your pg_stat_activity (as 
opposed to plain "IDLE" entries).

This means you are not ending (e.g. committing) your transactions properly. 
Even after a plain SELECT you should issue a COMMIT (or ROLLBACK) to end the 
transaction that was implicitely started with the SELECT.

The ALTER TABLE should not be a problem if you only see "IDLE" sessions.

Regards
Thomas




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Autovacuum firing up during my manual vacuum on same table

[Henry C.]

Greets,

I just noticed something odd:  I'm busy with a manual vacuum on a table and an
autovacuum keeps firing up as well.  Thinking this looks rather weird, I
pg_cancel_backend() the autovacuum process:

current_query | vacuum analyze page_citation_text;
age   | 11:34:10.759279
...
current_query | autovacuum: VACUUM ANALYZE public.page_citation_text
age   | 11:33:15.824014


However, Pg keeps firing up the autovacuum:

current_query | vacuum analyze page_citation_text;
age   | 11:46:57.245568
...
current_query | autovacuum: VACUUM ANALYZE public.page_citation_text
age   | 00:11:50.571


It seems counter-intuitive to have two vacuum procs running on the same
table...  how is this possible?

I'm trying to vacuum the table as quickly as possible so a manual vacuum seems
to be in order as my understanding (and experience) is that the autovac is a
hell of a lot slower to mitigate impact on general performance.

Anyway, is that autovac duplicating work or locked out and waiting?

Thanks
Henry



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table lock while adding a column and clients are logged in

[Jerry Sievers]

"Sven Haag"  writes:

> hello pg fans,
>
> we have an application that communicates via ODBC directly to the
> postgres database.
>
> if i'm trying to add an additional column to a table in pgadmin while
> clients are logged in, pgadmin hangs. only if all cients are logged
> out it returns to the normal state. according to our consultant of the
> application this behavior doesn't appear in oracle or sql-server.
>
> how can i avoid this?

Discharge that clueless consultant and study up on NVCC :-)

Then, find out why there are long running transactions in your
application.

> cheers sven
> -- 
> Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir
> belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de

-- 
Jerry Sievers
e: gsiever...@comcast.net
p: 305.321.1144

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Autovacuum firing up during my manual vacuum on same table

[Henry C.]

> Anyway, is that autovac duplicating work or locked out and waiting?

Impolitely responding to my own post:  a quick strace confirms the autovac
process is indeed locked out and waiting it's turn to work.

Presumably when my manual vacuum finishes, it will then proceed and
*hopefully* not re-vacuum the table?

Regards
Henry

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table lock while adding a column and clients are logged in

[Jerry Sievers]

Jerry Sievers  writes:

> "Sven Haag"  writes:
>
>> hello pg fans,
>>
>> we have an application that communicates via ODBC directly to the
>> postgres database.
>>
>> if i'm trying to add an additional column to a table in pgadmin while
>> clients are logged in, pgadmin hangs. only if all cients are logged
>> out it returns to the normal state. according to our consultant of the
>> application this behavior doesn't appear in oracle or sql-server.
>>
>> how can i avoid this?
>
> Discharge that clueless consultant and study up on NVCC :-)

Oops!   Make that MVCC !

> Then, find out why there are long running transactions in your
> application.
>
>> cheers sven
>> -- 
>> Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir
>> belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de
>
> -- 
> Jerry Sievers
> e: gsiever...@comcast.net
> p: 305.321.1144

-- 
Jerry Sievers
e: gsiever...@comcast.net
p: 305.321.1144

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Autovacuum firing up during my manual vacuum on same table

[Jens Wilke]

On Samstag, 2. April 2011, Henry C. wrote:

> I just noticed something odd:  I'm busy with a manual vacuum on a
> table and an autovacuum keeps firing up as well. 

Usually a manual vacuum cancels a running autovacuum task.
You should find a notice about the cancelation in th logfile.

> current_query | vacuum analyze 
> age   | 11:46:57.245568

Where is the age column from?
It's not in pg_stat_activity.
Is one of the two processes waiting=t in pg_stat_activity?
Shure it's the same Table? Do you have one Table named 
page_citation_text in public and one in anoter sheme?

> I'm trying to vacuum the table as quickly as possible so a manual
> vacuum seems to be in order as my understanding (and experience)
> is that the autovac is a hell of a lot slower to mitigate impact
> on general performance.

Tune the autovacuum settings, especially the scale factors of the 
tables in question and the cost limit.
Is autovacuum_vacuum_cost_limit != -1 ?
This could be one reason, why manual vacuum is faster.

Nevertheless since at least 8.4 IMO there's no need to bother with 
manual vacuum any more.

Regards, Jens

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Autovacuum firing up during my manual vacuum on same table

[Sven Haag]

 Original-Nachricht 
> Datum: Sat, 2 Apr 2011 14:17:37 +0200
> Von: Jens Wilke 
> An: pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] Autovacuum firing up during my manual vacuum on same 
> table

> On Samstag, 2. April 2011, Henry C. wrote:
> 
> > I just noticed something odd:  I'm busy with a manual vacuum on a
> > table and an autovacuum keeps firing up as well. 
> 
> Usually a manual vacuum cancels a running autovacuum task.
> You should find a notice about the cancelation in th logfile.
> 
> > current_query | vacuum analyze 
> > age   | 11:46:57.245568
> 
> Where is the age column from?
> It's not in pg_stat_activity.
> Is one of the two processes waiting=t in pg_stat_activity?
> Shure it's the same Table? Do you have one Table named 
> page_citation_text in public and one in anoter sheme?
> 
> > I'm trying to vacuum the table as quickly as possible so a manual
> > vacuum seems to be in order as my understanding (and experience)
> > is that the autovac is a hell of a lot slower to mitigate impact
> > on general performance.
> 
> Tune the autovacuum settings, especially the scale factors of the 
> tables in question and the cost limit.
> Is autovacuum_vacuum_cost_limit != -1 ?
> This could be one reason, why manual vacuum is faster.
> 
> Nevertheless since at least 8.4 IMO there's no need to bother with 
> manual vacuum any more.
> 
> Regards, Jens

a bit off-topic, but:
i'm using pg 9 and always getting messages to vacuum tables in pgadmin. 
according to your post this souldn't be the case?

cheers sven

-- 
GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit 
gratis Handy-Flat! http://portal.gmx.net/de/go/dsl

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Autovacuum firing up during my manual vacuum on same table

[Guillaume Lelarge]

Le 02/04/2011 16:06, Sven Haag a écrit :
> 
>  Original-Nachricht 
>> Datum: Sat, 2 Apr 2011 14:17:37 +0200
>> Von: Jens Wilke 
>> An: pgsql-general@postgresql.org
>> Betreff: Re: [GENERAL] Autovacuum firing up during my manual vacuum on same 
>> table
> 
>> On Samstag, 2. April 2011, Henry C. wrote:
>>
>>> I just noticed something odd:  I'm busy with a manual vacuum on a
>>> table and an autovacuum keeps firing up as well. 
>>
>> Usually a manual vacuum cancels a running autovacuum task.
>> You should find a notice about the cancelation in th logfile.
>>
>>> current_query | vacuum analyze 
>>> age   | 11:46:57.245568
>>
>> Where is the age column from?
>> It's not in pg_stat_activity.
>> Is one of the two processes waiting=t in pg_stat_activity?
>> Shure it's the same Table? Do you have one Table named 
>> page_citation_text in public and one in anoter sheme?
>>
>>> I'm trying to vacuum the table as quickly as possible so a manual
>>> vacuum seems to be in order as my understanding (and experience)
>>> is that the autovac is a hell of a lot slower to mitigate impact
>>> on general performance.
>>
>> Tune the autovacuum settings, especially the scale factors of the 
>> tables in question and the cost limit.
>> Is autovacuum_vacuum_cost_limit != -1 ?
>> This could be one reason, why manual vacuum is faster.
>>
>> Nevertheless since at least 8.4 IMO there's no need to bother with 
>> manual vacuum any more.
>>
>> Regards, Jens
> 
> a bit off-topic, but:
> i'm using pg 9 and always getting messages to vacuum tables in pgadmin. 
> according to your post this souldn't be the case?
> 

pgAdmin's hint doesn't take care of your PostgreSQL release. Its way to
guess that a table needs to be vacuumed is a bit outdated (that's
clearly an understatement :) ).


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to get index columns/dir/ord informations?

[durumdara]

Hi!

The pg_index, and pg_indexes is good for I get the index names, and types.

I have two indexes on test table "a":

CREATE INDEX ix1
  ON a
  USING btree
  (a);


CREATE UNIQUE INDEX x2
  ON a
  USING btree
  (a DESC, b);

From this I can recognize the type (unique or normal) of the index, but 
none of the columns.


I don't found any tables that can say to me, which columns with which 
direction used in index.


A pseudo code demonstrate it:

select * from pg_index_columns where index_name = 'x2'

Ordinal   ColNameIsAsc
1 a   False
2 b   True

Have PGSQL same information?

Thanks:
dd




2011.04.01. 18:01 keltezéssel, Raghavendra írta:

Hi,

Query to list the tables and its concerned indexes.

SELECT indexrelid::regclass as index , relid::regclass as
table FROM pg_stat_user_indexes JOIN pg_index USING
(indexrelid) WHERE idx_scan < 100 AND indisunique IS FALSE;

Query will list the contraints.

SELECT relname FROM pg_class WHERE oid IN ( SELECT indexrelid FROM 
pg_index, pg_class WHERE pg_class.oid=pg_index.indrelid AND ( 
indisunique = 't' OR indisprimary = 't' ) );


To get the column order number, use this query.

SELECT a.attname,a.attnum FROM pg_class c, pg_attribute a, pg_type t 
WHERE c.relname = 'vacc' AND a.attnum > 0 AND a.attrelid = c.oid AND 
a.atttypid = t.oid;


Note: This query for a particular Table 'VACC'


Best Regards,
Raghavendra
EnterpriseDB Corporation

On Fri, Apr 1, 2011 at 8:54 PM, Durumdara > wrote:


Hi!

I want to migrate some database to PG.
I want to make intelligens migrator, that makes the list of the
SQL-s what need to do to get same table structure in PG as in the
Source DB.

All things I can get from the views about tables, except the indices.

These indices are not containing the constraints - these elements
I can analyze.

I found and SQL that get the index columns:


select
t.relname as table_name,
i.relname as index_name,
a.attname as column_name
from
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
where
t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = 'r'
and t.relname = 'a'
and ix.indisunique = 'f'
and ix.indisprimary = 'f'
order by
t.relname,
i.relname;

This can list the columns. But - what a pity - this don't
containing that:
- Is this index unique?
- What the direction of the sort by columns
- What is the ordinal number of the column

So everything what I need to analyze that the needed index is
exists or not.


Please help me: how can I get these informations?
I don't want to drop the tables everytime if possible.

Thanks:
dd

Re: [GENERAL] Autovacuum firing up during my manual vacuum on same table

[Henry C.]

Forgot to mention:  I'm using 9.0.3

> Usually a manual vacuum cancels a running autovacuum task.

Not in my case - however, the autovac does seem to be in a waiting state.

> You should find a notice about the cancelation in th logfile.
>
> > current_query | vacuum analyze
> > age   | 11:46:57.245568
>
> Where is the age column from?
> It's not in pg_stat_activity.

age(now(), query_start)

>> Is one of the two processes waiting=t in pg_stat_activity?

ah, there it is.  Yes, the autovac is waiting.

>> > I'm trying to vacuum the table as quickly as possible so a manual
>> > vacuum seems to be in order as my understanding (and experience)
>> > is that the autovac is a hell of a lot slower to mitigate impact
>> > on general performance.
>>
>> Tune the autovacuum settings, especially the scale factors of the
>> tables in question and the cost limit.
>> Is autovacuum_vacuum_cost_limit != -1 ?
>> This could be one reason, why manual vacuum is faster.

autovacuum_vacuum_cost_limit is on default (-1).



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Autovacuum firing up during my manual vacuum on same table

[Henry C.]

On Sat, April 2, 2011 14:17, Jens Wilke wrote:
> Nevertheless since at least 8.4 IMO there's no need to bother with
> manual vacuum any more.

Sadly, in my case, the db is so busy that autovac processes run for weeks and
never catch up (insufficient h/w for the app quite frankly - the addition of
some more SSD drives have already helped).  I eventually run up against the
wraparound wall and the only way forward is to stop everything and
dump/restore (vacuuming the entire db would take an unknown period of N x
weeks - dumping/restoring completes in a day or two).

I really wish the xid was 64 bits instead of 32, but that's another topic
entirely.

Cheers
Henry


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Autovacuum firing up during my manual vacuum on same table

[Scott Marlowe]

On Sat, Apr 2, 2011 at 11:26 AM, Henry C.  wrote:
> On Sat, April 2, 2011 14:17, Jens Wilke wrote:
>> Nevertheless since at least 8.4 IMO there's no need to bother with
>> manual vacuum any more.
>
> Sadly, in my case, the db is so busy that autovac processes run for weeks and
> never catch up (insufficient h/w for the app quite frankly - the addition of
> some more SSD drives have already helped).  I eventually run up against the
> wraparound wall and the only way forward is to stop everything and
> dump/restore (vacuuming the entire db would take an unknown period of N x
> weeks - dumping/restoring completes in a day or two).

Have you tried upping the aggressiveness of autovacuum?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Autovacuum firing up during my manual vacuum on same table

[Jens Wilke]

On Samstag, 2. April 2011, Henry C. wrote:

> Sadly, in my case, the db is so busy that autovac processes run
> for weeks and never catch up 

Increase the cost_limit and the HW and/or check your application, if 
it's possible to reduce the amount of deletes and/or updates.

Regards, Jens

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Autovacuum firing up during my manual vacuum on same table

[Henry C.]

On Sat, April 2, 2011 21:26, Scott Marlowe wrote:
> On Sat, Apr 2, 2011 at 11:26 AM, Henry C.  wrote:
>
>> On Sat, April 2, 2011 14:17, Jens Wilke wrote:
>>
>>> Nevertheless since at least 8.4 IMO there's no need to bother with
>>> manual vacuum any more.
>>
>> Sadly, in my case, the db is so busy that autovac processes run for weeks
>> and never catch up (insufficient h/w for the app quite frankly - the
>> addition of some more SSD drives have already helped).  I eventually run up
>> against the wraparound wall and the only way forward is to stop everything
>> and dump/restore (vacuuming the entire db would take an unknown period of N
>> x weeks - dumping/restoring completes in a day or two).
>
> Have you tried upping the aggressiveness of autovacuum?

Thanks for the suggestion - I'm going to give autovacuum_vacuum_cost_delay=0 a
try (instead of the default 20ms, which if I'm reading the docs correctly,
means the same aggressiveness as a manual vacuum), and see how things go in
terms of the I/O cost/responsiveness and ensuring the damn vacuums finish in a
reasonable time before the wraparound tactical nuke hits :)




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Autovacuum firing up during my manual vacuum on same table

[Tom Lane]

Scott Marlowe  writes:
> On Sat, Apr 2, 2011 at 11:26 AM, Henry C.  wrote:
>> Sadly, in my case, the db is so busy that autovac processes run for weeks and
>> never catch up (insufficient h/w for the app quite frankly - the addition of
>> some more SSD drives have already helped).

> Have you tried upping the aggressiveness of autovacuum?

I'm wondering about poor selection of the cost_delay settings in
particular.  It's quite easy to slow autovacuum to the point that
it takes forever to do anything.

It's also possible that Henry is getting bit by the bug fixed here:

Author: Tom Lane 
Branch: master [b58c25055] 2010-11-19 22:28:20 -0500
Branch: REL9_0_STABLE Release: REL9_0_2 [b5efc0940] 2010-11-19 22:28:25 -0500
Branch: REL8_4_STABLE Release: REL8_4_6 [fab2af30d] 2010-11-19 22:28:30 -0500
Branch: REL8_3_STABLE Release: REL8_3_13 [6cb9d5113] 2010-11-19 22:28:35 -0500

Fix leakage of cost_limit when multiple autovacuum workers are active.

When using default autovacuum_vac_cost_limit, autovac_balance_cost relied
on VacuumCostLimit to contain the correct global value ... but after the
first time through in a particular worker process, it didn't, because we'd
trashed it in previous iterations.  Depending on the state of other autovac
workers, this could result in a steady reduction of the effective
cost_limit setting as a particular worker processed more and more tables,
causing it to go slower and slower.  Spotted by Simon Poole (bug #5759).
Fix by saving and restoring the GUC variables in the loop in do_autovacuum.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] wal_level=minimal produces more data than archive level

[Tomas Vondra]

Hi everyone,

I've been playing with various wal_level settings, mainly to find out
what is the overhead of hot standby, and I've noticed a strange thing.
In some cases the wal_level=minimal produces signigicantly more xlog
data than wal_level=archive (and hot_standby).

=

Example:

1) with wal_level=minimal

$ psql -c "select pg_xlogfile_name_offset(pg_current_xlog_location())"

  pg_xlogfile_name_offset

 (00010001,5799664)

$ pgbench -i -s 10

NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
creating tables...
1 tuples done.
2 tuples done.
...
99 tuples done.
100 tuples done.
set primary key...
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"pgbench_branches_pkey" for table "pgbench_branches"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"pgbench_tellers_pkey" for table "pgbench_tellers"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"pgbench_accounts_pkey" for table "pgbench_accounts"
vacuum...done.

$ psql -c "select pg_xlogfile_name_offset(pg_current_xlog_location())"

  pg_xlogfile_name_offset

 (00010001,5945832)

$ pgbench -c 1 -t 1

starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 1
number of transactions actually processed: 1/1
tps = 515.508932 (including connections establishing)
tps = 515.623415 (excluding connections establishing)

$ psql -c "select pg_xlogfile_name_offset(pg_current_xlog_location())"

  pg_xlogfile_name_offset

 (00010006,3395840)

=

2) with wal_level=archive

$ psql -c "select pg_xlogfile_name_offset(pg_current_xlog_location())"

  pg_xlogfile_name_offset

 (00010001,5799708)

$ pgbench -i -s 10

NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
creating tables...
1 tuples done.
2 tuples done.
...
99 tuples done.
100 tuples done.
set primary key...
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"pgbench_branches_pkey" for table "pgbench_branches"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"pgbench_tellers_pkey" for table "pgbench_tellers"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"pgbench_accounts_pkey" for table "pgbench_accounts"
vacuum...done.

$ psql -c "select pg_xlogfile_name_offset(pg_current_xlog_location())"

  pg_xlogfile_name_offset

 (0001000B,8772044)

$ pgbench -c 1 -t 1

starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 1
number of transactions actually processed: 1/1
tps = 748.476327 (including connections establishing)
tps = 748.664607 (excluding connections establishing)

$ psql -c "select pg_xlogfile_name_offset(pg_current_xlog_location())"

   pg_xlogfile_name_offset
-
 (0001000B,13937304)

=

Which means the pgbench init takes about 142kB with 'minimal' level and
about 163MB with 'archive' level (which is expected).

But the actual pgbench run produces much more xlog data with minimal wal
level compared to archive level. With minimal level it produces about
90MB and with archive level it produces just about 5MB.

I've check the docs and the only possible explanation I've noticed when
reading the docs is that while wal_level=minimal allows to skip logging
of some bulk operations (e.g. COPY, which is exactly what pgbench does
when initializing the DB), it may need to log more data later (when
actually running the bench).

Is that a correct conclusion, or am I missing something?

regards
Tomas

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] wal_level=minimal produces more data than archive level

[Tom Lane]

Tomas Vondra  writes:
> I've been playing with various wal_level settings, mainly to find out
> what is the overhead of hot standby, and I've noticed a strange thing.
> In some cases the wal_level=minimal produces signigicantly more xlog
> data than wal_level=archive (and hot_standby).
> ...
> Which means the pgbench init takes about 142kB with 'minimal' level and
> about 163MB with 'archive' level (which is expected).

> But the actual pgbench run produces much more xlog data with minimal wal
> level compared to archive level. With minimal level it produces about
> 90MB and with archive level it produces just about 5MB.

> I've check the docs and the only possible explanation I've noticed when
> reading the docs is that while wal_level=minimal allows to skip logging
> of some bulk operations (e.g. COPY, which is exactly what pgbench does
> when initializing the DB), it may need to log more data later (when
> actually running the bench).

I wonder whether the discrepancy relates to having to produce
full-page-image WAL entries during the first touch of a page during the
pgbench run, versus having already done so in initialization.  If you
force a checkpoint after the init step, do the results change?

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres 9.1 - Release Theme

[Brent Wood]

I haven't checked to follow this up, but it seems like the sort of announcement 
one might expect on 1 April.

Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Darren Duncan  04/02/11 3:01 PM >>>
I was under the impression that QUEL was actually a good language in some ways, 
and that it was more relational and better than SQL in some ways.

   http://en.wikipedia.org/wiki/QUEL_query_languages

Maybe bringing it back would be a good idea, but as an alternative to SQL 
rather 
than a replacement.

In any event, QUEL was somewhat similar to SQL.

-- Darren Duncan

Rajasekhar Yakkali wrote:
> "Following a great deal of discussion, I'm pleased to announce that the
> PostgreSQL Core team has decided that the major theme for the 9.1
> release, due in 2011, will be 'NoSQL'.
> 
> "... the intention is to remove SQL support from
> Postgres, and replace it with a language called 'QUEL'. This will
> provide us with the flexibility we need to implement the features of
> modern NoSQL databases. With no SQL support there will obviously be
> some differences in the query syntax that must be used to access your
> data. "
> 
> hmm..  shock it is this shift for 9.1 due in mid 2011 is unexpectedly
> soon :)
> 
> Curious to understand as to
> 
> - how this relates to every feature that is provide at the moment based on
> RDBMS paradigm.
> 
> ACID compliance, support for the features provided by SQL,  referential
> integrity, joins, caching etc, ..
> 
> -  Also does this shift take into an assumption that all the use cases fit
> the likes of data access patterns & usecases similar to facebook/twitter?
> or to address the the likes of those ?
> 
> Thanks,
> Raj
> 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Please consider the environment before printing this email.

NIWA is the trading name of the National Institute of Water & Atmospheric 
Research Ltd.

Re: [GENERAL] wal_level=minimal produces more data than archive level

[Tomas Vondra]

Dne 3.4.2011 02:45, Tom Lane napsal(a):
> Tomas Vondra  writes:
>> I've been playing with various wal_level settings, mainly to find out
>> what is the overhead of hot standby, and I've noticed a strange thing.
>> In some cases the wal_level=minimal produces signigicantly more xlog
>> data than wal_level=archive (and hot_standby).
>> ...
>> Which means the pgbench init takes about 142kB with 'minimal' level and
>> about 163MB with 'archive' level (which is expected).
> 
>> But the actual pgbench run produces much more xlog data with minimal wal
>> level compared to archive level. With minimal level it produces about
>> 90MB and with archive level it produces just about 5MB.
> 
>> I've check the docs and the only possible explanation I've noticed when
>> reading the docs is that while wal_level=minimal allows to skip logging
>> of some bulk operations (e.g. COPY, which is exactly what pgbench does
>> when initializing the DB), it may need to log more data later (when
>> actually running the bench).
> 
> I wonder whether the discrepancy relates to having to produce
> full-page-image WAL entries during the first touch of a page during the
> pgbench run, versus having already done so in initialization.  If you
> force a checkpoint after the init step, do the results change?

Yes, a forced CHECKPOINT results in a much more xlog data in case of the
archive level. Without the checkpoint there was about 5MB, now there is
about about 90MB (a fet kB more than with the minimal wal level).

Hmmm, I'm wondering which of these two cases is more apropriate when
comparing wal levels - with the checkpoint or without it? I believe the
one with checkpoint, as checkpoints happen all the time anyway.

regards
Tomas

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] wal_level=minimal produces more data than archive level

[Tom Lane]

Tomas Vondra  writes:
> Dne 3.4.2011 02:45, Tom Lane napsal(a):
>> I wonder whether the discrepancy relates to having to produce
>> full-page-image WAL entries during the first touch of a page during the
>> pgbench run, versus having already done so in initialization.  If you
>> force a checkpoint after the init step, do the results change?

> Yes, a forced CHECKPOINT results in a much more xlog data in case of the
> archive level. Without the checkpoint there was about 5MB, now there is
> about about 90MB (a fet kB more than with the minimal wal level).

> Hmmm, I'm wondering which of these two cases is more apropriate when
> comparing wal levels - with the checkpoint or without it? I believe the
> one with checkpoint, as checkpoints happen all the time anyway.

Well, both of them are boundary cases --- in a realistic situation you'd
have some but not all pages already dirty since the last checkpoint.

The important point here is that you had non-comparable starting
conditions.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres 9.1 - Release Theme

[Darren Duncan]

Brent Wood wrote:

I haven't checked to follow this up, but it seems like the sort of announcement 
one might expect on 1 April.


I know that the announcement wasn't serious, but I still took it as an 
opportunity to suggest in seriousness that something of value was lost when QUEL 
was dropped from Postgres. -- Darren Duncan



Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand

Darren Duncan  04/02/11 3:01 PM >>>
I was under the impression that QUEL was actually a good language in some ways, 
and that it was more relational and better than SQL in some ways.


   http://en.wikipedia.org/wiki/QUEL_query_languages

Maybe bringing it back would be a good idea, but as an alternative to SQL rather 
than a replacement.


In any event, QUEL was somewhat similar to SQL.

-- Darren Duncan

Rajasekhar Yakkali wrote:

"Following a great deal of discussion, I'm pleased to announce that the
PostgreSQL Core team has decided that the major theme for the 9.1
release, due in 2011, will be 'NoSQL'.

"... the intention is to remove SQL support from
Postgres, and replace it with a language called 'QUEL'. This will
provide us with the flexibility we need to implement the features of
modern NoSQL databases. With no SQL support there will obviously be
some differences in the query syntax that must be used to access your
data. "

hmm..  shock it is this shift for 9.1 due in mid 2011 is unexpectedly
soon :)

Curious to understand as to

- how this relates to every feature that is provide at the moment based on
RDBMS paradigm.

ACID compliance, support for the features provided by SQL,  referential
integrity, joins, caching etc, ..

-  Also does this shift take into an assumption that all the use cases fit
the likes of data access patterns & usecases similar to facebook/twitter?
or to address the the likes of those ?

Thanks,
Raj







--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general