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

Non-blocking "set read_only"

Details

    • Task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.3.8
    • None

    Description

      It can be dangerous to run "set read_only" on a production server because it can block in close_cached_tables. More details about the pain this caused previously are at:
      http://mysqlha.blogspot.com/2008/07/what-exactly-does-flush-tables-with.html

      Per the code in set_var.cc:

       /*
      		 
          Perform a 'FLUSH TABLES WITH READ LOCK'.
      		 
          This is a 3 step process:
      		 
          - [1] lock_global_read_lock()
      		 
          - [2] close_cached_tables()
      		 
          - [3] make_global_read_lock_block_commit()
      		 
          [1] prevents new connections from obtaining tables locked for write.
      		 
          [2] waits until all existing connections close their tables.
      		 
          [3] prevents transactions from being committed.
      		 
        */

      Can there be a variant that doesn't do #2? My workload doesn't use MyISAM and I don't know if #2 is done because of MyISAM. Calling close_cached_tables seems like a heavy way to force LOCK TABLEs to be unlocked. Any long running queries will cause #2 to block.

      See also http://lists.mysql.com/commits/142825

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-309 reimplement FLUSH ... CHECKPOINT

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

          Activity

            Ascending order - Click to sort in descending order
            ratzpo Rasmus Johansson (Inactive) created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            Description It can be dangerous to run "set read_only" on a production server because it can block in close_cached_tables. More details about the pain this caused previously are at:
            http://mysqlha.blogspot.com/2008/07/what-exactly-does-flush-tables-with.html

            Per the code in set_var.cc:

             /*
                Perform a 'FLUSH TABLES WITH READ LOCK'.
                This is a 3 step process:
                - [1] lock_global_read_lock()
                - [2] close_cached_tables()
                - [3] make_global_read_lock_block_commit()
                [1] prevents new connections from obtaining tables locked for write.
                [2] waits until all existing connections close their tables.
                [3] prevents transactions from being committed.
              */

            Can there be a variant that doesn't do #2? My workload doesn't use MyISAM and I don't know if #2 is done because of MyISAM. Calling close_cached_tables seems like a heavy way to force LOCK TABLEs to be unlocked. Any long running queries will cause #2 to block.
            		 It can be dangerous to run "set read_only" on a production server because it can block in close_cached_tables. More details about the pain this caused previously are at:
            http://mysqlha.blogspot.com/2008/07/what-exactly-does-flush-tables-with.html

            Per the code in set_var.cc:
            {code}
             /*
                Perform a 'FLUSH TABLES WITH READ LOCK'.
                This is a 3 step process:
                - [1] lock_global_read_lock()
                - [2] close_cached_tables()
                - [3] make_global_read_lock_block_commit()
                [1] prevents new connections from obtaining tables locked for write.
                [2] waits until all existing connections close their tables.
                [3] prevents transactions from being committed.
              */
            {code}
            Can there be a variant that doesn't do #2? My workload doesn't use MyISAM and I don't know if #2 is done because of MyISAM. Calling close_cached_tables seems like a heavy way to force LOCK TABLEs to be unlocked. Any long running queries will cause #2 to block.
            serg Sergei Golubchik made changes -
            Workflow jira [ 10634 ] defaullt [ 10770 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Comment [ By Domas:

            essentially we want to be able to use:

            SELECT * FROM ( SELECT ... WHERE x=123 LIMIT 1000000 ) WHERE y=456 LIMIT 1000;

            Mostly this is to allow paging over large datasets that don't have that great selectivity - so we want to be able to return within reasonable time, even if x=123 condition matches billions of rows, but
            y=456 matches very few.
            Currently the internal subselect would get materialized by MySQL and wouldn't be used as streaming dataset, which makes it prohibitive on OLTP machines.

            In certain cases having DISTINCT not materialize would also be extremely useful for us, but that would probably somewhat different task? (we want to optimize b=Y,c=Z fetches on (a,b,c) indexes).
            ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Labels paid-for pf1
            serg Sergei Golubchik made changes -
            Description It can be dangerous to run "set read_only" on a production server because it can block in close_cached_tables. More details about the pain this caused previously are at:
            http://mysqlha.blogspot.com/2008/07/what-exactly-does-flush-tables-with.html

            Per the code in set_var.cc:
            {code}
             /*
                Perform a 'FLUSH TABLES WITH READ LOCK'.
                This is a 3 step process:
                - [1] lock_global_read_lock()
                - [2] close_cached_tables()
                - [3] make_global_read_lock_block_commit()
                [1] prevents new connections from obtaining tables locked for write.
                [2] waits until all existing connections close their tables.
                [3] prevents transactions from being committed.
              */
            {code}
            Can there be a variant that doesn't do #2? My workload doesn't use MyISAM and I don't know if #2 is done because of MyISAM. Calling close_cached_tables seems like a heavy way to force LOCK TABLEs to be unlocked. Any long running queries will cause #2 to block.
            		 It can be dangerous to run "set read_only" on a production server because it can block in close_cached_tables. More details about the pain this caused previously are at:
            http://mysqlha.blogspot.com/2008/07/what-exactly-does-flush-tables-with.html

            Per the code in set_var.cc:
            {code}
             /*
                Perform a 'FLUSH TABLES WITH READ LOCK'.
                This is a 3 step process:
                - [1] lock_global_read_lock()
                - [2] close_cached_tables()
                - [3] make_global_read_lock_block_commit()
                [1] prevents new connections from obtaining tables locked for write.
                [2] waits until all existing connections close their tables.
                [3] prevents transactions from being committed.
              */
            {code}
            Can there be a variant that doesn't do #2? My workload doesn't use MyISAM and I don't know if #2 is done because of MyISAM. Calling close_cached_tables seems like a heavy way to force LOCK TABLEs to be unlocked. Any long running queries will cause #2 to block.

            See also http://lists.mysql.com/commits/142825
            ratzpo Rasmus Johansson (Inactive) made changes -
            Assignee Rasmus Johansson [ ratzpo ] Michael Widenius [ monty ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Due Date 2012-05-13
            holyfoot Alexey Botchkov made changes -
            Assignee Michael Widenius [ monty ] Alexey Botchkov [ holyfoot ]
            serg Sergei Golubchik made changes -
            Fix Version/s 5.3.8 [ 10900 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Assignee Alexey Botchkov [ holyfoot ] Sergei Golubchik [ serg ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Due Date 2012-05-13 2012-05-16
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexey Botchkov [ holyfoot ]
            holyfoot Alexey Botchkov made changes -
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Workflow defaullt [ 10770 ] MariaDB v2 [ 45004 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 45004 ] MariaDB v3 [ 65512 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 65512 ] MariaDB v4 [ 131902 ]

            People

              holyfoot Alexey Botchkov
              ratzpo Rasmus Johansson (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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