currently PG 8.1.3. See attached for my postgresql.conf. Server is freebsd 6.2 w/ a fast 3TB storage array and only 2GB of ram. We're running RTG which is a like mrtg, cricket, etc. basically queries network devices via SNMP, throws stats into the DB for making pretty bandwidth graphs. We've got hundreds of devices, with 10K+ ports and probably 100K's of stats being queried every 5 minutes. In order to do all that work, the back end SNMP querier is multi-threaded and opens a PG connection per-thread. We're running 30 threads. This is basically all INSERTS, but only ends up to being about 32,000/5 minutes. The graphing front end CGI is all SELECT. There's 12k tables today, and new tables are created each month. The number of rows per table is 100-700k, with most in the 600-700K range. 190GB of data so far. Good news is that queries have no joins and are limited to only a few tables at a time. Basically, each connection is taking about 100MB resident. As we need to increase the number of threads to be able to query all the devices in the 5 minute window, we're running out of memory. There aren't that many CGI connections at anyone one time, but obviously query performance isn't great, but honestly is surprisingly good all things considered. Honestly, not looking to improve PG's performance, really although I wouldn't complain. Just better manage memory/hardware. I assume I can't start up two instances of PG pointing at the same files, one read-only and one read-write with different memory profiles, so I assume my only real option is throw more RAM at it. I don't have $$$ for another array/server for a master/slave right now. Or perhaps tweaking my .conf file? Are newer PG versions more memory efficient? Thanks, Aaron -- Aaron Turner http://synfin.net/ ; Twitter: @synfinatic http://tcpreplay.synfin.net/ - Pcap editing and replay tools for Unix & Windows Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. -- Benjamin Franklin "carpe diem quam minimum credula postero"
Attachment:
postgresql.conf
Description: Binary data
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
