Large import causes mysql to peg cpu forever

[rya]

Hi,

I'm running 6.1.2 CE.

I just got done importing 20,000 Account records.

When the import was done, the web interface said that the import was complete (in the same box that does the count-up of the records, at Import Step 4), but it didn't get to the next screen that told me the import was successful.

Furthermore, mysqld is stuck using over 40% cpu (for the last half hour or so).

If I look at the currently running queries, I can see that one query has been running for awhile:

mysql> show full processlist\G
*************************** 1. row ***************************
     Id: 880
   User: sugarcrm
   Host: localhost
     db: sugarcrm
Command: Query
   Time: 566
  State: Sending data
   Info: SELECT count(*) c FROM accounts   LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c , users_last_import where ( accounts.id IN (
                        SELECT users_last_import.bean_id
                            FROM users_last_import
                            WHERE users_last_import.assigned_user_id = '1'
                                AND users_last_import.bean_type = 'Account'
                                AND users_last_import.deleted = 0 )) AND accounts.deleted=0 AND users_last_import.assigned_user_id = '1'
                AND users_last_import.bean_type = 'Account'
                AND users_last_import.bean_id = accounts.id
                AND users_last_import.deleted = 0
                AND accounts.deleted = 0

From what I can tell, the data all imported. Except a couple rows - my CSV had 20,617 rows incl. the header, while the list-view of accounts in SugarCRM says (1-20 of 20614). I expected 20,616 accounts. There is also no indicated problems in the files in the sugarcrm/cache/import directory. (dupes_1.csv and errorrecords_1.csv only contain 1 row, which appears to be the header row from the import.) However, after further investigation, I did find where I had two records that had duplicate ID's. I wonder why dupes_1.csv didn't have this info. (Maybe that file shows data as a result of the long queries?)

I am able to revive my runaway mysqld by killing the long-running query, and everything seems to be fine.

Anyway, so my main issue is that the company wants to do daily or weekly imports of the data from their ERP, and I can't have it keep sticking like this.

Any ideas? Maybe instead of using the website to import, I should just take the CSV and make my own import script that dumps the data directly into the the DB (although dealing with putting custom fields in a different table might be a slight pain).

thanks for any help!

-Ray

[rya]

..So I just did an import over top the original 20,000+ entries (changing something in one account record just to see if it would update properly), and it didn't hang after the import finished. So maybe doing an import all the time won't be a problem, now that the first one is done.

[eggsurplus]

If you do run into issues and importing will be a regular thing you may want to check into an ETL tool. That should help automate the process. You'd have to set it up to insert directly into the db though. I can't recall off-hand but there is an ETL solution with a Sugar plugin already making that portion easier to deal with.