Kenji Morishige <kenjim@xxxxxxxxxxx> writes: > ... We generally have somewhere between 150-200 connections to > the database at any given time and probably anywhere between 5-10 new > connections being made every second and about 100 queries per second. Most > of the queries and transactions are very small due to the fact that the tools > were designed to work around the small functionality of MySQL 3.23 DB. You should think seriously about putting in some sort of connection-pooling facility. Postgres backends aren't especially lightweight things; the overhead involved in forking a process and then getting its internal caches populated etc. is significant. You don't want to be doing that for one small query, at least not if you're doing so many times a second. > it seems as if the database is not making use of the available ram. Postgres generally relies on the kernel to do the bulk of the disk caching. Your shared_buffers setting of 30000 seems quite reasonable to me; I don't think you want to bump it up (not much anyway). I'm not too familiar with FreeBSD and so I'm not clear on what "Inact" is: > Mem: 181M Active, 2632M Inact, 329M Wired, 179M Cache, 199M Buf, 81M Free > Swap: 4096M Total, 216K Used, 4096M Free If "Inact" covers disk pages cached by the kernel then this is looking reasonably good. If it's something else then you got a problem, but fixing it is a kernel issue not a database issue. > #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each You almost certainly need to bump this way up. 20000 is enough to cover dirty pages in about 200MB of database, which is only a fiftieth of what you say your disk footprint is. Unless most of your data is static, you're going to be suffering severe table bloat over time due to inability to recycle free space properly. regards, tom lane
