SEO

Steve Crawford wrote:
> I'm in the process of archiving data on one of my PG machines. After
> backing up the data, I delete the old records and then run a "vacuum
> full" on each table.
>
> I'm vacuuming the first table now and it is taking much longer than I
> expected (I'm now past the 2-hour mark). Some info:
>
> Version: 8.1.2
> On-disk table size: ~1.9GB
> Records deleted from the table: 10,290,892 (~60% of records)
> Physical memory: 2GB
> Connection maintenance_work_mem: 1GB
> Table indexes: 7
> CPU: Intel(R) Pentium(R) 4 CPU 3.00GHz
> Disk: 2x200GB SATA as RAID-1 using 3-ware card
>
> The vacuum full is the only significant load on the server at the moment
> (PG or otherwise). IO is probably the bottleneck as CPU is running near
> 50% idle and 40% wait-state with PG using in the 5-15% range.

You could try CLUSTER instead of VACUUM FULL, as I think it should be
faster. And the indexes will be devoid of any bloat, which will be a
nice side effect.

I wonder, though, if you set maintenance_work_mem too high and are
causing the OS to swap?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

> You could try CLUSTER instead of VACUUM FULL, as I think it should be
> faster. And the indexes will be devoid of any bloat, which will be a
> nice side effect.
>
> I wonder, though, if you set maintenance_work_mem too high and are
> causing the OS to swap?
>

Hmmm, why would cluster be faster?

No swapping - "top" shows swap mem of 3MB used and that wasn't changing.
Just to be sure I ran "swapoff -a ; swapon -a" which brought it back to
zero and it's not budging from there.

Cheers,
Steve

---------------------------(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

Alvaro Herrera <alvherre@commandprompt.com> writes:
> I wonder, though, if you set maintenance_work_mem too high and are
> causing the OS to swap?

AFAIR, vacuum full pays no attention to maintenance_work_mem anyway.
If the data it needs doesn't fit in memory, you lose ...

regards, tom lane

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

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

Hi:

I'm a new user of Postgresql (8.2.3), and I'm very happy with both
the performance and operation of the system. My compliments to you
the many authors who keep this database running and useful.

My question is:

I want to "freeze" a snapshot of the database every year (think of
end of year tax records). However, I want this frozen version (and
all the previous frozen versions) available to the database user as
read-only. My thinking is to copy the entire public schema (which is
where all the current data lives) into a new schema, named 2007
(2008, etc.)

Is this a valid plan. I had thought of using a different database,
but that would require multiple opens. I looked to see if there were
an easy way to script doing an exact schema copy, but I haven't found
anything like it in the docs.

This is not heavy usage, nor is there a large amount of data (current
pg_dump backups are around 30 Megabytes.

Am I on the right track, or would you suggest a different strategy?

-Owen

Clipboard, Inc.

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

On Wed, Apr 25, 2007 at 09:36:35AM -0700, Steve Crawford wrote:
> Hmmm, why would cluster be faster?

Basically, vacuum full moves tuples from the end to the beginning of a
table so it can compact the table. In the process it needs to update
all the indexes too. So you save heap space but it tends to fragment
your index. Lots of disk writes also.

OTOH, cluster simply scans the table, sorts it, writes it out then
rebuilds the indexes. If you've removed a lot of tuples, empirically
it's faster.

VACUUM FULL is discouraged these days, simply becuase it isn't actually
as efficient as you might expect. Better to make sure it doesn't grow
big in the first place, and use CLUSTER to rebuild the table if you
really need to.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFGL5ImIB7bNG8LQkwRAmWAAJ9DIJU2bPHBFPjFvwYKCM MFm4GHCwCfVzij
RF5/MzOHXUbAIAcHcKP9IhI=
=S5e9
-----END PGP SIGNATURE-----

Owen Hartnett wrote:
> I want to "freeze" a snapshot of the database every year (think of end
> of year tax records). However, I want this frozen version (and all the
> previous frozen versions) available to the database user as read-only.
> My thinking is to copy the entire public schema (which is where all the
> current data lives) into a new schema, named 2007 (2008, etc.)

Sounds perfectly reasonable. You could either do it as a series of:
CREATE TABLE archive2007.foo AS SELECT * FROM public.foo;
or do a pg_dump of schema "public", tweak the file to change the schema
names and restore it.

--
Richard Huxton
Archonet Ltd

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

Martijn van Oosterhout wrote:
> On Wed, Apr 25, 2007 at 09:36:35AM -0700, Steve Crawford wrote:
>> Hmmm, why would cluster be faster?
>
> Basically, vacuum full moves tuples from the end to the beginning of a
> table so it can compact the table. In the process it needs to update
> all the indexes too. So you save heap space but it tends to fragment
> your index. Lots of disk writes also.
>
> OTOH, cluster simply scans the table, sorts it, writes it out then
> rebuilds the indexes. If you've removed a lot of tuples, empirically
> it's faster.
>
> VACUUM FULL is discouraged these days, simply becuase it isn't actually
> as efficient as you might expect. Better to make sure it doesn't grow
> big in the first place, and use CLUSTER to rebuild the table if you
> really need to.
>
> Hope this helps,

So my mental-model is utterly and completely wrong. My assumption was
that since a full vacuum requires an access exclusive lock, it would do
the intelligent and efficient thing which would be to first compact the
table and then recreate the indexes.

Am I reading that what it actually does is to thrash around keeping
indexes unnecessarily updated, bloating them in the process?

Will cluster reduce the on-disk size like vacuum does?

(

And am I the only one who thinks the cluster command is backwards -
after all it is the table that is being reordered based on an index so:

CLUSTER tablename ON indexname

seems way more intuitive than

CLUSTER indexname ON tablename

)

Cheers,
Steve


---------------------------(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

Steve Crawford wrote:

> So my mental-model is utterly and completely wrong. My assumption was
> that since a full vacuum requires an access exclusive lock, it would do
> the intelligent and efficient thing which would be to first compact the
> table and then recreate the indexes.

Right, it doesn't do the intelligent and efficient thing. There are
differences though: VACUUM FULL does not need an extra copy of the table
and indexes, while CLUSTER does.

OTOH, VACUUM FULL also needs to WAL log every action, which makes it
slower; CLUSTER only calls fsync when it's done, but since it keeps the
original files around it doesn't need to involve WAL.

> Am I reading that what it actually does is to thrash around keeping
> indexes unnecessarily updated, bloating them in the process?

Yes.

> Will cluster reduce the on-disk size like vacuum does?

Yes. And a bit more because indexes don't suffer.

> And am I the only one who thinks the cluster command is backwards -
> after all it is the table that is being reordered based on an index so:

No, you're not, which is why a new syntax has been introduced for 8.3.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Steve Crawford wrote:
>> Am I reading that what it actually does is to thrash around keeping
>> indexes unnecessarily updated, bloating them in the process?

> Yes.

Just for the record, it's not "unnecessary". The point of that is to
not leave a corrupted table behind if VACUUM FULL fails midway through.
The algorithm is:

1. copy tuples to lower blocks, inserting index entries for them too

During this stage, if we fail then the copied tuples are invalid (since
they were inserted by a failed transaction) and so no corruption.
Meanwhile the original tuples are marked as "moved by this vacuum
transaction", but their validity is not affected by that.

2. mark the transaction committed

This atomically causes all the copied tuples to be GOOD and all the
originals to be INVALID according to the tuple validity rules.

3. remove the index entries for moved-off tuples

If we crash here, some of the invalid tuples will have index entries
and some won't, but that doesn't matter because they're invalid.
(The next vacuum will take care of finishing the cleanup.)

4. remove the moved-off tuples (which just requires truncating the
table)


I don't see a way to remove the old index entries before inserting new
ones without creating a window where the index and table will be
inconsistent if vacuum fails.

CLUSTER avoids all this thrashing by recopying the whole table, but
of course that has peak space requirements approximately twice the
table size (and is probably not a win anyway unless most of the table
rows need to be moved). You pays your money, you takes your choice.

regards, tom lane

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

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

> I don't see a way to remove the old index entries before inserting new
> ones without creating a window where the index and table will be
> inconsistent if vacuum fails.

VACUUM FULL is slow because it plays with the indexes...
CLUSTER is slow because it has to order the rows...

Maybe, drop all the indexes, VACUUM FULL only the table, then recreate
all the indexes ?
If vacuum fails, the index drop would be rolled back.

By the way, about indexes :

When you have a small table (say, for a website, maybe a few tens of
megabytes max...) reindexing it takes just a few seconds, maybe 10-20
seconds.
It could be interesting, performance-wise, to tell postgres not to bother
about crash-survivability of indexes on this table. Like temporary tables.
Write nothing to WAL. If it crashes, on recovery, postgres would reindex
the table.
btree indexing is so fast on postgres that I'd definitely use this
feature.
I'd rather trade a minute of recovery versus less disk IO for index
update.

You could even do that for whole tables (like, web sessions table) which
hold "perishable" data...

> CLUSTER avoids all this thrashing by recopying the whole table, but
> of course that has peak space requirements approximately twice the
> table size (and is probably not a win anyway unless most of the table
> rows need to be moved). You pays your money, you takes your choice.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/