Image
Image
Search our site for answers
Help Docs Server Administration Server Database Management MariaDB / MySQL Database Administration Understanding key MySQL variables

Understanding key MySQL variables

This guide explains key MySQL variables, their purpose, impact, and default values. It's a reference for understanding server behavior and optimization, with caution for changes.
Databases MySQL
9 min read

This article serves as a reference to help you understand various MySQL server variables. Knowing what each variable represents and its general impact can be helpful for understanding server behavior, though you likely do not need to adjust these variables unless specifically troubleshooting or optimizing under expert guidance.

Warning

Modifying MySQL variables, especially on production servers, can lead to performance degradation, instability, or even data loss if done incorrectly. It’s generally recommended to only adjust innodb_buffer_pool_size and key_buffer_size without specific, well-understood reasons for other changes.

MySQL variables explained

Here’s a breakdown of key MySQL variables and their purpose:

  • innodb_file_per_table
    • Purpose: Controls whether InnoDB stores each table’s data and indexes in its own .ibd file or in a shared tablespace file (ibdata1).
    • Benefit: When enabled (ON), it simplifies database management and recovery. If a single table becomes corrupted, you can often recover or remove just that .ibd file without affecting other tables. It also aids in OPTIMIZE TABLE operations.
    • Default: OFF in older versions, often ON in newer versions.
  • innodb_log_file_size
    • Purpose: Defines the size of the InnoDB redo log files (ib_logfile0, ib_logfile1, etc.). These logs record changes to ensure data integrity.
    • Impact: Larger log files can improve performance for write-heavy workloads by allowing more changes to be buffered before being written to disk, thus reducing disk I/O.
  • pid_file
    • Purpose: Specifies the location of the MySQL process ID (PID) file, which stores the process ID of the running MySQL server.
    • Default: Often /var/run/mysqld/mysqld.pid or similar.
  • port
    • Purpose: Defines the network port on which the MySQL server listens for incoming connections.
    • Default: 3306.
  • datadir
    • Purpose: Specifies the absolute path to the directory where MySQL stores all its databases, tables, and internal information.
    • Warning: You should generally avoid directly modifying files within this directory, or creating new files there. Everything in this directory is managed by MySQL; incorrect changes can lead to severe problems, crashes, or data loss.
    • Recommendation: Most of the time, this is left at its default (e.g., /var/lib/mysql). If you need to move your MySQL data, it’s best practice to create a symbolic link (symlink) from the default datadir path to your desired location.
  • query_cache_size
    • Purpose: Defines the size of the memory area used by MySQL to cache the complete result sets of SELECT statements.
    • Caveats: The query cache is a linked list, which can be inefficient on systems with frequent write operations because any modification to a table invalidates all cached queries related to that table.
    • Recommendation: For most modern, high-traffic WordPress sites or applications with frequent updates, the MySQL query cache is often a bottleneck, not an optimization. It’s typically recommended to set this value low (e.g., 0 or 1M) or disable it entirely, and rely on external caching solutions (like Memcached or Redis) that are more efficient. Setting this higher than 128M to 192M is generally inadvisable.
  • query_cache_limit
    • Purpose: Sets a limit on the maximum size of a single result set that can be stored in the query_cache.
    • Recommendation: Setting this low (e.g., 2M to 4M) is recommended. This prevents very large queries from consuming a disproportionate amount of the query cache and forcing frequent invalidations of other, smaller cached results.
  • query_cache_wlock_invalidate
    • Purpose: If set to ON (non-default), queries in the query cache will be invalidated if the underlying table is locked for writing.
    • Impact: Setting this to ON can lead to higher wait times for locks but ensures that results are not served from a potentially stale query cache if a table is being modified.
    • Default: OFF.
  • read_only
    • Purpose: When set to ON, only users with the SUPER privilege can make changes to databases (e.g., INSERT, UPDATE, DELETE). Non-super users can only read data.
    • Recommendation: This is typically set at boot time if you intend for your server to be read-only.
  • tmpdir
    • Purpose: Specifies the directory where MySQL writes temporary files and tables to disk during operations like large sorts, ALTER TABLE, or complex JOINs.
    • Warning: This location must have sufficient free space and be readable, writable, and executable by the MySQL user. If set to a small partition (like /tmp), operations on large tables could fail due to insufficient space.
    • Recommendation: Point tmpdir to a partition with ample free space, separate from your main data directory if possible.
  • delayed_insert_* (delayed_insert_limit, delayed_insert_timeout, delayed_queue_size)
    • Purpose: These variables control the behavior of INSERT DELAYED statements, allowing MySQL to handle writes when it’s less busy.
    • delayed_insert_limit: The number of rows to insert in a batch before checking for other pending queries.
    • delayed_insert_timeout: How many seconds to wait for more inserts before processing a batch.
    • delayed_queue_size: The maximum amount of memory to reserve for the delayed insert queue.
  • interactive_timeout
    • Purpose: The number of seconds the server waits for activity on an interactive connection (e.g., from the mysql client) before closing it.
    • Impact: Setting this lower can prevent idle or stuck interactive connections from consuming resources indefinitely.
  • join_buffer_size
    • Purpose: The size of the buffer that MySQL uses for join operations that cannot use an index, temporarily storing data for intermediate results.
    • Recommendation: Setting this too high can consume excessive memory as it’s allocated per client connection that performs a join requiring it.
  • key_buffer_size
    • Purpose: This is the size of the buffer used for caching index blocks for MyISAM tables.
    • Impact: Crucial for MyISAM table performance, as it caches frequently accessed index blocks, reducing disk I/O.
    • Recommendation: If you are primarily using InnoDB tables, this value can often be set to a relatively small amount (e.g., 8M-16M), as InnoDB uses innodb_buffer_pool_size for its own caching.
  • low_priority_updates
    • Purpose: If set to ON, INSERT, UPDATE, DELETE, REPLACE, and LOCK TABLES WRITE operations will wait until there are no pending SELECT statements for tables that are locked. This gives read operations higher priority.
    • Impact: Can be useful for read-heavy workloads where consistent reads are more critical than immediate writes.
  • auto_increment_increment and auto_increment_offset
    • Purpose: These variables define the server-wide default step and starting point for AUTO_INCREMENT columns if not explicitly set within the table structure itself.
    • auto_increment_increment: How much the auto-increment value increases for each new row (default is 1).
    • auto_increment_offset: The starting value for the auto-increment counter (default is 1).
    • Use case: Primarily used in master-master replication setups to avoid conflicts in auto-incrementing IDs.
  • concurrent_insert
    • Purpose: Controls how MyISAM tables handle concurrent INSERT statements while other clients are reading from the table.
    • Values: 0 (serial inserts), 1 (default, concurrent with SELECT on tables with no free blocks), 2 (concurrent with SELECT even with free blocks).
    • Impact: Setting this to 1 is generally optimal for MyISAM read-heavy tables.
  • lower_case_table_names
    • Purpose: Controls how MySQL server handles table and database names regarding case sensitivity.
    • Values: 0 (case-sensitive, Linux common), 1 (lowercase, Windows common), 2 (stored as given, compared as lowercase).
    • Warning: Changing this on a system with existing databases can cause issues if table names use mixed cases.
  • max_allowed_packet
    • Purpose: The maximum size of a single packet that MySQL can send or receive (e.g., max length of a query, size of a large BLOB).
    • Impact: You might need to increase this if importing very large SQL dumps or working with large data fields.
    • Default: 4M or 16M, depending on MySQL version.
  • max_connections
    • Purpose: The maximum number of simultaneous client connections that MySQL will allow.
    • Recommendation: A high max_connections can consume significant server memory and often indicates underlying application issues. It’s generally better to optimize application database interactions and use external caching to reduce the need for many simultaneous connections.
  • sort_buffer_size
    • Purpose: The size of the buffer MySQL uses when performing sort operations, allocated per client connection that needs a sort.
    • Recommendation: Optimize queries to avoid large sorts. Values greater than 256K can lead to diminishing returns, and values over 2MB are strongly discouraged due to potential memory allocation inefficiencies.
  • table_lock_wait_timeout
    • Purpose: The number of seconds an ACQUIRE_LOCK or RELEASE_LOCK statement waits for a table lock before timing out.
    • Impact: Helps prevent queries from hanging indefinitely if a table is locked.
  • time_zone
    • Purpose: Sets the server’s default time zone for MySQL.
    • Default: If set to SYSTEM, MySQL inherits the operating system’s time zone upon startup.
  • version
    • Purpose: Displays the MySQL server’s version. Note that the mysql command-line client version can differ from the server version.
  • wait_timeout
    • Purpose: The number of seconds the server waits for activity on a non-interactive connection (e.g., from web applications) before closing it.
    • Impact: Helps close idle connections, freeing up resources.
  • sql_mode
    • Purpose: Controls the SQL syntax and data validation behavior of MySQL, enforcing stricter rules for data integrity. Example (on cPanel servers): 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

STRICT_TRANS_TABLES: This mode prevents invalid or missing values from being inserted into columns, throwing an error instead of silently converting them. This is good for data integrity but can cause issues with applications not adhering to strict SQL standards.

Location: Often found in /usr/my.cnf on cPanel servers.

Conclusion

Understanding MySQL variables provides deep insight into your database’s behavior and performance. While many settings are best left at their defaults, knowing what each variable does empowers you to make informed decisions when troubleshooting, optimizing, or hardening your MySQL server. Always back up your configuration files before making changes, and test modifications in a staging environment before applying them to production. For complex tuning or database issues, Liquid Web’s Heroic Support® team is always available to assist.

Was this article helpful?