On 29/02/12 06:06, David Kerr wrote:
On 02/27/2012 12:08 AM, Reuven M. Lerner wrote:
Hi, everyone. I wanted to thank you again for your help on the huge
delete problem that I was experiencing.
After a lot of trial and error, we finally came to the conclusion that
deleting this much data in the
On 02/27/2012 07:14 AM, Shaun Thomas wrote:
On 02/27/2012 08:59 AM, Reuven M. Lerner wrote:
From what I understand, the issue isn't one of current disk space,
but rather of how quickly the disk space is being used up.
Noted. Just keep in mind that dead rows are not free. In the case of sequen
On 02/27/2012 12:08 AM, Reuven M. Lerner wrote:
Hi, everyone. I wanted to thank you again for your help on the huge
delete problem that I was experiencing.
After a lot of trial and error, we finally came to the conclusion that
deleting this much data in the time frame that they need, on
underpow
On Mon, Feb 27, 2012 at 2:13 PM, Samuel Gendler
wrote:
>
>
> On Mon, Feb 27, 2012 at 6:59 AM, Reuven M. Lerner
> wrote:
>>
>>
>> So for now, we'll just try to DELETE faster than we INSERT, and combined
>> with autovacuum, I'm hoping that this crisis will be averted. That said,
>> the current sta
On Mon, Feb 27, 2012 at 6:59 AM, Reuven M. Lerner wrote:
>
> So for now, we'll just try to DELETE faster than we INSERT, and combined
> with autovacuum, I'm hoping that this crisis will be averted. That said,
> the current state of affairs with these machines is pretty fragile, and I
> think that
On Mon, Feb 27, 2012 at 12:01 PM, Shaun Thomas wrote:
>
> Like I said, this will stop his tables from growing further so long as he
> keeps his maintenance functions running regularly from now on, but the
> existing rows he's trying to delete will never go away until he runs a
> CLUSTER or some ot
On 02/27/2012 08:59 AM, Reuven M. Lerner wrote:
From what I understand, the issue isn't one of current disk space,
but rather of how quickly the disk space is being used up.
Noted. Just keep in mind that dead rows are not free. In the case of
sequence scans, the rows still have to be read fro
On 02/27/2012 08:53 AM, Andrew Dunstan wrote:
If he has autovacuum on he could well be just fine with his proposed
strategy. Or he could have tables partitioned by time and do the delete
by just dropping partitions. There are numerous way he could get this to
work.
He isn't using partitions th
Hi, Shaun. You wrote:
In the end, it was agreed that we could execute the deletes over
time, deleting items in the background, or in parallel with the
application's work. After all, if the disk is filling up at the rate
of 2 GB/day, then so long as we delete 4 GB/day (which is pretty easy
to d
On 02/27/2012 09:45 AM, Shaun Thomas wrote:
On 02/27/2012 02:08 AM, Reuven M. Lerner wrote:
In the end, it was agreed that we could execute the deletes over
time, deleting items in the background, or in parallel with the
application's work. After all, if the disk is filling up at the rate
of
On 02/27/2012 02:08 AM, Reuven M. Lerner wrote:
In the end, it was agreed that we could execute the deletes over
time, deleting items in the background, or in parallel with the
application's work. After all, if the disk is filling up at the rate
of 2 GB/day, then so long as we delete 4 GB/day (w
I have a question.
Your data is growing 1Gb by 1 day.
Can we use another Disk or partition to continue archive data ?
I mean, do postgreSql support a Layering System for archive data ?
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Very-long-deletion-time-on-a-200-GB-d
Hi, everyone. I wanted to thank you again for your help on the huge
delete problem that I was experiencing.
After a lot of trial and error, we finally came to the conclusion that
deleting this much data in the time frame that they need, on
underpowered hardware that is shared with an applicat
Hi again, everyone.
Wow, I can't get over how helpful everyone has been.
Shaun wrote:
The main problem you're going to run into is that your table is larger than the
memory in that server. 4GB is really pretty small for a server hosting a 200+GB
database. That they didn't mean it to get that
Hi, everyone. Jeff wrote:
Is this 9 hours run time for deleting one day worth of data, or for
deleting the entire accumulation of cruft that filled up the hard
drive in the first place (which would be 170 days, if you have 200GB
that accumulated at 1GB per day and you only need 30 days) ?
Unf
On Thu, Feb 23, 2012 at 12:39 AM, Reuven M. Lerner wrote:
> Hi, everyone. I'm maintaining 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 admi
On 02/23/2012 12:39 AM, Reuven M. Lerner wrote:
Hi, everyone. ...
...at one installation, we're quickly 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.
I've been following the discussion of approaches and tuning for bulk
On 02/24/2012 08:54 AM, Reuven M. Lerner wrote:
I/O writes: process1: 820,000, process2: 1Milion Process3: 33,000
That's not especially helpful, unfortunately. That doesn't really tell
us how saturated the controller is. However I suspect it's being
effectively slammed based simply on your C
Hi, everyone. Shaun said:
On 02/24/2012 12:39 AM, Reuven M. Lerner wrote:
- CPU is virtually idle when running the deletes, using about 1% of
CPU
I think you found your problem.
See if you can get the Windows admins to give you some info on how busy
the disks are (percent utilization, IOPS,
On 02/24/2012 12:39 AM, Reuven M. Lerner wrote:
- CPU is virtually idle when running the deletes, using about 1% of
CPU
I think you found your problem.
See if you can get the Windows admins to give you some info on how busy
the disks are (percent utilization, IOPS, something) the next time y
On Fri, Feb 24, 2012 at 9:37 AM, Reuven M. Lerner wrote:
> I just checked, and work_mem is set to 30 MB. That seems a bit low to me,
> given the size of the database and the fact that we're doing so much sorting
> and subselecting. Am I right that we should push that up some more?
You can certa
Hi, everyone. Samuel wrote:
What is work_mem set to? If all the other values were set so low, I'd
expect work_mem to also be small, which could be causing all kind of
disk activity when steps don't fit into a work_mem segment.
I just checked, and work_mem is set to 30 MB. That seems a bit l
On Thu, Feb 23, 2012 at 10:39 PM, Reuven M. Lerner wrote:
> Hi, everyone.
>
> So it turns out that we're not using 25 GB of virtual memory. (That's
> what I had been shown yesterday, and it was a bit surprising, to say the
> least...)
>
> A few statistics that I managed to get from the Windows de
On 02/23/2012 07:28 AM, k...@rice.edu wrote:
On Thu, Feb 23, 2012 at 05:25:46PM +0200, Reuven M. Lerner wrote:
What is the distribution of end_dates? It might be worth running this in
several steps, deleting records older than, say, 90 days, 60 days, 30 days.
I've suggested something similar,
Hi, everyone.
So it turns out that we're not using 25 GB of virtual memory. (That's
what I had been shown yesterday, and it was a bit surprising, to say the
least...)
A few statistics that I managed to get from the Windows
developers/system administrators:
- The machine has a total of 3.5
On 23/02/12 09:39, Reuven M. Lerner wrote:
Hi, everyone. I'm maintaining 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 administration are being
I just reread the original post and noted this:
"Reuven M. Lerner" writes:
> (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.
Greg Spiegelberg writes:
> I used LOCK simply because if a VACUUM FULL x; slipped in between the
> SELECT and the DELETE the ctid's could conceivably change.
VACUUM FULL can't "slip in" there, because you'd have AccessShareLock
just from the SELECT. The real problem goes like this:
1. Y
On Thu, Feb 23, 2012 at 11:11 AM, Andy Colson wrote:
> On 2/23/2012 12:05 PM, Shaun Thomas wrote:
>
>> On 02/23/2012 11:56 AM, Greg Spiegelberg wrote:
>>
>> I know there are perils in using ctid but with the LOCK it should be
>>> safe. This transaction took perhaps 30 minutes and removed 100k ro
On 2/23/2012 12:05 PM, Shaun Thomas wrote:
On 02/23/2012 11:56 AM, Greg Spiegelberg wrote:
I know there are perils in using ctid but with the LOCK it should be
safe. This transaction took perhaps 30 minutes and removed 100k rows
and once the table was VACUUM'd afterward it freed up close to 20
On 02/23/2012 11:56 AM, Greg Spiegelberg wrote:
I know there are perils in using ctid but with the LOCK it should be
safe. This transaction took perhaps 30 minutes and removed 100k rows
and once the table was VACUUM'd afterward it freed up close to 20 GB
on the file system.
It took *30 minutes
On Thu, Feb 23, 2012 at 8:25 AM, Reuven M. Lerner wrote:
>
> I've suggested something similar, but was told that we have limited time
> to execute the DELETE, and that doing it in stages might not be possible.
>
>
Just so happens I had this exact problem last week on a rather large table.
* DELET
On 02/23/2012 02:39 AM, Reuven M. Lerner wrote:
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.
O_o
That... that would pro
On 02/23/2012 12:39 AM, Reuven M. Lerner wrote:
Hi, everyone...
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
I don't recall which versions like which appro
On Thu, Feb 23, 2012 at 05:25:46PM +0200, Reuven M. Lerner wrote:
> >
> >What is the distribution of end_dates? It might be worth running this in
> >several steps, deleting records older than, say, 90 days, 60 days, 30 days.
>
> I've suggested something similar, but was told that we have limited
>
Hi, everyone. Thanks for all of the help and suggestions so far; I'll
try to respond to some of them soon. Andrew wrote:
How about:
DELETE FROM B
WHERE r_id IN (SELECT distinct R.id
FROM R WHERE r.end_date< (NOW() - (interval '1 day' * 30))
?
Or possibly without the DISTINCT. But I agree
On 02/23/2012 05:07 AM, Marcin Mańk wrote:
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
How about:
DELETE FROM B
WHERE r_id IN (SELECT distinct R.id
FROM R WHERE r.end_date< (NOW() - (in
On Thu, Feb 23, 2012 at 5:39 AM, Reuven M. Lerner 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 w
On 23/02/12 08:39, Reuven M. Lerner wrote:
(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 f
> 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
>
How about:
DELETE FROM B
WHERE r_id IN (SELECT distinct R.id
FROM R WHERE r.end_date < (NOW() - (interval '1 day' * 30))
?
Greetings
Marcin
Do you have any more detailed information about the hardware, what sort of disk
configuration does it have?
Can you get onto the machine to look at what is using those resources? You
mention the 25gb of virtual memory; is that being used? If so is it being used
by postgres or something else?
41 matches
Mail list logo
