Re: DELETE vs TRUNCATE explanation - Mailing list pgsql-performance

On 07/12/2012 02:12 PM, Daniel Farina wrote:
> On Wed, Jul 11, 2012 at 6:41 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
>> 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().
> No. Full disk encryption is not that slow.  And as we see, there is a
> workaround that works "just fine" (maybe it could be faster, who
> knows?) in this exact configuration.  The greater problem is more
> likely to be HFS+, the file system.

The two are somewhat hand in hand in any case.

"Three weeks" is of course hyperbole. Nonetheless, I haven't seen a full
disk encryption system that doesn't dramatically slow down synchronous
operations by forcing a lot more work to be done than would be the case
without disk encryption. Perhaps the Mac OS X / HFS+ solution is an
exception to this, but I doubt it.

Given a small program that repeats the following sequence:

- Creates a file
- Writes few bytes to it
- fsync()s and closes it
- deletes it
- fsync()s the directory  to ensure the metadata change is flushed

... and times it, it'd be interesting to do test runs with and without
encryption on HFS+.


> But
> that's surprisingly challenging: for example, last I checked,
> Postgres.app, principally written by one of my colleagues, does *not*
> disable fsync because we don't know of a great way to communicate the
> relaxed expectations of durability, even though Postgres.app is
> targeted towards developers

I think this is an issue of developer and user responsibility. Proper
test/dev separation from production, and a bit of thought, is all it
takes. After all, Pg can't stop you running your unit tests (full of all
those slow TRUNCATEs) against your production database, either.
Durability isn't worth a damn if you just deleted all your data.

About the only technical aid I can see for this would be some kind of
GUC that the app could proactively check against. Set it to "production"
for your production DB, and "test" for your throwaways. If the unit
tests see "production" they refuse to run; if the app proper sees "test"
it warns about data durability. Have it default to unset or "test" so
admins must explicitly set it to "production".

Handily, this is already possible. You can add whatever custom GUCs you
want. If you want to make your unit tests require that a GUC called
"stage.is_production" be off in order to run, just add to postgresql.conf:

   custom_variable_classes = 'stage'
   stage.is_production = off

now, you can see the new GUC:

regress=# SHOW stage.is_production;
  stage.is_production
---------------------
  off
(1 row)

... so your unit tests and app can check for it. Since you're producing
custom installers, this is something you can bundle as part of the
generated postgresql.conf for easy differentiation between test and
production DBs.

If requirements like this were integrated into common unit testing
frameworks some of these worries would go away. That's not something Pg
cane make happen, though.

How would you want to see it work? How would you solve this problem?

> Until then, this is an at the level of an is-ought problem: there is
> no immediate to even moderately distant future where people are not
> going to click the full disk encryption button their OS vendor gives
> them (nor should they *not* click that: people love to download bits
> of data from production to their local machine to figure out problems,
> and I think the world is a better place for it), and people are going
> to use HFS+ in large numbers, so talking about how many people "just"
> ought to reconfigure is tantamount to blaming the victim, especially
> when we have a sound and workable workaround in hand to at least prove
> definitively that the problem is not intractable.

Yes, people do work on production data in test envs, and FDE is overall
a plus. I'd rather they not turn it off - and rather they not have to.
That's why I suggested using a ramdisk as an alternative; it's
completely non-durable and just gets tossed out, so there's no more
worry about data leakage than there is for access to the disk cache
buffered in RAM or the mounted disks of a FDE machine when it's unlocked.

Setting up Pg to run off a ramdisk isn't a one-click trivial operation,
and it sounds like the group you're mainly interested in are the
database-as-a-utility crowd that prefer not to see, think about, or
touch the database directly, hence Postgres.app etc. If so this is much
more of a packaging problem than a core Pg problem. I take your point
about needing to be able to indicate lack of durability to clients, but
think it's relatively easily done with a custom GUC as shown above.

Of course, Pg on a ramdisk has other issues that quickly become apparent
when you "COPY" that 2GB CSV file into your DB...

> Lousy file system is my guess. HFS is not that great. I bet ext3 would
> be a reasonable model of this amount of pain as well.

Hey, HFS+ Journaled/Extended, which is all that you're ever likely to
see, is merely bad :-P

The original HFS, now that was a monster. Not-so-fond memories of
regular Norton tools defrag runs resurfacing from my Mac OS 7 days...

> I'm sure I could, but first I want to put to complete rest the notion
> that this is an "edge case."  It's only an edge case if the only
> database you have runs in production.  An understanding by more people
> that this is a problem of at least moderate impact is a good first
> step.  I'll ask some of my more Macintosh-adept colleagues for advice.

That'd be great; as this is an issue having real world impact, people
with mac equipment and knowledge need to get involved in helping to
solve it. It's not confined to mac, but seems to be worse there.

The other way you could help would be by providing canned self-contained
test cases that can be used to demonstrate the big performance gaps
you're reporting and test them on other platforms / OSes / file systems.
Something with a "I've never used Ruby" quickstart.

> Here's an example of a person that works on a Postgres-oriented
> project at his day job, has multi-year experience with it, and can
> write detailed articles like these:
> https://devcenter.heroku.com/articles/postgresql-concurrency .  If he
> didn't know to get this right without having it called out as a
> caveat, what number of people have but the most slim chance?  Our best
> asset is probably the relative obscurity of TRUNCATE vs. DELETE for
> those who are less familiar with the system.

Yep. This whole issue was new to me until last week too. I run tests
against my DB but it's fast enough here. In any case, for my tests other
costs are greatly more significant than a few fractions of a second
difference in one DB operation. Clearly that's not the case for some DB
unit testing designs.

Other than ruby/rails/rake, what other systems are you aware of that're
affected by these issues? I'm not dismissing ruby, I just want to know
if you know of other groups or techs that're ALSO affected.

> Which is a not-as-pathetic slowdown, but still pretty substantial,
> being somewhat shy of 2x.  I'll ask around for someone who is
> Macintosh-OS-inclined (not as a user, but as a developer) about a good
> way to get a profile.

That'd be great.  Get them onto the list and involved, because if you
want to see this improved it's going to take some back and forth and
someone who can interpret the profile results, test changes, etc.

I only have a limited ability and willingness to drive this forward; I
have to focus on other things. You'll need to be willing to be proactive
and push this a bit. Figuring out what part of truncation is taking the
time would be a big plus, as would determining how much worse FDE makes
it vs an unencrypted disk.

Hopefully others are interested and following along too.

--
Craig Ringer