MySQL – InnoDB Index in Swap

indexinnodbMySQL

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?

Best Answer

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 !!!

Related Solutions

Mysql – Are two indexes needed

An index can seek by a subset of characters, as long as you're searching from the left. E.g., "Inter%" can seek, "%net" will not.

However, the first character is not necessarily the character under which the article would be sorted. "The Internet" should go under "I", not "T". You probably need two fields, DisplayTitle and SortTitle; a single-character index on the latter may be worthwhile, but most likely a full-length index will be just fine.

Indexes are typically B-trees, and a seek will jump to the right location about equally quickly whether you have 10 or 100 entries per page. Scans are another matter, but I'd start with the simplest solution and add an extra index only if performance proves inadequate in practice.

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;