pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problems with high traffic
6+ messages / 2 participants
[nested] [flat]

* Problems with high traffic
@ 2005-01-06 08:02 Ben Bostow <ben@viatornetworks.com>
  2005-01-06 13:06 ` Re: Problems with high traffic Dave Cramer <pg@fastcrypt.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Ben Bostow @ 2005-01-06 08:02 UTC (permalink / raw)
  To: pgsql-performance

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. 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Problems with high traffic
  2005-01-06 08:02 Problems with high traffic Ben Bostow <ben@viatornetworks.com>
@ 2005-01-06 13:06 ` Dave Cramer <pg@fastcrypt.com>
  2005-01-06 13:18   ` Re: Problems with high traffic Ben Bostow <ben@viatornetworks.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Dave Cramer @ 2005-01-06 13:06 UTC (permalink / raw)
  To: Ben Bostow <ben@viatornetworks.com>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Problems with high traffic
  2005-01-06 08:02 Problems with high traffic Ben Bostow <ben@viatornetworks.com>
  2005-01-06 13:06 ` Re: Problems with high traffic Dave Cramer <pg@fastcrypt.com>
@ 2005-01-06 13:18   ` Ben Bostow <ben@viatornetworks.com>
  2005-01-06 13:32     ` Re: Problems with high traffic Dave Cramer <pg@fastcrypt.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Ben Bostow @ 2005-01-06 13:18 UTC (permalink / raw)
  To: pg@fastcrypt.com; +Cc: pgsql-performance

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
>




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Problems with high traffic
  2005-01-06 08:02 Problems with high traffic Ben Bostow <ben@viatornetworks.com>
  2005-01-06 13:06 ` Re: Problems with high traffic Dave Cramer <pg@fastcrypt.com>
  2005-01-06 13:18   ` Re: Problems with high traffic Ben Bostow <ben@viatornetworks.com>
@ 2005-01-06 13:32     ` Dave Cramer <pg@fastcrypt.com>
  2005-01-06 13:57       ` Re: Problems with high traffic Ben Bostow <ben@viatornetworks.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Dave Cramer @ 2005-01-06 13:32 UTC (permalink / raw)
  To: Ben Bostow <ben@viatornetworks.com>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Problems with high traffic
  2005-01-06 08:02 Problems with high traffic Ben Bostow <ben@viatornetworks.com>
  2005-01-06 13:06 ` Re: Problems with high traffic Dave Cramer <pg@fastcrypt.com>
  2005-01-06 13:18   ` Re: Problems with high traffic Ben Bostow <ben@viatornetworks.com>
  2005-01-06 13:32     ` Re: Problems with high traffic Dave Cramer <pg@fastcrypt.com>
@ 2005-01-06 13:57       ` Ben Bostow <ben@viatornetworks.com>
  2005-01-06 15:16         ` Re: Problems with high traffic Dave Cramer <pg@fastcrypt.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Ben Bostow @ 2005-01-06 13:57 UTC (permalink / raw)
  To: pg@fastcrypt.com; +Cc: pgsql-performance

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
>




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Problems with high traffic
  2005-01-06 08:02 Problems with high traffic Ben Bostow <ben@viatornetworks.com>
  2005-01-06 13:06 ` Re: Problems with high traffic Dave Cramer <pg@fastcrypt.com>
  2005-01-06 13:18   ` Re: Problems with high traffic Ben Bostow <ben@viatornetworks.com>
  2005-01-06 13:32     ` Re: Problems with high traffic Dave Cramer <pg@fastcrypt.com>
  2005-01-06 13:57       ` Re: Problems with high traffic Ben Bostow <ben@viatornetworks.com>
@ 2005-01-06 15:16         ` Dave Cramer <pg@fastcrypt.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Dave Cramer @ 2005-01-06 15:16 UTC (permalink / raw)
  To: Ben Bostow <ben@viatornetworks.com>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2005-01-06 15:16 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-06 08:02 Problems with high traffic Ben Bostow <ben@viatornetworks.com>
2005-01-06 13:06 ` Dave Cramer <pg@fastcrypt.com>
2005-01-06 13:18   ` Ben Bostow <ben@viatornetworks.com>
2005-01-06 13:32     ` Dave Cramer <pg@fastcrypt.com>
2005-01-06 13:57       ` Ben Bostow <ben@viatornetworks.com>
2005-01-06 15:16         ` Dave Cramer <pg@fastcrypt.com>

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