> On Sep 5, 2020, at 11:22 AM, Greg Ingram <ing...@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
_______________________________________________
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
