Re: [PERFORM] Very long deletion time on a 200 GB database

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

Re: [PERFORM] set autovacuum=off

On Thu, Feb 23, 2012 at 1:11 PM, Peter van Hardenberg wrote: > My hunch is still that your issue is lock contention. > > How would I check that? I tried looking at pg_locks but I don't know what to look for. > We have many customers who do much more than this throughput, though > I'm not sure wh

Re: [PERFORM] set autovacuum=off

On Thu, Feb 23, 2012 at 1:37 PM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > It's possible that you might get a nice boost by wrapping the inserts into > a transaction: > begin; > insert into...; > insert into...; > insert into...; > ... > commit; > > This only requires all that disk

Re: [PERFORM] set autovacuum=off

On 02/23/2012 01:07 PM, Alessandro Gagliardi wrote: The second one (a bunch of insert statements within a single connection). As I mentioned above, I was going to try the temp table thing, but that wasn't fast enough. COPY might be my next attempt. insert into...; insert into...; insert into..

Re: [PERFORM] set autovacuum=off

On Thu, Feb 23, 2012 at 11:26 AM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > ** > You need to rethink things a bit. Databases can fail in all sorts of ways > and can slow down during bursts of activity, data dumps, etc. You may need > to investigate some form of intermediate bufferi

Re: [PERFORM] set autovacuum=off

On 2/23/2012 2:40 PM, Alessandro Gagliardi wrote: checkpoint_segments can help insert speed, what do you have that set to? 40. Checking http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server it looks like setting that as high as 256 would not necessarily be unreasonable. What do you

Re: [PERFORM] set autovacuum=off

On Thu, Feb 23, 2012 at 11:07 AM, Andy Colson wrote: > That depends on if you have triggers that are doing selects. But in > general you are correct, analyze wont help inserts. > > I do have some, actually. I have a couple trigger functions like: CREATE OR REPLACE FUNCTION locations_quiet_uniqu

Re: [PERFORM] set autovacuum=off

I'm unable to make sense of pg_locks. The vast majority are locktype='transactionid', mode='ExclusiveLock', granted=t. There are some 'relation' locks with mode='RowExclusiveLock' and fewer with 'AccessShareLock'. I have no idea what I should be looking for here. On Thu, Feb 23, 2012 at 10:42 AM,

Re: [PERFORM] Disable-spinlocks while compiling postgres 9.1 for ARM Cortex A8

Jayashankar K B writes: > Hi All, > I am trying to compile Postgres Source code for ARM cortex A8 architecture. > While compiling, I got an error message which read "selected processor does > not support `swpb r4,r4,[r3]' " > One of the Postgres forums at the location > "http://postgresql.104569

[PERFORM] Disable-spinlocks while compiling postgres 9.1 for ARM Cortex A8

Hi All, I am trying to compile Postgres Source code for ARM cortex A8 architecture. While compiling, I got an error message which read "selected processor does not support `swpb r4,r4,[r3]' " One of the Postgres forums at the location "http://postgresql.1045698.n5.nabble.com/BUG-6331-Cross-compi

Re: [PERFORM] Very long deletion time on a 200 GB database

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

Re: [PERFORM] set autovacuum=off

On 02/23/2012 10:38 AM, Alessandro Gagliardi wrote: On Thu, Feb 23, 2012 at 10:01 AM, Steve Crawford > wrote: The documentation has information like "This parameter can only be set in the postgresql.conf file or on the server command line." tha

Re: [PERFORM] set autovacuum=off

On 2/23/2012 12:38 PM, Alessandro Gagliardi wrote: Does analyze increase the efficiency of inserts or just selects? (I assumed the latter.) Obviously, I will need to analyze sometimes, but That depends on if you have triggers that are doing selects. But in general you are correct, analyze wo

Re: [PERFORM] Very long deletion time on a 200 GB database

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.

Re: [PERFORM] set autovacuum=off

On Thu, Feb 23, 2012 at 10:38 AM, Alessandro Gagliardi wrote: > On Thu, Feb 23, 2012 at 10:01 AM, Steve Crawford > wrote: > So if it's not auto-vacuuming that's making my inserts so slow, what is it? > I'm batching my inserts (that didn't seem to help at all actually, but maybe > cause I had alre

Re: [PERFORM] set autovacuum=off

On Thu, Feb 23, 2012 at 10:01 AM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > ** > The documentation has information like "This parameter can only be set in > the postgresql.conf file or on the server command line." that will tell > you in advance which settings will fail when you at

Re: [PERFORM] Very long deletion time on a 200 GB database

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

Re: [PERFORM] Very long deletion time on a 200 GB database

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

Re: [PERFORM] Very long deletion time on a 200 GB database

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

Re: [PERFORM] Very long deletion time on a 200 GB database

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

Re: [PERFORM] set autovacuum=off

On 02/23/2012 09:35 AM, Alessandro Gagliardi wrote: I should have been more clear. I virtually never delete or do updates, but I insert /a lot/. So the table does change quite a bit, but only in one direction. I was unable to disable autovacuum universally (due to the cant_change_runtime_para

Re: [PERFORM] set autovacuum=off

Hm. Okay, so just to be perfectly clear, my database with all its INSERTs, but no DELETEs or UPDATEs should not be VACUUMing anyway, so disabling auto-vacuum is redundant (and possibly hazardous). FWIW, I did notice a speed increase after disabling auto-vacuum on several of my tables though that c

Re: [PERFORM] Very long deletion time on a 200 GB database

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

Re: [PERFORM] set autovacuum=off

On 23 February 2012 17:35, Alessandro Gagliardi wrote: > I should have been more clear. I virtually never delete or do updates, but I > insert a lot. So the table does change quite a bit, but only in one > direction. The same thing applies. VACUUM cleans up dead tuples, which INSERTs don't creat

Re: [PERFORM] set autovacuum=off

I should have been more clear. I virtually never delete or do updates, but I insert *a lot*. So the table does change quite a bit, but only in one direction. I was unable to disable autovacuum universally (due to the cant_change_runtime_param error) but I was able to disable it on individual table

Re: [PERFORM] : Cost calculation for EXPLAIN output

On 02/23/2012 06:21 AM, Venkat Balaji wrote: The cost is "13.88" to fetch 1 row by scanning an Primary Key indexed column. Isn't the cost for fetching 1 row is too high ? Not really. The "cost" is really just an estimate to rank alternate query plans so the database picks the least expensive

Re: [PERFORM] : Cost calculation for EXPLAIN output

Venkat Balaji wrote: > The cost is "13.88" to fetch 1 row by scanning an Primary Key > indexed column. > > Isn't the cost for fetching 1 row is too high ? I don't know, how many index pages will need to be randomly accessed in addition to the random heap access? How many dead versions of th

Re: [PERFORM] Very long deletion time on a 200 GB database

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

Re: [PERFORM] Very long deletion time on a 200 GB database

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

Re: [PERFORM] Very long deletion time on a 200 GB database

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 >

Re: [PERFORM] Very long deletion time on a 200 GB database

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

Re: [PERFORM] set autovacuum=off

On 2/23/2012 6:34 AM, Thom Brown wrote: On 22 February 2012 23:50, Alessandro Gagliardi wrote: I have a database where I virtually never delete and almost never do updates. (The updates might change in the future but for now it's okay to assume they never happen.) As such, it seems like it migh

Re: [PERFORM] Very long deletion time on a 200 GB database

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

Re: [PERFORM] Very long deletion time on a 200 GB database

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

Re: [PERFORM] set autovacuum=off

On 22 February 2012 23:50, Alessandro Gagliardi wrote: > I have a database where I virtually never delete and almost never do > updates. (The updates might change in the future but for now it's okay to > assume they never happen.) As such, it seems like it might be worth it to > set autovacuum=off

[PERFORM] set autovacuum=off

I have a database where I virtually never delete and almost never do updates. (The updates might change in the future but for now it's okay to assume they never happen.) As such, it seems like it might be worth it to set autovacuum=off or at least make it so vacuuming hardly ever occurs. Actually,

[PERFORM] : Cost calculation for EXPLAIN output

Hello, I am trying to understand the analysis behind the "cost" attribute in EXPLAIN output. postgres = # explain select * from table_event where seq_id=8520960; QUERY PLAN --

Re: [PERFORM] Very long deletion time on a 200 GB database

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

Re: [PERFORM] Very long deletion time on a 200 GB database

> 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

Re: [PERFORM] Very long deletion time on a 200 GB database

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? 

[PERFORM] Very long deletion time on a 200 GB database

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 handled by other people; I'm just the Post