Re: TRUNCATE TABLE

From: Adriaan van Os
Subject: Re: TRUNCATE TABLE
Date: ,
Msg-id: 4697A58E.8060904@microbizz.nl
(view: Whole thread, Raw)
In response to: Re: TRUNCATE TABLE  (Tom Lane)
Responses: Re: TRUNCATE TABLE  ("Steinar H. Gunderson")
Re: TRUNCATE TABLE  (Tom Lane)
List: pgsql-performance

Tree view

TRUNCATE TABLE  (Adriaan van Os, )
 Re: TRUNCATE TABLE  (Tom Lane, )
  Re: TRUNCATE TABLE  (Adriaan van Os, )
   Re: TRUNCATE TABLE  (Tom Lane, )
 Re: TRUNCATE TABLE  (Gregory Stark, )
  Re: TRUNCATE TABLE  (Adriaan van Os, )
   Re: TRUNCATE TABLE  (Gregory Stark, )
   Re: TRUNCATE TABLE  (Jean-Max Reymond, )
    Re: TRUNCATE TABLE  ("Thomas Samson", )
   Re: TRUNCATE TABLE  (Michael Stone, )
    Re: TRUNCATE TABLE  (Tom Lane, )
     Re: TRUNCATE TABLE  (Adriaan van Os, )
      Re: TRUNCATE TABLE  ("Steinar H. Gunderson", )
      Re: TRUNCATE TABLE  (Tom Lane, )
       Re: TRUNCATE TABLE  ("Jim C. Nasby", )
       Re: TRUNCATE TABLE  (Alvaro Herrera, )
        Re: TRUNCATE TABLE  ("Pavel Stehule", )
         Re: TRUNCATE TABLE  ("Jim C. Nasby", )
          Re: TRUNCATE TABLE  (Tom Lane, )
           Re: TRUNCATE TABLE  (Adriaan van Os, )
           Re: TRUNCATE TABLE  (Adriaan van Os, )
            Re: TRUNCATE TABLE  (Alvaro Herrera, )
           Re: TRUNCATE TABLE  ("Kevin Grittner", )
            Re: TRUNCATE TABLE  (Adriaan van Os, )
             Re: TRUNCATE TABLE  (Decibel!, )
              Re: TRUNCATE TABLE  (Tom Lane, )
       Re: TRUNCATE TABLE  (Adriaan van Os, )

Tom Lane wrote:
> Michael Stone <> 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


pgsql-performance by date:

From: Alvaro Herrera
Date:
Subject: Re: Database Statistics???
From: Hannes Dorbath
Date:
Subject: Re: FORGOT TO CONFIGURE RAID! DELL POWEREDGE 2950