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.

asked 21 Mar '12, 11:52

Siger%20Matt's gravatar image

Siger Matt
3.1k486493
accept rate: 13%

edited 15 Mar '13, 21:18

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266


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.

permanent link

answered 21 Mar '12, 13:12

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266
accept rate: 40%

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

  • is shorter and
  • would only do an "update" if there's need to.
(22 Mar '12, 04:34) Volker Barth
2

Shorter form: "AND dbnumber = DB_ID()"

(22 Mar '12, 05:13) Arthoor
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
showing 3 of 10 show all flat view

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;
permanent link

answered 21 Mar '12, 14:07

Breck%20Carter's gravatar image

Breck Carter
27.3k424585836
accept rate: 21%

edited 21 Mar '12, 14:09

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... alt text

(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?

permanent link

answered 22 Mar '12, 06:05

Martin's gravatar image

Martin
8.6k115149237
accept rate: 14%

That's a nice hint - but how to analyze these numbers in the "maxconcurconn" node?

<F N="0" D="0" M="1,6,0,2,0,0,1,0,1,0,0,0,0,1,0,0,0,0,2,0,1" />
<F N="1" D="1,1" M="2,3,3,2,5,0,2,0,0,1,1,3,1,1,0,0,1,4,7,1,1,1,1" />

(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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×17
×5
×5

question asked: 21 Mar '12, 11:52

question was seen: 1,801 times

last updated: 15 Mar '13, 21:18