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

QUERY CACHE - Dynamic query_cache_min_res_unit

        Details

        • Type: Task
        • Status: Open
        • Priority: Trivial
        • Resolution: Unresolved
        • Fix Version/s: None
        • Component/s: Query Cache
        • Labels:

          Description

          The Query Cache could perhaps benefit from a smarter, dynamic, query_cache_min_res_unit. In fact, it may be more useful to come up with a dynamic algorithm, per query, to determine the size of a allocation block, and enforce a "query_cache_MAX_res_unit".

          SELECT COUNT(1) FROM test - Allocation block should be tiny
          SELECT * FROM test - Allocation block might be "Avg_row_length" from table stats
          SELECT col1, col2 FROM test - Allocation block might be "Avg_row_length" from table stats
          SELECT col1 FROM test - Allocation block might be "Avg_row_length" from table stats

          For more complicated queries where a reasonable estimate isn't possible, or cases where the "average_row_length" is large, use the max_res_unit.

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                serg Sergei Golubchik added a comment -

                Oleksandr Byelkin, what do you think about it?

                Show
                serg Sergei Golubchik added a comment - Oleksandr Byelkin , what do you think about it?
                Hide
                Permalink
                sanja Oleksandr Byelkin added a comment -

                The tiny block is evil thing (lead to fragmentation), but detection of huge results could be useful. AFAIK after optimization and before execution (i.e. before allocation first data block) we know size of the result record (without blobs) and estimation for number of rows.

                Show
                sanja Oleksandr Byelkin added a comment - The tiny block is evil thing (lead to fragmentation), but detection of huge results could be useful. AFAIK after optimization and before execution (i.e. before allocation first data block) we know size of the result record (without blobs) and estimation for number of rows.
                Hide
                Permalink
                sanja Oleksandr Byelkin added a comment -

                Problems:

                • optimizer interested more in read records then send
                • optimizer gives upper limit estimation and in most cases it is more then will be sent to client
                • then more tables in upper join then less accurate is estimaion
                • we can say nothing about accuracy of estimation
                Show
                sanja Oleksandr Byelkin added a comment - Problems: optimizer interested more in read records then send optimizer gives upper limit estimation and in most cases it is more then will be sent to client then more tables in upper join then less accurate is estimaion we can say nothing about accuracy of estimation
                Hide
                Permalink
                rspadim roberto spadim added a comment - - edited

                we could use SQL_SMALL_RESULT and SQL_BIG_RESULT flags and LIMIT at least?

                Show
                rspadim roberto spadim added a comment - - edited we could use SQL_SMALL_RESULT and SQL_BIG_RESULT flags and LIMIT at least?

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    belugabehr BELUGABEHR
                  • Votes:
                    1 Vote for this issue
                    Watchers:
                    5 Start watching this issue

                    Dates

                    • Created:
                      Updated: