Sfrgttk

We're running locally managed databases for our Atlassian suite (Jira/Confluence/Fisheye). I noticed something strange in the slow query logs. There are a bunch of selects of session variables showing up like

# Schema: fisheyedb  Last_errno: 0  Killed: 0

# Query_time: 2.487396  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0  Rows_affected: 0

# Bytes_sent: 90  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0

# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No

# Filesort: No  Filesort_on_disk: No  Merge_passes: 0

# No InnoDB statistics available for this query

use fisheyedb;

SET timestamp=1511794875;

SELECT @@session.tx_isolation;

We have the slow query time threshold set to 2 seconds.

They're so prevelant that they're the number 1 entry in our daily pt-query -digest archives

# Query 1: 0.08 QPS, 0.22x concurrency, ID 0x30AAEC10BC3D0C37 at byte 7953939

# This item is included in the report because it matches --limit.

# Scores: V/M = 0.33

# Time range: 2017-11-27 09:01:15 to 2017-11-28 00:11:48

# Attribute    pct   total     min     max     avg     95%  stddev  median

# ============ === ======= ======= ======= ======= ======= ======= =======

# Count         25    4120

# Exec time     19  11926s      2s     14s      3s      5s   972ms      3s

# Lock time      0       0       0       0       0       0       0       0

# Rows sent      0   4.02k       1       1       1       1       0       1

# Rows examine   0       0       0       0       0       0       0       0

# Rows affecte   0       0       0       0       0       0       0       0

# Bytes sent     0 362.12k      90      91   90.00   88.31       0   88.31

# Merge passes   0       0       0       0       0       0       0       0

# Tmp tables     0       0       0       0       0       0       0       0

# Tmp disk tbl   0       0       0       0       0       0       0       0

# Tmp tbl size   0       0       0       0       0       0       0       0

# Query size     2 116.68k      29      29      29      29       0      29

# String:

# Databases    fisheyedb (4105/99%), confluence... (15/0%)

# Hosts        x.x.x.x

# Last errno   0

# Users        fisheyeuse... (4105/99%), confluence... (15/0%)

# Query_time distribution

#   1us

#  10us

# 100us

#   1ms

#  10ms

# 100ms

#    1s  ################################################################

#  10s+  #

# EXPLAIN /*!50100 PARTITIONS*/

SELECT @@session.tx_isolationG

What can cause reading a simple session variable like tx_isolation to be anything less than instantaneous?

• Sometimes things like this happen because of other things going on at the same time. Can you spot such in the slowlog?


• The probably will probably be solved in version 8.0, when a number of things get turned into real InnoDB tables.


• Some 3rd party products issue an awful number of seemingly spurious and redundant information queries. Let's see the output from SHOW GLOBAL STATUS LIKE 'Com%';
  


• Set long_query_time = 0.1 and see if you catch a lot more of these queries, plus other things.