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

Add per-table "QUERY_CACHE" option

    XMLWordPrintable

Details

    • New Feature
    • Status: In Testing (View Workflow)
    • Minor
    • Resolution: Unresolved
    • None
    • 13.3
    • None
    • None

    Description

      The initial MDEV-37070 scope included 3 per-table options: QUERY_CACHE, ADAPTIVE_HASH_INDEX, BINLOG_ROW.
      MDEV-37070 scope was refined: ADAPTIVE_HASH_INDEX became also a per-index option, while QUERY_CACHE and BINLOG_ROW were excluded from the first implementation.
      This ticket is opened to track the feature request of the QUERY_CACHE per-table option (values DEFAULT, YES, NO).

      From a performance point of view, QUERY_CACHE should not be used with tables that change very frequently.

      The effect of the new per-table QUERY_CACHE option would be:

      Having this option OFF is the same as using SELECT SQL_NO_CACHE for any query using this table.
      Having this option ON is same as using SELECT SQL_CACHE for this table and the query will be cached if no other table has QUERY_CACHE=OFF

      The new values for query_cache_type are (0-2 are same as before)

      OFF (0) = Query cache off
      ON (1) = Query cache on, all queries are cached except queries marked with SQL_NO_CACHE
      DEMAND (2) = Query cache on, only queries marked with SQL_CACHE are cached
      DEMAND_STRICT (3) = Query cache on, only queries starting with SELECT SQL_CACHE
      are cached. This avoids a query cache lock+lookup for every
      query.
      TABLES (4) = Query cache on, only cache queries marked with SQL_CACHE or
      where all used tables are using the create option SQL_CACHE=1
      ALWAYS_OFF (5) = To turn of query cache permanently for new open tables. query_cache_type
      cannot be changed without a server restart.

      The purpose of ALWAYS_OFF is to tell engines that they can totally ignore the query cache. This speed ups engines (especially InnoDB) as they don't have to take into account that query cache can any time switch between on/off.

      Other optimizations done:

      • Speed up query cache interface to make query_cache_type=OFF code a bit faster.
      • Added query_cache_use_rw_lock to allow concurrent query cache lookups
      • Setting query_cache_size in a config file will not automatically enable the query cache
      • This was done to address user complaints that query_cache_size should not affect query_cache_type (not standard or expected behaviour for MariaDB variables).

      As a note, in MDEV-37070 there were suggestion to make QUERY_CACHE automatically toggled per table (link here) as well as counter-arguments in favor of this new per-table option (link here). Please refer to full MDEV-37070 list of comments for further details.

      Attachments

        Issue Links

          Activity

            People

              monty Michael Widenius
              alessandro.vetere Alessandro Vetere
              Elena Stepanova Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - 10d
                  10d
                  Remaining:
                  Time Spent - 1d Remaining Estimate - 9d
                  9d
                  Logged:
                  Time Spent - 1d Remaining Estimate - 9d
                  1d

                  Git Integration

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