Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30736

Speed up prepared statements by caching tables

    XMLWordPrintable

Details

    • New Feature
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • None

    Description

      When running a prepared statement the high level process is:

      • Open all tables used by the prepared statement (from the table cache)
      • Take two MDL locks on all tables (this is part of opening a table)
      • Lock the table (with lock tables)
      • Re-prepare the statement (call fix_fields on all used fields; Fast if the table has not changed)
      • Run the statement
      • unlock tables
      • free MDL locks
      • Add table back to table cache

      This could be speed up by caching all tables for the prepared statement, which should give a notable performance increase.

      This should be done in two stages:

      • Do a prototype that attach the tables to the prepared statement. This is needed to measure
        the maximum performance gain we can achieve.
        This could be done in a reasonable short time.

      Stage two would be to make the cached tables work with DDL's.

      Some possible solutions (in no particular order)
      1) Allow other threads to free tables in prepared statements that are not running.
      2) Add a marker to THD's that it has prepared statements with involves tables that should be freed. For this we also need a wakeup call to wake up threads that are sleeping on connection to do this work.
      3) When a prepared statements stops, put the tables in a 'public area' that a DDL statement can remove tables from. When using the prepared statement next time, if a used table was removed, free all tables and re-prepare the statement (needs to be done anyway as things may have changed).

      Attachments

        Activity

          People

            Unassigned Unassigned
            monty Michael Widenius
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.