Philip Warner heeft op woensdag, 18 dec 2002 om 13:05
(Europe/Amsterdam) het volgende geschreven:
At 12:47 PM 18/12/2002 +0100, Roel Rozendaal - IC&S wrote:
pg_toast_10945937 | 3841293
...
What exactly is this pg_toast thing?
When a text field is too large to fit on a page, it is written to a
separate table (one for each real table) in chunks. My guess is that
if you do:
select relname from pg_class where oid = '10945937'
you will get 'messageblks'. If so, then the messageblks toast table is
the culprit (and is about 30GB in size).
If the guess is wrong, let me know the table name...
It is the messageblks table. However, the query
select sum(blocksize) from messageblks;
(which should return the amount of actual data in the table) gives ~10
GB - so where does the extra 20 GB come from?
I am somewhat reluctant to run a 'VACUUM xxx' right now as it has the
side-effect of raising the load from ca. 0.7 to ca. 7 and turning the
database completely irresponsive - it does concern a production
machine.
This should not happen. We don't have such a large DB, but the users
barely notice a VACUUM - do you know which resource is the problem?
Disk IO, CPU or memory? In Linux, eg, if DMA is not enabled for disks,
then high I/O stalls the machine - could some similar issue be
occurring? Another thought: try changing the nice value for the
backend doing the vacuum (in general this is a very bad idea, but it
may reduce the impact of the vacuum if it is CPU related).
The system has high-speed SCSI disks, a PIII 1.2 GHz processor and 512
MB of ram - should be sufficient shouldn't it?
I certainly understand your desire not to upset a production machine;
if you are unable to anything else, can you set your next VACCUUM to
be a VACUUM VERBOSE ANALYZE? That way we can get some idea of the
correct settings to use to at least stop the growth.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
_______________________________________________
Dbmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
