Re: [PERFORM] Problems with high traffic

2005-01-06 Thread Dave Cramer
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 [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PERFORM] Problems with high traffic

2005-01-06 Thread Ben Bostow
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 [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly


--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PERFORM] Problems with high traffic

2005-01-06 Thread Dave Cramer
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 [EMAIL PROTECTED] 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 8: explain analyze is your friend


Re: [PERFORM] Problems with high traffic

2005-01-06 Thread Ben Bostow
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly


Re: [PERFORM] Problems with high traffic

2005-01-06 Thread Dave Cramer
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html