"Create Unique Index" on big table taking too long time [closed]

Question

I am trying to restore GHTorrent's database dumps (CSV files containing GitHub metadata). The commits table has more than 891 Million rows and the project_commits has more than 5.4 Billion rows. Since these tables are quite big I had to load them using LOAD DATA INFILE with foreign key check off. I am using MyISAM engine. After finishing importing records into the tables, I am trying to create indexes for these tables.

I am running the following mysql command for the commits table and it didn't finish in more than 12 hours.

CREATE UNIQUE INDEX `sha` ON `ghtorrent_restore`.`commits` (`sha` ASC)  COMMENT '';

The commits table looks like below:

I have read other stackexchange questions regarding slow indexing and set the following in my.cnf file in /etc/mysql directory.

[mysqld]
bulk_insert_buffer_size=1G
myisam_sort_buffer_size=8G
key_buffer_size=6G
sort_buffer_size=10M

Since the previous command was not finishing in time, I had to stop it with ctrl+z from the console. I checked the table on MySQL workbench, it is not showing as corrupted, but it shows some 36GB as index length.

Importing this table took about 25 minutes, so I am expecting indexing it should not take more than an hour, but I am running the 'create unique index' command now for about 2 hours without any sign of progress.

When I run the command, mysqld takes a lot of cpu and it keeps taking memory. After reaching some 6GB it becomes less active and seems almost doing nothing.

Here is how the command (selected one in the following picture) looks from the mysql workbench.

I am running Mysql 5.7.22-0ubuntu0.16.04.1 on a Linux Mint 17.03 machine with 16GB RAM.

Since I am not an advanced user, any help will be of great help.

Update [as Wilson H. suggested]:

my.cnf file [06/05/2018 01:06]

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
secure-file-priv = ""

[mysqld]
bulk_insert_buffer_size=1G
myisam_sort_buffer_size=8G
key_buffer_size=6G
sort_buffer_size=10M

Here are snapshots of buffer and cache related variables.

I have not modified anything else in my mysql installation. Mysqld takes different amount of memory depending on different tables out of total 16GB ram available in the system. I do not run any cpu/memory intensive application beside mysql.

Interesting Observation: Some tests with other tables showing increasing time with respect to increasing number of rows. The trend looks polynomial.

The following stats were captured while the last mysql command from the above picture was being executed (i.e., indexing for table 'projects').

top:

iostat -x:

ulimit -a:

df -h:

Update 2: Since creating index on 'commits' table was not finishing, I was trying out other tables and finally I attempted indexing on the 'project_commits' table before sleeping last night. To my surprise, I found it took only 18 minutes to finish the indexing.

I didn't make any additional modification and I do not understand why 'commits' table is never finishing. I am running the indexing again on the 'commits' table and see how far it goes.

Update 3:

'SHOW CREATE TABLE commits;'

CREATE TABLE `commits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sha` varchar(40) DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  `committer_id` int(11) DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `commits_ibfk_1` (`author_id`),
  KEY `commits_ibfk_2` (`committer_id`),
  KEY `commits_ibfk_3` (`project_id`)
) ENGINE=MyISAM AUTO_INCREMENT=922093754 DEFAULT CHARSET=utf8

SHOW PROCESSLIST:

Bottomline:

Indexing for all tables are completed except for the 'commit' table (i.e., the following command is not finish executing).

CREATE UNIQUE INDEX `sha` ON `ghtorrent_restore`.`commits` (`sha` ASC)  COMMENT '';

When I am running a query (see the below picture) which counts about 19000 rows from 891 million rows, it takes about 76 seconds. Is this time too high given I have a computer with Core i707700HQ CPU @ 2.8Ghz x 4, 16GB DDR4 Ram and the database is installed on HDD with 7200RPM? Does the 76 seconds indicate that indexing is not working properly in 'commits' table? Please note that this query was executed right after booting the computer to avoid impacts from buffers.

Answer 1

There are multiple problems.

Auto_inc of 922M is half way to the 2 billion limit on INT SIGNED. Suggest you change to INT UNSIGNED (4 billion limit) during the next ALTER.

MyISAM saves disk space, but is otherwise 'worse' than InnoDB. Note: changing to InnoDB will require changing several settings.

FOREIGN KEYS are ignored by MyISAM.

If sha is a SHA-1 hash, it is terrible for indexing.

If sha is a SHA-1 hash, it could be compressed to BINARY(20) via UNHEX(). This would shrink the table by over 20GB, 30% of the current size!

If sha is a SHA-1 hash, don't use utf8; use ascii or latin1.

If sha is a SHA-1 hash, and that is the column you are creating the UNIQUE index on, check SHOW PROCESSLIST. If it says "Repairing by key_buffer", then you should kill it; it will take months to finish. If it says "Repairing by sort", then there is hope that it will finish.

Consider using smaller ids than the 4-byte INT for author, committer, and project -- unless you really have many billion distinct values. Wait! What? Each of those is going to be UNIQUE? I doubt it.

What SELECTs do you have? Might some of them need 'composite' indexes?

Put multiple ALTERs (including creating indexes) into a single statement. Each ALTER is a complete copy of the table (in MyISAM).

myisam_sort_buffer_size = 8G is half of RAM. This is bad. Suggest 3G.

Polynomial

The trend looks polynomial.

I hope you are not adding a UNIQUE INDEX on a column on a table that already has the same column as PRIMARY KEY. That would be totally redundant. Please provide SHOW CREATE TABLE for one of them.

Why "polynomial"? If it is (and I question such), then it would be because of the nature of "Repair by key_buffer":

For each row, look up the column in all unique (including primary) indexes to make sure it is not a "duplicate". This lookup requires fetching the block of the index's BTree and putting it into the "key_buffer" for testing. Depending on the 'order' of the key versus the 'order' of the data:

• For auto_increment or timestamp, where the row were inserted chronological (and the table had not become fragmented), the next block needed is very likely to be the one that was just touched. This is the most efficient because it needs the least I/O.
• For SHA1/MD5/UUID/etc, this lookup will be jumping all around the index. So the next block needed is less and less likely to be in the in-RAM key_buffer. At the extreme, this leads to almost 1 disk read per lookup!
• For other indexes, the timing is somewhere in between.

Answer 2

891 Million rows is quite a bit. This is a problem with MyISAM and unique indexes.

• Bug #22487 mysql performs extremely slow when creating unique indexes on huge table
• WL#1333: Speed up ALTER TABLE (part 3: Rebuild unique indexes by sorting in ALTER TABLE)

Perhaps you could migrate to PostgreSQL, which doesn't have this problem? ghtorrent seems to support PostgreSQL