Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Won't Fix
-
Affects Version/s: 10.1, 10.0, 5.5
-
Fix Version/s: N/A
-
Component/s: Query Cache
-
Labels:None
Description
when using
SELECT sql_some_hint SQL_NO_CACHE ...
or
SELECT sql_some_hint SQL_CACHE ...
the query cache 'parser' don't check for SQL_CACHE/SQL_NO_CACHE if it's not after "SELECT", we should check more bytes after "SELECT" to make sure we don't have "SQL_NO_CACHE"/"SQL_CACHE" hint, i think 50~100 bytes of search is ok, but we can have some situations like
SELECT /* comments more than 100 byts */ SQL_CACHE/SQL_NO_CACHE -- must read only hints, not comments
or
SELECT /* comment SQL_CACHE */ SQL_NO_CACHE ... -- should not cache
or
SELECT /* comment SQL_NO_CACHE */ SQL_CACHE ... -- should cache
must check what's a nice number to have as 'buffer size' to execute this "HINT" search
- a idea is search one space after each hint, we could check 10 spaces, if SQL_NO_CACHE / SQL_CACHE isn't found, continue checking qc normally as we don't know if hints are present or not without executing the "real" parser
- another idea is search for 50~100 bytes after "SELECT"
- run query cache before and after parser (to execute a 100% SQL_NO_CACHE / SQL_CACHE search)
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
-
- Open
-
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
MDEV-6631 show performace loss!
roberto spadim,
As discussed many times before, please don't spread information about one problem across different JIRA entries, and don't put different problems in one entry, it's really impossible to track or takes way too much time which we simply can't afford.
If this issue is the same as MDEV-6631, the problem should be fixed in MDEV-6631 and this one should be closed as a duplicate.
If this issue is not the same as MDEV-6631, then it's unclear why MDEV-6631 describes performance degradation which relates to the problem described here, and how to extract that particular piece of information from MDEV-6631, which is also full of scattered thoughts and side observations.
Same goes for "feature requests" you were coming up with while commenting on this issue, they will simply be ignored, because they have nothing to do with the original complaint.
All in all, theories are great, and undoubtedly every additional operation is a performance loss, but unless we have a quantifiable proof of performance loss that is meaningful in real life, it will stand little to no chance to be fixed.
mdev 6631 -> avoid when possible query cache check with SQL_CACHE / SQL_NO_CACHE hints (in this report that we checked query cache when it shouldn't be checked)
mdev 7132 -> what should be done? report warning or execute parser before query cache check
1) SQL parser check it with any number of spaces and comments (see sql/sql_yacc.yy which is the parser description)
2) What you refer is an optimization to catch most common cases. if make it more complex it will slow down all other queries so it not acceptable.
nice, no problem, should we document that sql_no_cache should be after first select to avoid one query cache check?
roberto spadim as per your parent issue comment yes only a propper parse will probably do. ./sql/sql_parse.cc:mysql_parse contains some FIXMEs that seem to indicate this. Attempting to parse the query as just a SELECT before deciding on query_cache_send_result_to_client is one way, but looking at the bison generated parse code I'm out of my depth.