CODEWITHSUNDEEP
phpmysqlperformanceyii
Paul De Zwaan
Paul De Zwaan

Reputation: 77

Performance issue: Load data infile is making select query slow

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?

Upvotes: 1

Views: 716

Answers (2)

Rick James
Rick James

Reputation: 142356

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

Upvotes: 1

Oxy Synth
Oxy Synth

Reputation: 154

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/

Upvotes: 0

Related Questions