Hi. I had tweaked my PG 8.2.6 with the very kind help of this list a couple years ago. It has been working fine, until recently. Not sure if it is after the update to 8.3 or because my DB has been growing, but the db is very slow now and the cache doesn't seem enough. ~ > free -m total used free shared buffers cached Mem: 4051 4033 18 0 6 2576 -/+ buffers/cache: 1450 2601 Swap: 2047 43 2003 Some of the SQL queries that were super fast (indexed with LIMIT 1!) are now slow too. What is a good starting point for me apart from checking the slow SQL? Because almost every SQL is now slow. I can restart the PG server and it is fast for a little while after that but then the buffer fills up I think. It's a CentOS server, Pentium Core2Duo dual processor, 6MB RAM. Same server runs Apache (low mem consumption), MySQL (for really small web stuff, not much load) and PGSQL (huge load). I can devote a lot of memory to PG, no problem. Autovacuum is on but I also manually vacuum the big tables by crontab -- per hour. This one is not a huge DB, about 5GB right now. The tables are as such: relname | rowcnt | inserted | updated | deleted ---------------+---------+----------+---------+--------- books | 8622136 | 1852965 | 938229 | 16304 checkout_count| 261317 | 9834 | 116664 | 1225 subscribers | 10180 | 1267 | 79623 | 0 interesting | 4196 | 53 | 54774 | 0 pg_statistic | 411 | 0 | 43104 | 0 books_deleted | 896 | 16350 | 0 | 11473 users | 62865 | 2428 | 2493 | 0 pg_attribute | 1844 | 1322 | 575 | 1321 (8 rows) Below are my CONF settings: listen_addresses = 'localhost,*' max_connections = 300 shared_buffers = 330MB effective_cache_size = 512000 max_fsm_relations = 100 max_fsm_pages = 300000 work_mem = 20MB temp_buffers = 4096 authentication_timeout = 10s ssl = off checkpoint_warning = 3600 random_page_cost = 1 autovacuum = on autovacuum_vacuum_cost_delay = 20 vacuum_cost_delay = 20 autovacuum_naptime = 10 stats_start_collector = on stats_row_level = on autovacuum_vacuum_threshold = 75 autovacuum_analyze_threshold = 25 autovacuum_analyze_scale_factor = 0.02 autovacuum_vacuum_scale_factor = 0.01 Any pointers or advice MUCH appreciated! THANKS. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
