MySQL my.ini directives

tmp_table_size sort_buffer_size read_buffer_size

Submitted by chris on January 8, 2006 - 14:43

If you have complex queries sort_buffer_size and tmp_table_size are likely to be very important.
If the space required to build the temporary MyISAM table is less than or equal to tmp_table_size, MySQL keeps it in memory rather than incur the overhead and time required to write the data to disk and read it again. However, if the space required exceeds tmp_table_size, MySQL creates a disk-based table in its tmpdir directory.

To determine a good value for tmp_table_size:
Compare the relative sizes of the Created_tmp_tables and Created_tmp_disk_tables counters. You want to increase tmp_table_size if Created_tmp_tables is not a lot larger then Created_tmp_disk_tables (like in the order of 20x: 5%).

my.ini most important values: key_buffer_size table_cache

Submitted by chris on January 8, 2006 - 11:28

When tuning MySQL, the two most important variables to configure are key_buffer_size and table_cache. You should first feel confident that you have these set appropriately before trying to optimize any other variables. Ideally, key_buffer_size will be large enough to contain all the indexes (i.e. at least the total size of all .MYI files on the server) of your MyISAM tables.

MySQL recommendations (which appears to be on the conservative side...):
256MB system: key_buffer_size=64M table_cache=256 sort_buffer_size=4M read_buffer_size=1M
128MB system: key_buffer_size=16M sort_buffer_size=1M

How to determine, if you need to increase or decrease key_buffer_size:
Look at the "key_reads" (actual reads from the disk) and "key_read_requests" status variables.

query_cache_size query_cache_type query_cache_limit

Submitted by chris on January 8, 2006 - 01:15

my.ini directives

MySQL version 4.0.1 and later has a query cache that stores the identical SELECT queries issued by clients. It also stores the query's result set in the query cache, which can significantly reduce the overhead of creating complex result sets for queries from the disk or memory caches, reducing both physical and logical I/O.

The query_cache_size parameter is used to allocate an amount of memory to cache the frequently executed queries and return the result set back to the client. Check the qcache_inserts, qcache_hits, and qcache_free_memory during runtime.

thread_cache_size thread_concurrency

Submitted by chris on January 8, 2006 - 14:23

my.ini directives

The thread cache hit rate can be calculated with Threads_created/Connections. Ideally you want the threads cached to be able to handle all connections. So a stable (zero growth) or low Threads_created status variable is the goal.

A thread_cache_size of zero is the default for my-medium.cnf but the recommended size in my-large.cnf is 8.
Depending on your specific situation, in some case this value can be very high (like 150+).
For an idea of the value needed, check the status variable Max_used_connections. If possible have thread_cache_size equal to the peak Max_used_connections value.
According to my-large.cnf the value for thread_concurrency should follow: number of CPU's*2 for thread_concurrency

My Values:

max_allowed_packet max_connections Wait_timeout

Submitted by chris on January 11, 2006 - 05:31

my.ini directives

max_allowed_packet: A communication packet is a single SQL statement sent to MySQL or a single row that is sent to the client. Since MySQL 4.0.1, the maximum largest possible packet is 1GB (3.23.x was 16MB). The default value is 1MB. It's safe to increase the value of this variable because the extra memory is allocated only when needed. The small default value of the variable is a precaution to catch incorrect packets between the client and server and also to ensure that you don't run out of memory by using large packets accidentally.

skip-symbolic-links skip-external-locking (draft)

Submitted by chris on January 11, 2006 - 05:50

my.ini directives

Enable or disable symbolic link support. This option has different effects on Windows and Unix.
On Windows, enabling symbolic links allows you to establish a symbolic link to a database directory by creating a directory.sym file that contains the path to the real directory. See Section 7.6.1.3, “Using Symbolic Links for Databases on Windows” (from the MySQL documentation).

I'm currently running with:
skip-symbolic-links

Reference:
http://dev.mysql.com/doc/refman/5.0/en/windows-symbolic-links.html

The only case in which you cannot use --skip-external-locking is if you run multiple MySQL servers (not clients) on the same data, or if you run myisamchk to check (not repair) a table without telling the server to flush and lock the tables first. Note that using multiple MySQL servers to access the same data concurrently is generally not recommended, except when using MySQL Cluster.

PHP: Persistent database connections

Submitted by chris on January 12, 2006 - 20:50

You can turn persistent database connections on/off from your configuration file php.ini.
For MySQL:
mysql.allow_persistent = Off or On

It is difficult to see any difference in the speed, for me. If the speed seems the same turn it off.
This settings can have a direct correlation to the MySQL connection settings.