11 ms·

ZFS is excellent for database development! Create a snapshot before you try something that mess up your data and instantly restore it! If your development datab

by olavgg 10y ago

ZFS is excellent for database development! Create a snapshot before you try something that mess up your data and instantly restore it! If your development database is a few gigabytes, this will save you a lot of time.

amelius 10y ago
Sounds great. Can you also duplicate versions of a database to coworkers on the same machine without much cost?
- olavgg 10y ago
  Yes, if they have ZFS too, it is basically snapshot, send & receive. Or you can clone it and rsync it to them. Do not try to snapshot a running database though. Before you execute the snapshot you should make sure everything is flushed to disk. For example with PostgreSQL you need to create a checkpoint before the snapshot, so you write: SELECT pg_start_backup('prepare_my_snapshot'); AND when the snapshot is done: SELECT pg_stop_backup();
  - db48x 10y ago
    It's not so critical as that. Any transactions in flight when you take the snapshot won't be finalized and will be discarded when you start it back up from the snapshot, but that won't affect the consistency of the database.
  - Bromskloss 10y ago
    > Do not try to snapshot a running database though. Before you execute the snapshot you should make sure everything is flushed to disk. Is a database (or anything else) really supposed to behave that way? Shouldn't every state along the way be possible to resume from? In particular, shouldn't database transactions take care of this?
    - amelius 10y ago
      If the snapshot is atomic as it should be, then yes.
    - shanemhansen 10y ago
      A database should handle a snapshot gracefully. I think you should only expect corruption if you are taking a non-atomic backup with something like tar.
    - majewsky 10y ago
      Yes, it is. One of the fundamental principles of practical RDBMS is durability (the D in https://en.wikipedia.org/wiki/ACID https://en.wikipedia.org/wiki/ACID), meaning that when the DB acknowledges a commit to the client, it must be guaranteed that the transaction has been reflected in permanent storage (i.e., on disk). The fine print: Due to the architecture of file system access in contemporary kernels (esp. file system caches), it's next to impossible for a user-space application to actually guarantee that writes are durable (i.e. synced to disk at a defined point in time), but most RDBMS manage well enough in practice.
      - cyphar 10y ago
        > The fine print: Due to the architecture of file system access in contemporary kernels (esp. file system caches), it's next to impossible for a user-space application to actually guarantee that writes are durable (i.e. synced to disk at a defined point in time), but most RDBMS manage well enough in practice. While this doesn't relate to a specific file, you can always look at the size of Dirty and Writeback in /proc/meminfo. They correspond to the amount of dirty file page caches that have yet to be synced.
    - hodgesrm 10y ago
      Maybe. RDBMS should be able to recover from any crash consistent copy of storage, where crash consistency means the storage represents a state of the system at run time. FS snapshots provide this property for single volumes. The problem is that production RDBMS tend to span multiple volumes, e.g., for logs vs. data to pick a simple case. FS snapshots in this case may not be consistent which would cause recovery to fail. AFAIK this is why even robust RDBMS like MS SQL server use mechanisms like VSS to ensure data are flushed, which guarantees not just crash consistency but application consistency.
  - xenophonf 10y ago
    It's too bad Unix/Linux doesn't offer something like Windows' VSS API, where the operating system can inform registered applications or services of an impending backup operation.
    - greedo 10y ago
      It's too bad that VSS is unreliable. If I had a nickel for everytime one of my backups failed due to a VSS writer issue, I'd own Symantec AND EMC. This has been across Win2k/2003/2008. We've been using 2012 for the last year (yeah, old stodgy corporations move slowly), and I haven't seen the same number of errors, but we're also not doing as many client based backups either.
      - olavgg 10y ago
        Have you guys thought about giving Windows ZFS volumes over iSCSI and then take ZFS snapshots? For example with SQL Server, it should be possible to talk to the SQL Writer Service and create a consistent database snapshot of a live SQL Server database with ZFS. When this snapshot is taken you can send & recv the diff to a remote backup server.
        greedo 10y ago
        Our experience with iSCSI hasn't been very good; its use is limited to some dev ESX systems. Our DBAs prefer to perform cold backups that we backup with traditional tools (Avamar).
        ryao 10y ago
        I believe that SoftNAS is able to do that when Windows is using Samba shares hosted on ZFS.
        leostrat 10y ago
        You're right. Here's more info about it: https://www.softnas.com/docs/softnas/v3/html-reference-guide/configuring_cifs_shares.html https://www.softnas.com/docs/softnas/v3/html-reference-guide...
  - ryao 10y ago
    If it is the same pool, you can just do a clone. Send/recv is completely unnecessary there. As for flushing the database first, it is unnecessary as long as the database is ACID compliant. If you use MySQL, you will probably want to freeze and flush the database first to be absolutely certain of safety. DDL statements and a few minor other things in MySQL are known not to be ACID compliant.
- ryao 10y ago
  Do a snapshot and clone. Then you will have an exact copy of the data mounted elsewhere that you can modify independently.
heisnotanalien 10y ago
Except you'll lose all the data since you created the snapshot... You've been able to do this for years on Linux with LVM anyway.
- raverbashing 10y ago
  > Except you'll lose all the data since you created the snapshot... Yes, but that's the point You're right about LVM though
- toolz 10y ago
  That's the point. If you mess up your db doing something you want to lose all the data since you created the snapshot. >You've been able to do this for years on Linux with LVM anyway. What's the point of saying this in that tone? I could achieve the same goal in a number of different ways - that doesn't make any of those methods invalid because another one exists.
- JustSomeNobody 10y ago
  I don't think you read the comment thoroughly. This is the point. Also, the discussion is centered around ZFS, there's no reason to bring up LVM. Everyone knows there's many ways to skin this cat and it doesn't make anyone appear any smarter because they know of one way.
  - username3 10y ago
    Bringing up alternatives is the norm around here. Some people don't know LVM.
  - heisnotanalien 10y ago
    My point is that it's not really a great way of 'backing' up a database because typically you want the transactions after you've made the change as well.
- ryao 10y ago
  The original poster should have advised people to clone the snapshot. Then you instantly have a copy elsewhere that you can use for experiments without disturbing the original.
heisnotanalien 10y ago
Except you'll lose all the data since you created the snapshot... You've been able to do this for years on Linux with LVM anyway.
_pmf_ 10y ago
Traditionally, DB engines avoid FS specific features like the plague.
- JustSomeNobody 10y ago
  That is why this works.
coldnebo 10y ago
ZFS is also great for teams that work on ginormous code projects. When I was a contractor at IBM in the 90's we had something similar so we could work on OS/2 source code. The entire build project was made up of hundreds of other sub projects and it was way too big to fit on a single developer machine of the time. Plus the reality was that individual developers only edited small portions of any part, even though they needed the whole to build and test. In later years, I've found myself thinking about ZFS for other big project scenarios. Like legacy enterprise websites that have gigs of static files and scripts that integrate with parts of webapps, etc. it's a pita to sync an 80gb mirror to every dev and a horrible waste of space. Much more efficient to simply mount the production mirror once and then store small edits locally. Note that these kind of legacy sites are usually not checked into source control because they are too big.
savedmybutt 10y ago
Absolutely. It's also good for production. This feature saved my butt once when I upgraded a postgresql 8.3 server to 8.4 on a Sun 4500 not realizing that the new server was compiled with a different timestamp format then the old one was (8 byte integers vs double precision floats). Thanks to ZFS I was able to trivially roll back the update.
- tkinom 10y ago
  That's what docker would help you do too, right? What's the pro/con of zfs compare to docker?
  - 0xCMP 10y ago
    ZFS is what the filesystem your volume would be using. You should not store ("commit" really) the database data in a Docker image.
  - zenlikethat 10y ago
    Usually data / persistence layer is not stored in Docker images directly. Frequently the data directory is marked in containers as a "volume" to bypass the usual CoW filesystem and write directly to the backing filesystem. So, it's uncommon to snapshot DBs directly in Docker and likely (this is pure speculation) 'zfs snapshot' is more efficient than 'docker commit' (on disk space usage and/or speed of snapshot) for this workload due to different use cases. I'd be really curious to see some actual numbers on this though.
    - j_s 10y ago
      Just to clarify if I'm understanding correctly here: the data itself is often outside Docker for performance and other reasons, but the DB software itself (PostgreSQL / MySQL / whatever) can still easily be within Docker.
      - zenlikethat 10y ago
        Sounds pretty correct, only nitpick is that the data is not "outside of Docker", but rather outside of the Docker image.
ryao 10y ago
It is better to do a clone and do experiments on that before doing anything to the production copy.
espadrine 10y ago
I have read that COW filesystems tend to perform terribly on HDD with database workloads because overwrites mid-file are common and increase external fragmentation. Is ZFS any better than Btrfs in this regard?
- johannes1234321 10y ago
  The answer to that is: use an SSD. With SSD random seek vs sequential access doesn't matter. Second part of the answer is that ZFS provides features for cache devices. So you get a fast disk as cache and a set of larger disks for as actual storage. All writes at first go to the cache device to be safe and then are written to actual storage, thanks to cow this can be done in larger sequential sequences. (Unless the disks become too full, ZFS has issues when disks are ~80% full, then it takes too much time to find free space) What's left are longer "sequential" read operations. There ZFS tries to predict the read, this can be successful, but might also happen that the seeks take too much time for highest performance.
mkhpalm 10y ago
True, but you can/could easily do that with LVM snapshots and merges too. We snapshot the databases (copy on write) which is instant, then if we want to roll it back we lvm merge and remount. Everything is pretty instant and it doesn't matter what filesystem you want to use.
- Freaky 10y ago
  Do LVM snapshots still cause massive performance degradation? https://www.nikhef.nl/~dennisvd/lvmcrap.html https://www.nikhef.nl/~dennisvd/lvmcrap.html
  - zxcvcxz 10y ago
    Not for me.
  - cmurf 10y ago
    Depends. Thick provisioned (conventional) LV snapshots take a hit. Thin provisioned LV snapshots take a much smaller hit, almost negligible.
  - mkhpalm 10y ago
    I think its wise to have a healthy housekeeping policy if you're going to use lvm snapshots. Any point in time copy on write snapshot is going to cause performance degradation. However, it really depends on the volume and you can't speak universally for everything/everybody. For example if you have a drive with 100 files but only write to one of them. (for simplicity) The first time you rewrite those blocks are going to endure the hit for the copy on write. (dual writes) After that first rewrite of all those blocks you can't really tell the difference that the snapshot exists. This obviously assumes that you don't have a ton of additional snapshots compounding writes..
dorfsmay 10y ago
Not ZFS, but filesystem snapshot technology in general. Ten years ago we were force to move from a NetApp (which included snapshot) filer to a SAN with no snapshot and that was our biggest impact. Database changes went from being minutes to hours because the DBAs now wanted to do full backups before any change.
ryao 10y ago
> ZFS is excellent for database development! Create a snapshot before you try something that mess up your data and instantly restore it! If your development database is a few gigabytes, this will save you a lot of time. The proper way of doing this is to clone the snapshot and then spin up a new container using the clone. That way you do not need to risk downtime on your production code. I believe that Delphix has a ZFS-based solution for doing this.
- mschuster91 10y ago
  > The proper way of doing this is to clone the snapshot Might not be really feasible. In order to create a consistent snapshot you have to stop the database server (or, if running a multi-master setup, take one master out of replication, but MM setups are rare and even more so is experience with them). Then, you have to actually do the clone - with a couple-GB-sized database, that's easy and fast but once you hit triple-digits GB sizes, you'll end up with a massive disk performance loss during the copy. Also, in case your DB upgrade went through, you have to (magically) apply it to the other servers because in the time between clone-begin, clone-end and db-upgrade-end data will have changed on the prod system... It's easier to make a short maintenance window, do a snapshot, hope for the best and if it blows up, restore service with a single click (by removing the maintenance page).
  - ryao 10y ago
    I think you are confused about ZFS clones. You never stop a production application for a clone because the clone is made from a snapshot. It also is copy on write, so you have no massive data copy. If your application, is not crash consistent, you do need to get it to pause, flush a consistent state and do the snapshot, but the snapshot takes less than a second to do, so such a thing is probably fine. Also, if you need to stop the database for a consistent snapshot, then your database is not ACID compliant.

by olavgg 10y ago

amelius 10y ago

Sounds great. Can you also duplicate versions of a database to coworkers on the same machine without much cost?

olavgg 10y ago
Yes, if they have ZFS too, it is basically snapshot, send & receive. Or you can clone it and rsync it to them. Do not try to snapshot a running database though. Before you execute the snapshot you should make sure everything is flushed to disk. For example with PostgreSQL you need to create a checkpoint before the snapshot, so you write: SELECT pg_start_backup('prepare_my_snapshot'); AND when the snapshot is done: SELECT pg_stop_backup();
- db48x 10y ago
  It's not so critical as that. Any transactions in flight when you take the snapshot won't be finalized and will be discarded when you start it back up from the snapshot, but that won't affect the consistency of the database.
- Bromskloss 10y ago
  > Do not try to snapshot a running database though. Before you execute the snapshot you should make sure everything is flushed to disk. Is a database (or anything else) really supposed to behave that way? Shouldn't every state along the way be possible to resume from? In particular, shouldn't database transactions take care of this?
  - amelius 10y ago
    If the snapshot is atomic as it should be, then yes.
  - shanemhansen 10y ago
    A database should handle a snapshot gracefully. I think you should only expect corruption if you are taking a non-atomic backup with something like tar.
  - majewsky 10y ago
    Yes, it is. One of the fundamental principles of practical RDBMS is durability (the D in https://en.wikipedia.org/wiki/ACID https://en.wikipedia.org/wiki/ACID), meaning that when the DB acknowledges a commit to the client, it must be guaranteed that the transaction has been reflected in permanent storage (i.e., on disk). The fine print: Due to the architecture of file system access in contemporary kernels (esp. file system caches), it's next to impossible for a user-space application to actually guarantee that writes are durable (i.e. synced to disk at a defined point in time), but most RDBMS manage well enough in practice.
    - cyphar 10y ago
      > The fine print: Due to the architecture of file system access in contemporary kernels (esp. file system caches), it's next to impossible for a user-space application to actually guarantee that writes are durable (i.e. synced to disk at a defined point in time), but most RDBMS manage well enough in practice. While this doesn't relate to a specific file, you can always look at the size of Dirty and Writeback in /proc/meminfo. They correspond to the amount of dirty file page caches that have yet to be synced.
  - hodgesrm 10y ago
    Maybe. RDBMS should be able to recover from any crash consistent copy of storage, where crash consistency means the storage represents a state of the system at run time. FS snapshots provide this property for single volumes. The problem is that production RDBMS tend to span multiple volumes, e.g., for logs vs. data to pick a simple case. FS snapshots in this case may not be consistent which would cause recovery to fail. AFAIK this is why even robust RDBMS like MS SQL server use mechanisms like VSS to ensure data are flushed, which guarantees not just crash consistency but application consistency.
- xenophonf 10y ago
  It's too bad Unix/Linux doesn't offer something like Windows' VSS API, where the operating system can inform registered applications or services of an impending backup operation.
  - greedo 10y ago
    It's too bad that VSS is unreliable. If I had a nickel for everytime one of my backups failed due to a VSS writer issue, I'd own Symantec AND EMC. This has been across Win2k/2003/2008. We've been using 2012 for the last year (yeah, old stodgy corporations move slowly), and I haven't seen the same number of errors, but we're also not doing as many client based backups either.
    - olavgg 10y ago
      Have you guys thought about giving Windows ZFS volumes over iSCSI and then take ZFS snapshots? For example with SQL Server, it should be possible to talk to the SQL Writer Service and create a consistent database snapshot of a live SQL Server database with ZFS. When this snapshot is taken you can send & recv the diff to a remote backup server.
      - greedo 10y ago
        Our experience with iSCSI hasn't been very good; its use is limited to some dev ESX systems. Our DBAs prefer to perform cold backups that we backup with traditional tools (Avamar).
      - ryao 10y ago
        I believe that SoftNAS is able to do that when Windows is using Samba shares hosted on ZFS.
        leostrat 10y ago
        You're right. Here's more info about it: https://www.softnas.com/docs/softnas/v3/html-reference-guide/configuring_cifs_shares.html https://www.softnas.com/docs/softnas/v3/html-reference-guide...
- ryao 10y ago
  If it is the same pool, you can just do a clone. Send/recv is completely unnecessary there. As for flushing the database first, it is unnecessary as long as the database is ACID compliant. If you use MySQL, you will probably want to freeze and flush the database first to be absolutely certain of safety. DDL statements and a few minor other things in MySQL are known not to be ACID compliant.
ryao 10y ago
Do a snapshot and clone. Then you will have an exact copy of the data mounted elsewhere that you can modify independently.

heisnotanalien 10y ago

Except you'll lose all the data since you created the snapshot... You've been able to do this for years on Linux with LVM anyway.

raverbashing 10y ago
> Except you'll lose all the data since you created the snapshot... Yes, but that's the point You're right about LVM though
toolz 10y ago
That's the point. If you mess up your db doing something you want to lose all the data since you created the snapshot. >You've been able to do this for years on Linux with LVM anyway. What's the point of saying this in that tone? I could achieve the same goal in a number of different ways - that doesn't make any of those methods invalid because another one exists.
JustSomeNobody 10y ago
I don't think you read the comment thoroughly. This is the point. Also, the discussion is centered around ZFS, there's no reason to bring up LVM. Everyone knows there's many ways to skin this cat and it doesn't make anyone appear any smarter because they know of one way.
- username3 10y ago
  Bringing up alternatives is the norm around here. Some people don't know LVM.
- heisnotanalien 10y ago
  My point is that it's not really a great way of 'backing' up a database because typically you want the transactions after you've made the change as well.
ryao 10y ago
The original poster should have advised people to clone the snapshot. Then you instantly have a copy elsewhere that you can use for experiments without disturbing the original.

heisnotanalien 10y ago

Except you'll lose all the data since you created the snapshot... You've been able to do this for years on Linux with LVM anyway.

_pmf_ 10y ago

Traditionally, DB engines avoid FS specific features like the plague.

JustSomeNobody 10y ago
That is why this works.

coldnebo 10y ago

ZFS is also great for teams that work on ginormous code projects. When I was a contractor at IBM in the 90's we had something similar so we could work on OS/2 source code. The entire build project was made up of hundreds of other sub projects and it was way too big to fit on a single developer machine of the time. Plus the reality was that individual developers only edited small portions of any part, even though they needed the whole to build and test. In later years, I've found myself thinking about ZFS for other big project scenarios. Like legacy enterprise websites that have gigs of static files and scripts that integrate with parts of webapps, etc. it's a pita to sync an 80gb mirror to every dev and a horrible waste of space. Much more efficient to simply mount the production mirror once and then store small edits locally. Note that these kind of legacy sites are usually not checked into source control because they are too big.

savedmybutt 10y ago

Absolutely. It's also good for production. This feature saved my butt once when I upgraded a postgresql 8.3 server to 8.4 on a Sun 4500 not realizing that the new server was compiled with a different timestamp format then the old one was (8 byte integers vs double precision floats). Thanks to ZFS I was able to trivially roll back the update.

tkinom 10y ago
That's what docker would help you do too, right? What's the pro/con of zfs compare to docker?
- 0xCMP 10y ago
  ZFS is what the filesystem your volume would be using. You should not store ("commit" really) the database data in a Docker image.
- zenlikethat 10y ago
  Usually data / persistence layer is not stored in Docker images directly. Frequently the data directory is marked in containers as a "volume" to bypass the usual CoW filesystem and write directly to the backing filesystem. So, it's uncommon to snapshot DBs directly in Docker and likely (this is pure speculation) 'zfs snapshot' is more efficient than 'docker commit' (on disk space usage and/or speed of snapshot) for this workload due to different use cases. I'd be really curious to see some actual numbers on this though.
  - j_s 10y ago
    Just to clarify if I'm understanding correctly here: the data itself is often outside Docker for performance and other reasons, but the DB software itself (PostgreSQL / MySQL / whatever) can still easily be within Docker.
    - zenlikethat 10y ago
      Sounds pretty correct, only nitpick is that the data is not "outside of Docker", but rather outside of the Docker image.

ryao 10y ago

It is better to do a clone and do experiments on that before doing anything to the production copy.

espadrine 10y ago

I have read that COW filesystems tend to perform terribly on HDD with database workloads because overwrites mid-file are common and increase external fragmentation. Is ZFS any better than Btrfs in this regard?

johannes1234321 10y ago
The answer to that is: use an SSD. With SSD random seek vs sequential access doesn't matter. Second part of the answer is that ZFS provides features for cache devices. So you get a fast disk as cache and a set of larger disks for as actual storage. All writes at first go to the cache device to be safe and then are written to actual storage, thanks to cow this can be done in larger sequential sequences. (Unless the disks become too full, ZFS has issues when disks are ~80% full, then it takes too much time to find free space) What's left are longer "sequential" read operations. There ZFS tries to predict the read, this can be successful, but might also happen that the seeks take too much time for highest performance.

mkhpalm 10y ago

True, but you can/could easily do that with LVM snapshots and merges too. We snapshot the databases (copy on write) which is instant, then if we want to roll it back we lvm merge and remount. Everything is pretty instant and it doesn't matter what filesystem you want to use.

Freaky 10y ago
Do LVM snapshots still cause massive performance degradation? https://www.nikhef.nl/~dennisvd/lvmcrap.html https://www.nikhef.nl/~dennisvd/lvmcrap.html
- zxcvcxz 10y ago
  Not for me.
- cmurf 10y ago
  Depends. Thick provisioned (conventional) LV snapshots take a hit. Thin provisioned LV snapshots take a much smaller hit, almost negligible.
- mkhpalm 10y ago
  I think its wise to have a healthy housekeeping policy if you're going to use lvm snapshots. Any point in time copy on write snapshot is going to cause performance degradation. However, it really depends on the volume and you can't speak universally for everything/everybody. For example if you have a drive with 100 files but only write to one of them. (for simplicity) The first time you rewrite those blocks are going to endure the hit for the copy on write. (dual writes) After that first rewrite of all those blocks you can't really tell the difference that the snapshot exists. This obviously assumes that you don't have a ton of additional snapshots compounding writes..

dorfsmay 10y ago

Not ZFS, but filesystem snapshot technology in general. Ten years ago we were force to move from a NetApp (which included snapshot) filer to a SAN with no snapshot and that was our biggest impact. Database changes went from being minutes to hours because the DBAs now wanted to do full backups before any change.

ryao 10y ago

> ZFS is excellent for database development! Create a snapshot before you try something that mess up your data and instantly restore it! If your development database is a few gigabytes, this will save you a lot of time. The proper way of doing this is to clone the snapshot and then spin up a new container using the clone. That way you do not need to risk downtime on your production code. I believe that Delphix has a ZFS-based solution for doing this.

mschuster91 10y ago
> The proper way of doing this is to clone the snapshot Might not be really feasible. In order to create a consistent snapshot you have to stop the database server (or, if running a multi-master setup, take one master out of replication, but MM setups are rare and even more so is experience with them). Then, you have to actually do the clone - with a couple-GB-sized database, that's easy and fast but once you hit triple-digits GB sizes, you'll end up with a massive disk performance loss during the copy. Also, in case your DB upgrade went through, you have to (magically) apply it to the other servers because in the time between clone-begin, clone-end and db-upgrade-end data will have changed on the prod system... It's easier to make a short maintenance window, do a snapshot, hope for the best and if it blows up, restore service with a single click (by removing the maintenance page).
- ryao 10y ago
  I think you are confused about ZFS clones. You never stop a production application for a clone because the clone is made from a snapshot. It also is copy on write, so you have no massive data copy. If your application, is not crash consistent, you do need to get it to pause, flush a consistent state and do the snapshot, but the snapshot takes less than a second to do, so such a thing is probably fine. Also, if you need to stop the database for a consistent snapshot, then your database is not ACID compliant.