Kenji Morishige <kenjim@juniper.net> 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
