Uploaded image for project: 'Doctrine 1'
  1. Doctrine 1
  2. DC-994

Doctrine_Data_Import creates unnecessary transactions, big slowdown


    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.3
    • Fix Version/s: None
    • Component/s: Import/Export
    • Labels:
    • Environment:
      MySQL, symfony doctrine:load-data


      While trying to load ~25M data fixtures (one big table with relations to 4 smaller ones, sfGuard included in that, row size around 500 bytes) in Symfony i ended up waiting around 80 minutes, while waiting i looked at what could have make it so dreadfully slow. Turns out, when Doctrine_Data_Load gets UnitOfWorks it executes save() on every new record. Save makes it's own transaction - not a problem if it's nested, but when this is the main transactions, 70000 of them make quite a difference. Remember - one of the main factors of DBMS speed is transactions/second.

      I patched Doctrine_Data_Import to wrap everything in one transaction, and the results were great - from 80 minutes i got down to around 10. Still, not as fast as it should be but now it's usable. The time difference is notable also in smaller dumps.

      Patch to speed up loading times included, would be great if you add it to trunk.

      Please note - i have not checked this patch with any other setup or DBMS, please do so.

      Also i have noticed something that might be a problem in much larger loads - if wrapping in a single transaction, my total memory usage went up for about 500M higher than in 70000 transactions. At some point, about 5 minutes in the process some kind of garbage collector fired and freed around 1 gig, so perhaps on larger dumps it might be a good idea to wrap the import not in one, but more transactions (like one transaction every 10000 operations).


        There are no comments yet on this issue.


          • Assignee:
            jwage Jonathan H. Wage
            chanibal Krzysztof Bociurko / ChanibaL
          • Votes:
            0 Vote for this issue
            1 Start watching this issue


            • Created: