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. 

        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:

                Smart Checklist