2

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:

  1. 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?

  2. Is Mysql is the right solution to my case, or should I use different DB server for the pulling this info?

  3. 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?
  • Because the table is bigger than can be cached; your reads will each take about one disk hit. Slow. (UUIDs suck in this respect.) – Rick James Jun 26 '16 at 6:06
2

Let's do some calculation:

uuid: 36 bytes (Add 1 because it is varchar) ==> 37
uuid_tags_codes: 4 bytes
When indexed, full length of uuid field: 100 bytes

(37+4+100)bytes * 162,281,544 rows = 21.3 G

So, 21G without adding the overhead! Therefore, your first problem is the space.

To answer your questions:

  1. It is more than 6-8G. To ba safe you need ~30G. Single MyISAM table can handle that. I don't recommend it though. MyISAM is not as safe as InnoDB for example.
  2. MySQL is not wrong solution, but I think there are better solution. Since it is kind of look up table, key-value store would do better job. For example, memcache, Redis.
  3. You can improve the structure by making uuid char(36). In fact, this article has even more efficient solution. Also, use the smallest integer type that fits your maximum value of uuid_tags_codes
  4. You may want to load the data first to the table without its indexes, then create the required index.

HTH

  • Thank you for you great answer, are memcache and redis storing data on disk, in other words will rhey be a solution to my case in such wheight of data? – talsibony Jun 10 '16 at 21:45
  • memcache doesn't keep data on disk. Redis on the other hand can store the data on disk – Jehad Keriaki Jun 12 '16 at 1:32
  • Thank you for you help and answer I will consider migrating to redis – talsibony Jun 12 '16 at 7:20
1
  • Sort the data by uuid before loading it -- this will speed up the load and shrink the disk space some (because of less fragmentation)
  • Use InnoDB -- this will speed up the SELECTs.
  • Pack the uuids into BINARY(16) -- this will shrink the disk space by perhaps a factor of 2.
  • If uuid_tags_codes is just tiny numbers, use TINYINT UNSIGNED -- 1 byte instead of 4. (Range: 0..255). Or SMALLINT UNSIGNED (2 bytes; 0..64K)
  • Stored routines for converting to/from BINARY(16)
  • thank you very much for your tips, currently I migrated to redis but it looks like it will cost, I have to have 16GB ram machine which is quite expensive, I guess your answer will cut some of the resources in mysql, I will try to do it with mysql and see the results, It may save me some disk space and ram :) – talsibony Jun 26 '16 at 5:57

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.