Is a very high >1K queries per second metric normal?

[Chad Barnes]

We are running several Atlassian applications against a single MySQL server. We're seeing several thousand queries per second on a regular basis. In fact, the lowest count is 1200 q/s. It spikes at 3.7 q/s.

Breaking down the query log we have found that 62% of the total query traffic comes from 'Com_' queries, specifically: set, rollback, and commit commands.

All apps are tied into Crowd, which in turn is tied into our LDAP server. About 22,000 users are being synchronized from LDAP into Crowd, and subsequently into the other Atlassian apps (JIRA, Confluence, Fisheye, Bamboo).

From the graph of this behavior it seems to be associated with our 20-minute synchronization interval across the tools. It spikes for about that amount of time then settles back down to 1.2 Kq/s.

Is this normal behavior for the Atlassian tools? Does this metric affect performance of useful queries (like SELECT - 17% of the total query traffic)?

__ Questions ___________________________________________________________
Total           2.12G    1.4k/s
  Com_          1.31G   866.9/s  %Total:  61.92
  QC Hits     416.53M   275.0/s           19.64
  DMS         390.87M   258.0/s           18.43
  COM_QUIT    610.59k     0.4/s            0.03
  -Unknown    166.96k     0.1/s            0.01
Slow 10 s         129     0.0/s            0.00  %DMS:   0.00  Log: OFF
DMS           390.87M   258.0/s           18.43
  SELECT      360.03M   237.7/s           16.97         92.11
  UPDATE       30.53M    20.2/s            1.44          7.81
  INSERT      234.41k     0.2/s            0.01          0.06
  DELETE       71.78k     0.0/s            0.00          0.02
  REPLACE           0       0/s            0.00          0.00
Com_            1.31G   866.9/s           61.92
  set_option  711.73M   469.8/s           33.55
  rollback    312.11M   206.0/s           14.71
  commit      288.07M   190.2/s           13.58

[GuilhermeA · Accepted solution]

Hmm, you might be right. For a huge amount of use it could go up higher. But I believe that this should not give you problems ( if the machine were MySQL is hosted is robust). Otherwise we would see a lot of users complaining that Crowd can't handle large amount of users, which I have yet to see :)

From lots of customers using Crowd, few complained about performance, which turned out to be memory, so I believe that its unlikely to your MySQL to be the bottleneck for you.

Cheers!

Guilherme

[GuilhermeA]

Hi Chad,

All Atlassian products rely heavily on the database. For every action performed there is a select query. For every search or update there is another set of queries running. So I believe that this is a normal behavior FOR 22k users, as you said you have.

You can monitor this at non working hours, or when there is less users in the company. But to my belief its normal. Unless its affecting your performance.

Hope this helps!

Guilherme

[Chad Barnes]

Thanks. I'm not so much worried about the "real" work happening via SELECT/UPDATE/INSERT/etc. statements. It just seems unusual to have such a high percentage of Com_ overhead (set_option, rollback, commit).

It's traffic like the following that concerns me (but hasn't seemed to cause any issues):

610081 Query	update cwd_token set directory_id=294913, entity_name='JIMAPLES', rand...
		610081 Query	commit
		610081 Query	rollback
		610081 Query	SET autocommit=1
		610081 Query	SET autocommit=0
		610081 Query	select this_.id as id28_0_, this_.application_name as applicat2_28_...
		610081 Query	commit
		610081 Query	rollback
		610081 Query	SET autocommit=1
		610081 Query	SET autocommit=0
		610081 Query	commit
		610081 Query	rollback
		610081 Query	SET autocommit=1
		610081 Query	SET autocommit=0
		610081 Query	select this_.id as id28_0_, this_.application_name as applicat2_28_0_...
		610081 Query	commit
		610081 Query	rollback
		610081 Query	SET autocommit=1
		610081 Query	SET autocommit=0

I worry about scalability. If MySQL is serving 3 Kq/s now... what happens when we have heavy use of this system by the enterprise? Maybe nothing. Perhaps this is just the normal activity for a crowd-enabled installation.