show-db is unreasonably slow, figure out why #210

Closed
opened 2026-05-30 18:20:36 +02:00 by oysteikt · 2 comments
Owner

One of the sql statements are really slow, see if we can figure out why and rewrite the statement

May 30 18:18:19 isvegg muscl-server[996272]: [OK|session:3|user:oysteikt] list-users
May 30 18:18:24 isvegg muscl-server[996272]: [OK|session:4|user:oysteikt] list-privileges
May 30 18:19:03 isvegg muscl-server[996272]: slow statement: execution time exceeded alert threshold
May 30 18:19:03 isvegg muscl-server[996272]: [OK|session:5|user:oysteikt] list-databases
May 30 18:20:00 isvegg muscl-server[996272]: slow statement: execution time exceeded alert threshold
May 30 18:20:00 isvegg muscl-server[996272]: [OK|session:6|user:oysteikt] list-databases
May 30 18:21:12 isvegg muscl-server[996272]: slow statement: execution time exceeded alert threshold
May 30 18:21:12 isvegg muscl-server[996272]: [OK|session:7|user:oysteikt] list-databases
One of the sql statements are really slow, see if we can figure out why and rewrite the statement ``` May 30 18:18:19 isvegg muscl-server[996272]: [OK|session:3|user:oysteikt] list-users May 30 18:18:24 isvegg muscl-server[996272]: [OK|session:4|user:oysteikt] list-privileges May 30 18:19:03 isvegg muscl-server[996272]: slow statement: execution time exceeded alert threshold May 30 18:19:03 isvegg muscl-server[996272]: [OK|session:5|user:oysteikt] list-databases May 30 18:20:00 isvegg muscl-server[996272]: slow statement: execution time exceeded alert threshold May 30 18:20:00 isvegg muscl-server[996272]: [OK|session:6|user:oysteikt] list-databases May 30 18:21:12 isvegg muscl-server[996272]: slow statement: execution time exceeded alert threshold May 30 18:21:12 isvegg muscl-server[996272]: [OK|session:7|user:oysteikt] list-databases ```
oysteikt added the bug label 2026-05-30 18:20:36 +02:00
Author
Owner
May 30 18:31:18 isvegg muscl-server[1000070]: Request:
                                              {
                                                "ListDatabases": null
                                              }
May 30 18:31:23 isvegg muscl-server[1000070]: slow statement: execution time exceeded alert threshold
May 30 18:31:23 isvegg muscl-server[1000070]: Response:
                                              {
                                                "ListAllDatabases": {
                                                  "Ok": [
                                                    {
                                                      "database": "oysteikt_test",
                                                      "tables": [
                                                        "pet",
                                                        "pet2",
                                                        "pet3"
                                                      ],
                                                      "users": [
                                                        "oysteikt_test",
                                                        "oysteikt_test2"
                                                      ],
                                                      "collation": "utf8mb4_unicode_ci",
                                                      "character_set": "utf8mb4",
                                                      "size_bytes": 98304
                                                    }
                                                  ]
                                                }
                                              }

Trace logging didn't really make it more clear...

``` May 30 18:31:18 isvegg muscl-server[1000070]: Request: { "ListDatabases": null } May 30 18:31:23 isvegg muscl-server[1000070]: slow statement: execution time exceeded alert threshold May 30 18:31:23 isvegg muscl-server[1000070]: Response: { "ListAllDatabases": { "Ok": [ { "database": "oysteikt_test", "tables": [ "pet", "pet2", "pet3" ], "users": [ "oysteikt_test", "oysteikt_test2" ], "collation": "utf8mb4_unicode_ci", "character_set": "utf8mb4", "size_bytes": 98304 } ] } } ``` Trace logging didn't really make it more clear...
Author
Owner

This seems to be it

pub async fn list_all_databases_for_user(
unix_user: &UnixUser,
connection: &mut MySqlConnection,
_db_is_mariadb: bool,
group_denylist: &GroupDenylist,
) -> ListAllDatabasesResponse {
let result = sqlx::query_as::<_, DatabaseRow>(
r"
SELECT
CAST(`information_schema`.`SCHEMATA`.`SCHEMA_NAME` AS CHAR(64)) AS `database`,
GROUP_CONCAT(DISTINCT CAST(`information_schema`.`TABLES`.`TABLE_NAME` AS CHAR(64)) SEPARATOR ',') AS `tables`,
GROUP_CONCAT(DISTINCT CAST(`mysql`.`db`.`User` AS CHAR(64)) SEPARATOR ',') AS `users`,
MAX(`information_schema`.`SCHEMATA`.`DEFAULT_COLLATION_NAME`) AS `collation`,
MAX(`information_schema`.`SCHEMATA`.`DEFAULT_CHARACTER_SET_NAME`) AS `character_set`,
CAST(IFNULL(
SUM(`information_schema`.`TABLES`.`DATA_LENGTH` + `information_schema`.`TABLES`.`INDEX_LENGTH`),
0
) AS UNSIGNED INTEGER) AS `size_bytes`
FROM `information_schema`.`SCHEMATA`
LEFT OUTER JOIN `information_schema`.`TABLES`
ON `information_schema`.`SCHEMATA`.`SCHEMA_NAME` = `TABLES`.`TABLE_SCHEMA`
LEFT OUTER JOIN `mysql`.`db`
ON `information_schema`.`SCHEMATA`.`SCHEMA_NAME` = `mysql`.`db`.`DB`
WHERE `information_schema`.`SCHEMATA`.`SCHEMA_NAME` NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
AND `information_schema`.`SCHEMATA`.`SCHEMA_NAME` REGEXP ?
GROUP BY `information_schema`.`SCHEMATA`.`SCHEMA_NAME`
",
)
.bind(create_user_group_matching_regex(unix_user, group_denylist))
.fetch_all(connection)
.await
.map_err(|err| ListAllDatabasesError::MySqlError(err.to_string()));

This seems to be it https://git.pvv.ntnu.no/Projects/muscl/src/commit/f16239aceb3bac3eaca091ce0ad16bec2a6bb9b6/src/server/sql/database_operations.rs#L314-L345
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: Projects/muscl#210