You are here

What's going on when MySQL does operations on Partitions

Following question came up recently: What happens if you drop or add a partition of an existing already partitioned table with ALTER TABLE? Will it be copied or will just the single partition be dropped and added? How are the index(es) rebuild after such a drop/add?

In the MySQL documentation were not to many details mentioned:

ALTER TABLE ... ADD PARTITION creates no temporary table except for MySQL Cluster.
...
ADD or DROP operations for RANGE or LIST partitions are immediate operations or nearly so. ADD or COALESCE operations for HASH or KEY partitions copy data between changed partitions
...
If other cases, MySQL creates a temporary table, even if the data wouldn't strictly need to be copied ...

So it would be interesting to find out what happens. Because I am not a developer I am not able to read code. So I have to find it out somehow different:

  1. We need the following Tool: MySQL Super Smack
  2. With gen-data from MySQL Super Smack we generate some data:
  3. ./gen-data -n 1000000 -f '%n,%d,%12-12s,%5-25s' > rows.txt
    
  4. Then we create a table into MySQL 5.1 were we want to run our tests:
  5. CREATE TABLE test (
        id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
      , lauf INT UNSIGNED NOT NULL
      , fix  CHAR(12)     NOT NULL
      , dyn  VARCHAR(25)  NOT NULL
    );
    
  6. Now the data can be loaded:
  7. LOAD DATA
    INFILE '~/super-smack/bin/rows.txt'
    INTO TABLE test
    FIELDS TERMINATED BY ',';
    

    A short check shows us roughly how long operations are expected to run:

    mysql> SELECT COUNT(*) FROM (SELECT * FROM test) x;
    +----------+
    | count(*) |
    +----------+
    |  1000000 |
    +----------+
    1 row in set (3.30 sec)
    
  8. Now we need an other tool called MySQL Profiler to see what happens. Also mytop could help here. To gather data with MySQL Profiler we first have to start the tracer who collects the data:
  9. ./tracer.pl -i 20
    
  10. Then immediately after you started the tracer you should start the SQL command. And immediately the SQL command finished you should stop the trace with ^C to not collect to much idling time:
  11. ALTER TABLE test
        PARTITION BY HASH(id)
        PARTITIONS 8;
    Query OK, 1000000 rows affected (9.85 sec)
    Records: 1000000  Duplicates: 0  Warnings: 0
    
  12. Now you can analyse the data with the profiler:
  13. ./profiler.pl --pid=3 -t mysql_processlist.trc.15256
    
    General infos
    -------------
    Slots         :      549
    Time          :       12.107 s
    Interval      :        0.022 s
    Filters       : pid = 3,
    Lines total   :     3088
    Lines skipped :      892
    Lines matched :     2196
    
    Commands
    --------
    Query                            446          9.835 s    81.2 %
    Sleep                            103          2.271 s    18.8 %
    -------------------------   --------   --------------   -------
    Total                            549         12.107 s   100.0 %
    
    State
    -----
    copy to tmp table                441          9.725 s    80.3 %
    Idling                           103          2.271 s    18.8 %
    creating table                     3          0.066 s     0.5 %
    rename result table                2          0.044 s     0.4 %
    -------------------------   --------   --------------   -------
    Total                            549         12.107 s   100.0 %
    

    We can see here, that most of the time (80.3%) was used to copy data to a temporary table to convert a conventional table to a partitioned table.

    Caution: Because we only have rough time slots (here 20 ms) this results are not always 100% correct!

  14. Next test is to add a new index to our partitioned table:
  15. ALTER TABLE test ADD index dyn_i (dyn);
    Query OK, 1000000 rows affected (14.45 sec)
    Records: 1000000  Duplicates: 0  Warnings: 0
    
    General infos
    -------------
    Slots         :      752
    Time          :       16.636 s
    Interval      :        0.022 s
    Filters       : pid = 3,
    Lines total   :     2256
    Lines skipped :        0
    Lines matched :     2256
    
    Commands
    --------
    Query                            651         14.401 s    86.6 %
    Sleep                            101          2.234 s    13.4 %
    -------------------------   --------   --------------   -------
    Total                            752         16.636 s   100.0 %
    
    State
    -----
    copy to tmp table                373          8.251 s    49.6 %
    Repair by sorting                273          6.039 s    36.3 %
    Idling                           101          2.234 s    13.4 %
    creating table                     3          0.066 s     0.4 %
    rename result table                2          0.044 s     0.3 %
    -------------------------   --------   --------------   -------
    Total                            752         16.636 s   100.0 %
    

    We can see here that coping the data to a temporary table and the following repair (index build) took most of the time. This seems to be still the old bad behaviour of MySQL!

  16. First we wanted to DROP a partition. But this is not possible with HASH partitions. So we try a COALESCE:
  17. ALTER TABLE test COALESCE PARTITION 2;
    Query OK, 0 rows affected (1 min 0.87 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    General infos
    -------------
    Slots         :     2864
    Time          :       64.118 s
    Interval      :        0.022 s
    Filters       : pid = 3,
    Lines total   :     8592
    Lines skipped :        0
    Lines matched :     8592
    
    Commands
    --------
    Query                           2715         60.782 s    94.8 %
    Sleep                            149          3.336 s     5.2 %
    -------------------------   --------   --------------   -------
    Total                           2864         64.118 s   100.0 %
    
    State
    -----
    setup                           2709         60.648 s    94.6 %
    Idling                           149          3.336 s     5.2 %
    end                                6          0.134 s     0.2 %
    -------------------------   --------   --------------   -------
    Total                           2864         64.118 s   100.0 %
    

    Here we can see a new status never seen before: set-up. This seems to be new 5.1 technology for partitioning?

  18. But we still want to see how MySQL behaves dropping and adding partitions. So we have to convert our table to a RANGE partitioned table:
  19. ALTER TABLE test
    PARTITION BY RANGE (id) (
        PARTITION p0100k VALUES LESS THAN (0100001)
      , PARTITION p0200k VALUES LESS THAN (0200001)
      , PARTITION p0300k VALUES LESS THAN (0300001)
      , PARTITION p0400k VALUES LESS THAN (0400001)
      , PARTITION p0500k VALUES LESS THAN (0500001)
      , PARTITION p0600k VALUES LESS THAN (0600001)
      , PARTITION p0700k VALUES LESS THAN (0700001)
      , PARTITION p0800k VALUES LESS THAN (0800001)
      , PARTITION p0900k VALUES LESS THAN (0900001)
      , PARTITION p1000k VALUES LESS THAN (1000001)
      , PARTITION p1100k VALUES LESS THAN (1100001)
    )
    ;
    Query OK, 1000000 rows affected (18.15 sec)
    Records: 1000000  Duplicates: 0  Warnings: 0
    
    General infos
    -------------
    Slots         :     1082
    Time          :       23.997 s
    Interval      :        0.022 s
    Filters       : pid = 3,
    Lines total   :    13893
    Lines skipped :    10647
    Lines matched :     3246
    
    Commands
    --------
    Query                            819         18.164 s    75.7 %
    Sleep                            263          5.833 s    24.3 %
    -------------------------   --------   --------------   -------
    Total                           1082         23.997 s   100.0 %
    
    State
    -----
    copy to tmp table                550         12.198 s    50.8 %
    Idling                           263          5.833 s    24.3 %
    Repair by sorting                262          5.811 s    24.2 %
    creating table                     3          0.067 s     0.3 %
    rename result table                3          0.067 s     0.3 %
    end                                1          0.022 s     0.1 %
    -------------------------   --------   --------------   -------
    Total                           1082         23.997 s   100.0 %
    
  20. Now we are able to first ADD a partition:
  21. ALTER TABLE test
    ADD PARTITION (
        PARTITION p1200k VALUES LESS THAN (1200001)
    );
    Query OK, 0 rows affected (0.14 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    General infos
    -------------
    Slots         :        6
    Time          :        0.111 s
    Interval      :        0.018 s
    Filters       : pid = 3,
    Lines total   :        6
    Lines skipped :        0
    Lines matched :        6
    
    Commands
    --------
    Query                              6          0.111 s   100.0 %
    -------------------------   --------   --------------   -------
    Total                              6          0.111 s   100.0 %
    
    State
    -----
    setup                              6          0.111 s   100.0 %
    -------------------------   --------   --------------   -------
    Total                              6          0.111 s   100.0 %
    

    This seems pretty fast. So we can assume that no needles coping around of data is done.

  22. And now we want to drop a full partition:
  23. ALTER TABLE test
    DROP PARTITION p0800k
    ;
    Query OK, 0 rows affected (0.52 sec)
    
    General infos
    -------------
    Slots         :       23
    Time          :        0.502 s
    Interval      :        0.022 s
    Filters       : pid = 3,
    Lines total   :       23
    Lines skipped :        0
    Lines matched :       23
    
    Commands
    --------
    Query                             23          0.502 s   100.0 %
    -------------------------   --------   --------------   -------
    Total                             23          0.502 s   100.0 %
    
    State
    -----
    setup                             22          0.480 s    95.7 %
    Writing to net                     1          0.022 s     4.3 %
    -------------------------   --------   --------------   -------
    Total                             23          0.502 s   100.0 %
    

    This also went pretty fast. So also here we can assume, that no copying is done.