•  

Announcement

Announcement Module
Collapse
No announcement yet.

Simultaneous LOAD DATA INFILE and SELECT

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • Simultaneous LOAD DATA INFILE and SELECT

    #1
    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,
    Tags: None

  • #2
    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.

    Comment


    • #3
      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?

      Comment


      • #4
        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

        Comment


        • #5
          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 ) )

          Comment

          Working...
          X