MySQL Variables

Server variables can be set at server startup, and many of them can be changed while the server is running.

SHOW VARIABLES LIKE '%buffer_size%';

Some server variables are static (we can only set them at sertver startup time). For example, we can only specify the data directory by using the datadir startup option, but we cannot tell a running server to change its data directory. Other variables are dynamic and can be changed while the server is running:

mysql> SET GLOBAL key_buffer_size = 128 * 1024 * 1024;
mysql> SET @@global.key_buffer_size = 128 * 1024 * 1024;

With a SET statement, we can not use suffix such as K, M, or G to indicate units for values but we can use expression.

Global variables apply server wide and is used to initialize the corresponding session variables for new client connections. Each client may subsequently change its own session variables.

Session variables are session-specific and applies only to a particular client connection.

To set global variables, you must have the SUPER privilege.

SET GLOBAL storage_engine = MyISAM;
SET @@global.storage_engine = MyISAM;
SET SESSION storage_engine = MyISAM;
SET @@session.storage_engine = MyISAM;

LOCAL is synonymous for SESSION.

If we do not explicitly indicate whether to set the global or session variable, MySQL sets the session variable.

SELECT @@global.storage_engine, @@session.storage_engine;

Two memory-related InnoDB resources are the buffer pool and the log buffer:

  • The InnoDB buffer pool caches data and index information for InnoDB tables. Making the buffer pool larger reduces disk I/O for frequently accessed InnoDB table contents. The buffer pool size is controlled by innodb_buffer_pool_size system variable. Its default value is 8MB. On a machine dedicated to MySQL, you can set innodb_bufffer_pool_size variable to anywhere from 50% to 80% of the total amount of memory. However, this setting should take into account how large you set the key_buffer_size value for MyISAM.
  • The InnoDB log buffer holds information about modifications made during transaction processing. Ideally, you want a transaction's changes to be held in the buffer until the transaction commits, at which point they can be written to the InnoDB log file all at once. If the buffer is too small, changes might need to be written several times before commit time, resulting in additional disk activity. The log buffer size is controlled by the innodb_log_buffer_size system variable. Typical values range from 1MB to 8MB. The default is 1MB.

Status Variables:

SHOW STATUS LIKE 'Connections'

Connections: total number of connection attempts (both successful, and unsuccessful)

Aborted_connects: The number of unsuccessful connection attempts

Connections - Aborted_connects: The number of successful connection attempts.

Aborted_clients: The number of successful connections that terminate abnormally (if the client die, or network went down)

Connections - Aborted_connects - Aborted_clients: The number of successful connections that terminate normally.

Threads_connected: The number of clients currently connected to the server.

The Com variables give us a break down of the number of statements that the server has executed by statement type. You can see all these variables:

mysql> SHOW STATUS LIKE 'Com%';



Com_select does not include the number of queries that are processed by the query cache.

Questions: The number of queries the server has processed.

Uptime: The number of seconds the server has been running

Questions / Uptime: how many queries per second the server has processed.

Slow_queries: indicates the number of queries that take a long time to process. Ideally, its value should increase slowly or not at all. If it increase quickly, you may have problem with certain queries.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License