Re: Postgres 7.3.1 poor insert/update/search performance - Mailing list pgsql-performance
From | Seth Robertson |
---|---|
Subject | Re: Postgres 7.3.1 poor insert/update/search performance |
Date | |
Msg-id | 200301220719.h0M7JjA04509@winwood.sysdetect.com Whole thread Raw |
In response to | Re: Postgres 7.3.1 poor insert/update/search performance (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-performance |
In message <13165.1043188295@sss.pgh.pa.us>, Tom Lane writes: Seth Robertson <pgsql-performance@sysd.com> writes: > I'll try that and report back later, but I was under the (false?) > impression that it was primarily important when you had multiple > database connections using the same table. Definitely false. shared_buffers needs to be 1000 or so for production-grade performance. There are varying schools of thought about whether it's useful to raise it even higher, but in any case 64 is just a toy-installation setting. Increasing the setting to 4096 improved write performance by 20%. Increasing the setting to 8192 had no additional effect. I could try a few more probes if anyone cared. The quotes are important when you are dealing with BIGINT indexes. You won't get an indexscan if the constant looks like int4 rather than int8. You are not kidding!!!! Changing this increased the search performance to 2083 transactions/second. This is 30 times faster than before, and 5 times faster than Oracle! Go Tom Lane!!! Unfortunately, the update accidentally already used the quoting, so this top did not directly help the write case. However, it did inspire me to check some other suggestions I have read since obviously performance was to be had. ---------------------------------------------------------------------- Oracle read performance: 395 Original read performance: 69 shared_buffer = 4096 118 + quoted where (WHERE val = '5') 2083 ---------------------------------------------------------------------- ---------------------------------------------------------------------- Oracle write performance: 314 Original write performance: 39 shared_buffer = 4096: 47 + Occassional (@ 10K & 60K vectors) vacuum analyze in bg: 121 + Periodic (every 10K vectors) vacuum analyze in background: 124 + wal_buffers = 24: 125 + wal_method = fdatasync 127 + wal_method = open_sync 248 + wal_method = open_datasync Not Supported + fsync=false: 793 ---------------------------------------------------------------------- Just to round out my report, using the fastest safe combination I was able to find (open_sync *is* safe, isn't it?), I reran all 7 performance tests to see if there was any different using the different access methods: ---------------------------------------------------------------------- "normal" C libpq 256 t/s "normal" Perl DBI 251 t/s "DBI Prepared Statement" Perl DBI 254 t/s "Batching" Perl DBI 1149 t/s "arrays" Perl DBI 43 t/s "server-side function" Perl DBI 84 t/s "server-side trigger" Perl DBI 84 t/s "normal" Perl DBI read 1960 t/s "normal" Perl DBI for Oracle 314 t/s "normal" Perl DBI read for Oracle 395 t/s ---------------------------------------------------------------------- With a batching update of 1149 transactions per second (2900% improvement), I am willing to call it a day unless anyone else has any brilliant ideas. However, it looks like my hope to use arrays is doomed though, I'm not sure I can handle the performance penalty. -Seth Robertson
pgsql-performance by date: