My servers are using way more memory then it should.

Hello Folks,

I've been playing around with FreeBSD for about 1 year now and i like it. Recently I've been reinstalling most of the Linux systems i use to use FreeBSD instead. Most of the systems are under minimal load and have lots of physical memory so i have not had any real issues. Last week I've decided to reinstall our MySQL server and before going live with it, I've run some tests of similar hardware and some test hardware. Here i started to run into massive memory using importing and exporting MySQL Schema. I'm testing with our largest DB, its about 5GB in size. Here is some test results:

1. Production Server Dell R610, 2 x X5675, 64GB RAM, 64GB SWAP, FreeBSD 12.1-p1, ZFS, MySQL-5.7.2.9, innodb_buffer_pool_size=40GB
The import took 1 Hour and 35 Minutes and consumed 100% of RAM and about 50% of SWAP (Live Server with other schemas and traffic but the load is minimal and Free RAM before i start the import was around 40 GB)
2. Test Server Dell T610 ESXi, FreeBSD 12.1-p1 VM, 1 CPU, 2GB RAM, 4 GB SWAP, ZFS, MySQL-5.7.2.9, innodb_buffer_pool_size=1GB/default config
Within 2 Minutes it consumed 100% of RAM, SWAP and died
3. Test Server Dell T610 ESXi, Debian 10.1.0 VM, 1 CPU, 2GB RAM, 2 GB SWAP, EXT4, MySQL-5.7.2.9, innodb_buffer_pool_size=128M/default config
Completed in 23 Minutes, RAM was in use about 95%, 0% of SWAP was used.
4.Test Server Dell T610 ESXi, Fedora 31 VM, 1 CPU, 2GB RAM, 2 GB SWAP, XFS, MySQL-5.7.2.9, innodb_buffer_pool_size=128M/default config
Completed in 21 Minutes, RAM was in use about 95%, 0% of SWAP was used.
5. Test Server Dell T610 ESXi, FreeBSD 12.1-p1 VM, 1 CPU, 2GB RAM, 4 GB SWAP, ZFS, MySQL-5.7.2.9, reduced innodb_buffer_pool_size=128M the rest is default
Within 2 Minutes it consumed 100% of RAM, SWAP and died. As if the innodb_buffer_pool_size value didnt reflect anything.
6. Test Desktop i7-8700 ESXi, FreeBSD 12.1-p1 VM, 1 CPU, 2GB RAM, 4 GB SWAP, ZFS, MySQL-5.7.2.9, innodb_buffer_pool_size=1GB/default config
Completed in 17 Minutes, 100% RAM was used and about 20% of the SWAP

As you can see in the test results. The Linux systems seem to do just fine where as BSD wants enormous amounts or it dies on the Dell Servers. Interesting the results are quiet different on a Desktop Computer. I have also seen this behavior on some of the other test VM systems with lesser memory when ClamAV performs system scan. All of the systems above are minimalist installs of the OS with only PKG, MySQL, SUDO, NANO, (OSSEC and CLAMAV on Production) and OpenVM Tools of used on ESXi.

Most of my rc.conf looks like this plus minus the application the server is used for:
Code:
hostname="test"
clear_tmp_enable="YES"
dumpdev="NO"
ifconfig_vmx0="inet 10.10.10.100 netmask 255.255.255.0"
ip6addrctl_enable="NO"
defaultrouter="10.10.10.10"
sshd_enable="YES"
ntpd_enable="YES"
ntpd_flags="-4"
zfs_enable="YES"
firewall_enable="YES"
firewall_script="/usr/local/etc/ipfw.rules"
firewall_logging="YES"
vmware_guest_vmblock_enable="YES"
vmware_guest_vmhgfs_enable="NO"
vmware_guest_vmmemctl_enable="YES"
vmware_guest_vmxnet_enable="YES"
vmware_guestd_enable="YES"
mysql_enable="yes"
ossec_hids_enable="YES"
clamav_clamd_enable="yes"
clamav_freshclam_enable="yes"
syslogd_enable="YES"
syslogd_flags="-4 -n -s -vv"
update_motd="NO"
Is there something I'm doing wrong? I've spent so many hours testing and reading manuals i think i'm going in circles now. Any help with greatly appreciate.
 
