0

Strange problem on the production server. After restarting the container assembly (which had the unintended side-effect of re-initialising the MySQL server with default settings), some memory-intensive pages (specifically the Views-enabled All Content page) wouldn't load. I couldn't import an SQL snapshot into a dev server for testing because MySQL kept on complaining about max_allowed_packet. After increasing max_allowed_packet, the import was successful and the troublesome pages loaded fine. I increased the setting on the production server and the problem went away as well. Why did max_allowed_packet need increasing unexpectedly? Is increasing it by a factor of 10 likely to avoid further problems, or will it cause more problems further down the line?

  • 1
    It's pretty much impossible to say from here, there are literally dozens of reasons a site can go from quick -> slow, low mem usage -> high, etc. It's also impossible to say whether increasing it by a factor of 10 is likely to fix it for the future, as it depends entirely on the way the server is used. It sounds like you should restore your MySQL config to what it was before the change, if it was working before. – Clive Jun 16 '16 at 8:39
  • Due to my fault, that wasn't actually documented. It was something about trebling the allowed memory size in my.cnf, but I'm not sure about the exact setting. It seems like setting max_packet_size would allow for greater tuning, maybe? – DMCoding Jun 16 '16 at 15:28
2

Why does max_allowed_packet need to be increased?

When your server is processing big queries, then you have to increase the value of the max_allowed_packet (or to shorten your queries if possible ?).

Big queries makes me think of Views. One way to find those queries is, to enable the "Show the SQL query" under admin/structure/views/settings edit a view and see how big its query is.

It is possible that if your content has been increased, your queries will have been increased as well (will return a large result row).

You can increase the max_allowed_packet, if needed, as MySQL says:

It is safe to increase the value of this variable because the extra memory is allocated only when needed.

  • 1
    It's usually cache that trips this in my experience; and like you say, often related to the Views module. The Views module stuffs whole view objects into cache as serialised strings, so it does make sense. Plus anything under MySQL 5.5 (I think) comes with a default max_allowed_packet of 1M. It's more sensible these days, but that legacy default messes people up often – Clive Jun 16 '16 at 9:45
  • I forgot the crucial bit of information which is this occurred on some large Views-overriden pages like the Content page. – DMCoding Jun 16 '16 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.