Recently while working on a very large import process using the Migrate module and Drupal 7 I encountered some odd issues. I saw my basic migrations had some deadlocking issues and in general the speed was very very slow. After a bit of research I was able to find the necessary documentation to achieve writing many thousands of writes per minute.

As an example of the conditions I was experiencing I have the following logged output:

Example error message of Deadlocking:

 -> T0: in drupal_write_record() (line 7239 of
/var/www/2015/presidency/drupal7-project//htdocs/includes/common.inc).

 -> T0: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found
when trying to get lock; try restarting transaction: UPDATE {node} SET
 type=:db_update_placeholder_0,

Example of estimated throughput of the migrate module with no MySQL tuning:

drush --user=1 mi PrezDocument --limit="1000 items" --feedback="100 items"
Processed 100 (100 created, 0 updated, 0 failed, 0 ignored) in 25.5 sec (235/min) - continuing with 'PrezDocument'    [status]
Processed 100 (100 created, 0 updated, 0 failed, 0 ignored) in 25.3 sec (237/min) - continuing with 'PrezDocument'    [status]
Processed 100 (100 created, 0 updated, 0 failed, 0 ignored) in 25 sec (240/min) - continuing with 'PrezDocument'      [status]
Processed 100 (100 created, 0 updated, 0 failed, 0 ignored) in 26.8 sec (224/min) - continuing with 'PrezDocument'    [status]
Processed 100 (100 created, 0 updated, 0 failed, 0 ignored) in 24.7 sec (243/min) - continuing with 'PrezDocument'    [status]
Processed 100 (100 created, 0 updated, 0 failed, 0 ignored) in 25 sec (240/min) - continuing with 'PrezDocument'      [status]
Processed 100 (100 created, 0 updated, 0 failed, 0 ignored) in 24 sec (251/min) - continuing with 'PrezDocument'      [status]
Processed 100 (100 created, 0 updated, 0 failed, 0 ignored) in 28.8 sec (208/min) - continuing with 'PrezDocument     [status]
Processed 100 (100 created, 0 updated, 0 failed, 0 ignored) in 30.2 sec (198/min) - continuing with 'PrezDocument'

As you can see the system is experiencing deadlocking issues and the overall write speed is very low.  Well *cough* this is generally because I have never tuned my MySQL instance for any sort of workload, I had been running MySQL with the standard Ubuntu 14.04 LTS my.cnf configuration file.

After much googling and research I found this very useful page of the Migrate Documentation under Improving Migration Performance that lists some changes you can make to tune your MySQL instance to better tune it for write-heavy loads:

    innodb_flush_log_at_trx_commit = 0
    innodb_doublewrite = 0
    innodb_support_xa = 0


    key_buffer_size = 128M
    max_allowed_packet = 20M
    query_cache_size = 128M
    table_open_cache = 64
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 16M
    join_buffer_size = 4M
    tmp_table_size = 92M
    max_heap_table_size = 92M
    sort_buffer_size = 4M
    innodb_additional_mem_pool_size = 8M

I’ve read the Migrate documentation before (in years past) and I guess I neglected to see this information previously. The documentation on Drupal.org is community driven so one lesson to learn here is to check back on documentation you’ve already read because over time it’s possible the documentation will have been improved!

So I added the above MySQL configuration values to the Ubuntu MySQL my.cnf file found in /etc/mysql/my.cnf. and I restarted MySQL by issuing sudo service mysql restart on my local development box.

For my system this resolved the Deadlocking issues and on average my migration processes can hit ~2000 writes per second vastly improving my migration time. Let this be a lesson that sometimes a “quick fix” can net you massive gains. I have not gone into detail on what all the MySQL configuration values affect on my local instance, that is simply learned by looking at the MySQL documentation and like most things on the web, many problems have already been solved … you simply need to know how best to ask your favorite Search Engine the problem you are having — you’ll likely quickly find the answer.

Looking for quality web hosting? Look no further than Arvixe Web Hosting!