3

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:

enter image description here

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.

enter image description here

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.

enter image description here

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

enter image description here

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.

buffer and cache related system 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.

Polynomial increase of time

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

top: top

iostat -x: iostat -x

ulimit -a:

ulimit -a

df -h:

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.

enter image description here

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: 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.

enter image description here

closed as off-topic by mustaccio, hot2use, RDFozz, Paul White Jul 7 '18 at 5:26

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Please post your complete my.cnf file for analysis in your original question labeled my.cnf as of mm/dd/ccyy hh mm We also need to know how much RAM is available to your server. Optional very helpful information, if available includes - htop, top & mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, includes hda & sda (SSD) filesystem type clues. free -h for a linux/unix Total Used Free Mem: and Swap:, – Wilson Hauck Jul 4 '18 at 22:00
  • 1
    You still haven't explicitly stated what your issue is. Or put differently: What is your actual question? Please consider asking an actual question. (e.g. How can I speed this up? Is this normal behaviour? ...). Thanks. – hot2use Jul 5 '18 at 12:08
  • WilsonHauck and hot2use: Could you please look at the bottom-line section (at the end of the question) and give your thoughts about the query execution time? – Narnia_Optimus Jul 7 '18 at 23:48
  • @Narnia_Optimus Considering there is NO index for project_id, 76 seconds for counting ~ 19,000 found is quite reasonable. Please consider posting on pastebin.com or here complete TEXT results of SHOW GLOBAL STATUS; and SHOW GLOBAL VARIABLES; and complete report from Mysqltuner.pl for analysis. Would like to Skype with you, view my profile, Network profile for Skype ID and other contact info. – Wilson Hauck Jul 8 '18 at 19:46
  • @Narnia_Optimus Please review these specifics for your MyISAM table limit consideration dev.mysql.com/doc/refman/5.7/en/table-size-limit.html you may be encountering limits without warnings or messages. Just a thought. – Wilson Hauck Jul 8 '18 at 20:54
3

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.
  • Impressed with your suggestions! Thanks a lot. I will check 'PROCESSLIST'. Figure 2 in the question shows 36.7 GiB for index length for the 'commits' table. I recognized even for some other table, there are values before executing 'CREATE INDEX' command. Do you know the reason for this? How can I be sure that indexing is done on the 'commits' table? – Narnia_Optimus Jul 5 '18 at 9:29
  • @Narnia_Optimus - MyISAM's .MYI file holds some dynamic, non-data, non-schema, information about the table, so it is at least 1024 bytes (1 "block"). In addition, that file holds any indexes (PRIMARY or secondary). (Innodb works differently.) The 36.7G indicates that there are some index(es); but it does not say how many indexes. SHOW CREATE TABLE commits is reliable at listing all the indexes (but not the space taken). – Rick James Jul 5 '18 at 13:10
  • @Narnia_Optimus - and I added some words about "polyonomial", though I think it is close to proportional to the table size. Yes, there are variations, but I can't explain them without seeing the schema. – Rick James Jul 5 '18 at 13:26
  • I have added 'show create table commits' and 'show PROCESSLISTS' results. For the latter, I see neither "Repairing by key_buffer" nor "Repairing by sort". – Narnia_Optimus Jul 5 '18 at 14:35
  • SHOW PROCESSLIST needs to be done via root while the CREATE INDEX is being done. – Rick James Jul 5 '18 at 14:51
1

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

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

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