DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:sender:in-reply-to:references:date
	:x-google-sender-auth:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	b=YuJcoN41QkVkq8z005sWEM7dCScXfvpJZldXNFTbBy5tPYfsWWk27LFOtZQibQOp7y
	qFJ4APPCn1hrdBQoyAF/jldliC4QzvxTk1vn0pa19uk5x6Z+VVEYX2euEfN0acQ1elSf
	tVOOpUZ5uUeHV2PBXnu6BpB92TfrfU/KRfpC8=
MIME-Version: 1.0
In-Reply-To: <3401.1236804370@sss.pgh.pa.us>
References: <7d10d2df0903111227g2e40e742g5992cba5158dd4d2@mail.gmail.com>
	<3401.1236804370@sss.pgh.pa.us>
Date: Wed, 11 Mar 2009 22:42:29 +0000
Message-ID: <7d10d2df0903111542o53abb3dbvfc405a597e4c9fa8@mail.gmail.com>
Subject: Re: Full statement logging problematic on larger machines?
From: Frank Joerdens <frank@joerdens.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org, Nic Ferrier <nic@woome.com>,
	Avleen Vig <avleen@woome.com>, Mike Rogers <mike.rogers@woome.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Wed, Mar 11, 2009 at 8:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Frank Joerdens <frank@joerdens.de> writes:
>> Greetings. We're having trouble with full logging since we moved from
>> an 8-core server with 16 GB memory to a machine with double that
>> spec and I am wondering if this *should* be working or if there is a
>> point on larger machines where logging and scheduling seeks of
>> background writes - or something along those lines; it might be a
>> theory - doesn't work together any more?
>
> You didn't tell us anything interesting about *how* you are logging,
> so it's hard to comment on this. =A0Are you just writing to stderr?
> syslog? =A0using PG's built-in log capture process? =A0It would be good
> to show all of your log-related postgresql.conf settings.

Here's the complete postgresql.conf (I've whittled it down as much as
I could so it's quite compact):

frank@db04:~$ cat /etc/postgresql/8.2/main/postgresql.conf
data_directory =3D '/var/lib/postgresql/8.2/main'
hba_file =3D '/etc/postgresql/8.2/main/pg_hba.conf'
ident_file =3D '/etc/postgresql/8.2/main/pg_ident.conf'

listen_addresses                =3D 'localhost,172.16.222.62'
port                            =3D 5432

max_connections                 =3D 1000
shared_buffers                  =3D 16GB
work_mem                        =3D 200MB
maintenance_work_mem            =3D 1GB
max_fsm_pages                   =3D 50000
wal_buffers                     =3D 8MB
checkpoint_segments             =3D 16


autovacuum                      =3D on
stats_start_collector           =3D on
stats_row_level                 =3D on


effective_cache_size            =3D 4GB
default_statistics_target       =3D 10
constraint_exclusion            =3D off
checkpoint_warning              =3D 1h
escape_string_warning           =3D off

log_duration                    =3D off
log_min_duration_statement      =3D 1000
log_statement                   =3D 'ddl'
log_line_prefix                 =3D '%m %p %h %u '

archive_command =3D '/usr/bin/walmgr.py
/var/lib/postgresql/walshipping/master.ini xarchive %p %f'

redirect_stderr                 =3D on
log_directory                   =3D '/dev/shm/'
log_rotation_age                =3D 0
log_rotation_size               =3D 0

The above is what we're doing right now, only logging queries that run
for over a second, and that is no problem; so the answer to Scott's
question in his reply to my posting is: Yes, logging only the slower
queries does work.

Yesterday I changed log_duration =3D on and log_statement =3D 'all' at
off-peak time and left it on for 4 hours while traffic was picking up.
Eventually I had to stop it because the server got bogged down.

Regards,

Frank