Configuring MySQL

Background

The main system configuration file is /etc/my.cnf.

MySQL is extremely versatile and can be configured in many ways. Find the optimum configuration for your particular environment is an interative process. Starting with reasonable assumptions can speed up this process.

This exercise has five real life examples of my.cnf file. They are all different because they are used in different situations.

Objectives

• Examine five different my.cnf scripts;
• Create a configuration script that works best for your environment.

Steps

1. Examine the different my.cnf scripts;
• There are five different my.cnf configuration scripts.
• Configuration A
• Configuration B
• Configuration C
• Configuration D
• Configuration E


2. Examine Configuration A
• The configuration is for a dedicated pentium dual core with 4GB RAM.
• Notice the following about the entries in this file.
• It uses port 3306, which is the standard port.
• The slow query log is enabled.
• long_query_time is set to the minimum.
• query_cache_limit is set to the default value.
• The server is allowed to use an old hashing algorithm for passwords.
• The path to process ID file used by mysqld_safe has been set.
• Users are restricted (although at a high level) with:
• max_connections
• max_connect_errors
• max_user_connections
• There is no host_cache_size entry and DNS lookup has been disabled.

What other observations can you derive from this configuration file?

3. Examine Configuration B
• The configuration is for a Web portal with a database holding about 120 tables.
• Notice the following about the entries in this file.
• It uses port 3307, which is not the standard port.
• The slow query log is enabled.
• long_query_time is set to three seconds.
• table_cache is greater than the number of tables in the database.
• The default value is 64, which less than the number of tables.

What other observations can you derive from this configuration file?

4. Examine Configuration C
• The configuration is for a 2GHz machine with 1GB of memory.
• Notice the following about the entries in this file.
• query_cache_size
  • In this situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.
• key_buffer_size
  • The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server.
  • Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).
  • Check the actual performance of the buffer by examining four additional variables:
    • key_read_requests
    • key_reads
    • key_write_requests
    • key_writes
  • Divide the value of key_reads by the value of key_read_requests, the result should be less than 0.01.
  • Divide the value of key_writes by the value of key_write_requests, the result should be less than 1.
• table_cache
  • The default is 64.
  • Each time MySQL accesses a table, it places it in the cache.
  • If the system accesses many tables, it is faster to have these in the cache.
  • MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table.
  • Examine the value of open_tables at peak times.
  • Increase table_cache if there is enough memory.
• sort_buffer
  • sort_buffer is very useful for speeding up myisamchk operations, and when performing large numbers of sorts.
    • Set it much higher than the default.
• read_rnd_buffer_size
  • read_rnd_buffer_size is used after a sort, when reading rows in sorted order.
  • If using many queries with ORDER BY, upping this can improve performance.
  • Unlike key_buffer_size and table_cache, this buffer is allocated for each thread.
  • It defaults to the same size as the read_buffer_size.
  • Allocate 1KB for each 1MB of memory on the server.
    • For example 1MB on a machine with 1GB memory.
• thread_cache
  • Increase this if the server gets lots of quick connections.
  • This should take some of the load off of the CPU.


5. Examine Configuration D
• The configuration is for MySQL running only InnoDB.
• Notice the following about the entries in this file.
• The two datafiles are on different disks.
• Putting log files on a different disk from data is very often beneficial for performance.
• The log files are on a third disk.

What other observations can you derive from this configuration file?

6. Examine Configuration E
• The configuration is for a 1.4 GHz pentium machine with 512MB RAM, hosting about 2000 albums and about 150000 songs, and using InnoDB tables.
• This system runs very slow.
• Notice the following about the entries in this file.
• This is a simple, standard my.cnf file.
• It has no settings for optimizing InnoDB.
• It has no settings for caching.

What changes could be made to this configuration file to improve performance?

7. Review the findings
• Discuss your observations with your fellow classmates.
• With your fellow classmates, prepare a my.cnf file to begin tuning your system.

Congratulations! You have successfully examined my.cnf configuration scripts.