[GNC] XML vs MySQL Data Storage

[Derek Atkins]

Hi,

On Fri, September 17, 2021 12:21 pm, Thomas Forrester wrote:
> I've been using GnuCash for several years now, and almost right from the
> start I made the decision to use MySQL as the database engine.  Other that
> forgetting to start MySQL first now and then, it's working fine, and the
> ability to easily schedule automated backups is nice.
>
> I am assuming that the program gains some optimization by using a
> professional RDBMS as its data engine.  But is it?  I know that XML is the
> native data storage type for GnuCash, but that's really just a text file
> format without any back end optimization.
>
> My question is:  With an ever-increasing data load, am I correct in
> assuming that performance is going to take in increasingly large hit over
> MySQL?  Would there be any reason at all to move back to XML over MySQL?

At this point, the only real benefit of using a SQL backend is that
commits go into storage immediately instead of waiting for you to save
them.  Beyond that, GnuCash generally doesn't differentiate between them. 
Specifically, GnuCash always loads the whole dataset into RAM when the
application starts.  It does this regardless of whether it's SQL or XML. 
In this case, you may find that as the dataset size increases, SQL may
"load" slower, but you will be able to exit faster (because saves are
incremental).

On the roadmap is a plan to change to full SQL, so GnuCash would be a true
SQL DB application and (hopefully) not have to read the whole dataset into
RAM.  For XML it would load the data into an in-RAM SQLite image, but
MySQL would just be used directly.

Whether to move from SQL back to XML is up to you, of course.

Hope this helps!

> Thanks!
> --
> Tom
> Thomas L. Forrester
> Middleton, WI  USA

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek

-- 
       Derek Atkins                 617-623-3745
       derek at ihtfp.com             www.ihtfp.com
       Computer and Internet Security Consultant