0

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_isolation\G

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

0
  • 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.