SHOW SLAVE STATUS allocates ~1.7 MB per call due to oversized VARCHAR columns

SHOW SLAVE STATUS (and its information_schema equivalent, INFORMATION_SCHEMA.SLAVE_STATUS) builds an in-memory temporary table to hold one row of status data. Many of its columns are declared as a maximum-length VARCHAR, which makes the server reserve 65,535 bytes per such column in the record buffer – even though the real value is almost always a few dozen bytes. The record is also allocated twice in the temporary table.

The net effect is roughly 1.7 MB allocated for every SHOW SLAVE STATUS (record length ~= 880,889 bytes x 2). This is wasteful, causes memory fragmentation, and is slow to handle because the server is shuffling very large rows around. The fix is to store these columns as BLOB (which keeps only a reference to the string data) instead of a giant fixed VARCHAR.

The columns of SHOW SLAVE STATUS are described by an array in sql/sql_show.cc (the slave_status_info[] array):

ST_FIELD_INFO slave_status_info[]=

{

  Column("Connection_name", Name(), NOT_NULL),

  ...

  Column("Replicate_Do_DB", Varchar(), NOT_NULL),

  ...

};

Each Column(...) uses a small helper type defined in sql/sql_i_s.h. The relevant ones are:

• Varchar(length) – a VARCHAR(length).
• Varchar() (no argument) – this is the problem. Its definition is:

  Varchar(): Type(&type_handler_varchar, MAX_FIELD_VARCHARLENGTH/3, false) {}

  MAX_FIELD_VARCHARLENGTH is 65535, so a no-argument Varchar() is a maximum-size VARCHAR. When the temporary table record is built, the server reserves the full width for it.

• Blob(length) – a BLOB column stores only a pointer + length in the record (the actual bytes live elsewhere), so it costs a handful of bytes in the record buffer regardless of how big the value can be.

Several slave_status_info columns are declared with the bare Varchar(), e.g. Replicate_Do_DB, Replicate_Ignore_DB, and Gtid_Slave_Pos. Each of these costs ~64 KB in the record, twice.

What to change

1. Switch the oversized columns from Varchar() to Blob()

In slave_status_info[] (sql/sql_show.cc), change the columns currently declared as a bare Varchar() to Blob(MAX_FIELD_VARCHARLENGTH). This keeps the same maximum capacity but stops the server from reserving 64 KB per column in the record.

2. Make sure the values are stored safely

The values come from two places, and each needs a small bit of care so the pointer that the BLOB stores stays valid:

• Values that come straight from Master_info (mi) – these can use the pointer to the data already living in mi. This is safe because a lock is held over mi while the row is being copied and stored, so the data won't change or disappear underneath us.
• Values we generate on the fly – for example mi->gtid_current_pos.to_string(&tmp). For these, copy the generated string into the statement's MEM_ROOT before calling store(), so the buffer outlives the temporary StringBuffer and stays valid for the lifetime of the row.

3. Also reconsider Longtext

The Longtext helper in sql/sql_i_s.h is currently built on type_handler_varchar:

class Longtext: public Type

{

public:

  Longtext(uint length) :Type(&type_handler_varchar, length, false) { }

  Longtext(uint length, CHARSET_INFO *cs) :Type(&type_handler_varchar, length, false, NULL, cs) {}

};

For exactly the same reason, Longtext should probably use type_handler_blob instead of type_handler_varchar. Longtext is used elsewhere (e.g. SHOW EXPLAIN/ANALYZE JSON and CHECK_CONSTRAINTS) with very large lengths, so it has the same per-record cost problem. Note this change is broader in scope than the slave_status_info change – it touches several INFORMATION_SCHEMA tables – so verify the affected outputs still look correct.

How to verify

• SHOW SLAVE STATUS and SELECT * FROM INFORMATION_SCHEMA.SLAVE_STATUS produce identical output to before (same column names, same values, same NULL-ability).
• Existing replication tests in mysql-test/suite/rpl/ that check SHOW SLAVE STATUS output still pass.
• The column types reported by the client change from VAR_STRING/VARCHAR to BLOB/TEXT for the affected columns – this is expected. Check that no test asserts on the type of these columns (only on their values); if any does, it will need updating.

Pointers / hints

• Column definitions: slave_status_info[] in sql/sql_show.cc
• Type helpers (Varchar, Blob, Longtext): sql/sql_i_s.h
• How the row gets filled and stored: search for fill_slave_status in sql/slave.cc.