Option and Variable Matrix

SHOW STATUS and SHOW VARIABLES are very useful features for analyzing server performance, server setup and general debugging. Many times, there are items applicable to a scenario in both lists. Further, sometimes it’s difficult to know whether an option or variable can be changed on the commandline or in an option file.

For example, let’s say you are debugging a lot of aborted connects. You may care about the following items in “SHOW STATUS”:

Aborted_connects
Connections
Max_used_connections
Threads_connected
Uptime

And the following items in SHOW VARIABLES:

connect_timeout
init_connect
max_connect_errors
max_connections
max_user_connections

You might compare max_connections to max_used_connections, for example. Let’s say you realize you want to increase max_connections from 100 to 200 — can you do it dynamically on the commandline? Do you have to put it in an option file and restart?

Now MySQL has a matrix of variables and options that easily shows what can be changed where:

http://dev.mysql.com/doc/refman/5.0/en/mysqld-option-tables.html

As a bonus, many of the items in the list have links to the section that explains what they do.

This is a great reference — the matrix is only for 5.0 and up, but most of it likely applies to below 5.0 — just that some options may not be available. At any rate, you can use the 5.0 matrix, click through to the variable/option’s description, and then use the link on the left-hand side to go to the “3.23, 4.0, 4.1” documentation, which will give you the correct information for that version.

I know this will save me much time.

SHOW STATUS and SHOW VARIABLES are very useful features for analyzing server performance, server setup and general debugging. Many times, there are items applicable to a scenario in both lists. Further, sometimes it’s difficult to know whether an option or variable can be changed on the commandline or in an option file.

For example, let’s say you are debugging a lot of aborted connects. You may care about the following items in “SHOW STATUS”:

Aborted_connects
Connections
Max_used_connections
Threads_connected
Uptime

And the following items in SHOW VARIABLES:

connect_timeout
init_connect
max_connect_errors
max_connections
max_user_connections

You might compare max_connections to max_used_connections, for example. Let’s say you realize you want to increase max_connections from 100 to 200 — can you do it dynamically on the commandline? Do you have to put it in an option file and restart?

Now MySQL has a matrix of variables and options that easily shows what can be changed where:

http://dev.mysql.com/doc/refman/5.0/en/mysqld-option-tables.html

As a bonus, many of the items in the list have links to the section that explains what they do.

This is a great reference — the matrix is only for 5.0 and up, but most of it likely applies to below 5.0 — just that some options may not be available. At any rate, you can use the 5.0 matrix, click through to the variable/option’s description, and then use the link on the left-hand side to go to the “3.23, 4.0, 4.1” documentation, which will give you the correct information for that version.

I know this will save me much time.

Comments are closed.