Slow loading #1453
-
|
Hey everyone! Anyone had problem like this: Image page, Gallery page, Loop page are loading too long. For example it can be something like 2 minutes just to load "design" of the page and after this 3 minutes to load the image. After that it works pretty good. This problem worse with every day, like it depends on the count of the images. Maybe someone knows how to improve this thing? Maybe there are some parameters about cache or something to speed up loading? |
Beta Was this translation helpful? Give feedback.
Replies: 3 comments 7 replies
-
|
Are you just using the standard sqlite database with your system? |
Beta Was this translation helpful? Give feedback.
-
|
Hey! Yes, tbh i didn't know, that I can use something else. But I use Raspberry Pi 3B+, so, I'm not sure, that this device can run something like else. |
Beta Was this translation helpful? Give feedback.
-
|
@piratinskii Try creating the 3 indexes using the commands from this page for SQLite: https://github.com/aaronwmorris/indi-allsky/wiki/Image-viewer-DB-performance Hopefully, this will speed some things up. |
Beta Was this translation helpful? Give feedback.
-
|
Unfortunettly, it didn't help :( So, I'll try the way with MariaDB. Thank you! |
Beta Was this translation helpful? Give feedback.
-
|
That also didn't help. I configured the system to work with MariaDB, but, it still loads Image and Gallery pages too long. Seems like Raspberry just can't work when DB contains more than something...Now I have 226 000 records in the Image table. |
Beta Was this translation helpful? Give feedback.
-
|
SQLite will be better than MariaDB for most cases, especially with the indexes I had you create. I expect the indexes to at least decrease some of the queries by 50%. However, 50% of a long time is usually still a long time. :-) The indexes I had you create are not possible to do on mariadb because it does not support functional indexes. One issue is the Raspberry Pi 3 which can be relatively slower than a lot of newer SBCs. 226,000 records is a lot and I believe your system is lagging. I have 63,000 in mine with a similar SBC and there are limits to the performance. I am still looking for ways to improve the query speeds of the database. |
Beta Was this translation helpful? Give feedback.
-
|
With MariaDB it's unexpected a little bit better. After indexes it was
something like 10% faster, now it's 25% faster but still a looooooot of
time for me. 1 minutes for page loading and 3 minutes for first image
loading and something like 45 seconds for every change (hour, day etc).
So, yeah, I have a lot of photos because I use astronomical camera in the
pretty strong light pollution, so, I have to use short exposures, like 5
seconds and I do it with minimal pause between the frames to catch
meteors..so, its a lot of images. Maybe I should change time to delete it...
…On Thu, Aug 15, 2024, 16:25 Aaron W Morris ***@***.***> wrote:
SQLite will be better than MariaDB for most cases, especially with the
indexes I had you create. I expect the indexes to at least decrease some of
the queries by 50%. However, 50% of a long time is usually still a long
time. :-) The indexes I had you create is not possible to do on mariadb
because it does not support functional indexes.
One issue is the Raspberry Pi 3 which can be relatively slower than a lot
of newer SBCs.
226,000 records is a lot and I believe your system is lagging. I have
63,000 in mine with a similar SBC and there are limits to the performance.
I am still looking for ways to improve the query speeds of the database.
—
Reply to this email directly, view it on GitHub
<#1453 (reply in thread)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/A2VORHDNBKOXCYDDX4OGV73ZRST3FAVCNFSM6AAAAABMO327BCVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTAMZUG43TSOA>
.
You are receiving this because you were mentioned.Message ID:
***@***.***
com>
|
Beta Was this translation helpful? Give feedback.
-
|
One of the reasons it is so slow is the image queries need to extract the year, month, day, and hour from the createDate of the images. The indexes I sent you for sqlite index all of those values. Unfortunately, mariadb does not support functional indexes (indexes for data returned from functions). I have been considering adding explicit year, month, day, and hour columns so that these could be indexed. |
Beta Was this translation helpful? Give feedback.
-
|
I was able get some time to focus on this. I found a regression that was slowing some queries down by a factor of 4 because one of the date components was being used as a string instead of an integer. This only appeared to cause problems with MariaDB. However, the new index across the new date component fields yields significantly faster performance. Basically, for some of the database entries, there are now dedicated fields for year, month, day, and hour so that indi-allsky does not have to rely on extracted fields. Here is an example of one query for the distinct hours on a given day. The hardware is roughly the speed of a Raspi3, so not a fast system. (original) Year as string regression: While this single query was slow, the image and gallery views required 4-5 queries which were all suffering from the same problem. In some cases, it was taking 2-3 minutes to run all of the queries to generate the page. For the most part, SQLite is still faster, but the gap is closer. Merged #1488 |
Beta Was this translation helpful? Give feedback.
-
|
Wow, it's great, dude! Thank you very much! Now it's only 5 seconds to load instead of 3 minutes!!! |
Beta Was this translation helpful? Give feedback.
I was able get some time to focus on this.
I found a regression that was slowing some queries down by a factor of 4 because one of the date components was being used as a string instead of an integer. This only appeared to cause problems with MariaDB.
However, the new index across the new date component fields yields significantly faster performance. Basically, for some of the database entries, there are now dedicated fields for year, month, day, and hour so that indi-allsky does not have to rely on extracted fields.
Here is an example of one query for the distinct hours on a given day. The hardware is roughly the speed of a Raspi3, so not a fast system.
(original) Year as string regressi…