什么时候需要对MySQL INNODB进行微调? [英] At what point does MySQL INNODB fine tuning become a requirement?
问题描述
我看了这个:
http://www.mysqlperformanceblog .com/2009/01/12/should-you-move-from-myisam-to-innodb/
和:
http://www.mysqlperformanceblog.com/2007/11 /01/innodb-performance-optimization-basics/
I had a look at this:
http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-myisam-to-innodb/
and:
http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
这些回答了我有关INNODB和MyISAM的许多问题.毫无疑问，INNODB是我应该走的路.但是，我自己工作，并且为了开发我创建了LAMP(ubuntu 10.10 x64)VM服务器.目前，该服务器具有2 GB内存和一个SATA 20GB驱动器.我可以毫不费力地将这两个数量增加到大约3-3.5 GB内存和200GB驱动器.
These answer a lot of my questions regarding INNODB vs MyISAM. There is no doubt in my mind that INNODB is the way I should go. However, I am working on my own and for development I have created a LAMP (ubuntu 10.10 x64) VM server. At present the server has 2 GB memory and a single SATA 20GB drive. I can increase both of these amounts without too much trouble to about 3-3.5 GB memory and a 200GB drive.
我犹豫要改用INNODB的原因是:
A)以上文章提到INNODB将大大增加表的大小，他建议使用更大数量的RAM和驱动器空间.虽然在生产环境中我不介意这种增长，但是在开发环境中，我担心我无法容纳.
B)我真的看不到在虚拟机上微调INNODB引擎的任何意义.在我的生产环境中，这甚至可能是我不允许做的事情.这些文章听起来好像INNODB注定要在不进行微调的情况下失败.
The reasons I hesitate to switch over to INNODB is:
A) The above articles mention that INNODB will vastly increase the size of the tables, and he recommends much larger amounts of RAM and drive space. While in a production environment I don't mind this increase, in a development environment, I fear I can not accommodate.
B) I don't really see any point in fine tuning the INNODB engine on my VM. This is likely something I will not even be allowed to do in my production environment. The articles make it sound like INNODB is doomed to fail without fine tuning.
我的问题是这个. INNODB在什么时候可行?我需要在服务器上运行INNODB多少内存(仅用于测试的数据.该服务器仅对我开放)?而且我可以肯定地认为，不允许我微调数据库的生产环境本身已经可以对其进行微调了吗?
My question is this. At what point is INNODB viable? How much RAM would I need to run INNODB on my server (with just my data for testing. This server is not open to anyone but me)? and also is it safe for me to assume that a production environment that will not allow me to fine tune the DB has likely already fine tuned it themselves?
此外，我是否对事情太想太多/太担心了?
Also, am I overthinking/overworrying about things?
推荐答案
恕我直言，当您有成千上万的行或可以预测数据的增长速度时，它成为必需.
IMHO, it becomes a requirement when you have tens of thousands of rows, or when you can forecast the rate of growth for data.
您需要集中精力调整innodb缓冲池和日志文件的大小.另外，请确保已启用innodb_file_per_table.
You need to focus on tuning the innodb buffer pool and the log file size. Also, make sure you have innodb_file_per_table enabled.
要了解使innodb缓冲池的大小以KB为单位，请运行以下查询:
To get an idea of how big to make the innodb buffer pool in KB, run this query:
SELECT SUM(data_length+index_length)/power(1024,1) IBPSize_KB
FROM information_schema.tables WHERE engine='InnoDB';
这里是MB
SELECT SUM(data_length+index_length)/power(1024,2) IBPSize_MB
FROM information_schema.tables WHERE engine='InnoDB';
这里是GB
SELECT SUM(data_length+index_length)/power(1024,3) IBPSize_GB
FROM information_schema.tables WHERE engine='InnoDB';
我写了有关这种调优的文章
I wrote articles about this kind of tuning
- First Article
- Second Article
- Third Article
- Fourth Article
如果您受到服务器上RAM数量的限制，那么就操作系统而言，请不要超过已安装内存的25％.
IF you are limited by the amount of RAM on your server, do not surpass more than 25% of the installed for the sake of the OS.
这篇关于什么时候需要对MySQL INNODB进行微调?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
