Details

    • Task
    • Status: Closed (View Workflow)
    • Trivial
    • Resolution: Duplicate
    • None
    • None

    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 =)

      Attachments

        Issue Links

          Activity

            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!

            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!

            please close this MDEV, MDEV-4581 solve this one

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

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

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

            People

              Unassigned Unassigned
              rspadim roberto spadim
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.