I had a couple machines building up identical MyISAM tables. I've dumped all of those tables to a few files, and am now trying to restore them all into a single table on my own machine.
The first four restores, done by pv'ing the dumpfile into mysql, took no more than 10 hrs in total (may have been as little as 5). Those files were around 5-13 GB each.
The table is now about 60 million rows and 21 GB. It is also hash-partitioned into 100 partitions. However, I partitioned it when it was 20 million rows and got it up to 60 million without much problem.
Now I'm trying to dump another 5GB (maybe about 15 million rows) into the table, and pv is predicting it will take 50+ days. This doesn't seem to be a problem with pv, however, because the db's data directory is growing very slowly when I do the restore.
Additionally, I've looked at gnome-system-monitor, and the CPU and memory usage seems to be quite low. Tried rebooting and attempting to restore again, no change.
This is actually my second attempt at building up the big table. I tried once before over a longer period (waiting for the data to come in on the other machines), and suddenly I was getting this same problem.
This is a table with a single (natural) BIGINT primary key. I don't know a whole lot about database internals, but I tried a query suggested in this other post: slow load speed of data from mysqldump
to get the size of the index. Here are the results (reformatted for readability)
Storage Engine: MyISAM
Data Size: 19.706 GB
Index Size: 0.954 GB
Table Size: 20.660 GB
I would make sense that inserts would get slower as the table gets larger. However... it doesn't make sense that going from 60GB to 80 or less should take 100 times as long as going from 0 to 60.
So, does anyone have any guess what might be causing this?