MySQL Performance: MySQL => Simultaneous LOAD DATA INFILE and SELECT

Members

Help

Home

Home » Performance » MySQL » Simultaneous LOAD DATA INFILE and SELECT

Show: Today's Messages :: Show Polls :: Message Navigator

Simultaneous LOAD DATA INFILE and SELECT [message #3620]

Fri, 10 October 2008 11:12

cowboymathu
Messages: 7
Registered: October 2008

Junior Member

Hi all,

When LOAD DATA INFILE query is executed if SELECT from same_table is executed, the SELECT query is locked. I want to run both queries simultaneously. How can I achieve that.

Thanks,

Report message to a moderator

Re: Simultaneous LOAD DATA INFILE and SELECT [message #3628 is a reply to message #3620 ]

Tue, 14 October 2008 07:19

lyolechka
Messages: 4
Registered: October 2008
Location: London

Junior Member

Hi cowboymathu,

What storage engine is your table? In MyIsam you can use LOAD DATA CONCURRENT INFILE to allow concurrent reads from the table while you are inserting into it. This can slow the performance of LOAD DATA especially with large amounts of data to load and also may lead to inconsistent reads from the table. More here: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

An alternative would be: create a copy of the table you want to load data to load data into the copy, swap table names when load is done.

Hope this helps.

Report message to a moderator

Re: Simultaneous LOAD DATA INFILE and SELECT [message #3631 is a reply to message #3628 ]

Wed, 15 October 2008 09:30

cowboymathu
Messages: 7
Registered: October 2008

Junior Member

Hi lyolechka,

Thanks for your reply. Yes, I am using MyISAM type with partitioning. Currently I am using LOAD DATA CONCURRENT INFILE syntax but seems it does not allow me to do simultaneous SELECT.

Your alternative idea seems better but in my instance I am using table with both partition and sub partition and which has almost 1 billion (1000 millions) of records. It eats more than 400 GB in capacity. So making copy is not feasible in this.

Any better way in my case?

Report message to a moderator

Re: Simultaneous LOAD DATA INFILE and SELECT [message #3640 is a reply to message #3631 ]

Mon, 20 October 2008 09:44

lyolechka
Messages: 4
Registered: October 2008
Location: London

Junior Member

Hi cowboymathu,

Sorry for a long reply. Have you found a solution yet?

Quote:

but seems it does not allow me to do simultaneous SELECT

Are the "concurrent" SELECTs waiting in the queue? Does the table get locked?

Which version of Mysql are you using? May be it is the LOAD DATA INFILE being too slow that it hangs everything? I have found this in the mysql manual perhaps it can help:

"Prior to MySQL 5.1.23, LOAD DATA performed very poorly when importing into partitioned tables. The statement now uses buffering to improve performance; however, the buffer uses 130 KB memory per partition to achieve this. (Bug#26527)"

The patch is here: http://lists.mysql.com/commits/21693

Report message to a moderator

Re: Simultaneous LOAD DATA INFILE and SELECT [message #3642 is a reply to message #3640 ]

Tue, 21 October 2008 02:44

cowboymathu
Messages: 7
Registered: October 2008

Junior Member

Hi lyolechka,

yes. after concurrent SELECT query is started the table is locked and until that query fully executed, all the select queries are in queue.

The version I am using is 5.1.22.

This version is able to manage around 20 millions of records loading in 4 minutes. For my application it is all right. Is it slow performance according to your say?

thanks for the reply. I will try with the patch as well (I have never used patch for mysql before Smile

)

Report message to a moderator

Previous Topic:	Innodb tweaks, feedback on my ideas?
Next Topic:	Avoiding Stored Procedure Metadata Requests

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 6 21:09:39 EDT 2009

Total time taken to generate the page: 0.05972 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.