6

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?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.