mysql - 为magento性能测试修改innodb的innodb_buffer_pool_size而引发的问题

mysql的日志文件

mysql的日志文件有四种,分别是错误日志(error log),普通数据操作日志(general query log),二进制日志(binary log)和低效率查询日志(slow query log).错误日志记录了mysqld服务器本身在启动,关闭和运行过程中发生的问题,错误.普通数据操作日志记录了在服务器上执行过的普通查询SQL语句.二进制日志则记录了所有修改数据的SQL语句操作记录.而低效率查询日志则记录了所有执行时间超过设定时间(mysqld配置参数long_query_time)或者是没有使用索引的SQL语句.

日志文件非常重要.程序开发人员和管理人员都需要了解掌握.通常这些日志文件默认是保存在mysqld的数据目录下的.

error log的保存目录可以在启动mysqld服务守护程序时通过--log-error参数指定.如果没有指定,则默认使用host_name.err为文件名保存在服务器的数据目录下.

我在本地使用wamp作为开发环境.为了让magento跑的快一点,导入产品数据快一点,magento官网论坛上搜索了一下,有帖子说修改mysql的innodb配置会提升导入的速度.于是打开phpmyadmin查看,发现我的innodb是打开的,但是innodb_buffer_pool_size竟然是8M.但是实际上我设置的是354M啊.怎么无效?备份了原来的my.ini我复制了my_huge.ini修改一下作为my.ini配置文件.只是简单去掉innodb相关配置选项的注释好让它生效.第一次我只是去掉innodb_data_file_path和innodb_buffer_pool_size这行的注释,

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = d:\\wamp\\bin\mysql\
innodb_data_file_path = ibdata1:100M:autoextend
#innodb_log_group_home_dir = d:\wamp\\bin\\mysql\
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 300M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

重启后.发现magento的网站打不开了.错误:

a:4:{i:0;s:95:"SQLSTATE[42S02]: Base table or view not found: 1146 Table 'testdb.core_store' doesn't exist";i:1;s:1759:"#0 ...\includes\src\Varien_Db_Statement_Pdo_Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)

怀疑是innodb没有启用成功.进phpmyadmin一看.果然innodb是灰色的.在mysql控制台,使用show engines;support状态也是no.

我记得以前我碰到过同样的情况,我是在innodb配置选项的上方加一个section,如
[INNODB]
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = d:\\wamp\\bin\mysql\
innodb_data_file_path = ibdata1:100M:autoextend
#innodb_log_group_home_dir = d:\wamp\\bin\\mysql\
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 300M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

加[INNODB]保存后重启,发现innodb可以使用了,但是innodb_buffer_pool_size还是8M.还是默认的值.但是显然我设置的不是默认的值.事实上这个地方我的理解一直是错误的.加了一个section,相当于注释掉这个section以下的所有配置选项了.而mysql默认其实是打开innodb引擎的.(所有加一个section的作法是错误而无根据的,而且到现在才发现,)

把[INNODB]配置section去掉,重启当然还是不行了.这个时候才想起应该去查看一下error log.

InnoDB: Error: auto-extending data file d:\wamp\bin\mysql\mysql5.1.33\data\ibdata1 is of a different size
InnoDB: 0 pages (rounded down to MB) than specified in the .cnf file:
InnoDB: initial 6400 pages, max 0 (relevant if non-zero) pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
110906 21:06:09 [ERROR] Plugin 'InnoDB' init function returned error.
110906 21:06:09 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
110906 21:06:09 [Note] Event Scheduler: Loaded 0 events
110906 21:06:09 [Note] wampmysqld: ready for connections.
Version: '5.1.33-community-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
110906 21:08:08 [Note] wampmysqld: Normal shutdown

这个是第一次修改innodb_buffer_pool_size和innodb_data_file_path选项重启的日志,没有定义数据文件保存目录(innodb_data_home_dir),则innodb的数据文件默认的是保存到mysql服务器的数据目录,但是我在innodb_data_file_path配置选项中却修改了大小,和默认大小不一样出现了如错误.

于是我去掉innodb_data_home_dir的注释,指定了一个目录d:\wamp\bin\mysql\

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = d:\wamp\bin\mysql\
innodb_data_file_path = ibdata1:100M:autoextend
#innodb_log_group_home_dir = d:\wamp\\bin\\mysql\
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 300M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

下面是重启后对应在error log中的日志,幸亏我注意到了倒数第三行,我明明设置的d:\wamp\bin\mysql什么变成了这个,不会是\b被转义了吧.事实果真如此.(注:我们经常看见在apache,php和mysql的配置文件中总是用/或\\,虽然说文档都有建议这么写,但是觉得不这么写也可以,现在算是明白了.)

110906 21:10:47  InnoDB: Operating system error number 123 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html
InnoDB: File name d:\wampin\mysql\ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

我修改mysql innodb相关配置:

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = d:\\wamp\\bin\\mysql\
innodb_data_file_path = ibdata1:100M:autoextend
#innodb_log_group_home_dir = d:\wamp\\bin\\mysql\
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 300M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

应该这样写,innodb_data_home_dir = d:\\wamp\\bin\\mysql\或
innodb_data_home_dir = d:/wamp/bin/mysql/,重启后error log

这样就是正常了.如果不正常可能是由于之前的数据库文件或日志有问题.我在服务器上配置修改时就碰到了问题.后面再说.

先说这样总算是把innodb_buffer_pool_size修改了.你可以使用phpmyadmin查看一下.但是问题来了,原先的那些magento网站全出问题了.magento的report看了你根本就不明白:
a:4:{i:0;s:95:"SQLSTATE[42S02]: Base table or view not found: 1146 Table 'testdb.core_store' doesn't exist";i:1;s:1759:"#0 ...\includes\src\Varien_Db_Statement_Pdo_Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)

就是说表找不到.上面有提到出这个问题的可能原因是innodb没启用.现在明明启用了.而且还增加了buffer size呢.看了mysql的error log:

110906 22:55:50 [ERROR] Cannot find or open table copybagcom/core_store from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

这个最后我在magento论坛和google上查阅了N多帖子,现虽不敢下结论,但是估计是由于原来的数据文件中有损坏,导致我们最后只能重新导入备份的数据.如果你没有备份,可以尝试一下强制恢复.不过能不能成功就不清楚了.能用就不清楚了.那个测试起来可要不少时间.

最后有必要强调的是,mysql的error log在解决问题中所起到的作用.但是想要查看日志,首先要先打到它保存在什么位置.引用一下mysql手册中的说明:

 

By default, all log files are created in the mysqld data directory.

You can specify where mysqld writes the error log with the--log-error[=file_name] option. If nofile_name value is given,mysqld uses the namehost_name.err by default and writes the file in the data directory. If you executeFLUSH LOGS, the error log is renamed with the suffix-old and mysqld creates a new empty log file. (No renaming occurs if the--log-error option was not given tomysqld.)

If you specify --log-error in an option file in a section thatmysqld reads,mysqld_safe also will find and use the option.