4.05.23 Meeting Summary

• most likely the main issue is database locking as we don’t have control over events which participate in database writes/reads
• currently database is locked even when reading
• sqlite is not the issue, it is sqlcipher, as it limits open database connections to 1
• scanning rows in go is very slow in compared to other clients. Other people also complain on go perf even without sqlcipher on top  (https://github.com/mattn/go-sqlite3/issues/379 ; https://stackoverflow.com/questions/48000940/faster-sqlite-3-query-in-go-i-need-to-process-1million-rows-as-fast-as-possibl).
  • it needs to be confirmed by testing in separation, without status protocol running
• chats interactions performance depends on the content, it is noticeably slower if channel has only discord imported messages
• write locks are desired, because our logic relies on it, that is: operationA → saveChat → operationB → operationC → readChat
• how we interact with database is also important, we can’t just write in a loop to the database as it would block in any case
  • come up with generic throttling mechanism? avoid adding hacks/ad-hoc solutions all over the place
  • as example from the past: throttling archive protocol messages

Stuff that needs testing (not in order)

When starting testing one point, put your name next to it so two people don’t start on it at the same time

Write your findings below the point or create a new section below with the name of the test as section heading

• test with the DB set with no password (PRAGMA=0) @Patryk Osmaczko
  • I experimented with the application while the encryption was turned off. The application's performance improved significantly, but there were still some lags when it was pulling numerous messages from the storage nodes. The duration for switching channels varied, in "busy" mode it was taking on average 1-2 secs, with a maximum of around 10 secs, in "non-busy" mode it was instant.
  • Profiling: 2.5 minutes, switching channels, scrolling, looking for new messages. As far as I understand the data, the bottleneck lies in handleRetrievedMessages, where the cpu spends 10% of all cycles, most of its time is occupied by checkForDeletes and checkForDeletesForMes which aligns with the queries profiling I did. 13% of all cycles was occupied by sqlite3*, these were triggered most likely indirectly by handleRetrievedMessages, but that's a guess. Torrent-related stuff took around 7%. Rendering 17%, main event loop 15%.
• test if a db connection pool (with max 1 open connection each) would work with sqlcipher as described here (https://turriate.com/articles/making-sqlite-faster-in-go) @Alexandru Jbanca https://github.com/status-im/status-desktop/issues/10611
  • test if WAL works (https://turriate.com/articles/making-sqlite-faster-in-go).
  • The DB connection pool seems to work fine with sqlcipher. There is only one limitation: writing needs to be done on a dedicated connection to avoid database is locked or table is locked errors.
  • Task to implement the connection pool: https://github.com/status-im/status-desktop/issues/10723
• try adding indexes to the Discord tables
• try deleting the Discord tables (or at least the JOINs)
• at least on M1 macs most of the time is spent resetting the stack to 0 after sha1 compression. Removing this define speeds up the app and improves the CPU usage. @Alexandru Jbanca https://github.com/status-im/status-desktop/issues/10572

status-go/tomcrypt_custom.h at e7fbc191f4916f5801704ed9b6a06c738019a73e · status-im/status-go

• test the same queries that take a lot of time outside of status-go
  • first using sqlcypher directly
  • then using a basic go file to see if Go is the problem
• we found that without internet connection, it is faster, so it’s worth testing:
  • investigate if the wallet calls are abusing the DB
  • check if the waku messages hit the DB even for messages (topics) that we are not subbed to