{"id":1997,"date":"2010-06-09T06:35:08","date_gmt":"2010-06-09T04:35:08","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1997"},"modified":"2010-06-09T13:00:04","modified_gmt":"2010-06-09T11:00:04","slug":"those-oversized-undersized-variables-defaults","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/those-oversized-undersized-variables-defaults","title":{"rendered":"Those oversized, undersized variables defaults"},"content":{"rendered":"<p>Some <strong>mysqld<\/strong> parameters are far from having reasonable defaults. Most notable are the engine-specific values, and in particular the InnoDB parameters.<\/p>\n<p>Some of these variables have different defaults as of MySQL 5.4. <strong>innodb_buffer_pool_size<\/strong>, for example, is <strong>128M<\/strong> on 5.4. <strong>innodb_log_file_size<\/strong>, however, has changed back and forth, as far as I understand, and is down to <strong>5M<\/strong> again. These settings are still the same on 5.5.<\/p>\n<p>I wish to present some not-so-obvious parameters which, in my opinion, have poor defaults, for reasons I will explain.<\/p>\n<ul>\n<li><strong>group_concat_max_len<\/strong>: This parameter limits the maximum text length of a <strong>GROUP_CONCAT<\/strong> concatenation result. It defaults to <strong>1024<\/strong>. I think this is a very low value. I have been using <strong>GROUP_CONCAT<\/strong> more and more, recently, to solve otherwise difficult problems. And in most cases, <strong>1024<\/strong> was just too low, resulting in <a href=\"http:\/\/code.openark.org\/blog\/mysql\/but-i-do-want-mysql-to-say-error\">silent<\/a> (<em>Argh!<\/em>) truncating of the result, thus returning incorrect results. It is interesting to learn that the maximum value for this parameter is limited by <strong>max_packet_size<\/strong>. I would suggest, then, that this parameter should be altogether removed, and have the <strong>max_packet_size<\/strong> limitation as the only limitation. Otherwise, I&#8217;d like it to have a very large default value, in the order of a few MB.<\/li>\n<li><strong>wait_timeout<\/strong>: Here&#8217;s a parameter whose default value is over permissive. <strong>wait_timeout<\/strong> enjoys an <strong>8 hour<\/strong> default. I usually go for <strong>5-10 minutes<\/strong>. I don&#8217;t see a point in letting idle connections waste resources for 8 hours. Applications which hold up such connections should be aware that they&#8217;re doing something wrong, in the form of a forced disconnection. Connection pools work beautifully with low settings, and can themselves do keepalives, if they choose to.<\/li>\n<li><strong>sql_mode<\/strong>: I&#8217;ve <a href=\"http:\/\/code.openark.org\/blog\/mysql\/do-we-need-sql_mode\">discussed this<\/a> in length before. My opinion unchanged.<\/li>\n<li><strong>open_files_limit<\/strong>: What with the fact connections, threads, table descriptors, table file descriptors (depending on how you use InnoDB), temporary file tables &#8212; all are files on unix-like systems, and considering this is an inexpensive payment, I think <strong>open_files_limit<\/strong> should default to a few thousands. Why risk the crash of &#8220;too many open files&#8221;?<\/li>\n<\/ul>\n<p><!--more-->No setting will ever be perfect for everyone, I know. But there are those parameters which you automatically set values for when you do a new install. These should be at focus and their defaults change.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Some mysqld parameters are far from having reasonable defaults. Most notable are the engine-specific values, and in particular the InnoDB parameters. Some of these variables have different defaults as of MySQL 5.4. innodb_buffer_pool_size, for example, is 128M on 5.4. innodb_log_file_size, however, has changed back and forth, as far as I understand, and is down to [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[11,35],"class_list":["post-1997","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-configuration","tag-sql_mode"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-wd","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1997","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=1997"}],"version-history":[{"count":26,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1997\/revisions"}],"predecessor-version":[{"id":2566,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1997\/revisions\/2566"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=1997"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=1997"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=1997"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}