Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 0ACF71EB83E6 for ; Thu, 23 Feb 2012 05:50:37 -0400 (AST) Received: from nm1.bullet.mail.ird.yahoo.com ([77.238.189.58]) by makus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1S0VJr-0001vT-1z for pgsql-performance@postgresql.org; Thu, 23 Feb 2012 09:50:37 +0000 Received: from [77.238.189.234] by nm1.bullet.mail.ird.yahoo.com with NNFMP; 23 Feb 2012 09:50:21 -0000 Received: from [212.82.108.238] by tm15.bullet.mail.ird.yahoo.com with NNFMP; 23 Feb 2012 09:50:21 -0000 Received: from [127.0.0.1] by omp1003.mail.ird.yahoo.com with NNFMP; 23 Feb 2012 09:50:21 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 575860.47590.bm@omp1003.mail.ird.yahoo.com Received: (qmail 15409 invoked by uid 60001); 23 Feb 2012 09:50:21 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.uk; s=s1024; t=1329990621; bh=+JmMlZxoo6fFaWUA++w98DzBKNKMPoLF8XK7l0T5Uac=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=tZ7Ck2KA+sjyhQWXsBN/ZEq7L9GePg8QNJc0HpqjnIf3lzZdvTwfxL19CiWmlyuHt0Op1dcqZbb/+93/ziMjuIun4vcyJSwi1GNk4nRHqP199ygHDnYpfrBBGLqSbZyNHLSskxDzYPzcvUbCQQuWbO+955tBsmawgVPuzoQ0th4= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=kNHp9ZE4JAZIBxBiFQqraXzAKYlT+DzWrMIJoBolPxpuYklEI/r2nDe97SoUpj2euxuJ7mqA7WBdny9+Bd4C9fPI5UrjYaVFIi/a7xGx6iHhePQuo3Zjzu/t+QO7b6j5jiJ+O8oquXXey2kOM4f0OwxTP/VMaV/wCO4z11k3EAo=; X-YMail-OSG: XIEMA9MVM1m0ztI5KSwJ0_ynppC61_4NoRlKTCP670m.n3S QZa9KvBs32TzZY3QtPo3rJRwI5UAidHvJftHF8U8c_KgjJgU8HecqK6vcbv5 AAgiFU.UpQXST5fo39AzG0z3WTdIZh_fybtL2cQn9WGUnrAkQbDkY7D_qx5j npWAJzRmc.nf7vgV5PvkFXoiBoYlcOa2GLOyfI8B_sJb4dLP4AkU1JZzSml3 Ne6AtteCKb.OkOfZZalAp9tmx_9kjgOYNfx_iTLaZpFmOtmkTkViaRap7vpQ a1gGqjc3szvon71IiL710TWwHfCXpYVt3Pc1EYIv.fH8rBVpDi.8ib1grQt2 R1N7qWZ.cuZOk278mPn.u584JsO6Zia5dqHYGuNNhFJ_0E.pWOuI.p7iI6st sOBd_jynol6_CqPIV0TRP0DQPNC0dOcAo9TDM7PkuOf5MqNnQEyNoEZKsddP 0.Oupd5RpaJDgFlE4kvf9mkGNnwz1gwm4.5o9S8UFvUxFI00uco_VytwxlvP ruQXuZZ67kBBljLkc.dc88bKH4J4azzY- Received: from [83.100.146.195] by web171403.mail.ir2.yahoo.com via HTTP; Thu, 23 Feb 2012 09:50:20 GMT X-Mailer: YahooMailWebService/0.8.116.338427 References: <4F45FB55.1020600@lerner.co.il> Message-ID: <1329990620.15378.YahooMailNeo@web171403.mail.ir2.yahoo.com> Date: Thu, 23 Feb 2012 09:50:20 +0000 (GMT) From: Glyn Astill Reply-To: Glyn Astill Subject: Re: Very long deletion time on a 200 GB database To: "Reuven M. Lerner" , "pgsql-performance@postgresql.org" In-Reply-To: <4F45FB55.1020600@lerner.co.il> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-1610926429-435149022-1329990620=:15378" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201202/153 X-Sequence-Number: 46325 ---1610926429-435149022-1329990620=:15378 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Do you have any more detailed information about the hardware, what sort of = disk configuration does it have?=0A=0ACan you get onto the machine to look = at what is using those resources?=A0 You mention the 25gb of virtual memory= ; is that being used?=A0 If so is it being used by postgres or something el= se?=A0 If it's being used by postgres you should change postgresql.conf to = work within your 5gb, otherwise can you stop the other applications to do y= our delete?=A0 A snapshot from task manager or process monitor of process r= esource usage would be useful, even better somelogging from perfmon includi= ng physical disk usage.=0A=0A=0AWhat would be even more useful is the table= definitions; you mention trying to drop constraints to speed it up but is = there anything else at play, e.g. triggers?=0A=0A=0A=0A>___________________= _____________=0A> From: Reuven M. Lerner =0A>To: pgsql= -performance@postgresql.org =0A>Sent: Thursday, 23 February 2012, 8:39=0A>S= ubject: [PERFORM] Very long deletion time on a 200 GB database=0A> =0A>=0A>= =0A>Hi, everyone.=A0 I'm maintaining an application that exists as a "black= box" in manufacturing plants.=A0 The system is based on Windows, .NET, and= PostgreSQL 8.3.=A0 I'm a Unix kind of guy myself, but the application laye= r and system administration are being handled by other people; I'm just the= PostgreSQL guy.=0A>=0A>Because of the nature of the application, we don't = have direct control over what happens.=A0 And it turns out that at one inst= allation, we're quickly running out of disk space.=A0 The database is alrea= dy taking up about 200 GB of space, and is growing by 1 GB or so a day.=A0 = Switching disks, either to a larger/faster traditional drive, or even to a = SSD, is not an option.=A0 (And yes, I know that SSDs have their own risks, = but I'm just throwing that out as one option.)=0A>=0A>Right now, the best s= olution to the space problem is to delete information associated with old r= ecords, where "old" is from at least 30 days ago.=A0 The old records are sp= read across a few tables, including many large objects.=A0 (The application= was written by people who were new to PostgreSQL, and didn't realize that = they could use BYTEA.) Basically, given a foreign key B.a_id that points to= table A, I want to DELETE all in B where A's creation date is at least 30 = days ago.=0A>=0A>Unfortunately, when we implemented this simple delete, it = executed slower than molasses, taking about 9 hours to do its thing.=A0 No= t 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.=0A>=0A>I've tried a = few approaches so far, none of which have been hugely successful.=A0 The fa= ct that it takes several hours to test each theory is obviously a bit of a = pain, and so I'm curious to hear suggestions from people here.=0A>=0A>I sho= uld note that my primary concern is available RAM.=A0 The database, as I wr= ote, is about 200 GB in size, and PostgreSQL is reporting (according to Win= dows) use of about 5 GB RAM, plus another 25 GB of virtual memory.=A0 I've = 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 dat= abase and operating system are both transferring massive amounts of data ba= ck and forth.=A0 But there doesn't seem to be a good way to handle this=0A>= =0A>This is basically what I'm trying to execute:=0A>=0A>DELETE FROM=A0 B= =0A>WHERE r_id IN (SELECT R.id=0A>=A0 =A0 FROM R, B=0A>=A0 =A0 WHERE r.end= _date < (NOW() - (interval '1 day' * 30))=0A>=A0 =A0 =A0 AND r.id =3D b.r_i= d=0A>=0A>(1) I tried to write this as a join, rather than a subselect.=A0 B= ut B has an oid column that points to large objects, and on which we have a= rule that removes the associated large object when a row in B is removed. = Doing the delete as a join resulted in "no such large object with an oid of= xxx" errors.=A0 (I'm not sure why, although it might have to do with the r= ule.)=0A>=0A>(2) I tried to grab the rows that *do* interest me, put them i= nto a temporary table, TRUNCATE the existing table, and then copy the rows = back.=A0 I only tested that with a 1 GB subset of the data, but that took = longer than other options.=0A>=0A>(3) There are some foreign-key constraint= s on the B table.=A0 I thought that perhaps doing a mass DELETE was queuein= g up all of those constraints, and possibly using up lots of memory and/or = taking a long time to execute.=A0 I thus rewrote my queries such that they = first removed the constraints, then executed the DELETE, and then restored = the constraints.=A0 That didn't seem to improve things much either, and too= k a long time (30 minutes) just to remove the constraints.=A0 I expected re= -adding the constraints to take a while, but shouldn't removing them be rel= atively quick?=0A>=0A>(4) I tried "chunking" the deletes, such that instead= of trying to delete all of the records from the B table, I would instead d= elete just those associated with 100 or 200 rows from the R table.=A0 On a = 1 GB subset of the data, this seemed to work just fine.=A0 But on the actua= l database, it was still far too slow.=0A>=0A>I've been surprised by the ti= me it takes to delete the records in question.=A0 I keep trying to tell the= others on this project that PostgreSQL isn't inherently slow, but that a 2= 00 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 sof= tware we're using.=A0 But even so, 9 hours to delete 100 GB of data strikes= me as a very long process.=0A>=0A>Again, I continue to believe that given = our hard time deadlines, and the fact that we're using a large amount of vi= rtual memory, that there isn't really a solution that will work quickly and= easily.=A0 But I'd be delighted to be wrong, and welcome any and all comme= nts and suggestions for how to deal with this.=0A>=0A>Reuven=0A>=0A>-- Reuv= en M. Lerner -- Web development, consulting, and training=0A>Mobile: +972-5= 4-496-8405 * US phone: 847-230-9795=0A>Skype/AIM: reuvenlerner=0A>=0A>-- Se= nt via pgsql-performance mailing list (pgsql-performance@postgresql.org)=0A= >To make changes to your subscription:=0A>http://www.postgresql.org/mailpre= f/pgsql-performance=0A>=0A>=0A> ---1610926429-435149022-1329990620=:15378 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Do you have any more detailed information about the hardware, what sort o= f disk configuration does it have?

