Обсуждение: How to ENABLE SQL capturing???
Hello all, I am trying to enable capturing of the submitted code via an application...how do I do this in Postgres? Performance is SLOW on my server and I have autovacuum enabled as well as rebuilt indexes...whatelse should be looked at? Thanks...Michelle -- View this message in context: http://www.nabble.com/How-to-ENABLE-SQL-capturing----tf4238694.html#a12060736 Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
On Wed, Aug 08, 2007 at 01:02:24PM -0700, smiley2211 wrote: > I am trying to enable capturing of the submitted code via an > application...how do I do this in Postgres? Performance is SLOW on my > server and I have autovacuum enabled as well as rebuilt indexes...whatelse > should be looked at? Try "log_min_duration_statement = 100" in postgresql.conf; it will show all statements that take more than 100ms. Set to 0 to log _all_ statements, or -1 to turn the logging back off. /* Steinar */ -- Homepage: http://www.sesse.net/
we currently have logging enabled for all queries over 100ms, and keep the last 24 hours of logs before we rotate them. I've found this tool very helpful in diagnosing new performance problems that crop up: http://pgfouine.projects.postgresql.org/ Bryan On 8/8/07, Steinar H. Gunderson <sgunderson@bigfoot.com> wrote: > On Wed, Aug 08, 2007 at 01:02:24PM -0700, smiley2211 wrote: > > I am trying to enable capturing of the submitted code via an > > application...how do I do this in Postgres? Performance is SLOW on my > > server and I have autovacuum enabled as well as rebuilt indexes...whatelse > > should be looked at? > > Try "log_min_duration_statement = 100" in postgresql.conf; it will show all > statements that take more than 100ms. Set to 0 to log _all_ statements, or > -1 to turn the logging back off. > > /* Steinar */ > -- > Homepage: http://www.sesse.net/ > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
Hello all, I have ENABLED this 'log_min_duration_statement = 100" but I can't figure out WHERE it's writing the commands to ...I have it set to 'syslogs' but this file is 0 bytes :confused: Should I set other parameters in my postgresql.conf file??? Thanks...Michelle Bryan Murphy-3 wrote: > > we currently have logging enabled for all queries over 100ms, and keep > the last 24 hours of logs before we rotate them. I've found this tool > very helpful in diagnosing new performance problems that crop up: > > http://pgfouine.projects.postgresql.org/ > > Bryan > > On 8/8/07, Steinar H. Gunderson <sgunderson@bigfoot.com> wrote: >> On Wed, Aug 08, 2007 at 01:02:24PM -0700, smiley2211 wrote: >> > I am trying to enable capturing of the submitted code via an >> > application...how do I do this in Postgres? Performance is SLOW on my >> > server and I have autovacuum enabled as well as rebuilt >> indexes...whatelse >> > should be looked at? >> >> Try "log_min_duration_statement = 100" in postgresql.conf; it will show >> all >> statements that take more than 100ms. Set to 0 to log _all_ statements, >> or >> -1 to turn the logging back off. >> >> /* Steinar */ >> -- >> Homepage: http://www.sesse.net/ >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 6: explain analyze is your friend >> > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > -- View this message in context: http://www.nabble.com/How-to-ENABLE-SQL-capturing----tf4238694.html#a12096180 Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Michelle, What platform are you on? If you're on linux, than logging to syslog will likely show up in the /var/log/messages file. On Fri, 10 Aug 2007, smiley2211 wrote: > > Hello all, > > I have ENABLED this 'log_min_duration_statement = 100" but I can't figure > out WHERE it's writing the commands to ...I have it set to 'syslogs' but > this file is 0 bytes :confused: > > Should I set other parameters in my postgresql.conf file??? > > Thanks...Michelle > > > Bryan Murphy-3 wrote: >> >> we currently have logging enabled for all queries over 100ms, and keep >> the last 24 hours of logs before we rotate them. I've found this tool >> very helpful in diagnosing new performance problems that crop up: >> >> http://pgfouine.projects.postgresql.org/ >> >> Bryan >> >> On 8/8/07, Steinar H. Gunderson <sgunderson@bigfoot.com> wrote: >>> On Wed, Aug 08, 2007 at 01:02:24PM -0700, smiley2211 wrote: >>>> I am trying to enable capturing of the submitted code via an >>>> application...how do I do this in Postgres? Performance is SLOW on my >>>> server and I have autovacuum enabled as well as rebuilt >>> indexes...whatelse >>>> should be looked at? >>> >>> Try "log_min_duration_statement = 100" in postgresql.conf; it will show >>> all >>> statements that take more than 100ms. Set to 0 to log _all_ statements, >>> or >>> -1 to turn the logging back off. >>> >>> /* Steinar */ >>> -- >>> Homepage: http://www.sesse.net/ >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 6: explain analyze is your friend >>> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq >> >> > > -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
Jeff, You are CORRECT...my queries were going to /var/log/messages...had to get the Linux Admin to grant me READ access to the file... Thanks for your reply. Michelle. Jeff Frost wrote: > > Michelle, > > What platform are you on? If you're on linux, than logging to syslog will > likely show up in the /var/log/messages file. > > On Fri, 10 Aug 2007, smiley2211 wrote: > >> >> Hello all, >> >> I have ENABLED this 'log_min_duration_statement = 100" but I can't figure >> out WHERE it's writing the commands to ...I have it set to 'syslogs' but >> this file is 0 bytes :confused: >> >> Should I set other parameters in my postgresql.conf file??? >> >> Thanks...Michelle >> >> >> Bryan Murphy-3 wrote: >>> >>> we currently have logging enabled for all queries over 100ms, and keep >>> the last 24 hours of logs before we rotate them. I've found this tool >>> very helpful in diagnosing new performance problems that crop up: >>> >>> http://pgfouine.projects.postgresql.org/ >>> >>> Bryan >>> >>> On 8/8/07, Steinar H. Gunderson <sgunderson@bigfoot.com> wrote: >>>> On Wed, Aug 08, 2007 at 01:02:24PM -0700, smiley2211 wrote: >>>>> I am trying to enable capturing of the submitted code via an >>>>> application...how do I do this in Postgres? Performance is SLOW on my >>>>> server and I have autovacuum enabled as well as rebuilt >>>> indexes...whatelse >>>>> should be looked at? >>>> >>>> Try "log_min_duration_statement = 100" in postgresql.conf; it will show >>>> all >>>> statements that take more than 100ms. Set to 0 to log _all_ statements, >>>> or >>>> -1 to turn the logging back off. >>>> >>>> /* Steinar */ >>>> -- >>>> Homepage: http://www.sesse.net/ >>>> >>>> ---------------------------(end of >>>> broadcast)--------------------------- >>>> TIP 6: explain analyze is your friend >>>> >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 3: Have you checked our extensive FAQ? >>> >>> http://www.postgresql.org/docs/faq >>> >>> >> >> > > -- > Jeff Frost, Owner <jeff@frostconsultingllc.com> > Frost Consulting, LLC http://www.frostconsultingllc.com/ > Phone: 650-780-7908 FAX: 650-649-1954 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > > -- View this message in context: http://www.nabble.com/How-to-ENABLE-SQL-capturing----tf4238694.html#a12099590 Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 smiley2211 wrote: > Jeff, > > You are CORRECT...my queries were going to /var/log/messages...had to get > the Linux Admin to grant me READ access to the file... You may want to actually get that to stop. Syslog is a notorious performance bottleneck for postgresql. > > Thanks for your reply. > Michelle. > > > Jeff Frost wrote: >> Michelle, >> >> What platform are you on? If you're on linux, than logging to syslog will >> likely show up in the /var/log/messages file. >> >> On Fri, 10 Aug 2007, smiley2211 wrote: >> >>> Hello all, >>> >>> I have ENABLED this 'log_min_duration_statement = 100" but I can't figure >>> out WHERE it's writing the commands to ...I have it set to 'syslogs' but >>> this file is 0 bytes :confused: >>> >>> Should I set other parameters in my postgresql.conf file??? >>> >>> Thanks...Michelle >>> >>> >>> Bryan Murphy-3 wrote: >>>> we currently have logging enabled for all queries over 100ms, and keep >>>> the last 24 hours of logs before we rotate them. I've found this tool >>>> very helpful in diagnosing new performance problems that crop up: >>>> >>>> http://pgfouine.projects.postgresql.org/ >>>> >>>> Bryan >>>> >>>> On 8/8/07, Steinar H. Gunderson <sgunderson@bigfoot.com> wrote: >>>>> On Wed, Aug 08, 2007 at 01:02:24PM -0700, smiley2211 wrote: >>>>>> I am trying to enable capturing of the submitted code via an >>>>>> application...how do I do this in Postgres? Performance is SLOW on my >>>>>> server and I have autovacuum enabled as well as rebuilt >>>>> indexes...whatelse >>>>>> should be looked at? >>>>> Try "log_min_duration_statement = 100" in postgresql.conf; it will show >>>>> all >>>>> statements that take more than 100ms. Set to 0 to log _all_ statements, >>>>> or >>>>> -1 to turn the logging back off. >>>>> >>>>> /* Steinar */ >>>>> -- >>>>> Homepage: http://www.sesse.net/ >>>>> >>>>> ---------------------------(end of >>>>> broadcast)--------------------------- >>>>> TIP 6: explain analyze is your friend >>>>> >>>> ---------------------------(end of broadcast)--------------------------- >>>> TIP 3: Have you checked our extensive FAQ? >>>> >>>> http://www.postgresql.org/docs/faq >>>> >>>> >>> >> -- >> Jeff Frost, Owner <jeff@frostconsultingllc.com> >> Frost Consulting, LLC http://www.frostconsultingllc.com/ >> Phone: 650-780-7908 FAX: 650-649-1954 >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 9: In versions below 8.0, the planner will ignore your desire to >> choose an index scan if your joining column's datatypes do not >> match >> >> > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGvN2qATb/zqfZUUQRAmxSAJ96tbd3n12W79mxtad4dtD0F/7w6wCeI1uj RpgRIKSMNrMHgm1wrCkqpjU= =gJD2 -----END PGP SIGNATURE-----
On 8/10/07, Joshua D. Drake <jd@commandprompt.com> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > smiley2211 wrote: > > Jeff, > > > > You are CORRECT...my queries were going to /var/log/messages...had to get > > the Linux Admin to grant me READ access to the file... > > You may want to actually get that to stop. Syslog is a notorious > performance bottleneck for postgresql. Can you elaborate? The only reference to this I could find was a thread from 2004 where someone wasn't rotating his logs. -Jonathan
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Jonathan Ellis wrote:
> On 8/10/07, Joshua D. Drake <jd@commandprompt.com> wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> smiley2211 wrote:
>>> Jeff,
>>>
>>> You are CORRECT...my queries were going to /var/log/messages...had to get
>>> the Linux Admin to grant me READ access to the file...
>> You may want to actually get that to stop. Syslog is a notorious
>> performance bottleneck for postgresql.
>
> Can you elaborate? The only reference to this I could find was a
> thread from 2004 where someone wasn't rotating his logs.
I am not sure what to elaborate on :). Syslog is slow, logging to file
isn't. Although both will certainly slow down your installation quite a
bit, syslog will slow it down more.
If I recall correctly, it is because syslog is blocking.
Joshua D. Drake
>
> -Jonathan
>
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFGwHv6ATb/zqfZUUQRAgMlAKCcZpj+CCP50Deo/CsSCN21IyjrCACghXfN
uJQ+qsu4FI4Kjf8fpNiWgnw=
=BJ8E
-----END PGP SIGNATURE-----
"Joshua D. Drake" <jd@commandprompt.com> writes: > If I recall correctly, it is because syslog is blocking. Are you sure it isn't just that syslog fsyncs its log files after every log message? I don't think the individual syslogs are synchronous but if syslog falls behind the buffer will fill and throttle the sender. If your Postgres data is on the same device as the syslogs those fsyncs will probably cause a big slowdown directly on Postgres's I/O as well. You can turn off the fsyncs in syslog by putting a - before the filename. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Gregory Stark wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>
>> If I recall correctly, it is because syslog is blocking.
>
> Are you sure it isn't just that syslog fsyncs its log files after every log
> message?
Nope I am not sure at all ;). Darcy actually found the issue and can
speak better to it, I never use syslog and have always logged direct to
file.
I don't think the individual syslogs are synchronous but if syslog
> falls behind the buffer will fill and throttle the sender.
>
> If your Postgres data is on the same device as the syslogs those fsyncs will
> probably cause a big slowdown directly on Postgres's I/O as well.
>
> You can turn off the fsyncs in syslog by putting a - before the filename.
>
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFGwIOnATb/zqfZUUQRAqWqAKCEhoW/01Hc//cDEpREit8ipn2SZwCfUxPE
1Ir6eyuD4EcShwsn4sMAeKA=
=W2cJ
-----END PGP SIGNATURE-----