MySQL Common configuration instructions

Source: Internet
Author: User

[mysqld]Configurationdefault-storage- engine = MyISAM Select a table storage engine by default ignore -bulidin- InnoDB ignores the InnoDB engine that comes with MySQL, and uses this configuration to introduce itself to the external InnoDB engine, otherwise the InnoDB table cannot be used. plugin -load= innodb=ha_innodb_plugin.soLoad the external engine, such as using the Ignore-Bulidin-After InnoDB, an external InnoDB engine needs to be introduced. Note the external engine files need to be placed in the directory specified by the Plugin_dir variable (config). If you have more than one external engine, use this format: plugin-Load= "Myplug1=Myplug1.so;myplug2=myplug2.so "init_connect = ' Set names UTF8 ' client connection, pre-executed command. -- Character-set-client-handshake does not ignore the client's encoding settings, the corresponding configuration is--skip-character-set-client-handshake, Ignoring the encoding settings of the client, directly using the encoding of the server-side character-set-server configurationDataDir Data Store directory sets the socket socket location Key_buffer_size Sets the memory buffer size of the index file (index block) of the MyISAM engine. Because MyISAM's index blocks are taken into memory, and the index blocks are shared by all threads, Key_buffer_size has a significant impact on the performance of MyISAM. How to determine whether the key_buffer_size setting is reasonable, view system state variables key_read_requests, key_reads, key_write_requests, and key_writes, general requirements, key_reads /Key_read_requests is less than 0.001, is more ideal. and Key_writes/ The ratio of key_write_requests is generally close to 1 unless you are using deferred writes, or are often bulk-updated. Max_allowed_packet allowed packet size Table_open_cache (old named Table_cache) cache Open Table handle number, you can observe the state variable, opened_tables determine whether to increase Table_ The size of the Open_cache. Sort_buffer_size Setting the sort buffer size for each connection is important when using the order by and group by values, because these two operations are difficult to improve performance with SQL optimizations, and it is important to increase the sort buffer to improve performance. Observing the sort_merge_passes state variable can help you determine if it is necessary to increase this value. It is important to note that the larger the value of the sort_buffer_size, the longer it will take to allocate, the more likely it is to make most of the query operations, so it is recommended that you set the session level, and then set the large value for each connection as needed. The read buffer size of the Read_buffer_size MyISAM read_rnd_buffer_size MyISAM is used to read the buffer size of the data queried by the order by. Myisam_sort_buffer_size MyISAM the buffer used to batch modify and rebuild indexes. Thread_cache_size the number of MySQL cache threads, caching threads for reuse, thereby reducing the consumption of initialization threads. Query_cache_size caches the buffer size of the query results max_heap_table_size and the maximum memory space for tmp_table_size memory tables and temporary tables. Max_connections Maximum number of connections long_query_time specifies a length of time, and a query that exceeds this time is identified as slow query. Slow_query_log and Slow_query_log_file whether to turn on slow log and slow log storage address Interactive_timeout and wait_timeout Interactive connection with non-interactive connection empty wait time. Innodb_buffer_pool_size InnoDB The amount of memory space used to cache data and indexes. Innodb_additional_mem_pool_size InnoDB is used to store the data dictionary and other internal use of information memory space size, when the application of the data table is too much, this value should be set to super large.

MySQL Common configuration instructions

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.