Re: Very long deletion time on a 200 GB database

On Thu, Feb 23, 2012 at 5:39 AM, Reuven M. Lerner <reuven(at)lerner(dot)co(dot)il> wrote:
> Unfortunately, when we implemented this simple delete, it executed slower
> than molasses, taking about 9 hours to do its thing.   Not only does this
> seem like a really, really long time to do such deleting, but we have only a
> 4-hour window in which to run this maintenance activity, before the factory
> starts to use our black box again.

PG 8.3 had horrible hash joins for big tables, so you might try "set
enable_hashjoin=false" prior to your query. I suspect this is not your
biggest problem, though...

> I should note that my primary concern is available RAM.  The database, as I
> wrote, is about 200 GB in size, and PostgreSQL is reporting (according to
> Windows) use of about 5 GB RAM, plus another 25 GB of virtual memory.

You really *have* to look into that situation. 25GB of *active*
virtual memory? That would mean a thrashing server, and an utterly
unresponsive one from my experience. Your data is probably wrong,
because if I had a server *using* 30G of RAM only 5 of which are
physical, I wouldn't even be able to open a remote desktop to it.

I bet your numbers are wrong. In any case, you have to look into the
matter. Any amount of swapping will kill performance for postgres,
throwing plans out of whack, turning sequential I/O into random I/O, a
mess.

> told the Windows folks on this project that virtual memory kills a database,
> and that it shouldn't surprise us to have horrible performance if the
> database and operating system are both transferring massive amounts of data
> back and forth.  But there doesn't seem to be a good way to handle this

There is, tune postgresql.conf to use less memory.

> This is basically what I'm trying to execute:
>
> DELETE FROM  B
> WHERE r_id IN (SELECT R.id
>     FROM R, B
>    WHERE r.end_date < (NOW() - (interval '1 day' * 30))
>      AND r.id = b.r_id

DELETE is way faster when you have no constraints, no primary key, no indices.

If you could stop all database use in that 4-hour period, it's
possible dropping indices, FKs and PK, delete, and recreating the
indices/FKs/PK will run fast enough.
You'll have to test that on some test server though...

> (3) There are some foreign-key constraints on the B table.  I thought that
> perhaps doing a mass DELETE was queueing up all of those constraints, and
> possibly using up lots of memory and/or taking a long time to execute.  I
> thus rewrote my queries such that they first removed the constraints, then
> executed the DELETE, and then restored the constraints.

That's not enough. As I said, you have to drop the PK and all indices too.

> That didn't seem to
> improve things much either, and took a long time (30 minutes) just to remove
> the constraints.  I expected re-adding the constraints to take a while, but
> shouldn't removing them be relatively quick?

That means your database is locked (a lot of concurrent access), or
thrashing, because dropping a constraint is a very quick task.
If you stop your application, I'd expect dropping constraints and
indices to take almost no time.

> (4) I tried "chunking" the deletes, such that instead of trying to delete
> all of the records from the B table, I would instead delete just those
> associated with 100 or 200 rows from the R table.  On a 1 GB subset of the
> data, this seemed to work just fine.  But on the actual database, it was
> still far too slow.

Check the hash join thing.
Check/post an explain of the delete query, to see if it uses hash
joins, and which tables are hash-joined. If they're big ones, 8.3 will
perform horribly.

> I've been surprised by the time it takes to delete the records in question.
>  I keep trying to tell the others on this project that PostgreSQL isn't
> inherently slow, but that a 200 GB database running on a non-dedicated
> machine, with an old version (8.3), and while it's swapping RAM, will be
> slow regardless of the database software we're using.  But even so, 9 hours
> to delete 100 GB of data strikes me as a very long process.

Deletes in MVCC is more like an update. It's a complex procedure to
make it transactional, that's why truncate is so much faster.