ezmlm home : internet : mail : toaster : FAQ : what applications are installed : mysql perl modules

  • MySQL (3,4,5): A fast, reliable database engine

Why MySQL?

It's free, it's stable, it's wicked fast, and it's supported by nearly everything. It runs on Windows, MacOS, Unix, and Linux. It has a billion already written applications for it. In this case, as is many others, the choice some down to "it's the software, stupid". No, not MySQL, but all the applications written that use it that make it so compelling.

On Mail::Toaster, MySQL is used extensively. You can store all the authentication (vpopmail) info in MySQL, per-user spam preferences, webmail address books and preferences, and more. By using MySQL as the storage engine, you can scale your system to support clustered environments supporting tens of thousands of users.

What get's installed?

The programs installed are the version of MySQL you selected in toaster-watcher.conf and the perl modules for interacting with MySQL databases (DBI & DBD::mysql).

On FreeBSD, if /etc/my.cnf is not installed, the installer will also install the my-large.cnf from the mysql examples for you. The defaults are quite reasonable for most people. If you will have tens of thousands of users or less than 64MB of RAM, you may want to adjust some settings there.

What version of MySQL is recommended?

As of 4/26/2005, the best version of MySQL to use is MySQL 4.0.x with x being the latest released version.

MySQL 3.x is still known to work but lacks current support from, well, everyone.

MySQL 4.0 is production stable. It's feature set is stable and you won't be surprised when things work differently after an upgrade (as is the case with 4.1). Nearly all of the billions of apps written for LAMP expect to see 4.0.x. All the apps included with Mail::Toaster are configured (by default) to use MySQL 4.0.x. Unless you have a very specific and compelling reason otherwise, use 4.0.

According to many, MySQL 4.1.x is "production quality" but I have had plenty of issues since upgrading to 4.1. I'm a developer, I've been able to work around them. If you aren't a developer, you'll waste a lot of time trying to figure out why something in the docs simply doesn't work.

If it weren't such a major hassle to downgrade, I'd revert back to 4.0.x. I started using 4.1 for several months before I started seeing issues and it's not exactly trivial to downgrade. The worst of the issues I've seen is having myisamchk cause table corruption. It is a difficult problem to pin down, but I warned a client of mine who uses MySQL extensively in a huge MySQL I set up for them. He ignored my warning until a month later, he got bit by it. I grinned.

The workaround for using myisachk is to use "repair table NAME" instead. I've had several other similar issues that have left me feeling that even if the version number is 4.1.11, it's still not ready for "production". Consider it pre-production. It's stable, but unless you have a MySQL guru or two handy, wait a while before upgrading.

Should I use linuxthreads?

I don't recommend it, but others disagree. YMMV.

Rather than spending much time on why you might want to use linuxthreads, I recommend reading those two articles. In summary, if you have problems with MySQL spin locking (sucking up 100% of your CPU), then you are running FreeBSD 4.x and likely seeing a known bug. The workaround is to rebuild MySQL with the linuxthreads option.

However, if seen quite a number of problems with FreeBSD 4.x systems running MySQL with linuxthreads. The issue always manifests itself as random authentication (or other db related) failures. The errors are most prominent on heavily loaded systems, or while running a compile. As a consequence, I do not recommend using MySQL with linuxthreads on FreeBSD 4. I've personally witnessed this problem on FreeBSD 4.9, 4.10, and 4.11 running various version of MySQL 4.0.x.

You can rebuild MySQL with linuxthreads as follows:

1. using Mail::Toaster

  • edit toaster-watcher.conf and set install_mysql_linuxthreads
  • deinstall MySQL: pkg_delete `pkg_info | grep "^ mysql-" | cut -f1 -d" "`
  • install MySQL: toaster_setup.pl -s mysql

2. using portupgrade

  • manually edit /usr/local/etc/pkgtools.conf and add WITH_LINUXTHREADS=yes to the databases/mysql40-server section of MAKE_ARGS (see contrib/pkgtools.conf in Mail::Toaster)
  • portupgrade -f `pkg_info | grep "^ mysql-" | cut -f1 -d" "`

3. manually

  • cd /usr/ports/databases/mysql40-server
  • make -DWITH_LINUXTHREADS
  • make deinstall
  • make -DWITH_LINUXTHREADS install clean

How do I set up replication?

You have a couple options. I still favor the one to many replication used in MySQL 4.0.x. If you choose that, you should look into MySQL replicate manager.

If you're brave and adventurous, feel free to try out the new cluster management available in MySQL 4.1. See more info here.

http://dev.mysql.com/tech-resources/articles/mysql-cluster-for-two-servers.html

Last modified on 6/17/05.