private chats [#74366]

I was just checking the MySQL Usage of my site on DreamHost(my web host)'s control panel. I found something interesting. Here is a little table:

Day Disk Usage Connects Queries (Conueries) (Ratio)
2006-07-13 43.324MB 8752 1530801 1.750MCn 6.996
2006-07-14 44.672MB 10485 1815631 2.078MCn 6.927
2006-07-15 46.316MB 182047 3811995 8.363MCn 0.838
2006-07-16 49.434MB 258103 3878290 10.331MCn 0.601

The point to note here is the "Ratio", here is a quote of what it is:

What does that "ratio" mean on the mysql usage page?

The ratio column gives you the number of queries divided by 25 times the number of connects (queries / (connects * 25)), and is an indicator of whether you're using a disproportionate number of database connections.

A value of 1 is ideal (meaning 1 connect for every 25 queries). Ratios less than one mean you're using less than 25 queries per connection, an indication of either poor connection management or a particularly simple database. Generally, the higher the ratio, the better your database connection optimization (which is a good thing)

July 15th is the time I first started using the chatroom module. As you can see, the number of connections and queries exploded, and the ratio dropped like a stone.

I kinda expected chatroom would be somewhat resource intensive(I think I saw a warning somewhere), but I'm wondering if any optimization can be done to ease the burden a bit.

Comments

Comment #1

Anonymous (not verified) commented 18 July 2006 at 23:21

Assigned:

Unassigned

hi luperry,

while it may seem counterintuitive at first, the ratio is dropping because of optimisation, and not in spite of it.

chatroomread.php is one big performance hack aimed at:

- avoiding any db queries all together. this is what all the checks on file modification time are. if the browser requests something with a timestamp thats not older than the timestamp for the data its requesting, then the request ends there. no drupal bootstrap, no db connection.

- if we have a cache miss, then chatroomread.php only performs a drupal session bootstrap. we need a db connection and access to the user object, but thats it. this is where the low ratio comes in: the connection is used only to perform a few select queries needed by the chatroom, and doesn't perform all the extra queries associated with a full drupal bootstrap.

make sense?

now, given that we perform a fairly expensive connection just to perform a small number of queries, it may be that investigating permanent connections might be the way to go.

this is database specific, however, so the performance gain might not be worth the extra complexity.

anyway, i hope that gives you a better understanding of what you are seeing.

cheers
justin

Comment #2

luperry commented 18 July 2006 at 23:37

So in other words, much of the queries are eliminated(but the connections are not), resulting a drop in ratio?

hmm... If that's the case, I guess there's nothing we can do about that in the short term.

thanks for the informative explanation.

Comment #3

moshe weitzman commented 19 July 2006 at 02:39

justin - based on your description of chatread, it seems we cannot easily use the regular node_access system to protect private chat nodes. is that true? thats OK, but we might mention in the README that anyone can read any chat (unless you have some other room access control) ... you might not have ventured into this part of drupal yet. if not, i can help orient you.

Comment #4

Anonymous (not verified) commented 19 July 2006 at 03:18

hi moshe,

yes, that's right, kind of. right now, chats are not nodes at all, but when they do become nodes, we won't be able to use the 'normal' mechanisms. private chats really haven't been implemented at all yet.

at the moment there is only user_access('access chatrooms') call.

the complexity here is avoiding a drupal bootstrap while allowing for fine grained access control, and recognising that we need to control access to the loading of the UI for a chat and to chat messages, which can be accessed without loading UI.

my (as yet unimplemented) idea is to only allow access to a chats messages where there is a row in the chatroom_online_list table for the request session id for a given chat. checking for this wont require a full bootstrap, so we can run this check on all cache misses without killing the db.

the row can be written when loading the chat UI, which is full page load anyway. my intention is to provide hooks at this stage to allow modules to control what gets written to the online list, and a default simple set of permission control provided by the chatroom module.

on a related note, implementing admin features during a running chat (like being able to kick users out of an active chat) will require either another table or an ajax request that does a full page load, or some other combination, because we'll need to validate that an admin kick request has more rights than just access to the chat.

does that make any sense? feedback much appreciated.

cheers
justin

Comment #5

moshe weitzman commented 24 July 2006 at 19:58

Title:

database connection optimization

» private chats

justin and i have dicussed a mechanism for private chats whereby users would request chatread with a special token on querystring. the token is a hash of the user's sessionID and chatID.

somewhat related is the nice campfire chat feature where a secret url exists that guests may use to access the chat. that url probably changes every day and on demand. not sure how they do it.