[mysql] MyISAM 대 InnoDB



Answers

저는 데이터베이스 전문가가 아니며 경험으로 말하지 않습니다. 하나:

MyISAM 테이블은 테이블 레벨 잠금을 사용한다 . 트래픽 견적을 기반으로하면 초당 200 회의 쓰기가 발생합니다. MyISAM을 사용하면 언제든지 이들 중 하나만 진행될 수 있습니다 . 하드웨어가 오버런을 피하기 위해 이러한 트랜잭션을 따라갈 수 있어야합니다. 즉, 단일 쿼리가 5ms를 넘지 않아도됩니다.

즉, 행 레벨 잠금을 지원하는 저장소 엔진, 즉 InnoDB가 필요할 것이라고 제안합니다.

반면에, 각 스토리지 엔진의로드를 시뮬레이트하기 위해 몇 가지 간단한 스크립트를 작성한 다음 결과를 비교하는 것은 상당히 사소한 일입니다.

Question

나는 많은 데이터베이스 쓰기를 포함하는 프로젝트에서 작업하고 있는데, ( 70 % 삽입과 30 % 읽기 )라고 말하고 싶습니다. 이 비율에는 내가 읽은 것과 읽은 것으로 간주되는 업데이트도 포함됩니다. 읽기가 더러울 수 있습니다 (예 : 읽을 때 100 % 정확한 정보가 필요하지 않음).
문제의 작업은 1 시간에 1 백만 건이 넘는 데이터베이스 트랜잭션을 수행합니다.

MyISAM과 InnoDB의 차이점에 대해 웹에서 많은 것을 읽었습니다. MyISAM은이 작업을 위해 사용할 특정 데이터베이스 / 테이블에 대한 확실한 선택입니다. InnoDB는 행 레벨 잠금이 지원되기 때문에 트랜잭션이 필요하다면 좋을 것입니다.

아무도 이러한 유형의 부하 (또는 그 이상)에 대한 경험이 있습니까? MyISAM은 갈 길입니까?




In short, InnoDB is good if you are working on something that needs a reliable database that can handles a lot of INSERT and UPDATE instructions.

and, MyISAM is good if you needs a database that will mostly be taking a lot of read (SELECT) instructions rather than write (INSERT and UPDATES), considering its drawback on the table-lock thing.

you may want to check out;
Pros and Cons of InnoDB
Pros and Cons of MyISAM




나는 MySQL을 사용하는 대용량 시스템에서 작업했으며 MyISAM과 InnoDB를 모두 시도했다.

MyISAM의 테이블 수준 잠금으로 인해 귀하의 작업 부하와 비슷한 수준의 심각한 성능 문제가 있음을 발견했습니다. 불행히도 InnoDB에서의 성능 또한 내가 기대했던 것보다 나빴음을 발견했습니다.

결국 나는 삽입이 "핫"테이블에 들어가고 절대로 핫 테이블을 쿼리하지 않도록 데이터를 조각화하여 경합 문제를 해결했습니다.

이것은 또한 선택 쿼리에 의해 다시 접촉되지 않은 "부실"테이블에서 삭제 (데이터는 시간에 민감했으며 우리는 X 일만 유지했습니다)가 발생하도록 허용했습니다. InnoDB는 벌크 삭제시 성능이 좋지 않은 것처럼 보입니다. 따라서 데이터 삭제를 계획하는 경우 이전 데이터가 삭제 된 테이블 대신 삭제 될 수있는 오래된 테이블에 구조화하는 것이 좋습니다.

당연히 나는 당신의 애플리케이션이 무엇인지 전혀 모르겠다. 그러나 이것이 MyISAM과 InnoDB의 문제점들에 대해 약간의 통찰력을 제공하기를 희망한다.




I tried to run insertion of random data into MyISAM and InnoDB tables. The result was quite shocking. MyISAM needed a few seconds less for inserting 1 million rows than InnoDB for just 10 thousand!




I know this won't be popular but here goes:

myISAM lacks support for database essentials like transactions and referential integrity which often results in glitchy / buggy applications. You cannot not learn proper database design fundamentals if they are not even supported by your db engine.

Not using referential integrity or transactions in the database world is like not using object oriented programming in the software world.

InnoDB exists now, use that instead! Even MySQL developers have finally conceded to change this to the default engine in newer versions, despite myISAM being the original engine that was the default in all legacy systems.

No it does not matter if you are reading or writing or what performance considerations you have, using myISAM can result in a variety of problems, such as this one I just ran into: I was performing a database sync and at the same time someone else accessed an application that accessed a table set to myISAM. Due to the lack of transaction support and the generally poor reliability of this engine, this crashed the entire database and I had to manually restart mysql!