For the combination ZFS and MySQL you really need to limit vfs.zfs.arc_max or else MySQL and ARC are going to battle for the same memory. You also need to configure a few ZFS settings to improve performance: https://wiki.freebsd.org/ZFSTuningGuide#MySQL

Thank you for the quick reply SirDice!

I've tried with:
/boot/loader.conf
vfs.zfs.arc_max="256M"

/usr/local/etc/mysql/my.cnf
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M
innodb_flush_log_at_trx_commit = 2
skip-innodb_doublewrite

ARC Total did not exceed 150M, RAM and SWAP still filled 100% and process was killed within 2 minutes. I haven't tried the Tweaks for ZFS, not sure how they work at this point. Obviously the ARC did not take all of that memory.
 
I haven't tried the Tweaks for ZFS, not sure how they work at this point.
The most important ones are the recordsizes, for the best results these need to match with what MySQL is using. You will also want to turn off atime as this will cause a bunch of useless writes (updating the last access timestamp). I really suggest following those settings.

While you mentioned ZFS you didn't mention what, are they mirrors? Single disk? Striped sets? Raid-Z? For databases I would recommend RAID-10 (striped set of mirrors) as that provides a nice balance between performance and fault-tolerance. For the VMs UFS might be a better option, especially with single disk set ups.

Also keep in mind that imports are a single process, regardless of how big the server or the database is. So they don't necessarily give you a good indication of the performance of the database. Fill the databases and run a set of standard queries. Measure how long those take. That will give you much better real world figures.
 
The most important ones are the recordsizes, for the best results these need to match with what MySQL is using. You will also want to turn off atime as this will cause a bunch of useless writes (updating the last access timestamp). I really suggest following those settings.

While you mentioned ZFS you didn't mention what, are they mirrors? Single disk? Striped sets? Raid-Z? For databases I would recommend RAID-10 (striped set of mirrors) as that provides a nice balance between performance and fault-tolerance. For the VMs UFS might be a better option, especially with single disk set ups.

Also keep in mind that imports are a single process, regardless of how big the server or the database is. So they don't necessarily give you a good indication of the performance of the database. Fill the databases and run a set of standard queries. Measure how long those take. That will give you much better real world figures.

You're absolutely correct. I forgot to mention how I'm using it. I should probably start at the beginning. A lot of my systems are running on ESXi, specially the ones i use for testing. I was using UFS on all VMs up until recently. I have a DB that has a logs table that's been growing very rapidly. Currently its about 5 GB, just the one table, around 8 GB total for the schema. I manage that table every day, keeping only 90 days of recent data. Rest is backed up else where. Performing searches on this table has gotten slow via our application. So I started looking to see if there is a way to speed things up. From my perspective of course, there are things application does that is not very efficient, like joining 2 biggest tables to retrieve a record set. I started benchmarking disk read writes. All servers are running RAID 6, 15K SAS Drives on PERC Controller. The DB Servers are running on the bare hardware, no virtualization. I only created few VMs with different OS's just for the testing purposes. I've noticed considerable difference in performance between Fedora XFS vs FreeBSD UFS. This is when i decided to install ZFS. In all instances I have configured ZFS as Stripe Single Drive. It was the first option in the configuration choices. When I benchmarked FreeBSD ZFS, it was totally on par with Fedora XFS in terms of read and wrote operations. I have also benchmarked slow queries, ZFS again was much faster then UFS, not as fast as XFS but reasonably close. So i decided to move forward and start reinstalling all test systems to use ZFS to see how they will perform. All was going well till i got to the DB server.

There are few tutorials on the net that I found how to configure MySQL on ZFS using appropriate datasets and zpool. I will give them a try to see if they would help. although I'm a bit skeptical at the moment, something tells me the problem is else where.
 
Performing searches on this table has gotten slow via our application.
Adjust your buffer pool. Also add indexes if you haven't done that already, especially when your tables get bigger and bigger adding proper indexes really speeds up the searches.

