Thread: Postgresql Configutation and overflow
Good day,
I have been reading about the configuration of postgresql, but I have a server who does not give me the performance that should. The tables are indexed and made vacuum regularly, i monitor with top, ps and pg_stat_activity and when i checked was slow without a heavy load overage.
Before, the server reached 2000 connections to postgresql (with max_connections=3000 in it for future workflow).
I divided the load with another server for better performance, and now reach 500 connections, but yet is overflow.
My question is about how much memory should i configure in shared_buffers and effective_cache_size.
Features:
- 4 Processsors Intel Xeon Dual 3.0Ghz
- 12 GB RAM
- 2 discos en RAID 1 for OS
- 4 discs RAID 5 for DB
- S.O Slackware 11.0 Linux 2.6.17.7
- Postgres 8.1.4
=====In internet i found this:
Tuning PostgreSQL for performance
2 Some basic parameters
2.1 Shared buffers
# Start at 4MB (512) for a workstation
# Medium size data set and 256-512MB available RAM: 16-32MB (2048-4096)
# Large dataset and lots of available RAM (1-4GB): 64-256MB (8192-32768)
======
My postgresql.conf configuration is:
#---------------------------------------------------------------------------
# FILE LOCATIONS
#---------------------------------------------------------------------------
# The default values of these variables are driven from the -D command line
# switch or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
#ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file
# If external_pid_file is not explicitly set, no extra pid file is written.
#external_pid_file = '(none)' # write an extra pid file
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
port = 5432
max_connections = 3000
# note: increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction). You
# might also need to raise shared_buffers to support more connections.
#superuser_reserved_connections = 2
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#bonjour_name = '' # defaults to the computer name
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 81920 # min 16 or max_connections*2, 8KB each
temp_buffers = 5000 # min 100, 8KB each
max_prepared_transactions = 1000 # can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 10240 # min 64, size in KB
maintenance_work_mem = 253952 # min 1024, size in KB
max_stack_depth = 4096 # min 100, size in KB
# - Free Space Map -
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~70 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
#preload_libraries = ''
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 0-10000 credits
# - Background writer -
#bgwriter_delay = 200 # 10-10000 milliseconds between rounds
#bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
#fsync = on # turns forced synchronization on or off
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_buffers = 8 # min 4, 8KB each
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
checkpoint_segments = 20 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # in seconds, 0 is off
# - Archiving -
#archive_command = '' # command to use to archive a logfile
# segment
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
enable_nestloop = off
enable_seqscan = off
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
effective_cache_size = 65536 # typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch
# cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)
the sysctl.conf
kernel.shmmax = 970170573
kernel.shmall = 970170573
kernel.sem = 400 42000 32 1024
vm.overcommit_memory = 2
=========The configuration is correct?=======
If you can help me i will be pleased, thanks.
I have been reading about the configuration of postgresql, but I have a server who does not give me the performance that should. The tables are indexed and made vacuum regularly, i monitor with top, ps and pg_stat_activity and when i checked was slow without a heavy load overage.
Before, the server reached 2000 connections to postgresql (with max_connections=3000 in it for future workflow).
I divided the load with another server for better performance, and now reach 500 connections, but yet is overflow.
My question is about how much memory should i configure in shared_buffers and effective_cache_size.
Features:
- 4 Processsors Intel Xeon Dual 3.0Ghz
- 12 GB RAM
- 2 discos en RAID 1 for OS
- 4 discs RAID 5 for DB
- S.O Slackware 11.0 Linux 2.6.17.7
- Postgres 8.1.4
=====In internet i found this:
Tuning PostgreSQL for performance
2 Some basic parameters
2.1 Shared buffers
# Start at 4MB (512) for a workstation
# Medium size data set and 256-512MB available RAM: 16-32MB (2048-4096)
# Large dataset and lots of available RAM (1-4GB): 64-256MB (8192-32768)
======
My postgresql.conf configuration is:
#---------------------------------------------------------------------------
# FILE LOCATIONS
#---------------------------------------------------------------------------
# The default values of these variables are driven from the -D command line
# switch or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
#ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file
# If external_pid_file is not explicitly set, no extra pid file is written.
#external_pid_file = '(none)' # write an extra pid file
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
port = 5432
max_connections = 3000
# note: increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction). You
# might also need to raise shared_buffers to support more connections.
#superuser_reserved_connections = 2
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#bonjour_name = '' # defaults to the computer name
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 81920 # min 16 or max_connections*2, 8KB each
temp_buffers = 5000 # min 100, 8KB each
max_prepared_transactions = 1000 # can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 10240 # min 64, size in KB
maintenance_work_mem = 253952 # min 1024, size in KB
max_stack_depth = 4096 # min 100, size in KB
# - Free Space Map -
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~70 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
#preload_libraries = ''
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 0-10000 credits
# - Background writer -
#bgwriter_delay = 200 # 10-10000 milliseconds between rounds
#bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
#fsync = on # turns forced synchronization on or off
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_buffers = 8 # min 4, 8KB each
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
checkpoint_segments = 20 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # in seconds, 0 is off
# - Archiving -
#archive_command = '' # command to use to archive a logfile
# segment
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
enable_nestloop = off
enable_seqscan = off
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
effective_cache_size = 65536 # typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch
# cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)
the sysctl.conf
kernel.shmmax = 970170573
kernel.shmall = 970170573
kernel.sem = 400 42000 32 1024
vm.overcommit_memory = 2
=========The configuration is correct?=======
If you can help me i will be pleased, thanks.
What are your table sizes? What are your queries like? (Mostly read, mostly write?)
Can you post the "analyze" output for some of the slow queries?
The three things that stand out for me is your disk configuration (RAID 5 is not ideal for databases,
you really want RAID 1 or 1+0) and also that you have enable_seqscan set to off. I would leave
that turned on. Lastly, your effective_cache_size looks low. Your OS is probably caching more
than 512 MB, I know mine is usually 1-2 GB and I don't have 12 GB of ram available.
-----Original Message-----Good day,
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of fabrix peñuelas
Sent: Thursday, December 28, 2006 7:58 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Postgresql Configutation and overflow
I have been reading about the configuration of postgresql, but I have a server who does not give me the performance that should. The tables are indexed and made vacuum regularly, i monitor with top, ps and pg_stat_activity and when i checked was slow without a heavy load overage.
Before, the server reached 2000 connections to postgresql (with max_connections=3000 in it for future workflow).
I divided the load with another server for better performance, and now reach 500 connections, but yet is overflow.
My question is about how much memory should i configure in shared_buffers and effective_cache_size.
Features:
- 4 Processsors Intel Xeon Dual 3.0Ghz
- 12 GB RAM
- 2 discos en RAID 1 for OS
- 4 discs RAID 5 for DB
- S.O Slackware 11.0 Linux 2.6.17.7
- Postgres 8.1.4
=====In internet i found this:
Tuning PostgreSQL for performance
2 Some basic parameters
2.1 Shared buffers
# Start at 4MB (512) for a workstation
# Medium size data set and 256-512MB available RAM: 16-32MB (2048-4096)
# Large dataset and lots of available RAM (1-4GB): 64-256MB (8192-32768)
======
My postgresql.conf configuration is:
#---------------------------------------------------------------------------
# FILE LOCATIONS
#---------------------------------------------------------------------------
# The default values of these variables are driven from the -D command line
# switch or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
#ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file
# If external_pid_file is not explicitly set, no extra pid file is written.
#external_pid_file = '(none)' # write an extra pid file
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
port = 5432
max_connections = 3000
# note: increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction). You
# might also need to raise shared_buffers to support more connections.
#superuser_reserved_connections = 2
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#bonjour_name = '' # defaults to the computer name
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 81920 # min 16 or max_connections*2, 8KB each
temp_buffers = 5000 # min 100, 8KB each
max_prepared_transactions = 1000 # can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 10240 # min 64, size in KB
maintenance_work_mem = 253952 # min 1024, size in KB
max_stack_depth = 4096 # min 100, size in KB
# - Free Space Map -
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~70 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
#preload_libraries = ''
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 0-10000 credits
# - Background writer -
#bgwriter_delay = 200 # 10-10000 milliseconds between rounds
#bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
#fsync = on # turns forced synchronization on or off
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_buffers = 8 # min 4, 8KB each
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
checkpoint_segments = 20 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # in seconds, 0 is off
# - Archiving -
#archive_command = '' # command to use to archive a logfile
# segment
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
enable_nestloop = off
enable_seqscan = off
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
effective_cache_size = 65536 # typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch
# cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)
the sysctl.conf
kernel.shmmax = 970170573
kernel.shmall = 970170573
kernel.sem = 400 42000 32 1024
vm.overcommit_memory = 2
=========The configuration is correct?=======
If you can help me i will be pleased, thanks.
Hi,
start with 25% of your 12G as shared buffers, and 75% of 12G for effective cache
On 28-Dec-06, at 8:58 PM, fabrix peñuelas wrote:
Good day,Why would you need 2000 connections ?
I have been reading about the configuration of postgresql, but I have a server who does not give me the performance that should. The tables are indexed and made vacuum regularly, i monitor with top, ps and pg_stat_activity and when i checked was slow without a heavy load overage.
Before, the server reached 2000 connections to postgresql (with max_connections=3000 in it for future workflow).
I divided the load with another server for better performance, and now reach 500 connections, but yet is overflow.
My question is about how much memory should i configure in shared_buffers and effective_cache_size.
You can go higher for shared buffers, but only do so with testing.
Dave
Features:
- 4 Processsors Intel Xeon Dual 3.0Ghz
- 12 GB RAM
- 2 discos en RAID 1 for OS
- 4 discs RAID 5 for DB
- S.O Slackware 11.0 Linux 2.6.17.7
- Postgres 8.1.4
=====In internet i found this:
Tuning PostgreSQL for performance
2 Some basic parameters
2.1 Shared buffers
# Start at 4MB (512) for a workstation
# Medium size data set and 256-512MB available RAM: 16-32MB (2048-4096)
# Large dataset and lots of available RAM (1-4GB): 64-256MB (8192-32768)
======
My postgresql.conf configuration is:
#---------------------------------------------------------------------------
# FILE LOCATIONS
#---------------------------------------------------------------------------
# The default values of these variables are driven from the -D command line
# switch or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
#ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file
# If external_pid_file is not explicitly set, no extra pid file is written.
#external_pid_file = '(none)' # write an extra pid file
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
port = 5432
max_connections = 3000
# note: increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction). You
# might also need to raise shared_buffers to support more connections.
#superuser_reserved_connections = 2
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#bonjour_name = '' # defaults to the computer name
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 81920 # min 16 or max_connections*2, 8KB each
temp_buffers = 5000 # min 100, 8KB each
max_prepared_transactions = 1000 # can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 10240 # min 64, size in KB
maintenance_work_mem = 253952 # min 1024, size in KB
max_stack_depth = 4096 # min 100, size in KB
# - Free Space Map -
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~70 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
#preload_libraries = ''
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 0-10000 credits
# - Background writer -
#bgwriter_delay = 200 # 10-10000 milliseconds between rounds
#bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
#fsync = on # turns forced synchronization on or off
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_buffers = 8 # min 4, 8KB each
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
checkpoint_segments = 20 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # in seconds, 0 is off
# - Archiving -
#archive_command = '' # command to use to archive a logfile
# segment
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
enable_nestloop = off
enable_seqscan = off
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
effective_cache_size = 65536 # typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch
# cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)
the sysctl.conf
kernel.shmmax = 970170573
kernel.shmall = 970170573
kernel.sem = 400 42000 32 1024
vm.overcommit_memory = 2
=========The configuration is correct?=======
If you can help me i will be pleased, thanks.
On Thu, Dec 28, 2006 at 10:35:29PM -0500, Dave Cramer wrote: > start with 25% of your 12G as shared buffers, and 75% of 12G for > effective cache I'm curious... why leave 3G for the kernel? Seems like overkill... Granted, as long as you're in the ballpark on effective_cache_size that's all that matters... > You can go higher for shared buffers, but only do so with testing. > > Dave > > > >Features: > > > >- 4 Processsors Intel Xeon Dual 3.0Ghz > >- 12 GB RAM > >- 2 discos en RAID 1 for OS > >- 4 discs RAID 5 for DB > >- S.O Slackware 11.0 Linux 2.6.17.7 > >- Postgres 8.1.4 > > > > > >=====In internet i found this: > > > >Tuning PostgreSQL for performance > >2 Some basic parameters > >2.1 Shared buffers > > > ># Start at 4MB (512) for a workstation > ># Medium size data set and 256-512MB available RAM: 16-32MB > >(2048-4096) > ># Large dataset and lots of available RAM (1-4GB): 64-256MB > >(8192-32768) > >====== > > > > > >My postgresql.conf configuration is: > > > >#--------------------------------------------------------------------- > >------ > ># FILE LOCATIONS > >#--------------------------------------------------------------------- > >------ > > > ># The default values of these variables are driven from the -D > >command line > ># switch or PGDATA environment variable, represented here as > >ConfigDir. > > > >#data_directory = 'ConfigDir' # use data in another directory > >#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication > >file > >#ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file > > > ># If external_pid_file is not explicitly set, no extra pid file is > >written. > >#external_pid_file = '(none)' # write an extra pid file > > > > > >#--------------------------------------------------------------------- > >------ > ># CONNECTIONS AND AUTHENTICATION > >#--------------------------------------------------------------------- > >------ > > > ># - Connection Settings - > > > >listen_addresses = '*' # what IP address(es) to listen on; > > # comma-separated list of addresses; > > # defaults to 'localhost', '*' = all > >port = 5432 > >max_connections = 3000 > ># note: increasing max_connections costs ~400 bytes of shared > >memory per > ># connection slot, plus lock space (see > >max_locks_per_transaction). You > ># might also need to raise shared_buffers to support more connections. > >#superuser_reserved_connections = 2 > >#unix_socket_directory = '' > >#unix_socket_group = '' > >#unix_socket_permissions = 0777 # octal > >#bonjour_name = '' # defaults to the computer name > > > > > > > >#--------------------------------------------------------------------- > >------ > ># RESOURCE USAGE (except WAL) > >#--------------------------------------------------------------------- > >------ > > > ># - Memory - > > > > > >shared_buffers = 81920 # min 16 or max_connections*2, > >8KB each > >temp_buffers = 5000 # min 100, 8KB each > >max_prepared_transactions = 1000 # can be 0 or more > > > ># note: increasing max_prepared_transactions costs ~600 bytes of > >shared memory > > > ># per transaction slot, plus lock space (see > >max_locks_per_transaction). > >work_mem = 10240 # min 64, size in KB > >maintenance_work_mem = 253952 # min 1024, size in KB > >max_stack_depth = 4096 # min 100, size in KB > > > ># - Free Space Map - > > > >#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 > >bytes each > >#max_fsm_relations = 1000 # min 100, ~70 bytes each > > > ># - Kernel Resource Usage - > > > >#max_files_per_process = 1000 # min 25 > >#preload_libraries = '' > > > ># - Cost-Based Vacuum Delay - > > > >#vacuum_cost_delay = 0 # 0-1000 milliseconds > >#vacuum_cost_page_hit = 1 # 0-10000 credits > >#vacuum_cost_page_miss = 10 # 0-10000 credits > >#vacuum_cost_page_dirty = 20 # 0-10000 credits > >#vacuum_cost_limit = 200 # 0-10000 credits > > > ># - Background writer - > > > >#bgwriter_delay = 200 # 10-10000 milliseconds between > >rounds > >#bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/ > >round > >#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round > >#bgwriter_all_percent = 0.333 # 0-100% of all buffers > >scanned/round > >#bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round > > > > > >#--------------------------------------------------------------------- > >------ > ># WRITE AHEAD LOG > >#--------------------------------------------------------------------- > >------ > > > ># - Settings - > > > >#fsync = on # turns forced synchronization on or off > >#wal_sync_method = fsync # the default is the first option > > # supported by the operating system: > > # open_datasync > > # fdatasync > > # fsync > > # fsync_writethrough > > # open_sync > >#full_page_writes = on # recover from partial page writes > >#wal_buffers = 8 # min 4, 8KB each > >#commit_delay = 0 # range 0-100000, in microseconds > >#commit_siblings = 5 # range 1-1000 > > > ># - Checkpoints - > > > >checkpoint_segments = 20 # in logfile segments, min 1, 16MB > >each > >#checkpoint_timeout = 300 # range 30-3600, in seconds > >#checkpoint_warning = 30 # in seconds, 0 is off > > > ># - Archiving - > > > >#archive_command = '' # command to use to archive a logfile > > # segment > > > > > >#--------------------------------------------------------------------- > >------ > ># QUERY TUNING > >#--------------------------------------------------------------------- > >------ > > > ># - Planner Method Configuration - > > > >#enable_bitmapscan = on > >#enable_hashagg = on > >#enable_hashjoin = on > >#enable_indexscan = on > >#enable_mergejoin = on > >enable_nestloop = off > >enable_seqscan = off > >#enable_sort = on > >#enable_tidscan = on > > > ># - Planner Cost Constants - > > > >effective_cache_size = 65536 # typically 8KB each > >#random_page_cost = 4 # units are one sequential page fetch > > # cost > >#cpu_tuple_cost = 0.01 # (same) > >#cpu_index_tuple_cost = 0.001 # (same) > >#cpu_operator_cost = 0.0025 # (same) > > > > > >the sysctl.conf > > > >kernel.shmmax = 970170573 > >kernel.shmall = 970170573 > >kernel.sem = 400 42000 32 1024 > >vm.overcommit_memory = 2 > > > >=========The configuration is correct?======= > > > >If you can help me i will be pleased, thanks. > > > -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)