Details

      Description

      5 new SQL commands:

      1)QUERY CACHE LOCK <TIMEOUT>
      2)QUERY CACHE UNLOCK
      3)QUERY CACHE REMOVE <query_id>
      4)QUERY CACHE REMOVE TABLE LIKE "database.table name"
      5)QUERY CACHE RESULT <query_id>

      let me explain:

      1/2)
      1) here we will add a new variable (lock_connection_id) at query cache class
      this will receive the connection id (or another connection unique identifier)
      <timeout> is the time to wait query cache to lock, if timeout<=0 it will wait forever until lock query cache
      if query cache is turned off or, if command timeout => return a error "can't lock query cache"

      2) this set the query cache lock_connection_id to 0 and unlock query cache

      3) this remove the query inside query hash of query cache class that's in the position = 99999 (query_id)
      if it doesn't exists return an error
      if query cache isn't locked for this connection (connection!=0 and connection!=current connection) return an error
      if query cache isn't locked for any connection (connection=0) return a warning

      4) this will remove queries in cache that use some table using "LIKE" operator to get table names
      if query cache isn't locked for this connection (connection!=0 and connection!=current connection) return an error
      if query cache isn't locked for any connection (connection=0) return a warning

      5) get the result of a query cached (something like MDEV-4599 Closed )


      internal changes.. well i don't know all we have to change but...
      1)
      it's a soft lock, just to one process don't execute lock while other process is using it, but it don't lock internall events like invalidation


      somewhere in the end of query cache lock function:

      if lock_connection_id !=0, check if that connection is a dead connection or not
      if it is not a dead connection
      return "can't lock"
      else
      set lock_connection_id=0 (clear dead lock)
      return "lock acquired"


      check that we will lock all query cache system using SQL user interface...
      maybe a new priority could/should be created at super user level (?!)

      2) unlock will just set lock_connection_id=0 when lock_connection_id = current connection_id
      if lock_connection_id!=0 check if it's a dead connection
      if it's a dead connection set it to 0
      continue to next line...
      if the lock_connection_id=0 return a warning "query cache not locked"
      if lock_connection!=0 (should never happen since we will not get the lock to check this variable)
      return a error "query cache locked by some one else"
      3) this will remove a query cache inside cache
      if query cache isn't locked by "query cache lock" SQL
      we generate a warning to tell user to lock query cache (safe)
      if index don't exists return an error "this entry doesn't exists, are you crazy?"
      4) the same as (3) but remove using table name

      end

      with this, we can add a new feature of MDEV-4583 Open (query cache clean procedure)

      when query cache is full, we could call a user procedure and execute a user procedure to clean cache
      since we have information_schema.query_cache_queries and information_schema.query_cache_tables
      we can do this all in SQL!!! =]
      i don't know how to do it, but i think we can do it =)
      in other words, MDEV-4583 Open could be reduced to:

      query_cache_clean_method=DEFAULT / PROCEDURE
      query_cache_procedure_name=some global procedure name, maybe inside <mysql> schema
      query_cache_procedure_timeout=9999999

      maybe we could create a new query_cache table with less information
      removing "statment_text" column since it's very big (~32000 bytes)

      after accepting MDEV-4581 Closed , and this MDEV-4584 Open , we should add a new table to MDEV-4581 Closed (qc_info.cc) that have this information:

      QC_ID DB TABLE
      1 DB_NAME TABLE_USED1
      1 DB_NAME TABLE_USED2

      Instead of a column in QUERY_CACHE_QUERIES table with a 'group_concat' column having tables like: `db_name`.`table_used1`, `db_name`.`table_used2` ..

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                rspadim roberto spadim
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:

                  Time Tracking

                  Estimated:
                  Original Estimate - 5 weeks
                  5w
                  Remaining:
                  Remaining Estimate - 5 weeks
                  5w
                  Logged:
                  Time Spent - Not Specified
                  Not Specified