SQL Global Variables

Global variables in SQL are system-level variables that store settings for the SQL database server. These variables control the behavior of SQL queries, transactions, storage, and performance.

Use the SHOW VARIABLES command or SELECT @@global.<variable_name>.

SHOW VARIABLES LIKE 'autocommit';
SELECT @@global.autocommit;

Use the SET GLOBAL command.

SET GLOBAL autocommit = 0;

Note: You need SUPER privileges to modify global variables.

SQL_MODE defines SQL behavior, such as handling of invalid values, strict data validation, and compatibility with SQL standards.

Check SQL_MODE:

SELECT @@global.sql_mode;

Set SQL_MODE:

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION';

AUTOCOMMIT controls whether each SQL statement is committed automatically.

• 1 (ON): Each statement is committed immediately.
• 0 (OFF): Transactions must be committed explicitly.

Check:

SELECT @@global.autocommit;

Disable:

SET GLOBAL autocommit = 0;

MAX_CONNECTIONS sets the maximum number of simultaneous connections allowed to the server.

Check:

SELECT @@global.max_connections;

Increase:

SET GLOBAL max_connections = 500;

Answer:
New connections are rejected with an error:
"Too many connections."
A user with SUPER privileges can still connect.

WAIT_TIMEOUT determines the time in seconds a connection can stay idle before being closed.

Check:

SELECT @@global.wait_timeout;

Change:

SET GLOBAL wait_timeout = 300;

INTERACTIVE_TIMEOUT is similar to WAIT_TIMEOUT but applies to interactive clients like MySQL command-line.

Check:

SELECT @@global.interactive_timeout;

• WAIT_TIMEOUT: Applies to non-interactive clients (e.g., applications, scripts).
• INTERACTIVE_TIMEOUT: Applies to interactive clients (e.g., MySQL CLI).

THREAD_CACHE_SIZE controls how many threads are cached for reuse.

Check:

SELECT @@global.thread_cache_size;

Increase:

SET GLOBAL thread_cache_size = 16;

QUERY_CACHE_SIZE determines the memory allocated for caching query results.

Query cache caused performance bottlenecks due to frequent invalidation, so it was removed in MySQL 8.

TMP_TABLE_SIZE defines the maximum size of temporary tables in memory.

Check:

SELECT @@global.tmp_table_size;

Increase:

SET GLOBAL tmp_table_size = 67108864; -- 64MB

MAX_ALLOWED_PACKET sets the maximum size of a packet or query sent to the server.

Check:

SELECT @@global.max_allowed_packet;

Increase:

SET GLOBAL max_allowed_packet = 67108864; -- 64MB

INNODB_BUFFER_POOL_SIZE determines the size of the memory buffer for caching InnoDB data and indexes.

Check:

SELECT @@global.innodb_buffer_pool_size;

Increase:

SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2GB

Controls how InnoDB logs transactions:

• 0 - Flush logs every second.
• 1 - Flush logs after every transaction (safe, but slow).
• 2 - Flush logs at commit but not write to disk immediately.

Check:

SELECT @@global.innodb_flush_log_at_trx_commit;

LOG_BIN enables binary logging, used for replication and point-in-time recovery.

Check:

SELECT @@global.log_bin;

SLOW_QUERY_LOG records queries that take longer than LONG_QUERY_TIME.

Enable:

SET GLOBAL slow_query_log = 1;

Defines the threshold (in seconds) for logging slow queries.

Check:

SELECT @@global.long_query_time;

Set:

SET GLOBAL long_query_time = 2;

sync_binlog controls how binary logs are flushed to disk.

• 0: OS handles flushing (unsafe but faster).
• 1: Flushes after each transaction (safer, but slow).
• >1: Flushes after every N transactions.

Check:

SELECT @@global.sync_binlog;

Set:

SET GLOBAL sync_binlog = 1;

Defines how long a transaction waits for a row lock before timing out.

Check:

SELECT @@global.innodb_lock_wait_timeout;

Set:

SET GLOBAL innodb_lock_wait_timeout = 50;

ft_min_word_len sets the minimum word length for full-text searches in MySQL.
Default is 4 characters.

Check:

SELECT @@global.ft_min_word_len;

Change:

SET GLOBAL ft_min_word_len = 3;

For changes to take effect, you must rebuild the full-text index.

log_error specifies the error log file location where MySQL stores server errors, crashes, and warnings.

Check:

SELECT @@global.log_error;

log_slave_updates logs replicated queries on a MySQL replica, useful in multi-tier replication setups.

Enable:

SET GLOBAL log_slave_updates = 1;

Defines the size of InnoDB redo log files (ib_logfile0). Larger sizes improve transaction performance.

Check:

SELECT @@global.innodb_log_file_size;

Set (requires MySQL restart):

SET GLOBAL innodb_log_file_size = 512M;

Controls the number of concurrent InnoDB threads. Setting it to 0 lets MySQL handle it dynamically.

Check:

SELECT @@global.innodb_thread_concurrency;

Defines the max size for memory-based (HEAP) tables.

Check:

SELECT @@global.max_heap_table_size;

Set:

SET GLOBAL max_heap_table_size = 134217728; -- 128MB

Sets the default engine for table creation.

Check:

SELECT @@global.default_storage_engine;

Change:

SET GLOBAL default_storage_engine = 'InnoDB';

Defines how long MySQL waits for data from a client before timing out.

Check:

SELECT @@global.net_read_timeout;

Increase for slow networks:

SET GLOBAL net_read_timeout = 60;

Defines the timeout for writing data to a client.

Check:

SELECT @@global.net_write_timeout;

Change:

SET GLOBAL net_write_timeout = 60;

Defines memory allocated for sorting large datasets.

Check:

SELECT @@global.sort_buffer_size;

Increase:

SET GLOBAL sort_buffer_size = 2M;

Defines buffer size for joins that cannot use indexes.

Check:

SELECT @@global.join_buffer_size;

Increase for complex joins:

SET GLOBAL join_buffer_size = 2M;

Controls how InnoDB writes to disk. Common values:

• O_DIRECT: Recommended for high-performance SSDs.
• fsync: Writes to OS cache before disk.

Check:

SELECT @@global.innodb_flush_method;

Defines the isolation level for transactions:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ (default in MySQL)
• SERIALIZABLE

Check:

SELECT @@global.transaction_isolation;

Change:

SET GLOBAL transaction_isolation = 'READ COMMITTED';

Defines the number of tables stored in memory to avoid reopening tables repeatedly.

Check:

SELECT @@global.table_open_cache;

Increase:

SET GLOBAL table_open_cache = 4000;

Defines memory allocated for indexing in MyISAM storage engine.

Check:

SELECT @@global.key_buffer_size;

Increase:

SET GLOBAL key_buffer_size = 256M;

Limits the maximum connections a single user can open.

Check:

SELECT @@global.max_user_connections;

Change:

SET GLOBAL max_user_connections = 50;

Memory allocated for bulk inserts.

Check:

SELECT @@global.bulk_insert_buffer_size;

Increase:

SET GLOBAL bulk_insert_buffer_size = 128M;

Defines the max number of open files allowed by MySQL.

Check:

SELECT @@global.open_files_limit;

Change:

SET GLOBAL open_files_limit = 10000;