0

I am trying to import a .sql file which has MyISAM engine, about 51 million rows with 18 columns. The file has 1 primary and 1 unique key. I have after lurking around this site and reading mysql manual learned about how to import by using repair by sort. This however started my problem. Although show processlist shows Repair by sort I cant see the tmp tables that it supposedly creates for the process not to mention when I check the /mysql/data directory .myd and .myi files totals the .sql file but the process does not end. I have left the process open for 2 days without any change at which time I killed it.

Could this be because the computer I have to use as mysql server is wamp or cause of the hardware limitations which has 2.gen i5 with 4GB Ram and 1 500GB HDD with 2 partitions or could the dump I have been given is corrupted?

Thnx in advance for any nudge in the right direction to this newbie about mysql and well databases in general. (Below is the parts of the my.ini with the help of percona config tool)

tmpdir = D:/temp
key_buffer_size = 2344M
myisam_file_sort_size = 80G
myisam_max_sort_file_size = 80G
myisam_sort_buffer_size=164M
join_buffer=1M
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
query_cache_size=132M
table_cache=1024
thread_cache_size=256
max_allowed_packet=156M
Improve this question
0

The tmp tables won't be in /mysql/data, but rather in the subdirectory corresponding to the database. Or maybe in the tmpdir directory.

key_buffer_size = 2344M -- that is just for indexes. In a 4GB machine, that does not leave enough room for data. Change it to 700M.

thread_cache_size -- 0 is better for Windows.

query_cache_size=132M -- too big; drop to 50M

max_allowed_packet=156M -- Probably too high. Suggest 32M.

None of those necessarily explain the symptoms you have. Can you tell if mysql is "swapping"? If it is, that would be deadly, and my suggestions would probably prevent swapping.

Is the "repair" because of a crash? If so, you should seriously consider switching to InnoDB. At least put it on your roadmap. But be aware that the disk footprint with be 2-3 times as big.

"Repair by sorting" would, I think, proceed as follows:

  1. Load all the data into the #sql...MYD file. Meanwhile the .MYI file would be 1K or empty.
  2. Read the data, sort it, and load it into the .MYI for one index.
  3. Ditto for second index.

I do not know if having two unique index complicates things, and necessitates use of the key_buffer.

Improve this answer
2
  • I have tried your recommendations about the variables but that forced me on 'repair by keycache'. I don't know how to check whether there is swapping or not. As for why I'm repairing; the company I'm working for was using this from a website but those guys went under so we got the database(as it was our data in the first place) and trying to turn it into either an intranet or an easy program to access the data as that is all we do. And 'repair by sort' process should work like u have described and should create tmp file inside the tmpdir but it does not so not sure it is 'repairing by sort – Allison Kirkwright Apr 11 '16 at 10:47
  • 'Repair by sort' is usually much faster for big tables. Since you are in the process of loading, I suggest you go straight to InnoDB. My notes on converting. – Rick James Apr 11 '16 at 18:40
0

You will insert by chunks, using mysqlsh util.importTable.

What is your version of MySQL? Your file is delimited by |,"?

what is value for your variables:

innodb_buffer_pool_size innodb_buffer_pool_instances innodb_write_io_threads innodb_reads_io_threads

Improve this answer
1
  • He said he's using MyISAM, so those variables don't matter. – Federico Razzoli Oct 30 '20 at 18:19

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.