=
Can you get onto the machine to look at what is using those reso= urces?  You mention the 25gb= of virtual memory; is that being used?  If so is it being used by pos= tgres or something else?  If it's being used by postgres you should ch= ange postgresql.conf to work within your 5gb, otherwise can you stop the ot= her applications to do your delete?  A snapshot from task manager or <= /span>process monitor of process resource usage would be useful, even= better some logging from perfmon including physical disk usag= e.

What would be even more useful is the table definitions; you mention trying to drop cons= traints to speed it up but is there anything else at play, e.g. triggers?


From: Reuven M. Lerner <reuven@lerner.co.i= l>
To: pgsql-perfor= mance@postgresql.org
Sent: Thursday, 23 February 2012, 8:39
Subject: [PERFORM] Very long deletion time on a 200 GB dat= abase

Hi, everyone.  I'm maint= aining an application that exists as a "black box" in manufacturing plants.=   The system is based on Windows, .NET, and PostgreSQL 8.3.  I'm = a Unix kind of guy myself, but the application layer and system administrat= ion are being handled by other people; I'm just the PostgreSQL guy.

= Because of the nature of the application, we don't have direct control over= what happens.  And it turns out that at one installation, we're quick= ly running out of disk space.  The database is already taking up about= 200 GB of space, and is growing by 1 GB or so a day.  Switching disks= , either to a larger/faster traditional drive, or even to a SSD, is not an = option.  (And yes, I know that SSDs have their own risks, but I'm just= throwing that out as one option.)

Right now, the best solution to t= he space problem is to delete information associated with old records, where "old" is from at least 30 days ago.  The old records a= re spread across a few tables, including many large objects.  (The app= lication was written by people who were new to PostgreSQL, and didn't reali= ze that they could use BYTEA.) Basically, given a foreign key B.a_id that p= oints to table A, I want to DELETE all in B where A's creation date is at l= east 30 days ago.

Unfortunately, when we implemented this simple del= ete, 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 maintenanc= e activity, before the factory starts to use our black box again.

I'= ve tried a few approaches so far, none of which have been hugely successful= .  The fact that it takes several hours to test each theory is obvious= ly a bit of a pain, and so I'm curious to hear suggestions from people here.

I should note that my primary concern is available RAM= .  The database, as I wrote, is about 200 GB in size, and PostgreSQL i= s reporting (according to Windows) use of about 5 GB RAM, plus another 25 G= B of virtual memory.  I've 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 transfe= rring massive amounts of data back and forth.  But there doesn't seem = to be a good way to handle this

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() - (interv= al '1 day' * 30))
      AND r.id =3D b.r_id

(1) I = tried to write this as a join, rather than a subselect.  But B has an = oid column that points to large objects, and on which we have a rule that removes the associated large object when a row in B is removed. Doing= the delete as a join resulted in "no such large object with an oid of xxx"= errors.  (I'm not sure why, although it might have to do with the rul= e.)

(2) I tried to grab the rows that *do* interest me, put them int= o a temporary table, TRUNCATE the existing table, and then copy the rows ba= ck.  I only tested that with a 1 GB subset of the data, but that took= longer than other options.

(3) There are some foreign-key constrain= ts on the B table.  I thought that perhaps doing a mass DELETE was que= ueing 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 tha= t they first removed the constraints, then executed the DELETE, and then re= stored the constraints.  That didn't seem to improve things much eithe= r, and took a long time (30 minutes) just to remove the constraints.  I expected re-adding the constraints to take a while, b= ut shouldn't removing them be relatively quick?

(4) I tried "chunkin= g" the deletes, such that instead of trying to delete all of the records fr= om the B table, I would instead delete just those associated with 100 or 20= 0 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 slo= w.

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 Post= greSQL isn't inherently slow, but that a 200 GB database running on a non-d= edicated machine, with an old version (8.3), and while it's swapping RAM, w= ill 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.
Again, I continue to believe that given our hard time deadlines, and the fact that we're using a large amount of virtual memory,= that there isn't really a solution that will work quickly and easily. = ; But I'd be delighted to be wrong, and welcome any and all comments and su= ggestions for how to deal with this.

Reuven

-- Reuven M. Lern= er -- Web development, consulting, and training
Mobile: +972-54-496-8405= * US phone: 847-230-9795
Skype/AIM: reuvenlerner

-- Sent via pgs= ql-performance mailing list (pgsql-performance= @postgresql.org)
To make changes to your subscription:
ht= tp://www.postgresql.org/mailpref/pgsql-performance


<= /div>
---1610926429-435149022-1329990620=:15378--