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

Print ENGINE INNODB STATUS in machine parsable format

Details

    Description

      I think it would greatly help to have a SHOW ENGINE INNODB STATUS; in a machine parsable format.
      Currently it requires too many regex expressions to extract info.
      It should have the same info (or more) but with some strategy to easily extract data.
      It can be useful for trends, comparisons, graphing over multiple outputs.
      It could be any implementation, from prepending a label to each data field, to use tabular format for such info, it should keep allowing human to easily read it like it is now.
      Eventually there could be an option to select the format output.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-16580 Remove unused monitor counters from InnoDB

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Task - A task that needs to be done. MDEV-18698 Show InnoDB's internal background threads in SHOW ENGINE INNODB STATUS

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21566 Lock monitor doesn't print a name for RW-latches

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22087 Increase buffer size for query in SHOW ENGINE INNODB STATUS output

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17237 thread IDs are printed in different formats in different sections of SHOW ENGINE INNODB STATUS output

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18429 Consistent non-locking reads do not appear in TRANSACTIONS section of SHOW ENGINE INNODB STATUS

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18572 Thread executing DROP TABLE listed twice in SHOW ENGINE INNODB STATUS output

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Task - A task that needs to be done. MDEV-18582 Port status variables related to SHOW ENGINE INNODB STATUS from XtraDB to InnoDB in 10.2+

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21330 Lock monitor doesn't print a semaphore's last reserved thread in non-debug builds and INFORMATION_SCHEMA.INNODB_SYS_SEMAPHORE_WAITS is totally broken

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21390 lock_print_info_summary() should work even when trx_sys.mutex is locked

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            I agree that the SHOW ENGINE INNODB STATUS is difficult to handle.

            Which are the most interesting pieces of output? Some (such as "history list length") are not currently exported via any other interface. Could someone compose a full list, preferrably with suggestions of how to export, say, SHOW VARIABLES or via performance_schema. My preference would be SHOW VARIABLES except when some history is needed.

            I’d prefer to phase out SHOW ENGINE INNODB STATUS. However, if some parts of the output are not feasible to export in more structural ways, I guess that we could keep it. In that case, we would need a formal grammar for this, both as a specification for implementation, and for testing that the revised output actually conforms to the formal grammar.

            marko Marko Mäkelä added a comment - I agree that the SHOW ENGINE INNODB STATUS is difficult to handle. Which are the most interesting pieces of output? Some (such as "history list length") are not currently exported via any other interface. Could someone compose a full list, preferrably with suggestions of how to export, say, SHOW VARIABLES or via performance_schema . My preference would be SHOW VARIABLES except when some history is needed. I’d prefer to phase out SHOW ENGINE INNODB STATUS . However, if some parts of the output are not feasible to export in more structural ways, I guess that we could keep it. In that case, we would need a formal grammar for this, both as a specification for implementation, and for testing that the revised output actually conforms to the formal grammar.
            GeoffMontee Geoff Montee (Inactive) added a comment -

            The status variables that XtraDB exported are listed in MDEV-18582.

            If we create a replacement for SHOW ENGINE INNODB STATUS, then it may be a good idea for that replacement to be designed to do some of the things that SHOW ENGINE INNODB STATUS can't do, like the things described in MDEV-18429, MDEV-18698, etc.

            GeoffMontee Geoff Montee (Inactive) added a comment - The status variables that XtraDB exported are listed in MDEV-18582 . If we create a replacement for SHOW ENGINE INNODB STATUS, then it may be a good idea for that replacement to be designed to do some of the things that SHOW ENGINE INNODB STATUS can't do, like the things described in MDEV-18429 , MDEV-18698 , etc.
            rjasdfiii Rick James added a comment -

            When helping novices, the first thing I look for is the SQL statements that conflicted in a deadlock. Maybe put that pair of statements could be inserted (replaced) in a P_S entry or a "deadlock" table, thereby making a simple SELECT sufficient for my need.

            rjasdfiii Rick James added a comment - When helping novices, the first thing I look for is the SQL statements that conflicted in a deadlock. Maybe put that pair of statements could be inserted (replaced) in a P_S entry or a "deadlock" table, thereby making a simple SELECT sufficient for my need.
            marko Marko Mäkelä added a comment -

            rjasdfiii, MDEV-24738 in 10.6 introduced a configuration parameter innodb_deadlock_report that controls what will be included in the SHOW ENGINE INNODB STATUS output:

            • innodb_deadlock_report=off: Do not report any details of deadlocks.
            • innodb_deadlock_report=basic: Report transactions and waiting locks.
            • innodb_deadlock_report=full (default): Report also the blocking locks.

            If innodb_print_all_deadlocks=ON, information about deadlocks will also be written to the server error log.

            marko Marko Mäkelä added a comment - rjasdfiii , MDEV-24738 in 10.6 introduced a configuration parameter innodb_deadlock_report that controls what will be included in the SHOW ENGINE INNODB STATUS output: innodb_deadlock_report=off: Do not report any details of deadlocks. innodb_deadlock_report=basic: Report transactions and waiting locks. innodb_deadlock_report=full (default): Report also the blocking locks. If innodb_print_all_deadlocks=ON , information about deadlocks will also be written to the server error log.

            People

              Unassigned Unassigned
              claudio.nanni Claudio Nanni
              Votes:
              5 Vote for this issue
              Watchers:
              7 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.