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.
- Greg
_______________________________________________
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
