Mysql – Does MySQL/InnoDB use table caches when dealing with foreign tables

cacheMySQL

TL;DR: Does a table referred to via schema.table get held in table cache?

I have a large number of client databases that our application connects to based on an http request. These are used for every part of the application except for access logging. We use a second central schema for recording of login attempts across all instances of the application.

The login table, however, is accessed via the connection to the local schema as a foreign table. For example:

 UPDATE central_schema.login SET column = 'value';

Rather than using a second connection directly to the central schema and just dealing with login as a local table:

UPDATE login SET column = 'value';

Or switching schemas:

USE central_schema;
UPDATE login SET column = 'value';
USE client_schema;

Given that during busy times I see a lot of simple update or select queries waiting for the table to close is it possible that MySQL doesn't cache tables when referred to cross-schema? Is it possible that, because it's connected to client_schema, it wants to use the client schema cache and thus the foreign table can't be properly cached?

And, if I can't get a definitive answer to that, how would I even begin to test what's in the cache and what isn't?

Best Answer

Let's see if you have issues with table_open_cache:

SHOW VARIABLES LIKE 'table%cache';
SHOW GLOBAL STATUS LIKE '%open%';
SHOW GLOBAL STATUS LIKE 'Up%';

How many tables do you have in the system (not just the one database)?

FOREIGN_KEYs need to reach into an index of the 'other' table. This is usually the cost penalty for FKs. However all such accesses are cached in the innodb_buffer_pool, so it is often not a disk hit.

Related Question