Mysiam Table Parameters Detailed

Http://imysql.cn/2008_09_27_deep_into_mysql_sort_buffer

http://my.oschina.net/realfighter/blog/364420

To improve the performance of MySQL, the first is to understand the configuration parameters of MySQL, after understanding the configuration parameters of MySQL, according to their own project needs and operating environment, and then make corresponding adjustments, then the following parameters are the official configuration parameters of MySQL, online information query, As well as personal conclusions drawn from various experiments of their own.

2. Skip-external-locking: Skips the external lock. To understand this parameter, you must first understand external-locking (external locking, the role is to myisam data table in multi-process "multiple services Common database directory" access under the Lock), in most cases, our MySQL service is a single process service, From the MySQL official web site, the skip-external-locking parameter is on by default,

   Mysql> Show variables like '%skip% '; +------------------------+-------+| Variable_name          | Value |+------------------------+-------+| Skip_external_locking  | On    |

   In the configuration file [mysqld], open this parameter ok.
4. Key_buffer_size = 256M: Open an index cache for thread sharing for the MyISAM data table. The data table in our project basically uses the InnoDB engine, so this parameter is not adjusted for the time being, it is necessary to refer to http://database.51cto.com/art/201010/229939.htm
6. Max_allowed_packet = 16M: The maximum allowable packet size for the server. When this configuration item is not adjusted, the server defaults to 4M. Of course this parameter and MySQL (default 16M) and mysqldump (the default is 24M, I have adjusted to 16M) in the packet size has a relationship, generally 1M can, the official recommendation if the use of a blog or a larger string when the adjustment of this parameter, in general, The database is initialized to the size of net_buffer_length (the minimum 1024byte, the maximum is 1M, and the default is 16KB).
8. Table_open_cache = 512: The number of open tables for all threads (the default setting is 1000). If Opened_tables is large and does not often use flush tables, the official recommendation is to increase the size of the parameter. This value is not the bigger the better, need to according to the actual situation open_tables and opened_tables synthesis to adjust, detailed visible http://www.cnblogs.com/suredandan/p/4010931.html
10. Sort_buffer_size = 512K: The cache size that requires a sort session is for each connection, and this value will not be as large as the default size is 256kb, and an oversized configuration consumes more memory. I have not tested it personally and can view http://bbs.chinaunix.net/thread-1805254-1-1.html
11. Read_buffer_size = 512K: Specifies the cache for MyISAM data table threads that require full table scans, and is alsofor each connection, this parameter for the moment I do not need too much attention.

     each thread, does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table I T scans. If you don't many sequential scans, you might want to increase this value, whichdefaults to 131072. The value of this variable should is a multiple of 4KB. If It is set to a value, which is nota multiple of 4KB, its value would be rounded down to the nearest multiple of 4KB. This option was also used in the following context for all search engines: for caching the indexes in a temporary file (no t a temporary table), when sorting rows for ORDER by. for BULK INSERT into partitions. for caching results of nested que Ries.and in one and storage engine-specific way:to determine the memory block size for memory tables. The maximum permissible setting for Read_buffer_size is 2GB. For more information on memory use during different operations, see sections 8.11.4.1, "How mysqluses memory". 

13. Read_rnd_buffer_size = 1 m: First, the variable can be used by any storage engine, and when a row is read from a sorted key-value table, it is fetched from the buffer and no longer fetched from disk. The default is 256K.

    This variable was used for reads from MyISAM tables, and, for any storage engine, for Multi-range readoptimization.when Rea Ding rows from a MyISAM table in sorted order following a key-sorting operation, the rows areread through this buffer to a void disk seeks. See sections 8.2.1.15, "ORDER by Optimization". Settingthe variable to a large value can improve ORDER by performance by a lot.  However, this was a bufferserver System variables627allocated for each client, so you should not set the global variable to A large value. Instead, change thesession variable only from within those clients, need to run large queries. The maximum permissible setting for Read_rnd_buffer_size is 2GB.

    Also refer to http://bbs.chinaunix.net/forum.php?mod=viewthread&tid=3642777&highlight=
14. Thread_cache_size = 18: How many threads are available for service cache use.

    How many threads the server should caches for reuse. When a client disconnects, the client's threadsare put in the cache if there is fewer than thread_cache_size threads ther  E. Requests for threadsare satisfied by reusing threads taken from the cache if possible, and only if the cache is empty ISA new thread created. This variable can is increased to improve performance if you had a lot of newconnections. Normally, this does not provide a notable performance improvement if you have a goodthread implementation. However, if your server sees hundreds of connections per second you shouldnormally set thread_cache_size high enough so th At the most new connections use cached threads. Byexamining the difference between the Connections and threads_created status variables, you cansee how efficient the Thre Ad Cache is. For details, see section 5.1.6, "Server Status Variables". The default value is based on the following formula, capped to a limit of 100:8 + (MAX_CONNECTIONS/100) This variable have No effect for the embedded server (LIBMYSQLD) and as of MySQL 5.7.2 are no longervisible within the embedded server. 

15. Query_cache_size= 8M: The amount of memory allocated to the query cache. To use with Query_cache_type, the default is not turned on. Just from the surface of the parameter, it seems that the larger the value is set, the better the effect will be, but notice how the query cache works, when a SELECT statement comes in, the database caches the query results into the cache, and when the same select query comes in, If the query results in this period of time does not change, the database will be cached in the cache results returned, then if the query related data table additions and deletions, the data table changes during this time, to the cache invalidation, and then update the data, for the deletion and modification, the time spent is much, So there's a tradeoff, and this parameter will be sorted out in the future with the relevant test data.

     by default, the query cache isdisabled. This was achieved using a default value of 1M, with a default for Query_cache_type of0. (to reduce overhead significantly if you set the size to 0, you should also start the server withquery_cache_type=0.the PE Rmissible values is multiples of 1024; Other values is rounded down to the nearest multiple. Note that query_cache_size bytes of memory be allocated even if Query_cache_type is set to 0.See section 8.9.3.3, "Query Cache Configuration "For more information. The query cache needs a minimum size of about 40KB to allocate its structures. (The exact sizedepends on system architecture.) If you set the value of query_cache_size too small, a warning willoccur, as described on section 8.9.3.3, "Query cache Con Figuration ". 

17. Query_cache_type = 1:1 means that all queries are cached, and 2 is a query that caches select Sql_cache to see the following
    

    0 or OFF do not cache results in or retrieve results from the query cache. Note Thatthis does not deallocate the query cache buffer.  To does, you should setquery_cache_size to 0.1 or on the cache all cacheable query results except for those this begin with Selectsql_no_cache.2 or DEMAND CACHE results only for cacheable queries this begin with SELECT Sql_cache.

19. Set Global max_connections = 500: Note This is set by the command line maximum number of connections, not configured in the configuration file, because I try to modify in the configuration file, after restarting the MySQL service does not work, after this parameter setting, after restarting the service, Still not working, if a friend knows the reason, please let me know. If your project is using spring's connection pool, I think the spring connection will correspond to this connection. Depends on the needs of your project.

key_buffer_size：

Index blocks for tables was buffered and is shared by all MyISAM threads. Is the size of the key_buffer_size buffer used fo R index blocks. The key buffer is also known as the key cache.

Mysiam Table Parameters Detailed