Performance issue: Load data infile is making select query slow

Question

I have a program which scans twitter, facebook, google+ 24 hours a day. Per user a searchlist is running and inserted with (100 results at one time, function runs in a loop untill there are not futher results)

Yii::app()->db->createCommand(
     "LOAD DATA INFILE '/var/tmp/inboxli_user".$user.".txt'
      INTO TABLE inbox
      FIELDS TERMINATED BY ',$%'
      LINES STARTING BY 'thisisthebeginningxxx'
          (created_on, created_at, tweet, tweet_id, profile_image,
           twitter_user_id, screenname, followers, lang, tags, type,
           positive_score, readme, answered, deleted, searchlist_id,
           handled_by, used_as_newsitem,  user_id)
     "                       )->execute();

into the database in order to keep the load as small as possible on the server. How ever when my functions are doing the bulk insert, my select functions runs very slow. Normally the inbox loads within 1.5 second but when the insertion is running sometimes it takes like 20 seconds for a page to open.

My question how can i optimize this? So insertion and select can use the database at the same time without slowing things down?

UPDATE: I notice if like only 5 new news items are inserted into the database in my myisam table the first time it loads slow and the second time not. So maybe load in file doesnot insert the indexes the right way? — Paul De Zwaan, Apr 07 '16 at 15:19

score 1 · Answer 1 · answered Apr 07 '16 at 23:32

1

Get off MyISAM! Use InnoDB; it does a much better job of not locking out other actions.
Load data is very efficient, increase the count to, say, 500.
What indexes do you have? Let's see SHOW CREATE TABLE. DROP any unnecessary indexes; this will speed up the LOAD.
Consider turning off the Query cache.

answered Apr 07 '16 at 23:32

Rick James

135,179
13
127
222

+1 for this comment turning off query caching helped a lot http://dev.mysql.com/doc/refman/5.1/en/query-cache-in-select.html SQL_NO_CACHE adding this in the select statement, now the select doesnot slow down anymore, InnoDB i was already using / indexes were set good and i did some small modification on my.cnf for innodb optimalization. – Paul De Zwaan Apr 09 '16 at 00:54
`SELECT` _does_ slow down, just not as much. Furthermore, in a system with lots of 'simultaneous' connections _every_ select will hit a QC Mutex before it realizes that it won't use the QC. This was not a problem is single-core machines when the QC was invented; but it is a big problem for heavily used 32-core machines. – Rick James Apr 09 '16 at 01:03
The way to _really_ turn it off is `query_cache_type = OFF` _and_ `query_cache_size = 0`. – Rick James Apr 09 '16 at 01:06
Thanks for the explination Rick, has been more than helpfull, i just corrected this and things running good now. – Paul De Zwaan Apr 10 '16 at 04:34

score 0 · Answer 2 · edited May 23 '17 at 12:24

0

Well, first you should make sure you indexed your table correctly. See How does database indexing work?

that will speed up the select statements pretty much.

Second, it's possible that you split your file into multiple chunks. So the database server removes the caches and logs for each new file you loaded.

See: https://www.percona.com/blog/2008/07/03/how-to-load-large-files-safely-into-innodb-with-load-data-infile/

edited May 23 '17 at 12:24

Community

1
1

answered Apr 07 '16 at 07:34

Oxy Synth

154
2
11

Thank you for your answer, however the table is good indexed and also the text file only contains 100 results each time it inserts. So its seems unlikely that this is the case. – Paul De Zwaan Apr 07 '16 at 14:14

Performance issue: Load data infile is making select query slow

2 Answers2