MySQL Troubleshooting

Options That Limit Hardware Resources

The options in this group set limits on various hardware resources. They can be used for two purposes: to tune performance and to limit use for certain operations. The latter options are useful when you want to limit traffic between clients and the server or prevent Denial of Service attacks. It’s better for particular users to get graceful errors because of lack of resources than for mysqld to die because it can’t handle all incoming requests.

Later in this chapter, I will describe the tactics one should follow when adjusting these options. Here I want to point to cases when such variables lead to different and perhaps unexpected behavior compared to setups where the variables were not set. As always, I will show by example.

In my day-to-day job, I see many users who are affected by ignoring the value of max_allowed_packet. This variable limits the number of bytes that can be set in a single packet between the server and the client. In this example, I lower the default 1MB value of max_allowed_packet just to demonstrate its effect:

mysql> SELECT repeat('a',1025);
+------------------+
| repeat('a',1025) |
+------------------+
| NULL             |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1301
Message: Result of repeat() was larger than max_allowed_packet (1024) - truncated
1 row in set (0.00 sec)

This time, it is clear from the warning message ...