Re: InnoDB as storage engine for sa_bayes

From: Pawe³ Têcza <ptecza(at)uw.edu.pl>
Date: Thu Aug 30 2007 - 03:47:52 EDT

Alex Woick <alex@wombaz.de> writes:

>> -rw-rw---- 1 mysql mysql 1010M Aug 28 08:25 ibdata1
>
>> -rw-rw---- 1 mysql mysql 264M Aug 27 17:09 awl.ibd
>> -rw-rw---- 1 mysql mysql 112K Aug 28 08:25 bayes_expire.ibd
>> -rw-rw---- 1 mysql mysql 96K Aug 27 17:09 bayes_global_vars.ibd
>> -rw-rw---- 1 mysql mysql 468M Aug 27 21:11 bayes_seen.ibd
>> -rw-rw---- 1 mysql mysql 148M Aug 27 21:43 bayes_token.ibd
>> -rw-rw---- 1 mysql mysql 112K Aug 28 08:25 bayes_vars.ibd
>
>> As you can see above, the new storage engine consumed 2 times
>> bigger diskspace then the old. Is it a good behave or I should
>> feel worried?
>
> Nothing to worry. But you have perhaps imported your data twice and have an
> empty ibdata1 file which only occupies space.

Hello Alex,

At first, thanks a lot for your reply and interesting comments! :)

> I quoted the innodb data files. Since you have defined
> innodb_file_per_table, the table data is saved into the *.ibd files in
> the database directory. Without that option all table data would go to
> the ibdata* file(s) in the base data directory. As far as I know, data
> for one table is saved either in ibdata* or in the *.ibd file, but not
> both.

I can quote innodb_data_file_path option to test it, of course, but I affraid that it's necessary. The MySQL doc [1] says:

"Note: InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate."

> Perhaps you played around and first imported the data without
> innodb_file_per_table, which imported into ibdata1. Then you perhaps
> dropped the tables and defined innodb_file_per table and imported
> again, so the *.ibd files were created and filled. The ibdata1 may now
> be empty, but it will never shrink.

I remember that before injecting the MySQL dump I removed all ib* files and created initial InnoDB tablespace and logs running mysqld from command line:

root@mysql:/var/lib/mysql# /usr/sbin/mysqld InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created!
070827 15:25:01 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 070827 15:25:01 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 10 MB InnoDB: Database physically writes the file full: wait... 070827 15:25:01 InnoDB: Log file ./ib_logfile1 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created

070827 15:25:02 InnoDB: Started; log sequence number 0 0 070827 15:25:02 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.38-Ubuntu_0ubuntu1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Ubuntu 7.04 distribution

While injecting I could see how the ibdata1 file was growing from 10MB to 1010MB.

> Try the following: Dump all databases which have innodb tables and
> drop all innodb tables. Stop the server, remove the ibdata1 and *.ibd
> files and restart the server. An empty and small ibdata1 file will be
> recreated. Now import your databases. I bet the ibdata1 file will not
> grow and all data will be imported into *.ibd.

Please look at below. What have I won in the bet? ;)

> It is not neccessary to dump/reload the data for changing the database
> engine of a table. Simply edit the tables with Mysql Query Browser or
> the Mysql Administrator and change the table engine from myisam to
> innodb. Or execute an SQL statement: "ALTER TABLE mytable
> ENGINE=innodb".

Yes, I know it and even I was trying to convert the MyISAM tables in that way, but it was terrible slowly, so I chose a method with injecting MySQL dump. Unfortunately it wasn't faster ;)

root@mysql:/var/lib/mysql# date && mysql sa_bayes -u root -p < ~/sa_bayes_innodb.sql && date Mon Aug 27 15:28:40 CEST 2007
Enter password:
Mon Aug 27 21:42:45 CEST 2007
root@mysql:/var/lib/mysql#

You can see above that it took more then 6 hours for ~560MB dump file!

My best regards,

Pawel

[1] http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html Received on Thu Aug 30 03:47:21 2007