How can i exclude a complete schema from buffering or cacheing? Each Query for this schema shouldt never buffered in query cache or innoDB Buffer.
2 Answers
Since you tagged your question innodb, I assume you want to exclude buffering pages for a particular schema in the InnoDB Buffer Pool.
There are no options to control the schema or tables that get stored in the buffer pool. In fact, any page read by a query must be stored in the buffer pool, at least while you're querying it.
InnoDB will automatically load pages into the buffer pool when you query them. InnoDB will also automatically evict pages if the space is needed for some other page by a subsequent query. The pages are managed by an LRU (least recently used) algorithm, which makes it more likely for an infrequently-used page to be evicted.
But InnoDB goes one step further. In the old days, there was a risk that a big table-scan would evict all the pages, even if your table-scan was a once-per-day query (like those run by mysqldump). So InnoDB tries to make the buffer pool scan-resistant by tracking pages that are newcomers to the buffer pool, or those which have "seniority" because they have been read many times. The senior pages are less likely to be evicted by newcomers.
All the above should help to explain why you probably don't need to control which schemas can use the buffer pool. InnoDB makes a good effort to make sure the pages you need are in RAM, and those you don't need aren't.
1 Comment
Enchanter
You`re rights my focus was innoDB and saving space. Your answer was helpful for me.
For the disabling of query cache for the specific schema - generally it's not possible, however, you can turn off query cache for your connection using
SET SESSION query_cache_type = OFF;
It will completely turn off query cache for the current session.
Or you can include SQL_NO_CACHE to your select queries.
As for the InnoDB buffer pool - I don't think it's possible as there are no schema specific configuration values for it.
4 Comments
Rick James
Completely turning of the QC for just one session would lead to inconsistencies. Perhaps that setting disables it for your
SELECTs, but it should not disable purging when you 'write'.
Alexey
Well, as far as I know query cache works only for selects as it's pretty useless to cache DML queries due to obvious reasons (noone will insert same values to the same table)
Rick James
My point is that DML queries, though not cached, do involve the QC for purging.
Alexey
Well, that's true, though completely turning off query caching (not query cache itself, as
SET SESSION query_cache_type = OFF; acts like appending sql_no_cache to each query) for the session sometimes pretty good idea especially if you are doing one-time heavy queries with huge result.