Обсуждение: 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. Any help would be appreciated. Thanks Thom
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Thom Brown > Sent: Tuesday, November 04, 2008 1:45 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] 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