Gnucash file is getting long!

[AC]

On 2017-06-17 12:04, John Ralls wrote:
> 
>> On Jun 17, 2017, at 10:22 AM, AC <gnucash at acarver.net 
>> <mailto: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 
>>> <mailto:gnucash at acarver.net>> wrote:
>>>> On 2017-06-16 09:35, Derek Atkins wrote:
>>>>> Adam Funk <a24061 at ducksburg.com <mailto: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.

Well ok, it makes many insert/update/appends and then the transaction
causes a pile of writes.  The disk I/O is still the bottleneck for most
applications.


> 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.


No, that's the point of me bringing it up.  MySQL compression is
transparent.  It's done server side and has no bearing on the
applications accessing the data.  You configure the server to support
compressed tables then create the tables.  There's an option to allow
compression on a table-by-table basis or to simply enable it for all
tables by default.  GC would have no idea the tables were compressed.