Friday, June 14, 2013

List the active connection/user in PostgreSQl,MySQL & mongoDB

As a DBA have to ensure number of active connection in database & which query consuming longer time to execute.

In PostgreSQL we can query from "pg_stat_activity" to know the status of the each connection.

when I was working in mongoDB(no-SQL) , wanted  to monitor the client  who currently accessing my server & the list active of operation.

here simple comparison,  how we to get  number of  active client (remote / local i.e different user ) connected to databases ( PostgreSQL,MySQL and mongoDB ) server.


PostgreSQL
----------------

select* from pg_stat_activity
postgres=# select datname, usename, application_name, client_addr,client_port,query_start,current_query from pg_stat_activity
 +------+---------+------------------------------+-------+---------+------+-------+------------------+
|datname  |usename   | application_name         | client_addr  | client_port | query_start 
+------+---------+------------------------------+-------+---------+------+-------+------------------+
template1 | postgres | pgAdmin III - Browser    | 127.0.0.1    |       56019 | 2013-06-14 19:52:52.775344+05:30
 SAAS     | postgres | psql                     | 192.168.1.10 |       58776 | 2013-06-14 19:58:33.084661+05:30
 koha     | postgres | pgAdmin III - Browser    | 192.168.0.2  |       56020 | 2013-06-14 19:52:52.729838+05:30
 pis      | postgres | pgAdmin III - Query Tool | 127.0.0.1    |       56022 | 2013-06-14 19:56:21.729744+05:30-
   - It display the list of active user/client ip address and query details.

Mysql
----------


show processlist; 
mysql> show processlist;
+------+---------+------------------------------+-------+---------+------+-------+------------------+
| Id   | User    | Host                         | db    | Command | Time | State | Info             |
+------+---------+------------------------------+-------+---------+------+-------+------------------+
| 3832 | root    | localhost                    | mysql | Query   |    0 | NULL  | show processlist |
| 3834 | erbnext | localhost                    | NULL  | Sleep   | 2524 |       | NULL             |
| 3837 | root    | solaimurugan.chennai.in:36125| koha  | Sleep   |    3 |       | NULL             |
+------+---------+------------------------------+-------+---------+------+-------+------------------+
3 rows in set (0.00 sec) 

it states that 3 active connections, 2 from localhost & 1 from client solaimurugan.chennai.in & it connects database mysql & koha.


mongoDB
------------



db.currentOp(true)
root@boss[bin]#./mongo  
MongoDB shell version: 2.2.3
-----------------------------------------------------------------------------------------------------------------------
db.currentOp(true).inprog.forEach(function(d){if(d.client && d.client!="0.0.0.0:0")printjson(d.client)})
----------------------------------------------------------------------------------------------------------------------
"solaimurugan.chennai.in:56231"
"127.0.0.1:49563" 
"192.168.31.101:50132" 
-------------------------------------------------------------------------------------------------------------------------------------

 mongoDB also has command to view total number of current connection & available connection to be established by client


db.serverStatus()

root@boss[bin]#./mongo  
MongoDB shell version: 2.2.3
-----------------------------------------------------------------------------------------------------------------------
db.serverStatus().connections
-----------------------------------------------------------------------------------------------------------------------------
{ "current" : 3, "available" : 816 }
------------------------------------------------------------------------------------------------------------------------------


No comments: