Details

    • Type: Task
    • Status: Closed
    • Priority: Trivial
    • Resolution: Duplicate
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      Hi guys, very nice job with query_cache_information plugin!!!
      Could we add some things?

      --------
      1) more columns... "table, rows, query_id, insert method, hits"
      a)tables from one query cache row
      maybe something like "database1.table_a, database2.tableb"
      database + "." + table name
      or maybe a separate table just for this? read (2)
      b)show how many rows in query entry
      this help to optimize app thinks like...
      SELECT * FROM table WHERE ....
      SELECT rows FROM query_cache_information WHERE statment_text="SELECT * FROM table WHERE "...
      IF return 0 rows
      SELECT COUNT(*) FROM table WHERE ...
      c)show query entry id like "primary key" of query_cache_information

      with this we can:
      delete from query_cache_information where query_id = "some_query_key"

      d)'insert method' => 'SQL_CACHE' or 'ALL'

      e)hits

      ok here we will lose some memory per entry (ulong hits)
      maybe will lose some time too, since we need to lock and unlock (i'm right?), like:
      lock the entry
      add +1 to hit var
      unlock it
      it's a good information to understand what query in a table have more or less 'hits'
      maybe a switch or variable could change this feature on/off to avoid time lost
      SET query_cache_entry_hit=ON or OFF

      --------
      2) get queries that have table "x", like
      SELECT * FROM query_cache_information WHERE table like '%,x,%' or table like '%,x' or table like 'x,%' or table='x'

      the problem is the table string, that can be 'x' or 'x, y' or 'y, x' or 'y, x, z' ...

      i don't know if a table string is the better option, or maybe a subquery could do the job...
      in this case we should have a table like:
      database,table,query_id

      and execute:
      SELECT * FROM query_cache_information WHERE query_id IN (
      SELECT query_id FROM query_cache_tables_query_information (or another table name)
      WHERE database='y' AND table='x'
      )

      since query cache must remove a query that have some table when we do insert / update / delete
      i think the new table is a better option than a text column for table name

      --------
      3) show tables that have query cache, like
      SELECT * FROM query_cache_tables_information
      should return: database,table,query_count

      --------
      4) remove a table from query cache
      like when "INSERT INTO table", and query cache remove entries with that table

      maybe via two options...
      DELETE FROM query_cache_information WHERE table = xxx
      or
      DELETE FROM query_cache_table_information WHERE table = xxx

      -------
      that's all =)

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

              Hide
              rspadim roberto spadim added a comment -

              more somethings, that the end of my brain strom about query cache, and i will sleep =]

              1)add "query_time" and "rows_examined" to query entry
              why?
              a)if query time is <1 second or rows_examined<1000 or something like this, we know that this query could be excluded (or not), since it's don't have a big "execution cost"

              b)if query time > 30 second or rows_examined>=1000000, it's a bad idea exclude this query, only if it have hits<xxxx we should remove it (maybe not), but this values are nice to know

              2)add a new status value about query cache (maybe... it's cost cpu time to get this values)
              Qcache_biggest_entry_hit
              Qcache_slowest_entry_time
              Qcache_fastest_entry

              we need to get all query cache values to know the biggest value, maybe a cache of this values can help, but...
              maybe we could use information schema information and MAX() MIN() functions

              3)maybe in future a dynamic query cache "clean" option:
              query_cache_full_memory_clean_strategy="today page remove" or "less expensive" or "any other"...

              with biggests values we could know what's the best query entry to delete:
              we have this informations:
              query time (queries that are fast to execute may be reexecuted fast again... ok fast is relative...)
              rows_count, result_size (queries with many rows may consume many memory)
              rows_examined (many time in i/o may be a problem to reexecute the query)
              hits (very 'popular' entry isn't a good idea to removed since we will need to recache it in near time, very popular => ORDER hits DESC )

              well end thanks guys!

              Show
              rspadim roberto spadim added a comment - more somethings, that the end of my brain strom about query cache, and i will sleep =] 1)add "query_time" and "rows_examined" to query entry why? a)if query time is <1 second or rows_examined<1000 or something like this, we know that this query could be excluded (or not), since it's don't have a big "execution cost" b)if query time > 30 second or rows_examined>=1000000, it's a bad idea exclude this query, only if it have hits<xxxx we should remove it (maybe not), but this values are nice to know 2)add a new status value about query cache (maybe... it's cost cpu time to get this values) Qcache_biggest_entry_hit Qcache_slowest_entry_time Qcache_fastest_entry we need to get all query cache values to know the biggest value, maybe a cache of this values can help, but... maybe we could use information schema information and MAX() MIN() functions 3)maybe in future a dynamic query cache "clean" option: query_cache_full_memory_clean_strategy="today page remove" or "less expensive" or "any other"... with biggests values we could know what's the best query entry to delete: we have this informations: query time (queries that are fast to execute may be reexecuted fast again... ok fast is relative...) rows_count, result_size (queries with many rows may consume many memory) rows_examined (many time in i/o may be a problem to reexecute the query) hits (very 'popular' entry isn't a good idea to removed since we will need to recache it in near time, very popular => ORDER hits DESC ) well end thanks guys!
              Hide
              rspadim roberto spadim added a comment -

              please close this MDEV, MDEV-4581 solve this one

              Show
              rspadim roberto spadim added a comment - please close this MDEV, MDEV-4581 solve this one
              Hide
              rspadim roberto spadim added a comment -

              please close this MDEV, MDEV-4584 and MDEV-4581 is what we need

              Show
              rspadim roberto spadim added a comment - please close this MDEV, MDEV-4584 and MDEV-4581 is what we need

                People

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

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Time Tracking

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