I am facing a problem with importing large data into single mysql table. Basically I assume it is happening because limited machine resources but, I am having troubles to proof it.
this is my CSV file information:
root@my:~# tail -n 1 /tmp/keys.csv
ffff7771-e330-9d8e-6783-daf8a75fe4ef,7
root@my:~# wc -l /tmp/keys.csv
162281544 /tmp/keys.csv
So basically I have 162,281,544 lines of
ffff7771-e330-9d8e-6783-daf8a75fe4ef,7
in my CSV and its file size is 6G
I need to import this table on a daily basis and to use it only for SELECT information by the KEY eg:
SELECT uuid_tags_codes FROM keys WHERE uuid = 'ffff7771-e330-9d8e-6783-daf8a75fe4ef';
I choose MyISAM as my table engine because I read that it should be better from selecting information.
CREATE TABLE `keys` (
`uuid` varchar(60) NOT NULL,
`uuid_tags_codes` int(11) NOT NULL,
PRIMARY KEY (`uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
After all this Information and I really hope it is clarifying all my technical stuff, If you think I missed something I will be happy to provide more details as you wish.
The problem is when I am using load data in file command as below:
LOAD DATA LOCAL INFILE '/tmp/keys.csv'
INTO TABLE key
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(uuid,uuid_tags_codes);
After I am executing this command it mysql is running but it is unresponding and I have to delete the table from filesystem and restart it, Also I think the system have no enough data storage 10G free space in total before importing the file. But this is OK I always can Improve the server machine, by extending the SSD size.
Here are my questions:
In case I extend my SSD drive should this process be OK, is mysql single table should be able to handle 6-8G data of 2 cols?
Is Mysql is the right solution to my case, or should I use different DB server for the pulling this info?
- In case using mysql is ok, is my table definition is ok for the data I am storing in it? and what type of indexing should I use?