pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedChange to 'timing on' globally
6+ messages / 3 participants
[nested] [flat]
* Change to 'timing on' globally
@ 2010-07-05 17:00 Balkrishna Sharma <b_ki@hotmail.com>
2010-07-05 17:10 ` Re: Change to 'timing on' globally Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 6+ messages in thread
From: Balkrishna Sharma @ 2010-07-05 17:00 UTC (permalink / raw)
To: pgsql-admin
I know that we can toggle the timing at session level by using \timing in psql.
Is there a way to set the default to 'timing on' globally across the database or atleast across all psql statements by a specificied user ?
Thanks,-Bala
_________________________________________________________________
The New Busy is not the too busy. Combine all your e-mail accounts with Hotmail.
http://www.windowslive.com/campaign/thenewbusy?tile=multiaccount&ocid=PID28326::T:WLMTAGL:ON:WL:...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Change to 'timing on' globally
2010-07-05 17:00 Change to 'timing on' globally Balkrishna Sharma <b_ki@hotmail.com>
@ 2010-07-05 17:10 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-07-05 19:40 ` Re: Change to 'timing on' globally Balkrishna Sharma <b_ki@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2010-07-05 17:10 UTC (permalink / raw)
To: Balkrishna Sharma <b_ki@hotmail.com>; +Cc: pgsql-admin
Excerpts from Balkrishna Sharma's message of lun jul 05 13:00:44 -0400 2010:
>
> I know that we can toggle the timing at session level by using \timing in psql.
> Is there a way to set the default to 'timing on' globally across the database or atleast across all psql statements by a specificied user ?
.psqlrc ?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Change to 'timing on' globally
2010-07-05 17:00 Change to 'timing on' globally Balkrishna Sharma <b_ki@hotmail.com>
2010-07-05 17:10 ` Re: Change to 'timing on' globally Alvaro Herrera <alvherre@commandprompt.com>
@ 2010-07-05 19:40 ` Balkrishna Sharma <b_ki@hotmail.com>
2010-07-06 14:48 ` Re: Change to 'timing on' globally Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 6+ messages in thread
From: Balkrishna Sharma @ 2010-07-05 19:40 UTC (permalink / raw)
To: alvherre@commandprompt.com; +Cc: pgsql-admin
Thanks. If I want to do at system-wide level, where do I store the psqlrc file (assuming I want to change the timing behavior system-wide)?
(CentOS 5, Postgres 8.4)
$ ./pg_config --sysconfdir/opt/PostgreSQL/8.4/etc/postgresql
But I don't have /opt/PostgreSQL/8.4/etc/postgresql directory. Just creating the directory and putting a psqlrc file over there does not seem to work.
On a side-note, I observered that timing value in ~/.psqlrc was ignored by psql -c "..." command but not by echo "...."|psqlThought it was strange.
> CC: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Change to 'timing on' globally
> From: alvherre@commandprompt.com
> To: b_ki@hotmail.com
> Date: Mon, 5 Jul 2010 13:10:30 -0400
>
> Excerpts from Balkrishna Sharma's message of lun jul 05 13:00:44 -0400 2010:
> >
> > I know that we can toggle the timing at session level by using \timing in psql.
> > Is there a way to set the default to 'timing on' globally across the database or atleast across all psql statements by a specificied user ?
>
> .psqlrc ?
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
_________________________________________________________________
Hotmail has tools for the New Busy. Search, chat and e-mail from your inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_1=
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Change to 'timing on' globally
2010-07-05 17:00 Change to 'timing on' globally Balkrishna Sharma <b_ki@hotmail.com>
2010-07-05 17:10 ` Re: Change to 'timing on' globally Alvaro Herrera <alvherre@commandprompt.com>
2010-07-05 19:40 ` Re: Change to 'timing on' globally Balkrishna Sharma <b_ki@hotmail.com>
@ 2010-07-06 14:48 ` Bruce Momjian <bruce@momjian.us>
2010-07-06 15:20 ` Re: Change to 'timing on' globally Balkrishna Sharma <b_ki@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 2010-07-06 14:48 UTC (permalink / raw)
To: Balkrishna Sharma <b_ki@hotmail.com>; +Cc: alvherre@commandprompt.com; pgsql-admin
Balkrishna Sharma wrote:
>
> Thanks. If I want to do at system-wide level, where do I store the
> psqlrc file (assuming I want to change the timing behavior system-wide)?
> (CentOS 5, Postgres 8.4)
> $ ./pg_config --sysconfdir/opt/PostgreSQL/8.4/etc/postgresql
> But I don't have /opt/PostgreSQL/8.4/etc/postgresql directory. Just
> creating the directory and putting a psqlrc file over there does not
> seem to work.
I just tested it here on Ubuntu and it worked:
$ sudo mkdir etc
$ sudo mkdir etc/postgresql
$ cd etc/postgresql/
$ sudo vi psqlrc
# add \echo test
$ pwd
/opt/PostgreSQL/8.4/etc/postgresql
$ ../../bin/psql -U postgres postgres
--> test
psql (8.4.2)
Type "help" for help.
postgres=#
> On a side-note, I observered that timing value in ~/.psqlrc was
> ignored by psql -c "..." command but not by echo "...."|psqlThought
> it was strange.
Yeah, that is odd.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Change to 'timing on' globally
2010-07-05 17:00 Change to 'timing on' globally Balkrishna Sharma <b_ki@hotmail.com>
2010-07-05 17:10 ` Re: Change to 'timing on' globally Alvaro Herrera <alvherre@commandprompt.com>
2010-07-05 19:40 ` Re: Change to 'timing on' globally Balkrishna Sharma <b_ki@hotmail.com>
2010-07-06 14:48 ` Re: Change to 'timing on' globally Bruce Momjian <bruce@momjian.us>
@ 2010-07-06 15:20 ` Balkrishna Sharma <b_ki@hotmail.com>
2010-07-06 15:29 ` Re: Change to 'timing on' globally Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 6+ messages in thread
From: Balkrishna Sharma @ 2010-07-06 15:20 UTC (permalink / raw)
To: bruce@momjian.us; +Cc: alvherre@commandprompt.com; pgsql-admin
> I just tested it here on Ubuntu and it worked:I followed your steps and it worked in the way you indicated, on CentOS as well. But it still does not:a. work with psql -c "query" syntax. (Works in echo mode or in interactive mode.)b. it does not still seem to work if you fire the queries from a client box (in any mode - interactive or otherwise)ON SERVER I get:Timing is on. now------------------------------ 2010-07-06 11:06:13.16734-04(1 row)Time: 0.574 ms
ON CLIENT I just get: now------------------------------- 2010-07-06 11:06:28.455395-04(1 row)
~~~~~~~~~~~~~~~~~~~~Basically I am firing a lot of psql through unix script on several client machines and a lot of the psql are hanging for some other reasons. I also need to capture the timing of each query. So I need timing to be on.
Doing the following captures the timing but I don't know which psql statement is hanging when I do ps aux|grep psqlecho '\timing \\select * from ........' | psqlOn ps aux|grep psql I just see:> ps aux|grep psql2255 0.0 0.0 155636 1668 pts/1 S Jul05 0:00 psql3883 0.0 0.0 155636 1676 pts/1 S Jul05 0:00 psql4672 0.0 0.0 155636 1672 pts/1 S Jul05 0:00 psql4713 0.0 0.0 155636 1672 pts/1 S Jul05 0:00 psql4737 0.0 0.0 155636 1672 pts/1 S Jul05 0:00 psql4798 0.0 0.0 155636 1668 pts/1 S Jul05 0:00 psql5050 0.0 0.0 155636 1676 pts/1 S Jul05 0:00 psql5086 0.0 0.0 155636 1668 pts/1 S Jul05 0:00 psql5405 0.0 0.0 155636 1668 pts/1 S Jul05 0:00 psql7255 0.0 0.0 155644 1796 pts/1 S Jul05 0:00 psql
psql -c 'select * from "DAPP".student_common_data where student_id = 1000 and field_id =1988;' does make the ps aux more informative but it does not capture the query timing. From what I understand you cannot mix ('timing + query') in "-c" mode.
So trying to set 'timing on' outside the individual queries (and preferably outside the client machines) somewhere on the server so that psql -c on client would capture the timing automatically.
> From: bruce@momjian.us
> Subject: Re: [ADMIN] Change to 'timing on' globally
> To: b_ki@hotmail.com
> Date: Tue, 6 Jul 2010 10:48:48 -0400
> CC: alvherre@commandprompt.com; pgsql-admin@postgresql.org
>
> Balkrishna Sharma wrote:
> >
> > Thanks. If I want to do at system-wide level, where do I store the
> > psqlrc file (assuming I want to change the timing behavior system-wide)?
>
> > (CentOS 5, Postgres 8.4)
> > $ ./pg_config --sysconfdir/opt/PostgreSQL/8.4/etc/postgresql
>
> > But I don't have /opt/PostgreSQL/8.4/etc/postgresql directory. Just
> > creating the directory and putting a psqlrc file over there does not
> > seem to work.
>
> I just tested it here on Ubuntu and it worked:
>
> $ sudo mkdir etc
> $ sudo mkdir etc/postgresql
> $ cd etc/postgresql/
> $ sudo vi psqlrc
> # add \echo test
> $ pwd
> /opt/PostgreSQL/8.4/etc/postgresql
> $ ../../bin/psql -U postgres postgres
> --> test
> psql (8.4.2)
> Type "help" for help.
>
> postgres=#
>
> > On a side-note, I observered that timing value in ~/.psqlrc was
> > ignored by psql -c "..." command but not by echo "...."|psqlThought
> > it was strange.
>
> Yeah, that is odd.
>
> --
> Bruce Momjian <bruce@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + None of us is going to be here forever. +
_________________________________________________________________
Hotmail is redefining busy with tools for the New Busy. Get more from your inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2=
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Change to 'timing on' globally
2010-07-05 17:00 Change to 'timing on' globally Balkrishna Sharma <b_ki@hotmail.com>
2010-07-05 17:10 ` Re: Change to 'timing on' globally Alvaro Herrera <alvherre@commandprompt.com>
2010-07-05 19:40 ` Re: Change to 'timing on' globally Balkrishna Sharma <b_ki@hotmail.com>
2010-07-06 14:48 ` Re: Change to 'timing on' globally Bruce Momjian <bruce@momjian.us>
2010-07-06 15:20 ` Re: Change to 'timing on' globally Balkrishna Sharma <b_ki@hotmail.com>
@ 2010-07-06 15:29 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2010-07-06 15:29 UTC (permalink / raw)
To: Balkrishna Sharma <b_ki@hotmail.com>; +Cc: alvherre@commandprompt.com; pgsql-admin
Balkrishna Sharma wrote:
>
> > I just tested it here on Ubuntu and it worked:I followed your steps and it worked in the way you indicated, on CentOS as well. But it still does not:a. work with psql -c "query" syntax. (Works in echo mode or in interactive mode.)b. it does not still seem to work if you fire the queries from a client box (in any mode - interactive or otherwise)ON SERVER I get:Timing is on. now------------------------------ 2010-07-06 11:06:13.16734-04(1 row)Time: 0.574 ms
>
> ON CLIENT I just get: now------------------------------- 2010-07-06 11:06:28.455395-04(1 row)
>
> ~~~~~~~~~~~~~~~~~~~~Basically I am firing a lot of psql through unix script on several client machines and a lot of the psql are hanging for some other reasons. I also need to capture the timing of each query. So I need timing to be on.
> Doing the following captures the timing but I don't know which psql statement is hanging when I do ps aux|grep psqlecho '\timing \\select * from ........' | psqlOn ps aux|grep psql I just see:> ps aux|grep psql2255 0.0 0.0 155636 1668 pts/1 S Jul05 0:00 psql3883 0.0 0.0 155636 1676 pts/1 S Jul05 0:00 psql4672 0.0 0.0 155636 1672 pts/1 S Jul05 0:00 psql4713 0.0 0.0 155636 1672 pts/1 S Jul05 0:00 psql4737 0.0 0.0 155636 1672 pts/1 S Jul05 0:00 psql4798 0.0 0.0 155636 1668 pts/1 S Jul05 0:00 psql5050 0.0 0.0 155636 1676 pts/1 S Jul05 0:00 psql5086 0.0 0.0 155636 1668 pts/1 S Jul05 0:00 psql5405 0.0 0.0 155636 1668 pts/1 S Jul05 0:00 psql7255 0.0 0.0 155644 1796 pts/1 S Jul05 0:00 psql
>
> psql -c 'select * from "DAPP".student_common_data where student_id = 1000 and field_id =1988;' does make the ps aux more informative but it does not capture the query timing. From what I understand you cannot mix ('timing + query') in "-c" mode.
> So trying to set 'timing on' outside the individual queries (and preferably outside the client machines) somewhere on the server so that psql -c on client would capture the timing automatically.
I think you need to look at postgresql.conf variables like
log_min_duration_statement, and you are right that psqlrc is only going
to be read for clients on the server machine, and only via psql.
---------------------------------------------------------------------------
>
>
>
> > From: bruce@momjian.us
> > Subject: Re: [ADMIN] Change to 'timing on' globally
> > To: b_ki@hotmail.com
> > Date: Tue, 6 Jul 2010 10:48:48 -0400
> > CC: alvherre@commandprompt.com; pgsql-admin@postgresql.org
> >
> > Balkrishna Sharma wrote:
> > >
> > > Thanks. If I want to do at system-wide level, where do I store the
> > > psqlrc file (assuming I want to change the timing behavior system-wide)?
> >
> > > (CentOS 5, Postgres 8.4)
> > > $ ./pg_config --sysconfdir/opt/PostgreSQL/8.4/etc/postgresql
> >
> > > But I don't have /opt/PostgreSQL/8.4/etc/postgresql directory. Just
> > > creating the directory and putting a psqlrc file over there does not
> > > seem to work.
> >
> > I just tested it here on Ubuntu and it worked:
> >
> > $ sudo mkdir etc
> > $ sudo mkdir etc/postgresql
> > $ cd etc/postgresql/
> > $ sudo vi psqlrc
> > # add \echo test
> > $ pwd
> > /opt/PostgreSQL/8.4/etc/postgresql
> > $ ../../bin/psql -U postgres postgres
> > --> test
> > psql (8.4.2)
> > Type "help" for help.
> >
> > postgres=#
> >
> > > On a side-note, I observered that timing value in ~/.psqlrc was
> > > ignored by psql -c "..." command but not by echo "...."|psqlThought
> > > it was strange.
> >
> > Yeah, that is odd.
> >
> > --
> > Bruce Momjian <bruce@momjian.us> http://momjian.us
> > EnterpriseDB http://enterprisedb.com
> >
> > + None of us is going to be here forever. +
>
> _________________________________________________________________
> Hotmail is redefining busy with tools for the New Busy. Get more from your inbox.
> http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-07-06 15:29 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-05 17:00 Change to 'timing on' globally Balkrishna Sharma <b_ki@hotmail.com>
2010-07-05 17:10 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-07-05 19:40 ` Balkrishna Sharma <b_ki@hotmail.com>
2010-07-06 14:48 ` Bruce Momjian <bruce@momjian.us>
2010-07-06 15:20 ` Balkrishna Sharma <b_ki@hotmail.com>
2010-07-06 15:29 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox