Date: Tue, 16 Sep 2008 23:04:25 +0200 From: =?ISO-8859-1?Q?Morgan_Wesstr=F6m?= <freebsd-ports@pp.dyndns.biz> To: "freebsd-ports@freebsd.org" <freebsd-ports@freebsd.org> Subject: Re: databases/mysql51-server and beginner's InnoDB questions Message-ID: <48D01F59.10008@pp.dyndns.biz> In-Reply-To: <48D00DD5.9030806@infracaninophile.co.uk> References: <48D0000E.9050709@pp.dyndns.biz> <48D00DD5.9030806@infracaninophile.co.uk>
next in thread | previous in thread | raw e-mail | index | archive | help
Sorry, I forgot to cc the list... Matthew Seaman wrote: Thanks Matthew. Your answers are very helpful for someone like me to understand MySQL better and to guide me what parts to explore more thoroughly. > 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. InnoDB pops up in several LAMP related guides and instructions I've come across so that's what caught my attention. I only use MySQL to run some low traffic forums on my FreeBSD server at home so it's not that important but if it's good for your average data center, then it's good for me I figured :-) >> _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 ; Simple as that. I really should have searched some more, I apologize for that. I will refrain from asking how to change the default engine to InnoDB and find it out myself... >> _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. I feared there was some vital relation between those files. Your instructions are now part of my growing collection of helpful information. Thanks. > 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. Yes, I took a calculated risk when I installed it but I thought the difference would be minor in the way I use it and since 6.0-alpha was also in the ports tree I went for the version in between. >> _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 I found some other report suggesting it was fixed in 5.1.25 but in this bugreport it appears to be fixed in versions after 5.1.26 if I understand it correctly? Regards Morgan
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?48D01F59.10008>