Micheal, > I changed the postgres.conf settings as suggested by several people. I've > attached it to this email, please let me know if you see anything else I > can tweak. top still says I have plenty of ram, so should I increase the > buffers and/or effective_cache even more?
Effective cache, yes. Buffers, no. Even if you have RAM available, increasing buffers beyond an optimal but hard to locate point decreases performance. I'd advise you to start playing with buffers only after you are done playing with other memory-eating params. I would suggest, though, increasing FSM_relations even more, until your daily VACUUM FULL does almost no work. This will improve index usage and speed queries. > I moved the WAL (pg_xlog directory) to another drive. There are two drives > in the system, so one has the OS, servers, all files, and the WAL and the > other has nothing but the data. I think it'd be best to put the WAL on a > separate drive from the OS but I don't know if I can get another drive > added just for that due to our limited budget. A high-speed IDE drive might be adequate for WAL, except that Linux has booting issues with a mix of IDE & SCSI and many motherboards. > I learned that I only need to vacuum tables that are changed frequently. > My app doesn't do any deletes, and only one table changes, the monitor > table. several times a second. So I only need to vacuum that table. > Vacuuming the entire database is slow and unecessary. If I only do the > monitor table, it takes only a few seconds. Much better than the 35 > minutes for the entire database that it was taking this morning. Increasing FSM_relations will also make vacuums more efficient. > I want to thank everyone for their input. I've heard Postgres is slow and > doesn't scale, but now I do it's really just a matter of learning to > configure it properly and trial & error. I do think the documentation > could be enhanced a bit here, but I'm sure there are some users who don't Absolutely. I'm working on it. Look to Techdocs next week. -- Josh Berkus Aglio Database Solutions San Francisco ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html