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

Import Tablespace takes too long on a busy server

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.7.20-18
    • Fix Version/s: 5.7.21-20
    • Component/s: None

      Description

      Even though with small tables, import tablespace command takes too long to complete in a busy slave server with slave_parallel_workers > 1. pt-pmp output shows always same following function calls for import tablespace thread:

      2 nanosleep,os_thread_sleep(os0thread.cc:303),buf_flush_wait_flushed(buf0flu.cc:2155),log_preflush_pool_modified_pages(log0log.cc:1581),log_make_checkpoint_at(log0log.cc:1581),row_import_cleanup(row0import.cc:2188),row_import_for_mysql(row0import.cc:4005),ha_innobase::discard_or_import_tablespace(ha_innodb.cc:13150),mysql_discard_or_import_tablespace(sql_table.cc:6316),Sql_cmd_discard_import_tablespace::execute(sql_alter.cc:391),mysql_execute_command(sql_parse.cc:5155),mysql_parse(sql_parse.cc:5929),dispatch_command(sql_parse.cc:1493),do_command(sql_parse.cc:1021),handle_connection(connection_handler_per_thread.cc:312),pfs_spawn_thread(pfs.cc:2188),start_thread,clone

      It seems the thread is hung waiting for sync flushing. buffer_flush_sync_waits metric starts incrementing till the command finishes, as follows:

      ------------------------------+

      name count

      ------------------------------+

      buffer_flush_sync_waits 13666

      ------------------------------+

      Even with a busy server the import tablespace command shouldn't take so long to complete. 

      MySQL Community 5.7.20 can also take long to complete, but it's not showing same function calls and also buffer_flush_sync_waits is not incremented.

      2 pthread_cond_wait,wait(os0event.cc:165),os_event::wait_low(os0event.cc:165),buf_dblwr_add_to_batch(buf0dblwr.cc:1110),buf_flush_write_block_low(buf0flu.cc:1099),buf_flush_page(buf0flu.cc:1099),buf_flush_try_neighbors(buf0flu.cc:1453),buf_flush_page_and_try_neighbors(buf0flu.cc:1453),buf_do_flush_list_batch(buf0flu.cc:1780),buf_flush_batch(buf0flu.cc:1780),buf_flush_do_batch(buf0flu.cc:1780),buf_flush_lists(buf0flu.cc:2124),log_preflush_pool_modified_pages(log0log.cc:1510),log_make_checkpoint_at(log0log.cc:1902),row_import_cleanup(row0import.cc:2185),row_import_for_mysql(row0import.cc:4002),ha_innobase::discard_or_import_tablespace(ha_innodb.cc:12311),mysql_discard_or_import_tablespace(sql_table.cc:6050),Sql_cmd_discard_import_tablespace::execute(sql_alter.cc:369),mysql_execute_command(sql_parse.cc:4894),mysql_parse(sql_parse.cc:5612),dispatch_command(sql_parse.cc:1461),do_command(sql_parse.cc:999),handle_connection(connection_handler_per_thread.cc:300),pfs_spawn_thread(pfs.cc:2188),start_thread,clone

      Please find attached file to repeat this case.

      Regards. 

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              zsolt.parragi Zsolt Parragi
              Reporter:
              juan.arruti Juan Arruti
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: