Api search performance

Get Help FAQ
1

Hello I’m trying to understand why the search bar is slow for regular user (around ~30s and more). I end up with this SQL snippet that is part of the search query (api/search)

SELECT 
  `mt`.`id` 
FROM 
  `metabase_table` AS `mt` 
WHERE 
  EXISTS (
    SELECT 
      1 
    FROM 
      `data_permissions` AS `dp` 
    WHERE 
      (
        `dp`.`perm_type` = 'perms/view-data'
      ) 
      AND (
        (
          (`mt`.`db_id` = `dp`.`db_id`) 
          AND (`dp`.`table_id` IS NULL)
        ) 
        OR (`mt`.`id` = `dp`.`table_id`)
      ) 
      AND EXISTS (
        SELECT 
          1 
        FROM 
          `permissions_group` AS `pg` 
        WHERE 
          (`pg`.`id` = `dp`.`group_id`) 
          AND EXISTS (
            SELECT 
              1 
            FROM 
              `permissions_group_membership` AS `pgm` 
            WHERE 
              (`pgm`.`group_id` = `pg`.`id`) 
              AND (`pgm`.`user_id` = 13372210)
          )
      ) 
    GROUP BY 
      `mt`.`id` 
    HAVING 
      (
        CASE WHEN MIN(
          CASE WHEN `dp`.`perm_value` = 'unrestricted' THEN 0 WHEN `dp`.`perm_value` = 'blocked' THEN 1 WHEN `dp`.`perm_value` = 'legacy-no-self-service' THEN 2 END
        ) = 0 THEN 0 WHEN MIN(
          CASE WHEN `dp`.`perm_value` = 'unrestricted' THEN 0 WHEN `dp`.`perm_value` = 'blocked' THEN 1 WHEN `dp`.`perm_value` = 'legacy-no-self-service' THEN 2 END
        ) = 1 THEN 2 WHEN MIN(
          CASE WHEN `dp`.`perm_value` = 'unrestricted' THEN 0 WHEN `dp`.`perm_value` = 'blocked' THEN 1 WHEN `dp`.`perm_value` = 'legacy-no-self-service' THEN 2 END
        ) = 2 THEN 1 END <= 0
      )
  ) 
  AND EXISTS (
    SELECT 
      1 
    FROM 
      `data_permissions` AS `dp` 
    WHERE 
      (
        `dp`.`perm_type` = 'perms/create-queries'
      ) 
      AND (
        (
          (`mt`.`db_id` = `dp`.`db_id`) 
          AND (`dp`.`table_id` IS NULL)
        ) 
        OR (`mt`.`id` = `dp`.`table_id`)
      ) 
      AND EXISTS (
        SELECT 
          1 
        FROM 
          `permissions_group` AS `pg` 
        WHERE 
          (`pg`.`id` = `dp`.`group_id`) 
          AND EXISTS (
            SELECT 
              1 
            FROM 
              `permissions_group_membership` AS `pgm` 
            WHERE 
              (`pgm`.`group_id` = `pg`.`id`) 
              AND (`pgm`.`user_id` = 13372210)
          )
      ) 
    GROUP BY 
      `mt`.`id` 
    HAVING 
      (
        MIN(
          CASE WHEN `dp`.`perm_value` = 'query-builder-and-native' THEN 0 WHEN `dp`.`perm_value` = 'query-builder' THEN 1 WHEN `dp`.`perm_value` = 'no' THEN 2 END
        ) <= 1
      )
  )

Metabase version: v0.56/v0.55

SQL Engine: mariadb: 11.4

EXPLAIN seems not to show a missing index…

Do you have any hint ? (is it a limitation of metabase versus the number of tables ~50.000 in our case…)

Many thanks for your inputs !

Regards

2

What is the contents of the table search_index_metadata?

3

:thinking: it’s completely empty. It’s related to this feature ? (maybe only for enterprise installation, here it’s a community one)

image
image500×181 10.1 KB

4

Yes, the search_index_metadata table tracks the search indexes that power the Search box. It’s a feature on all Metabase versions. There should be 2 records in it.

If there’s nothing there, then the search index build is failing for some reason. Check the Metabase logs for anything related to “search” and see if it’s getting a database error or is throwing an exception during its index run.

5

Thanks for your answer, I see nothing about search/reindex job errors (I’m still digging though) in the log even when I check during the quartz trigger… Are you sure Mariadb is compatible ? I can see some pgvector method in the source code abour search…

6

EDIT: Remove post, information was not correct.

7

Hello Doug,

First of all, I’m really grateful for your answers.

Thanks to you I feel that I’m missing something on my setup but still don’t know exactly what. I confirm that my mariadb user is able to write in search_index_metadata and can create tables as well. So I’ve tried to launch a simple compose.yml like this:

services:
  mariadb:
    image: docker.registry.vptech.eu/mariadb:11
    container_name: mariadb
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: example_root_password
      MYSQL_DATABASE: metabase
      MYSQL_USER: metabase_user
      MYSQL_PASSWORD: metabase_password
    ports:
      - "3306:3306"
    volumes:
      - mariadb_data:/var/lib/mysql

  metabase:
    image: metabase/metabase:latest
    container_name: metabase
    restart: always
    ports:
      - "3000:3000"
    environment:
      MB_DB_TYPE: mysql
      MB_DB_HOST: mariadb
      MB_DB_PORT: 3306
      MB_DB_DBNAME: metabase
      MB_DB_USER: metabase_user
      MB_DB_PASS: metabase_password

volumes:
  mariadb_data:
  • I still see big SQL query (UNION ALL etc) when I do a global search (the one that kills my performance on my production setup)
  • search_index_metadata still empty
  • http://127.0.0.1:3000/api/search/force-reindex says Search index is not supported for this installation.
  • No search_index__xxx tables

Will wait one hour for the scheduler, I let you know.

Edit: still nothing after 1h

8

think it’s really a postgresql feature unfortunately for me:

9

OK, guess I was wrong, I can see that the search engine is different between MySQL and PostgreSQL appdb’s.

UPDATE: I left the test Metabase with MySQL appdb running for the day and it never built a search index, so looks like your code examination is correct.

Looking at the query you posted at the top again, I think it is a standard one to check what tables the given user ID can view. It should be pretty fast. Not sure for search if it needs to run more than once. Is it popping up in the slow query log?

Past that, we need to dig into if your appdb is having performance issues of some kind.