Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id EE26963347F for ; Wed, 11 Mar 2009 20:59:10 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 31287-08 for ; Wed, 11 Mar 2009 20:59:02 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f170.google.com (mail-bw0-f170.google.com [209.85.218.170]) by mail.postgresql.org (Postfix) with ESMTP id BC95E63316F for ; Wed, 11 Mar 2009 20:59:08 -0300 (ADT) Received: by bwz18 with SMTP id 18so303712bwz.19 for ; Wed, 11 Mar 2009 16:59:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=8tmq3lEa2bJ04W/XlGe68IOxZbTMiTiD/HBXBCvSt7g=; b=ismwnYSe/9UA2o0c0h/NeCHV2Uafx2S3pzIQXjlnlPSTVqfeIKw8j1jNFgfsHKTuSJ oE43YEZXxTQjd3C2ENMpU5AuNprdHFm+f85s9AQra4aQxB+Wv39ULkb2ABJh92ky47Gi P2ylDXFwV2zrKNYqjozXt2UzN44LXM2fHqta0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=cArXus5hhJfuwWM4W73v3gxUbOKoollSr8vcN8aL6JtmYyIkMWDrTeIlxGEUFxEb0P 4DhlAsPrqBr/2XRm8iSDgL35Oam88Xolvzv+SafkqqZSOzgjz47qBzofNjgDoOxjREhV JqbJVCp+lRsf+q5OC5KhR2hC4mvPDL73ircI8= MIME-Version: 1.0 Received: by 10.223.113.68 with SMTP id z4mr6872466fap.72.1236815946645; Wed, 11 Mar 2009 16:59:06 -0700 (PDT) In-Reply-To: <7d10d2df0903111227g2e40e742g5992cba5158dd4d2@mail.gmail.com> References: <7d10d2df0903111227g2e40e742g5992cba5158dd4d2@mail.gmail.com> Date: Thu, 12 Mar 2009 00:59:06 +0100 Message-ID: <1d4e0c10903111659x6c96e6cau33ee121ee29a48a@mail.gmail.com> Subject: Re: Full statement logging problematic on larger machines? From: Guillaume Smet To: Frank Joerdens Cc: pgsql-performance@postgresql.org, Nic Ferrier , Avleen Vig , Mike Rogers , Andrew Dunstan Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200903/108 X-Sequence-Number: 33030 On Wed, Mar 11, 2009 at 8:27 PM, Frank Joerdens wrote: > This works much better but once we are at about 80% of peak load - > which is around 8000 transactions per second currently - the server goes > into a tailspin in the manner described above and we have to switch off full > logging. First, don't use log_duration = on + log_statement = 'all' to log all the queries, use log_min_duration_statement=0, it's less verbose. I don't know if the logging integrated into PostgreSQL can bufferize its output. Andrew? If not, you should try syslog instead and see if asynchronous logging with syslog is helping (you need to prefix the path with a dash to enable asynchronous logging). You can also try to send the logs on the network via udp (and also tcp if you have an enhanced syslog-like). Another option is to log the duration of every query but not the text. We used to have this sort of configuration to gather comprehensive statistics and slowest queries on highly loaded servers (it's not perfect though but it can be an acceptable compromise): log_duration = on log_min_duration_statement = 100 -- Guillaume