On Thu, 2003-12-04 at 09:12, Rob Fielding wrote:
> >
> > I've tweaked shared buffers to 8192, pushed sort memory to 2048, vacuum
> > memory to 8192, and effective cache size to 10000.
> > /proc/sys/kernel/shmmax is set to 1600000000 and /proc/sys/fs/file-max
> > is set to 65536. Ulimit -n 3192.
>
> Your sharedmemory is too high, and not even being used effectivey. Your
> other settings are too low.
>
> Ball park guessing here, but I'd say first read (and understand) this:
>
> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
I've read it many times, understanding is slower :-)
>
> Then make shared memory about 10-20% available ram, and set:
>
> ((shmmax/1024) - ( 14.2 * max_connections ) - 250 ) / 8.2 = shared_buffers
>
> decrease random_page_cost to 0.3 and wack up sort mem by 16 times,
> effective cache size to about 50% RAM (depending on your other settings)
> and try that for starters.
Following this, I've done:
2gb ram
=
2,000,000,000
bytes
15 % of that
=
300,000,000
bytes
divided by
1024
=
292,969
kbytes
max_conn *
14.2
=
454
kbytes
subtract c4
=
292,514
kbytes
subtract 250
=
292,264
kbytes
divide by 8.2
=
35,642
shared_buffers
performance is unchanged for the 18M job -- pg continues to use ~
285-300M, system load and memory usage stay the same. I killed that,
deleted from the affected tables, inserted a 6M job, and started a
vacuumdb --anaylze. It's been running for 20 minutes now...
getting the SQL query better optimized for PG is on my todo list, but
not something I can do right now -- this application is designed to be
cross-platform with MS-SQL, PG, and Oracle so tweaking SQL is a touchy
subject.
The pgavd conversation is intriguing, but I don't really understand the
role of vacuuming. Would this be a correct statement: "PG needs to
regularly re-evaluate the database in order to adjust itself?" I'm
imagining that it continues to treat the table as a small one until
vacuum informs it that the table is now large?
--
Jack Coates, Lyris Technologies Applications Engineer
510-549-4350 x148, jack@lyris.com
"Interoperability is the keyword, uniformity is a dead end."
--Olivier Fourdan