Fusion - performance database - possible missing indexes

12 views
Skip to first unread message

Tore Van Grembergen

unread,
Jun 21, 2022, 1:00:50 AMJun 21
to Fusio
Hi,

I was doing some testing with fusio and noticed a high load on the database.
It looks like some indexes might be missing on the database.

I added below indexes and these seem to help improve performance.

ALTER TABLE `fusiodb`.`fusio_log`
ADD INDEX `category_date` (`category_id` ASC, `ip` ASC, `date` ASC) VISIBLE;

ALTER TABLE `fusiodb`.`fusio_routes`
ADD INDEX `category_status` (`category_id` ASC, `status` ASC) VISIBLE;

ALTER TABLE `fusiodb`.`fusio_rate`
ADD INDEX `status_id` (`status` ASC, `id` ASC) VISIBLE;

ALTER TABLE `fusiodb`.`fusio_routes`
ADD INDEX `status` (`status` ASC) VISIBLE;

Maybe something to check and evaluate ?

Kind regards

Tore

Christoph Kappestein

unread,
Jun 21, 2022, 5:57:16 PMJun 21
to Fusio
Hi Tore,

thanks for the info, we could indeed easily add those indexes to our schema.
How have you measured this performance, then I will also try to test and evaluate those indexes.

best regards
Christoph

Tore Van Grembergen

unread,
Jun 29, 2022, 4:09:38 AMJun 29
to Fusio
Dear Christoph,

Due to lack of time, I did not measure it scientificaly.
I noticed that when using fusio, the server running fusio would go to 100% usage.
I looked at it in more detail and noticed that the load mainly came from mysql.
I had a look at the queries that were sent to mysql and logged the onces that where not using indexes.
After applying the indexes the load dropped to 25% .

I hope this helps.

Kind regards

Tore



Op dinsdag 21 juni 2022 om 23:57:16 UTC+2 schreef christoph....@gmail.com:

Christoph Kappestein

unread,
Jun 29, 2022, 3:50:43 PMJun 29
to Fusio
Hi Tore,

thanks for the feedback and it is really good idea to check whether we can gain additional performance through those indexes,
I have also just created a PR s. https://github.com/apioo/fusio-impl/pull/27/files where I add those indexes, I will try and test a little bit but it looks indeed promising,
so thanks again for the feedback.

best regards
Christoph
Reply all
Reply to author
Forward
0 new messages