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
