MySQL and ZFS

S

singhsukhwinder

I have installed FreeBSD on ZFS, I am going to make it as Apache/MySQL/PHP server. I have read some articles about tuning MySQL on ZFS, I am following the following procedure

Created a zfs file system zroot/svr/mysql (mounted on /svr/mysql)
Code: 
atime=off
record size=8kb
exec=off
compression=off
setuid=off
Moved /var/db/mysql to /svr/mysql/
# ln -s /svr/mysql/mysql /var/db/mysql

Now during testing I have observed that MySQL is not as fast as it is working in mu ubuntu 6.10 (i386) server, same queries is taking 1.7 seconds (sime times >2 seconds), where as in ubuntu it is taking <0.8 sec. I have searched more on tuning, and found
# zfs set primarycache=metadata zroot/svr/mysql
but after this command mysql becomes dead slow, and when I again do
# zfs set primarycache=all zroot/svr/mysql
it returns to previous status. Is the above method corrected or we should leave the Mysql folder in /var/db? Or some how we can seperate mysql data directory from log directory ?

Is there any other special tuning we need to do in FreeBSD for MySql especially in ZFS.
I have observed that in FreeBSD ufs, even same query it taking 1.2 seconds. I don't know why it is so fast in ubuntu, can it be due to Mysql version 5.0.24 and in FreeBSD 5.5.28?

kindly help
Thanks and regards
 
singhsukhwinder said:
Moved /var/db/mysql to /svr/mysql/
# ln -s /svr/mysql/mysql /var/db/mysql
Click to expand...
There's no need for this as you can simply set:
Code: 
mysql_dbdir="/svr/mysql/"
 
@singhsukhwinder,

I had told you when you asked me in my blog that this will not work easily, remember?

Have a look here, a bit old but useful for starters.
 
Are you using InnoDB, MyISAM, or another DB engine? You need to tune the recordsize to match the DB engine in use. I thought InnoDB used 16 KB records, but could be mis-remembering things.
 
gkontos said:
@singhsukhwinder,

I had told you when you asked me in my blog that this will not work easily, remember?

Have a look here, a bit old but useful for starters.
Click to expand...

@gkontos
But I have updated RAM to 4GB. If I check the CPU/RAM usage using top command, plenty of RAM remains free and swap is never used at all. CPU usage goes up to 60-70% while query is fired. Do you think even then RAM may be a bottleneck? Or CPU(Intel Dual Core 2.8) may be a bottleneck? Or ZFS is meant for CPUs >4 Core?

It is not very slow, but slower when compared with my running ubuntu (6.10, i386, 32 Bit with gnome desktop) system, with equivalent CPU (Intel Dual Core) and 1GB of RAM.

What about Mysql performance on UFS (on Intel P4 , running AMD 64Bit Freebsd 8.3)? As it is still not comparable with ubuntu.
Do we need some special tuning if using mysql on FreeBSD?

How about PgSQL performance compared with MySQL on ZFS?

I have come to know that we should set ZFS parameters for mysql, like recordsize, atime, chache etc before populating the DB. Does that mean after installing Mysql and before restoring the DB from backup?

Thanks & Regards

I am using MyISAM engine and recordsize=8
 
@singhsukhwinder,

I am afraid you are not providing correct information of what you are comparing so it is difficult to help you.

Mysql version 5.0.24 and in FreeBSD 5.5.28
Click to expand...

but slower when compared with my running ubuntu (6.10, i386, 32 Bit with gnome desktop)
Click to expand...

I can only tell you what I usually do when it comes to database servers. I ALWAYS use Innodb engine so this may not apply to you.

  1. Separate log files from DB files. This can be done from my.cnf
  2. Separate Inodb databases from Isam
  3. Much the recordset to 16k for Inodb
  4. Adjust my.cnf according to the server
  5. Use a ZIL device

Generally speaking you can not compare the performance of a database server running in desktop environment with one running in a dedicated server environment.

Most of the times the performance can be increased or reduced dramatically with the right values in my.cnf
 
gkontos said:
@singhsukhwinder,

I am afraid you are not providing correct information of what you are comparing so it is difficult to help you.





