4 ms·

When delete or update records, the database engine basically marks the space as available, then the next record will be filled in. Because the file system only

by docuru 8y ago

When delete or update records, the database engine basically marks the space as available, then the next record will be filled in. Because the file system only allows append or update content on a file. To release the disk space, it will need to completely rewrite the whole data file, which will cause much more things to handle and time. So it is not a way to design a database engine

twa927 8y ago
Yep, you're describing a typical design of a DB. I'm looking for an alternative design. I'm guessing it could use multiple small files (a few MBs) and merge them to avoid fragmentation.
- docuru 7y ago
  What is the use case that you need the database for? I'm interested to know more. (Sorry I didn't get notice from HN so I don't know someone left a comment)
- zzzcpan 8y ago
  Maybe check out TokuDB, I haven't tried it myself, but it has things like tokudb_cleaner_iterations [1] and tokudb_cleaner_period. There is also MyRocks based on RocksDB fork of LevelDB, but LevelDB is piece of garbage, not sure about how much RocksDB fork fixes it though. [1] https://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_variables.html#tokudb_cleaner_iterations https://www.percona.com/doc/percona-server/5.6/tokudb/tokudb...
  - zepearl 8y ago
    TokuDB won't release the allocated blocks (it will reorganize its internal blocks to achieve max compression but as far as I know and saw won't release what it has allocated previously, but I might be wrong :) ).
    - twa927 8y ago
      And it looks like TokuDB is no longer developed?
      - zepearl 8y ago
        Noooooo, please tell me that it's not true - it's the foundation of many of my experiments delivering huge performance and flexibility compared to anything else...! Where did you read/hear it?
        zepearl 8y ago
        TokuDB still active - last bugfix was on 29.Nov.2018: https://www.percona.com/doc/percona-server/5.6/release-notes/Percona-Server-5.6.42-84.2.html https://www.percona.com/doc/percona-server/5.6/release-notes... List of which TokuDB version is integrated in which MariaDB version: https://mariadb.com/kb/en/meta/tokudb-versions/ https://mariadb.com/kb/en/meta/tokudb-versions/
    - zepearl 8y ago
      Sooooooooorry, I have to take it back - just saw now that one of my Tokudb shrank from 5'554'252 to 5'423'180 KiB. Still please don't expect big shrinks with TokuDB when having big variance of active data as it's not its first priority (first prio is to be fast and/or use little space).
zzzcpan 8y ago
A database storage engine, a decent one by modern standards at least, has to do some things in background and rewrite the data continuously either way. Log structured with all the scrubbing, merging, compression, optimizations, etc. naturally allows for a tunable unused space pressure, merging/moving things from tail to reuse the space as aggressively as necessary. And if the log is spread across many files, it's even possible to release unused files to the filesystem by unlinking them, although this is not really important for DELETEs, only for shrinking, scaling down. So, it's definitely a good way to design a databases storage engine. But there isn't one open source as far as I know. There are some better than default choices though.

by docuru 8y ago

twa927 8y ago

Yep, you're describing a typical design of a DB. I'm looking for an alternative design. I'm guessing it could use multiple small files (a few MBs) and merge them to avoid fragmentation.

docuru 7y ago
What is the use case that you need the database for? I'm interested to know more. (Sorry I didn't get notice from HN so I don't know someone left a comment)
zzzcpan 8y ago
Maybe check out TokuDB, I haven't tried it myself, but it has things like tokudb_cleaner_iterations [1] and tokudb_cleaner_period. There is also MyRocks based on RocksDB fork of LevelDB, but LevelDB is piece of garbage, not sure about how much RocksDB fork fixes it though. [1] https://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_variables.html#tokudb_cleaner_iterations https://www.percona.com/doc/percona-server/5.6/tokudb/tokudb...
- zepearl 8y ago
  TokuDB won't release the allocated blocks (it will reorganize its internal blocks to achieve max compression but as far as I know and saw won't release what it has allocated previously, but I might be wrong :) ).
  - twa927 8y ago
    And it looks like TokuDB is no longer developed?
    - zepearl 8y ago
      Noooooo, please tell me that it's not true - it's the foundation of many of my experiments delivering huge performance and flexibility compared to anything else...! Where did you read/hear it?
      - zepearl 8y ago
        TokuDB still active - last bugfix was on 29.Nov.2018: https://www.percona.com/doc/percona-server/5.6/release-notes/Percona-Server-5.6.42-84.2.html https://www.percona.com/doc/percona-server/5.6/release-notes... List of which TokuDB version is integrated in which MariaDB version: https://mariadb.com/kb/en/meta/tokudb-versions/ https://mariadb.com/kb/en/meta/tokudb-versions/
  - zepearl 8y ago
    Sooooooooorry, I have to take it back - just saw now that one of my Tokudb shrank from 5'554'252 to 5'423'180 KiB. Still please don't expect big shrinks with TokuDB when having big variance of active data as it's not its first priority (first prio is to be fast and/or use little space).

zzzcpan 8y ago

A database storage engine, a decent one by modern standards at least, has to do some things in background and rewrite the data continuously either way. Log structured with all the scrubbing, merging, compression, optimizations, etc. naturally allows for a tunable unused space pressure, merging/moving things from tail to reuse the space as aggressively as necessary. And if the log is spread across many files, it's even possible to release unused files to the filesystem by unlinking them, although this is not really important for DELETEs, only for shrinking, scaling down. So, it's definitely a good way to design a databases storage engine. But there isn't one open source as far as I know. There are some better than default choices though.