Over the past 15 years of development I have used many databases and engines. myISAM crashed on me about a dozen times during this period, other databases, only once! And that was a microsoft SQL database where some developer wrote faulty CLR code (common language runtime - basically C# code that executes inside the database) by the way, it was not the database engine's fault exactly.

I agree with the other answers here that say that quality high-availability, high-performance applications should not use myISAM as it will not work, it is not robust or stable enough to result in a frustration-free experience. See Bill Karwin's answer for more details.

PS Gotta love it when myISAM fanboys downvote but can't tell you which part of this answer is incorrect.




If you use MyISAM, you won't be doing any transactions per hour, unless you consider each DML statement to be a transaction (which in any case, won't be durable or atomic in the event of a crash).

Therefore I think you have to use InnoDB.

300 transactions per second sounds like quite a lot. If you absolutely need these transactions to be durable across power failure make sure your I/O subsystem can handle this many writes per second easily. You will need at least a RAID controller with battery backed cache.

If you can take a small durability hit, you could use InnoDB with innodb_flush_log_at_trx_commit set to 0 or 2 (see docs for details), you can improve performance.

There are a number of patches which can increase concurrency from Google and others - these may be of interest if you still can't get enough performance without them.




주제를 조금 벗어나지 만, 문서 작성과 완성을 위해 다음을 추가하고 싶습니다.

일반적으로 InnoDB를 사용하면 복잡하지 않은 응용 프로그램이 훨씬 더 복잡해지며 버그가 발생하지 않을 것입니다. 모든 참조 무결성 (외래 키 - 제약)을 데이터 모델에 넣을 수 있기 때문에 MyISAM에서 필요한만큼의 애플리케이션 코드가 필요하지 않습니다.

레코드를 삽입, 삭제 또는 교체 할 때마다 관계를 확인하고 유지해야합니다. 예를 들어 부모를 삭제하면 모든 어린이도 삭제해야합니다. 예를 들어, 단순한 블로깅 시스템이라 할지라도 블로그 게시물 레코드를 지우면 주석 레코드, 좋아하는 것을 지워야 할 것입니다. InnoDB에서 이것은 데이터베이스 엔진에 의해 자동으로 수행됩니다 (모델에서 contraint를 지정한 경우). ) 응용 프로그램 코드가 필요 없습니다. MyISAM에서는 웹 서버에서 매우 어려운 애플리케이션에 코딩해야합니다. 웹 서버는 본질적으로 매우 동시 적 / 병렬 적이며, 이러한 동작은 원자 적이어야하고 MyISAM은 실제 트랜잭션을 지원하지 않으므로 웹 서버에 MyISAM을 사용하는 것은 위험하거나 오류를 발생시키기 쉽습니다.

또한 대부분의 일반적인 경우, InnoDB는 여러 가지 이유로 테이블 수준 잠금과 달리 레코드 수준 잠금을 사용할 수있는 여러 가지 이유로 더 나은 성능을 발휘합니다. 쓰기가 읽기보다 빈번한 상황뿐만 아니라 대규모 데이터 세트에서 복잡한 조인이있는 상황에서도 마찬가지입니다. 우리는 아주 큰 조인 (몇 분이 걸림)을 위해 MyISAM 테이블에서 InnoDB 테이블을 사용하는 것만으로도 3 배의 성능 향상을 보았다.

필자는 MySQL을 사용할 때 일반적으로 InnoDB (참조 무결성을 갖춘 3NF 데이터 모델 사용)가 기본 선택 항목이어야한다고 말합니다. MyISAM은 매우 특정한 경우에만 사용해야합니다. 가장 가능성이 적어서 더 큰 버그가있는 응용 프로그램이 만들어집니다.

이것을 말한 것. 데이터 모델링은 웹 디자이너 / 프로그래머간에 거의 찾아 볼 수없는 예술입니다. 불쾌감은 없지만, MyISAM이 너무 많이 사용된다고 설명합니다.




MyISAM

The MyISAM engine is the default engine in most MySQL installations and is a derivative of the original ISAM engine type supported in the early versions of the MySQL system. The engine provides the best combination of performance and functionality, although it lacks transaction capabilities (use the InnoDB or BDB engines) and uses table-level locking .

FlashMAX and FlashMAX Connect: Leading the Flash Platform Transformation Download Now Unless you need transactions, there are few databases and applications that cannot effectively be stored using the MyISAM engine. However, very high-performance applications where there are large numbers of data inserts/updates compared to the number of reads can cause performance proboelsm for the MyISAM engine. It was originally designed with the idea that more than 90% of the database access to a MyISAM table would be reads, rather than writes.

With table-level locking, a database with a high number of row inserts or updates becomes a performance bottleneck as the table is locked while data is added. Luckily this limitation also works well within the restrictions of a non-transaction database.

MyISAM Summary

