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

QUERY CACHE - add STATISTICS per query and show this informations in qc_info plugin

    XMLWordPrintable

Details

    • 10.1.8-1

    Description

      Change sql_cache.cc and sql_cache.h to add hit rates and statistics
      usable with qc_info plugin

      Statistics:
      total hits / total time expent in query cache
      hits in three categories:
      1) low period category: period between the last hit and the current hit < query expent time
      2) high period category: period < query expent time * 10 and > query expent time
      3) outlier category: period > 1 second, and period > query expent time * 10
      3.1) in a future version instead of * 10, we could use number of competitors (concurrent) when inserting this query at query cache
      for example if we have 9 concurrent inserts of the same query at query cache this number *10 is an acceptable value for high period, if we have n concurrent inserts a (n+1) value is acceptable

      These categories helps to understand what kind of query we have cached:
      1) low period hits: in this case if we lose this query entry, we will have a performace problem, and users will tell "the server is slow now"
      2) high period hits: we have a query that is executed with a good frequency, removing it we will have a higher i/o in disks
      3) outlier period hits: this query isn't a query that we should cache, or we can loose it and we don't see users reporting "hey the server is slow, or the disks is spinning a lot"

      this allow a better query cache control (MDEV-4584) and a better cache hit rate when optimizing queries with SQL_CACHE/SQL_NO_CACHE


      a second part could be an variable (query_cache_stats=0/1) to turn off/on the statistics and use a block memory just to statistics instead of one big block sharing memory with queries flags

      in this case:
      turn on -> off, should clean this stats memory block
      turn off -> on, should create stats with 0 value (maybe a problem with lock contention), or clean all query cache and start from zero

      changing query cache queries (invalidate, table invalidate, reallocation, etc) should handle the stats block too

      tables of this plugin:

      query_cache_queries:

      QUERY_CACHE_ID STATEMENT_SCHEMA STATEMENT_TEXT RESULT_FOUND_ROWS QUERY_ROWS SELECT_ROWS_READ QUERY_HITS QUERY_HITS_PERIOD_LOW QUERY_HITS_PERIOD_HIGH QUERY_HITS_PERIOD_OUTLIERS QUERY_HITS_TOTAL_TIME_US QUERY_HITS_MEAN_PERIOD_US QUERY_HITS_MEAN_PERIOD_LOW_US QUERY_HITS_MEAN_PERIOD_HIGH_US QUERY_INSERT_TIME QUERY_LAST_HIT_TIME SELECT_EXPEND_TIME_US SELECT_LOCK_TIME_US TABLES_TYPE RESULT_LENGTH RESULT_BLOCKS_COUNT RESULT_BLOCKS_SIZE RESULT_BLOCKS_SIZE_USED FLAGS_CLIENT_LONG_FLAG FLAGS_CLIENT_PROTOCOL_41 FLAGS_PROTOCOL_TYPE FLAGS_MORE_RESULTS_EXISTS FLAGS_IN_TRANS FLAGS_AUTOCOMMIT FLAGS_PKT_NR FLAGS_CHARACTER_SET_CLIENT FLAGS_CHARACTER_SET_RESULTS FLAGS_COLLATION_CONNECTION FLAGS_LIMIT FLAGS_TIME_ZONE FLAGS_SQL_MODE FLAGS_MAX_SORT_LENGTH FLAGS_GROUP_CONCAT_MAX_LEN FLAGS_DIV_PRECISION_INCREMENT FLAGS_DEFAULT_WEEK_FORMAT FLAGS_LC_TIME_NAMES
      1 teste select * from t1 3 3 3 0 0 0 0 0       1411247500.713750   232 35 NON TRANSACT 78 1 512 142 1 1 0 0 0 1 1 utf8 utf8 utf8_general_ci -1 SYSTEM PIPES_AS_CONCAT,ALLOW_INVALID_DATES 1024 1024 5 0 en_US

      query_cache_queries_tables:

      QUERY_CACHE_ID SCHEMA TABLE_NAME TABLE_SUFFIX
      1 teste t1 (here we display partition name like P#p0)

      query_cache_tables

      TABLE_SCHEMA TABLE_NAME TABLE_HASHED TABLE_TYPE QUERIES_IN_CACHE
      teste t1 1 NON_TRANSACT 1

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              rspadim roberto spadim
              Votes:
              2 Vote for this issue
              Watchers:
              5 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.