程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> mysql 加大 了logfile之後,遇到innodb disable的問題 FW

mysql 加大 了logfile之後,遇到innodb disable的問題 FW

編輯:關於MYSQL數據庫

這兩天在做MySQL的性能測試,直接將my-large.cnf的文件覆蓋了原先my.cnf(基於my-small.cnf),這其中innodb的參數,如下變化:

bullet:/data/db # diff big_inno.cnf small_inno.cnf
5,7c5,7
< innodb_buffer_pool_size = 256M
< innodb_additional_mem_pool_size = 20M
< innodb_log_file_size = 64M > innodb_buffer_pool_size = 16M
> innodb_additional_mem_pool_size = 2M
> innodb_log_file_size = 5M

然後我就折騰啊折騰,測試啊測試,突然發現有個表竟然是三個文件(MYD,MYI,FRM)的形式存在數據庫目錄下邊

我畢竟是個新手!

mysql> show engines; | Engine | Support | Comment | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | NO | Supports transactions and page-level locking |
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | NO | Archive storage engine |
| CSV | NO | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | NO | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| ISAM | NO | Obsolete storage engine |
+------------+----------+----------------------------------------------------------------+

MySQL> show variables like 'have_innodb'; | Variable_name | Value | | have_innodb | DISABLED | 1 row in set (0.00 sec)

後來發現日志裡邊有些報錯:

Version: '5.0.41' socket: '/tmp/MySQL.sock' port: 3306 Source distribution
070625 19:16:29 InnoDB: ERROR: the age of the last checkpoint is 9433987,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

070626 09:30:35 MySQLd started
InnoDB: Error: log file /data/db/ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specifIEd in the .cnf file 0 67108864 bytes!
070626 9:30:35 [Note] /usr/local/mysql5.0.41/libexec/MySQLd: ready for connections.
Version: '5.0.41-log' socket: '/tmp/MySQL.sock' port: 3306 Source distribution

報錯也挺明顯的,不過也沒想到問題所在,我比較笨!

只是懷疑mysql編譯的時候是不是innodb沒編譯進來,還懷疑Oracle與MySQL的關系是否已經惡化....,顯示的加上--with-innodb重新編了,沒用

後來也是在http://forums.MySQL.com/read.PHP?10,159210,159291#msg-159291 發現有人跟我有相同的郁悶之處,(他可能更郁悶,他按照手冊做了),然後就刪除了所有innodb相關文件,連數據文件,重啟就可以了。

正確的操作在這裡:

http://dev.MySQL.com/doc/refman/4.1/en/adding-and-removing.Html

13.2.6. Adding, Removing, or Resizing InnoDB Data and Log Files

This section describes what you can do when your InnoDB tablespace runs out of room or when you want to change the size of the log files.

From MySQL 3.23.50 and 4.0.2, the easIEst way to increase the size of the InnoDB tablespace is to configure it from the beginning to be auto-extending. Specify the autoextend attribute for the last data file in the tablespace definition. Then InnoDB increases the size of that file automatically in 8MB increments when it runs out of space. Starting with MySQL 4.0.24 and 4.1.5, the increment size can be changed by setting the value of the system variable, which is measured in MB.

Alternatively, you can increase the size of your tablespace by adding another data file. To do this, you have to shut down the MySQL Server, change the tablespace configuration to add a new data file to the end of innodb_data_file_path, and start the server again.

If your last data file was defined with the keyWord autoextend, the procedure for reconfiguring the tablespace must take into account the size to which the last data file has grown. Obtain the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify the rounded size explicitly in innodb_data_file_path. Then you can add another data file. Remember that only the last data file in the innodb_data_file_path can be specifIEd as auto-extending.

As an example, assume that the tablespace has just one auto-extending data file ibdata1:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

Suppose that this data file, over time, has grown to 988MB. Here is the configuration line after modifying the original data file to not be auto-extending and adding another auto-extending data file:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

When you add a new file to the tablespace configuration, make sure that it does not exist. InnoDB will create and initialize the file when you restart the server.

Currently, you cannot remove a data file from the tablespace. To decrease the size of your tablespace, use this procedure:

  • Use MySQLdump to dump all your InnoDB tables.

  • Stop the server.

  • Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.

  • Remove any .frm files for InnoDB tables.

  • Configure a new tablespace.

  • Restart the server.

  • Import the dump files.

    If you want to change the number or the size of your InnoDB log files, stop the MySQL server and make sure that it shuts down without errors (to ensure that there is no information for outstanding transactions in the log). Copy the old log files into a safe place in case something went wrong during the shutdown and you need them to recover the tablespace. Delete the old log files from the log file directory, edit my.cnf to change the log file configuration, and start the MySQL Server again. MySQLd sees that no InnoDB log files exist at startup and creates new ones.

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved