2

In MySQL 5.5, I have a table which is recreated on a daily basis and for which the process of adding an index is taking about 50 minutes which I'm hoping to reduce. At the time when the index is created the table usually has about 25 million records and there is plenty of disk space and RAM. While the index is being added the process status is "copy to tmp table" for most of the time.

my.ini file settings

[mysqld]
basedir = C:/Bitnami/WAMPST~1/mysql
datadir = D:/mysql\data
key_buffer_size = 384M
max_allowed_packet = 16M
tmpdir = E:/mysql/tmp/
tmp_table_size = 1024M
max_heap_table_size = 1024M
table_cache = 256
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
net_buffer_length = 8K
myisam_sort_buffer_size = 8M

Table structure:

CREATE TABLE `log_access` (
  `_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `type_id` int(11) NOT NULL,
  `building_id` int(11) NOT NULL,
  `card_id` varchar(15) NOT NULL,
  `user_key` varchar(35) DEFAULT NULL,
  `user_name` varchar(25) DEFAULT NULL,
  `user_validation` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`_id`),
  KEY `log_access__user_key_timestamp` (`user_key`,`timestamp`)
  KEY `log_access__timestamp` (`timestamp`)
) ENGINE=MyISAM

Statement to add the index

ALTER TABLE `log_access` ADD INDEX `log_access__user_key` (`user_key` ASC);
  • Don't use MyISAM. Use an SSD. Rethink your strategy. Recreating a table with 25m records on a daily basis sounds like wrong app. architecture. – N.B. Sep 25 '14 at 15:05
  • @N.B. InnoDB would speed up the index creation? Also SSD is not an option, the server is a VM on Azure. Also, 25 million of records is a daily amount of logs on which I need to create daily stats. I have decided to do it on a separate table because running stats on a separate instance instead of on the instance that is receiving the logs. This question is actually a follow up to my initial issue stackoverflow.com/questions/26022508/…. – Kate Sep 25 '14 at 15:23
2

Use InnoDB because it supports Fast Index Creation.*

If you have plenty of RAM, configure a tmpfs partition and point your MySQL tmpdir to that location.


* There are many other reasons besides that to stop using MyISAM. MyISAM is known for its table-level locking, tendency to corrupt data, and lack of support for ACID and referential integrity. It's also slower than InnoDB under most workloads.

| improve this answer | |
  • I understand there are many benefits of using InnoDB over MyISAM. The reason I went with MyISAM was mainly because of the insert speed; for example, performing LOAD DATA INFILE for 25 million records into MyISAM takes less than 2 minutes vs well over an hour into InnoDB. Also, since this is a basic, non-referential logs table I'm not worried about ACID and table locking because there is only one service accessing it once a day to prepare statistical reports. – Kate Sep 25 '14 at 15:46
  • Okay then go with the tmpfs solution. Based on this and your other question, it sounds like your VM disks are really slow. – Bill Karwin Sep 25 '14 at 16:01
  • See msdn.microsoft.com/library/dn197896.aspx, it confirms that you only get 300 IOPS per disk in Azure. That's really disappointing. – Bill Karwin Sep 25 '14 at 16:05
  • 500 IOPS in my case ;) (basic vs standard tier) and yes, it is very, very disappointing, but such is the case with IaaS. Disk striping helps a little, but nowhere near to what in this day of age we would expect. Since I'm on a Windows environment I'm a little reluctant tmpfs since that would require a third party tool for which I would have to jump through hoops to convince the "head honchos" to accept :( – Kate Sep 25 '14 at 16:29
  • I guess I assume everyone using MySQL is using a Linux server. Because for the most part, they are. Running a Windows server on a VM seems to me like fighting a boxing match with both hands tied behind your back. – Bill Karwin Sep 25 '14 at 16:38
1

Change the

    key_buffer_size

to the amount of memory you need for storing the whole index (check data directory and have a look at the .MYI file from the last creaton cycle).

    myisam_max_sort_file_size

The maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes.

The default value is 2GB. If MyISAM index files exceed this size and disk space is available, increasing the value may help performance.

    myisam_sort_buffer_size

The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.

The maximum allowable setting for myisam_sort_buffer_size is 4GB.

And try to use linux over windows for mysql for versions < MySQL 5.1.38. if you use > MySQL 5.1.38 on windows, use

    innodb_thread_concurrency

set to 0 for let mysql automatically adjust concurrency and use multiple cores even on windows.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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