Gnucash file is getting long!

[John Ralls]

> On Jun 17, 2017, at 10:22 AM, AC <gnucash at acarver.net> wrote:
> 
> 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.

It doesn’t “push all that to the database”. It runs update or insert queries for the tables in question, very stupidly. For example, adding a GnuCash transaction will do one insert for the transaction record, one for each slot in the transaction, one for each split, and one for each slot in each split.
I don’t remember offhand how many slots normal transactions and splits have, but it isn’t a lot. The whole mess is wrapped in a database transaction.

GnuCash uses generic SQL commands that work on all 3 supported databases. That would have to change in order for it to support MySql compression. GnuCash would also need a way to determine that the server is correctly configured. Neither is likely to get implemented any time soon.

Regards,
John Ralls