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
