[GNC-dev] Save as Postgres

John Ralls jralls at ceridwen.us
Sat Sep 5 14:49:55 EDT 2020



> On Sep 5, 2020, at 11:22 AM, Greg Ingram <ingram at symsys.com> wrote:
> 
> On 9/5/20 10:41 AM, John Ralls wrote:
>> Thanks for the feedback, but please remember to copy the list on all replies.
> 
> Sorry about that.  Your message arrived just as I was looking for mine and I realized I hadn't replied to the list.
>> Running GnuCash from the command line with --log gnc.backend.sql=debug --log gnc.backend.dbi=debug might provide some insight into what's going on (the output will be in the trace file), but if that's too much trouble I understand completely.
> 
> If it would be of real help, I'll run it again and find that trace file.
> 
> In the meantime, it finished! 
> 
> Somewhere between the 6 and 8 hour marks, it created the database and tables but there were no rows yet in accounts, transactions, or splits. GnuCash was still working on the task but using more like 5% of a CPU. I set little bash while loop running "select current_time, (select count(*) from accounts) as accounts, (select count(*) from transactions) as transactions, (select count(*) from splits) as splits" once a minute. After about 10.5 hours of human time and nearly 8 hours of CPU time, I got these consecutive query results:
> 
>        timetz       | accounts | transactions | splits 
> --------------------+----------+--------------+--------
>  10:30:30.446848-05 |        0 |            0 |      0
> (1 row)
> 
>        timetz       | accounts | transactions | splits 
> --------------------+----------+--------------+--------
>  10:31:30.571612-05 |    10223 |        51730 | 162784
> (1 row)
> 
> So when it finally started writing rows, it got them all done within one minute. I hadn't expect that. FWIW, in this case the server is remote. 
> 
> I ran down the trace file and here are some lines:
> 
> * 00:16:35  WARN <gnc.pricedb> [add_price()] no commodity
> * 00:16:35  WARN <gnc.pricedb> [add_price()] no commodity
> * 07:58:01 ERROR <> secret_password_store_sync: assertion 'password != NULL' failed
> * 07:58:01 ERROR <Gtk> gtk_widget_event: assertion 'WIDGET_REALIZED_FOR_EVENT (widget, event)' failed
> * 07:58:01 ERROR <Gtk> gtk_widget_event: assertion 'WIDGET_REALIZED_FOR_EVENT (widget, event)' failed
> * 10:30:35 ERROR <gnc.backend.dbi> void GncDbiSqlConnection::unlock_database(): assertion 'dbi_conn_error       (m_conn, nullptr) == 0' failed
> 
> During the time between 00:16 and 07:58  is when GnuCash pegged one of the CPU's. Then for ~2.5 hours it was more like 5% CPU. Of course, for both periods I didn't monitor continuously. If I do the whole thing again, I'll run top in batch mode for just the gnucash process and collect that data too.
> 
> Since it finished, I think I'll run with PostgreSQL for awhile after all. The lag when recording / committing a new or changed transaction was very noticeable with SQLite3 and seemed to be getting progressively worse. I've only played with a couple of transactions now in PostgreSQL but it seems snappier. That could just be my optimism. 
> 
> And BTW, does GnuCash ever issue VACUUM commands? I ask because it doesn't seem like it does so routinely. I've used a SQLite3 DB where I then deleted a lot of transactions - like a year's worth - to recreate a snapshot for a prior year. It would delete a LOT of transactions. I then noticed that the file size didn't change, or not by much, and while I don't know how SQLite3 manages its file, I can imagine how a sparser file might be less efficient. Kind of like a fragmented disk. When I would run a VACUUM command on the file, it would shrink. 
> 
> It's the sort of thing that got me wondering if PostgreSQL would be better at handling my data.

I could only speculate about what those timed results mean or what GnuCash was doing for 10 hours. I'll resist the temptation.

No, GnuCash doesn't ever run vacuum (or optimize-table in MySQL). In general GnuCash tables only get bigger so it would very seldom do anything.

Regards,
John Ralls


More information about the gnucash-devel mailing list