Mysql – partially invalidate the MySQL table cache

cachemyisamMySQL

I want to set a fairly large table to MyISAM mode to keep it cached proactively. The table file is approximately 3GB.

After several hours, MySQL invalidates the entire table from cache, while < 0.1% of the table actually changed in the meantime.

Sometimes we need to do ±100,000 SELECT statements very quickly, which causes a cache stampede. When cached, very nice. When not cached, terrible.

What can I do to ensure the table is kept in memory?

Best Answer

The only thing that is definitively cached for MyISAM is an index.

The key_buffer_size variables sets up how large the MyISAM Key Cache will be.

There are two suggestions I can offer

SUGGESTION #1 : Use a Dedicated Key Cache for the Table

Did you know you could create a keycache dedicated to one or more MyISAM tables? Suppose you have a table called mydb.mytable and you want to load the entire mytable.MYI file into it. Here are the steps to set this up:

STEP 01) Get the physical size of the table's .MYI file

Run this query

SELECT index_length MYISize FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';

Suppose the query returns 1234567890 for MYISize. That's 1.114978 GB = 1177.3757 MB. Round that up to 1280M (that 1.25 GB).

STEP 02) Create the Buffer with that size

SET GLOBAL mykeycache.key_buffer_size = 1280 * 1024 * 1024;

STEP 03) Associate the cache with the indexes for mydb.mytable

CACHE INDEX mydb.mytable INTO mykeycache;

STEP 04) Load the Index Pages from the .MYI into the Dedicated Cache

LOAD INDEX INTO CACHE mykeycache;

That's all. All indexes for mydb.mytable are now in that cache only. Running these commands also removes the same index entries from the general MyISAM keycache.

You could create a startup script for mysql to preload the indexes.

I have written about this subject is past posts

Aug 28, 2012 : MySQL InnoDB Index in swap
Jun 09, 2012 : Can we assign mysql Key_buffer_size value for particular table?
Mar 20, 2012 : optimal table design mysql with primay key and varchar value
Jan 26, 2012 : Less RAM than Index_length MyISAM
Dec 30, 2011 : Is splitting a 'users' table for authentication purposes a good idea?

As a sidenote, I would like to suggestion something optional. You could speed up the access time of MyISAM data by changing the ROW_FORMAT of the MyISAM you want. For the same MyISAM table mydb.mytable, here is what you do:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

That's it. This a good and bad side to changing ROW_FORMAT to Fixed

GOOD SIDE : Queries hitting mydb.mytable will run 20-30% faster without any additional enhancements
BAD SIDE : This bloats all VARCHAR columns and treats them as CHAR. This will bloat indexes that have VARCHAR columns as well. This could increase table and index sizes 80-100%
My Past Posts Where I Discussed This
- May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
- Jan 03, 2012 : Optimized my.cnf for high-end and busy server
- May 03, 2012 : Which is faster, InnoDB or MyISAM?

SUGGESTION #2 : Switch the table's Storage Engine to InnoDB

If you switch that table to InnoDB, you set up the InnoDB Buffer Pool in such a way that data and indexes that enter the Buffer Pool will linger a lot longer. This will simulate what your original question is asking: Partially invalidating a cache. That way, mysqldumps, full table scans, and queries like these will have roadblocks to cache stampedes. The options to tweak are innodb_old_blocks_pct and innodb_old_blocks_time.

According to the Configuration Options section of the MySQL Documentation for the InnoDB Buffer Pool:

Setting innodb_old_blocks_time greater than 0 prevents one-time table scans from flooding the new sublist with blocks used only for the scan. Rows in a block read in for a scan are accessed many times in rapid succession, but the block is unused after that. If innodb_old_blocks_time is set to a value greater than time to process the block, the block remains in the “old” sublist and ages to the tail of the list to be evicted quickly. This way, blocks used only for a one-time scan do not act to the detriment of heavily used blocks in the new sublist.

innodb_old_blocks_time can be set at runtime, so you can change it temporarily while performing operations such as table scans and dumps:
SET GLOBAL innodb_old_blocks_time = 1000;... perform queries that scan tables ...
SET GLOBAL innodb_old_blocks_time = 0;
This strategy does not apply if your intent is to “warm up” the buffer pool by filling it with a table's content. For example, benchmark tests often perform a table or index scan at server startup, because that data would normally be in the buffer pool after a period of normal use. In this case, leave innodb_old_blocks_time set to 0, at least until the warmup phase is complete.

Related Solutions

MySQL Indexing – How to Optimize MySQL Setup for Faster Index Creation

For starters, I would not touch the buffer sizes just yet. The sizes youhave in the question are monstrously too big.

Here is another observation: You have BLOB data. Ouch, your temp table is going to eat space rather quickly. You could do somehting like this:

Create a 32GB RAM Disk called /var/tmpfs by adding this line to /etc/fstab

none                    /var/tmpfs              tmpfs   defaults,size=32g        1 2

Next, create a folder called /mysqltmp and mount the RAM disk on it

mkdir /mysqltmp
chown mysql:mysql /mysqltmp
mount /mysqltmp /var/tmpfs

Add this to my.cnf and restart mysql

[mysqld]
tmpdir=/mysqltmp

Now, any tmp table made via DDL lands in the RAM disk.

Here is yet another observation: Why not create a separate table that keeps the BLOB data away from the unique names?

CREATE TABLE `data_store_name` SELECT id,uniqname FROM `data_store` WHERE 1=2;
ALTER TABLE `data_store_name` ADD PRIMARY KEY (id);
ALTER TABLE `data_store_name` ADD UNIQUE KEY (uniqname);
ALTER TABLE `data_store_name` ADD INDEX name_id_ndx (uniqname,id);
INSERT INTO `data_store_name` SELECT id,uniqname FROM `data_store`;

This will prevent any moving around of BLOB data when indexing.

From here, you would have to always join data_store using its name like this:

SELECT
    A.uniqname,B.data
FROM
    (SELECT * FROM data_store_name WHERE uniqname = 'mydataname') A
    LEFT JOIN
    data_store B USING (id)
;

Making these changes will sidestep this whole mess of dealing with keycache, RAM disks, and tmp tables.

Give it a Try !!!

Innodb – Fastest way to copy data from MyISAM to InnoDB

There are a few solutions. First, however, I'm not sure about the process in the first place. Is this a one time copy operation? A recurring copy operation? Do you want to migrate from MyISAM to InnoDB?

What is the main reason for your desire for a quick operation?

If you're looking for migration, then why don't you use an online table alter tool, such as oak-online-alter-table (disclaimer: I'm author of this tool) or pt-online-schema-change? Both will allow you to change your schema live and online with very little disturbance.

If you're looking to a copy+paste of your data, then I would suggest using chunking: copying the data in small packets. This way you don't get that huge lock and no funny timeouts. You can use either oak-chunk-update or pt-archiver for this. This may actually make the total runtime shorter because of reduces locking, but may also take longer. Also consider that it is not an atomic operation, and changes to original table while copying is made, may not get caught, so you may get an inconsistent copy.

Otherwise (or in addition) you can use all the usual tweaks, such as

SET GLOBAL innodb_flush_log_at_trx_commit := 2;

or set

[mysqld]
innodb_doublewrite = 0

or perhaps, depending on OS and disks,

[mysqld]
innodb_flush_method = O_DIRECT

Each of the above may reduce disk I/O access. First two will also make your server less crash safe. But if for limited time, this may be OK for you.