On the other hand, InnoDB uses an internal cache, so it would be a waste on memory to cache the same data into ZFS and InnoDB. MyISAM engine relies on the underlying filesystem caching mechanism, so you must ensure ZFS will cache both data and metadata (that's the default behavior). You must set the proper block size before putting any data on the dataset, otherwise pre-existing data won't use the desired block size. It's easy to setup datasets for a particular block size at creation with -o option, or after creation with zfs set. MyISAM uses a 8k block size, InnoDB uses a 16k block size for data and 128k for logs. In order to get the best block size and cache tuning possible, I've created 3 datasets: one for InnoDB data files, one for innodb logs, and one for everything else, including MyISAM databases. On top of that, the block size for InnoDB is not the same when you deal with data files, and when you deal with log files. They use different block sizes, and they use different caching mechanisms. If like me you've started hosting MySQL databases a long time ago (say ~15 years ago) you might have some DB using the old MyISAM engine, and some newer ones using InnoDB. After a good dive into experts do's & don't's here are few steps I've taken to tune my server. I'm running a FreeBSD 9.x server hosting http, php, mysql, mail, and many other things, so a databases-only optimization would be counterproductive. ZFS default behavior and settings are perfect for a wide range of workloads and uses, but it's not exactly what you need for databases hosting. Lots of FreeBSD users are coming to ZFS since the release of FreeBSD 10, as it's so easy to install the system on top of that powerful filesystem.
0 kommentar(er)
