Gnucash file is getting long!

[AC]

On 2017-06-17 00:07, Colin Law wrote:
> On 17 June 2017 at 02:31, AC <gnucash at acarver.net> wrote:
>> On 2017-06-16 09:35, Derek Atkins wrote:
>>> Adam Funk <a24061 at ducksburg.com> writes:
>>>
>>>>> Not necessarily.  The "default" backend would be SQLite, which is a DB
>>>>> that stores into a single file.  So it will act like the current XML
>>>>> backend in terms of storage, but not necessarily the same with backup
>>>>> files.  However no server is required.
>>>>
>>>> Great!  Thanks to you & Colin for that information.
>>>
>>> Also keep in mind that the mysql data isn't compressed, so your disk
>>> space usage will grow significantly when using a SQL backend vs the
>>> (compressed) XML.
>>>
>>
>> You can enable compression in MySQL 5.5.  This applies to InnoDB table
>> types using a file per table and the Barracuda file format.  This
>> configuration must be enabled before the tables are created.
> 
> Given the small size (in MySQL terms) of a GC database and the complex
> nature of some of the queries I suggest we would be better to accept a
> larger database and (presumably) quicker access. Though I suppose if
> the db indices are arranged appropriately the overhead may be small.
> 
> Colin

The disk speed is going to be the major performance driver even if GC
were running fully transactional rather than as the full table load.
For a modern processor and the size of the GC tables and indicies it's
really only going to be maybe a 5% slowdown in the actual transaction
within the CPU but disk write is always going to be much slower in wall
clock time.  However, there would likely be a recovery of that speed
when it's only having to handle transactions rather than manipulating a
massive data structure in memory and pushing all that to the database at
once.  In transaction mode with compression, the order of operations is
compression in memory then disk write so there's less data to write to
disk.  The loss in speed from the compression process would be made up
by the reduction in wait time for the disk write.

There's lots of documentation and tests of MySQL's compression
performance especially in the later versions (after 5.7 where some code
optimizations were made).  The savings they report in space is about 40%
compression on average which isn't bad for a minor speed bump.