Обсуждение: Re: [GENERAL] log sql?
DEBUG_LEVEL (integer) The higher this value is set, the more "debugging" output of various sorts is generated in the server log during operation. This option is 0 by default, which means no debugging output. Values up to about 4 currently make sense. DEBUG_PRINT_QUERY (boolean) DEBUG_PRINT_PARSE (boolean) DEBUG_PRINT_REWRITTEN (boolean) DEBUG_PRINT_PLAN (boolean) DEBUG_PRETTY_PRINT (boolean) These flags enable various debugging output to be sent to the server log. For each executed query, prints either the query text, the resulting parse tree, the query rewriter output, or the execution plan. DEBUG_PRETTY_PRINT indents these displays to produce a more readable but much longer output format. Setting DEBUG_LEVEL above zero implicitly turns on some of these flags. -- I'd recommend debug level 1 and print query true for starters. Also remember that excessive debug logging can cause performance issues, so keep an eye on things if you crank this up on a production server. Robert Treat On Tue, 2002-07-30 at 00:14, Bruce Momjian wrote: > Andrew Sullivan wrote: > > On Fri, Jul 26, 2002 at 03:27:45PM -0700, Laurette Cisneros wrote: > > > > > > Is there way to track each sql statement and the user for the server? > > > > Sort of. Turn on query logging in the postgresql.conf file, and also > > log connections and PID. Then, you can track back by using the PID, > > to discovere who logged in (and therefore, who issued that query). > > Andrew, what postgresql.conf parameter is query logging. I don't see > it. I see: > > #log_connections = false > #log_timestamp = false > #log_pid = false > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
I'll try this. The last time I turned the debugging level up, it generated massive amounts of info in the log file so I turned it off. I'll try this... Thanks! L. On 30 Jul 2002, Robert Treat wrote: > DEBUG_LEVEL (integer) > > The higher this value is set, the more "debugging" output of various > sorts is generated in the server log during operation. This option is 0 > by default, which means no debugging output. Values up to about 4 > currently make sense. > DEBUG_PRINT_QUERY (boolean) > DEBUG_PRINT_PARSE (boolean) > DEBUG_PRINT_REWRITTEN (boolean) > DEBUG_PRINT_PLAN (boolean) > DEBUG_PRETTY_PRINT (boolean) > > These flags enable various debugging output to be sent to the server > log. For each executed query, prints either the query text, the > resulting parse tree, the query rewriter output, or the execution plan. > DEBUG_PRETTY_PRINT indents these displays to produce a more readable but > much longer output format. Setting DEBUG_LEVEL above zero implicitly > turns on some of these flags. > -- > I'd recommend debug level 1 and print query true for starters. Also > remember that excessive debug logging can cause performance issues, so > keep an eye on things if you crank this up on a production server. > > Robert Treat > > On Tue, 2002-07-30 at 00:14, Bruce Momjian wrote: > > Andrew Sullivan wrote: > > > On Fri, Jul 26, 2002 at 03:27:45PM -0700, Laurette Cisneros wrote: > > > > > > > > Is there way to track each sql statement and the user for the server? > > > > > > Sort of. Turn on query logging in the postgresql.conf file, and also > > > log connections and PID. Then, you can track back by using the PID, > > > to discovere who logged in (and therefore, who issued that query). > > > > Andrew, what postgresql.conf parameter is query logging. I don't see > > it. I see: > > > > #log_connections = false > > #log_timestamp = false > > #log_pid = false > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > -- Laurette Cisneros The Database Group (510) 420-3137 NextBus Information Systems, Inc. www.nextbus.com ---------------------------------- "Intelligence complicates. Wisdom simplifies." -- Mason Cooley
Hi, once you do that keep an eye over the log file size.. i rember messing up once on this,, postmaster will core dump once size(log_file)> 2.1 GB. regds mallah. > I'll try this. The last time I turned the debugging level up, it > generated massive amounts of info in the log file so I turned it off. > I'll try this... > > Thanks! > > L. > On 30 Jul 2002, Robert Treat wrote: > >> DEBUG_LEVEL (integer) >> >> The higher this value is set, the more "debugging" output of various >> sorts is generated in the server log during operation. This option is >> 0 by default, which means no debugging output. Values up to about 4 >> currently make sense. >> DEBUG_PRINT_QUERY (boolean) >> DEBUG_PRINT_PARSE (boolean) >> DEBUG_PRINT_REWRITTEN (boolean) >> DEBUG_PRINT_PLAN (boolean) >> DEBUG_PRETTY_PRINT (boolean) >> >> These flags enable various debugging output to be sent to the server >> log. For each executed query, prints either the query text, the >> resulting parse tree, the query rewriter output, or the execution >> plan. DEBUG_PRETTY_PRINT indents these displays to produce a more >> readable but much longer output format. Setting DEBUG_LEVEL above zero >> implicitly turns on some of these flags. >> -- >> I'd recommend debug level 1 and print query true for starters. Also >> remember that excessive debug logging can cause performance issues, so >> keep an eye on things if you crank this up on a production server. >> >> Robert Treat >> >> On Tue, 2002-07-30 at 00:14, Bruce Momjian wrote: >> > Andrew Sullivan wrote: >> > > On Fri, Jul 26, 2002 at 03:27:45PM -0700, Laurette Cisneros wrote: >> > > > >> > > > Is there way to track each sql statement and the user for the >> > > > server? >> > > >> > > Sort of. Turn on query logging in the postgresql.conf file, and >> > > also log connections and PID. Then, you can track back by using >> > > the PID, to discovere who logged in (and therefore, who issued >> > > that query). >> > >> > Andrew, what postgresql.conf parameter is query logging. I don't >> > see it. I see: >> > >> > #log_connections = false >> > #log_timestamp = false >> > #log_pid = false >> > >> > -- >> > Bruce Momjian | http://candle.pha.pa.us >> > pgman@candle.pha.pa.us | (610) 853-3000 >> > + If your life is a hard drive, | 830 Blythe Avenue >> > + Christ can be your backup. | Drexel Hill, Pennsylvania >> > 19026 >> > >> > ---------------------------(end of >> > broadcast)--------------------------- TIP 4: Don't 'kill -9' the >> > postmaster >> >> >> > > -- > Laurette Cisneros > The Database Group > (510) 420-3137 > NextBus Information Systems, Inc. > www.nextbus.com > ---------------------------------- > "Intelligence complicates. Wisdom simplifies." > -- Mason Cooley > > > ---------------------------(end of > broadcast)--------------------------- TIP 2: you can get off all lists > at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
Yes indeed...the size has reach 9.6M in 15 minutes...it is spewing a lot of info. Hmmm.... Thanks, L. On Tue, 30 Jul 2002 mallah@trade-india.com wrote: > > Hi, > > once you do that keep an eye over the log file size.. > i rember messing up once on this,, postmaster will core dump > once size(log_file)> 2.1 GB. > > regds > mallah. > > > > > I'll try this. The last time I turned the debugging level up, it > > generated massive amounts of info in the log file so I turned it off. > > I'll try this... > > > > Thanks! > > > > L. > > On 30 Jul 2002, Robert Treat wrote: > > > >> DEBUG_LEVEL (integer) > >> > >> The higher this value is set, the more "debugging" output of various > >> sorts is generated in the server log during operation. This option is > >> 0 by default, which means no debugging output. Values up to about 4 > >> currently make sense. > >> DEBUG_PRINT_QUERY (boolean) > >> DEBUG_PRINT_PARSE (boolean) > >> DEBUG_PRINT_REWRITTEN (boolean) > >> DEBUG_PRINT_PLAN (boolean) > >> DEBUG_PRETTY_PRINT (boolean) > >> > >> These flags enable various debugging output to be sent to the server > >> log. For each executed query, prints either the query text, the > >> resulting parse tree, the query rewriter output, or the execution > >> plan. DEBUG_PRETTY_PRINT indents these displays to produce a more > >> readable but much longer output format. Setting DEBUG_LEVEL above zero > >> implicitly turns on some of these flags. > >> -- > >> I'd recommend debug level 1 and print query true for starters. Also > >> remember that excessive debug logging can cause performance issues, so > >> keep an eye on things if you crank this up on a production server. > >> > >> Robert Treat > >> > >> On Tue, 2002-07-30 at 00:14, Bruce Momjian wrote: > >> > Andrew Sullivan wrote: > >> > > On Fri, Jul 26, 2002 at 03:27:45PM -0700, Laurette Cisneros wrote: > >> > > > > >> > > > Is there way to track each sql statement and the user for the > >> > > > server? > >> > > > >> > > Sort of. Turn on query logging in the postgresql.conf file, and > >> > > also log connections and PID. Then, you can track back by using > >> > > the PID, to discovere who logged in (and therefore, who issued > >> > > that query). > >> > > >> > Andrew, what postgresql.conf parameter is query logging. I don't > >> > see it. I see: > >> > > >> > #log_connections = false > >> > #log_timestamp = false > >> > #log_pid = false > >> > > >> > -- > >> > Bruce Momjian | http://candle.pha.pa.us > >> > pgman@candle.pha.pa.us | (610) 853-3000 > >> > + If your life is a hard drive, | 830 Blythe Avenue > >> > + Christ can be your backup. | Drexel Hill, Pennsylvania > >> > 19026 > >> > > >> > ---------------------------(end of > >> > broadcast)--------------------------- TIP 4: Don't 'kill -9' the > >> > postmaster > >> > >> > >> > > > > -- > > Laurette Cisneros > > The Database Group > > (510) 420-3137 > > NextBus Information Systems, Inc. > > www.nextbus.com > > ---------------------------------- > > "Intelligence complicates. Wisdom simplifies." > > -- Mason Cooley > > > > > > ---------------------------(end of > > broadcast)--------------------------- TIP 2: you can get off all lists > > at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > ----------------------------------------- > Get your free web based email at trade-india.com. > "India's Leading B2B eMarketplace.!" > http://www.trade-india.com/ > > -- Laurette Cisneros The Database Group (510) 420-3137 NextBus Information Systems, Inc. www.nextbus.com ---------------------------------- "Intelligence complicates. Wisdom simplifies." -- Mason Cooley
On Tue, Jul 30, 2002 at 09:53:23AM -0700, Laurette Cisneros wrote:
> Yes indeed...the size has reach 9.6M in 15 minutes...it is spewing a lot of
> info. Hmmm....
You really need some mechanism for rotating your logs if you are
going to use them. Otherwise, they become too unwieldy.
> > once you do that keep an eye over the log file size..
> > i rember messing up once on this,, postmaster will core dump
> > once size(log_file)> 2.1 GB.
I'll bet that's system-dependent. Probably on a filesystem that
handles files larger than 2G, it doesn't crash. Not that you'd want
to have to manage a 2 gig log file.
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
On Tue, 30 Jul 2002, Andrew Sullivan wrote: > On Tue, Jul 30, 2002 at 09:53:23AM -0700, Laurette Cisneros wrote: > > Yes indeed...the size has reach 9.6M in 15 minutes...it is spewing a lot of > > info. Hmmm.... > > You really need some mechanism for rotating your logs if you are > going to use them. Otherwise, they become too unwieldy. If one has apache installed, one has the mechanism installed. Apache has a nice little log rotation utility called rotatelogs that can used for this purpose, just put it into the path of the postgres user and start the database like so: pg_ctl start | rotatelogs "$PGDATA/logs/pgsql" 86400 & where $PGDATA/logs is a directory you made and pgsql will be the name all your log files start with.