pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-performance@postgresql.org
To: Kevin Grittner <Kgrittn.CCAP.Courts@wicourts.gov>
Subject: Re: Create and drop temp table in 8.3.4
Date: Wed, 05 Nov 2008 13:05:59 -0600
Message-ID: <49119A37.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <4911957A.EE98.0025.0@wicourts.gov>
References: <4911957A.EE98.0025.0@wicourts.gov>

>>> "Kevin Grittner" <Kevin.Grittner@wicourts.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@COUNTY2-PG:~> cat /proc/version
Linux version 2.6.16.60-0.31-smp (geeko@buildhost) (gcc version 4.1.2
20070115 (SUSE Linux)) #1 SMP Tue Oct 7 16:16:29 UTC 2008
ccsa@COUNTY2-PG:~> cat /etc/SuSE-release
SUSE Linux Enterprise Server 10 (x86_64)
VERSION = 10
PATCHLEVEL = 2
ccsa@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@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@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




view thread (17+ messages)  latest in thread

Message-ID: <49119A37.EE98.0025.0@wicourts.gov>
Permalink:  ../49119A37.EE98.0025.0@wicourts.gov/
Also on:    postgresql.org/message-id/49119A37.EE98.0025.0@wicourts.gov

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: Kevin.Grittner@wicourts.gov, Kgrittn.CCAP.Courts@wicourts.gov
  Subject: Re: Create and drop temp table in 8.3.4
  In-Reply-To: <49119A37.EE98.0025.0@wicourts.gov>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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