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?
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. In most cases this is not something you have to worry about unless you are inserting large batches of data using SQL statements.
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 1 byte from it: the packet header's length will be the SQL query plus the COM_QUERY byte. In general going this far to match the limits is rarely worth it and staying well below the limit tends to be a more practical approach.
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.
And as Georg Richter mentioned in the comments, @max_allowed_packet also affects the length of some literals/strings. For example, SET @a:= REPEAT("A", @@max_allowed_packet + 1) will fail, even if the string length of the actual SQL statement is only a few bytes.