From my perspective of course, there are things application does that is not very efficient, like joining 2 biggest tables to retrieve a record set.
A long time ago I worked for a large oil company. They had a huge database containing all information and configurations for about 15000 routers and switches (several tables contained more than 8 million records). Running a scan to collect all that info took more than 8 hours. Just by tuning the database properly and the various queries from the scantool we managed to get it down to 4 hours. Then we optimized the scan process itself and got it further down to 2 hours. All while still collecting the same amount of information and running it all on the same server hardware.

Unfortunately, nowadays it's more common just to throw more hardware at it to increase performance. Nobody seems to be bothering with actually optimizing code and databases :(
 
Adjust your buffer pool. Also add indexes if you haven't done that already, especially when your tables get bigger and bigger adding proper indexes really speeds up the searches.

A long time ago I worked for a large oil company. They had a huge database containing all information and configurations for about 15000 routers and switches (several tables contained more than 8 million records). Running a scan to collect all that info took more than 8 hours. Just by tuning the database properly and the various queries from the scantool we managed to get it down to 4 hours. Then we optimized the scan process itself and got it further down to 2 hours. All while still collecting the same amount of information and running it all on the same server hardware.

Unfortunately, nowadays it's more common just to throw more hardware at it to increase performance. Nobody seems to be bothering with actually optimizing code and databases :(

The logs table i was talking about already has 17.4 Million Records. There are indexes. I'm not authorized to change the DB structure. Best i can do is make a request and have one of the programmers to take a look at it. Unfortunately it is low priority due to the fact that these searches are performed by Admins only. Will have to wait and see if when and if it gets looked at.

I did some more testing and there is some interesting results. Will post tomorrow some details. Seems that this problem only exist while using BSD 12.1 or 12.0. FreeBSD 11.3 works just fine, I'm monitoring performance via TOP tool. Seems that the bufferpool value in MySQL is totally ignored on BSD 12+. Will do some more testing to be sure.
 
Think you are hitting what I've hit but not found a real answer for - FreeBSD 12.x + MySQL 5.7 + importing millions of rows *of a certain layout* = dead machine (RAM, swap, OOM killer).


For now I'm staying with MySQL 5.6; long-term I might change the layout of the problematic table and make sure I've moved everything to InnoDB.

I can't remember all the details but my last posting in that thread looks like I could work around the import by changing the ENGINE=MyISAM; to ENGINE=InnoDB; for the problematic table. So might be worth a try to see if it helps in your case.

I could not find the pattern - there are many tables with millions or tens of millions of records in the databases I import and only one of them triggers the problem. So it's not just the millions of them - something about their size or factor X makes things go boom!

It does seem to affect Linux too, but the solutions there seem to be trying different memory allocators.
 
Think you are hitting what I've hit but not found a real answer for - FreeBSD 12.x + MySQL 5.7 + importing millions of rows *of a certain layout* = dead machine (RAM, swap, OOM killer).


For now I'm staying with MySQL 5.6; long-term I might change the layout of the problematic table and make sure I've moved everything to InnoDB.

I can't remember all the details but my last posting in that thread looks like I could work around the import by changing the ENGINE=MyISAM; to ENGINE=InnoDB; for the problematic table. So might be worth a try to see if it helps in your case.

I could not find the pattern - there are many tables with millions or tens of millions of records in the databases I import and only one of them triggers the problem. So it's not just the millions of them - something about their size or factor X makes things go boom!

It does seem to affect Linux too, but the solutions there seem to be trying different memory allocators.

Very interesting Richard. The symptoms are the same as I can see. However, I don’t have any tables using MyISAM engine. Everything is InnoDB. Another interesting fact is that I’m not able to reproduce this issue on several desktop computers I have here. Yet every Dell server I have at my disposal behaves the same. As you can see, I was not able to reproduce is on Linux either. Will do some more testing in the morning. Feel like I’m getting somewhere.
 
Test Server Dell T610 ESXi, FreeBSD 11.3-p5 VM, 1 CPU, 2GB RAM, 4 GB SWAP, ZFS, MySQL-5.7.2.9, reduced innodb_buffer_pool_size=256M the rest is default
Completed in 26 Minutes, RAM was in use about 95%, 0% of SWAP was used. I did not set any limits for the ARC, it was stable at around 1GB total.
It seems that innodb_buffer_pool_size is not working on BSD12+. MYSQLD will consume about 50GB of RAM using my 5GB import regardless what you put in innodb_buffer_pool_size. I will run the same test on BSD/UFS to see if ZFS is even involved here. Most of my servers have well over 100GB RAM, so i haven't really payed attention to this up until now.
 
