From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jakub Ouhrabka <kuba@comgate.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: Linux/PostgreSQL scalability issue - problem with 8 cores
Date: Tue, 15 Jan 2008 11:26:00 -0500
Message-ID: <24676.1200414360@sss.pgh.pa.us> (raw)
In-Reply-To: <478CD535.6030004@comgate.cz>
References: <flii57$748$1@news.hub.org>
<7083.1199379570@sss.pgh.pa.us>
<477D1D59.7050002@comgate.cz>
<4782063C.7070105@comgate.cz>
<26502.1199745090@sss.pgh.pa.us>
<4782AB4D.8010009@comgate.cz>
<27655.1199750492@sss.pgh.pa.us>
<47836A4C.5080707@comgate.cz>
<10713.1199813111@sss.pgh.pa.us>
<47874EE8.2050406@comgate.cz>
<12997.1200063940@sss.pgh.pa.us>
<478B4BB9.4030208@comgate.cz>
<13894.1200324134@sss.pgh.pa.us>
<478CA26E.9040105@comgate.cz>
<23470.1200410459@sss.pgh.pa.us>
<478CD535.6030004@comgate.cz>
Jakub Ouhrabka <kuba@comgate.cz> writes:
>>> Huh. One transaction truncating a dozen tables? That would match the
>>> sinval trace all right ...
> It should be 4 tables - the shown log looks like there were more truncates?
Actually, counting up the entries, there are close to 2 dozen relations
apparently being truncated in the trace you showed. But that might be
only four tables at the user level, since each index on these tables
would appear separately, and you might have a toast table plus index
for each one too. If you want to dig down, the table OIDs are visible
in the trace, in the messages with type -1:
>> LOG: sending inval msg -1 0 30036 0 30700 3218341912
^^^^^ ^^^^^
DBOID RELOID
so you could look into pg_class to confirm what's what.
> Yes, performance was the initial reason to use truncate instead of
> delete many years ago. But today the truncated tables usualy contain
> exactly one row - quick measurements now show that it's faster to issue
> delete instead of truncate in this case.
Okay, for a table of just a few entries I agree that DELETE is probably
better. But don't forget you're going to need to have those tables
vacuumed fairly regularly now, else they'll start to bloat.
regards, tom lane
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: tgl@sss.pgh.pa.us, kuba@comgate.cz
Subject: Re: Linux/PostgreSQL scalability issue - problem with 8 cores
In-Reply-To: <24676.1200414360@sss.pgh.pa.us>
* 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