> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Thom Brown > Sent: Tuesday, November 04, 2008 1:45 AM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: Best memory/planner settings for Postgres > > 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. http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server http://www.powerpostgresql.com/Downloads/annotated_conf_80.html http://www.powerpostgresql.com/download/TFCKUpload/5.x-pdf http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.h tml http://www.scribd.com/doc/4846381/PostgreSQL-Performance-Tuning -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