So much for my MyISAM theory.

I've had what seems to be the same issue on a variety of desktop machines and Dell servers. ZFS hasn't been a factor at all, so will be interested to see how your testing goes.

On a variety of machines, server & desktop, ranging from 8G to 48G RAM:

FreeBSD 11.3 + MySQL 5.7 = fine
FreeBSD 12.0 or 12.1 + MySQL 5.7 = OOM

MySQL 5.6 is fine on both versions of FreeBSD.

It has definitely been reported on Linux and it's the same sort of thing - importing millions of rows into MySQL 5.7. And some people have got it to work by changing the memory allocator used (but confusingly some say *do* use jemalloc and others say they got it to work by *not* using jemalloc).

e.g. https://bugs.mysql.com/bug.php?id=83047

FreeBSD 12.x has a newer version of jemalloc than FreeBSD 11.x.

It seems to me that there is something about the import of millions of rows of a certain size or maybe combination of fields or field types. That's why most MySQL users don't encounter it - it's some corner case.

One thing to double-check as well - has the importing *definitely* worked? I've had some issues with max_allowed_packet - the MySQL client will crash on a table, but the import will continue on the next table. Might be a red herring so take this with a pinch of salt.

All the above is what I found but very interested to see if you find the same or something different (and if there's a solution that doesn't involve re-working the tables, even better!)
 
So much for my MyISAM theory.

I've had what seems to be the same issue on a variety of desktop machines and Dell servers. ZFS hasn't been a factor at all, so will be interested to see how your testing goes.

On a variety of machines, server & desktop, ranging from 8G to 48G RAM:

FreeBSD 11.3 + MySQL 5.7 = fine
FreeBSD 12.0 or 12.1 + MySQL 5.7 = OOM

MySQL 5.6 is fine on both versions of FreeBSD.

It has definitely been reported on Linux and it's the same sort of thing - importing millions of rows into MySQL 5.7. And some people have got it to work by changing the memory allocator used (but confusingly some say *do* use jemalloc and others say they got it to work by *not* using jemalloc).

e.g. https://bugs.mysql.com/bug.php?id=83047

FreeBSD 12.x has a newer version of jemalloc than FreeBSD 11.x.

It seems to me that there is something about the import of millions of rows of a certain size or maybe combination of fields or field types. That's why most MySQL users don't encounter it - it's some corner case.

One thing to double-check as well - has the importing *definitely* worked? I've had some issues with max_allowed_packet - the MySQL client will crash on a table, but the import will continue on the next table. Might be a red herring so take this with a pinch of salt.

All the above is what I found but very interested to see if you find the same or something different (and if there's a solution that doesn't involve re-working the tables, even better!)

I’m only speculating here but do you think there is a reason why FreeNAS and PFSense is still using FreeBSD 11.x?
 
So I followed the advice from SirDice. I've setup ZFS to use appropriate datasets and zpool per tutorial i found here:
https://www.patpro.net/blog/index.php/2014/03/09/2617-mysql-on-zfs-on-freebsd/

I ran more imports using my 5GB SQL Dump on this VM with few variations of settings.

Test Server Dell T610 ESXi, FreeBSD 12.1-p1 VM, 1 CPU, 2GB RAM, 4 GB SWAP, ZFS, MySQL-5.7.2.9

1st Test with additional settings below completed in 33 minutes, used about 66% of ram
vfs.zfs.arc_max="256M"
innodb_buffer_pool_size=128M
innodb_log_file_size=32M

2rd Test with additional settings below completed in 32 minutes, used about 66% of ram
vfs.zfs.arc_max="256M"
innodb_buffer_pool_size=256M
innodb_log_file_size=32M

3rd Test with additional settings below. DIED the first time i ran by consuming all the resources. Second time completed in 32 minutes, used about 66% of ram
vfs.zfs.arc_max="256M"
innodb_buffer_pool_size=256M
innodb_log_file_size=64M

4th Test with additional settings below completed in 31 minutes, used about 75% of ram
vfs.zfs.arc_max="256M"
innodb_buffer_pool_size=512M
innodb_log_file_size=48M

5th Test with additional settings below completed in 30 minutes, used about 88% of ram
vfs.zfs.arc_max="512M"
innodb_buffer_pool_size=512M
innodb_log_file_size=48M

5th Test with additional settings below completed in 30 minutes, used about 90% of ram
vfs.zfs.arc_max="512M"
innodb_buffer_pool_size=512M
innodb_log_file_size=64M

The results are a bit slower then what I've got with Linux or BSD 11.3 but the memory issue is gone.
 
Strange ... I'm definitely not using ZFS and converting the table from MyISAM to InnoDB seemed to "fix" it for me.

So maybe the same sort of memory issue (importing millions of rows) but if you encounter it with InnoDB on ZFS you can work around it by tweaking ZFS/InnoDB settings.
 
Strange ... I'm definitely not using ZFS and converting the table from MyISAM to InnoDB seemed to "fix" it for me.

So maybe the same sort of memory issue (importing millions of rows) but if you encounter it with InnoDB on ZFS you can work around it by tweaking ZFS/InnoDB settings.

By the way, that tutorial is a bit unclear about somethings. You cant create zroot/var/db/mysql without having to create zroot/var/db first. /var/db is not an empty folder if you were to mount ZFS pool to this location you would loose access to all of its contents. I think i messed things up the first time i tried it. I've tried to perform the same thing on BSD 11.3 this time to see if there is any difference in performance. This time I moved /var/db to /var/db-original and created basic pool zfs create zroot/var/db and moved /var/db-original to /var/db. Now you could follow the tutorial to complete the rest. I've also added to MySQL skip-innodb_doublewrite per tutorial and ran few more imports.

First import was with all default settings except for skip-innodb_doublewrite and innodb_buffer_pool_size=256M. It completed in 33 minutes. Just the same as it was on BSD 12.1 with the same tweaks. Not very impressing at all.

So i reverted it back. Removed all the pools and datasets but left skip-innodb_doublewrite and innodb_buffer_pool_size=256M as is. Ran another import. It completed in 24 minutes. A couple of minutes better then my previous to runs. Probably due to skip-innodb_doublewrite setting.

I ran another one but increased the innodb_buffer_pool_size=512M. The import completed in 23 minutes. At all time there was 300-400MB of free RAM.

So i thought to push it harder. I've limited the ARC to 512M and increased innodb_buffer_pool_size=1G. Import completed in 24 minutes. During import something dipped into SWAP from time to time, 1%. That didnt really help.

So tweaking ZFS helps the memory leakage but doesn't seem to help the performance. Tomorrow or Monday I will setup BSD 11.3/ZFS on a dedicated server with lots of RAM. Will limit MySQL to 50% RAM and 25% limit for the ARK. Fingers crossed for some better numbers.

I don't think at this point of the game i'm comfortable using BSD 12 in production. Will continue using it on the test servers.
 
#1
best guess is your performance issue may not be a performance issue .. It may be more of an efficacy or pool issue.. something more like fine-tuning the read/writes .. or a setting like vfs.zfs.dirty_data_max_max may be perfectly tuned for your total system memory.. but not tuned for the actual amount alloted to your arc. ZFS is awesome but it is very fussy and super literal about the finer details if you configure a read/write cash specifically .. then it will follow those setting // even if it means waiting an extra cycle or writing a cycle eairly. thus killing the idea of matching your blocks and record sizes to disk vs db.. keeping that 1:1 is a pretty big deal and 1.5 -1 or 1 - 1.5 .. will really slow down everything

many people really feel the need to tweek memory as its generally scarry when it top says its low, or been consumed ..zfs by default will consume 95% of your memory.. and thats usually a good thing ..

I would have a close look at calomels guide.. https://calomel.org/zfs_raid_speed_capacity.html go through the zfs config and have a look at the commented notes.. they have put a good amount of time into perfomance tuning.

