Note
You are viewing the documentation for an older release of Interworx (6.x). To see documentation for the current generally available release of Interworx, click here: 7.13.
How to: Configure MySQL options via NodeWorx¶
Click the System Services menu item if it is not already open.
Click the MySQL Server menu item if it is not already open.
Click the Overview menu item.
Locate the MySQL Server Options section.
Change the option(s) you wish to update to the desired value(s).
Click the Update button.
You will see the following message at the top of the screen: Directives updated successfully.
If you are running a version of MySQL previous to 4.1 on your server, the following system variables will not be available for modification through the NodeWorx MySQL interface: Query Cache Limit and Query Cache Size. See the variable descriptions below for more details.
The most common configuration options are exposed in the InterWorx Control Panel. As with many of the system services, a system administrator still retains the ability to configure the service by editing the configuration file.
Connections¶
The maximum number of simultaneous connections allowed for MySQL. The number of connections must be between 1 and 65536, with the default value set to 100. Increasing max_connections increases the number of file descriptors that mysqld requires. It is strongly suggested that you keep the maximum number of connections below 1500.
MySQL Dynamic System Variables MySQL System Variables Overview Receiving a Too Many Connections Error?
Connection Errors¶
The maximum number of allowed connection errors for a given host. The value must be between 1 and 999999999. If set, the server blocks further connections from a remote host when the number of interrupted connections from that host exceeds this number. A successful connection from a host resets the number of connection errors. You can also unblock a host with the MySQL command FLUSH HOSTS.
Connect Timeout¶
The maximum number of seconds before a connection to the MySQL server will time out. Possible values range from 2 to 999999999 seconds.
Wait Timeout¶
The number of seconds the server waits for activity on a connection before closing the connection. Values can be between 2 and 999999999. On thread startup, SESSION.WAIT_TIMEOUT is initialized from GLOBAL.WAIT_TIMEOUT or GLOBAL.INTERACTIVE_TIMEOUT depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option).
Key Buffer Size¶
The key buffer is a memory buffer shared by all threads. The key buffer size mut be at least 16384 bytes. Increasing the key buffer size will result in better index handling (for all reads and multiple writes). 64M on a 256M machine that mainly runs MySQL is quite common. If you, however, make this too big (for instance more than 50% of your total memory) your system may start to page and become extremely slow. Remember that because MySQL does not cache data reads, you will have to leave some room for the OS filesystem cache.
Sort Buffer Size¶
The size of the buffer used when sorting table data. The minimum value is 8192 bytes and the default value is 1MB. Increasing this value will lead to faster ORDER BY or GROUP BY operations. A temporary buffer of sort_buffer_size is allocated when a thread needs to perform a sort operation.
Read Buffer Size¶
Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value. The minimum value for read_buffer_size is 8192 bytes, and the default value is 128KB. In versions of MySQL prior to 4.x, this system variable was referred to as record_buffer.
Maximum Allowed Packet Size¶
The maximum size of one packet. The minimum value for this variable is 1024 bytes. By default, the value is small so as to catch big (possibly wrong) packets. You must increase this value if you are using big BLOB columns, as it should be as big as the biggest BLOB you want to use. The protocol limits for max_allowed_packet is 16M in MySQL 3.23 and 1G in MySQL 4.0.
Thread Cache Size¶
The number of threads that are kept in the cache for reuse. This variable can be increased to improve performance if you have a lot of new connections. Increasing this value increases the number of file descriptors that mysqld requires.
Table Cache¶
This variable controls the number of open tables that are cached. Table cache is related to max connections. For example, for 200 concurrent running connections, you should have a table cache of at leat 200 * N, where N is the maximum number of tables in a join. If you increase this value, the number of file descriptors needed by MySQLD will also increase. You can check if you need to increase the table cache by checking the Opened_tables variable. If this variable is big and you don’t do FLUSH TABLES a lot (which just forces all tables to be closed and reopened), then you should increase the value of this variable.
Query Cache Limit¶
Sets the maximum amount of memory to be allocated for storage of old query results. The default value is 1MB. This variable is only available in versions of MySQL > 4.1.x.
Query Cache Size¶
Sets size of the query cache. The default value is 0. If this is set to 0, query caching is disabled. This variable is only available in versions of MySQL > 4.1.x.