pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql Configutation and overflow
5+ messages / 5 participants
[nested] [flat]
* Postgresql Configutation and overflow
@ 2006-12-29 01:32 Fabricio Peñuelas <fabrixio1@hotmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Fabricio Peñuelas @ 2006-12-29 01:32 UTC (permalink / raw)
To: pgsql-performance
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.
_________________________________________________________________
¿ Ya tienes la última versión de Messenger?
www.imagine-msn.com/messenger/launch80/default.aspx?locale=es-mx Windows
Live Messenger en Prodigy/MSN
^ permalink raw reply [nested|flat] 5+ messages in thread
* Postgresql Configutation and overflow
@ 2006-12-29 01:58 fabrix peñuelas <fabrixio1@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: fabrix peñuelas @ 2006-12-29 01:58 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgresql Configutation and overflow
@ 2006-12-29 02:12 Adam Rich <adam.r@sbcglobal.net>
parent: fabrix peñuelas <fabrixio1@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Adam Rich @ 2006-12-29 02:12 UTC (permalink / raw)
To: 'fabrix peñuelas' <fabrixio1@gmail.com>; pgsql-performance
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-----
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
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgresql Configutation and overflow
@ 2006-12-29 03:35 Dave Cramer <pg@fastcrypt.com>
parent: fabrix peñuelas <fabrixio1@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Dave Cramer @ 2006-12-29 03:35 UTC (permalink / raw)
To: fabrix peñuelas <fabrixio1@gmail.com>; +Cc: pgsql-performance
Hi,
On 28-Dec-06, at 8:58 PM, fabrix peñuelas wrote:
> 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).
Why would you need 2000 connections ?
>
> 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.
start with 25% of your 12G as shared buffers, and 75% of 12G for
effective cache
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.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgresql Configutation and overflow
@ 2007-01-09 16:44 Jim C. Nasby <jim@nasby.net>
parent: Dave Cramer <pg@fastcrypt.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jim C. Nasby @ 2007-01-09 16:44 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: fabrix pe?uelas <fabrixio1@gmail.com>; pgsql-performance
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-01-09 16:44 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-29 01:32 Postgresql Configutation and overflow Fabricio Peñuelas <fabrixio1@hotmail.com>
2006-12-29 01:58 Postgresql Configutation and overflow fabrix peñuelas <fabrixio1@gmail.com>
2006-12-29 02:12 ` Re: Postgresql Configutation and overflow Adam Rich <adam.r@sbcglobal.net>
2006-12-29 03:35 ` Re: Postgresql Configutation and overflow Dave Cramer <pg@fastcrypt.com>
2007-01-09 16:44 ` Re: Postgresql Configutation and overflow Jim C. Nasby <jim@nasby.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox