starred/shiori

Fork 0

mirror of https://github.com/go-shiori/shiori.git synced 2026-04-25 06:25:54 +03:00

[GH-ISSUE #408] Performance regression #258

New issue

Closed

opened 2026-02-25 23:33:48 +03:00 by kerem · 6 comments

kerem commented

2026-02-25 23:33:48 +03:00

Owner

Originally created by @jhe2 on GitHub (Mar 22, 2022).
Original GitHub issue: https://github.com/go-shiori/shiori/issues/408

I recently updated Shiori from the old 1.5.0 release to the latest version and noticed a substantial performance regression. I'm using sqlite for the database (with around 4500 bookmarks) and the performance was perfectly fine with version 1.5.0.

When running the latest code (which includes commit sqlite: remove unneeded LEFT JOIN to improve performance ), just loading the bookmarks list after logging in takes about 20 seconds (and causes very high cpu load on the server during those 20 seconds). Switching to the next page takes similarily long. In comparison, on version 1.5.0 the same thing took less than a second.

I noticed that migrating the database to FTS5 caused its size to almost double (395 MB vs. 202 MB). I don't know if this is related in any way, but I thought it might be worth mentioning.

Originally created by @jhe2 on GitHub (Mar 22, 2022). Original GitHub issue: https://github.com/go-shiori/shiori/issues/408 I recently updated Shiori from the old 1.5.0 release to the latest version and noticed a substantial performance regression. I'm using sqlite for the database (with around 4500 bookmarks) and the performance was perfectly fine with version 1.5.0. When running the latest code (which includes commit [sqlite: remove unneeded LEFT JOIN to improve performance](https://github.com/go-shiori/shiori/commit/ca3cc11b2de4d894e5fee3e1deea86ccfb541093) ), just loading the bookmarks list after logging in takes about 20 seconds (and causes very high cpu load on the server during those 20 seconds). Switching to the next page takes similarily long. In comparison, on version 1.5.0 the same thing took less than a second. I noticed that migrating the database to FTS5 caused its size to almost double (395 MB vs. 202 MB). I don't know if this is related in any way, but I thought it might be worth mentioning.

kerem

2026-02-25 23:33:48 +03:00

closed this issue
added the
type:bug

component:backend

database:sqlite

priority:high
labels

kerem commented

2026-02-25 23:33:48 +03:00

Author

Owner

@Mati20187 commented on GitHub (Mar 29, 2022):

I don't have a comparison as I haven't used previous versions but having an option to set entry limit per page would likely significantly improve loading time.

I haven't yet reached the number of bookmarks that would create a 2nd page but when I refresh the page (with F5, not "refresh storage"), it takes about 2 seconds for the bookmarks to load (on LAN).
I don't know about you @jhe2 but I mainly go by tags, I don't really scroll through the homepage to find something, so being able to drastically limit entry limit per page (or even having an option to create a homepage where one can pin the most important links) would be very beneficial.

I use Miniflux for reading RSS (also written in go but uses PostgreSQL as the database) and even with 100 entries per page, the load times are extremely good. Maybe SQLite is the culprit... As soon as shiori + postgresql combination becomes usable (database error when adding tags: #215 ) I'll do a comparison.

I noticed that migrating the database to FTS5 caused its size to almost double

I don't have experience with SQLite but in postgresql there's an option to vacuum the db in order to free up space taken by "empty slots" remaining after deleted entries. If there's something like that in SQLite, give it a try.

@Mati20187 commented on GitHub (Mar 29, 2022): I don't have a comparison as I haven't used previous versions but having an option to set entry limit per page would likely significantly improve loading time. I haven't yet reached the number of bookmarks that would create a 2nd page but when I refresh the page (with F5, not "refresh storage"), it takes about 2 seconds for the bookmarks to load (on LAN). I don't know about you @jhe2 but I mainly go by tags, I don't really scroll through the homepage to find something, so being able to drastically limit entry limit per page (or even having an option to create a homepage where one can pin the most important links) would be very beneficial. I use Miniflux for reading RSS (also written in go but uses PostgreSQL as the database) and even with 100 entries per page, the load times are extremely good. Maybe SQLite is the culprit... As soon as shiori + postgresql combination becomes usable (database error when adding tags: #215 ) I'll do a comparison. > I noticed that migrating the database to FTS5 caused its size to almost double I don't have experience with SQLite but in postgresql there's an option to vacuum the db in order to free up space taken by "empty slots" remaining after deleted entries. If there's something like that in SQLite, give it a try.

kerem commented

2026-02-25 23:33:48 +03:00

Author

Owner

@jhe2 commented on GitHub (Mar 29, 2022):

I don't really scroll through the homepage to find something, so being able to drastically limit entry limit per page

I like being able to do that. So I'd prefer to keep it the way it is, especially since it worked so nicely before.

Maybe SQLite is the culprit

This is unlikely, since with the previous version I also used sqlite3 as the backend with the exact same amount of bookmarks and it performed really well. It performed so well in fact, that I didn't even notice that there was this spinning loading symbol, because the list of bookmarks appeared pretty much instantly. Besides that, sqlite is actually known to be really fast in most situations, especially when it comes to read performance. Pretty much the only exception are multiple concurrent writes, which can slow things down, due to global locking of the database file.

Since the sqlite implementation used by Shiori was changed from using the normal C implementation (via mattn's go-sqlite lib) to a Go conversion of sqlite3 to avoid the need to build with CGO, there might be a performance impact because of that, although I wouldn't have expected a change that big. If it was because of that change, that would be unfortunate, since I like the idea of not requiring CGO.

I don't have experience with SQLite but in postgresql there's an option to vacuum the db in order to free up space taken by "empty slots" remaining after deleted entries. If there's something like that in SQLite, give it a try.

Good idea about vacuuming the database. That brought its size back down to where it was before. Unfortunately that didn't improve performance.
It's probably still a good idea to have the migration tool execute a vacuum command after doing its migration.

@jhe2 commented on GitHub (Mar 29, 2022): > I don't really scroll through the homepage to find something, so being able to drastically limit entry limit per page I like being able to do that. So I'd prefer to keep it the way it is, especially since it worked so nicely before. > Maybe SQLite is the culprit This is unlikely, since with the previous version I also used sqlite3 as the backend with the exact same amount of bookmarks and it performed really well. It performed so well in fact, that I didn't even notice that there was this spinning loading symbol, because the list of bookmarks appeared pretty much instantly. Besides that, sqlite is actually known to be really fast in most situations, especially when it comes to read performance. Pretty much the only exception are multiple concurrent writes, which can slow things down, due to global locking of the database file. Since the sqlite implementation used by Shiori was changed from using the normal C implementation (via mattn's go-sqlite lib) to a Go conversion of sqlite3 to avoid the need to build with CGO, there might be a performance impact because of that, although I wouldn't have expected a change that big. If it was because of that change, that would be unfortunate, since I like the idea of not requiring CGO. > I don't have experience with SQLite but in postgresql there's an option to vacuum the db in order to free up space taken by "empty slots" remaining after deleted entries. If there's something like that in SQLite, give it a try. Good idea about vacuuming the database. That brought its size back down to where it was before. Unfortunately that didn't improve performance. It's probably still a good idea to have the migration tool execute a vacuum command after doing its migration.

kerem commented

2026-02-25 23:33:48 +03:00

Author

Owner

@Mati20187 commented on GitHub (Mar 29, 2022):

Well, then we can probably rule out that the performance drop has something to do with the db migration that you've done as the loading is definitely noticeable even for me with much fewer bookmarks (2sec loading circle).
It's probably because of the change in sqlite implementation or web server improvements introduced in v.1.5.2 with the former being more likely imo.
If I decide to bite the bullet and recreate the same bookmarks but using postgresql to compare performance, I'll share the results. I might just give up on adding tags to not have to deal with the tag issue on postgres, the lack of tags probably won't affect the results too much.

@Mati20187 commented on GitHub (Mar 29, 2022): Well, then we can probably rule out that the performance drop has something to do with the db migration that you've done as the loading is definitely noticeable even for me with much fewer bookmarks (2sec loading circle). It's probably because of the change in sqlite implementation or web server improvements introduced in v.1.5.2 with the former being more likely imo. If I decide to bite the bullet and recreate the same bookmarks but using postgresql to compare performance, I'll share the results. I might just give up on adding tags to not have to deal with the tag issue on postgres, the lack of tags probably won't affect the results too much.

kerem commented

2026-02-25 23:33:48 +03:00

Author

Owner

@Mati20187 commented on GitHub (Mar 30, 2022):

@jhe2 I can confirm the performance drop occurs on SQLite but not on PostgreSQL.

I created shiori-postgres.service with a separate directory from shiori with sqlite, created a new postgres database and added the same links (25 bookmarks) that I have on sqlite (but had to add tags manually after adding the links - @fmartingr would it be possible for you to revisit #215 ? I'll add my logs there asap).

The web page load time with PostgreSQL is less than a second.
With SQLite it's 2 seconds.

@Mati20187 commented on GitHub (Mar 30, 2022): @jhe2 I can confirm the performance drop occurs on SQLite but not on PostgreSQL. I created shiori-postgres.service with a separate directory from shiori with sqlite, created a new postgres database and added the same links (25 bookmarks) that I have on sqlite (but had to add tags manually after adding the links - @fmartingr would it be possible for you to revisit #215 ? I'll add my logs there asap). The web page load time with PostgreSQL is less than a second. With SQLite it's 2 seconds.

kerem commented

2026-02-25 23:33:48 +03:00

Author

Owner

@Orhideous commented on GitHub (May 10, 2022):

Well. It happens due to one subtle detail: SQLite doesn't support covering indexes for virtual tables. That means any innocent query with LEFT JOIN will cause a serious performance hit. Sometimes by one or even two orders of magnitude in terms of query time. In my case, 20ms vs 2000ms on 3k articles with content.

I suggest to not join bookmark_content just for one has_content field.
I think it makes sense to move this field to the bookmark table — this will speed up all list queries. Yes, we will have to denormalize data a little bit.

@fmartingr What do you think about such pull request?

@Orhideous commented on GitHub (May 10, 2022): Well. It happens due to one subtle detail: SQLite doesn't support covering indexes for virtual tables. That means any innocent query with `LEFT JOIN` will cause a serious performance hit. Sometimes by one or even two orders of magnitude in terms of query time. In my case, 20ms vs 2000ms on 3k articles with content. I suggest to not join `bookmark_content` just for one `has_content` field. I think it makes sense to move this field to the `bookmark` table — this will speed up all list queries. Yes, we will have to denormalize data a little bit. @fmartingr What do you think about such pull request?

kerem commented

2026-02-25 23:33:48 +03:00

Author

Owner

@fmartingr commented on GitHub (May 26, 2022):

#425 should fix the performance issues while working with pagination, good work @Orhideous! Really appreciated.

@fmartingr commented on GitHub (May 26, 2022): #425 should fix the performance issues while working with pagination, good work @Orhideous! Really appreciated.

kerem referenced this issue

2026-02-25 23:34:41 +03:00

[PR #258] [MERGED] Continuous integration #551