[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