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.

I can't find any info regarding my question. This is more theoretical question. For example, i have table keywords.

CREATE TABLE IF NOT EXISTS `keywords` (
  `kid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `language_id` int(11) NOT NULL,
  `keyword` varchar(120) NOT NULL,
  PRIMARY KEY (`kid`),
  UNIQUE KEY `custom_idx` (`language_id`,`keyword`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

My server has 1GB of free RAM memory. My table has millions of rows and size of my custom_idx is more than 1GB. Index can't fit into RAM memory. What would mysql do ? Is only part of this index will be stored in the memory ? Will this break BTREE/Clustered index performance dramatically ?

Also, if size of my index can't fit into innodb_buffer_pool_size limits, what would happen ?

Is replication is the answer to enormous InnoDB databases where indexes can't fit into RAM memory ?

I need to keep all my indexes in RAM ? What if my db size is more than 500gb?

share|improve this question
    
I need the following information: 1) SELECT COUNT(1) FROM keywords; 2) SELECT MAX(language_id) FROM keywords; –  RolandoMySQLDBA Aug 28 '12 at 16:58
1  
Are you looking for something different than your identically asked question on SO ? –  Derek Downey Aug 28 '12 at 17:12
    
yes, this answer wasn't exactly what i was looking for. 1) 631 2) 14. But my question is more theoretical than practical –  twoface88 Aug 28 '12 at 17:14

2 Answers 2

up vote 2 down vote accepted

InnoDB caches both data pages and index pages into the InnoDB Buffer Pool

MyISAM caches index pages only. In fact, you can do something interest if you combine replication with MyISAM.

SUGGESTION #1

Since there only 6700 spoken languages on Earth, you should use SMALLINT for language_id instead of INT. Makes for a smaller table and smaller index.

SUGGESTION #2

If you use MySQL Replication, try out this crazy idea: Convert the keywords table on the Slave to MyISAM. Leave the Master's Copy of the keywords as InnoDB.

You could run this query on the Slave ONLY to convert it...

ALTER TABLE keywords ENGINE=MyISAM;

SUGGESTION #3

Once you make the keywords table MyISAM on the Slave, you could create a dedicated MyISAM Key Cache just for keywords as follows (Example: Setup 512M KeyCache for keywords):

First create a script on the Slave

cd /var/lib/mysql
echo "SET GLOBAL keywords_cache.key_buffer_size = 1024 * 1024 *512;" > InitMySQL.sql
echo "CACHE INDEX keywords IN keywords_cache;" >> InitMySQL.sql
echo "LOAD INDEX INTO CACHE keywords;" >> InitMySQL.sql

Next, create the following entry for the startup script in /etc/my.cnf on the Slave

[mysqld]
init-file=/var/lib/mysql/InitMySQL.sql

Then, just restart mysql

service mysql restart

This keeps keyword index pages away from the general MyISAM Key Cache and in a dedicated cache.

SUGGESTION #4

Perform your keyword lookups on the Slave

I hope this helps !!!

share|improve this answer

Back to the question...

The buffer_pool and key_buffer are just caches. That is, if your index is bigger than the cache, mysql will take care of reading/writing/purging/etc the BTree blocks as needed.

If your table were a terabyte, you could still use it with only 1GB of available RAM. Sure, it would be slower -- maybe just a little slower (if the working set is small); maybe a lot slower (if you do a lot of random lookups).

InnoDB's buffer_pool uses 16KB blocks; MyISAM's key_buffer uses 1KB blocks. (Not that this matters.)

In any performance question, we need to know the queries that are applied.

SELECT kid WHERE language_id = 123 AND keyword = 'abc'

will probably take a disk hit, since the index cannot be fully cached. That is, your application may be limited to about 100 such queries per second.

I will express caution about one of Rolando's suggestion -- turning over 0.5GB to the key_buffer will starve the data caching, and may slow things down.

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.