How to determine required max_allowed_packet size when used with PDO and prepared statements?

Question

Or through example, what does max_allowed_packet need to be to perform the following query? Please do not just give me the value, but explain how you determined it. Thank you

$stmt=$pdo->prepare('INSERT INTO t(c1,c2,c3) VALUES(?,?,?)');
$stmt->execute([9876543210,9876543210,9876543210]);

EDIT. Or what about this one:

$sql='INSERT INTO t(pk,c1,c2) VALUES(?,?,?), (?,?,?), (?,?,?)
ON DUPLICATE KEY UPDATE c1=VALUES(c1),c2=VALUES(c2)';
$stmt=$pdo->prepare($sql);
$stmt->execute([1,111,111,2,222,222,3,333,333]);

Count the bytes in your query, multiply by 11. Yes, it's a magic number. [See this post.](http://drupal.stackexchange.com/a/31898) But why calculate it per query? Set it to the largest possible `INSERT` you'd perform and be done with it: MySQL uses this value as an upper bound, not a buffer pre-allocation size. — bishop, Dec 13 '16 at 16:42
@bishop Didn't expect `11`! I won't be calculating it per query, but will need to check the size of the resultant query before creating the query, and break it up into sections if it exceeds the `max_allowed_packet`. So, for my example, `max_allowed_packet` will need to be 638? This is based the length of the three 10 digit numbers plus the other SQL text. Are whitespaces between digits included? Thanks! — user1032531, Dec 13 '16 at 16:53
I know, right: 11. When sending a prepared query you're sending a data structure that breaks out the query template and the data. Plus you have the overhead of network protocol, thread buffer, and all that jazz. But yes, whitespace counts. Comments count. Everything counts in the template query. — bishop, Dec 13 '16 at 17:12
That said, are you breaking up a multi-value insert? If you are, I've attempted that before and my advice: don't. Finding a break point that "shrink wraps" the query just enough isn't worth it in terms of total performance optimization. I ended up counting bytes and when I reached 67% of max_allowed_packet, sending the query along. That still gave multi-value inserts a 100x improvement over looped single value. — bishop, Dec 13 '16 at 17:13
@bishop Thanks again. I am actually doing multiple updates in a single query, and will be using ON DUPLICATE as described by http://stackoverflow.com/a/3466/1032531. So, I won't break up a single query, but will just not do all the update queries in the same attempt. Does this make sense? — user1032531, Dec 13 '16 at 17:20
@bishop I updated the original post to show an UPDATE query. — user1032531, Dec 13 '16 at 17:36
At the end of the day, tuning this number is evidence-based. The "multiply times 11" guideline is magic for when you're dealing with large blobs and not quite appropriate for large valued statements. Personally, I'd set this value to the biggest MySQL supports (`1G`), then bisect with tests or production data until I got a `PACKET_TOO_LARGE` error. I've just had too much trouble trying to tune it to specific query loads. — bishop, Dec 13 '16 at 18:04

score 1 · Accepted Answer · edited Apr 13 '17 at 12:46

To calculate the max_allowed_packet size, take the byte length of your largest blob column and multiply times 11. For more details, see this post.

In your example, though you have a lengthy query not a large quantity of blob data. Based on my experience the multiply by 11 guideline will still work, but it's not an optimal way to tune the value.

As commented above, evidence is the optimal means to tune this value. I would set the max_allowed_packet to an arbitrarily huge value, like 1G. (The maximum MySQL supports, I believe.) Then, run your software in normal operation. If you don't get a PACKET_TOO_LARGE error, then cut the value in half and try again. Repeat until you do overflow the limit then double. That will then represent a reasonable upper-bound for your particular needs, with plenty of head room for growth.

How to determine required max_allowed_packet size when used with PDO and prepared statements?

1 Answers1