[GNC-dev] Save as Postgres

[Greg Ingram]

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