show_slave_status_nolock.patch

   1 # name       : show_slave_status_nolock.patch
   2 # introduced : 12
   3 # maintainer : Oleg
   4 #
   5 #!!! notice !!!
   6 # Any small change to this file in the main branch
   7 # should be done or reviewed by the maintainer!
   8 --- /dev/null
   9 +++ b/patch_info/show_slave_status_nolock.patch
  10 @@ -0,0 +1,6 @@
  11 +File=show_slave_status_nolock.patch
  12 +Name= SHOW SLAVE STATUS NOLOCK
  13 +Version=1.0
  14 +Author=Percona <info@percona.com>
  15 +License=GPL
  16 +Comment= Implement SHOW SLAVE STATUS without lock (STOP SLAVE lock the same mutex what lock SHOW SLAVE STATUS)
  17 --- a/sql/lex.h
  18 +++ b/sql/lex.h
  19 @@ -378,6 +378,7 @@
  20    { "NONE",            SYM(NONE_SYM)},
  21    { "NOT",             SYM(NOT_SYM)},
  22    { "NO_WRITE_TO_BINLOG",  SYM(NO_WRITE_TO_BINLOG)},
  23 +  { "NOLOCK",           SYM(NOLOCK_SYM)},
  24    { "NULL",            SYM(NULL_SYM)},
  25    { "NUMERIC",         SYM(NUMERIC_SYM)},
  26    { "NVARCHAR",                SYM(NVARCHAR_SYM)},
  27 --- a/sql/mysqld.cc
  28 +++ b/sql/mysqld.cc
  29 @@ -2971,6 +2971,7 @@
  30    {"show_relaylog_events", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_RELAYLOG_EVENTS]), SHOW_LONG_STATUS},
  31    {"show_slave_hosts",     (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_SLAVE_HOSTS]), SHOW_LONG_STATUS},
  32    {"show_slave_status",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_SLAVE_STAT]), SHOW_LONG_STATUS},
  33 +  {"show_slave_status_nolock", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_SLAVE_NOLOCK_STAT]), SHOW_LONG_STATUS},
  34    {"show_status",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STATUS]), SHOW_LONG_STATUS},
  35    {"show_storage_engines", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STORAGE_ENGINES]), SHOW_LONG_STATUS},
  36    {"show_table_status",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLE_STATUS]), SHOW_LONG_STATUS},
  37 --- a/sql/sql_lex.h
  38 +++ b/sql/sql_lex.h
  39 @@ -190,6 +190,8 @@
  40    SQLCOM_SHOW_PROFILE, SQLCOM_SHOW_PROFILES,
  41    SQLCOM_SIGNAL, SQLCOM_RESIGNAL,
  42    SQLCOM_SHOW_RELAYLOG_EVENTS,
  43 +  /* SHOW SLAVE STATUS NOLOCK */
  44 +  SQLCOM_SHOW_SLAVE_NOLOCK_STAT,
  45    /*
  46      When a command is added here, be sure it's also added in mysqld.cc
  47      in "struct show_var_st status_vars[]= {" ...
  48 --- a/sql/sql_parse.cc
  49 +++ b/sql/sql_parse.cc
  50 @@ -336,6 +336,7 @@
  51    sql_command_flags[SQLCOM_SHOW_CREATE]=  CF_STATUS_COMMAND;
  52    sql_command_flags[SQLCOM_SHOW_MASTER_STAT]= CF_STATUS_COMMAND;
  53    sql_command_flags[SQLCOM_SHOW_SLAVE_STAT]=  CF_STATUS_COMMAND;
  54 +  sql_command_flags[SQLCOM_SHOW_SLAVE_NOLOCK_STAT]=  CF_STATUS_COMMAND;
  55    sql_command_flags[SQLCOM_SHOW_CREATE_PROC]= CF_STATUS_COMMAND;
  56    sql_command_flags[SQLCOM_SHOW_CREATE_FUNC]= CF_STATUS_COMMAND;
  57    sql_command_flags[SQLCOM_SHOW_CREATE_TRIGGER]=  CF_STATUS_COMMAND;
  58 @@ -2393,12 +2394,17 @@
  59      mysql_mutex_unlock(&LOCK_active_mi);
  60      break;
  61    }
  62 +  case SQLCOM_SHOW_SLAVE_NOLOCK_STAT:
  63    case SQLCOM_SHOW_SLAVE_STAT:
  64    {
  65      /* Accept one of two privileges */
  66      if (check_global_access(thd, SUPER_ACL | REPL_CLIENT_ACL))
  67        goto error;
  68 -    mysql_mutex_lock(&LOCK_active_mi);
  69 +    bool do_lock=SQLCOM_SHOW_SLAVE_NOLOCK_STAT != lex->sql_command;
  70 +    if(do_lock)
  71 +    {
  72 +      mysql_mutex_lock(&LOCK_active_mi);
  73 +    }
  74      if (active_mi != NULL)
  75      {
  76        res = show_master_info(thd, active_mi);
  77 @@ -2409,7 +2415,19 @@
  78                     WARN_NO_MASTER_INFO, ER(WARN_NO_MASTER_INFO));
  79        my_ok(thd);
  80      }
  81 -    mysql_mutex_unlock(&LOCK_active_mi);
  82 +    if(do_lock)
  83 +    {
  84 +      mysql_mutex_unlock(&LOCK_active_mi);
  85 +    }
  86 +    DBUG_EXECUTE_IF("after_show_slave_status",
  87 +                    {
  88 +                      const char act[]=
  89 +                        "now "
  90 +                        "signal signal.after_show_slave_status";
  91 +                      DBUG_ASSERT(opt_debug_sync_timeout > 0);
  92 +                      DBUG_ASSERT(!debug_sync_set_action(current_thd,
  93 +                                                         STRING_WITH_LEN(act)));
  94 +                    };);
  95      break;
  96    }
  97    case SQLCOM_SHOW_MASTER_STAT:
  98 --- a/sql/sql_yacc.yy
  99 +++ b/sql/sql_yacc.yy
 100 @@ -1293,6 +1293,7 @@
 101  %token  STARTS_SYM
 102  %token  START_SYM                     /* SQL-2003-R */
 103  %token  STATUS_SYM
 104 +%token  NOLOCK_SYM                    /* SHOW SLAVE STATUS NOLOCK */
 105  %token  STDDEV_SAMP_SYM               /* SQL-2003-N */
 106  %token  STD_SYM
 107  %token  STOP_SYM
 108 @@ -11111,6 +11112,11 @@
 109            {
 110              Lex->sql_command = SQLCOM_SHOW_SLAVE_STAT;
 111            }
 112 +       /* SHOW SLAVE STATUS NOLOCK */
 113 +        | SLAVE STATUS_SYM NOLOCK_SYM
 114 +          {
 115 +           Lex->sql_command = SQLCOM_SHOW_SLAVE_NOLOCK_STAT; //SQLCOM_SHOW_SLAVE_NOLOCK_STAT;
 116 +          }
 117          | QUERY_RESPONSE_TIME_SYM wild_and_where
 118           {
 119  #ifdef HAVE_RESPONSE_TIME_DISTRIBUTION
 120 --- /dev/null
 121 +++ b/mysql-test/t/percona_show_slave_status_nolock.test
 122 @@ -0,0 +1,90 @@
 123 +--source include/master-slave.inc
 124 +--source include/have_debug_sync.inc
 125 +--source include/have_binlog_format_statement.inc
 126 +
 127 +call mtr.add_suppression("Slave SQL: Request to stop slave SQL Thread received while applying a group that has non-transactional changes");
 128 +
 129 +--let $rpl_connection_name=slave_lock
 130 +--let $rpl_server_number=2
 131 +--source include/rpl_connect.inc
 132 +
 133 +--let $rpl_connection_name=slave_nolock
 134 +--let $rpl_server_number=2
 135 +--source include/rpl_connect.inc
 136 +
 137 +--let $show_statement= SHOW PROCESSLIST
 138 +--let $field= Info
 139 +
 140 +connection master;
 141 +--echo [master]
 142 +--disable_warnings
 143 +DROP TABLE IF EXISTS t;
 144 +--enable_warnings
 145 +CREATE TABLE t(id INT);
 146 +sync_slave_with_master;
 147 +
 148 +connection slave;
 149 +--echo [slave]
 150 +SET DEBUG_SYNC='RESET';
 151 +SET GLOBAL DEBUG="+d,after_mysql_insert,after_show_slave_status";
 152 +
 153 +connection master;
 154 +--echo [master]
 155 +INSERT INTO t VALUES(0);
 156 +
 157 +connection slave;
 158 +--echo [slave]
 159 +--let $condition= 'INSERT INTO t VALUES(0)'
 160 +--source include/wait_show_condition.inc
 161 +
 162 +--echo check 'SHOW SLAVE STATUS' and 'SHOW SLAVE STATUS NOLOCK' - both should work fine
 163 +--source include/percona_show_slave_status_nolock.inc
 164 +
 165 +connection master;
 166 +--echo [master]
 167 +INSERT INTO t VALUES(1);
 168 +
 169 +connection slave;
 170 +--echo [slave]
 171 +--let $condition= 'INSERT INTO t VALUES(1)'
 172 +--source include/wait_show_condition.inc
 173 +
 174 +--let $rpl_connection_name=slave_stop
 175 +--let $rpl_server_number=2
 176 +--source include/rpl_connect.inc
 177 +
 178 +connection slave_stop;
 179 +--echo [slave_stop]
 180 +send STOP SLAVE;
 181 +
 182 +connection slave;
 183 +--echo [slave]
 184 +--let $condition= 'STOP SLAVE'
 185 +--source include/wait_show_condition.inc
 186 +
 187 +--echo check 'SHOW SLAVE STATUS' and 'SHOW SLAVE STATUS NOLOCK' - just NOLOCK version should works fine
 188 +--source include/percona_show_slave_status_nolock.inc
 189 +
 190 +
 191 +connection slave_stop;
 192 +--echo [slave_stop]
 193 +reap;
 194 +--source include/wait_for_slave_to_stop.inc
 195 +START SLAVE;
 196 +--source include/wait_for_slave_to_start.inc
 197 +
 198 +connection master;
 199 +--echo [master]
 200 +SET DEBUG_SYNC='RESET';
 201 +
 202 +connection slave;
 203 +--echo [slave]
 204 +SET GLOBAL DEBUG='';
 205 +SET DEBUG_SYNC='RESET';
 206 +
 207 +connection master;
 208 +--echo [master]
 209 +DROP TABLE t;
 210 +sync_slave_with_master;
 211 +
 212 +--source include/rpl_end.inc
 213 --- /dev/null
 214 +++ b/mysql-test/r/percona_show_slave_status_nolock.result
 215 @@ -0,0 +1,69 @@
 216 +include/master-slave.inc
 217 +[connection master]
 218 +call mtr.add_suppression("Slave SQL: Request to stop slave SQL Thread received while applying a group that has non-transactional changes");
 219 +include/rpl_connect.inc [creating slave_lock]
 220 +include/rpl_connect.inc [creating slave_nolock]
 221 +[master]
 222 +DROP TABLE IF EXISTS t;
 223 +CREATE TABLE t(id INT);
 224 +[slave]
 225 +SET DEBUG_SYNC='RESET';
 226 +SET GLOBAL DEBUG="+d,after_mysql_insert,after_show_slave_status";
 227 +[master]
 228 +INSERT INTO t VALUES(0);
 229 +[slave]
 230 +check 'SHOW SLAVE STATUS' and 'SHOW SLAVE STATUS NOLOCK' - both should work fine
 231 +
 232 +[slave_lock]
 233 +SHOW SLAVE STATUS;
 234 +SET DEBUG_SYNC='now WAIT_FOR signal.after_show_slave_status TIMEOUT 1';
 235 +SIGNAL after SHOW SLAVE STATUS is 'signal.after_show_slave_status'
 236 +[slave]
 237 +SET DEBUG_SYNC='now SIGNAL signal.empty';
 238 +[slave_nolock]
 239 +SHOW SLAVE STATUS NOLOCK;
 240 +SET DEBUG_SYNC='now WAIT_FOR signal.after_show_slave_status TIMEOUT 1';
 241 +# should be 'signal.after_show_slave_status'
 242 +SIGNAL after SHOW SLAVE STATUS NOLOCK is 'signal.after_show_slave_status'
 243 +[slave]
 244 +SET DEBUG_SYNC='now SIGNAL signal.continue';
 245 +[slave]
 246 +SET DEBUG_SYNC='now SIGNAL signal.empty';
 247 +
 248 +[master]
 249 +INSERT INTO t VALUES(1);
 250 +[slave]
 251 +include/rpl_connect.inc [creating slave_stop]
 252 +[slave_stop]
 253 +STOP SLAVE;
 254 +[slave]
 255 +check 'SHOW SLAVE STATUS' and 'SHOW SLAVE STATUS NOLOCK' - just NOLOCK version should works fine
 256 +
 257 +[slave_lock]
 258 +SHOW SLAVE STATUS;
 259 +SET DEBUG_SYNC='now WAIT_FOR signal.after_show_slave_status TIMEOUT 1';
 260 +SIGNAL after SHOW SLAVE STATUS is 'signal.empty'
 261 +[slave]
 262 +SET DEBUG_SYNC='now SIGNAL signal.empty';
 263 +[slave_nolock]
 264 +SHOW SLAVE STATUS NOLOCK;
 265 +SET DEBUG_SYNC='now WAIT_FOR signal.after_show_slave_status TIMEOUT 1';
 266 +# should be 'signal.after_show_slave_status'
 267 +SIGNAL after SHOW SLAVE STATUS NOLOCK is 'signal.after_show_slave_status'
 268 +[slave]
 269 +SET DEBUG_SYNC='now SIGNAL signal.continue';
 270 +[slave]
 271 +SET DEBUG_SYNC='now SIGNAL signal.empty';
 272 +
 273 +[slave_stop]
 274 +include/wait_for_slave_to_stop.inc
 275 +START SLAVE;
 276 +include/wait_for_slave_to_start.inc
 277 +[master]
 278 +SET DEBUG_SYNC='RESET';
 279 +[slave]
 280 +SET GLOBAL DEBUG='';
 281 +SET DEBUG_SYNC='RESET';
 282 +[master]
 283 +DROP TABLE t;
 284 +include/rpl_end.inc
 285 --- /dev/null
 286 +++ b/mysql-test/include/percona_show_slave_status_nolock.inc
 287 @@ -0,0 +1,56 @@
 288 +--echo
 289 +--disable_result_log
 290 +connection slave_lock;
 291 +--echo [slave_lock]
 292 +send SHOW SLAVE STATUS;
 293 +
 294 +connection slave;
 295 +--let $condition= 'SHOW SLAVE STATUS'
 296 +--source include/wait_show_condition.inc
 297 +
 298 +--disable_warnings
 299 +SET DEBUG_SYNC='now WAIT_FOR signal.after_show_slave_status TIMEOUT 1';
 300 +--enable_warnings
 301 +
 302 +--let current=`SELECT SUBSTR(Variable_value FROM 22) FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE Variable_name = 'DEBUG_SYNC'`
 303 +--echo SIGNAL after SHOW SLAVE STATUS is $current
 304 +
 305 +connection slave;
 306 +--echo [slave]
 307 +SET DEBUG_SYNC='now SIGNAL signal.empty';
 308 +
 309 +connection slave_nolock;
 310 +--echo [slave_nolock]
 311 +send SHOW SLAVE STATUS NOLOCK;
 312 +
 313 +connection slave;
 314 +--let $condition= 'SHOW SLAVE STATUS NOLOCK'
 315 +--source include/wait_show_condition.inc
 316 +
 317 +--disable_warnings
 318 +SET DEBUG_SYNC='now WAIT_FOR signal.after_show_slave_status TIMEOUT 1';
 319 +--enable_warnings
 320 +
 321 +--echo # should be 'signal.after_show_slave_status'
 322 +--let current=`SELECT SUBSTR(Variable_value FROM 22) FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE Variable_name = 'DEBUG_SYNC'`
 323 +--echo SIGNAL after SHOW SLAVE STATUS NOLOCK is $current
 324 +
 325 +connection slave;
 326 +--echo [slave]
 327 +SET DEBUG_SYNC='now SIGNAL signal.continue';
 328 +
 329 +connection slave_lock;
 330 +--disable_result_log
 331 +reap;
 332 +--enable_result_log
 333 +
 334 +connection slave_nolock;
 335 +--disable_result_log
 336 +reap;
 337 +--enable_result_log
 338 +
 339 +connection slave;
 340 +--echo [slave]
 341 +SET DEBUG_SYNC='now SIGNAL signal.empty';
 342 +--enable_result_log
 343 +--echo
 344 --- a/sql/slave.cc
 345 +++ b/sql/slave.cc
 346 @@ -1816,6 +1816,7 @@
 347
 348    if (mi->host[0])
 349    {
 350 +    bool do_lock=SQLCOM_SHOW_SLAVE_NOLOCK_STAT != thd->lex->sql_command;
 351      DBUG_PRINT("info",("host is set: '%s'", mi->host));
 352      String *packet= &thd->packet;
 353      protocol->prepare_for_resend();
 354 @@ -1824,9 +1825,15 @@
 355        slave_running can be accessed without run_lock but not other
 356        non-volotile members like mi->io_thd, which is guarded by the mutex.
 357      */
 358 -    mysql_mutex_lock(&mi->run_lock);
 359 +    if (do_lock)
 360 +    {
 361 +      mysql_mutex_lock(&mi->run_lock);
 362 +    }
 363      protocol->store(mi->io_thd ? mi->io_thd->proc_info : "", &my_charset_bin);
 364 -    mysql_mutex_unlock(&mi->run_lock);
 365 +    if (do_lock)
 366 +    {
 367 +      mysql_mutex_unlock(&mi->run_lock);
 368 +    }
 369
 370      mysql_mutex_lock(&mi->data_lock);
 371      mysql_mutex_lock(&mi->rli.data_lock);