{"id":220,"date":"2009-11-08T16:12:19","date_gmt":"2009-11-08T14:12:19","guid":{"rendered":"http:\/\/code.openark.org\/forge\/?page_id=220"},"modified":"2009-11-08T21:11:28","modified_gmt":"2009-11-08T19:11:28","slug":"detecting-parameters-change","status":"publish","type":"page","link":"https:\/\/code.openark.org\/forge\/mycheckpoint\/documentation\/detecting-parameters-change","title":{"rendered":"Detecting parameters change"},"content":{"rendered":"<p>Change of server variables is an elusive problem. Configuration files are rarely version controlled, which makes it difficult to follow changes as the application evolves. But even then, some variables are dynamically changed (via SET GLOBAL &#8230; commands), only to be discarded by the next node restart.<\/p>\n<p><em>mycheckpoint<\/em> records most server variables, and provides with change tracking. It is possible to learn which variable changed, when, from what value to what new value. The <strong>sv_param_change<\/strong> view provides this information.<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT * FROM sv_param_change;\r\n+---------------------+-----------------+-----------+-----------+\r\n| ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | variable_name\u00a0\u00a0 | old_value | new_value |\r\n+---------------------+-----------------+-----------+-----------+\r\n| 2009-11-04 13:00:01 | max_connections |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 500 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 200 |\r\n+---------------------+-----------------+-----------+-----------+<\/pre>\n<\/blockquote>\n<p>In the above example, only one parameter has been changed in the known past, which is <strong>max_connections<\/strong>. The change was performed after careful analysis of number of connections to the server.<\/p>\n<p>In the next example, though, it seems like something went wrong:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT * FROM sv_param_change;\r\n+---------------------+--------------------------------+------------+------------+\r\n| ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | variable_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | old_value\u00a0 | new_value\u00a0 |\r\n+---------------------+--------------------------------+------------+------------+\r\n| 2009-11-05 10:05:00 | innodb_buffer_pool_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 4718592000 | 2621440000 |\r\n| 2009-11-05 10:05:00 | innodb_flush_log_at_trx_commit |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 |\r\n| 2009-11-05 10:05:00 | max_allowed_packet\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 67108864 |\u00a0\u00a0 33554432 |\r\n| 2009-11-05 10:05:00 | server_id\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 25 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 17 |\r\n| 2009-11-05 10:05:00 | thread_cache_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 32 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 64 |\r\n+---------------------+--------------------------------+------------+------------+<\/pre>\n<\/blockquote>\n<p>The change of <strong>server-id<\/strong> and <strong>max_allowed_packet<\/strong> may suggest mysql is using the wrong <strong>my.cnf<\/strong> configuration file, or has been using a wrong one before. Weird replication symptoms can be explained by the above.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Change of server variables is an elusive problem. Configuration files are rarely version controlled, which makes it difficult to follow changes as the application evolves. But even then, some variables are dynamically changed (via SET GLOBAL &#8230; commands), only to be discarded by the next node restart. mycheckpoint records most server variables, and provides with [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":147,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","meta":{"footnotes":""},"class_list":["post-220","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/220","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/comments?post=220"}],"version-history":[{"count":5,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/220\/revisions"}],"predecessor-version":[{"id":223,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/220\/revisions\/223"}],"up":[{"embeddable":true,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/147"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/media?parent=220"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}