Thread: Finding number of current connections
I posted this message to the admin list, but just realized maybe it belongs in the general list. Sorry for double posting! I need to find a way to determine how many concurrent connections are being made to the database server at any given time. I'm coming from administering a MySQL database and am familiar with the following command: mysqladmin -uroot -p processlist This lists something like this: +-----+------+-----------+----+---------+------+------------------+ | Id | User | Host | db | Command | Time | Info | +-----+------+-----------+----+---------+------+------------------+ | 307 | root | localhost | | Sleep | 0 | | | 308 | root | localhost | | Sleep | 0 | | | 309 | root | localhost | | Query | 0 | show processlist | +-----+------+-----------+----+---------+------+------------------+ User's on the system can also get a listing of only their own process list and not see anyone else's processes by simply using their own username and password. I've looked through the FAQ and manual, but have not been able to find an answer to this. Is there some pgsql command that will provide this information. I need both a Full Listing from an administrative viewpoint and a Per User listing. If there is not a tool to do this, how difficult would it be to build a tool to obtain this information? Or is there simply no way to extract this information from pgsql? Thanks! Tauren ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
"Tauren Mills" <tauren@servlets.net> writes:
> If there is not a tool to do this, how difficult would it be to build a tool
> to obtain this information? Or is there simply no way to extract this
> information from pgsql?
Coming soon to a 7.2 near you:
regression=# select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename | current_query
--------+------------+---------+----------+----------+---------------------------------
400867 | regression | 29709 | 256 | postgres | select * from pg_stat_activity;
400867 | regression | 452 | 256 | postgres |
(2 rows)
(the second backend doesn't have collect_querystring turned on, so it's
not reporting its current query...)
Hopefully, Jan will get around to writing some documentation about
this before release ;-)
regards, tom lane
Thanks, Tom! I assume the fact you only mention 7.2 means the current versions have no way to determine this. Will 7.2 also be able to let you control how many connections each user is using? For instance, will I be able to limit one user to 5 database connections and another one to 10? Or will it only report how many are in use? Thanks, Tauren > Coming soon to a 7.2 near you: > > regression=# select * from pg_stat_activity; > datid | datname | procpid | usesysid | usename | > current_query > --------+------------+---------+----------+----------+------------ > --------------------- > 400867 | regression | 29709 | 256 | postgres | select * > from pg_stat_activity; > 400867 | regression | 452 | 256 | postgres | > (2 rows) > > (the second backend doesn't have collect_querystring turned on, so it's > not reporting its current query...) > > Hopefully, Jan will get around to writing some documentation about > this before release ;-) > > regards, tom lane
Tauren Mills wrote:
> I posted this message to the admin list, but just realized maybe it belongs
> in the general list. Sorry for double posting!
>
> I need to find a way to determine how many concurrent connections are being
> made to the database server at any given time. I'm coming from
> administering a MySQL database and am familiar with the following command:
>
> mysqladmin -uroot -p processlist
>
> This lists something like this:
>
> +-----+------+-----------+----+---------+------+------------------+
> | Id | User | Host | db | Command | Time | Info |
> +-----+------+-----------+----+---------+------+------------------+
> | 307 | root | localhost | | Sleep | 0 | |
> | 308 | root | localhost | | Sleep | 0 | |
> | 309 | root | localhost | | Query | 0 | show processlist |
> +-----+------+-----------+----+---------+------+------------------+
Among alot more statistics down to number of rows returned by
scans, this will be added in v7.2:
pgsql=# select * from pg_stat_database;
datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit
-------+-----------+-------------+-------------+---------------+-----------+----------
1 | template1 | 0 | 0 | 0 | 0 | 0
19030 | template0 | 0 | 0 | 0 | 0 | 0
19070 | pgsql | 3 | 0 | 0 | 0 | 0
19093 | wieck | 0 | 0 | 0 | 0 | 0
(4 rows)
pgsql=# select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename | current_query
-------+---------+---------+----------+---------+---------------
19070 | pgsql | 1576 | 501 | pgsql |
19070 | pgsql | 1578 | 501 | pgsql |
19070 | pgsql | 1580 | 501 | pgsql |
(3 rows)
The numbers are mostly zero because I haven't activated the
collection of statistics in the config file.
Approximately what you're looking for?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Tom Lane wrote:
> Hopefully, Jan will get around to writing some documentation about
> this before release ;-)
Nag, nag, nag ;-P
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
"Tauren Mills" <tauren@servlets.net> writes:
> Will 7.2 also be able to let you control how many connections each user is
> using?
No, there's no present plans for that.
regards, tom lane
On Wed, Aug 01, 2001 at 10:11:22AM -0400, Tom Lane wrote: > "Tauren Mills" <tauren@servlets.net> writes: > > Will 7.2 also be able to let you control how many connections each user is > > using? > > No, there's no present plans for that. So you can't just do CREATE CONSTRAINT on that table and have it work? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > It would be nice if someone came up with a certification system that > actually separated those who can barely regurgitate what they crammed over > the last few weeks from those who command secret ninja networking powers.
Hi Nils, Thanks for this information! I should be able to use this to create a script that will solve my needs. Does anyone know (Tom?) if this will still work when 7.2 is out? Or are there changes to the basic server structure and what 'ps' reports? Tauren > -----Original Message----- > From: Nils O. Selåsdal [mailto:noselasd@frisurf.no] > Sent: Wednesday, August 01, 2001 8:01 PM > To: Tauren Mills; pgsql-general@postgresql.org > Subject: RE: [GENERAL] Finding number of current connections > > > On Wednesday 01 August 2001 07:53 am, Tauren Mills wrote: > > Thanks, Tom! > > > > I assume the fact you only mention 7.2 means the current > versions have no > > way to determine this. > Well, postgresql starts one process per connection so you could > see how many > postmasters that are running(minus one for the 1. server..) > > On my linux machine, the -f and -l switch to 'ps'comes handy: > [noselasd@space noselasd]$ ps -efl |grep postmast |grep -v grep > 000 S postgres 873 1 0 69 0 - 1541 do_sel 02:51 ? > 00:00:00 /usr/bin/postmaster -D /var/lib/pgsql/data > 040 S postgres 1356 873 0 69 0 - 1697 unix_s 03:53 ? > 00:00:00 postgres: noselasd noselasd [local] idle > > 1 connection here, user noselasd conected to the 'noselasd' database. > > -- > Nils O. Selåsdal
On Wednesday 01 August 2001 07:53 am, Tauren Mills wrote: > Thanks, Tom! > > I assume the fact you only mention 7.2 means the current versions have no > way to determine this. Well, postgresql starts one process per connection so you could see how many postmasters that are running(minus one for the 1. server..) On my linux machine, the -f and -l switch to 'ps'comes handy: [noselasd@space noselasd]$ ps -efl |grep postmast |grep -v grep 000 S postgres 873 1 0 69 0 - 1541 do_sel 02:51 ? 00:00:00 /usr/bin/postmaster -D /var/lib/pgsql/data 040 S postgres 1356 873 0 69 0 - 1697 unix_s 03:53 ? 00:00:00 postgres: noselasd noselasd [local] idle 1 connection here, user noselasd conected to the 'noselasd' database. -- Nils O. Selåsdal