Performance issue on dashboard with many charts

Configuration
1

Edit: I changed the title of this post since it turned out to be a performance issue specific to Gladys.

Hello :slight_smile:

It’s not really a home automation feature strictly speaking and it’s something that would require a lot of development work ^^

But I think it could be useful to have the choice during Gladys installation to use either a SQLite or MySQL database.

Why this need?
When you start having many modules on a dashboard, especially graphs, SQLite shows its limitations (about 10s to display my main dashboard)

A MySQL database will make Gladys perform better during multiple concurrent queries and improve the responsiveness of dashboards.

This is just my opinion ^^

2

My opinion: If during installation you ask me to choose, I won’t install because I don’t understand and I tell myself it’s not for me

3

I do agree that this kind of choice would drive away a large portion of users and that SQLite is sufficient for the vast majority of

1 Like
4

Hi @Checconio !

I don’t think MySQL is the solution to your problem. SQLite is just as performant for this use, it’s not because there’s « Lite Â» in the name that it’s slower, quite the opposite :smiley:

The performance issue should instead be addressed in Gladys :slight_smile:

Would you mind providing me your DB (privately) so I can run performance tests on my side?

What’s the speed of your disk?

To measure the speed, I recommend following this tutorial => Your Web Host Doesn’t Want You To Read This: Benchmark Your VPS, with the dd command you can measure write/then read performance.

5

Thank you for the feedback @pierre-gilles

I know that SQLite is very efficient; every DBMS has its weaknesses and strengths, which is why the topic remains a proposal :slight_smile:
You’re in the best position to know how Gladys works with the database

No problem helping with optimization!

Gladys is on an M.2 SSD, write speed after benchmark: between 350 and 380 MB/s
Processor: 2.7 GHz 4 cores
RAM: 8 GB + 1 GB swap

I’ll send you my database privately

1 Like
6

Thanks @Checconio for the DB you sent me privately, I was able to perform a small performance audit on your data and it helped me a lot, I found the issue! :slight_smile:

First, the issue was very clear: it was super slow even on my fairly powerful development machine, I don’t know how you manage to cope with that, it was ultra slow ^^

To illustrate, I saw this:

Screenshot 2022-09-12 at 15.48.17
Screenshot 2022-09-12 at 15.48.173248×1948 695 KB

Almost 40 seconds to display the page, that’s just huge ^^

I ran Gladys locally in debug mode to see the executed SQL queries and I ran them manually on the DB, « live Â».

Screenshot 2022-09-12 at 15.55.40
Screenshot 2022-09-12 at 15.55.402002×1007 134 KB

6 seconds for a query that returns nothing, that’s a lot!

So I pulled the thread, there are 2 conditions on this query (WHERE device_feature_id = ? AND created_at >= ?), and both of these attributes do have an index


I run EXPLAIN QUERY PLAN to understand what SQLite is doing, and I understand.

SQLite could use the index on device_feature_id to fetch all states for that feature, but then it did a sequential scan to filter by date: the index on the date wasn’t used.

The solution is very simple: just add an index that covers the 2 attributes used in the query.

So I added 2 indexes on device_feature_state and t_device_feature_state_aggregate covering the attributes used by the dashboard queries.

await queryInterface.addIndex('t_device_feature_state', ['device_feature_id', 'created_at']);
await queryInterface.addIndex('t_device_feature_state_aggregate', ['device_feature_id', 'type', 'created_at']);

And then: bam! That same query goes from 6 seconds to
 5ms!! :grimacing:

For the whole page, it goes from 40 seconds to.. 100 ms on my machine!

I told you, it wasn’t due to SQLite, we would have had the same issue on MySQL :wink:

I made a PR that fixes the issue, it will be included in the next Gladys release:

@Terdious @lmilcent this will interest you who have a lot of data!

6 Likes
7

That’s great!

And thanks for the explanations :smiley:
This big optimization will allow me to continue to make my Gladys grow properly :wink:

2 Likes
8

Oh yes, I can’t wait to test and confirm that it changes everything for me :slight_smile:

1 Like
9

Pierre Gilles, foremost defender of SQLite :shield: Thanks for the analysis and the correction, very interesting.

2 Likes
10

I have a build on the dev tag in progress, I’ll keep you posted when it’s ready :wink:

In our use case (embedded program), SQLite is the most suitable, that’s why I defend it :smiley: Client/server DBMSs (MySQL/PostgreSQL) are suitable when you have a database server that is accessed by multiple clients over a network.

I quote the SQLite website: Appropriate Uses For SQLite

Screenshot 2022-09-12 at 18.17.22
Screenshot 2022-09-12 at 18.17.221920×295 60.8 KB

Screenshot 2022-09-12 at 18.17.35
Screenshot 2022-09-12 at 18.17.351920×319 72.1 KB

2 Likes
11

The build is ready:

12

After 26 minutes of updating my database following your patch, I confirm the huge speed improvement on the dashboards.

I can load all the dashboards in a loop almost instantly or so. Previously I would make Gladys « crash Â» for 5 to 10 seconds on my dashboards with many charts.

1 Like
13

Great! Yes, it’s normal for the migration to take some time; you have to build the entire index.

Your database will also be larger, easily +20% typically, but that’s the price to pay for it to display instantly! :grinning_face_with_smiling_eyes:

14

Oh yes, from 6.3GB to 7.9GB :scream:

Screenshot_20220912-201907_JuiceSSH
Screenshot_20220912-201907_JuiceSSH1440×389 88 KB

1 Like
15

Yeah, but you’ll be able to remove features from the history, that’ll free up some space :grin:

2 Likes
16

Clearly.
I admit I don’t dare touch it yet, has anyone already done it?

17

I used to do it in the DB :smiling_face_with_sunglasses:

2 Likes
18

@euguuu tested it and it works!

The bonus is that each time you remove features from the history, Gladys runs a little VACUUM so it cleans up as well :wink:

1 Like
19

Wow, thanks for the link, I had missed it. I can’t wait to try it!!

1 Like
20

@pierre-gilles, I tried last