Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7216

COM_Select goes up together with Qcache_hits

Details

    • 5.5.48-0

    Description

      According to the KnowledgeBase:

      "Note that queries returned from the query cache do not increment the Com_select status variable, so to find the total number of valid queries run on the server, add Com_select to Qcache_hits."
      ( https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/buffers-caches-and-threads/query-cache/ )

      This is true for MySQL, but not for MariaDB where Com_select actually does seem to get incremented even on query cache hits. Not sure whether this was an intended change (IMHO the MariaDB behaviour is the more correct one), but it is in contradiction to the documented state for sure.

      MariaDB [test]> CREATE TABLE t1 (id INT PRIMARY KEY);
      Query OK, 0 rows affected (0.06 sec)
       
      MariaDB [test]> INSERT INTO t1 VALUES (1);
      Query OK, 1 row affected (0.02 sec)
       
      MariaDB [test]> SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_select', 'Qcache_hits');
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | Com_select    | 0     |
      | Qcache_hits   | 0     |
      +---------------+-------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> SELECT * FROM t1;
      +----+
      | id |
      +----+
      |  1 |
      +----+
      1 row in set (0.00 sec)
       
      MariaDB [test]> SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_select', 'Qcache_hits');
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | Com_select    | 1     |
      | Qcache_hits   | 0     |
      +---------------+-------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> SELECT * FROM t1;
      +----+
      | id |
      +----+
      |  1 |
      +----+
      1 row in set (0.00 sec)
       
      MariaDB [test]> SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_select', 'Qcache_hits');
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | Com_select    | 2     |
      | Qcache_hits   | 1     |
      +---------------+-------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> SELECT * FROM t1;
      +----+
      | id |
      +----+
      |  1 |
      +----+
      1 row in set (0.00 sec)
       
      MariaDB [test]> SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_select', 'Qcache_hits');
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | Com_select    | 3     |
      | Qcache_hits   | 2     |
      +---------------+-------+
      2 rows in set (0.00 sec)

      Attachments

        Issue Links

          Activity

            LucasRolff Lucas Rolff added a comment -

            Oleksandr,

            https://screenshots.lucasrolff.com/2016_02_07_k4k0gl3pojf0h70.png

            As you can see - according to the graph I'm doing 994.67 queries per second - what I actually do on the server is 557 queries per second.

            Here's a screenshot from when we switched to MariaDB - our queries didn't increase, but since MariaDB increments Com_select when it shouldn't it results in graphs that are wrong:
            https://screenshots.lucasrolff.com/2016_02_07_2vxrdrebh9zi198.png

            LucasRolff Lucas Rolff added a comment - Oleksandr, https://screenshots.lucasrolff.com/2016_02_07_k4k0gl3pojf0h70.png As you can see - according to the graph I'm doing 994.67 queries per second - what I actually do on the server is 557 queries per second. Here's a screenshot from when we switched to MariaDB - our queries didn't increase, but since MariaDB increments Com_select when it shouldn't it results in graphs that are wrong: https://screenshots.lucasrolff.com/2016_02_07_2vxrdrebh9zi198.png
            LucasRolff Lucas Rolff added a comment -

            Please reopen this.

            LucasRolff Lucas Rolff added a comment - Please reopen this.

            Just to make it clear. in 5.5 behaviour changed and you should not add QC hits. And we are really sorry for the mess in the documentation but we have just fixed it.

            So if you upgraded to 5.5 and up you should calculate statistics in other way.

            sanja Oleksandr Byelkin added a comment - Just to make it clear. in 5.5 behaviour changed and you should not add QC hits. And we are really sorry for the mess in the documentation but we have just fixed it. So if you upgraded to 5.5 and up you should calculate statistics in other way.
            LucasRolff Lucas Rolff added a comment -

            We use munin official mysql plugins - these calculate correctly in MySQL (both version 5.1, 5.5, 5.6) but in MariaDB counts incorrectly.

            MySQL 5.5+ does not increase Com_select when Qcache_hits gets incremented.

            LucasRolff Lucas Rolff added a comment - We use munin official mysql plugins - these calculate correctly in MySQL (both version 5.1, 5.5, 5.6) but in MariaDB counts incorrectly. MySQL 5.5+ does not increase Com_select when Qcache_hits gets incremented.

            You probably have to complain to the plugin producer.

            Sorry, but mariadb has different set of statistics in general and in this particular place also.

            sanja Oleksandr Byelkin added a comment - You probably have to complain to the plugin producer. Sorry, but mariadb has different set of statistics in general and in this particular place also.

            People

              sanja Oleksandr Byelkin
              hholzgra Hartmut Holzgraefe
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.