iGalerie

Knut

Membre

Lieu : Norway

Inscription : 27-12-2013

Messages : 74

Site Web

Album speed

I wonder if someone has same experience as I have now.

I have igalerie 3.0.22 with sqlite database. PHP version 8.1.17, SQLite version3.34.1
Database size 28 Mb

The igalereie has about 59 album. 2-3 of them large. One with 5100 pictures, the second large album with about 2500 pictures. All pictures with 1200 pixels, about 6-800 kb each photo. The database has 16100 items.

When I open the album with 5100 photos the response is quick. When I browse to the last photo, it takes 12-13 seconds. For the second large album with 2500 photos browsing to last photo takes 5-6 seconds.
The reponse corresponds with number of photos which make sence I guess.

This behavious is new, I have not noticed this delay before. Since the album has grown gradually over several years, I should have noticed this before.

I have done the maintenance actions in admin area with good reponse without issus. I have done a external check of the database sqlite file without finding problems.

Is this normal or do I have a bug?
Suggestions appreciated.
Knut

David

Développeur d'iGalerie

Inscription : 12-05-2006

Messages : 4 337

Site Web

Re : Album speed

Hi,

I've never had such slow loading with albums containing thousands of photos, so for the moment I don't know what's causing the problem. You should monitor page loading and SQL query execution times by setting the CONF_EXEC_TIME and CONF_DEBUG_SQL parameters to 1 in config/conf.php.

Knut

Membre

Lieu : Norway

Inscription : 27-12-2013

Messages : 74

Site Web

Re : Album speed

Thanks David,
I have complained to the slow server and they claim to have increased the resourses. Then I get a approx 3 seconds delay which is a larage improvement from 13 seconds.
I run the diagnostick mode as you explained and got this output. (sorry for the prsentation, could not find a way to prsent det table here.
Item no 10 is the one delaying the website.
[img]#    Fil    Linje    SQL-forespørsel    Resultat    Antall linjer behandlet    Utførelsestid
1    includes/core/dblayer/sqlite.class.php    210    PRAGMA synchronous = OFF    SUKSESS    0    0,317 ms
2    includes/core/dblayer/sqlite.class.php    216    PRAGMA foreign_keys = ON    SUKSESS    0    0,006 ms
3    includes/core/Config.class.php    925    SELECT * FROM config    SUKSESS    ?    0,030 ms
4    includes/core/Auth.class.php    191    SELECT g.*,
u.*,
(strftime('%s', '2024-03-26 12:21:40') - strftime('%s', CASE WHEN user_lastvstdt IS NULL
THEN '2000-01-01 00:00:00'
ELSE user_lastvstdt END)) > 900 AS user_update
FROM sessions AS s,
sessions_users AS su,
groups AS g,
users AS u
WHERE u.user_id = su.user_id
AND su.session_id = s.session_id
AND u.group_id = g.group_id
AND user_status = '1'
AND session_token = ?
AND session_expire > '2024-03-26 12:21:40'    SUKSESS    ?    0,130 ms
5    includes/core/Auth.class.php    262    UPDATE users
SET user_lastvstdt = '2024-03-26 12:21:40',
user_lastvstip = ?
WHERE user_id = ?    SUKSESS    1    0,239 ms
6    includes/gallery/classes/Gallery.class.php    672    SELECT user_id,
user_avatar,
user_login,
user_nickname,
user_lastvstdt
FROM users
WHERE user_id != 2
AND user_status = '1'
AND (strftime('%s', '2024-03-26 12:21:40') - strftime('%s', user_lastvstdt)) < 300    SUKSESS    ?    0,047 ms
7    includes/gallery/classes/GalleryCategory.class.php    692    SELECT cat.*,
CASE WHEN cat.password_id IS NULL THEN 1 ELSE (SELECT 2
FROM sessions AS s
LEFT JOIN sessions_categories AS sc USING (session_id)
LEFT JOIN passwords AS p USING (cat_id)
WHERE cat.password_id = p.password_id
AND session_token = '857710765d3ccd0f1ad108d87dc6ca2b2f6daf0c'
AND session_expire > '2024-03-26 12:21:40') END AS password_auth,
CASE WHEN cat_filemtime IS NULL
THEN 'category' ELSE 'album'
END AS cat_type,
i.item_id,
i.item_type,
i.item_adddt,
i.item_path,
i.item_orientation
FROM categories AS cat
LEFT JOIN items AS i
ON cat.thumb_id = i.item_id
WHERE 1=1
AND cat.cat_id = ?
AND cat_status = '1'
AND cat_filemtime IS NOT NULL    SUKSESS    ?    0,144 ms
8    includes/gallery/classes/GalleryCategory.class.php    810    SELECT 1
FROM favorites AS f
LEFT JOIN items AS i USING (item_id)
LEFT JOIN categories AS cat USING (cat_id)
WHERE 1=1
AND (cat.password_id IS NULL OR (SELECT 2
FROM sessions AS s
LEFT JOIN sessions_categories AS sc USING (session_id)
LEFT JOIN passwords AS p USING (cat_id)
WHERE cat.password_id = p.password_id
AND session_token = '857710765d3ccd0f1ad108d87dc6ca2b2f6daf0c'
AND session_expire > '2024-03-26 12:21:40') = 2)
AND item_status = '1'
AND f.user_id = ?
AND f.item_id = i.item_id
AND item_path LIKE ? ESCAPE '\'
LIMIT 1    SUKSESS    ?    0,124 ms

9    includes/gallery/classes/GalleryCategory.class.php    1141    SELECT t.*,
COUNT(*) AS tag_items_count
FROM tags AS t
LEFT JOIN tags_items AS ti
ON t.tag_id = ti.tag_id
LEFT JOIN items AS i
ON ti.item_id = i.item_id
LEFT JOIN categories AS cat
ON i.cat_id = cat.cat_id
WHERE 1=1
AND (cat.password_id IS NULL OR (SELECT 2
FROM sessions AS s
LEFT JOIN sessions_categories AS sc USING (session_id)
LEFT JOIN passwords AS p USING (cat_id)
WHERE cat.password_id = p.password_id
AND session_token = '857710765d3ccd0f1ad108d87dc6ca2b2f6daf0c'
AND session_expire > '2024-03-26 12:21:40') = 2)
AND i.item_path LIKE ? ESCAPE '\'
AND item_status = '1'

GROUP BY t.tag_id
ORDER BY tag_items_count DESC, t.tag_id ASC
LIMIT 0,100    SUKSESS    ?    67,901 ms

10    includes/gallery/classes/GalleryItems.class.php    847    SELECT i.*,
cat.cat_parents,
u.user_avatar,
u.user_login,
u.user_nickname,
u.user_status
FROM items AS i

LEFT JOIN categories AS cat
ON i.cat_id = cat.cat_id
LEFT JOIN users AS u
ON i.user_id = u.user_id
WHERE item_status = '1' AND 1=1 AND (cat.password_id IS NULL OR (SELECT 2
FROM sessions AS s
LEFT JOIN sessions_categories AS sc USING (session_id)
LEFT JOIN passwords AS p USING (cat_id)
WHERE cat.password_id = p.password_id
AND session_token = '857710765d3ccd0f1ad108d87dc6ca2b2f6daf0c'
AND session_expire > '2024-03-26 12:21:40') = 2) AND i.cat_id = :cat_id
ORDER BY LOWER(item_path) ASC, LOWER(item_name) ASC, item_position ASC
LIMIT 5100,60    SUKSESS    ?    2808,155 ms

11    includes/core/Item.class.php    1212    SELECT item_id
FROM favorites
WHERE item_id IN (26727, 26704, 26710, 26723, 26726, 26701, 26712, 26722, 26702, 26697, 26700, 26699, 26720, 26696, 26714, 26718, 26721, 26725, 26707, 26708, 26711, 26703, 26715, 26724, 26709)
AND user_id = ?    SUKSESS    ?    0,123 ms

12    includes/gallery/classes/Gallery.class.php    1290    SELECT i.*,
cat.cat_parents,
u.user_avatar,
u.user_login,
u.user_nickname,
u.user_status,
(0) AS in_selection
FROM items AS i
LEFT JOIN categories AS cat
ON i.cat_id = cat.cat_id
LEFT JOIN users AS u
ON i.user_id = u.user_id
WHERE 1=1
AND (cat.password_id IS NULL OR (SELECT 2
FROM sessions AS s
LEFT JOIN sessions_categories AS sc USING (session_id)
LEFT JOIN passwords AS p USING (cat_id)
WHERE cat.password_id = p.password_id
AND session_token = '857710765d3ccd0f1ad108d87dc6ca2b2f6daf0c'
AND session_expire > '2024-03-26 12:21:43') = 2)
AND item_status = '1'
ORDER BY RANDOM()
LIMIT 1    SUKSESS    ?    17,885 ms
13    includes/core/Category.class.php    655    SELECT cat.cat_id,
cat.user_id,
cat.password_id,
cat_parents,
cat_name,
cat_url,
cat_filemtime,
cat_creatable,
cat_uploadable,
cat_status,
cat_a_subcats + cat_d_subcats AS cat_subcats,
cat_a_subalbs + cat_d_subalbs AS cat_subalbs,
cat_a_images,
cat_a_videos,
p.cat_id AS p_cat_id,
CASE WHEN cat.password_id IS NULL THEN 1 ELSE (SELECT 2
FROM sessions AS s
LEFT JOIN sessions_categories AS sc USING (session_id)
LEFT JOIN passwords AS p USING (cat_id)
WHERE cat.password_id = p.password_id
AND session_token = '857710765d3ccd0f1ad108d87dc6ca2b2f6daf0c'
AND session_expire > '2024-03-26 12:21:43') END AS password_auth,
CASE WHEN cat.cat_id > 1
THEN parent_id
ELSE 0
END AS parent_id,
CASE WHEN cat_filemtime IS NULL
THEN 'category'
ELSE 'album'
END AS cat_type
FROM categories AS cat
LEFT JOIN passwords AS p USING (password_id)
WHERE 1=1 AND cat_status = '1'
AND thumb_id != -1
AND (cat_a_images + cat_a_videos) > 0
ORDER BY LOWER(cat_name) ASC    SUKSESS    ?    0,157 ms
*                        2895,258 ms[/img]
Knut

Knut

Membre

Lieu : Norway

Inscription : 27-12-2013

Messages : 74

Site Web

Re : Album speed

I have the ‘recent images’ turned on in the album.
When I click the flag for recent images which in the browser produce this code after the album address
?q=album/2/recent-images#top
Then the album page get dark nothing comes out. (I have dark mode in album).
This is flawless working on other server I use.
Suggestions appreciated, I looks like the server support can not find a solution.
Knut

Update:
This issue is a conflict with a javascript menu I used in the album. I removed the menu (not too important) then the last image flag is working properlly. This is server dependent. I get the menu workng on a second server under same conditions.

Dernière modification par Knut (26-03-2024 21:14:24)