Thread: How to identify which query is running - reg.
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.
Shanmugasundaram Doraisamy wrote: > Dear Group, > We have a java front-end for postgresql 3.4. Hopefully 7.3.4, otherwise upgrade :-) > 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?? 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. > 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. 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
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: > 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) >
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:
> Shanmugasundaram Doraisamy wrote:
>
>> Dear Group,
>> We have a java front-end for postgresql 3.4.
>
>
> Hopefully 7.3.4, otherwise upgrade :-)
>
> > 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??
>
>
> 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.
>
> > 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.
>
>
> 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.
>
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:
> 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:
>
>> 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: > 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, 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