SQLite time-consuming feed update (2.12 warning)

by mathew7, Wednesday, September 17, 2014, 16:27 (3502 days ago)

Hello,

I have just upgraded from 2.9 to 2.12 and noticed the feed update to be very slow.
On my Samsung 830 SSD, I saw 100% disk usage with 500 write requests with 2ms average time (=1s) every second.

Tracking with DEBUG option, I got the following at the end (note: 4000 entries and 7MB database on SSD):
09-17-14 13:55:46 Debug cleanup orphaned and old items
09-17-14 13:55:46 Debug cleanup orphaned and old items finished
09-17-14 13:55:46 Debug delete orphaned thumbnails
09-17-14 13:55:46 Debug delete orphaned thumbnails finished
09-17-14 13:55:46 Debug delete orphaned icons
09-17-14 13:55:46 Debug delete orphaned icons finished
09-17-14 13:55:46 Debug optimize database
09-17-14 13:58:14 Debug optimize database finished

That is 2'30" of normal update which I run hourly. During this time, no other selfoss page is server (browser just waits).

I have commented the body of optimize function for myself (daos/sqlite/Database.php) since it was empty in 2.9.

But since it takes so long for my case, I don't think it's a good idea to use it this way, especially since people who actually need this optimization will be impacted even worse.

Note: my DB has shrunk with this code from 10M (2.9 backup) to 7MB after about a year.