If the MySql format data file is not on the main hard drive but a) on a USB 2.0 or 3.0 key, b) on another computer accessed by Wi-Fi or Ethernet, c) on a cloud server.
Would the MySql database format be faster than the compressed XML in any of the above scenarios, particularly when periodic automatic saves are kicking in.? If GnuCash is remotely driven from another computer via ssh or Remote desktop are there any significant differences between the data formats for those scenarios? David C On Sat, Jun 17, 2017 at 2:04 PM, John Ralls <jra...@ceridwen.us> wrote: > > > On Jun 17, 2017, at 10:22 AM, AC <gnuc...@acarver.net> wrote: > > > > On 2017-06-17 00:07, Colin Law wrote: > >> On 17 June 2017 at 02:31, AC <gnuc...@acarver.net> wrote: > >>> On 2017-06-16 09:35, Derek Atkins wrote: > >>>> Adam Funk <a24...@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 > > _______________________________________________ > gnucash-user mailing list > gnucash-user@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-user > ----- > Please remember to CC this list on all your replies. > You can do this by using Reply-To-List or Reply-All. > _______________________________________________ gnucash-user mailing list gnucash-user@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-user ----- Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
