Autocomplete_suggestions causing Metabase to become unstable

Bug reports
1

Hello everyone!

Im facing a issue when i try to build SQL queries on Metabase.
The problem is: one autocomplete_suggestion call becomes 9, and this overload the application db and the application itself, causing the application to fail the health-check and restart.

First, i was investigating the fact that autocomplete_suggestions calls were taking minutes to complete.
When i was investigating, i noticed this strage behaviour.

metabase_autocomplete_suggestion_small

Metabase restarting after getting stuck on autocomplete_suggestions calls:

image
image1487×758 60.8 KB

DB columns and tables:

result_query_sensitivedata
result_query_sensitivedata1077×157 9.29 KB

Toubleshooting informations:
Metabase deployed on AWS ECS (Docker container), 1vCPU and 3GB RAM
Application DB: RDS Postgres, 2vCPU and 4GB RAM

{
  "browser-info": {
    "language": "en-US",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/116.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.20+8",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.20",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.20+8",
    "os.name": "Linux",
    "os.version": "5.10.184-175.749.amzn2.x86_64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "snowflake"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "15.2"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.5.1"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2023-07-28",
      "tag": "v0.46.6.4",
      "branch": "release-x.46.6.x",
      "hash": "7c60aca"
    },
    "settings": {
      "report-timezone": "America/Sao_Paulo"
    }
  }
}

Adicional information:
I have two Metabases deployed on AWS, with identical resources (separately).
The another is connected to the same DB (Snowflake), and i'm not facing this issue on the 2nd.

Thank you!

2

Hey aronsales

You are correct in that depending on how quickly you’re typing the query, as well as which autocomplete method you’re using, will dictate the amount of autocomplete calls being made.
Do you happen to know if you have a different setting for autocomplete between both Metabase instances?

Also, are the two instances in the same aws region?
Are metrics relatively similar when doing superficial tests like iperf to measure latency and bandwidth from either direction?

3

Hi @Filmon!

Thank you for the reply!

  • Both Metabase instances had same settings for autocomplete;
  • Yes, both are in the same AWS region (us-east-1);
  • About the test (iperf), i'll run in the next days.

I fix this issue changing the envoriment variable MB_NATIVE_QUERY_AUTOCOMPLETE_MATCH_STYLE to prefix.
Now, the autocomplete_suggestions take around 200~300ms to return.
Thank you for this!

Another question: If i exclude unused tables in the metabase_field and metabase_table tables (app db), this can optimize the autocomplete_suggestion calls? Is there any risk in doing so?

4

@aronsales chiming in from https://discourse.metabase.com/t/autocomplete-takes-down-our-self-hosted-metabase-instance/25318/2:

We are cleaning metabase_field and metabase_table on a regular basis, no harm done so far.