Dear Group,
We have a java front-end for postgresql 3.4. When
we monitor the system usage using top we find couple of postmasters
taking up close to 90% of the CPU time in total. I would like to know
which are the queries that are currently running at this point of time.
How do we do this?? Another thing that I have noticed is that when I
give df -h, I find there is a partition /dev/shm which is somewhere in
the range of about 128MB and never used. What is this supposed to be.
Is it being not used a good sign or am I loosing on my performance? How
do we get it to be used by the database if it will help improve the
performance. Thanks in advance.

Yours sincerely,
Shan.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Shanmugasundaram Doraisamy wrote:[color=blue]
> Dear Group,
> We have a java front-end for postgresql 3.4.[/color]

Hopefully 7.3.4, otherwise upgrade :-)
[color=blue]
> When
> we monitor the system usage using top we find couple of postmasters
> taking up close to 90% of the CPU time in total. I would like to know
> which are the queries that are currently running at this point of time.
> How do we do this??[/color]

Read the "monitoring activity" chapter for full details, but if you have
statistics gathering turned on try selecting from pg_stat_activity. You
might also be able to see backend status with ps / top set to show the
whole command-line.
[color=blue]
> Another thing that I have noticed is that when I
> give df -h, I find there is a partition /dev/shm which is somewhere in
> the range of about 128MB and never used. What is this supposed to be.
> Is it being not used a good sign or am I loosing on my performance? How
> do we get it to be used by the database if it will help improve the
> performance.[/color]

Almost certainly shared-mem (you don't say what system you're on) and it
will be used, regardless of what df says - increase the settings in your
postgresql.conf beyond 128MB and you'll see postgresql fail to start.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Hi Shan,
please, check your postgresql.conf and find "shared_buffers" and
"log_statement". I am not 100% sure, if statements are logged before
executing.

Regards,
pajout

Shanmugasundaram Doraisamy wrote:
[color=blue]
> Dear Group,
> We have a java front-end for postgresql 3.4.
> When we monitor the system usage using top we find couple of
> postmasters taking up close to 90% of the CPU time in total. I would
> like to know which are the queries that are currently running at this
> point of time. How do we do this?? Another thing that I have noticed
> is that when I give df -h, I find there is a partition /dev/shm which
> is somewhere in the range of about 128MB and never used. What is this
> supposed to be. Is it being not used a good sign or am I loosing on
> my performance? How do we get it to be used by the database if it will
> help improve the performance. Thanks in advance.
>
> Yours sincerely,
> Shan.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Dear Richard,
Thankyou for you immediate reply, I did set
the stats_command_string=true and tried select * from pg_stat_activity;
It gave me the desired result. In the mean while you had mentioned
about reading the monitoring activity chapter. Please tell me where I
could find it ( the website address). Thanking you,

Yours sincerely,
Shan.

Richard Huxton wrote:
[color=blue]
> Shanmugasundaram Doraisamy wrote:
>[color=green]
>> Dear Group,
>> We have a java front-end for postgresql 3.4.[/color]
>
>
> Hopefully 7.3.4, otherwise upgrade :-)
>[color=green]
> > When[/color]
>[color=green]
>> we monitor the system usage using top we find couple of postmasters
>> taking up close to 90% of the CPU time in total. I would like to
>> know which are the queries that are currently running at this point
>> of time. How do we do this??[/color]
>
>
> Read the "monitoring activity" chapter for full details, but if you
> have statistics gathering turned on try selecting from
> pg_stat_activity. You might also be able to see backend status with ps
> / top set to show the whole command-line.
>[color=green]
> > Another thing that I have noticed is that when I[/color]
>[color=green]
>> give df -h, I find there is a partition /dev/shm which is somewhere
>> in the range of about 128MB and never used. What is this supposed to
>> be. Is it being not used a good sign or am I loosing on my
>> performance? How do we get it to be used by the database if it will
>> help improve the performance.[/color]
>
>
> Almost certainly shared-mem (you don't say what system you're on) and
> it will be used, regardless of what df says - increase the settings in
> your postgresql.conf beyond 128MB and you'll see postgresql fail to
> start.
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Dear Jan Poslusny,
Thank you for you immediate reply, in
regards to the shared_buffers, what is the optimum value and how do I
calculate it. Thanking you,

Yours sincerely,
Shan.

Jan Poslusny wrote:
[color=blue]
> Hi Shan,
> please, check your postgresql.conf and find "shared_buffers" and
> "log_statement". I am not 100% sure, if statements are logged before
> executing.
>
> Regards,
> pajout
>
> Shanmugasundaram Doraisamy wrote:
>[color=green]
>> Dear Group,
>> We have a java front-end for postgresql 3.4.
>> When we monitor the system usage using top we find couple of
>> postmasters taking up close to 90% of the CPU time in total. I would
>> like to know which are the queries that are currently running at this
>> point of time. How do we do this?? Another thing that I have
>> noticed is that when I give df -h, I find there is a partition
>> /dev/shm which is somewhere in the range of about 128MB and never
>> used. What is this supposed to be. Is it being not used a good sign
>> or am I loosing on my performance? How do we get it to be used by the
>> database if it will help improve the performance. Thanks in advance.
>>
>> Yours sincerely,
>> Shan.
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>[/color][/color]

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Shanmugasundaram Doraisamy wrote:[color=blue]
> Dear Richard,
> Thankyou for you immediate reply, I did set the
> stats_command_string=true and tried select * from pg_stat_activity;
> It gave me the desired result. In the mean while you had mentioned
> about reading the monitoring activity chapter. Please tell me where I
> could find it ( the website address). Thanking you,[/color]

All the manuals are online at http://www.postgresql.org/docs/

If you're running version 7.4 you'll want "Chapter 23. Monitoring
Database Activity"

You probably have a copy on your server too. Where depends on how you
installed, but the RedHat RPMs put documents in /usr/share/doc/...

Also, you'll want:
http://techdocs.postgresql.org/
Various user-submitted notes and guides.
http://www.postgresql.org/lists.html
Mailing list archives (especially check the announcements list)
http://www.varlena.com/GeneralBits/
Elein's journal, with contributions from other community members.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend