Skip site navigation (1) Skip section navigation (2)

Re: TRUNCATE TABLE

From: Adriaan van Os <postgres(at)microbizz(dot)nl>
To:
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: TRUNCATE TABLE
Date: 2007-07-13 16:17:18
Message-ID: 4697A58E.8060904@microbizz.nl (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> Michael Stone <mstone+postgres(at)mathom(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Steinar H. GundersonDate: 2007-07-13 16:29:47
Subject: Re: TRUNCATE TABLE
Previous:From: Tom LaneDate: 2007-07-13 14:40:30
Subject: Re: TRUNCATE TABLE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group