|
How would you figure out what how many users were ever logged in at the same time? I am trying to come up with some testing scenarios for our software, and I know how many users there are total for a given site, and therefore how many possibly could be logged in at the same time. But I was wondering if there was a counter in the DB that I could query that kept track of how many actually ever were logged in at the same time historically. |
|
There is no property to tell you this information, but try using a connect event to count the number of users. For example: create table dba.max_connected_users ( on_date date, max_users int, primary key( on_date ) ); create event dba.Count_Max_Connected_Users type Connect handler begin declare @num int; declare @today date = today(); select count(*) into @num from sa_conn_info() where number < 1000000000; merge into max_connected_users as cv( on_date, max_users ) using ( select @today as on_date, @num as max_users ) nv on cv.on_date = nv.on_date when matched then update set cv.max_users = if cv.max_users < nv.max_users then nv.max_users else cv.max_users endif when not matched then insert; commit; end; Once this event is created it will insert/update the max_connected_users table to have one row per day indicating the maximum number of concurrently connected users on each day. What about... when matched and cv.max_users < nv.max_users then update when not matched then insert;
(21 Mar '12, 13:21)
Volker Barth
Replies hidden
Catch 22... sa_conn_info() lists connections on all databases, but the Connect event fires only on one database, and the max_connected_users exists on only one database.
(21 Mar '12, 13:53)
Breck Carter
Replies hidden
Yep, you are correct. The query that counts the number of connected users would need to be adjusted ... to count what you (as the developer) wants to count. As written it counts the number of users that are connected to the server (which is also what the server counts for its licensing checking... depending on your license type!). If you want to only count the current database then you need to add "AND dbnumber = ( select number from ( select number, db_name(number) as dbname from sa_db_list() where dbname = db_property('name') ) dt )"
(21 Mar '12, 14:24)
Mark Culp
Yep, that would work too.
(21 Mar '12, 14:29)
Mark Culp
FWIW, according to my understanding of license details (which might be inappropriate...), one could also count "different users" or connections from "different machines": Some applications will use several connections per user in parallel, say to separate read-only and write access. Therefore one might want to add some kind of grouping to the sa_conn_info() result, say by UserID or NodeAddr. Just my 2 cents.
(22 Mar '12, 04:32)
Volker Barth
IMHO, the - small - advantage would be that it
(22 Mar '12, 04:34)
Volker Barth
1
Duh! I figured there must be a function that gave the number of the current database but I didn't see it. Thanks.
(22 Mar '12, 08:20)
Mark Culp
Looks good, thanks Mark. I need to spend some more time with Mr. Merge. One of those things that came across as a new feature and I thought was neat but never really practiced.
(22 Mar '12, 09:41)
Siger Matt
Replies hidden
Sounds very common to me - "insert on existing" still looks way easier:)
(22 Mar '12, 10:10)
Volker Barth
Comment Text Removed
|
|
An old-school solution for the maximum number of connections to this database... CREATE TABLE max_connected_users (
on_date DATE NOT NULL PRIMARY KEY,
max_connections INTEGER NOT NULL );
CREATE EVENT record_max_connections TYPE CONNECT HANDLER
BEGIN
INSERT max_connected_users
ON EXISTING SKIP
VALUES ( CURRENT DATE, 0 );
UPDATE max_connected_users
SET max_connections = GREATER ( max_connections, DB_PROPERTY ( 'ConnCount' ) )
WHERE on_date = CURRENT DATE;
END;
Note that the ConnCount database property counts more than just user connections - it includes event connections and external environment connections.
(21 Mar '12, 14:47)
Mark Culp
@Breck: "Old-school" - so you don't code MERGE all the day? :)
(22 Mar '12, 04:35)
Volker Barth
Replies hidden
Someday, I will code a MERGE...
(22 Mar '12, 10:56)
Breck Carter
|
|
You can have a look at the file sadiags.xml in this you find an entry maxconcurconn, as Mark stated: "...records the maximum concurrent client connections that was seen during the database server's up-time..." see also question: Any documentation for sadiags.xml? That's a nice hint - but how to analyze these numbers in the "maxconcurconn" node?
(Yes, my somewhat rash comment on your cited question - "self-documenting XML" - hits back...)
(22 Mar '12, 08:14)
Volker Barth
Replies hidden
My understanding is, that N is the number of concurrent connections, but maybe Mark can give use more insight
(22 Mar '12, 08:50)
Martin
2
In the above two posted examples the N="0" and N="1" specify the maximum number of concurrent connections and the D-list and M-list of numbers refer to the number of times that that maximum was hit in the previous days and months respectively. Note that the days and months are relative to the date specified at the top of the file in the S tag. HTH
(22 Mar '12, 09:20)
Mark Culp
@Mark: Thanks for the clarification! I should add, this excerpt is taken from a rarely-used test server on my box, and there were further entries with N > 1:)
(22 Mar '12, 10:09)
Volker Barth
|
