Jonah H. Harris | 1 Dec 01:32 2007
Picon

Re: Enhancement request

On Nov 30, 2007 5:23 PM, Chris Browne <cbbrowne <at> acm.org> wrote:
> And why is does it not suffice to do the following?

I'm not saying it doesn't.  I'm fine with writing it manually.  I'm
talking about making it easy for most users.  Remember, we're not
UNIX-only; like it or not, most Windows people wouldn't easily be able
to script it.

--

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation                | fax: 732.331.1301
499 Thornall Street, 2nd Floor          | jonah.harris <at> enterprisedb.com
Edison, NJ 08837                        | http://www.enterprisedb.com/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

Scott Marlowe | 1 Dec 04:20 2007
Picon

Re: Enhancement request

On Nov 30, 2007 4:53 PM, Jonah H. Harris <jonah.harris <at> gmail.com> wrote:
> On Nov 30, 2007 5:00 PM, Joshua D. Drake <jd <at> commandprompt.com> wrote:
> > > So: show me a use case for this that will still make sense in a
> > > mostly-autovacuum world.
> >
> > I think you are living in a different world than I am if you think it
> > is a mostly-autovacuum world.
>
> Same here.
>
> > Yes autovacuum is great for general low use scenarios. Throw it at a
> > database doing hundreds of thousands (or even millions) of transactions
> > an hour that has relations that in the multiple hundred gig range and
> > autovacuum is useless for a good portion of that database.
>
> Yes, this is precisely the case I'm talking about.  Every single
> high-volume client we have or have consulted for is using custom
> vacuuming.  Autovacuum works fine for the common case, but it doesn't
> handle high-volume databases very well yet.

That's the case today, because autovacuum is single threaded and can't
hit >1 table at once, so a single very large table vacuum could allow
other, smaller tables to bloat inordinately.

Which is why 8.3 can vacuum > 1 table at a time.

I'm not against having a schema keyword mind you, I'm just pointing
out that the ultimate goal of the hackers seems to be an autovacuum
daemon that can keep the database vacuumed without the need for user
initiated vacuums at all.
(Continue reading)

Tom Lane | 1 Dec 04:44 2007
Picon

Re: Enhancement request

"Joshua D. Drake" <jd <at> commandprompt.com> writes:
> Tom Lane <tgl <at> sss.pgh.pa.us> wrote:
>> So: show me a use case for this that will still make sense in a
>> mostly-autovacuum world.

> I think you are living in a different world than I am if you think it
> is a mostly-autovacuum world.

Certainly it isn't a mostly-autovacuum world in 8.2 or earlier releases,
but that's irrelevant to whether it makes sense to expend effort on a
feature that would appear (at the earliest) in 8.4.  Autovac in 8.3 is
very significantly ahead of where it was in 8.2 --- to the point that
we've turned it on by default --- and I predict that the pressure of
being on by default will really light the afterburners behind its
development.  I think it's entirely likely that by the time 8.4 is
ready, it will be perfectly fair to characterize manual vacuuming
as a buggy-whip technology, at least for all but the
three-sigmas-above-normal users.  And I'd *much* rather see development
effort going into making that vision come true, than into adding
questionably-useful complexity in the support for manual vacuuming.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo <at> postgresql.org so that your
       message can get through to the mailing list cleanly

Joshua D. Drake | 1 Dec 21:30 2007

Re: Enhancement request

On Fri, 30 Nov 2007 19:34:02 -0300
Alvaro Herrera <alvherre <at> alvh.no-ip.org> wrote:

> Joshua D. Drake escribió:
> 
> > Autovacuum is a great utility for many workloads but even with the
> > upcoming changes I will continually find myself turning off
> > autovacuum for specific relations just so I can turn around and
> > turn on vacuum within cron for others.
> > 
> > The multi-worker autovacuum is a great new addition to help part of
> > that problem (starvation) but it is not help against the other
> > (resource consumption, specifically IO).
> 
> Huh, autovac will consume exactly the same amount of I/O as a
> user-induced vacuum, so I don't see your point.

It can be determined "when" the I/O is used.

> Remember, we're not adding VACUUM SCHEMA in 8.2 so it doesn't make any
> sense to try to compare it against the old autovac.  And you can bet
> that in 8.4 autovac will have even more goodies.
> 

I assume you mean 8.3, but that is certainly a valid point.

Sincerely,

Joshua D. Drake
(Continue reading)

Alvaro Herrera | 2 Dec 02:31 2007
Picon

Re: Enhancement request

Joshua D. Drake escribió:
> On Fri, 30 Nov 2007 19:34:02 -0300
> Alvaro Herrera <alvherre <at> alvh.no-ip.org> wrote:
> 
> > Joshua D. Drake escribió:
> > 
> > > The multi-worker autovacuum is a great new addition to help part of
> > > that problem (starvation) but it is not help against the other
> > > (resource consumption, specifically IO).
> > 
> > Huh, autovac will consume exactly the same amount of I/O as a
> > user-induced vacuum, so I don't see your point.
> 
> It can be determined "when" the I/O is used.

Future plans call for being able to change autovac parameters depending
on time of day, so you will be able to decide that using autovacuum too.

> > Remember, we're not adding VACUUM SCHEMA in 8.2 so it doesn't make any
> > sense to try to compare it against the old autovac.  And you can bet
> > that in 8.4 autovac will have even more goodies.
> 
> I assume you mean 8.3, but that is certainly a valid point.

No, I meant 8.4 --- the feature set of 8.3 is already set on stone.  So
VACUUM SCHEMA has to compete with whatever we're able to do for the
release following that one.

--

-- 
Alvaro Herrera                        http://www.advogato.org/person/alvherre
(Continue reading)

Usama Dar | 2 Dec 21:51 2007
Picon

Re: Enhancement request



On Dec 2, 2007 6:31 AM, Alvaro Herrera <alvherre <at> alvh.no-ip.org> wrote:
Joshua D. Drake escribió:
> On Fri, 30 Nov 2007 19:34:02 -0300
> Alvaro Herrera <alvherre <at> alvh.no-ip.org> wrote:
>
> > Joshua D. Drake escribió:
> >
> > > The multi-worker autovacuum is a great new addition to help part of
> > > that problem (starvation) but it is not help against the other
> > > (resource consumption, specifically IO).
> >
> > Huh, autovac will consume exactly the same amount of I/O as a
> > user-induced vacuum, so I don't see your point.
>
> It can be determined "when" the I/O is used.

Future plans call for being able to change autovac parameters depending
on time of day, so you will be able to decide that using autovacuum too.

> > Remember, we're not adding VACUUM SCHEMA in 8.2 so it doesn't make any
> > sense to try to compare it against the old autovac.  And you can bet
> > that in 8.4 autovac will have even more goodies.
>
> I assume you mean 8.3, but that is certainly a valid point.

No, I meant 8.4 --- the feature set of 8.3 is already set on stone.  So
VACUUM SCHEMA has to compete with whatever we're able to do for the
release following that one.

i think even when autovacuum is uber-perfect , the manual overriding commands like VACUUM and utilities like VACUUMDB will still exist for a long time to come, so there is a case of improving them if required.  Maybe it would be just easier to add a schema switch to VACUUMDB, which can just query the catalogs to find which tables to vacuum, this way no parser changes are involved or any tinkering with the backend code.



--
Alvaro Herrera                         http://www.advogato.org/person/alvherre
"Entristecido, Wutra                     (canción de Las Barreras)
echa a Freyr a rodar
y a nosotros al mar"

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq



--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar
Andrew Sullivan | 3 Dec 08:26 2007
Picon

Re: Enhancement request

On Fri, Nov 30, 2007 at 02:00:05PM -0800, Joshua D. Drake wrote:
> Yes autovacuum is great for general low use scenarios. Throw it at a
> database doing hundreds of thousands (or even millions) of transactions
> an hour that has relations that in the multiple hundred gig range and
> autovacuum is useless for a good portion of that database.

This isn't a good argument for adding new knobs, though.  It's an argument
for using the limited resources to make autovacuum better.

Compare with the never-ending arguments for index hints. 

A

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

Tommy Cheng | 3 Dec 11:21 2007
Picon

How to identify the current WAL segment file

Hi everyone,

I am new to postgresql. So point me to somewhere else if you got the right direction.

i am using PostgreSQL 8.1.9 on a linux system and i want to setup PITR. i found that the segment size is too large that the copying frequency is not high enough.

So, i want to set up a cron job that periodically identifies the current WAL segment file once a minute and scp it to other server.

How to do that, can someone give me a template script?

Btw, i saw archieve_timeout setting in http://www.postgresql.org/docs/8.2/static/continuous-archiving.html
that may suits my need, can i use it in 8.1.9?


Scott Marlowe | 3 Dec 22:22 2007
Picon

Re: Is there an easy and safe way to migrate a database to a new tablespace?

On Nov 26, 2007 3:05 PM, David Young <randomfire <at> gmail.com> wrote:
> Hi,
>
> I recently converted to using a postgresql engine that support tablespaces.
> There's a database that I would like to move to a new tablespace for
> performance reasons.  What is the safe and correct way of doing this?  The
> documentation is a bit sketchy on this.

I think you'd have to first set a new default_tablespace for the db,
then you'd have to write a script to go through each object and move
it with an alter (table|index) to the new tablespace.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate

KADDBA | 4 Dec 16:05 2007
Picon

WAL archiving hangs due to missing WAL segment


Hi,
I recently had set up WAL archiving in a testing environment and all went
well. As a test I let the disk fill up until archiving no longer was
possible. 
Then a co-worker noticed this disk filling up and removed some WALL
segments, that were not yet archived. Now the archiving proces hangs.
Messages in logfile:

2007-12-04 15:20:50 MET  LOG:  archive command "cp
"pg_xlog/00000001000000010000003A"
/u002/postgresql/admin/mdbp/arch/"archive_wal_00000001000000010000003A""
failed: return code 256
2007-12-04 15:20:51 MET  DEBUG:  executing archive command "cp
"pg_xlog/00000001000000010000003A"
/u002/postgresql/admin/mdbp/arch/"archive_wal_00000001000000010000003A""
cp: cannot access pg_xlog/00000001000000010000003A: No such file or
directory
2007-12-04 15:20:51 MET  LOG:  archive command "cp
"pg_xlog/00000001000000010000003A"
/u002/postgresql/admin/mdbp/arch/"archive_wal_00000001000000010000003A""
failed: return code 256
2007-12-04 15:20:51 MET  WARNING:  transaction log file
"00000001000000010000003A" could not be archived: too many failures

How can I resolve this issue? I only have WALL segments from
000000010000000100000080 so I am missing all segments from
00000001000000010000003A upto 000000010000000100000080.

--

-- 
View this message in context: http://www.nabble.com/WAL-archiving-hangs-due-to-missing-WAL-segment-tf4943617.html#a14152262
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


Gmane