Mysql query speed

[yaozhiwang]

I've used umami for two months, everything works fine until recent two weeks, when my site started to get traffic like 7k - 8k daily views. The query speed became very slow, it often needs larger than 10s to finish, since I deployed it on Vercel, they have a timeout limit of 10s, which causes the website overview page unusable.

My db is deployed on planetscale, so i go to their dashboard and inspect the slowest queries, i got some queries like:

select os as x, count(*) as y from website_event join `session` on website_event.session_id = `session`.session_id where website_event.website_id = ? and website_event.created_at between ? and ? and website_event.event_type = ? group by ? order by ? desc limit ?

By explain the above statement, it showed that mysql only uses the index with only website_id column, so i added an index to include all columns involved in the statement:

alter table website_event add index website_id_event_type_session_id_created_at_idx(website_id, event_type, session_id, created_at);

And then the query time dropped down to less than 1s.

That is for queries with select ?? as x, where the ?? is a column in session table. For the columns in website_event table, i have to add that column into index:

alter table website_event add index website_id_created_at_event_type_session_id_event_name_idx(website_id, created_at, event_type, session_id, event_name);
alter table website_event add index website_id_created_at_event_type_session_id_url_path_idx(website_id, created_at, event_type, session_id, url_path);
alter table website_event add index website_id_created_at_event_type_session_id_referrer_domain_idx(website_id, created_at, event_type, session_id, referrer_domain);
alter table website_event add index website_id_created_at_event_type_session_id_url_query_idx((website_id, created_at, event_type, session_id, url_query);

So now, it is usable for time range Today/24 hours. However when i set the time range larger than several days, it becomes slow again. I explain the query, found that mysql stops using index when time range is large:

explain format=tree select referrer_domain as x, count(*) as y from website_event join `session` on website_event.session_id = `session`.session_id where website_event.website_id = '75464a58-77bc-4f97-9bfa-e1fcc9601a8a' and website_event.created_at between '2023-08-20' and '2023-08-24' and website_event.event_type = 1 group by 1 order by 2 desc limit 100;

-> Limit: 100 row(s) -> Sort: y DESC, limit input to 100 row(s) per chunk -> Table scan on <temporary> -> Aggregate using temporary table -> Nested loop inner join (cost=25125.61 rows=6581) -> Filter: ((website_event.event_type = 1) and (website_event.created_at between '2023-08-20' and '2023-08-24')) (cost=17891.34 rows=6581) -> Index lookup on website_event using website_event_website_id_idx (website_id='75464a58-77bc-4f97-9bfa-e1fcc9601a8a') (cost=17891.34 rows=153240) -> Single-row index lookup on session using PRIMARY (session_id=website_event.session_id) (cost=1.00 rows=1)

explain format=tree select referrer_domain as x, count(*) as y from website_event join `session` on website_event.session_id = `session`.session_id where website_event.website_id = '75464a58-77bc-4f97-9bfa-e1fcc9601a8a' and website_event.created_at between '2023-08-20' and '2023-08-21' and website_event.event_type = 1 group by 1 order by 2 desc limit 100;

-> Limit: 100 row(s) -> Sort: y DESC, limit input to 100 row(s) per chunk -> Table scan on <temporary> -> Aggregate using temporary table -> Nested loop inner join (cost=15654.12 rows=2797) -> Filter: ((website_event.event_type = 1) and (website_event.website_id = '75464a58-77bc-4f97-9bfa-e1fcc9601a8a') and (website_event.created_at between '2023-08-20' and '2023-08-21')) (cost=12588.16 rows=2797) -> Index range scan on website_event using website_id_created_at_event_type_session_id_referrer_domain_idx (cost=12588.16 rows=27972) -> Single-row index lookup on session using PRIMARY (session_id=website_event.session_id) (cost=1.00 rows=1)

Another index I've tried is:

alter table website_event add index website_id_event_type_session_id_event_name_created_at_idx(website_id, event_type, session_id, event_name, created_at);

explain format=tree select event_name as x, count(*) as y from website_event join `session` on website_event.session_id = `session`.session_id where website_event.website_id = '75464a58-77bc-4f97-9bfa-e1fcc9601a8a' and website_event.created_at between '2023-08-20' and '2023-08-24' and website_event.event_type = 1 group by 1 order by 2 desc limit 100;

-> Limit: 100 row(s) -> Sort: y DESC, limit input to 100 row(s) per chunk -> Table scan on <temporary> -> Aggregate using temporary table -> Nested loop inner join (cost=85800.57 rows=66613) -> Index scan on session using session_created_at_idx (cost=6715.87 rows=55944) -> Filter: (website_event.created_at between '2023-08-20' and '2023-08-24') (cost=1.14 rows=1) -> Index lookup on website_event using website_id_event_type_session_id_event_name_created_at_idx (website_id='75464a58-77bc-4f97-9bfa-e1fcc9601a8a', event_type=1, session_id=`session`.session_id) (cost=1.14 rows=3)

But unfortunately, it only works on event_name column, for other columns i added some index, but not used by mysql:

explain format=tree select referrer_domain as x, count(*) as y from website_event join `session` on website_event.session_id = `session`.session_id where website_event.website_id = '75464a58-77bc-4f97-9bfa-e1fcc9601a8a' and website_event.created_at between '2023-08-20' and '2023-08-24' and website_event.event_type = 1 group by 1 order by 2 desc limit 100;

-> Limit: 100 row(s) -> Sort: y DESC, limit input to 100 row(s) per chunk -> Table scan on <temporary> -> Aggregate using temporary table -> Nested loop inner join (cost=25125.61 rows=6581) -> Filter: ((website_event.event_type = 1) and (website_event.created_at between '2023-08-20' and '2023-08-24')) (cost=17891.34 rows=6581) -> Index lookup on website_event using website_event_website_id_idx (website_id='75464a58-77bc-4f97-9bfa-e1fcc9601a8a') (cost=17891.34 rows=153240) -> Single-row index lookup on session using PRIMARY (session_id=website_event.session_id) (cost=1.00 rows=1)

I'm not an expert on database, I guess the way i add indexes is probably not right. Can anyone help me out?

[Fish1]

Haven't looked into the database. But yeah, I am self hosting my analytics with Umami. And just recently it's gotten so slow. 7 days of data takes about 10 seconds. 30 days of data takes upwards of a minute, and then I get errors.

[Fish1]

Maybe I should just switch to postgress. Only reason I choose MySQL is because I enjoy mariadb.

[juangacovas]

Hello. I'm using MariaDB and saw slow queries too. I recently converted all *_id columns in all tables to "UUID" data type (and NOT varchar 36) and not only saved GB of space, also query speed was good too. So it's possible to get nice speed with mysql and NOT having to switch to PostgreSQL...

[juangacovas]

No. UUID data type in MariaDB 10.7+ is a dedicated 128 bit column (16 bytes), so it seems better than VARCHAR(36) with a utf8mb4 collation which is the current for ids columns in umami for mysql/mariadb.

In fact, 9 GB of table "website_event" got reduced to ~500 MB after converting the _id columns to UUID data type, which is huge...

So in terms of searching / indexing / storage, the dedicated UUID for MariaDB 10.7 seems a much more efficient struct (I don't know for mysql)

[juangacovas]

Also, I was unable to DELETE a website with 5 million rows in website_event (query timeout). After the change to UUID, the query was run just fine.

[yaozhiwang]

Hi, it's awesome. Can you share more details on how to convert it？ And do you need to change the code of query statements in umami?

[juangacovas]

Wrote a general reply instead of replying to your reply, sorry

[yaozhiwang]

Thank you very much! I will try this.

[juangacovas]

Hey, please do a complete backup of your database before trying this, since I may have overseen something, but my stats are working fine after this change. The change to UUID does not seem to affect queries, since everything is the same when inserting, selecting, etc (uuids as strings, etc).

In MariaDB 10.7+ you want to convert all VARCHAR(36) columns (*_id columns) in all tables to the new UUID data type.

For example, to change column "user_id" at table "user":
ALTER TABLE userCHANGE COLUMNuser_id user_id UUID NOT NULL;

Biggest tables are session & website_event so expect some time to reindex / re-struct.

I've done it using HeidiSQL (change data type), then you have your ALTER code at tab "Alter code".

[yaozhiwang]

Sorry for the delayed update. I attempted to alter the table yesterday, but unfortunately discovered that MySQL does not support the UUID column type. I'm using PlanetScale's database service, which is backed by MySQL. I inquired on their GitHub, and it turns out that this is not feasible with MySQL.

[juangacovas]

Yeah it will depend on mysql version. With MariaDB everything seems ok from the version I mentioned

…

On Sat, Oct 21, 2023 at 4:37 AM Yaozhi Wang ***@***.***> wrote: Sorry for the delayed update. I attempted to alter the table yesterday, but unfortunately discovered that MySQL does not support the UUID column type. I'm using PlanetScale's database service, which is backed by MySQL. I inquired on their GitHub <planetscale/discussion#555>, and it turns out that this is not feasible with MySQL. — Reply to this email directly, view it on GitHub <#2232 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAI7NGNVBY6OXE2VNIROLD3YAMYOTAVCNFSM6AAAAAA4BEWRA2VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TGNBUGQ3TI> . You are receiving this because you commented.Message ID: ***@***.***>