Date: Fri, 13 Jul 2007 18:17:18 +0200
From: Adriaan van Os <postgres@microbizz.nl>
Subject: Re: TRUNCATE TABLE
In-reply-to: <21495.1184337630@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Message-id: <4697A58E.8060904@microbizz.nl>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <46953949.3030201@microbizz.nl>
 <87ps2ypox3.fsf@oxford.xeocode.com>
	<46972DFA.9000900@microbizz.nl> <20070713124435.GL31358@mathom.us>
	<21495.1184337630@sss.pgh.pa.us>
User-Agent: Thunderbird 1.5.0.4 (Macintosh/20060516)

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