openark forge

Open source utilities
 

Detecting parameters change

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 … commands), only to be discarded by the next node restart.

mycheckpoint 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 sv_param_change view provides this information.

mysql> SELECT * FROM sv_param_change;
+---------------------+-----------------+-----------+-----------+
| ts                  | variable_name   | old_value | new_value |
+---------------------+-----------------+-----------+-----------+
| 2009-11-04 13:00:01 | max_connections |       500 |       200 |
+---------------------+-----------------+-----------+-----------+

In the above example, only one parameter has been changed in the known past, which is max_connections. The change was performed after careful analysis of number of connections to the server.

In the next example, though, it seems like something went wrong:

mysql> SELECT * FROM sv_param_change;
+---------------------+--------------------------------+------------+------------+
| ts                  | variable_name                  | old_value  | new_value  |
+---------------------+--------------------------------+------------+------------+
| 2009-11-05 10:05:00 | innodb_buffer_pool_size        | 4718592000 | 2621440000 |
| 2009-11-05 10:05:00 | innodb_flush_log_at_trx_commit |          1 |          2 |
| 2009-11-05 10:05:00 | max_allowed_packet             |   67108864 |   33554432 |
| 2009-11-05 10:05:00 | server_id                      |         25 |         17 |
| 2009-11-05 10:05:00 | thread_cache_size              |         32 |         64 |
+---------------------+--------------------------------+------------+------------+

The change of server-id and max_allowed_packet may suggest mysql is using the wrong my.cnf configuration file, or has been using a wrong one before. Weird replication symptoms can be explained by the above.

 
Powered by Wordpress and MySQL. Theme by openark.org