[7.14] Ten-second event loop freezes on sqlite3.Connection.disconnect call

[kozlovsky]

This issue probably reveals an important reason for event loop freezes.

Sometimes, the event loop freezes at an innocuously-looking call of SQLite database's connection.close().

An example stack trace: https://sentry.tribler.org/organizations/tribler/issues/2752/events/c44a1c46f9bd4e599173eee3331d1646/

  File "core\components\metadata_store\remote_query_community\remote_query_community.py", line 343, in on_remote_select_response
  File "core\components\gigachannel\community\gigachannel_community.py", line 164, in on_packet_callback
  File "core\utilities\pony_utils.py", line 191, in __exit__
  File "Python38\lib\site-packages\pony\orm\core.py", line 467, in __exit__
  File "Python38\lib\site-packages\pony\orm\core.py", line 482, in _commit_or_rollback
  File "Python38\lib\site-packages\pony\orm\core.py", line 1831, in release
  File "Python38\lib\site-packages\pony\orm\core.py", line 1849, in close
  File "core\utilities\pony_utils.py", line 348, in release
  File "Python38\lib\site-packages\pony\orm\dbproviders\sqlite.py", line 701, in disconnect
  File "Python38\lib\site-packages\pony\orm\dbapiprovider.py", line 372, in disconnect

    def disconnect(pool):
        con = pool.con
        pool.con = None
        if con is not None: con.close()  # <-- line 372

At first glance, it seems surprising because the transaction commit has already occurred, and connection closing should be fast. But the pause is because the database is used in WAL journaling mode. In this mode, each transaction writes its changes not to the database directly but to the journal file; the changes are moved to the database file later, at the checkpoint time, or when the last open connection to the database is closing.

When the journal file is big, the checkpoint can be slow. It is also possible that some users store the Tribler state directory on a slow USB flash drive, and it slows down the checkpoint phase.

It looks like the default journal mode, DELETE, suits Tribler better. While the individual transactions can be a bit slower in this mode, it does not have the slow checkpoint phase, so using the default mode should eliminate the slow checkpoint phase that causes event loop freezes.

It is also possible to use the TRUNCATE journal mode, which works slightly faster than the DELETE journal mode.