MySQL my.ini directives
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%).
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.
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.
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
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 22.214.171.124, “Using Symbolic Links for Databases on Windows” (from the MySQL documentation).
I'm currently running with:
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.
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.
5.2.1. mysqld Command Options:
5.2.2. Server System Variables:
5.2.4. Server Status Variables: