Re: [PERFORM] set autovacuum=off

On Thu, Feb 23, 2012 at 3:28 PM, Alessandro Gagliardi wrote: > 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

Re: [PERFORM] set autovacuum=off

you might also consider increasing the wal_buffers value if it's still the default (64KB) BR

Re: [PERFORM] set autovacuum=off

yup there is. the parameter checkpoint_segments does not require a restart of the server, just a reload :) On Fri, Feb 24, 2012 at 12:54 AM, Alessandro Gagliardi wrote: > On Thu, Feb 23, 2012 at 1:11 PM, Peter van Hardenberg wrote: > >> My hunch is still that your issue is lock contention. >> >>

Re: [PERFORM] set autovacuum=off

On Thu, Feb 23, 2012 at 1:07 PM, Alessandro Gagliardi wrote: >> >> ...Apparently the last four columns don't exist in my database. As for the >> first four, that is somewhat illuminating >> >> Then you are not running a current version of PostgreSQL so the first step >> to performance enhancem

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] 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] 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] 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] 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] 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] 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,