From: Richard Plotkin <richard@richardplotkin.com>
To: pgsql-performance@postgresql.org
Subject: Re: Disk filling, CPU filling, renegade inserts and deletes?
Date: Thu, 21 Apr 2005 11:38:22 -0700
Message-ID: <062f2ea825779496716ce4e72b02e62f@richardplotkin.com> (raw)
In-Reply-To: <b155378a669f04e74f597d4b105c2a10@richardplotkin.com>
References: <6f4567d4e64297b356e2d71ec392cdae@richardplotkin.com>
<4574.1114030270@sss.pgh.pa.us>
<b155378a669f04e74f597d4b105c2a10@richardplotkin.com>
More info on what is bloating:
It's only in one database (the one that's most used), and after running
oid2name on the bloated files, the result is (mysteriously) empty.
Here's the run on the three enormous files:
$ /usr/local/bin/oid2name -d smt -o 160779
From database "smt":
Filenode Table Name
----------------------
$ /usr/local/bin/oid2name -d smt -o 65782869
From database "smt":
Filenode Table Name
----------------------
$ /usr/local/bin/oid2name -d smt -o 83345634
From database "smt":
Filenode Table Name
----------------------
The file list looks like this (with normal sized files mostly removed):
1.0G ./106779
1.0G ./106779.1
1.0G ./106779.2
1.0G ./106779.3
978M ./106779.4
1.0G ./65782869
248M ./65782869.1
0B ./65782871
8.0K ./65782873
780M ./83345634
0B ./83345636
8.0K ./83345638
So does the empty result mean it's a temporary table? There is one
temporary table (in the function previously mentioned) that does get
created and dropped with some regularity.
Thanks again,
Richard
On Apr 20, 2005, at 2:06 PM, Richard Plotkin wrote:
> Hi Tom,
>
>> Q: what have you got the FSM parameters set to?
>
> Here's from postgresql.conf -- FSM at default settings.
> # - Memory -
>
> shared_buffers = 30400 # min 16, at least max_connections*2,
> 8KB each
> work_mem = 32168 # min 64, size in KB
> #maintenance_work_mem = 16384 # min 1024, size in KB
> #max_stack_depth = 2048 # min 100, size in KB
>
> # - Free Space Map -
>
> #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes
> each
> #max_fsm_relations = 1000 # min 100, ~50 bytes each
>
> # - Kernel Resource Usage -
>
> max_files_per_process = 750 #1000 # min 25
> #preload_libraries = ''
>
>
>> Q: what exactly is bloating? Without knowing which tables or indexes
>> are growing, it's hard to speculate about the exact causes. Use du
>> and
>> oid2name, or look at pg_class.relpages after a plain VACUUM.
>
> This I do not know. I've disabled the cron jobs and will let the
> system bloat, then I will gather statistics (I'll give it 12-24
> hours).
>
>> It's likely that the real answer is "you need to vacuum more often
>> than every six hours", but I'm trying not to jump to conclusions.
>
> That could be it, except that I would expect the problem to then look
> more like a gradual increase in CPU usage and a gradual increase in
> use of disk space. Mine could be an invalid assumption, but the
> system here looks like it goes from no problem to 100% problem within
> a minute.
>
> Thanks again!
> Richard
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
> joining column's datatypes do not match
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: richard@richardplotkin.com
Subject: Re: Disk filling, CPU filling, renegade inserts and deletes?
In-Reply-To: <062f2ea825779496716ce4e72b02e62f@richardplotkin.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox