[GNC] XML vs MySQL Data Storage

Thomas Forrester tlforrester at gmail.com
Fri Sep 17 13:12:55 EDT 2021


Very good explanation, Derek.  Seems my choice of MySQL was the
best decision for me, and you've reminded me of some of the more subtle
reasons.  I'll keep things as they are.

Appreciate the help!
- Tom

On Fri, Sep 17, 2021 at 11:39 AM Derek Atkins <derek at ihtfp.com> wrote:

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

-- 
Tom
Thomas L. Forrester
3211 Patty Lane
Middleton, WI 53562-1652 USA
608-831-0769


More information about the gnucash-user mailing list