Tom Lane wrote:
Michael Stone <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
