I was wondering if anyone has a suggestion for what kind of storage engine to use. The programs needs to perform a lot of writes to database but very few reads.
我想知道是否有人建议使用什么样的存储引擎。程序需要对数据库执行大量写操作,但读取次数很少。
[edit] No foreign keys necessary. The data is simple, but it needs to preform the writes very fast.
[编辑]没有必要的外键。数据很简单,但需要非常快速地执行写入。
4 个解决方案
#1
From jpipes:
MyISAM and Table-Level Locks
MyISAM和表级锁
Unlike InnoDB, which employs row-level locking, MyISAM uses a much coarser-grained locking system to ensure that data is written to the data file in a protected manner. Table-level locking is the only level of lock for MyISAM, and this has a couple consequences:
与使用行级锁定的InnoDB不同,MyISAM使用更粗粒度的锁定系统来确保以受保护的方式将数据写入数据文件。表级锁定是MyISAM唯一的锁定级别,这会产生一些后果:
- Any connection issuing an UPDATE or DELETE against a MyISAM table will request an exclusive write lock on the MyISAM table. If no other locks (read or write) are currently placed on the table, the exclusive write lock is granted and all other connections issuing requests of any kind (DDL, SELECT, UPDATE, INSERT, DELETE) must wait until the thread with the exclusive write lock updates the record(s) it needs to and then releases the write lock.
对MyISAM表发出UPDATE或DELETE的任何连接都将在MyISAM表上请求独占写锁定。如果当前没有其他锁(读或写)放在表上,则授予独占写锁,并且发出任何类型的请求(DDL,SELECT,UPDATE,INSERT,DELETE)的所有其他连接必须等到具有独占的线程write lock更新它需要的记录,然后释放写锁。
- Since there is only table-level locks, there is no ability (like there is with InnoDB) to only lock one or a small set of records, allowing other threads to SELECT from other parts of the table data.
由于只有表级锁,因此没有能力(就像InnoDB一样)只能锁定一个或一小组记录,允许其他线程从表数据的其他部分进行SELECT。
The point is, for writing, InnoDB is better as it will lock less of the resource and enable more parallel actions/requests to occur.
关键是,对于写入,InnoDB更好,因为它将锁定更少的资源并使更多的并行操作/请求发生。
#2
"It needs to perform the writes very fast" is a vague requirement. Whatever you do, writes may be delayed by contention in the database. If your application needs to not block when it's writing audit records to the database, you should make the audit writing asynchronous and keep your own queue of audit data on disc or in memory (so you don't block the main worker thread/process)
“它需要非常快速地执行写入”是一个模糊的要求。无论你做什么,写入都可能因数据库中的争用而延迟。如果您的应用程序在将审计记录写入数据库时不需要阻止,您应该使审计写入异步并将您自己的审计数据队列保存在光盘或内存中(这样您就不会阻止主工作线程/进程)
InnoDB may allow concurrent inserts, but that doesn't mean they won't be blocked by contention for resources or internal locks for things like index pages.
InnoDB可能允许并发插入,但这并不意味着它们不会被索引页面之类的资源争用或内部锁定所阻止。
MyISAM allows one inserter and several readers ("Concurrent inserts") under the following circumstances:
在以下情况下,MyISAM允许一个插入器和多个读取器(“并发插入”):
- The table has no "holes in it"
- There are no threads trying to do an UPDATE or DELETE
桌上没有“漏洞”
没有线程尝试执行UPDATE或DELETE
If you have an append-only table, which you recreate each day (or create a new partition every day if you use 5.1 partitioning), you may get away with this.
如果您有一个仅附加表,您每天都会重新创建(或者如果您使用5.1分区,则每天创建一个新分区),您可以使用它。
MyISAM concurrent inserts are mostly very good, IF you can use them.
MyISAM并发插入大多非常好,如果你可以使用它们。
When writing audit records, do several at a time if possible - this applies whichever storage engine you use. It is a good idea for the audit process to "batch up" records and do an insert of several at once.
编写审计记录时,如果可能,一次执行几个 - 这适用于您使用的任何存储引擎。审计过程最好“批量”记录并一次插入几个记录。
#3
You've not really given us enough information to make a considered suggestion - are you wanting to use foreign keys? Row-level locking? Page-level locking? Transactions?
你没有给我们足够的信息来提出一个考虑的建议 - 你想使用外键吗?行级锁定?页面锁定?交易?
As a general rule, if you want to use transactions, InnoDB/BerkeleyDB. If you don't, MyISAM.
作为一般规则,如果要使用事务,InnoDB / BerkeleyDB。如果你不这样做,MyISAM。
#4
In my experience, MyISAM is great for fast writes as long as, after insertion, it's read-only. It'll keep happily appending faster than any other option I'm familiar with (including supporting indexes).
根据我的经验,MyISAM非常适合快速写入,只要在插入后,它就是只读的。它会比我熟悉的任何其他选项(包括支持索引)更快地追加。
But as soon as you start deleting records or updating index keys, and it needs to refill emptied holes (in tables or indexes) the discussion gets a lot more complicated.
但是,只要您开始删除记录或更新索引键,并且需要重新填充空洞(在表或索引中),讨论就会变得复杂得多。
For classic log-type or journal-type tables, though, it's very happy.
但是,对于经典的日志型或日记型表,它非常高兴。
#1
From jpipes:
MyISAM and Table-Level Locks
MyISAM和表级锁
Unlike InnoDB, which employs row-level locking, MyISAM uses a much coarser-grained locking system to ensure that data is written to the data file in a protected manner. Table-level locking is the only level of lock for MyISAM, and this has a couple consequences:
与使用行级锁定的InnoDB不同,MyISAM使用更粗粒度的锁定系统来确保以受保护的方式将数据写入数据文件。表级锁定是MyISAM唯一的锁定级别,这会产生一些后果:
- Any connection issuing an UPDATE or DELETE against a MyISAM table will request an exclusive write lock on the MyISAM table. If no other locks (read or write) are currently placed on the table, the exclusive write lock is granted and all other connections issuing requests of any kind (DDL, SELECT, UPDATE, INSERT, DELETE) must wait until the thread with the exclusive write lock updates the record(s) it needs to and then releases the write lock.
对MyISAM表发出UPDATE或DELETE的任何连接都将在MyISAM表上请求独占写锁定。如果当前没有其他锁(读或写)放在表上,则授予独占写锁,并且发出任何类型的请求(DDL,SELECT,UPDATE,INSERT,DELETE)的所有其他连接必须等到具有独占的线程write lock更新它需要的记录,然后释放写锁。
- Since there is only table-level locks, there is no ability (like there is with InnoDB) to only lock one or a small set of records, allowing other threads to SELECT from other parts of the table data.
由于只有表级锁,因此没有能力(就像InnoDB一样)只能锁定一个或一小组记录,允许其他线程从表数据的其他部分进行SELECT。
The point is, for writing, InnoDB is better as it will lock less of the resource and enable more parallel actions/requests to occur.
关键是,对于写入,InnoDB更好,因为它将锁定更少的资源并使更多的并行操作/请求发生。
#2
"It needs to perform the writes very fast" is a vague requirement. Whatever you do, writes may be delayed by contention in the database. If your application needs to not block when it's writing audit records to the database, you should make the audit writing asynchronous and keep your own queue of audit data on disc or in memory (so you don't block the main worker thread/process)
“它需要非常快速地执行写入”是一个模糊的要求。无论你做什么,写入都可能因数据库中的争用而延迟。如果您的应用程序在将审计记录写入数据库时不需要阻止,您应该使审计写入异步并将您自己的审计数据队列保存在光盘或内存中(这样您就不会阻止主工作线程/进程)
InnoDB may allow concurrent inserts, but that doesn't mean they won't be blocked by contention for resources or internal locks for things like index pages.
InnoDB可能允许并发插入,但这并不意味着它们不会被索引页面之类的资源争用或内部锁定所阻止。
MyISAM allows one inserter and several readers ("Concurrent inserts") under the following circumstances:
在以下情况下,MyISAM允许一个插入器和多个读取器(“并发插入”):
- The table has no "holes in it"
- There are no threads trying to do an UPDATE or DELETE
桌上没有“漏洞”
没有线程尝试执行UPDATE或DELETE
If you have an append-only table, which you recreate each day (or create a new partition every day if you use 5.1 partitioning), you may get away with this.
如果您有一个仅附加表,您每天都会重新创建(或者如果您使用5.1分区,则每天创建一个新分区),您可以使用它。
MyISAM concurrent inserts are mostly very good, IF you can use them.
MyISAM并发插入大多非常好,如果你可以使用它们。
When writing audit records, do several at a time if possible - this applies whichever storage engine you use. It is a good idea for the audit process to "batch up" records and do an insert of several at once.
编写审计记录时,如果可能,一次执行几个 - 这适用于您使用的任何存储引擎。审计过程最好“批量”记录并一次插入几个记录。
#3
You've not really given us enough information to make a considered suggestion - are you wanting to use foreign keys? Row-level locking? Page-level locking? Transactions?
你没有给我们足够的信息来提出一个考虑的建议 - 你想使用外键吗?行级锁定?页面锁定?交易?
As a general rule, if you want to use transactions, InnoDB/BerkeleyDB. If you don't, MyISAM.
作为一般规则,如果要使用事务,InnoDB / BerkeleyDB。如果你不这样做,MyISAM。
#4
In my experience, MyISAM is great for fast writes as long as, after insertion, it's read-only. It'll keep happily appending faster than any other option I'm familiar with (including supporting indexes).
根据我的经验,MyISAM非常适合快速写入,只要在插入后,它就是只读的。它会比我熟悉的任何其他选项(包括支持索引)更快地追加。
But as soon as you start deleting records or updating index keys, and it needs to refill emptied holes (in tables or indexes) the discussion gets a lot more complicated.
但是,只要您开始删除记录或更新索引键,并且需要重新填充空洞(在表或索引中),讨论就会变得复杂得多。
For classic log-type or journal-type tables, though, it's very happy.
但是,对于经典的日志型或日记型表,它非常高兴。