From: Adriaan van Os <postgres@microbizz.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: TRUNCATE TABLE
Date: Fri, 13 Jul 2007 18:17:18 +0200
Message-ID: <4697A58E.8060904@microbizz.nl> (raw)
In-Reply-To: <21495.1184337630@sss.pgh.pa.us>
References: <46953949.3030201@microbizz.nl>
<87ps2ypox3.fsf@oxford.xeocode.com>
<46972DFA.9000900@microbizz.nl>
<20070713124435.GL31358@mathom.us>
<21495.1184337630@sss.pgh.pa.us>
Tom Lane wrote:
> Michael Stone <mstone+postgres@mathom.us> writes:
>> xfs' slowness is proportional to the *number* rather than the *size* of
>> the files. In postgres you'll tend to have fewer, larger, files than you
>> would in (e.g.) a source code repository, so it is generally more
>> important to have a filesystem that deletes large files quickly than a
>> filesystem that deletes lots of files quickly.
>
> The weird thing is that the files in question were hardly "large".
> IIRC his test case used a single int4 column, so the rows were probably
> 36 bytes apiece allowing for all overhead. So the test cases with about
> 5K rows were less than 200K in the file, and the ones with 200K rows
> were still only a few megabytes.
Right.
> I tried the test on my Linux machine (which I couldn't do when I
> responded earlier because it was tied up with another test), and
> saw truncate times of a few milliseconds for both table sizes.
> This is ext3 on Fedora 6.
>
> So I'm still of the opinion that there's something broken about
> Adriaan's infrastructure, but maybe we have to look to an even
> lower level than the filesystem. Perhaps he should try getting
> some bonnie++ benchmark numbers to see if his disk is behaving
> properly.
Well, I can hardly believe that something is broken with the infrastructure, because I have seen
the same behaviour on other hardware (or it must be that I am using the standard postgresql.conf).
I started another test. I copied an existing database (not very large, 35 tables, typically a few
hundred up to a few thousand records) with CREATE DATABASE testdb TEMPLATE mydb and started to
remove random tables from testdb with DROP TABLE and TRUNCATE TABLE. I did this with the query tool
of pgAdmin III, to exclude any doubts about my own software (that uses pqlib). The hardware is an
Intel dual-core 17-inch MacBook Pro running Mac OS X 10.4.
I can not make any sense of the results. Truncating or dropping a table typically takes 1-2 ms or
30-70 ms or 200-500 ms. I have seen that truncating the *same* table with the *same* data takes 1
ms in one test and takes 532 ms in another one. The database has no foreign keys.
Based on these results, I still believe there is a problem in Postgres.
Regards,
Adriaan van Os
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: postgres@microbizz.nl
Subject: Re: TRUNCATE TABLE
In-Reply-To: <4697A58E.8060904@microbizz.nl>
* 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