Databases
StackOverflow2013
Postsdbo
POInnoDB takes over an hour to import 600MB file, MyISAM in a few minutes
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInnoDB takes over an hour to import 600MB file, MyISAM in a few minutes
    primarykey
    data
    text
    <p>I'm currently working on creating an environment to test performance of an app; I'm testing with MySQL and InnoDB to find out which can serve us best. Within this environment, we'll automatically prepare the database (load existing dumps) and instrument our test tools.</p> <p>I'm preparing to test the same data dump with MySQL and InnoDB, but I'm already failing to bring the initial import to an usable speed for the InnoDB part. The initial dump took longer, but that didn't concerned me yet:</p> <pre><code>$ for i in testdb_myisam testdb_innodb; do time mysqldump --extended-insert $i &gt; $i.sql; done real 0m38.152s user 0m8.381s sys 0m2.612s real 1m16.665s user 0m6.600s sys 0m2.552s </code></pre> <p>However, the import times were quite different:</p> <pre><code>$ for i in testdb_myisam testdb_innodb; do time mysql $i &lt; $i.sql; done real 2m52.821s user 0m10.505s sys 0m1.252s real 87m36.586s user 0m10.637s sys 0m1.208s </code></pre> <p>After research I came over <a href="https://stackoverflow.com/questions/457060/changing-tables-from-myisam-to-innodb-make-the-system-slow">Changing tables from MyISAM to InnoDB make the system slow</a> and then used <code>set global innodb_flush_log_at_trx_commit=2</code>:</p> <pre><code>$ time mysql testdb_innodb &lt; testdb_innodb.sql real 64m8.348s user 0m10.533s sys 0m1.152s </code></pre> <p>IMHO still shockingly slow. I've also disabled <code>log_bin</code> for these tests and here's a list of <a href="http://my-serve.rs/tmp/mysql-variables.txt" rel="nofollow noreferrer">all mysql variables</a>.</p> <p>Do I've to accept this long InnoDB times or can they be improved? I've full control over this MySQL server as it's purely for this test environment.</p> <p>I can apply special configurations only for initial import and change them back for applications tests so they better match production environments.</p> <p><strong>Update:</strong></p> <p>Given the feedback, I've disabled autocommit and the various checks:</p> <pre><code>$ time ( echo "SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;" \ ; cat testdb_innodb.sql ; echo "COMMIT;" ) | mysql testdb_innodb;date real 47m59.019s user 0m10.665s sys 0m2.896s </code></pre> <p>The speed improved, but not that much. Is my test flawed?</p> <p><strong>Update 2:</strong></p> <p>I was able to gain access to a different machine were imports only took about 8 minutes. I compared the configurations and applied the following settings to my MySQL installation:</p> <pre><code>innodb_additional_mem_pool_size = 20971520 innodb_buffer_pool_size = 536870912 innodb_file_per_table innodb_log_buffer_size = 8388608 join_buffer_size = 67104768 max_allowed_packet = 5241856 max_binlog_size = 1073741824 max_heap_table_size = 41943040 query_cache_limit = 10485760 query_cache_size = 157286400 read_buffer_size = 20967424 sort_buffer_size = 67108856 table_cache = 256 thread_cache_size = 128 thread_stack = 327680 tmp_table_size = 41943040 </code></pre> <p>With these settings I'm now down to about 25 minutes. Still far away from the few minutes MyISAM takes, but it's getting more usable for me.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USmark
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POInnoDB takes over an hour to import 600MB file, MyISAM in a few minutes
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POInnoDB takes over an hour to import 600MB file, MyISAM in a few minutes
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POInnoDB takes over an hour to import 600MB file, MyISAM in a few minutes
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou know what? We really should have moved this to serverfault. Voted to do that. Good luck.
      singulars
      1. POInnoDB takes over an hour to import 600MB file, MyISAM in a few minutes
      1. UST.J. Crowder
    2. CO@T.J. Sounds reasonable. Is there anything on my part I can/should do?
      singulars
      1. POInnoDB takes over an hour to import 600MB file, MyISAM in a few minutes
      1. USmark
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload