{"id":895,"date":"2009-10-20T21:04:40","date_gmt":"2009-10-20T19:04:40","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=895"},"modified":"2009-12-16T09:09:31","modified_gmt":"2009-12-16T07:09:31","slug":"how-to-calculate-a-good-innodb-log-file-size-recap","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/how-to-calculate-a-good-innodb-log-file-size-recap","title":{"rendered":"How to calculate a good InnoDB log file size – recap"},"content":{"rendered":"

Following Baron Schwartz’ post: How to calculate a good InnoDB log file size<\/a>, which shows how to make an estimate for the InnoDB log file size, and based on SQL: querying for status difference over time<\/a>, I’ve written a query to run on MySQL 5.1, which, upon sampling 60 seconds of status, estimates the InnoDB transaction log bytes that are expected to be written in the period of 1 hour.<\/p>\n

Recap<\/em>: this information can be useful if you’re looking for a good innodb_log_file_size<\/strong> value, such that will not pose too much I\/O (smaller values will make for more frequent flushes), not will make for a too long recovery time (larger values mean more transactions to recover upon crash).<\/p>\n

It is assumed that the 60 seconds period represents an average system load, not some activity spike period. Edit the sleep time and factors as you will to sample longer or shorter periods.<\/p>\n

\n
SELECT<\/strong>\r\n  innodb_os_log_written_per_minute*60\r\n    AS<\/strong> estimated_innodb_os_log_written_per_hour,\r\n  CONCAT(ROUND(innodb_os_log_written_per_minute*60\/1024\/1024, 1), 'MB')\r\n    AS<\/strong> estimated_innodb_os_log_written_per_hour_mb\r\nFROM<\/strong>\r\n  (SELECT<\/strong> SUM<\/strong>(value) AS<\/strong> innodb_os_log_written_per_minute FROM<\/strong> (\r\n    SELECT<\/strong> -VARIABLE_VALUE AS<\/strong> value\r\n      FROM<\/strong> INFORMATION_SCHEMA.GLOBAL_STATUS\r\n      WHERE<\/strong> VARIABLE_NAME = 'innodb_os_log_written'\r\n    UNION ALL<\/strong>\r\n    SELECT<\/strong> SLEEP(60)\r\n      FROM<\/strong> DUAL\r\n    UNION ALL<\/strong>\r\n    SELECT<\/strong> VARIABLE_VALUE\r\n      FROM<\/strong> INFORMATION_SCHEMA.GLOBAL_STATUS\r\n      WHERE<\/strong> VARIABLE_NAME = 'innodb_os_log_written'\r\n  ) s1\r\n) s2\r\n;<\/pre>\n<\/blockquote>\n
Sample output:<\/p>\n
\n
+------------------------------------------+---------------------------------------------+\r\n| estimated_innodb_os_log_written_per_hour | estimated_innodb_os_log_written_per_hour_mb |\r\n+------------------------------------------+---------------------------------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 584171520 | 557.1MB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+------------------------------------------+---------------------------------------------+<\/pre>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
Following Baron Schwartz’ post: How to calculate a good InnoDB log file size, which shows how to make an estimate for the InnoDB log file size, and based on SQL: querying for status difference over time, I’ve written a query to run on MySQL 5.1, which, upon sampling 60 seconds of status, estimates the InnoDB […]<\/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":[24,14,52,21],"class_list":["post-895","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-information_schema","tag-innodb","tag-performance","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-er","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/895","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=895"}],"version-history":[{"count":25,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/895\/revisions"}],"predecessor-version":[{"id":1428,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/895\/revisions\/1428"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=895"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=895"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=895"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}