Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Let the MySQL engine handle that. Increase memory buffers (how much RAM does the systme have? how much can it be given to the mysql service?). But the MySQL server cached image of a table or query will have to be altered or reloaded when the (underlying) tables change (with an insert, delete or update), right? –  ypercube Nov 16 '12 at 11:38
    
If the memory that mysql uses is large enough to hold the whole database and your tables are not written often, it will be using both the tables loaded in memory and cached queries. –  ypercube Nov 16 '12 at 11:42
    
@ypercube yes right, if new data inserted cache image must be reloaded... For example all db have about 10gb and i have 16gb of ram. Only two tables of this db are often changing, but there are using less then 50mb... It's a catalog, so all catalog tables i wanna to be cachedm and also this two tables (there are changing one time in a day) –  PavelBY Nov 16 '12 at 12:04
    
Sometimes people put memcached in front of DB to do what you described. It can lead to real performance increase if you can leverage specific knowledge in your application's access pattern. The cache-money gem integrates with ActiveRecord easily. –  kizzx2 Nov 16 '12 at 14:26
    
@kizzx2 maybe no... need just mysql –  PavelBY Nov 16 '12 at 14:48

2 Answers 2

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.

share|improve this answer
    
@ypercube BTREE indexes are available to MEMORY tables ( dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html ). The KEY specification just has to end with USING BTREE. There is still the headache of having a MEMORY table whose indexes may far exceed the data and waste memory. –  RolandoMySQLDBA Nov 16 '12 at 16:00
    
@Rolando: My bad, you are of course right! –  ypercube Nov 16 '12 at 16:05

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

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?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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