Re: Problems with high traffic - Mailing list pgsql-performance
From | Dave Cramer |
---|---|
Subject | Re: Problems with high traffic |
Date | |
Msg-id | 41DD5658.70803@fastcrypt.com Whole thread Raw |
In response to | Re: Problems with high traffic (Ben Bostow <ben@viatornetworks.com>) |
List | 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
pgsql-performance by date: