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

indexmyisamMySQLunique-constraint

I have a server running Ubuntu 10.04 with Mysql 5.1x installed via package. The system has 128GB ram, 8 cores, and has 4TB's free space where Mysql & Mysql tmp are stored.

I have an MyISAM like this:

CREATE TABLE `data_store` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uniqname` varchar(150) NOT NULL,
  `data` blob,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM CHARSET=latin1;

I inserted 800mil records (about 350gb data before inserting), then attempted to add the following index:

ALTER TABLE data_store DISABLE KEYS;
ALTER TABLE data_store ADD INDEX uniqname_index (uniqname);
ALTER TABLE data_store ENABLE KEYS;

(Regarding the DISABLE KEYS command, I saw that suggested elsewhere to use BEFORE inserting data, and since the command never got to ENABLE KEYS, I assume it is serving no purpose for me. I've mainly just included to be thorough in my description of what I'm doing.)

When I start the index job, the first status that shows in SHOW PROCESSLIST is "copying to tmp table".

After a few hours, I check bac, and the status remains on the "Repair With Keycache" message even after 24 hours. I tried running the job on a slightly older server, and after 3 days, it still remains with status "Repair with keycache". Because of this, I've canceled the create-index command on this newer machine.

I've read that the "Repair With Keycache" can be very slow, and "Repair by sorting" is preferred in many cases.

Based on some Stack Exchange and random posts online, I added the following settings to my Mysql server:

myisam_sort_buffer_size = 80G
bulk_insert_buffer_size = 80G
myisam_repair_threads   = 8
max_heap_table_size     = 20G
myisam_max_sort_file_size = 500G
tmp_table_size          = 20G
key_buffer_size         = 20G
sort_buffer_size        = 20G
join_buffer_size        = 20G

I restarted the job, and the same process happened again (copy to tmp file, then repair via key cache).

After I killed the job, I noticed in mysql/error.log, there is a message, "myisam_sort_buffer_size is too small". This happened earlier in the day, and not exactly when I killed the job.

questions

Am I going about this wrong way? I just want to be able to lookup my data quickly by some key (uniqname).
Is there any benefit to starting over w/ my table, adding the index from the beginning, use the DISABLE KEYS command, insert my 800mil records, and then ENABLE KEYS? I read elsewhere that this can prevent the copying tmp table (which may only save me a few hours?)
Do I want this 'Repair by sorting'?

Best Answer

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

Related Solutions

How to Get Status Messages During Large Re-Indexing Operation in MySQL 5.5

You need to monitor that progress outside of all mysql client programs.

For this example, let's say your MyISAM table is named workingset in the database mydata and the datadir is the default, /var/lib/mysql

Sidenote : If you are using Windows, run this SQL command to get your datadir

SHOW VARIABLES LIKE 'datadir';

You will have three files representing the table

/var/lib/mysql/mydata/workingset.frm
/var/lib/mysql/mydata/workingset.MYD
/var/lib/mysql/mydata/workingset.MYI

Try a temp table approach to loading the table workingset

use mydata
DROP TABLE IF EXIST workingsetload;
CREATE TABLE workingsetload LIKE workingset;
ALTER TABLE workingsetload DISABLE KEYS;
Perform 7 million row insert into workingsetload here, preferably LOAD DATA INFILE
ALTER TABLE workingsetload ENABLE KEYS;
ALTER TABLE workingset RENAME workingsetold;
ALTER TABLE workingsetload RENAME workingset;
DROP TABLE workingsetold;

During the load of the table workingsetload, there will be a temp table whose first five(5) characters are "#sql-". You can visually monitor in the Linux OS the progress of the loading of workingsetload like this:

cd /var/lib/mysql/mydata
watch -n 1 "ls -l workingsetload.MY[ID] *sql-*.MY[ID] | awk '{print $5,$9}'"

You could script this in linux like this:

cd /var/lib/mysql/mydata
ls -l workingsetload.MY[ID] *sql-*.MY[ID] | awk '{print $5,$9}' > /tmp/IndexRebuild.txt

You could then parse the columns accordingly and subtract the .MYD sizes to know when the data portion of the MyISAM table is complete. Subsequently, you could then parse the columns accordingly and subtract the .MYI sizes to know when the index portion of the MyISAM table is complete.

For Windows, you could script this using Perl (ActivePerl for Windows) or just have a DOS Batch loop over and over again.

Regardless of OS, once the "ls -l #sql-*.MY[ID]" or "if not exist #sql-*.MY*" comes back with nothing, the reload and indexing are complete.

Give it a Try !!!

UPDATE 2011-07-20 16:55 EDT

Monty has successfully implemented this feature in MariaDB. Check it out !!!

MySQL MyISAM index causes query to match no rows; indexes disabled, rows match

OBSERVATION #1

You have this:

CREATE TABLE mytable (id1 int, id2 int, score float) ENGINE=MyISAM;
LOAD DATA INFILE '50-billion-records.txt' INTO mytable (id1, id2, score);
ALTER TABLE mytable ADD INDEX id1_index (id1);

This is how you should load the MyISAM table:

CREATE TABLE mytable (id1 int, id2 int, score float,key (id1)) ENGINE=MyISAM;
ALTER TABLE mytable DISABLE KEYS;
LOAD DATA INFILE '50-billion-records.txt' INTO mytable (id1, id2, score);
ALTER TABLE mytable ENABLE KEYS;

When you do disable keys, it stops the .MYI file from updating nonunique indexes.

During the LOAD DATA INFILE, the .MYI file will not grow because it contains no unique indexes or a primary key. This will promarily focus on loading the .MYD.

The ENABLE KEYS phase will do a read pass through the .MYD and linearly build all the nonunique indexes. In your case, it will build the id1 index.

OBSERVATION #2

As for the bug you seem to be experiencing, think of this:

25 billions rows
4 byte integer for id1
100 billion bytes = 93.13G
That's bigger than the 80G myisam_sort_buffer_size

IMPLICATIONS

It is possible that incomplete or aborted indexing operations could have lost index pages that should have been written to the .MYI.
It is also possible that doing DISABLE KEYS makes your query run because the id1 index is somehow being ignored. This could be the case since the EXPLAIN plan says type ALL and Possible Keys NULL. That's a full table scan. The SELECT query will work.
With the keys enabled and incompletely written, some nonleaf node info being missing would lead to SELECT queries that are destined to fail because some of the id1 values are not among the nonleaf node in the .MYI.

SUGGESTIONS

Try running the LOAD DATA INFILE with my proposed code

CREATE TABLE mytable (id1 int, id2 int, score float,key (id1)) ENGINE=MyISAM;
ALTER TABLE mytable DISABLE KEYS;
LOAD DATA INFILE '50-billion-records.txt' INTO mytable (id1, id2, score);
ALTER TABLE mytable ENABLE KEYS;

Also, raise myisam_sort_buffer_size to 100G

Give it a Try !!!

UPDATE 2013-08-12 21:37 EDT

Repair By KeyCache is the result of hitting the max sort_buffer_size and myisam_sort_file_size. In turn, MySQL elects to sift through keys in the keycache.

There are three(3) options you could further adjust

OPTION #1

Set the sort_buffer_size to 16K. That's the minimum value allowed.

OPTION #2

Set the tmp_table_size to 1K. That's the minimum value allowed.

OPTION #3

Set the myisam_sort_buffer_size to its max value of 9223372036854775807

This should further prevent the Repair By KeyCache problem

Best Answer

Related Solutions

How to Get Status Messages During Large Re-Indexing Operation in MySQL 5.5

MySQL MyISAM index causes query to match no rows; indexes disabled, rows match

OBSERVATION #1

OBSERVATION #2

IMPLICATIONS

SUGGESTIONS

Give it a Try !!!

UPDATE 2013-08-12 21:37 EDT

OPTION #1

OPTION #2

OPTION #3

Give it a Try !!!

Related Question