I can only tell you what I usually do when it comes to database servers. I ALWAYS use Innodb engine so this may not apply to you.

  1. Separate log files from DB files. This can be done from my.cnf
  2. Separate Inodb databases from Isam
  3. Much the recordset to 16k for Inodb
  4. Adjust my.cnf according to the server
  5. Use a ZIL device

Generally speaking you can not compare the performance of a database server running in desktop environment with one running in a dedicated server environment.

Most of the times the performance can be increased or reduced dramatically with the right values in my.cnf
Click to expand...

@gkontos:
I am giving detailed information as under :

I have a ubuntu LAMP server running on Intel dual core CPU RAM 512MB 160GB HD. Basically the os is ubuntu 6.10 (32 Bit) Lamp server, but after installation I added a package in it called "ubuntu-desktop" to make it GUI Based, as at that time I was a newbie and didn't know much about linux/Unix command line. This server is giving very good performance, but due to end of support for ubuntu 6.10, as well as no Disk level protection (Mirroring), I thought to re-install its OS from scratch and use RAID 1.

But, as of now I was more inclined towards FreeBSD due to its stability, efficiency and ZFS support (which I recently explored, no prior experience in ZFS) and have a little experience in setting up some small servers on FreeBSD, I decided to go for FreeBSD.

I am able to install FreeBSD on ZFS using to HDDs of 500GBs, and upgraded RAM from 512MB to 4GB. I also installed Apache, PHP, MySQL using ports, and server is ready. But I have observed that mysql performance, though it is not bad, but it is poorer then that of Ubuntu server, then I tested same queries on other Freebsd server having UFS and Freebsd 8.3, on that also same query is very slower then Ubuntu. Now I thought that there may be some tunning that we need to do in case of FreeBSD for Mysql, like, one thing I observed that there is no my.cnf file in FreeBSD, it is running on default values, I have to copy one among 3 or 4 sample my.cnf files. I think CPU and RAM is not creating any bottleneck, as I observed using "top" command (But, I may be wrong)

Actually I want to stay with FreeBSD, then Ubuntu, for my love with FreeBSD! So I'm trying to improve the Mysql performance some how by my.cnf and some ZFS parameters like recordsize etc.

And I also want to know that whether ZFS is meant for my kinda server or large servers only?
 
singhsukhwinder said:
@gkontos:
Actually i want to stay with Freebsd, then Ubuntu, for my love with Freebsd ! So im trying to improve the Mysql performance some how by my.cnf and some ZFS parameters like recordsize etc
Click to expand...

You can find some example configurations in /usr/local/share/mysql. You can grab one from there and copy it to /usr/local/etc/my.cnf
Keep in mind that those examples are a bit outdated. my-huge.cnf is really not far from what we usually start with. And you can even grow the numbers more for your situation.

singhsukhwinder said:
And i also want to know that whether ZFS is meant for my kinda server or large servers only ?
Click to expand...

I think that 4GB is a good starting point. If you wonder about the cost then you might see that investing in memory is cheaper than investing in a good raid card.

Just a quick test if you can log into mysql and issue the following command:

[CMD=""]>SET GLOBAL innodb_flush_log_at_trx_commit=2;[/CMD]

Then rerun your benchmark test and let me know if this time is faster.
 
I have converted my MyISAM tables to innodb , stoped mysql-server, copied mysql directory to some other place
# zfs set recordsize=16K zroot/svr/mysql
Then copied mysql directory back to /svr/mysql/
After this the performance of Mysql has improved much, befor this i was using MyISAM engine with recordsize 8K, Now the queries are running as good as my Ubuntu server (some time better speed)

@gkontos:
I will try
Code: 
>SET GLOBAL innodb_flush_log_at_trx_commit=2;
this and inform the result again.

One thing is still missing, Mysql is not caching the queries, i.e if i run the same query repeatedly it takes same time , where as on my ubuntu system, if i repeat the query, the result is instant.
 
Perhaps you could compare the configuration files from Ubuntu to the ones you are using on FreeBSD. Most likely something is enabled/disabled.
 
You must log in or register to reply here.
Back
Top