zfs + mysql questions

[wonslung]

I've got a system that needs mysql running on it, i am using zfs.
It's running 7.2 current.

I was wondering if the zfs best practices guide applies to freebsd as well, i understand that it was originally written for opensolaris.

My main concern is this: I have a few sites with mysql databases, none of them are especially LARGE yet but they do get used often. Right now i'm running it with all default settings. I'd like to tune it some because i'm getting some lag and i'm pretty sure it's due to the database. All of my databases are currently myisam.

According to the opensolaris tuning guide i should set max ARC lower and set the zfs record size to match the mysql block size.

Is there anything else i need to do? does this still hold true for freebsd? i can't find a lot of information on mysql + zfs + freebsd. Also, does it matter than i run everything on one machine (mysql, apache and php)

thanks for any help.

 

[vivek]

ZFS tunning done with zfs and freebsd.

MySQL tuning done with my.cnf in /var/db/mysql. The only common factors between two is disk I/O and file system cache. Otherwise they work independently to each other. In short, most mysql optimization done via my.cnf should work on FreeBSD.

. Also, does it matter than i run everything on one machine (mysql, apache and php)

If you run mom and pops website it does not matter. You can run all services from a single FreeBSD box. If you are going to server millions and millions page view than you need separate boxes (for app server, mysql, Apache, reverse proxy), hardware RAID, load balancer, centralized storage (NFS or cluster aware file system) and so on. A well tuned FreeBSD server with software RAID1, SATA 250G disk and 4GB RAM can server 4-5 million page views easily (again depend upon hardware, server side programming and so on)...

 

[SirDice]

One thing to note though, MyISAM tables work fine on the 'regular' filesystems as it expects the filesystem to cache data. InnoDB tables however need to have filesystem cache turned off. If you use both types of tables it's best to split these onto 2 different filesystems. One with the cache turned on, the other where it's turned off.