Re: Full statement logging problematic on larger machines?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Mar 11, 2009 at 8:46 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> Frank Joerdens <frank@xxxxxxxxxxx> 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.  Are you just writing to stderr?
> syslog?  using PG's built-in log capture process?  It 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 = '/var/lib/postgresql/8.2/main'
hba_file = '/etc/postgresql/8.2/main/pg_hba.conf'
ident_file = '/etc/postgresql/8.2/main/pg_ident.conf'

listen_addresses                = 'localhost,172.16.222.62'
port                            = 5432

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


autovacuum                      = on
stats_start_collector           = on
stats_row_level                 = on


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

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

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

redirect_stderr                 = on
log_directory                   = '/dev/shm/'
log_rotation_age                = 0
log_rotation_size               = 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 = on and log_statement = '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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux