Help wanted: /rest/v3/tags/stats runs for 180 seconds after migration to a new server via Plesk

[SWEIHER]

Shlink version

3.7.3

PHP version

8.2.15

How do you serve Shlink

Self-hosted Apache

Database engine

MariaDB

Database version

10.11.6-MariaDB-0+deb12u1 - Debian 12

Summary

Shlink was running on a Debian 10 VM using Plesk Web Admin Edition (latest). Now we created a brand new VM with Debian 12, installed Plesk and used the migration tool to transfer shlink from the old to the new VM.

After a few problems we could fix, shlink runs as expected - except for calling manage tags from https://app.shlink.io/ . Here we got 504 errors after 1 minute. We looked at server status with phpmyadmin and saw a query which needs about 180 seconds:

SELECT
t.tag AS tag,
COALESCE(v.visits, 0) AS visits,
COALESCE(b.non_bot_visits, 0) AS non_bot_visits,
COALESCE(t.short_urls_count, 0) AS short_urls_count
FROM ( SELECT
t.id AS tag_id,
t.name AS tag,
COUNT(DISTINCT s.id) AS short_urls_count
FROM tags t
LEFT JOIN short_urls_in_tags st ON st.tag_id = t.id
LEFT JOIN short_urls s ON st.short_url_id = s.id
GROUP BY t.id, t.name
) t
LEFT JOIN ( SELECT
st.tag_id AS tag_id,
COUNT(DISTINCT v.id) AS visits
FROM visits v
INNER JOIN short_urls s ON v.short_url_id = s.id
INNER JOIN short_urls_in_tags st ON st.short_url_id = s.id
GROUP BY st.tag_id
) v ON t.tag_id = v.tag_id
LEFT JOIN ( SELECT
st.tag_id AS tag_id,
COUNT(DISTINCT v.id) AS non_bot_visits
FROM visits v
INNER JOIN short_urls s ON (v.short_url_id = s.id) AND (v.potential_bot = '0')
INNER JOIN short_urls_in_tags st ON st.short_url_id = s.id
GROUP BY st.tag_id
) b ON t.tag_id = b.tag_id
ORDER BY tag ASC
LIMIT 1;

On the old VM this query needs ~16 seconds for the first time and less than 1 seconds when calling again. On the new VM it lasts always ~180 seconds every call.

On both VMs the content of the database is the same:

Tabelle Aufsteigend	Aktion	Datensätze 	Typ	Kollation		Größe		Überhang
api_keys	 		1		InnoDB	utf8mb3_unicode_ci	32,0 KiB	-
api_key_roles	 		0		InnoDB	utf8mb3_unicode_ci	48,0 KiB	-
device_long_urls		0		InnoDB	utf8mb4_unicode_ci	48,0 KiB	-
domains	 			2		InnoDB	utf8mb3_unicode_ci	32,0 KiB	-
migrations	 		27		InnoDB	utf8mb3_unicode_ci	16,0 KiB	-
short_urls	 		283		InnoDB	utf8mb3_unicode_ci	112,0 KiB	-
short_urls_in_tags		502		InnoDB	utf8mb3_unicode_ci	80,0 KiB	-
tags	 			20		InnoDB	utf8mb3_unicode_ci	32,0 KiB	-
visits	 			~2.303.547	InnoDB	utf8mb3_unicode_ci	760,0 MiB	-
visit_locations	 		~2.281.297	InnoDB	utf8mb3_unicode_ci	207,8 MiB	-
	10 Tabellen	Gesamt	~4.585.679	InnoDB	utf8mb3_general_ci	0,9 GiB	0 B

Please note that shlinks runs since 2021 and recorded several visits ;-)

Since everything else except for the debian version is the same on the new VM and because all configs were transfered using the plesk migration tool, we do not have any ideas what the reason is for the long time it lasts. Do you have any suggestions for us?

I don't know what information you need, so please tell me and I'll provide. Thank you.

[acelaya]

This is unfortunate, however, the performance issues with tag stats endpoint are known. You can see more details here #1346 (the "complex query" mentioned there is the one you pasted above)

That doesn't explain why it is now way slower than before, but I'm thinking it might be related with internal database indexes/caches/whatever, that were set in the previous instance as opposed to the new one.

Maybe performance improves once that whole dataset has been successfully loaded at least once. Perhaps you can try running it manually until it finishes.

[acelaya]

@SWEIHER I have found a possible way to address this problem, although I think I'm working with a much smaller dataset than you.

For me, with around 1M visits, 10k short URLs and 20k tags, this endpoint takes 4 seconds to load. With the changes I applied here, it goes down to 250ms.

Also, I think there's room for improvement, as I just updated a very complex query (the one you originally shared) to count visits via new table that tracks aggregated visit counts, but I think with this, the whole query could be replaced and re-thought from scratch.

Could you share with me some rough numbers of how many short URLs, visits and tags does your system have? It will help me verify potential improvements.

[acelaya]

Shlink 4.1.0 has just been released, with a huge performance improvement when loading tags stats. Let me know if this helped.