1

I want to alter a table column from CHAR(2) utf8mb4 to CHAR(2) latin1 (so it will use 2 bytes instead of 8, utf8 is not needed because they are country codes so there are only latin characters).

This table receives about 50 new rows per second.

There are about 125 million rows, total table size 5GB, so the alter will take a wile.

How can I alter the table without locking the writes?

3

Issue 1 - How to do the ALTER without blocking everything too badly. Answer: use pt-online-schema-change.

Issue 2 - Storage for char-like datatypes.

In the past, CHAR(n) always reserved n*c bytes, where c is the maximum size based on the CHARACTER SET: 1 for latin1, 3 for utf8, 4 for utf8mb4. This was sometimes handy for Engine=MyISAM with FIXED sized rows.

In certain ROW_FORMATs of InnoDB in new enough versions (sorry, I don't have the specifics), CHAR(n) will occupy between n and n*c bytes, depending on the actual characters needed. With InnoDB, there is no concept of fixed sized rows.

A complex SELECT that needs a temp table (say for ORDER BY), will try to use a MEMORY table for such. In that case, the length of CHAR(n) and VARCHAR(n) will always be n*c. This sometimes leads to an inefficiency. Or it might use MyISAM for the tmp table. Version 8.0 will be moving toward using InnoDB for tmp tables, so this paragraph will eventually be moot.

Converting CHAR to VARCHAR and/or converting the column from one CHARACTER SET to another requires a heavy-duty ALTER (not INPLACE). See https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html That link is for 5.6; there have been changes; pick the page for your version.

VARCHAR has 1 or 2 bytes for the length, plus only the number of bytes needed for the actual characters. So it is 1+n*c for short max len.

  • 1
    bugs.mysql.com/84440 filed – Rick James Jan 7 '17 at 18:29
  • Thank you for the precious information, I was misleaded by that docs page. Anyway, there is a small saving in space, after doing the change (thanks to pt-online-schema-change), the table size is reduced from 5.0 to 4.8GB – the_nuts Jan 9 '17 at 14:09
  • A few doc page(s) are misleading, hence my bug report. Generally they stem from out-of-date information. – Rick James Jan 9 '17 at 17:30
2

CHAR(2) in UTF8MB is stored as VARCHAR(2*4) in latin1. So you won't save much - just one byte on a length field .

UPDATE:

A table t1:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `country` char(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

With two records:

mysql> SELECT * FROM t1;
+----+---------+
| id | country |
+----+---------+
|  1 | ua      |
|  2 | us      |
+----+---------+
2 rows in set (0.00 sec)

Looks like this on disk:

0000c060  02 00 1c 69 6e 66 69 6d  75 6d 00 03 00 0b 00 00  |...infimum......|
0000c070  73 75 70 72 65 6d 75 6d  02 00 00 00 10 00 1a 80  |supremum........|
0000c080  00 00 01 00 00 00 00 31  09 a9 00 00 01 1d 01 10  |.......1........|
0000c090  75 61 02 00 00 00 18 ff  d7 80 00 00 02 00 00 00  |ua..............|
0000c0a0  00 31 0a aa 00 00 01 1e  01 10 75 73 00 00 00 00  |.1........us....|

Let's split it.

Record 1:

  • 0x80000001 that would be a signed INT the primary key
  • 0x000000003109 - six bytes transaction id
  • 0xa90000011d0110 - seven bytes rollback pointer
  • 0x7561 - that would be a "ua" value in hex. Two bytes, not four.

Following bytes belong to the second record:

  • 0x02 - it's a length of country value - two bytes. The length itself uses one byte because CHAR(2) can use up to 2*4 bytes, which is less than 256.
  • 00 - NULL values map. country can be NULL, so at least one byte to encode a NULL value.
  • 0x000018ffd7 - five bytes of so called "extra bytes" field.
  • 0x80000002 - primary key of the second record - two.
  • 0x00000000310a - trx_id
  • 0xaa0000011e0110 - roll_ptr
  • 0x7573 - "us"

So, that was a COMPACT and DYNAMIC format.

Let's do same exercise for REDUNDANT format - the oldest InnoDB format available since versions 4.0.*+

0000c060  00 00 03 00 87 69 6e 66  69 6d 75 6d 00 09 03 00  |.....infimum....|
0000c070  08 03 00 00 73 75 70 72  65 6d 75 6d 00 19 11 0a  |....supremum....|
0000c080  04 00 00 10 09 00 aa 80  00 00 01 00 00 00 00 31  |...............1|
0000c090  14 b2 00 00 01 26 01 10  75 61 20 20 20 20 20 20  |.....&..ua      |
0000c0a0  19 11 0a 04 00 00 18 09  00 74 80 00 00 02 00 00  |.........t......|
0000c0b0  00 00 31 15 b3 00 00 01  27 01 10 75 73 20 20 20  |..1.....'..us   |
0000c0c0  20 20 20 19 11 0a 04 00  00 20 09 00 74 80 00 00  |   ...... ..t...|

Differences:

  • "Extra bytes" is six bytes long.
  • There is length for each field, including fix-length.
  • CHAR(2) is stored with trailing spaces to pad to eight bytes (2*4).
  • Are you sure? Here it says "MySQL must reserve four bytes for each character in a CHAR CHARACTER SET utf8mb4 column because that is the maximum possible length. For example, MySQL must reserve 40 bytes for a CHAR(10) CHARACTER SET utf8mb4 column" dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8mb4.html – the_nuts Jan 7 '17 at 16:49
  • 1
    No to 2*4. The length in both CHAR and VARCHAR counts characters, not bytes. – Rick James Jan 7 '17 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.