MySQL optimization (IV)

This is the last article about MySQL tunning, and we are going to present the way to change the mentioned parameters. The previous issue was MySQL optimization (III).

These parameters were established into the MySQL configuration file (my.cnf). Such modifications will not take effect until the mysqld service is rebooted. But there may be the case where we cannot reset the service, for example because the computer is on a production environment.

In this situation we must know that in MySQL, there are several dynamic variables wich can be modified at runtime. In order to see all system variables, we can run the following order:

mysql> show global variables;
+---------------------------------+------------------------------------------------------------+
| Variable_name                   | Value                                                      |
+---------------------------------+------------------------------------------------------------+
| auto_increment_increment        | 1                                                          |
| auto_increment_offset           | 1                                                          |
| automatic_sp_privileges         | ON                                                         |
| back_log                        | 50                                                         |
| basedir                         | /usr/                                                      |
| bdb_cache_size                  | 8384512                                                    |
| bdb_home                        | /var/lib/mysql/                                            |
| bdb_log_buffer_size             | 262144                                                     |
...

So as to show the value of a concrete variable:

mysql> show global variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache   | 64    |
+---------------+-------+
1 row in set (0.00 sec)

And if we want to modify its value at runtime (it must be changed globally wherever possible):

mysql> set global table_cache=1024;
Query OK, 0 rows affected (0.00 sec)