Re: performance tuning on inserts

Check this
http://candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html

I would think that you would see *some* increase in performance by turning
fsync off.... I see huge increases in performance of INSERT/UPDATE with
fsync off.

-Mitch

----- Original Message -----
From: "Peter T. Brown" <peter@memeticsystems.com>
To: <pgsql-admin@postgresql.org>
Sent: Monday, January 28, 2002 11:42 AM
Subject: [ADMIN] performance tuning on inserts


> I'm confused because after implementing much performance tuning advice
I've
> found that postgresql is no faster than when configured with the factory
> defaults. The particular query I used for testing exemplifies the type of
> query I need to run often.
>
> All of my queries rely heavily on doing INSERT INTO. So is there some
> special behavior with insert's, where they are flushed to disk one by one?
> If I simply increase checkpoint_segments to 50 or so would this cause
> inserts to occur only in memory and be flushed to disk at a later time? As
> far as I can tell, there is no performance gain in turning fsync=false. Am
I
> way off by having so many wal_buffers? And do many of them not even get
used
> since checkpoint_segments is only set to 6?
>
> If I can figure all this out, I think I'll make a website dedicated to
> postgres performance tuning.
>
> Thanks very much,
> Peter T. Brown
>
>
> Postgres 7.1.3 is all running on Redhat Linux (2.4.x), dual p3 1G, with a
> large RAID array. The database I'm using has ~10 tables with most having
> over 1.5 million rows.
>
> Sample SQL:
> INSERT INTO "VisitorPointer" ("CohortGroupID","VisitorID") SELECT
> 51,"Tidbit"."VisitorID" FROM "Tidbit" WHERE "Tidbit"."CustomFieldID" = 27
> AND "Tidbit"."Value" LIKE 'asd1834%'
> CREATE TEMP TABLE temp5946 AS SELECT DISTINCT ON ("VisitorID") * FROM
> "VisitorPointer" WHERE "CohortGroupID" = 51; DELETE FROM "VisitorPointer"
> WHERE "CohortGroupID" = 51;
> INSERT INTO "VisitorPointer" SELECT * FROM temp5946; DROP TABLE temp5946;
>
> Part of My postgresql.conf:
> max_connections = 100 # 1-1024
> sort_mem = 32168
> shared_buffers = 65536 # min 16
> fsync = true
> wal_buffers = 100 # min 4
> wal_files = 10 # range 0-64
> wal_sync_method = fsync # fsync or fdatasync or open_sync or open_datasync
> # Note: default wal_sync_method varies across platforms
> wal_debug = 0 # range 0-16
> commit_delay = 100 # range 0-100000
> commit_siblings = 5 # range 1-1000
> checkpoint_segments = 6 # in logfile segments (16MB each), min 1
> checkpoint_timeout = 300 # in seconds, range 30-3600
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>