Re: DELETE vs TRUNCATE explanation

On 07/12/2012 06:51 AM, Daniel Farina wrote:
> 15x slower.  This is a Macbook Air with full disk encryption and SSD
> disk with fsync off, e.g. a very typical developer configuration.
Don't use full disk encryption for throwaway test data if you care about
how long those tests take. It's a lot like tuning the engine in your car
while ignoring the fact that the handbrake is jammed on and you're
dragging a parachute. Use a ramdisk or un-encrypted partition, something
that doesn't take three weeks to fsync().


That said, this performance gap makes me wonder if TRUNCATE is forcing
metadata synchronisation even with fsync=off, causing the incredibly
glacially awesomely slow disk access of your average FDE system to kick
in, possibly even once per table or even once per file (index, table,
toast, etc). If so, it may be worth:

- Allowing TRUNCATE to skip synchronization when fsync=off. Pg is
already allowed to eat all your data if it feels like it in this
configuration, so there's no point flushing filesystem metadata to make
sure files are really swapped.

- When fsync=on, trying to flush all changes to all files out at once
rather than once per file as it could be doing (haven't checked) right
now. How to do this without also flushing all other pending I/O on the
whole system (with a global "sync()") would be somewhat OS/filesystem
dependent, unfortunately.

You could help progress this issue constructively by doing some
profiling on your system, tracing Pg's system calls, and determining
what exactly it's doing with DELETE vs TRUNCATE and where the time goes.
On Linux you'd use OProfile for this and on Solaris you'd use DTrace.
Dunno what facilities Mac OS X has but there must be something similar.

Once you've determined why it's slow, you have a useful starting point
for making it faster, first for test systems with fsync=off then, once
that's tracked down, maybe for robust systems with fsync=on.

> I've seen way more than one complaint, and I'm quite sure there are
> thousands of man hours (or more) spent on people who don't even know
> to complain about such atrocious performance (or maybe it's so bad
> that most people run a web search and find out, probably being left
> really annoyed from having to yak shave as a result).
I suspect you're right - as DB based unit testing becomes more
commonplace this is turning up a lot more. As DB unit tests were first
really popular in the ruby/rails crowd they've probably seen the most
pain, but as someone who doesn't move in those circles I wouldn't have
known. They certainly don't seem to have been making noise about it
here, and I've only recently seen some SO questions about it.

> Perhaps a doc fix is all we need (TRUNCATE is constant-time on large
> tables, but can be very slow compared to DELETE on small tables), but
> I completely and enthusiastically reject any notion from people
> calling this "contrived" or an "edge case," because people writing
> software against PostgreSQL that have unit tests have this use case
> constantly, often dozens or even hundreds of times a day.
I have to agree with this - it may have been an edge case in the past,
but it's becoming mainstream and is worth being aware of.

That said, the group of people who care about this most are not well
represented as active contributors to PostgreSQL. I'd love it if you
could help start to change that by stepping in and taking a little time
to profile exactly what's going on with your system so we can learn
what, exactly, is slow.

--
Craig Ringer