Message-ID: <555F9CF8.8000708@BlueTreble.com>
Date: Fri, 22 May 2015 16:17:44 -0500
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:31.0) Gecko/20100101 Thunderbird/31.4.0
MIME-Version: 1.0
To: "Muthusamy, Sivaraman" <sivaraman.muthusamy@in.verizon.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: How to clean/truncate / VACUUM FULL pg_largeobject
 without (much) downtime?
References: 
 <CE4383699009064E82F0D16438F7C4660418C79CAB@MS-BAN-E7MB01.intl1.one.verizon.com>
In-Reply-To: 
 <CE4383699009064E82F0D16438F7C4660418C79CAB@MS-BAN-E7MB01.intl1.one.verizon.com>
Content-Type: text/plain; charset="windows-1252"; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 22 May 2015 21:17:47.2227 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BLUPR11MB0673
X-Pg-Spam-Score: -3.0 (---)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 5/11/15 4:55 AM, Muthusamy, Sivaraman wrote:
> Hi Group,
>
> Facing a problem where pg_catalog.pg_largetobject has been growing fast
> recently, in last two weeks. The actual data itself, in user tables, is
> about 60GB, but pg_catalog.pg_largeobject table is 200GB plues. Please
> let me know how to clean/truncate this table without losing any user
> data in other table.

Autovacuum should be taking care of it for you, though you could also 
try a manual vacuum (VACUUM pg_largeobject;).

> With regards to this pg_largeobject, I have the following questions:
>
> -What is this pg_largetobject ?

It stores large objects 
http://www.postgresql.org/docs/9.4/static/lo-interfaces.html

> -what does it contain ? tried PostgreSQL documentation and lists, but
> could not get much from it.
>
> -why does it grow ?
>
> -Was there any configuration change that may have triggered this to
> grow? For last one year or so, there was no problem, but it started
> growing all of sudden in last two weeks. The only change we had in last
> two weeks was that we have scheduled night base-backup for it and
> auto-vacuum feature enabled.

Changes to autovacuum settings could certainly cause changes. 
Long-running transactions would prevent cleanup, as would any prepared 
transactions (which should really be disabled unless you explicitly need 
them).

> -pg_largeobject contains so many duplicate rows (loid). Though there are
> only about 0.6 million rows (LOIDs), but the total number of rows
> including duplicates are about 59million records. What are all these ?

Each row can only be ~2KB wide, so any LO that's larger than that will 
be split into multiple rows.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


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