I need to calculate the peak number of users we have on our web application on a given day.

i can calculate the total number of users on a given day no problem with a MySQL DISTINCT clause, but not sure how to work out peak concurrent users for a given day.

There is a session log which is updated as users login and logout of the system (with an auto logoff).

*note all sessions are automatically terminated at 10pm for overnight processing so no sessions run from one day to the next.

The key fields in the MySQL session log table are.

sessionId - unsigned int - auto increment

userId - unsigned int

loginTime - timestamp

logoutTime - timestamp

Thanks in advance for your help.

You have said that some of the columns in your session log table are -

1. sessionId - unsigned int - auto increment

2. userId - unsigned int

3. loginTime - timestamp

4. logoutTime - timestamp

and it might take quite a bit of statistical math analysis in a PHP function to go through this table at the end of the day and find "peak concurrent users for a given day", at least that's my view without actually trying it. I would think you would need to have math in a function to sort the user sessions, determine which were running at the same time, and apply some sort of "overlap" filter (a math formula for sort and differentiation) to see how many users were log-in at the same time, as johanntagle said in a post above "It's like plotting each row in as a bar graph and finding the which time period has the most graphs crossing it" , , not so simple, and my mind can not come up with a plan to do this, the main problem in my thinking is that there are so many time blocks (lets say you try a "10 Minute" or "One Hour" time block) to analyze for your "each row in as a bar graph" for all the users that day at many different times. As A Note, I have gotten help with math problems in my programming in the Math area here at EE.

I think it more simple to keep a running count evaluation in your table, counting the amount for number of users logged-In for each "time block" at that time. What I would try for testing, is to add five columns to your session log table -

5. loggedIn - unsigned int // holds the number of users logged in, starts at zero

6. overMax - boolean // true if users logged in is above Max

7. highOver - unsigned int // highest number in loggedIn while overMax is true

8. beginMax - timestamp

9. endMax - timestamp

when a user successfully logs in, I guess you already update the sessionId, userId, loginTime fields. What you could also do is to update the loggedIn to increase by one, for each new log in, if the boolean overMax is false, test the new increased loggedIn number to see if it is above the MAX for concurrent users, if it is above the MAX then update the overMax to true, and update the highOver to the value of the new loggedIn number, and update the beginMax to the current timestamp. As soon as the overMax was set to true, I would write this date time information to a log file -

beginMax:5-7-2011-14-32

if the overMax is true on login, test the highOver against the new increased loggedIn number, if higher, replace the highOver with the higher loggedIn number

When a user logs out (or time out), update the loggedIn to decrease by one, then see if the overMax is true, if it is true and the new decreased loggedIn is now below the MAX, update the overMax to false and set the endMax to the current timestamp. As soon as the overMax was set to false, I would write this date time and highOver information to a log file -

endMax:5-7-2011-14-56:high

someone then can read the log file to see if and how many times the number of current users was over the max. I suppose there are ways to parse and analyze the log file to get info you need without having a person read it, but thats more than I want to think about now.

As in all programming there's more than one way to solve a problem, this is what I might try.