12 Mysql-config Variables
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View 12 Mysql-config Variables as PDF for free.
More details
- Words: 1,423
- Pages: 4
12 useful MySQL configuration variables
Version 1.0 June 2, 2005
By Mellonfire
Takeaway The default MySQL server settings are optimized for most common uses. However, because MySQL is so open, it's fairly easy for you to further fine-tune MySQL's default settings to obtain even greater performance and stability.
MySQL configuration The creators of MySQL call it "the world's most popular open-source database". It's not hard to see why, given that there are over six million installations of MySQL worldwide, with the number growing monthly. For most of these installations, the "stock" MySQL binary works just fine—the MySQL development team does extensive research to ensure that the default server settings are optimized for most common uses. However, because MySQL is so open, it's fairly easy for you to further fine-tune MySQL's default settings to obtain even greater performance and stability. In this document, we'll be listing some of the most useful MySQL configuration variables, telling you what each one does and suggesting how you can use it. Before you begin modifying your MySQL configuration, it's worthwhile noting the following points: •
MySQL supports both global and session variables. Global variables are set for the server as a whole and affect all client connections. Session variables are set for an individual connection and only affect that connection. Administrator privileges are needed to alter global variables. • MySQL variables may be set/altered either at start-up through the my.ini or my.cnf configuration file, or at run-time with the SET command. You can view a snapshot of the current MySQL configuration at any time by using the SHOW VARIABLES command. With that out of the way, here's a list of the most useful MySQL configuration variables:
Page 1 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html
12 useful MySQL configuration variables
1
Altering Index Buffer Size (key_buffer)
This variable controls the size of the buffer used when handling table indices (both read and write operations). The MySQL manual recommends that this variable be increased to as much as you can afford to ensure you get the best performance on indexed tables, and recommends a value equivalent to about 25 percent of the total system memory. This is one of the more important MySQL configuration variables and if you're interested in optimizing and improving performance, trying different values for the key_buffer_size variable is a good place to start.
2
Altering Table Buffer Size (read_buffer_size)
When a query requires a table to be scanned sequentially, MySQL allocates a memory buffer to this query. The read_buffer_size variable controls the size of this buffer. If you find that sequential scans are proceeding slowly, you can improve performance by increasing this value, and hence the size of the memory buffer.
3
Setting the Number of Maximum Open Tables (table_cache)
This variable controls the maximum number of tables MySQL can have open at any one time, and thus controls the server's ability to respond to incoming requests. This variable is closely related to the max_connections variables increasing this value allows MySQL to keep a larger number of tables open, just as increasing max_connections increases the number of allowed connections. Consider altering this value if you have a high-volume server which receives queries on multiple different databases and tables.
4
Activating the Query Cache (query_cache_type)
MySQL 4.x includes a query cache which can improve query response time by caching the results of frequently-used queries and returning the cached data on subsequent calls to the same query. Whether or not the query cache is used is controlled by the query_cache_type variable, which may be set to ON, OFF or DEMAND. It's generally a good idea to turn this on, especially if you have large numbers of identical SELECT queries being executed repeatedly on the server. Tip: You can also use the query_cache_size variable to control the amount of memory allocated to the MySQL query cache. Increase this value for high-volume servers.
5
Setting the Maximum Size Of A Join (max_join_size)
This variable sets the maximum number of rows that a SELECT query should scan when performing a table join. This is useful to catch badly-written queries that might end up scanning millions of rows, thus decreasing the server's ability to satisfy other requests. Because this variable impacts all queries and users, it should be set only after careful consideration of what constitutes a legal query on your specific system.
6
Setting the Maximum Number of Permitted Connections (max_connections)
This variable controls the maximum number of incoming client connections MySQL can deal with at any one time. If your application is likely to experience large numbers of independent client connections simultaneously, it's a good idea to increase this value to avoid the "Too many connections" error.
Page 2 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html
12 useful MySQL configuration variables
7
Deciding a Time Limit for Long Queries (long_query_time)
MySQL comes with a so-called slow query log, which automatically logs all queries that do not end within a particular time limit. This log is useful to track inefficient or misbehaving queries, and to find targets for optimization algorithms. The long_query_time variable controls this maximum time limit, in seconds. Tip: To avoid false positives, set this to a relatively higher value on systems that experience heavy load.
8
Altering the Transaction Isolation Level (tx_isolation)
This variable controls the transaction isolation level, or the extent to which concurrent transactions and the changes they make to a table are "visible" to each other. There are four transaction levels in an ACID-compliant transactional RDBMS like MySQL, and you can set MySQL to use any one of them with this variable. Note: It's important to remember that this variable controls the global, default transaction level. Individual clients can, of course, override this value on a per-transaction basis if needed.
9
Activating the Binary Log (log_bin)
MySQL's binary log keeps track of all queries that alter the data in the database. It's mostly used to efficiently and accurately perform replication operations, and to restore the system to a stable snapshot in the event of a failure or transaction interruption. The log_bin variable enables this log.
10
Auto-Executing SQL Code on Client Connection (init_connect)
This variable can be used to run SQL commands on the server for every client that successfully opens a connection. This variable is usually set to one or more SQL commands, which are executed by the server to perform client-specific initialization.
11
Setting a Timeout for Interactive Connections (interactive_timeout)
This is a particularly useful variable to set if your MySQL server receives numerous requests for interactive use. This variable controls how long the server should wait for activity on an interactive client connection before terminating the connection. Set this to a reasonable value—five minutes is usually fair, although you will want to reduce that if your server has a lot of clients requesting interactive connection. Tip: You can use the connect_timeout variable to control how long MySQL waits for a client connection to be consummated before terminating it with an error.
12
Setting the Time Zone (system_time_zone)
This variable controls the time zone MySQL uses for all its date/time bookkeeping. It's important to ensure that this variable is correctly set, as using the wrong time zone can affect the accuracy of values entered into DATE, TIME and TIMESTAMP fields, as well as flaw date arithmetic operations.
Page 3 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html
12 useful MySQL configuration variables
Additional resources • •
Sign up for our TechRepublic NetNote, delivered on Mondays, Wednesdays, and Thursdays. Check out all of TechRepublic's newsletter offerings.
•
Take advantage of MySQL flexibility with this overview of table types (Download)
•
10 useful MySQL date and time functions (Download)
•
Backing up and restoring MySQL databases in three easy steps (Download)
Version history Version: 1.0 Published: June 2, 2005
Tell us what you think TechRepublic downloads are designed to help you get your job done as painlessly and effectively as possible. Because we're continually looking for ways to improve the usefulness of these tools, we need your feedback. Please take a minute to drop us a line and tell us how well this download worked for you and offer your suggestions for improvement. Thanks! —The TechRepublic Downloads Team
Page 4 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html
Version 1.0 June 2, 2005
By Mellonfire
Takeaway The default MySQL server settings are optimized for most common uses. However, because MySQL is so open, it's fairly easy for you to further fine-tune MySQL's default settings to obtain even greater performance and stability.
MySQL configuration The creators of MySQL call it "the world's most popular open-source database". It's not hard to see why, given that there are over six million installations of MySQL worldwide, with the number growing monthly. For most of these installations, the "stock" MySQL binary works just fine—the MySQL development team does extensive research to ensure that the default server settings are optimized for most common uses. However, because MySQL is so open, it's fairly easy for you to further fine-tune MySQL's default settings to obtain even greater performance and stability. In this document, we'll be listing some of the most useful MySQL configuration variables, telling you what each one does and suggesting how you can use it. Before you begin modifying your MySQL configuration, it's worthwhile noting the following points: •
MySQL supports both global and session variables. Global variables are set for the server as a whole and affect all client connections. Session variables are set for an individual connection and only affect that connection. Administrator privileges are needed to alter global variables. • MySQL variables may be set/altered either at start-up through the my.ini or my.cnf configuration file, or at run-time with the SET command. You can view a snapshot of the current MySQL configuration at any time by using the SHOW VARIABLES command. With that out of the way, here's a list of the most useful MySQL configuration variables:
Page 1 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html
12 useful MySQL configuration variables
1
Altering Index Buffer Size (key_buffer)
This variable controls the size of the buffer used when handling table indices (both read and write operations). The MySQL manual recommends that this variable be increased to as much as you can afford to ensure you get the best performance on indexed tables, and recommends a value equivalent to about 25 percent of the total system memory. This is one of the more important MySQL configuration variables and if you're interested in optimizing and improving performance, trying different values for the key_buffer_size variable is a good place to start.
2
Altering Table Buffer Size (read_buffer_size)
When a query requires a table to be scanned sequentially, MySQL allocates a memory buffer to this query. The read_buffer_size variable controls the size of this buffer. If you find that sequential scans are proceeding slowly, you can improve performance by increasing this value, and hence the size of the memory buffer.
3
Setting the Number of Maximum Open Tables (table_cache)
This variable controls the maximum number of tables MySQL can have open at any one time, and thus controls the server's ability to respond to incoming requests. This variable is closely related to the max_connections variables increasing this value allows MySQL to keep a larger number of tables open, just as increasing max_connections increases the number of allowed connections. Consider altering this value if you have a high-volume server which receives queries on multiple different databases and tables.
4
Activating the Query Cache (query_cache_type)
MySQL 4.x includes a query cache which can improve query response time by caching the results of frequently-used queries and returning the cached data on subsequent calls to the same query. Whether or not the query cache is used is controlled by the query_cache_type variable, which may be set to ON, OFF or DEMAND. It's generally a good idea to turn this on, especially if you have large numbers of identical SELECT queries being executed repeatedly on the server. Tip: You can also use the query_cache_size variable to control the amount of memory allocated to the MySQL query cache. Increase this value for high-volume servers.
5
Setting the Maximum Size Of A Join (max_join_size)
This variable sets the maximum number of rows that a SELECT query should scan when performing a table join. This is useful to catch badly-written queries that might end up scanning millions of rows, thus decreasing the server's ability to satisfy other requests. Because this variable impacts all queries and users, it should be set only after careful consideration of what constitutes a legal query on your specific system.
6
Setting the Maximum Number of Permitted Connections (max_connections)
This variable controls the maximum number of incoming client connections MySQL can deal with at any one time. If your application is likely to experience large numbers of independent client connections simultaneously, it's a good idea to increase this value to avoid the "Too many connections" error.
Page 2 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html
12 useful MySQL configuration variables
7
Deciding a Time Limit for Long Queries (long_query_time)
MySQL comes with a so-called slow query log, which automatically logs all queries that do not end within a particular time limit. This log is useful to track inefficient or misbehaving queries, and to find targets for optimization algorithms. The long_query_time variable controls this maximum time limit, in seconds. Tip: To avoid false positives, set this to a relatively higher value on systems that experience heavy load.
8
Altering the Transaction Isolation Level (tx_isolation)
This variable controls the transaction isolation level, or the extent to which concurrent transactions and the changes they make to a table are "visible" to each other. There are four transaction levels in an ACID-compliant transactional RDBMS like MySQL, and you can set MySQL to use any one of them with this variable. Note: It's important to remember that this variable controls the global, default transaction level. Individual clients can, of course, override this value on a per-transaction basis if needed.
9
Activating the Binary Log (log_bin)
MySQL's binary log keeps track of all queries that alter the data in the database. It's mostly used to efficiently and accurately perform replication operations, and to restore the system to a stable snapshot in the event of a failure or transaction interruption. The log_bin variable enables this log.
10
Auto-Executing SQL Code on Client Connection (init_connect)
This variable can be used to run SQL commands on the server for every client that successfully opens a connection. This variable is usually set to one or more SQL commands, which are executed by the server to perform client-specific initialization.
11
Setting a Timeout for Interactive Connections (interactive_timeout)
This is a particularly useful variable to set if your MySQL server receives numerous requests for interactive use. This variable controls how long the server should wait for activity on an interactive client connection before terminating the connection. Set this to a reasonable value—five minutes is usually fair, although you will want to reduce that if your server has a lot of clients requesting interactive connection. Tip: You can use the connect_timeout variable to control how long MySQL waits for a client connection to be consummated before terminating it with an error.
12
Setting the Time Zone (system_time_zone)
This variable controls the time zone MySQL uses for all its date/time bookkeeping. It's important to ensure that this variable is correctly set, as using the wrong time zone can affect the accuracy of values entered into DATE, TIME and TIMESTAMP fields, as well as flaw date arithmetic operations.
Page 3 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html
12 useful MySQL configuration variables
Additional resources • •
Sign up for our TechRepublic NetNote, delivered on Mondays, Wednesdays, and Thursdays. Check out all of TechRepublic's newsletter offerings.
•
Take advantage of MySQL flexibility with this overview of table types (Download)
•
10 useful MySQL date and time functions (Download)
•
Backing up and restoring MySQL databases in three easy steps (Download)
Version history Version: 1.0 Published: June 2, 2005
Tell us what you think TechRepublic downloads are designed to help you get your job done as painlessly and effectively as possible. Because we're continually looking for ways to improve the usefulness of these tools, we need your feedback. Please take a minute to drop us a line and tell us how well this download worked for you and offer your suggestions for improvement. Thanks! —The TechRepublic Downloads Team
Page 4 Copyright ©2005 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html
