Date: Tue, 16 Sep 2008 20:49:41 +0100 From: Matthew Seaman <m.seaman@infracaninophile.co.uk> To: =?ISO-8859-1?Q?Morgan_Wesstr=F6m?= <freebsd-ports@pp.dyndns.biz> Cc: freebsd-ports@freebsd.org Subject: Re: databases/mysql51-server and beginner's InnoDB questions Message-ID: <48D00DD5.9030806@infracaninophile.co.uk> In-Reply-To: <48D0000E.9050709@pp.dyndns.biz> References: <48D0000E.9050709@pp.dyndns.biz>
next in thread | previous in thread | raw e-mail | index | archive | help
This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig9C01234D08F989373288E91A
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Morgan Wesstr=F6m wrote:
> I realized today actually that there are different storage engines=20
> available for MySQL and that InnoDB seems to be preferred so I naturall=
y=20
> wanted to use it. I can see with "show create table <sometable>" that=20
> Mediawiki's tables for example are already created with ENGINE=3DInnoDB=
=2E=20
> But in my MySQL config file, which is simply a copy of my-large.cnf,=20
> there is a whole section for InnoDB that is commented out. It begins wi=
th:
> "# 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=20
things as ACID behaviour, Foreign Keys or High Concurrency -- ie. basical=
ly=20
for any real-world database solution -- then you should be using InnoDB.=
=20
> _First question:_
> Is InnoDB enabled by default regardless of the settings in my.cnf and=20
> 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. =20
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"=
,=20
> I uncommented all the InnoDB options in my config and restarted MySQL=20
> and got the first error:
>=20
> "InnoDB: Error: log file /usr/local/var/db/mysql/ib_logfile0 is of=20
> different size 0 5242880 bytes
> InnoDB: than specified in the .cnf file 0 67108864 bytes!"
>=20
> I was able to work around this by changing innodb_log_file_size from th=
e=20
> default 64M to 5M.
> _Second question:_
> How can I increase the logfile size from it's original 5M to the 64M=20
> 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.
>=20
> MySQL also complained about this:
>=20
> "[ERROR] /usr/local/libexec/mysqld: unknown variable=20
> 'innodb_log_arch_dir=3D/usr/local/var/db/mysql/'"
>=20
> After some googling I realized this was a deprecated variable and the=20
> 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=20
> 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=20
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=3D38249
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 Pr=
eview.pdf (Slide 16 in particular)
--=20
Dr Matthew J Seaman MA, D.Phil. 7 Priory Courtyard
Flat 3
PGP: http://www.infracaninophile.co.uk/pgpkey Ramsgate
Kent, CT11 9PW
--------------enig9C01234D08F989373288E91A
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (FreeBSD)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEAREIAAYFAkjQDdsACgkQ8Mjk52CukIzSawCfZ0psCYlfs+YlmOeEqNfz1Dvy
oasAoIxiwRDAnZ8NLLVzoZFn2lu1IADW
=/c0T
-----END PGP SIGNATURE-----
--------------enig9C01234D08F989373288E91A--
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?48D00DD5.9030806>