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:

                Time Tracking

                  Estimated:
                  Original Estimate - 1.25d
                  1.25d
                  Remaining:
                  Time Spent - 6.5h Remaining Estimate - 3.5h
                  3.5h
                  Logged:
                  Time Spent - 6.5h Remaining Estimate - 3.5h
                  6.5h

                  Git Integration

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