#2
If your running freebsd on esxi you may want to take a look at something like this : https://b3n.org/vmware-vs-bhyve-performance-comparison/ (in short, esxi disk access sucks) perhaps it may be worth installing freebsd to metal then use bhyve with zfs support … create block devices vs using a dataset.
also allocate your memory for the DB via the VM.. that way you dont have your db and zfs trying to reserver the same memory pool.

#3
another thought was on your pool selection .. zraid2 may not be the best for a db .. you may get better performance with a striped mirror ie: no parity and an extra copy of the data to read from .. (or even just a mirrored set .. 1 disk mirrored several times for read performance) however I didnt see any workload requirements so I cant say what pool would be best for what job.

#4
are you using tools like bonnie++ / zfs-mon?

#5
it may also be worth going through the change logs for both freebsd and openzfs .. perhaps there was a change or known issue that would specifically impact your enviroment.

idk maybe one of these thoughs will help .. im not a dba by any stretch .. I do run a small db with zvols and it works really well .. having them configured for 8k blocks and the rest of the system on metal runs ashift12 gives me the best of both worlds .. again tho idk how that translates into 12m records :p
 
#1
best guess is your performance issue may not be a performance issue .. It may be more of an efficacy or pool issue.. something more like fine-tuning the read/writes .. or a setting like vfs.zfs.dirty_data_max_max may be perfectly tuned for your total system memory.. but not tuned for the actual amount alloted to your arc. ZFS is awesome but it is very fussy and super literal about the finer details if you configure a read/write cash specifically .. then it will follow those setting // even if it means waiting an extra cycle or writing a cycle eairly. thus killing the idea of matching your blocks and record sizes to disk vs db.. keeping that 1:1 is a pretty big deal and 1.5 -1 or 1 - 1.5 .. will really slow down everything
many people really feel the need to tweek memory as its generally scarry when it top says its low, or been consumed ..zfs by default will consume 95% of your memory.. and thats usually a good thing ..
I would have a close look at calomels guide.. https://calomel.org/zfs_raid_speed_capacity.html go through the zfs config and have a look at the commented notes.. they have put a good amount of time into perfomance tuning.

I'm very new to ZFS, still learning about it. The other day I made my first changes to ZFS per this tutorial. Not even sure i did it correctly. The default installation of ZFS comes with zroot pool and 11 locations to different places. I have created (/var/db, /var/db/mysql, /var/db/mysql-innodb, /var/db/mysql-innodb-logs) in zpool. Is that even correct? Should I have created new pool like zmysql for this? At the moment its unclear how the whole thing works. Not even sure what you mean by "1.5 -1 or 1 - 1.5"

By the way, ZFS was not comsuming all the memory, MYSQLD was. innodb_buffer_pool_size value in MySQL simply did not work until i created the datasets.

SirDice recommanded some settings from bsd wiki:
zfs set primarycache=metadata tank/db
zfs set atime=off tank/db
zfs set recordsize=16k tank/db/innodb
zfs set recordsize=128k tank/db/logs
zfs set zfs:zfs_nocacheflush = 1
zfs set sync=disabled tank/db

I coulnt figure how to set them.
Here is what i did:
zfs create zroot/var/db (created this one with just defaults because i coulnt the others without this parent)
zfs create -o recordsize=16k -o primarycache=metadata zroot/var/db/mysql-innodb
zfs create -o recordsize=128k -o primarycache=metadata zroot/var/db/mysql-innodb-logs
zfs create -o recordsize=8k zroot/var/db/mysql


#2
If your running freebsd on esxi you may want to take a look at something like this : https://b3n.org/vmware-vs-bhyve-performance-comparison/ (in short, esxi disk access sucks) perhaps it may be worth installing freebsd to metal then use bhyve with zfs support … create block devices vs using a dataset.
also allocate your memory for the DB via the VM.. that way you dont have your db and zfs trying to reserver the same memory pool.

Actually ESXi performance is not that bad. After I tweaked ZFS on ESXi it took 32 minutes to import 5GB schema. Today i added this tweak to a dedicated server and ran the same import. The memory usage was stable but it took to perform the same operation 1 hour and 10 minutes. I don't get it how does it run faster on a small VM?

#3
another thought was on your pool selection .. zraid2 may not be the best for a db .. you may get better performance with a striped mirror ie: no parity and an extra copy of the data to read from .. (or even just a mirrored set .. 1 disk mirrored several times for read performance) however I didnt see any workload requirements so I cant say what pool would be best for what job.

I'm actually not even using the RAID feature of ZFS. All my servers have Dell Perc H700 RAID Controller. Its configured with 6 15K SAS Drives in RAID6. So i just select the first option during installation with a single drive.

#4
are you using tools like bonnie++ / zfs-mon?

I'll have to checkout these tools.

#5
it may also be worth going through the change logs for both freebsd and openzfs .. perhaps there was a change or known issue that would specifically impact your enviroment.

This is one of the things on my list already. Haven't gotten to it.
 
I highly recomend NOT using any tutorials on zfs to get started .. go here : https://www.tiltedwindmillpress.com/product/fm-storage/ buy all 4 of the MWL’s file systems books .. 2 on ufs 2 on zfs. grab a giant cup of coffee and power through them over the weekend.. those books will give you an excellent base line to build from.

one key thing you listed
I'm actually not even using the RAID feature of ZFS. All my servers have Dell Perc H700 RAID Controller. Its configured with 6 15K SAS Drives in RAID6. So i just select the first option during installation with a single drive.

zfs vdevs should be directly attached.. via HBA .. or a raid controller in IT mode.. if you have a hardware raid under a zpool, your already suffering a perfomance hit before you even boot up. you cant disable the raid portion of zfs .. even if you only have a single drive pool.. that is considered a single volume stripe
 
I highly recomend NOT using any tutorials on zfs to get started .. go here : https://www.tiltedwindmillpress.com/product/fm-storage/ buy all 4 of the MWL’s file systems books .. 2 on ufs 2 on zfs. grab a giant cup of coffee and power through them over the weekend.. those books will give you an excellent base line to build from.

one key thing you listed


zfs vdevs should be directly attached.. via HBA .. or a raid controller in IT mode.. if you have a hardware raid under a zpool, your already suffering a perfomance hit before you even boot up. you cant disable the raid portion of zfs .. even if you only have a single drive pool.. that is considered a single volume stripe

Thank you Zader, I will probably have to do that at some point. No way doing this blind folded.
 
I am not database admin, but that in the "FreeBSD Mastery: ZFS" by Michael W Lucas, and "Introducing ZFS on Linux: Understand the Basics of Storage with ZFS Damian" Wojsław wrote that
"As with most file systems, ZFS suffers terrible performance penalty when filled up to 80% or more of its capacity. It is a common problem with file systems. Remember, when your pool starts filling to 80% of capacity, you
need to look at either expanding the pool or migrating to a bigger setup."
besides Michael W Lucas wrote for heavy IO load that UFS preffered than ZFS.
And 2GB RAM for ZFS - it's very little - when I start FreeBSD 11.3 /12.1 VM on my laptop with phisical 3GB RAM - FreeBSD swears when booting that there little memory than 4Gb, I’ll turn off some functions.
 
anytime, those 4 books will give you a good base line for building pools, types of hardware to use and pitfalls to avoid .. as noted above. zfs is right up there with toilet paper, sliced bread as far as inventions .. but its not for every work load.

the key here is the scope.. zfs needs more hardware .. and it only gets better the more resources it gets .. another key issue is pool design .. just having the right pool for the job with the right resources is huge..

just as a quick example ... I just got one of these .. its life is a file server so I used x12 12TB drives in zraid3 ... but for DB something like this would be a great choice.

https://www.theserverstore.com/Supe...0v3-64GB-12x-Trays-IT-MODE-Special_p_958.html
with 8 drives of your choice .. in a 4x4 stripped mirror. (ssd, rust, optaine etc)
4 more ssd/optaine drives (stripped mirror) for a zil/slog device or just 2 and use 1 as a hot spare and a boot device.
freebsd on metal

one key thing, in a baremetal stripe mirror for this workload.. dont enable mirror swap or disable it when you go into prod.

when you compare a ufs 12 drive system vs a similar zfs system.. the benefits of zfs are a no brainier.. but in a single drive .. or smaller environment.. processing on ufs and backing up to zfs is also a great solution.

good luck
 
Back
Top