Re: Problems with high traffic

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