6 ms·

Which aspect in particular do you wish to emulate from TokuDB in PostgreSQL?

ak4g 11y ago
That compression ratio is one I'd love to have. PG's built-in data compression is limited to large values in a single row - if you have a hundred billion 100-byte tuples, TOAST isn't going to help. The closest thing I know of to achieve something comparable with PG would be to use a data directory mounted on something with transparent filesystem-level compression (meaning, practically speaking, ZFS). This gives you the less-than-ideal choice of a not-mainline-linux filesystem (for your database's data directory, which is worth being nervous about) or running an OpenSolaris descendant, which is a big departure for plenty of people who have only ever run production dbs on Linux servers.
- brobinson 11y ago
  I've used PostgreSQL with BTRFS + LZO compression on Linux. After I set it up, our large queries (2 minutes or > runtime) saw a speedup of 5-10x and our TPS went up by around 25%. BTRFS has been in the stable kernel for over two years, so it might be worth checking out!
  - agopaul 11y ago
    Mechanical or flash storage? Compression could give advantage on mechanical disks due to the reduced time spent on seeking, but I'm curious to know how they perform on SSD for which seek time is reduced to a minimum if not 0.
    - brobinson 11y ago
      It was 4 SSDs in a RAID-0 configuration.
- dmpk2k 11y ago
  On a tangent: I think it's rather sad that many nerds aren't prepared to try a different UNIX, when we're talking the lifeblood of their business: data. ZFS is the only filesystem it's reasonable to trust critical data on right now (I can't think of any other OSS self-validating Merkle trees that have been hammered in production for nearly a decade...), yet somehow some minor differences in Unicies trumps that. I'm going to make a bold claim I know will stir the nest, but I feel confident in making given all the bad shit most filesystems miss: if you're not running your OSS RDBMS on ZFS right now, and you don't have compelling specialized needs to explain why not, you shouldn't be let near a production DB due to plain negligence and/or incompetence.
  - AlisdairO 11y ago
    A good DBMS includes a bunch of integrity-preserving measures to deal with data corruption. No reason not to run your DBMS on ZFS as far as I'm aware (edit: at least if the workload does not trigger excessive fragmentation), but I'd reserve the word 'negligence' for, say, not using an appropriate backup solution.
  - jzwinck 11y ago
    Something funny about this: where are the OSS RDBMSs which do not rely on a general purpose filesystem? Because in the commercial RDBMS space that is a thing.
    - kbenson 11y ago
      My impression, wrong or not, was always that those were features from when a good volume manager could not be counted on in the host system.
  - kbenson 11y ago
    It depends on how you slice it. If your expertise in this system is less than your others, it could cause real problems down the line. If a mistake was made in the setup, maintenance or recovery of the system because it's different, it would be just as easy to say it was negligent to use it instead of something that was well understood by your team. Trade-offs like this are made all the time, to claim negligence is to ignore the complexities of the situation.

ak4g 11y ago

That compression ratio is one I'd love to have. PG's built-in data compression is limited to large values in a single row - if you have a hundred billion 100-byte tuples, TOAST isn't going to help. The closest thing I know of to achieve something comparable with PG would be to use a data directory mounted on something with transparent filesystem-level compression (meaning, practically speaking, ZFS). This gives you the less-than-ideal choice of a not-mainline-linux filesystem (for your database's data directory, which is worth being nervous about) or running an OpenSolaris descendant, which is a big departure for plenty of people who have only ever run production dbs on Linux servers.

brobinson 11y ago
I've used PostgreSQL with BTRFS + LZO compression on Linux. After I set it up, our large queries (2 minutes or > runtime) saw a speedup of 5-10x and our TPS went up by around 25%. BTRFS has been in the stable kernel for over two years, so it might be worth checking out!
- agopaul 11y ago
  Mechanical or flash storage? Compression could give advantage on mechanical disks due to the reduced time spent on seeking, but I'm curious to know how they perform on SSD for which seek time is reduced to a minimum if not 0.
  - brobinson 11y ago
    It was 4 SSDs in a RAID-0 configuration.
dmpk2k 11y ago
On a tangent: I think it's rather sad that many nerds aren't prepared to try a different UNIX, when we're talking the lifeblood of their business: data. ZFS is the only filesystem it's reasonable to trust critical data on right now (I can't think of any other OSS self-validating Merkle trees that have been hammered in production for nearly a decade...), yet somehow some minor differences in Unicies trumps that. I'm going to make a bold claim I know will stir the nest, but I feel confident in making given all the bad shit most filesystems miss: if you're not running your OSS RDBMS on ZFS right now, and you don't have compelling specialized needs to explain why not, you shouldn't be let near a production DB due to plain negligence and/or incompetence.
- AlisdairO 11y ago
  A good DBMS includes a bunch of integrity-preserving measures to deal with data corruption. No reason not to run your DBMS on ZFS as far as I'm aware (edit: at least if the workload does not trigger excessive fragmentation), but I'd reserve the word 'negligence' for, say, not using an appropriate backup solution.
- jzwinck 11y ago
  Something funny about this: where are the OSS RDBMSs which do not rely on a general purpose filesystem? Because in the commercial RDBMS space that is a thing.
  - kbenson 11y ago
    My impression, wrong or not, was always that those were features from when a good volume manager could not be counted on in the host system.
- kbenson 11y ago
  It depends on how you slice it. If your expertise in this system is less than your others, it could cause real problems down the line. If a mistake was made in the setup, maintenance or recovery of the system because it's different, it would be just as easy to say it was negligent to use it instead of something that was well understood by your team. Trade-offs like this are made all the time, to claim negligence is to ignore the complexities of the situation.