Details

    • Technical task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Done
    • N/A
    • N/A
    • Tests
    • None
    • 10.1.29

    Description

      marko wrote:

      DML before export, also DML before DISCARD TABLESPACE
      at least some 100 megabytes of data in the table, with a big buffer pool, so that stale pages will stay around
      maybe just import an empty table (that should be the worst exercise)
      and in fact, you don’t necessarily have to copy large .ibd files during the test; we only need one .ibd file for the empty table to be imported (in a test loop)

      so, basically:

      • create table;
      • loop:
        • insert/replace a lot;
        • alter table...discard tablespace;
        • copy empty file
        • alter table..import tablespace;
      • goto loop

      and of course, multiple threads in parallel, each using their own table
      and lots of secondary indexes


      Test description

      The implemented test is limited to what's prescribed in the request above, with only one addition – at the step "copy empty file", during the course of the test we alternate between using empty tablespaces and tablespaces with data.

      Since the test is expected to use rather big tables, which take a lot of time to create, in order to speed up the process, the test is performed in a half-automated-half-manual mode.
      One-time activity (manual):

      • server is started with default parameters;
      • tables are pre-created;
      • tables are flushed for export;
      • tablespaces are exported and stored manually;
      • tables are unlocked;
      • tables are populated;
      • tables are flushed for export;
      • tablespaces are exported and stored manually;
      • tables are unlocked;
      • server is shut down;
      • the whole data directory is stored along with the exported/stored tablespaces.

      Repeated activity;

      • the stored data directory is copied back;
      • server is started manually with parameters which are needed for the current test;
      • an automated test is performed on the running server;
      • server is shut down manually.

      Data structures

      CREATE TABLE t1 ( pk INT AUTO_INCREMENT PRIMARY KEY, f1 INT, f2 INT NOT NULL, f3 BIGINT, f4 BIGINT NOT NULL, f5 VARCHAR(1024), f6 VARCHAR(2048) NOT NULL, f7 CHAR(255), f8 CHAR(128) NOT NULL, UNIQUE(f1), UNIQUE(f7), KEY(f2,f5(64)), KEY(f3,f6(128)), KEY(f4,f7), KEY(f1,f8) ) ENGINE=INNODB ROW_FORMAT = COMPACT ;
      CREATE TABLE t2 ( pk INT AUTO_INCREMENT PRIMARY KEY, f1 INT, f2 INT NOT NULL, f3 BIGINT, f4 BIGINT NOT NULL, f5 VARCHAR(1024), f6 VARCHAR(2048) NOT NULL, f7 CHAR(255), f8 CHAR(128) NOT NULL, UNIQUE(f1), UNIQUE(f7), KEY(f2,f5(64)), KEY(f3,f6(128)), KEY(f4,f7), KEY(f1,f8) ) ENGINE=INNODB ROW_FORMAT = REDUNDANT ;
      set global innodb_file_format=Barracuda;
      CREATE TABLE t3 ( pk INT AUTO_INCREMENT PRIMARY KEY, f1 INT, f2 INT NOT NULL, f3 BIGINT, f4 BIGINT NOT NULL, f5 VARCHAR(1024), f6 VARCHAR(2048) NOT NULL, f7 CHAR(255), f8 CHAR(128) NOT NULL, UNIQUE(f1), UNIQUE(f7), KEY(f2,f5(64)), KEY(f3,f6(128)), KEY(f4,f7), KEY(f1,f8) ) ENGINE=INNODB ROW_FORMAT = COMPRESSED ;
      CREATE TABLE t4 ( pk INT AUTO_INCREMENT PRIMARY KEY, f1 INT, f2 INT NOT NULL, f3 BIGINT, f4 BIGINT NOT NULL, f5 VARCHAR(1024), f6 VARCHAR(2048) NOT NULL, f7 CHAR(255), f8 CHAR(128) NOT NULL, UNIQUE(f1), UNIQUE(f7), KEY(f2,f5(64)), KEY(f3,f6(128)), KEY(f4,f7), KEY(f1,f8) ) ENGINE=INNODB ROW_FORMAT = DYNAMIC ;
      CREATE TABLE t5 ( pk INT AUTO_INCREMENT PRIMARY KEY, f1 INT, f2 INT NOT NULL, f3 BIGINT, f4 BIGINT NOT NULL, f5 VARCHAR(1024), f6 VARCHAR(2048) NOT NULL, f7 CHAR(255), f8 CHAR(128) NOT NULL, UNIQUE(f1), UNIQUE(f7), KEY(f2,f5(64)), KEY(f3,f6(128)), KEY(f4,f7), KEY(f1,f8) ) ENGINE=INNODB ROW_FORMAT = DYNAMIC PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3;
      CREATE TABLE t6 ( pk INT AUTO_INCREMENT PRIMARY KEY, f1 INT, f2 INT NOT NULL, f3 BIGINT, f4 BIGINT NOT NULL, f5 VARCHAR(1024), f6 VARCHAR(2048) NOT NULL, f7 CHAR(255), f8 CHAR(128) NOT NULL, UNIQUE(f1), UNIQUE(f7), KEY(f2,f5(64)), KEY(f3,f6(128)), KEY(f4,f7), KEY(f1,f8) ) ENGINE=INNODB ROW_FORMAT = DYNAMIC PAGE_COMPRESSED=1;
      CREATE TABLE t7 ( pk INT AUTO_INCREMENT PRIMARY KEY, f1 INT, f2 INT NOT NULL, f3 BIGINT, f4 BIGINT NOT NULL, f5 VARCHAR(1024), f6 VARCHAR(2048) NOT NULL, f7 CHAR(255), f8 CHAR(128) NOT NULL, UNIQUE(f1), UNIQUE(f7), KEY(f2,f5(64)), KEY(f3,f6(128)), KEY(f4,f7), KEY(f1,f8) ) ENGINE=INNODB ROW_FORMAT = COMPACT PAGE_COMPRESSED=1;
      CREATE TABLE t8 ( pk INT AUTO_INCREMENT PRIMARY KEY, f1 INT, f2 INT NOT NULL, f3 BIGINT, f4 BIGINT NOT NULL, f5 VARCHAR(1024), f6 VARCHAR(2048) NOT NULL, f7 CHAR(255), f8 CHAR(128) NOT NULL, UNIQUE(f1), UNIQUE(f7), KEY(f2,f5(64)), KEY(f3,f6(128)), KEY(f4,f7), KEY(f1,f8) ) ENGINE=INNODB ROW_FORMAT = COMPACT PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=9;
      

      Initial table contents

      INSERT INTO t1 SELECT NULL, seq, seq%1000, seq%10, seq%10000, CONCAT('f5_',(seq%1000)), CONCAT('f6_',(seq%100)), CONCAT('f7_',seq), CONCAT('f8_',(seq%10)) FROM seq_1_to_10000;
      INSERT INTO t2 SELECT NULL, seq, seq%1000, seq%10, seq%10000, CONCAT('f5_',(seq%1000)), CONCAT('f6_',(seq%100)), CONCAT('f7_',seq), CONCAT('f8_',(seq%10)) FROM seq_1_to_50000;
      INSERT INTO t3 SELECT NULL, seq, seq%1000, seq%10, seq%10000, CONCAT('f5_',(seq%1000)), CONCAT('f6_',(seq%100)), CONCAT('f7_',seq), CONCAT('f8_',(seq%10)) FROM seq_1_to_100000;
      INSERT INTO t4 SELECT NULL, seq, seq%1000, seq%10, seq%10000, CONCAT('f5_',(seq%1000)), CONCAT('f6_',(seq%100)), CONCAT('f7_',seq), CONCAT('f8_',(seq%10)) FROM seq_1_to_200000;
      INSERT INTO t5 SELECT NULL, seq, seq%1000, seq%10, seq%10000, CONCAT('f5_',(seq%1000)), CONCAT('f6_',(seq%100)), CONCAT('f7_',seq), CONCAT('f8_',(seq%10)) FROM seq_1_to_300000;
      INSERT INTO t6 SELECT NULL, seq, seq%1000, seq%10, seq%10000, CONCAT('f5_',(seq%1000)), CONCAT('f6_',(seq%100)), CONCAT('f7_',seq), CONCAT('f8_',(seq%10)) FROM seq_1_to_30000;
      INSERT INTO t7 SELECT NULL, seq, seq%1000, seq%10, seq%10000, CONCAT('f5_',(seq%1000)), CONCAT('f6_',(seq%100)), CONCAT('f7_',seq), CONCAT('f8_',(seq%10)) FROM seq_1_to_80000;
      INSERT INTO t8 SELECT NULL, seq, seq%1000, seq%10, seq%10000, CONCAT('f5_',(seq%1000)), CONCAT('f6_',(seq%100)), CONCAT('f7_',seq), CONCAT('f8_',(seq%10)) FROM seq_1_to_800000;
      

      Stored tablespaces

      -rw-r----- 1 elenst elenst   26214400 Nov  6 13:28 data/test/t1.ibd.backup
      -rw-r----- 1 elenst elenst  104857600 Nov  6 13:28 data/test/t2.ibd.backup
      -rw-r----- 1 elenst elenst   96468992 Nov  6 13:28 data/test/t3.ibd.backup
      -rw-r----- 1 elenst elenst  364904448 Nov  6 13:28 data/test/t4.ibd.backup
      -rw-r----- 1 elenst elenst  515899392 Nov  6 13:29 data/test/t5.ibd.backup
      -rw-r----- 1 elenst elenst   62914560 Nov  6 13:29 data/test/t6.ibd.backup
      -rw-r----- 1 elenst elenst  146800640 Nov  6 13:29 data/test/t7.ibd.backup
      -rw-r----- 1 elenst elenst 1430257664 Nov  6 13:29 data/test/t8.ibd.backup
       
      -rw-r----- 1 elenst elenst 196608 Nov  6 13:28 data/test/t1.ibd.empty
      -rw-r----- 1 elenst elenst 196608 Nov  6 13:28 data/test/t2.ibd.empty
      -rw-r----- 1 elenst elenst  98304 Nov  6 13:28 data/test/t3.ibd.empty
      -rw-r----- 1 elenst elenst 196608 Nov  6 13:28 data/test/t4.ibd.empty
      -rw-r----- 1 elenst elenst 196608 Nov  6 13:28 data/test/t5.ibd.empty
      -rw-r----- 1 elenst elenst 196608 Nov  6 13:28 data/test/t6.ibd.empty
      -rw-r----- 1 elenst elenst 196608 Nov  6 13:28 data/test/t7.ibd.empty
      -rw-r----- 1 elenst elenst 196608 Nov  6 13:28 data/test/t8.ibd.empty
      

      Test results

      Note: Any noticeable deviations and inaccuracies in executing the scenario caused various problems. These problems are considered to be outside the scope of this task and attributed to the general instability of export and DISCARD/IMPORT tablespace functionality. Analysis of such failures was not performed.

      Smoke tests for stability

      Several runs of the described test were performed, all with the same data:

      • 1-thread test on a server with innodb_buffer_pool_size=128M general_log=on, otherwise defaults, for table t1;
      • 8-thread test on a server with innodb_buffer_pool_size=128M general_log=on, otherwise defaults;
      • 8-thread test on a server with innodb_buffer_pool_size=2G general_log=on, otherwise defaults;
      • 8-thread test on a server with innodb_buffer_pool_size=4G general_log=on, otherwise defaults;
      • 8-thread test on a server with innodb_buffer_pool_size=8G general_log=on, otherwise defaults;
      • 8-thread test on a server with innodb_buffer_pool_size=2G general_log=on ignore-builtin-innodb plugin-load-add=ha_innodb, otherwise defaults;

      The duration of the test runs varied from 10 to 30 min. All test runs were performed on a debug build.

      In all variations above, the test was considered a pass if the server didn't crash during the duration of the test.

      On the initial implementation, bb-10.1-marko 1fb7ac18c417c8fbd95, the test caused failure described in this comment. The failure was reproducible via a stress test and MTR.

      The failure stopped happening after a temporary patch and didn't re-appear after the real fix. No other crashes were observed.

      Sanity check for performance regressions

      The same test flow was converted into a performance comparison test. The tests were performed in an environment not tuned for benchmarks, results are not considered in any way accurate, and absolute values are irrelevant. The purpose of the test was to check for critical performance regressions. All tests were run on non-debug builds.

      Several test runs were performed:

      • 1-thread latency comparison* test on servers with innodb_buffer_pool_size=4G general_log=on, otherwise defaults, table t3;
      • 1-thread latency comparison* test on servers with innodb_buffer_pool_size=4G general_log=on, otherwise defaults, table t8;
      • 8-thread throughput comparison** tests on a servers with innodb_buffer_pool_size=8G general_log=on, otherwise defaults;
      • 1-thread throughput comparison** tests on a servers with innodb_buffer_pool_size=8G general_log=on, otherwise defaults, table t5;
      • 8-thread throughput comparison** tests on a servers with innodb_buffer_pool_size=128M general_log=on, otherwise defaults;

      * For the latency comparison test, two servers are running side-by-side. The test executes the same query on both servers one after another, compares the execution time, and reports differences which exceed a given threshold

      ** For the throughput comparison test, only one server is started at a time. All test flow is run on the server for a given time interval, after that the overall number of all queries (including DML) per thread is calculated, and resulting numbers are compared.

      Examples of results of throughput test:

      8-thread 10-min test, buffer pool 8G

      thread/table 10.1 bb-10.1-marko
      1 451 478
      2 283 286
      3 345 425
      4 271 320
      5 233 287
      6 418 418
      7 322 314
      8 381 348

      8-thread 10-min test, buffer pool 128M

      thread/table 10.1 bb-10.1-marko
      1 392 452
      2 286 378
      3 349 349
      4 325 340
      5 302 244
      6 391 468
      7 322 402
      8 381 348

      1-thread 10-min test, buffer pool 8G, table t5

      10.1 bb-10.1-marko
      1043 1043

      yes, exactly the same, not a typo, weird coincidence

      No differences which could not be explained by unreliability of the test environment have been noticed during tests.

      Attachments

        Activity

          People

            elenst Elena Stepanova
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.