databases/mysql51-server and beginner's InnoDB questions

Matthew Seaman m.seaman at infracaninophile.co.uk
Tue Sep 16 19:49:57 UTC 2008


Morgan Wesström wrote:

> I realized today actually that there are different storage engines 
> available for MySQL and that InnoDB seems to be preferred so I naturally 
> wanted to use it. I can see with "show create table <sometable>" that 
> Mediawiki's tables for example are already created with ENGINE=InnoDB. 
> But in my MySQL config file, which is simply a copy of my-large.cnf, 
> there is a whole section for InnoDB that is commented out. It begins with:
> "# Uncomment the following if you are using InnoDB tables"

Correct.  MySQl's reputation for speed is based on simple, generally
single threaded, tests against the basic MyISAM table type[*].  However
once your database starts getting busy and certainly if you need such 
things as ACID behaviour, Foreign Keys or High Concurrency -- ie. basically 
for any real-world database solution --  then you should be using InnoDB.  

> _First question:_
> Is InnoDB enabled by default regardless of the settings in my.cnf and 
> how can I verify it?

The easiest way to check is to run the following SQL:

   SHOW ENGINES ;

If it doesn't list InnoDB then check the following:

There is a compile-time option to include InnoDB support which is enabled
by default, so unless you've deliberately turned it off your MySQL server
should have compiled-in support for it.  

You can disable InnoDB from within my.cnf using the 'skip-innodb' command
-- if that isn't present then InnoDB should be enabled.

> Assuming it wasn't enabled, despite the output from "show create table", 
> I uncommented all the InnoDB options in my config and restarted MySQL 
> and got the first error:
> 
> "InnoDB: Error: log file /usr/local/var/db/mysql/ib_logfile0 is of 
> different size 0 5242880 bytes
> InnoDB: than specified in the .cnf file 0 67108864 bytes!"
> 
> I was able to work around this by changing innodb_log_file_size from the 
> default 64M to 5M.

> _Second question:_
> How can I increase the logfile size from it's original 5M to the 64M 
> suggested in the config? Can I just delete the old logfile and have it 
> recreated or will that break any of my databases?


Yes -- sizes of various InnoDB related files are set from the my.cnf
file and its not entirely trivial to change them.  A surefire method
which should always work is the following:

   i) Make sure your database is quiescent and dump out all of the
      contents using mysqldump.

  ii) Stop mysql, and remove all files prefixed with 'ib' from within
      /var/db/mysql -- *warning* this deletes any data stored in
      InnoDB tables in the live copy of the DB.  You did take the backup
      mentioned in (i) didn't you?

 iii) Edit file sizes to taste in my.cnf

  iv) Restart MySQL.  It will create new, empty copies of the required
      ib_logfileN and ibdataN files.

   v) Now reload your data from the dump you made in (i)

Note that the size quoted for the ibdata1 file is the initial size,
and that file will grow as you add more data.  The ib_logfileN files
are a fixed size, and tuning them can help optimize performance.

> 
> MySQL also complained about this:
> 
> "[ERROR] /usr/local/libexec/mysqld: unknown variable 
> 'innodb_log_arch_dir=/usr/local/var/db/mysql/'"
> 
> After some googling I realized this was a deprecated variable and the 
> reference to it in the config file should have been removed in 5.1.25.

MySQl 5.1 is still not available for general release.  Unless you need 5.1
specific features, you're actually going to be better off sticking with
MySQL 5.0.x -- and I do believe that MySQL 5.0 is still typically faster 
than MySQL 5.1.

> _Third question:_
> Is this an issue with the FreeBSD port specifically? Should I report 
> this to someone and how would I do that the correct way?

No, the sample my.cnf files in /usr/local/share/mysql/ are copied
straight out of the mysql sources -- there are no FreeBSD specific 
modifications to those files.  The bug is with the upstream MySQL
distribution.  There's already an open bug report:

    http://bugs.mysql.com/bug.php?id=38249

	Cheers,

	Matthew

[*] When you do more realistic benchmarks, MySQL doesn't look quite
so good.  See for example: http://people.freebsd.org/~kris/scaling/7.0 Preview.pdf (Slide 16 in particular)

-- 
Dr Matthew J Seaman MA, D.Phil.                   7 Priory Courtyard
                                                  Flat 3
PGP: http://www.infracaninophile.co.uk/pgpkey     Ramsgate
                                                  Kent, CT11 9PW

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 258 bytes
Desc: OpenPGP digital signature
Url : http://lists.freebsd.org/pipermail/freebsd-ports/attachments/20080916/5eb35f72/signature.pgp


More information about the freebsd-ports mailing list