X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A54E73A41E1 for ; Thu, 6 Jan 2005 15:17:57 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 98331-05 for ; Thu, 6 Jan 2005 15:17:27 +0000 (GMT) Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by svr1.postgresql.org (Postfix) with SMTP id 5BD943A42E4 for ; Thu, 6 Jan 2005 15:17:30 +0000 (GMT) Received: (qmail 30628 invoked from network); 6 Jan 2005 15:17:02 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.36?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 6 Jan 2005 15:17:02 -0000 Message-ID: <41DD5658.70803@fastcrypt.com> Date: Thu, 06 Jan 2005 10:16:40 -0500 From: Dave Cramer Reply-To: pg@fastcrypt.com Organization: Postgres International User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Ben Bostow Cc: pgsql-performance@postgresql.org Subject: Re: Problems with high traffic References: <5BE6686A-5FB9-11D9-9915-000D93C56ABE@viatornetworks.com> <41DD37EB.8050206@fastcrypt.com> <7D282078-5FE5-11D9-9915-000D93C56ABE@viatornetworks.com> <41DD3DF9.1090004@fastcrypt.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/64 X-Sequence-Number: 9754 Ben, It turns out that 7.2 has neither of those options you will have to set the debug_level to something higher than 0 and less than 4 to get information out. I'm afraid I'm not sure which value will give you what you are looking for. The link below explains what is available, and it isn't much :( http://www.postgresql.org/docs/7.2/static/runtime-config.html#LOGGING Dave Ben Bostow wrote: > I know 7.2 is old I'm trying to fix this in the mean time moving > everything to the latest Linux software when RedHat releases the > enterprise with 2.6. Postgres complains about log_duration and > log_statement are they a different name under 7.2? Is there > documentation on the type of logging the postgres can do? I can't seem > to find it in the 7.2 docs. If you know of any good resources for > Postgres in administering and tuning I would like to know. > > Benjamin > > On Jan 6, 2005, at 5:32 PM, Dave Cramer wrote: > >> Ben, >> >> Hmmm... ok 7.2.4 is quite old now and log_duration doesn't exist in >> the logging. You will see an immediate performance benefit just by >> moving to 7.4.x, but I'll bet that's not a reasonable path for you. >> >> in postgresql.conf you can change the logging to: >> >> log_pid=true >> log_duration=true >> log_statement=true >> >> syslog=2 ; to log to syslog >> >> Then in syslogd.conf >> >> add local0.none to the /var/log/messages line to stop logging to >> messages >> redirect local0.* to /var/log/postgres ; this step isn't really >> necesssary but will keep postgres logs separate >> >> HUP syslogd >> >> restart postgres >> >> Then you should be able to see which statements are taking the longest. >> >> Why do random hits to your web server cause postgres activity? Is >> your site dynamically created from the database ? >> >> Dave >> >> Ben Bostow wrote: >> >>> I am running postgresql 7.2.4-5.73, Dual P4, 1GB Ram. The big >>> problem is that I redirect all internal port 80 traffic to my web >>> server so I see all traffic whether it is a virus or not and >>> intended for my server or not. I originally had a problem with >>> running out of memory but I found a bug in my software that kept the >>> DB connection open so the next time a new connection was made on top >>> of that. As soon as I removed that I started getting the processor >>> problem. I am working on patching my kernel to have the string >>> matching and other new iptables features to limit the virus traffic >>> but I would like to figure the Processor problem out as I am working >>> on moving everything to the 2.6 kernel when RedHat finalizes their >>> release. >>> >>> I am not familular with many of the logging features of postgres >>> just the outputing the output to a file instead of /dev/null. >>> >>> Benjamin >>> >>> On Jan 6, 2005, at 5:06 PM, Dave Cramer wrote: >>> >>>> Ben >>>> >>>> Well, we need more information >>>> >>>> pg version, hardware, memory, etc >>>> >>>> you may want to turn on log_duration to see exactly which statement >>>> is causeing the problem. I'm assuming since it is taking a lot of >>>> CPU it will take some time to complete( this may not be true) >>>> >>>> On your last point, that is where you will get the most >>>> optimization, but I'd still use log_duration to make sure >>>> optimizing the statement will actually help. >>>> >>>> dave >>>> >>>> Ben Bostow wrote: >>>> >>>>> I'm still relatively new to Postgres. I usually just do SQL >>>>> programming but have found my self having to administer the DB >>>>> now. I have I have a problem on my website that when there is >>>>> high amounts of traffic coming from one computer to my web server. >>>>> I suspect it is because of a virus. But what when I notice this, >>>>> my processor drops to 0.0% idle with postmaster being my highest >>>>> CPU user. Under normal circumstances the processor runs >90% idle >>>>> or <10% used. I have tried tuning postgres but it doesn't seem to >>>>> make a difference, unless I am doing something wrong. If I would >>>>> like to find a solution other than rewriting all of my SQL >>>>> statements and creating them to take the least amount of time to >>>>> process. >>>>> >>>>> ---------------------------(end of >>>>> broadcast)--------------------------- >>>>> TIP 3: if posting/reading through Usenet, please send an appropriate >>>>> subscribe-nomail command to majordomo@postgresql.org so that >>>>> your >>>>> message can get through to the mailing list cleanly >>>>> >>>>> >>>> >>>> -- >>>> Dave Cramer >>>> http://www.postgresintl.com >>>> 519 939 0336 >>>> ICQ#14675561 >>>> >>> >>> >>> >> >> -- >> Dave Cramer >> http://www.postgresintl.com >> 519 939 0336 >> ICQ#14675561 >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561