Skip site navigation (1) Skip section navigation (2)

Re: set autovacuum=off

From: Alessandro Gagliardi <alessandro(at)path(dot)com>
To: Andy Colson <andy(at)squeakycode(dot)net>
Cc: Steve Crawford <scrawford(at)pinpointresearch(dot)com>, Thom Brown <thom(at)linux(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: set autovacuum=off
Date: 2012-02-23 20:40:45
Message-ID: CAAB3BBLUwzmqj516DDL54souKobh9o139AJzK8WCYPdgGZUELQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Feb 23, 2012 at 11:07 AM, Andy Colson <andy(at)squeakycode(dot)net> 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_unique_violation()
  RETURNS trigger AS
$BODY$
BEGIN
  IF EXISTS (SELECT 1 FROM public.locations WHERE geohash = NEW.geohash)
THEN
    RETURN NULL;
  ELSE
    RETURN NEW;
  END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

that are triggered thusly:

CREATE TRIGGER locations_check_unique_violation
  BEFORE INSERT
  ON locations
  FOR EACH ROW
  EXECUTE PROCEDURE locations_quiet_unique_violation();

I left auto-vacuum enabled for those tables.

checkpoint_segments can help insert speed, what do you have that set to?
>
> 40. Checking http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serverit looks like setting that as high as 256 would not necessarily be
unreasonable. What do you think?


> Also how you insert can make things faster too. (insert vs prepared vs
> COPY)
>
> I'm doing this all with INSERT. Is COPY that much faster? I don't know
anything about prepared.


> Also, if you have too many indexes on a table that can cause things to
> slow down.
>
> Yeah, got that. I removed a bunch. I'd rather not remove what's left
unless I have to.


> Your IO layer needs to be fast too.  Have you watched vmstat and iostat?
>
> I don't know if I have access to vmstat and iostat. Heroku is hosting this
for me on AWS.


> Have you read up on synchronous_commit?
>
> Only a tiny bit. A couple people suggested disabling it since my database
is being hosted on AWS so I did that. It seems a bit risky but perhaps
worth it.

In response to

Responses

pgsql-performance by date

Next:From: Andy ColsonDate: 2012-02-23 20:57:29
Subject: Re: set autovacuum=off
Previous:From: Alessandro GagliardiDate: 2012-02-23 20:28:30
Subject: Re: set autovacuum=off

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group