Cache all mysql table

Question

Maybe it sound's like i'm an idiot... But i'm developing app with huge db on RoR. Also i see that if i do some query and after that do it one more it is doing much faster, becouse it has been cached... But could i send all my table data to cache via some query, to improve speed, so that simple my table will be in ram memory? I read a lot about encreasing cahce size etc... But how can i send this all data in cache not when user do some query (if so user see data from cache)...

*************************** 1. row ***************************
       Table: ART_LOOKUP
Create Table: CREATE TABLE `ART_LOOKUP` (
  `ARL_ART_ID` int(11) NOT NULL,
  `ARL_SEARCH_NUMBER` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ARL_KIND` int(3) DEFAULT NULL,
  `ARL_BRA_ID` int(11) DEFAULT NULL,
  `ARL_DISPLAY_NR` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ARL_DISPLAY` int(11) DEFAULT NULL,
  `ARL_BLOCK` int(11) DEFAULT NULL,
  `ARL_SORT` int(11) DEFAULT NULL,
  KEY `ARL_ART_ID` (`ARL_ART_ID`),
  FULLTEXT KEY `ARL_SEARCH_NUMBER` (`ARL_SEARCH_NUMBER`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)



*************************** 1. row ***************************
       Table: ARTICLES
Create Table: CREATE TABLE `ARTICLES` (
  `ART_ID` int(11) NOT NULL,
  `ART_ARTICLE_NR` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `ART_SUP_ID` int(11) DEFAULT NULL,
  `ART_DES_ID` int(11) DEFAULT NULL,
  `ART_COMPLETE_DES_ID` int(11) DEFAULT NULL,
  `ART_CTM` blob,
  `ART_PACK_SELFSERVICE` int(11) DEFAULT NULL,
  `ART_MATERIAL_MARK` int(11) DEFAULT NULL,
  `ART_REPLACEMENT` int(11) DEFAULT NULL,
  `ART_ACCESSORY` int(11) DEFAULT NULL,
  `ART_BATCH_SIZE1` int(11) DEFAULT NULL,
  `ART_BATCH_SIZE2` int(11) DEFAULT NULL,
  `Quantity` int(5) DEFAULT NULL,
  `Price` float DEFAULT NULL,
  `Waittime` int(3) NOT NULL,
  `datetime_of_update` datetime DEFAULT NULL,
  KEY `ART_ID` (`ART_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

via privacy can't show all

Answer 1

Certain database engines do support pinning objects to cache, Oracle and MS-SQL to name a couple. MySQL does not have this option, as far as I know.

On the other hand I cannot think of many use cases for this option, as modern database systems are very good at managing cache. MySQL offers block caching with an LRU algorithm through the InnoDB storage engine.

There is also an in memory storage engine, but there is no easy and efficient way replicate changes back to disk.

There are complete in-memory databases out there if you want everything in RAM, including MySQL Cluster.

On a sidenote, in practical use there is a trade-off between I/O and CPU resources. More cache means less I/O but more CPU is consumed, and there is a real possibility of introducing new bottlenecks.

Answer 2

How to cache a MyISAM table

By design, the MyISAM storage engine only caches index pages from .MYI. There is a command to preload indexes for one or more MyISAM tables into a dedicated key cache. For your ART_LOOKUP table, you would do this:

STEP 01) Get the size of the .MYI

SELECT (index_length/1048576) IndexMB
FROM information_schema.tables
WHERE table_name = 'ART_LOOKUP';

Let's assume the index_length of this table is 200M.

STEP 02) Make a script that will create a key cache for ART_LOOKUP only

cd /var/lib/mysql
echo "SET GLOBAL art_lookup_cache.key_buffer_size = 1048576 * 200;" > init_file.sql
echo "CACHE INDEX ART_LOOKUP IN art_lookup_cache;" >> init_file.sql
echo "LOAD INDEX INTO CACHE ART_LOOKUP;" >> init_file.sql

When executed, this will remove all index pages from the default MyISAM key cache and load all the index pages into the art_lookup_cache.

STEP 03) Add this init_file option to /etc/my.cnf

[mysqld]
init_file=/var/lib/mysql/init_file.sql

STEP 04) Restart mysql

service mysql restart

Going forward, every restart of mysql will preload the indexes for ART_LOOKUP every time. If you cannot restart at this time, you can also reload from the mysql client:

mysql> source /var/lib/mysql/init_file.sql

I have written about caching MyISAM index pages in the past

• Jun 09, 2012 : Can we assign mysql Key_buffer_size value for particular table?
• Jan 26, 2012 : Less RAM than Index_length MyISAM
• Jan 09, 2012 : Any gotchas at all with converting from MyISAM to InnoDB?

How to cache an InnoDB table

By design, InnoDB caches data and index pages in the InnoDB Buffer Pool. You will have to make the InnoDB Buffer Pool is large enough to hold data and index pages. In your particular case, all you have to do to load all data and index pages into the InnoDB Buffer Pool, just run this:

SELECT * FROM ARTICLES;

Very straightforward, eh?

Now, if you have an InnoDB table with secondary indexes you will have to traverse the indexes. For example, ARTICLES has a secondary index on ART_ID. You would load all data and index pages for ARTICLES like this:

SELECT * FROM ARTICLES USE INDEX (ART_ID) ORDER BY ART_ID;

For emphasis, you will need to size your InnoDB Buffer Pool. Please run this query to find out how big an InnoDB Buffer Pool you need:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

If the recommended value is greater that 75% of installed RAM, then set innodb_buffer_pool_size to 75% of installed RAM.

Please see me earlier post on sizing InnoDB and MyISAM caches : What are the main differences between InnoDB and MyISAM?