I have a vBulletin site with 60k users, 100k forum threads and 1m posts that I'm migrating to Drupal. I'm trying to get the best possible speeds to migrate/rollback the site for testing, so I was wondering if anyone else has found ways of getting the best speeds during migration.

Here's what I've found so far...as I get more info together I'll create a documentation page for the Migrate module

  • Use Drush - approximately 20% faster than the Migrate UI
  • Switch off the pathauto and token modules, as well as any other modules that aren't needed for migration
  • Fix issues that produce error messages during migrations
  • In php.ini increase memory_limit, and increase max_execution_time to 300 seconds

However, I've also got something strange happening with 2 servers that I'm testing...

  1. Cloud server with 3G Ram, 1 CPU and 35GB SSD disk
  2. Dedicated server - AMD FX-8350, 4018MHz, 32GB, 1 CPUs, 8 cores, 464GB SSD

Both servers are fairly new, with only a few small sites on them, very similar configurations (both Centos 6.3 64 bit, Apache 2.2.2.4, PHP 5.3.24) and almost the same hosting company (different brands). However, the dedicated server is running APC, memcached and Varnish.

The cloud server is currently running my migration about 3-4x faster than the dedicated server.

Any tips on where I should look for reasons why the dedicated server is performing so badly? I've asked my host to add APC and memcache to the cloud server, to see if it slows things down.

Any other general speed tips would also be welcome and I'll add them to the documentation.

Stu

Comments

StuartDH’s picture

StuartDH commented
Issue summary: View changes

typos

StuartDH’s picture

StuartDH commented
Issue summary: View changes

extra

StuartDH’s picture

StuartDH commented
Issue summary: View changes

.....

StuartDH’s picture

StuartDH commented
Status: Postponed (maintainer needs more info) » Active

Both servers now have APC and memcache installed with the same settings, so they're even more similar, but the big differences are still showing in the 'Throughput' results.

As an example, importing forum threads using
drush mi vBulletinThread --limit="5000 items" --instruments

produces the following results for each server

================================================

32GB Dedicated server results

Processed 5000(5000 created, 0 updated, 0 failed, 0 ignored) in [completed]
146.5 sec (2047/mm) - done with vBulletinThread
Timer Cum (sec) Count Avg (msec)
page 146.839 1 146838.53
destination import 121.433 5000 24.287
node_save 64.432 5000 12.886
MigrateStatisticsEntityHandler-complete 19.538 5000 3.908
savelDMapping 17.895 5000 3.579
MigrateFieldsEntityHandler-prepare 1.546 5000 0.309
lookupDestinationlD 1.514 10000 0.151
MigrateDestinationEntity->prepareFields 1.495 5000 0.299
MigrateSourceSQL perîorrnRewind 1.239 1 1238.55
MigrateSourceSQL execute 1.239 1 1238.53
mapRowBySouæe 1.068 5000 0.214
MigrateFìeldsEntityHandler-complete 1.005 5000 0.201
Migrate Destination Entity->complete flelds 0.926 5000 0.185
MigrateTaxonomyTem, ReferenceFìeldHandler->prepare 0.284 15000 0.019
Migrate Extras Node Flag Handler->complete 0.164 5000 0.033
MigrateText Field Handler->prepare 0.105 5000 0.021
MigrateSourceSQLgetNextRow 0.102 5000 0.02
MigratePathEntityHandler-prepare 0.099 5000 0.02
MigrateValueFìeldHandler-prepare 0.052 5000 0.01
Migrate Pathauto Handler-’prepare 0.05 5000 0.01
MigratePoll EntityHandler->complete 0.049 5000 0.01
vBulletinThreadMigration prepareRow 0.049 5000 0.01

================================================

3GB Cloud Server

Processed 5000(5000 created, 0 updated, 0 failed, 0 ignored) in 81.1 [completed]
sec (3700/mm) - done with ‘vBulletinThread
Timer Cum (sec) Count Avg (msec)
page 81.341 1 81340.96
destination import 69.947 5000 13.989
node_save 57.174 5000 11.435
MigrateStatisticsEntityHandler-complete 3.679 5000 0.736
savelDMapping 3.406 5000 0.681
lookupDestinationlD 1.749 10000 0.175
Migrate Fields EntityHandler->prepare 1.683 5000 0.337
MigrateDestinationEntity->prepareFields 1.626 5000 0.325
MigrateSourceSQL perlom, Rewind 1.181 1 1181.44
MigrateSourceSQLexecute 1.181 1 1181.4
mapRowBySourte 1.1 5000 0.22
Migrate Fields EntityHandler->complete 1.044 5000 0.209
MigrateDestinationEntity->completeflelds 0.961 5000 0.192
MigrateTaxonornyTern, ReferenceFieldHandler-’prepare 0.315 15000 0.021
Migrate Extras Node Rag Handler-’complete 0.161 5000 0.032
MigrateSourceSQLgetNextRow 0.102 5000 0.02
MigrateTextPieldHandler->prepare 0.102 5000 0.02
MigratePathEntityHandler-prepare 0.101 5000 0.02
MigrateValueFieldHandler-prepare 0.096 5000 0.019
Migrate Pathauto Handler-’prepare 0.054 5000 0.011
vBulletinThreadMigration prepareRow 0.051 5000 0.01
Migrate Poll EntityHandler->complete 0.05 5000 0.01

