Consider this scenario with the following assumptions:
考虑这种情况,假设如下:
- The database is used for a non-critical webapp.
- Query speed is of vital importance.
- The read/write patterns are roughly >95 % reads and <5 % writes.
- The database is backuped up daily using mysqldump.
- The is no need for transactions or advanced crash recovery. If the database crashes I'll simply import last night's mysqldump. This is good enough in this scenario.
- No need for full-text searching.
该数据库用于非关键Web应用程序。
查询速度至关重要。
读/写模式大约> 95%读取和<5%写入。
每天使用mysqldump备份数据库。
不需要事务或高级崩溃恢复。如果数据库崩溃,我只需导入昨晚的mysqldump。在这种情况下,这已经足够了。
无需全文搜索。
Pros of MyISAM under said assumptions:
在所述假设下MyISAM的优点:
- It's very fast (with one exception - see below).
- It's light-weight and has an easy-to-understand mapping between database/table to physical files in file system (.MYD/.MYI/.frm).
- Easy backup (mysqldump).
它非常快(有一个例外 - 见下文)。
它重量轻,并且在数据库/表与文件系统中的物理文件(.MYD / .MYI / .frm)之间具有易于理解的映射。
轻松备份(mysqldump)。
All-in-all I'm very happy with MyISAM with one major exception. MyISAM has one major shortcoming under said assumptions and that is table level locking. When UPDATEs are running towards a frequently read table all reads are blocked. Needless to say this causes major performance problems which must be solved.
总而言之,我对MyISAM非常满意,只有一个例外。在所述假设下,MyISAM有一个主要缺点,那就是表级锁定。当UPDATE向经常读取的表运行时,所有读取都被阻止。不用说,这会导致必须解决的主要性能问题。
My questions are:
我的问题是:
- Is there some way to get rid of table level locking without switching away from MyISAM?
- If I must switch to InnoDB - how do I configure InnoDB so that it behaves as similar to MyISAM as possible (think no transactions, logical file structure, etc.). How do I configure a InnoDB to be "just like MyISAM but without table level locking"?
有没有办法在不切换MyISAM的情况下摆脱表级锁定?
如果我必须切换到InnoDB - 我如何配置InnoDB,使其行为尽可能与MyISAM类似(不要考虑事务,逻辑文件结构等)。如何将InnoDB配置为“就像MyISAM但没有表级锁定”?
4 个解决方案
#1
1
- No, MyISAM means table level locking.
- You can't get it "just like", but you can get it "a lot more like" by turning on the
innodb_file_per_tableoption. InnoDB will still store critical information in its system-level data file, however, and you can't do things like casually rename a database by renaming the directory it lives in like you can with MyISAM.
不,MyISAM意味着表级锁定。
你不能“只是喜欢”它,但你可以通过打开innodb_file_per_table选项让它“更像”。然而,InnoDB仍会将关键信息存储在其系统级数据文件中,并且您无法通过重命名数据库来重命名数据库,就像使用MyISAM一样。
#2
0
Have you actually taken performance metrics using myisam and innodb tables? In my experience the differences in speed is not really that much when you consider all the ACID benefits you get from innodb. Just the table locking alone will affect speed such that innodb would be overall faster.
您是否真的使用myisam和innodb表获取了性能指标?根据我的经验,当你考虑从innodb获得的所有ACID好处时,速度的差异并不是那么大。仅仅锁定表将影响速度,使得innodb总体上更快。
Also notice that myisam is much faster on inserts, not so much on selects. You are inserting only 5% of the time... do the math.
另请注意,myisam在插入时要快得多,而不是选择。你只插入5%的时间...做数学。
You can always do mysqldump using an innodb, so your backup rocess is the same.
你总是可以使用innodb做mysqldump,所以你的备份过程是一样的。
#3
0
I know some projects use a mirror DB for searching. It tends to be optimized for the searches and sometimes even run on a different machine, just to isolate the overhead.
我知道有些项目使用镜像DB进行搜索。它往往针对搜索进行优化,有时甚至可以在不同的机器上运行,只是为了隔离开销。
The only drawback here is that keeping them in sync is a bit of a hassle. If stale data in your search table isn't too troubling, it might be the best bet. If performance is an issue that is.
这里唯一的缺点是让它们保持同步有点麻烦。如果搜索表中的陈旧数据不是太麻烦,那么它可能是最好的选择。如果性能是一个问题。
It isn't my favorite solution, but it is pretty simple in theory.
这不是我最喜欢的解决方案,但理论上它非常简单。
#4
0
- When it comes to backup InnoDB doesn't prevent you from using mysqldump.
- Are you sure that you really need to maintain the mapping between database tables and files on disk? Manual operations on database files are rarely a good idea.
- With InnoDB you don't have to use transactions, by default it works in "autocommit" mode (every query will be commited automatically).
- "InnoDB is slower" is mostly myth these days, but of course it depends on your workload.
说到备份InnoDB并不妨碍你使用mysqldump。
您确定您确实需要维护磁盘上数据库表和文件之间的映射吗?对数据库文件的手动操作很少是个好主意。
使用InnoDB,您不必使用事务,默认情况下它以“自动提交”模式工作(每个查询将自动提交)。
“InnoDB速度较慢”现在主要是神话,但当然这取决于你的工作量。
In other words I think you should definitely give InnoDB a try and benchmark the performance of your application. Migration is extremely simple, so I don't see a reason not to try. For me InnoDB is a default choice for a long time.
换句话说,我认为你一定要试试InnoDB并对应用程序的性能进行基准测试。迁移非常简单,所以我没有理由不去尝试。对我来说,InnoDB很长一段时间都是默认选择。
#1
1
- No, MyISAM means table level locking.
- You can't get it "just like", but you can get it "a lot more like" by turning on the
innodb_file_per_tableoption. InnoDB will still store critical information in its system-level data file, however, and you can't do things like casually rename a database by renaming the directory it lives in like you can with MyISAM.
不,MyISAM意味着表级锁定。
你不能“只是喜欢”它,但你可以通过打开innodb_file_per_table选项让它“更像”。然而,InnoDB仍会将关键信息存储在其系统级数据文件中,并且您无法通过重命名数据库来重命名数据库,就像使用MyISAM一样。
#2
0
Have you actually taken performance metrics using myisam and innodb tables? In my experience the differences in speed is not really that much when you consider all the ACID benefits you get from innodb. Just the table locking alone will affect speed such that innodb would be overall faster.
您是否真的使用myisam和innodb表获取了性能指标?根据我的经验,当你考虑从innodb获得的所有ACID好处时,速度的差异并不是那么大。仅仅锁定表将影响速度,使得innodb总体上更快。
Also notice that myisam is much faster on inserts, not so much on selects. You are inserting only 5% of the time... do the math.
另请注意,myisam在插入时要快得多,而不是选择。你只插入5%的时间...做数学。
You can always do mysqldump using an innodb, so your backup rocess is the same.
你总是可以使用innodb做mysqldump,所以你的备份过程是一样的。
#3
0
I know some projects use a mirror DB for searching. It tends to be optimized for the searches and sometimes even run on a different machine, just to isolate the overhead.
我知道有些项目使用镜像DB进行搜索。它往往针对搜索进行优化,有时甚至可以在不同的机器上运行,只是为了隔离开销。
The only drawback here is that keeping them in sync is a bit of a hassle. If stale data in your search table isn't too troubling, it might be the best bet. If performance is an issue that is.
这里唯一的缺点是让它们保持同步有点麻烦。如果搜索表中的陈旧数据不是太麻烦,那么它可能是最好的选择。如果性能是一个问题。
It isn't my favorite solution, but it is pretty simple in theory.
这不是我最喜欢的解决方案,但理论上它非常简单。
#4
0
- When it comes to backup InnoDB doesn't prevent you from using mysqldump.
- Are you sure that you really need to maintain the mapping between database tables and files on disk? Manual operations on database files are rarely a good idea.
- With InnoDB you don't have to use transactions, by default it works in "autocommit" mode (every query will be commited automatically).
- "InnoDB is slower" is mostly myth these days, but of course it depends on your workload.
说到备份InnoDB并不妨碍你使用mysqldump。
您确定您确实需要维护磁盘上数据库表和文件之间的映射吗?对数据库文件的手动操作很少是个好主意。
使用InnoDB,您不必使用事务,默认情况下它以“自动提交”模式工作(每个查询将自动提交)。
“InnoDB速度较慢”现在主要是神话,但当然这取决于你的工作量。
In other words I think you should definitely give InnoDB a try and benchmark the performance of your application. Migration is extremely simple, so I don't see a reason not to try. For me InnoDB is a default choice for a long time.
换句话说,我认为你一定要试试InnoDB并对应用程序的性能进行基准测试。迁移非常简单,所以我没有理由不去尝试。对我来说,InnoDB很长一段时间都是默认选择。