Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 075C165047D for ; Wed, 5 Nov 2008 15:06:19 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 46187-01 for ; Wed, 5 Nov 2008 15:06:18 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id D34B265044E for ; Wed, 5 Nov 2008 15:06:06 -0400 (AST) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Wed, 05 Nov 2008 13:06:05 -0600 Message-Id: <49119A37.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.3 Date: Wed, 05 Nov 2008 13:05:59 -0600 From: "Kevin Grittner" To: , "Kevin Grittner" Subject: Re: Create and drop temp table in 8.3.4 References: <4911957A.EE98.0025.0@wicourts.gov> In-Reply-To: <4911957A.EE98.0025.0@wicourts.gov> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Archive-Number: 200811/12 X-Sequence-Number: 31743 >>> "Kevin Grittner" 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