pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dave Cramer <pg@fastcrypt.com>
To: Ben Bostow <ben@viatornetworks.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Problems with high traffic
Date: Thu, 06 Jan 2005 10:16:40 -0500
Message-ID: <41DD5658.70803@fastcrypt.com> (raw)
In-Reply-To: <ED0D1922-5FEA-11D9-9915-000D93C56ABE@viatornetworks.com>
References: <5BE6686A-5FB9-11D9-9915-000D93C56ABE@viatornetworks.com>
	<41DD37EB.8050206@fastcrypt.com>
	<7D282078-5FE5-11D9-9915-000D93C56ABE@viatornetworks.com>
	<41DD3DF9.1090004@fastcrypt.com>
	<ED0D1922-5FEA-11D9-9915-000D93C56ABE@viatornetworks.com>

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




view thread (6+ messages)

Message-ID: <41DD5658.70803@fastcrypt.com>
Permalink:  ../41DD5658.70803@fastcrypt.com/
Also on:    postgresql.org/message-id/41DD5658.70803@fastcrypt.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pg@fastcrypt.com, ben@viatornetworks.com
  Subject: Re: Problems with high traffic
  In-Reply-To: <41DD5658.70803@fastcrypt.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox