>>> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> We have found one area where jobs are running
> much longer and having a greater impact on concurrent jobs -- those
> where the programmer creates and drops many temporary tables
> (thousands) within a database transaction.
I forgot to include the standard information about the environment and
configuration.
ccsa(at)COUNTY2-PG:~> cat /proc/version
Linux version 2.6.16.60-0.31-smp (geeko(at)buildhost) (gcc version 4.1.2
20070115 (SUSE Linux)) #1 SMP Tue Oct 7 16:16:29 UTC 2008
ccsa(at)COUNTY2-PG:~> cat /etc/SuSE-release
SUSE Linux Enterprise Server 10 (x86_64)
VERSION = 10
PATCHLEVEL = 2
ccsa(at)COUNTY2-PG:~> uname -a
Linux COUNTY2-PG 2.6.16.60-0.31-smp #1 SMP Tue Oct 7 16:16:29 UTC 2008
x86_64 x86_64 x86_64 GNU/Linux
Two dual-core Xeon 3 GHz processors.
4 GB system RAM.
BBU RAID controller with 256 MB RAM.
RAID 5 on 5 spindles.
8.2.5:
ccsa(at)COUNTY2-PG:~> /usr/local/pgsql-8.2.5-64/bin/pg_config
BINDIR = /usr/local/pgsql-8.2.5-64/bin
DOCDIR = /usr/local/pgsql-8.2.5-64/doc
INCLUDEDIR = /usr/local/pgsql-8.2.5-64/include
PKGINCLUDEDIR = /usr/local/pgsql-8.2.5-64/include
INCLUDEDIR-SERVER = /usr/local/pgsql-8.2.5-64/include/server
LIBDIR = /usr/local/pgsql-8.2.5-64/lib
PKGLIBDIR = /usr/local/pgsql-8.2.5-64/lib
LOCALEDIR =
MANDIR = /usr/local/pgsql-8.2.5-64/man
SHAREDIR = /usr/local/pgsql-8.2.5-64/share
SYSCONFDIR = /usr/local/pgsql-8.2.5-64/etc
PGXS = /usr/local/pgsql-8.2.5-64/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/usr/local/pgsql-8.2.5-64'
'--enable-integer-datetimes' '--enable-debug' '--disable-nls'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g
CFLAGS_SL = -fpic
LDFLAGS = -Wl,-rpath,'/usr/local/pgsql-8.2.5-64/lib'
LDFLAGS_SL =
LIBS = -lpgport -lz -lreadline -lcrypt -ldl -lm
VERSION = PostgreSQL 8.2.5
max_connections = 50
shared_buffers = 256MB
temp_buffers = 10MB
max_prepared_transactions = 0
work_mem = 16MB
maintenance_work_mem = 400MB
max_fsm_pages = 1000000
bgwriter_lru_percent = 20.0
bgwriter_lru_maxpages = 200
bgwriter_all_percent = 10.0
bgwriter_all_maxpages = 600
wal_buffers = 256kB
checkpoint_segments = 50
archive_command = '/bin/true'
archive_timeout = 3600
seq_page_cost = 0.1
random_page_cost = 0.1
effective_cache_size = 3GB
geqo = off
from_collapse_limit = 20
join_collapse_limit = 20
redirect_stderr = on
log_line_prefix = '[%m] %p %q<%u %d %r> '
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 10
autovacuum_analyze_threshold = 10
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
escape_string_warning = off
sql_inheritance = off
standard_conforming_strings = on
8.3.4:
ccsa(at)COUNTY2-PG:~> /usr/local/pgsql-8.3.4-64/bin/pg_config
BINDIR = /usr/local/pgsql-8.3.4-64/bin
DOCDIR = /usr/local/pgsql-8.3.4-64/doc
INCLUDEDIR = /usr/local/pgsql-8.3.4-64/include
PKGINCLUDEDIR = /usr/local/pgsql-8.3.4-64/include
INCLUDEDIR-SERVER = /usr/local/pgsql-8.3.4-64/include/server
LIBDIR = /usr/local/pgsql-8.3.4-64/lib
PKGLIBDIR = /usr/local/pgsql-8.3.4-64/lib
LOCALEDIR =
MANDIR = /usr/local/pgsql-8.3.4-64/man
SHAREDIR = /usr/local/pgsql-8.3.4-64/share
SYSCONFDIR = /usr/local/pgsql-8.3.4-64/etc
PGXS = /usr/local/pgsql-8.3.4-64/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/usr/local/pgsql-8.3.4-64'
'--enable-integer-datetimes' '--enable-debug' '--disable-nls'
'--with-libxml'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing
-fwrapv -g
CFLAGS_SL = -fpic
LDFLAGS = -Wl,-rpath,'/usr/local/pgsql-8.3.4-64/lib'
LDFLAGS_SL =
LIBS = -lpgport -lxml2 -lz -lreadline -lcrypt -ldl -lm
VERSION = PostgreSQL 8.3.4
max_connections = 50
shared_buffers = 256MB
temp_buffers = 10MB
max_prepared_transactions = 0
work_mem = 16MB
maintenance_work_mem = 400MB
max_fsm_pages = 1000000
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4.0
wal_buffers = 256kB
checkpoint_segments = 50
archive_mode = on
archive_command = '/bin/true'
archive_timeout = 3600
seq_page_cost = 0.1
random_page_cost = 0.1
effective_cache_size = 3GB
geqo = off
from_collapse_limit = 20
join_collapse_limit = 20
logging_collector = on
log_checkpoints = on
log_connections = on
log_disconnections = on
log_line_prefix = '[%m] %p %q<%u %d %r> '
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 10
autovacuum_analyze_threshold = 10
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'
escape_string_warning = off
sql_inheritance = off
standard_conforming_strings = on