Name -MyISAM

Introduced -v3.23

Default install -Yes

Data limitations -None

Index limitations -64 indexes per table (32 pre 4.1.2); Max 16 columns per index

Transaction support -No

Locking level -Table

InnoDB

The InnoDB Engine is provided by Innobase Oy and supports all of the database functionality (and more) of MyISAM engine and also adds full transaction capabilities (with full ACID (Atomicity, Consistency, Isolation, and Durability) compliance) and row level locking of data.

The key to the InnoDB system is a database, caching and indexing structure where both indexes and data are cached in memory as well as being stored on disk. This enables very fast recovery, and works even on very large data sets. By supporting row level locking, you can add data to an InnoDB table without the engine locking the table with each insert and this speeds up both the recovery and storage of information in the database.

As with MyISAM , there are few data types that cannot effectively be stored in an InnoDB database. In fact, there are no significant reasons why you shouldn't always use an InnoDB database. The management overhead for InnoDB is slightly more onerous, and getting the optimization right for the sizes of in-memory and on disk caches and database files can be complex at first. However, it also means that you get more flexibility over these values and once set, the performance benefits can easily outweigh the initial time spent. Alternatively, you can let MySQL manage this automatically for you.

If you are willing (and able) to configure the InnoDB settings for your server, then I would recommend that you spend the time to optimize your server configuration and then use the InnoDB engine as the default.

InnoDB Summary

Name -InnoDB

Introduced -v3.23 (source only), v4.0 (source and binary)

Default install -No

Data limitations -None

Index limitations -None

Transaction support -Yes (ACID compliant)

Locking level -Row




MYISAM:

  1. MYISAM supports Table-level Locking

  2. MyISAM designed for need of speed

  3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
  4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
  5. MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it's done.

INNODB:

  1. InnoDB supports Row-level Locking
  2. InnoDB designed for maximum performance when processing high volume of data
  3. InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
  4. InnoDB stores its tables and indexes in a tablespace
  5. InnoDB supports transaction. You can commit and rollback with InnoDB



Also check out some drop-in replacements for MySQL itself:

MariaDB

http://mariadb.org/

MariaDB is a database server that offers drop-in replacement functionality for MySQL. MariaDB is built by some of the original authors of MySQL, with assistance from the broader community of Free and open source software developers. In addition to the core functionality of MySQL, MariaDB offers a rich set of feature enhancements including alternate storage engines, server optimizations, and patches.

Percona Server

https://launchpad.net/percona-server

An enhanced drop-in replacement for MySQL, with better performance, improved diagnostics, and added features.




I've figure out that even though Myisam has locking contention, it's still faster than InnoDb in most scenarios because of the rapid lock acquisition scheme it uses. I've tried several times Innodb and always fall back to MyIsam for one reason or the other. Also InnoDB can be very CPU intensive in huge write loads.




조금 늦었습니다 ...하지만 몇 달 전에 썼던 상당히 포괄적 인 입니다. MYISAM과 InnoDB의 주요 차이점을 자세히 설명합니다. 잡동사니 (및 어쩌면 비스킷)를 잡고 즐기십시오.

MyISAM과 InnoDB의 주요 차이점은 참조 무결성과 트랜잭션이다. 잠금, 롤백 및 전체 텍스트 검색과 같은 다른 차이점도 있습니다.

참조 무결성

참조 무결성은 테이블 간의 관계가 일관되게 유지되도록합니다. 즉, 테이블 (예 : Listings)이 다른 테이블 (예 : 제품)을 가리키는 외래 키 (예 : 제품 ID)를 가지고 있고, 포인팅 대상 테이블로 업데이트 또는 삭제가 발생하면 이러한 변경 사항이 연결에 계단식 연결됩니다 표. 이 예에서 제품의 이름이 바뀌면 연결 테이블의 외래 키도 업데이트됩니다. 제품이 '제품'테이블에서 삭제되면 삭제 된 항목을 가리키는 목록도 삭제됩니다. 또한 새로운 리스팅의 경우 유효한 외부 입력을 가리키는 외래 키가 있어야합니다.

InnoDB는 관계형 DBMS (RDBMS)이므로 MyISAM은 참조 무결성을 유지합니다.

거래 및 원자력

테이블의 데이터는 SELECT, INSERT, UPDATE 및 DELETE와 같은 DML (Data Manipulation Language) 문을 사용하여 관리됩니다. 트랜잭션은 두 개 이상의 DML 문을 하나의 작업 단위로 그룹화하여 전체 단위가 적용되거나 전체 단위가 적용되지 않습니다.

MyISAM은 InnoDB와는 달리 트랜잭션을 지원하지 않습니다.

