Thread: How to know server status variable in postgresql?
Hai, I am developing a java application for performance counter. For that i want to collect all server status counter names with current value. i just did it for MySQl by, *"SHOW GLOBAL STATUS"*. is their any query similar this to collect those details from the database. I am new to postgresql, so i dont know about it well. So if you know help me friends. Thanks in advance -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 1/7/2014 4:33 AM, ambilalmca wrote: > Hai, I am developing a java application for performance counter. For that i > want to collect all server status counter names with current value. i just > did it for MySQl by, *"SHOW GLOBAL STATUS"*. is their any query similar this > to collect those details from the database. I am new to postgresql, so i > dont know about it well. So if you know help me friends. there's no such single source for 'all server status counters'. there's a whole pile of different pg_catalog.pg_stat_*** tables with different sorts of information, for each database. the best script I've seen for pulling monitoring data out of postgres is check_postgres from the Bucardo folks, this is designed to work with a Nagios/Cacti type monitoring system.... its actually a perl script, you run it repeatedly giving it specific things you want to look at (for instance, size and name of largest tables, or total database size, or number of active connections, etcetcetc). -- john r pierce 37N 122W somewhere on the middle of the left coast
On Tue, Jan 7, 2014 at 8:33 PM, ambilalmca <ambilalmca@gmail.com> wrote:
Hai, I am developing a java application for performance counter. For that i
want to collect all server status counter names with current value. i just
did it for MySQl by, *"SHOW GLOBAL STATUS"*.
What details do you want to collect? That command in MySQL may be giving you few counters, but which of those are of your interest?
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Attachment
I want to collect, *Connections. * Current connections. The number of currently open connections. Connections executing requests. The number of currently open connections that are executing requests. Idle connections. The number of currently idle connections. Max connections. The maximum number of concurrent connections to the database server. Used connections. Connections used as a percentage of maximum connections. *Buffers. * Shared buffers size. Current size of shared buffers. Disk cache size. Current size of disk cache buffer. Sort buffer size. Current size of sort buffer. Work buffer size. Current size of working requests buffer. Temp buffer size. Current size of temporary buffer. *IO Requests.* Blocks read. Number of blocks directly read on disk. For optimal performance this value should be the smallest possible. If the database has to execute too many disk accesses, performance will suffer. Index blocks read. Number of index blocks directly read on disk. % Index blocks read. Percentage of index blocks directly read on disk. Sequence blocks read. Number of sequence blocks directly read on disk. *Cache* Blocks read. Number of cached blocks read. Index blocks read. Number of cached index blocks read. % Index blocks read. Percentage of cached index blocks read. For optimal performance, this value must be as large as possible. If an insufficient number of index blocks are declared in the table, it could negatively impact the database server performance. Sequence blocks read. Number of cached sequence blocks read. *Index*. Index read. Number of reads initiated by an index. Indexed rows read. Number of rows read by indexed requests. Indexed rows fetched. Number of live rows fetched by indexed requests. *Command Rates.* Rows read. Number of rows read. Rows fetched. Number of rows fetched. Inserted rows. Number of rows inserted. Updated rows. Number of rows updated. Deleted rows. Number of rows deleted. Committed transactions. Number of committed transactions. This value should be relatively stable, indicating that there are no performance-reducing load peaks. If applications do not commit often enough, it will lead to an overload on the database server. Rolled back transactions. Number of transactions rolled back. % Rolledback transactions. Percentage of transactions rolled back. *Locks. * Locks waiting. Number of locks waiting. Locks held. Number of locks held. Process holding locks. Number of processes holding locks. how to collect these details by using query. now i find queries for sonnections. but i dont know anout others. please help me.@Sameer Kumar -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5785833.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Wed, Jan 8, 2014 at 7:22 PM, ambilalmca <ambilalmca@gmail.com> wrote:
I want to collect,
*Connections. *
Current connections. The number of currently open connections.
Connections executing requests. The number of currently open connections
that are executing requests.
Idle connections. The number of currently idle connections.
Max connections. The maximum number of concurrent connections to the
database server.
Used connections. Connections used as a percentage of maximum connections.
*Buffers. *
Shared buffers size. Current size of shared buffers.
Disk cache size. Current size of disk cache buffer.
Sort buffer size. Current size of sort buffer.
Work buffer size. Current size of working requests buffer.
Temp buffer size. Current size of temporary buffer.
*IO Requests.*
Blocks read. Number of blocks directly read on disk.
For optimal performance this value should be the smallest possible. If the
database has to execute too many disk accesses, performance will suffer.
Index blocks read. Number of index blocks directly read on disk.
% Index blocks read. Percentage of index blocks directly read on disk.
Sequence blocks read. Number of sequence blocks directly read on disk.
*Cache*
Blocks read. Number of cached blocks read.
Index blocks read. Number of cached index blocks read.
% Index blocks read. Percentage of cached index blocks read.
For optimal performance, this value must be as large as possible. If an
insufficient number of index blocks are declared in the table, it could
negatively impact the database server performance.
Sequence blocks read. Number of cached sequence blocks read.
*Index*.
Index read. Number of reads initiated by an index.
Indexed rows read. Number of rows read by indexed requests.
Indexed rows fetched. Number of live rows fetched by indexed requests.
*Command Rates.*
Rows read. Number of rows read.
Rows fetched. Number of rows fetched.
Inserted rows. Number of rows inserted.
Updated rows. Number of rows updated.
Deleted rows. Number of rows deleted.
Committed transactions. Number of committed transactions.
This value should be relatively stable, indicating that there are no
performance-reducing load peaks. If applications do not commit often enough,
it will lead to an overload on the database server.
Rolled back transactions. Number of transactions rolled back.
% Rolledback transactions. Percentage of transactions rolled back.
*Locks. *
Locks waiting. Number of locks waiting.
Locks held. Number of locks held.
Process holding locks. Number of processes holding locks.
how to collect these details by using query. now i find queries for
sonnections. but i dont know anout others. please help me.@Sameer Kumar
Are you building your own scripts for monitoring the database?
Are are open source plug-ins available for that. Anyways, take a look at this documentation:
You will find most of the things you have asked for (I guess all of it). If you are not able to find something then you can post here.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5785833.htmlSent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
oh thanks @Sameer Kumar
Thanks & Regards,
A.Mohamed Bilal
On Thu, Jan 9, 2014 at 12:50 PM, Sameer Kumar [via PostgreSQL] <[hidden email]> wrote:
On Wed, Jan 8, 2014 at 7:22 PM, ambilalmca <[hidden email]> wrote:I want to collect,
*Connections. *Current connections. The number of currently open connections.*Buffers. *
Connections executing requests. The number of currently open connections
that are executing requests.
Idle connections. The number of currently idle connections.
Max connections. The maximum number of concurrent connections to the
database server.
Used connections. Connections used as a percentage of maximum connections.*IO Requests.*
Shared buffers size. Current size of shared buffers.
Disk cache size. Current size of disk cache buffer.
Sort buffer size. Current size of sort buffer.
Work buffer size. Current size of working requests buffer.
Temp buffer size. Current size of temporary buffer.*Cache*
Blocks read. Number of blocks directly read on disk.
For optimal performance this value should be the smallest possible. If the
database has to execute too many disk accesses, performance will suffer.
Index blocks read. Number of index blocks directly read on disk.
% Index blocks read. Percentage of index blocks directly read on disk.
Sequence blocks read. Number of sequence blocks directly read on disk.*Index*.
Blocks read. Number of cached blocks read.
Index blocks read. Number of cached index blocks read.
% Index blocks read. Percentage of cached index blocks read.
For optimal performance, this value must be as large as possible. If an
insufficient number of index blocks are declared in the table, it could
negatively impact the database server performance.
Sequence blocks read. Number of cached sequence blocks read.*Command Rates.*
Index read. Number of reads initiated by an index.
Indexed rows read. Number of rows read by indexed requests.
Indexed rows fetched. Number of live rows fetched by indexed requests.*Locks. *
Rows read. Number of rows read.
Rows fetched. Number of rows fetched.
Inserted rows. Number of rows inserted.
Updated rows. Number of rows updated.
Deleted rows. Number of rows deleted.
Committed transactions. Number of committed transactions.
This value should be relatively stable, indicating that there are no
performance-reducing load peaks. If applications do not commit often enough,
it will lead to an overload on the database server.
Rolled back transactions. Number of transactions rolled back.
% Rolledback transactions. Percentage of transactions rolled back.
Locks waiting. Number of locks waiting.
Locks held. Number of locks held.
Process holding locks. Number of processes holding locks.
how to collect these details by using query. now i find queries for
sonnections. but i dont know anout others. please help me.@Sameer KumarAre you building your own scripts for monitoring the database?Are are open source plug-ins available for that. Anyways, take a look at this documentation:You will find most of the things you have asked for (I guess all of it). If you are not able to find something then you can post here.--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5785833.htmlSent from the PostgreSQL - general mailing list archive at Nabble.com.Sent via pgsql-general mailing list ([hidden email])
--http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5786022.htmlIf you reply to this email, your message will be added to the discussion below:
View this message in context: Re: How to know server status variable in postgresql?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
@ sameer khan, i got query for all except *Number of cached blocks read, Number of cached index blocks read, Number of cached sequence blocks read*. can you tell query for these three counters only? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5786228.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, Jan 10, 2014 at 2:57 PM, ambilalmca <ambilalmca@gmail.com> wrote:
@ sameer khan,
That's Sameer Kumar :-)
i got query for all except
*Number of cached blocks read,
check pg_stat_all_tables
Number of cached index blocks read,
check pg_stat_all_indexes
Number of cached sequence blocks read*.
Why do you need this info?
can you tell query for these three counters only?
--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5786228.htmlSent from the PostgreSQL - general mailing list archive at Nabble.com.Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
--
because that is also one of the important counter to know postgresql server status. thats why i am asking @sameer
Thanks & Regards,
A.Mohamed Bilal
On Sat, Jan 11, 2014 at 7:15 PM, Sameer Kumar [via PostgreSQL] <[hidden email]> wrote:
On Fri, Jan 10, 2014 at 2:57 PM, ambilalmca <[hidden email]> wrote:@ sameer khan,That's Sameer Kumar :-)i got query for all except*Number of cached blocks read,check pg_stat_all_tablesNumber of cached index blocks read,check pg_stat_all_indexesNumber of cached sequence blocks read*.Why do you need this info?can you tell query for these three counters only?Sent via pgsql-general mailing list ([hidden email])
--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5786228.htmlSent from the PostgreSQL - general mailing list archive at Nabble.com.
--If you reply to this email, your message will be added to the discussion below:http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5786458.html
View this message in context: Re: How to know server status variable in postgresql?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
@sameer, can you tell me the full query for that? because in pg_stat_all_tables contains many fields. i dont know whats the correct one to get the result.
Thanks & Regards,
A.Mohamed Bilal
On Wed, Jan 15, 2014 at 10:57 AM, Mohamed Bilal <[hidden email]> wrote:
because that is also one of the important counter to know postgresql server status. thats why i am asking @sameerThanks & Regards,A.Mohamed BilalOn Sat, Jan 11, 2014 at 7:15 PM, Sameer Kumar [via PostgreSQL] <[hidden email]> wrote:On Fri, Jan 10, 2014 at 2:57 PM, ambilalmca <[hidden email]> wrote:@ sameer khan,That's Sameer Kumar :-)i got query for all except*Number of cached blocks read,check pg_stat_all_tablesNumber of cached index blocks read,check pg_stat_all_indexesNumber of cached sequence blocks read*.Why do you need this info?Sent via pgsql-general mailing list ([hidden email])can you tell query for these three counters only?
--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5786228.htmlSent from the PostgreSQL - general mailing list archive at Nabble.com.
--If you reply to this email, your message will be added to the discussion below:http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5786458.html
View this message in context: Re: How to know server status variable in postgresql?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Wed, Jan 15, 2014 at 7:36 AM, ambilalmca <ambilalmca@gmail.com> wrote: > can you tell me the full query for that? because in pg_stat_all_tables contains many fields. i dont know whats the correctone to get the result. >>>> *Number of cached blocks read, >>>> Number of cached index blocks read, They're in pg_statio_all_tables, it's all documented at: http://www.postgresql.org/docs/current/static/monitoring-stats.html Regards, Marti