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 A74FA3A44D8 for ; Thu, 6 Jan 2005 13:32:59 +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 75375-10 for ; Thu, 6 Jan 2005 13:32:47 +0000 (GMT) Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by svr1.postgresql.org (Postfix) with SMTP id 4B05B3A4449 for ; Thu, 6 Jan 2005 13:32:49 +0000 (GMT) Received: (qmail 29579 invoked from network); 6 Jan 2005 13:32:21 -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 13:32:21 -0000 Message-ID: <41DD3DF9.1090004@fastcrypt.com> Date: Thu, 06 Jan 2005 08:32:41 -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> In-Reply-To: <7D282078-5FE5-11D9-9915-000D93C56ABE@viatornetworks.com> 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.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/60 X-Sequence-Number: 9750 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