We've got a dedicated database server running PostgresSQL 8.0.9 (yes,
I know it needs upgrading), but I've noticed it looks criminally
under-configured.
Basically it's running on a server with 2 dual-core Intel Xeon 2.33
Ghz processors and 4Gb memory, but has the following settings in
postgresql.conf
shared_buffers = 1000
work_mem = 1024
effective_cache_size = 2500
default_statistics_target = 100
Maybe other settings should be the subject of focus too. The type of
data we have uses quite extensive use of IN lists (e.g. WHERE
target_id IN (3423452, 65465, 6523, 2436, 26464, 2646464, 4, 2644624,
264642, 5344342, 65746735, 25332, 6435375, 251353, 3573573, 357363634,
252523523, 235235235, 5688282, 28647532564, 452525, 335745, 376357357,
375757357, 3573735735)
That's just an example as lists can often be a longer than that, and
I've noticed it doesn't appear to be using the index on the column
being queried. Queries such as that are used very frequently. We
also make at least a couple joins on most queries and often use
DISTINCT.
Has anyone got recommendations on what the config settings should be
set to? And also any other settings I have neglected to highlight?
I feels like PostgreSQL is the only resident in a mansion, but is
locked in a room on the ground floor.
Any help would be appreciated.
Thanks
Thom