InnoDB는 600MB 파일, MyISAM을 몇 분 안에 가져 오는 데 1 시간 이상 걸립니다.

InnoDB는 600MB 파일, MyISAM을 몇 분 안에 가져 오는 데 1 시간 이상 걸립니다.

---

저는 현재 앱의 성능을 테스트하기위한 환경을 만들고 있습니다. 나는 MySQL과 InnoDB로 테스트하여 어떤 것이 우리에게 가장 잘 맞는지 알아 내고 있습니다. 이 환경에서 자동으로 데이터베이스를 준비하고 (기존 덤프로드) 테스트 도구를 계측합니다.

MySQL과 InnoDB를 사용하여 동일한 데이터 덤프를 테스트 할 준비를하고 있지만 InnoDB 부분에 대해 초기 가져 오기를 사용 가능한 속도로 가져 오지 못했습니다. 초기 덤프는 더 오래 걸렸지 만 아직 걱정하지 않았습니다.

$ for i in testdb_myisam testdb_innodb; do time mysqldump --extended-insert $i > $i.sql; done

real    0m38.152s
user    0m8.381s
sys     0m2.612s

real    1m16.665s
user    0m6.600s
sys     0m2.552s

그러나 가져 오기 시간은 상당히 달랐습니다.

$ for i in  testdb_myisam testdb_innodb; do time mysql $i < $i.sql; done

real    2m52.821s
user    0m10.505s
sys     0m1.252s

real    87m36.586s
user    0m10.637s
sys     0m1.208s

연구 후 MyISAM에서 InnoDB로 테이블을 변경하면 시스템이 느려지고 다음을 사용했습니다 set global innodb_flush_log_at_trx_commit=2.

$ time mysql testdb_innodb < testdb_innodb.sql

real    64m8.348s
user    0m10.533s
sys     0m1.152s

IMHO는 여전히 놀랍도록 느립니다. 나는 또한 log_bin이러한 테스트에 대해 비활성화 했으며 여기에 모든 mysql 변수 목록이 있습니다.

이 긴 InnoDB 시간을 수용해야합니까, 아니면 개선 할 수 있습니까? 순수하게이 테스트 환경을위한 것이기 때문에이 MySQL 서버를 완전히 제어 할 수 있습니다.

초기 가져 오기에만 특수 구성을 적용하고 프로덕션 환경과 더 잘 일치하도록 애플리케이션 테스트를 위해 다시 변경할 수 있습니다.

최신 정보:

피드백이 주어지면 자동 커밋과 다양한 검사를 비활성화했습니다.

$ 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

속도는 향상되었지만 그다지 많지는 않았습니다. 내 테스트에 결함이 있습니까?

업데이트 2 :

가져 오기에 8 분 밖에 걸리지 않았던 다른 기계에 액세스 할 수있었습니다. 구성을 비교하고 MySQL 설치에 다음 설정을 적용했습니다.

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

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.

---

Did you try the Bulk Data Loading Tips from the InnoDB Performance Tuning Tips (especially the first one):

• When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET autocommit and COMMIT statements:

  SET autocommit=0;
  ... SQL import statements ...
  COMMIT;
  

  If you use the mysqldump option --opt, you get dump files that are fast to import into an InnoDB table, even without wrapping them with the SET autocommit and COMMIT statements.

• If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

  SET unique_checks=0;
  ... SQL import statements ...
  SET unique_checks=1;
  

  For big tables, this saves a lot of disk I/O because InnoDB can use its insert buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys.

• If you have FOREIGN KEY constraints in your tables, you can speed up table imports by turning the foreign key checks off for the duration of the import session:

  SET foreign_key_checks=0;
  ... SQL import statements ...
  SET foreign_key_checks=1;
  

  For big tables, this can save a lot of disk I/O.

IMO, the whole chapter is worth the read.

---

Have you tried starting a transaction at the outset and committing it at the end? From the question you linked: "Modify the Insert Data step to start a transaction at the start and to commit it at the end. You will get an improvement, I guarantee it."

Remember that InnoDB is transactional, MyISAM is not. Transactional engines treat every statement as an individual transaction if you don't explicitly control the transaction. This can be costly.

---

I found the hard drive to be the bottleneck - old-fashioned disks are hopeless, SSD is okay-ish but still far from perfect. Importing to tmpfs and copying out the data is way faster, details: https://dba.stackexchange.com/a/89367/56667

---

I had issues doing a lot of bulk importing and recommend the accepted answer. I found you can also speed things up significantly by:

1. Dropping all indexes (other than primary key), loading the data then re-adding indexes
2. Checking your innodb_log_file_size * innodb_log_files_in_group is sufficient to avoid writing to disk in sub-second frequency

Regarding #2 the defaults of 5M * 2 will not be enough on a modern system. For details see innodb_log_file_size and innodb_log_files_in_group

참고URL : https://stackoverflow.com/questions/2167522/innodb-takes-over-an-hour-to-import-600mb-file-myisam-in-a-few-minutes