Find the size of a query-CodePudding

Based on the max_allowed_packet configuration parameter in MySQL, I am curious to understand how I can determine the size of the query related to that particular configuration?

We can use the SHOW VARIABLES LIKE 'max_allowed_packet' command in the MySQL Server. This will provide the current value for the parameter, which can then be used to calculate the size of the query accordingly. Additionally, one can also use the SET GLOBAL max_allowed_packet command to set the new value for the parameter.

My question is: How can I figure out the size of a query that is compared to this configuration?

CodePudding user response：

To do this 100% in the DB, you could create a table from the query results:

CREATE TABLE db_name.test_table SELECT a v1, b v2 FROM db_name.tbl2;

The query you want the result size of should replace

SELECT a v1, b v2 FROM db_name.tbl2

Then get the size of the table

  SELECT round((data_length / 1024 / 1024), 2) "Size in MB" 
      FROM information_schema.TABLES 
    WHERE table_schema = "db_name" AND table_name = "test_table";

Then drop the test table:

 drop table db_name.test_table;

This will just give you the raw data size, but not any additional overhead like data packet headers being transfered back (if your result set is broken into many packets).

There may be even a clever way to do this with temp tables or in a stored procedure where you pass the query into it.

After that, you can get your GLOBAL VARIABLES in a select to compare

SELECT
  variable_name,
  variable_value
FROM information_schema.global_variables
WHERE variable_name LIKE  'max_allowed_packet';

CodePudding user response：

The MariaDB protocol documentation for the packet states that each packet sent by the client must be below the max_allowed_packet limit. This means that all SQL queries must be shorter than max_allowed_packet.

Since there's some overhead in the network packets, a good rule of thumb for queries shorter than 16Mib is to take the string length of the query in bytes and subtract about 5 bytes from it (the packet header plus the COM_QUERY byte).

This does not mean that the server won't generate packets that are longer than this. Here's an example where the server exceeds the limit of max_allowed_packet:

MariaDB [test]> SET @a = (SELECT GROUP_CONCAT(seq) FROM seq_0_to_1000000);
Query OK, 0 rows affected, 1 warning (0.060 sec)

MariaDB [test]> SELECT LENGTH(@a), @@max_allowed_packet, @@group_concat_max_len;
 ------------ ---------------------- ------------------------ 
| LENGTH(@a) | @@max_allowed_packet | @@group_concat_max_len |
 ------------ ---------------------- ------------------------ 
|    1048576 |                16384 |                1048576 |
 ------------ ---------------------- ------------------------ 
1 row in set (0.001 sec)
 
MariaDB [test]> SELECT LENGTH(@a), @a\G
*************************** 1. row ***************************
LENGTH(@a): 1048576
        @a: 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
... More data ...

The value of max_allowed_packet is also not really related to how large your resultsets can be. You can generate a resultse that's a terabyte in size with max_allowed_packet set to 16Kib. This happens because resultsets are a stream of packets and if each individual packet is below the limit, there is no limit on how long the result can be.