MyISAM 테이블을 사용하는 동안 작업이 중단되면 작업이 즉시 중단되고 작업이 완료되지 않아도 영향을받는 행 (또는 각 행의 데이터까지)이 영향을받습니다.

원 인성을 가지는 트랜잭션 (transaction)를 사용하고 있기 (위해) 때문에, InnoDB 테이블을 사용하고있는 동안에 조작이 중단되었을 경우, 커밋이 행해지 지 않기 때문에 처리를 완료하지 않은 트랜잭션은 유효하지 않습니다.

테이블 잠금과 행 잠금

MyISAM 테이블에 대해 쿼리가 실행되면, 쿼리하는 전체 테이블이 잠길 것이다. 즉, 후속 쿼리는 현재 쿼리가 완료된 후에 만 ​​실행됩니다. 큰 테이블을 읽거나 빈번한 읽기 및 쓰기 작업이있는 경우 이는 거대한 백 로그의 쿼리를 의미 할 수 있습니다.

InnoDB 테이블에 대해 질의가 실행되면 관련된 행만 잠기고 나머지 테이블은 CRUD 작업에 계속 사용할 수 있습니다. 즉, 같은 행을 사용하지 않는다면 같은 테이블에서 동시에 쿼리를 실행할 수 있습니다.

InnoDB의이 기능은 동시성이라고합니다. 병행 성만큼이나 선택 범위의 테이블에 적용되는 커다란 단점이 있습니다. 커널 스레드 간 전환에 오버 헤드가 있으며 서버가 멈추지 않도록 커널 스레드에 제한을 설정해야합니다 .

거래 및 롤백

MyISAM에서 작업을 실행하면 변경 사항이 설정됩니다. InnoDB에서는 이러한 변경 사항을 롤백 할 수 있습니다. 트랜잭션을 제어하는 ​​데 사용되는 가장 일반적인 명령은 COMMIT, ROLLBACK 및 SAVEPOINT입니다. 1. COMMIT - 여러 개의 DML 작업을 작성할 수 있지만 COMMIT가 수행 될 때만 변경 사항이 저장됩니다. 2. ROLLBACK - 아직 커밋되지 않은 작업은 삭제할 수 있습니다. 3. SAVEPOINT - ROLLBACK 조작이 롤백 할 수있는 조작

신뢰할 수 있음

MyISAM은 데이터 무결성을 제공하지 않습니다. 하드웨어 오류, 부정한 종료 및 취소 된 작업으로 인해 데이터가 손상 될 수 있습니다. 이렇게하려면 인덱스와 테이블을 완전히 복구하거나 다시 작성해야합니다.

한편, InnoDB는 트랜잭션 로그, 이중 쓰기 버퍼 및 자동 체크섬 및 유효성 검사를 사용하여 손상을 방지합니다. InnoDB가 변경을 가하기 전에, 트랜잭션 전에 데이터를 ibdata1이라는 시스템 테이블 공간 파일에 기록합니다. 충돌이 발생하면 InnoDB는 로그를 재생하여 자동 복구합니다.

FULLTEXT 인덱싱

InnoDB는 MySQL 버전 5.6.4까지 FULLTEXT 인덱싱을 지원하지 않습니다. 이 글을 쓰는 시점에서 많은 공유 호스팅 제공 업체의 MySQL 버전은 여전히 ​​5.6.4 미만이며, 이는 FULLTEXT 색인 생성이 InnoDB 테이블에서 지원되지 않음을 의미합니다.

그러나 이것이 MyISAM을 사용하는 타당한 이유는 아닙니다. MySQL의 최신 버전을 지원하는 호스팅 제공 업체로 변경하는 것이 가장 좋습니다. FULLTEXT 인덱싱을 사용하는 MyISAM 테이블은 InnoDB 테이블로 변환 될 수 없다.

결론

결론적으로, InnoDB는 기본 저장소 엔진이되어야한다. MyISAM 또는 다른 데이터 유형이 특정 요구에 부합 할 때 선택하십시오.




Almost every time I start a new project I Google this same question to see if I come up with any new answers.

It eventually boils down to - I take the latest version of MySQL and run tests.

I have tables where I want to do key/value lookups... and that's all. I need to get the value (0-512 bytes) for a hash key. There is not a lot of transactions on this DB. The table gets updates occasionally (in it's entirety), but 0 transactions.

So we're not talking about a complex system here, we are talking about a simple lookup,.. and how (other than making the table RAM resident) we can optimize performance.

I also do tests on other databases (ie NoSQL) to see if there is anywhere I can get an advantage. The biggest advantage I have found is in key mapping but as far as the lookup goes, MyISAM is currently topping them all.

Albeit, I wouldn't perform financial transactions with MyISAM tables but for simple lookups, you should test it out.. typically 2x to 5x the queries/sec.

Test it, I welcome debate.




Related