Some indexes seem to be missing for "fusio_log" table and lead to poor performances

[gpenin]

Hi,

We were facing poor performances as long as the "fusio_log" table was growing (quickly as we are using HAProxy with a L7 check). The MariaDB "slow query" log showed that it was related to the rate limiting feature (SELECT COUNT(*) AS cnt FROM fusio_log WHERE ...).

According to those queries, we added 2 indexes on the "fusio_log" table :

ALTER TABLE `fusio`.`fusio_log` DROP INDEX `IDX_LOG_TID-IP-DATE`, ADD INDEX `IDX_LOG_TID-IP-DATE` (`tenant_id`, `ip`, `date`) USING BTREE;
ALTER TABLE `fusio`.`fusio_log` DROP INDEX `IDX_LOG_TID-UID-DATE`, ADD INDEX `IDX_LOG_TID-UID-DATE` (`tenant_id`, `user_id`, `date`) USING BTREE;

• Time for request processing as seen in Fusio Apache logs (last column, in ms) before adding those indexes :

XXX.XXX.XXX.XXX - - [22/Aug/2024:11:22:37 +0000] "GET /system/health HTTP/1.0" 200 459 "-" "HAProxy" 98
XXX.XXX.XXX.XXX - - [22/Aug/2024:11:22:39 +0000] "GET /system/health HTTP/1.0" 200 459 "-" "HAProxy" 88
XXX.XXX.XXX.XXX - - [22/Aug/2024:11:22:41 +0000] "GET /system/health HTTP/1.0" 200 459 "-" "HAProxy" 87
XXX.XXX.XXX.XXX - - [22/Aug/2024:11:22:43 +0000] "GET /system/health HTTP/1.0" 200 459 "-" "HAProxy" 117
XXX.XXX.XXX.XXX - - [22/Aug/2024:11:22:46 +0000] "GET /system/health HTTP/1.0" 200 459 "-" "HAProxy" 130

• Time for request processing as seen in Fusio Apache logs (last column, in ms) after adding those indexes :

XXX.XXX.XXX.XXX - - [22/Aug/2024:11:22:48 +0000] "GET /system/health HTTP/1.0" 200 459 "-" "HAProxy" 13
XXX.XXX.XXX.XXX - - [22/Aug/2024:11:22:50 +0000] "GET /system/health HTTP/1.0" 200 459 "-" "HAProxy" 13
XXX.XXX.XXX.XXX - - [22/Aug/2024:11:22:52 +0000] "GET /system/health HTTP/1.0" 200 459 "-" "HAProxy" 10
XXX.XXX.XXX.XXX - - [22/Aug/2024:11:22:54 +0000] "GET /system/health HTTP/1.0" 200 459 "-" "HAProxy" 12
XXX.XXX.XXX.XXX - - [22/Aug/2024:11:22:56 +0000] "GET /system/health HTTP/1.0" 200 459 "-" "HAProxy" 9

The "fusio_log" table contained ~250k lines during those measurements.

Maybe those indexes could be added upstream ?

Regards,

[chriskapp]

Hi @gpenin thanks for the feedback and this is indeed a good idea, I have just added a commit s. apioo/fusio-impl@15c10f3 to add those index for the next version

[chriskapp]

This is now included in the latest 5.1.4 release.