Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-4891

Percona version of mysqldump locks entire server

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Cannot Reproduce
    • Affects Version/s: 5.7.23-23
    • Fix Version/s: None
    • Component/s: MyRocks

      Description

      We are running Percona Server 5.7.23 on a server with a relatively large amount of tables / databases: 3.500+ databases 60.000+ tables.

      At some point after the upgrade from Percona Server 5.6 to Percona Server 5.7, we started to see unregular lockups of the server during the time that mysqldump was creating backups. It was not possible to create new connections, existing connections were still alive, but unable to do anything; in short: a complete lockup.

      We haven't found the cause yet, but we have some strong clues pointing at Percona's version of mysqldump or the specific queries it does.

      First of all, when we forcefully kill mysqld when the server appears to be locked, the following error is printed by mysqldump:

      mysqldump: Couldn't execute 'SELECT COUNT FROM performance_schema.session_variables WHERE VARIABLE_NAME LIKE 'rocksdb_skip_fill_cache'': Lost connection to MySQL server during query (2013)

      When investigating what queries Percona's mysqldump does around this time in a backup process, we get to this:

      /*!40100 SET @@SQL_MODE='' */
      /*!40103 SET TIME_ZONE='+00:00' */
      SHOW STATUS LIKE 'binlog_snapshot_%'
      SELECT COUNT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'performance_schema' AND table_name = 'session_variables'
      SELECT COUNT FROM performance_schema.session_variables WHERE VARIABLE_NAME LIKE 'rocksdb_skip_fill_cache'
      SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
      START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
      SHOW STATUS LIKE 'binlog_snapshot_%'
      UNLOCK TABLES
      SHOW VARIABLES LIKE 'ndbinfo_version'
      SAVEPOINT sp

      So, it appears that either of the SELECT COUNT queries causes some form of lockup in our setup and it appears to be caused by mechanisms that deal with rocksdb. Since we don't use rocksdb and I couldn't find any way to disable this on the command line, we switched to MariaDBs version of mysqldump (version 10.1.26), that does these queries:

      /*!40100 SET @@SQL_MODE='' */
      /*!40103 SET TIME_ZONE='+00:00' */
      SHOW STATUS LIKE 'binlog_snapshot_%'
      SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
      START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
      SHOW STATUS LIKE 'binlog_snapshot_%'
      UNLOCK TABLES
      SAVEPOINT sp

      This has been running now, and so far no crashes or lockups. So it seems that in some situations either SELECT COUNT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'performance_schema' AND table_name = 'session_variables' or
      SELECT COUNT FROM performance_schema.session_variables WHERE VARIABLE_NAME LIKE 'rocksdb_skip_fill_cache' can cause Percona Server to lockup.

      I'm happy to provide more information in an attempt to pinpoint the problem.

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                olaf.vanzandwijk Olaf van Zandwijk
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: