0

Basic info: My Mysql database is using TokuDB, InnoDB, MyIsam tables.

Server info: 16 core, 64GB RAM, CentOS 6.2, MySQL v 5.5

Process: 1. Import large amount data from one text file to one TokuDB table. 2. Select data by joining different table.

When process 1 and 2 running at the same time, the whole operation speed will be much slower.

Does anyone know specific reason?

Any suggestions to improve it?

Nick Ren
  • 65
  • 3
  • 10
  • Probably hitting the disk in two different places... – lc. Jul 12 '12 at 16:45
  • I agree. Regardless of the speed and 'low' latency of an HDD, on a dual process, it will continuously need to switch reading between one part of the disk and another. If this process of importing and joined selection happens a lot, you might want to consider adding a dedicated SSD drive for your SQL server. Since there is no disk and read/ write head, the latency is virtually zero. – Battle_707 Jul 12 '12 at 16:49
  • Can you give more specifics on your experiment? It would be helpful to understand your schema, how you are loading data, what your select queries look like, etc. – tmcallaghan Jul 19 '12 at 17:12
  • Hi, this is not a testing project I made myself. The schema is all about human genome sequencing data, hard to descript here. We had a kind of pipeline to continually generate large amount of data(txt files) in one disk location and then load generated data to our Database. Meanwhile, we had analysis tool need to get data from the same DB(Just some simple joined sql query). The problem is if the loading and retrieving data at the same time, it will slow down the insertion speed a lot... – Nick Ren Jul 20 '12 at 14:30

2 Answers2

0

Separate the IO to different disks/arrays. Having all IO on a single partition/array results in horrible performance. If possible, invest in a dedicated drive array such as IBM's DS3524 or HP Smart Array. Connecting the DB Server though Fibre Channel (or better yet SAS2) will give you an incredible performance gain. I stopped putting lots of disks into the server itself a few years ago. I get 5X the performance with MySQL on a drive array than disk in the server.

gmm
  • 89
  • 2
0

in tokudb, load data infile works much faster when importing on empty tables (especially when you have non-increment primarey key, or unique index)

dan
  • 15
  • 3