================================================

Any idea what could be causing such a big difference in the 'destination import', 'MigrateStatisticsEntityHandler' and 'savelDMapping' times?

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Status: Active » Postponed (maintainer needs more info)

<code> tags would be helpful to display the tables of output.

Usually most migration time is taken up in the Drupal save functions (node_save in this case), and the main bottleneck there is writing to the database. Tuning MySQL for writing can work wonders here - see #1905318: Documenting optimizing MySQL for performance (I still need to add a page to the docs). Here are some basic parameters I use when spinning up development servers:

        key_buffer_size = 128M
        max_allowed_packet = 20M
        table_open_cache = 64
        read_buffer_size = 2M
        read_rnd_buffer_size = 8M
        myisam_sort_buffer_size = 16M
        thread_cache_size = 8
        query_cache_size = 128M
        join_buffer_size = 4M
        tmp_table_size = 92M
        max_heap_table_size = 92M
        sort_buffer_size = 4M
        thread_concurrency = 8
        log-bin = 0
        innodb_buffer_pool_size = 2G
        innodb_log_file_size = 128M
        innodb_additional_mem_pool_size = 8M
        innodb_log_buffer_size = 8M
        innodb_flush_log_at_trx_commit = 0
        innodb_doublewrite = 0
        innodb_support_xa = 0
        innodb_file_per_table
StuartDH’s picture

StuartDH commented

Thanks for the list of tweaks Mike. I'll start giving them a try and see how much things improve.

The bit I can't quite get my head around is why my 3GB cloud server is running imports 3-4x faster than the same setup (site, source db, my.cnf settings etc) on my 32GB dedicated server.

It seems as though something must be misconfigured on the 32GB box and I was hoping that the above results might point to a likely cause.

The hosts are doing a great job of going through lots of options, so hopefully the dedicated server will end up giving results that are a lot better than the 3GB cloud.

Stu

StuartDH’s picture

StuartDH commented

The hosts have identified that the likeliest cause of speed difference between my two servers is that the cloud server is on RAID 10
(RAID 1+0) SSD. Whereas as the Dedicated server is SSD-backed storage, but no RAID whatsoever.

As RAID 10 is mirrored striping, especially with heavy write activity, effective striping of disks (of any type) will increase performance dramatically compared to no striping.

The difference is considerable, so I'll move the bigger server to a raid 10 setup and post the results here.

Stu

StuartDH’s picture

StuartDH commented

I've resized the dedicated server to RAID 10, with a slower CPU Xeon 1220 v2 and only 12GB ram, but I am now getting throughput considerably higher than the original dedicated server and faster than the cloud results.

Migration-----------Rows----------No Raid---------Raid 10

vBulletinAvatar-----3647-----------4813/min-------19111/min
vBulletinTag--------3373-----------3751/min-------11733/min
vBulletinUser-------53645----------1337/min-------6222/min
vBulletinThread-----94882----------2026/min-------3229/min
vBulletinPost-------962427---------2099/min-------4965/min

So clearly RAID 10 has a massive impact on import speeds and well worth looking at for large imports

StuartDH’s picture

StuartDH commented
Status: Active » Postponed (maintainer needs more info)

Just for info, it looks like SSD run my migrations about twice as fast as SATA drives

The same site and migrations on Opteron x8-6128 with 2 CPUs and 16 cores, 16GB ram and 917GB SATA Raid 10 produced the following results:

Migration-----------Rows----------Raid 10

vBulletinAvatar-----3647-----------9368/min
vBulletinTag--------3373-----------6507/min
vBulletinUser-------53645----------3166/min
vBulletinThread-----94882----------1517/min
vBulletinPost-------962427---------2437/min

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Status: Postponed (maintainer needs more info) » Fixed

Thanks for your reports - I've incorporated them into the new tuning page at http://drupal.org/node/1994584. Let's have any follow-ups in #1905318: Documenting optimizing MySQL for performance.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Anonymous’s picture

(not verified) commented
Issue summary: View changes

.....