MySQL optimized under

More comprehensive MySQL optimization reference (next) 8 replies. This article has compiled some general MySQL optimization methods, a simple summary share, designed to help those who do not have full-time MySQL DBA to do basic optimization work, as for the specific SQL optimization, Most by adding the appropriate index to achieve the results, more complex needs to be specific analysis, you can refer to some of the optimization of this site case or contact me, below have my contact information. This is the next article. 3, MySQL layer related optimization 3.1, about version selection official version we call Oracle MySQL, this is nothing to say, I believe most people will choose it. I personally strongly recommend that you choose the Percona branch version, which is a relatively mature, excellent MySQL branch version that has improved in terms of performance improvements, reliability, and management. It is basically fully compatible with the official Oracle MySQL version and has a performance improvement of about 20%, so I recommend it first, and I've been focusing on it myself since 2008. Another important branch version is MARIADB, which says that MARIADB is a branch version that is not quite appropriate because its goal is to replace Oracle MySQL. It is mainly in the original MySQL server layer made a lot of source-level improvements, but also a very reliable, excellent branch version. However, there are new features that are not compatible with Gtid and the official version (MySQL 5.7, which also supports Gtid mode on or off), and that most people will follow the official version, so there is no priority to recommend MARIADB. 3.2, about the most important parameter option adjustment recommended to adjust the following key parameters to achieve better performance (you can use the MY.CNF generator to generate profile template): 1, select Percona or MARIADB version, it is strongly recommended to enable the thread pool feature, This allows for high concurrency, where performance does not degrade significantly. In addition, there are extra_port functions, very practical, the key moment can help. There is another important feature is the Query_response_time function, but also allows us to the overall SQL response time distribution has intuitive feelings; 2, set Default-storage-engine=innodb, also is the default use InnoDB engine, It is highly recommended not to use the MyISAM engine, InnoDB engine can definitely meet more than 99% of the business scenario, 3, adjust the innodb_buffer_pool_size size, if it is a single instance and the vast majority of InnoDB engine table, Consider setting the physical memory to 50% ~ 70% or so, 4. Set the innodb_flush_log_at_trx_ according to the actual needThe value of Commit, Sync_binlog. If the requirement data cannot be lost, then two are set to 1. If you allow the loss of a bit of data, you can set it to 2 and 10, respectively. If the care data is not lost at all (for example, on slave, it's a big deal to redo), it can be set to 0. These three sets of values cause the performance of the database are: High, medium, low, that is, the first one will be the slowest database, the last one is the opposite; 5, set innodb_file_per_table = 1, using a separate table space, I really can't think of the benefits of using shared table space; 6 , set Innodb_data_file_path = Ibdata1:1g:autoextend, do not use the default 10M, otherwise high concurrency transactions, will be affected; 7, set up innodb_log_file_size=256m, Set innodb_log_files_in_group=2, basically can meet more than 90% of the scene; 8, set long_query_time = 1, and above the 5.5 version, can be set to less than 1, it is recommended to set to 0.05 (50 milliseconds), record those who perform slower SQL for subsequent analysis and troubleshooting, 9, according to the actual needs of business, appropriate adjustment max_connection (maximum number of connections), Max_ Connection_error (maximum number of errors, recommended to be set to more than 100,000, while Open_files_limit, Innodb_open_files, Table_open_cache, Table_definition_ The cache these parameters can be set to about 10 times times the size of max_connection, 10, the common mistake is to set tmp_table_size and max_heap_table_size relatively large, have seen set to 1G, These 2 options are allocated for each connection session, so do not set too large, otherwise it can easily cause oom to occur; some other connection session-level options such as: Sort_buffer_size, Join_buffer_size, Read_buffer_size, Read_ Rnd_buffer_size, and so on, also need to be careful not to set too large, 11, because it is recommended no longer use the MyISAM engine, so you can set the key_buffer_size to about 32M, and strongly recommend to close the query cache function; 3.3, About schema design specification and SQL usage recommendations here are a few common schema design specifications and SQL usage recommendations that will help improve MySQL efficiency: 1. All InnoDB tables are designedA non-business use of the self-increment of the key, for the vast majority of scenes are so, the true pure read-only with InnoDB table is not much, so it is better to use TOKUDB to get a good deal; 2, the field length to meet the needs of the premise, as far as possible to choose a small length. In addition, the field properties as far as possible with NOT NULL constraints, can improve the performance of a certain degree; 3, if possible, do not use the Text/blob type, it is recommended to split into the child table, do not put together with the main table, avoid the read performance is poor when select *. 4, when reading data, select only the required columns, do not select * Every time, avoid serious random reading problems, especially read some text/blob columns; 5. When you create an index on a varchar (N) column, you typically take 50% (even smaller) the length of the prefix index is sufficient to meet more than 80% of the query needs, there is no need to create a full-length index of the whole column, 6, usually, sub-query performance is poor, proposed to change the join writing, 7, multi-table join query, the associated field type as consistent as possible, and must have index; Multi-table connection query, the result set of small table (note, here refers to the filtered result set, not necessarily the full table data is small) as a driver table; 9, multi-table joins and sorting, the sort field must be the driver tables, otherwise the column sequence can not be used to index; 10, multi-use composite index, less use multiple independent indexes In particular, some cardinality (cardinality) is too small (for example, the column has a total number of unique values of less than 255) do not create a separate index, 11, similar to paging function of SQL, it is recommended to first use the primary Key association, and then return the result set, the efficiency is much higher; 3.3. Other recommendations on MySQL management and maintenance of other recommendations are: 1, usually, single table physical size of not more than 10GB, the number of single-table row not more than 100 million, the average length of the line is not more than 8KB, if the machine performance is sufficient, these data amount of MySQL is fully able to handle the come over, do not worry about performance issues, This recommendation is mainly to consider the cost of online DDL is high, 2, do not worry too much memory mysqld process, as long as no oom kill and use a lot of swap is OK; 3, in the past, the purpose of running multiple instances on a single machine is to maximize the use of computing resources, If a single instance can already exhaust most of the computing resources, there is no need to run more instances; 4. Periodically use Pt-duplicate-key-checker to check for and remove duplicate indexes. Regular use of the Pt-index-usage tool to check and remove the use of low-frequency index; 5, regular collection of slow query log, with pt-query-digest tools for analysis, can be combined with anemometer system slow Query management to analyze slow query and perform subsequent optimization work; 6, can use Pt-kill to kill a long time SQL request, Percona version has an option innodb_kill_idle_transaction can also achieve this function; 7. Use Pt-online-schema-change to complete the online DDL requirements for large tables, 8, regularly use Pt-table-checksum, pt-table-sync to check and repair MySQL master-slave replication data differences ; PostScript: This article according to individual years experience summary, individual suggestion may have imperfect place, welcome message or add me public number: MySQL Chinese network, qq:4700963 Mutual discussion exchange. Written at the end: This time the optimization reference, in most cases I have introduced the applicable scenario, if your application scenario and this article describes the different, then it is recommended to adjust according to the actual situation, rather than mechanically. Welcome to question the shooting of bricks, but refuse to pass through the habitual resistance of the brain. Appendix: Extended reading 1, Common PC Server array card, hard disk health monitoring 2, PC server array card management simple manual 3, measured RAID5 vs raid1+0 InnoDB performance 4, SAS vs SSD various modes of MySQL TPCC OLTP comparison test results 5, MySQL out of the door, Percona on the left, mariadb in the right 6, Percona Thread Pool Performance benchmark 7, [MySQL optimization case] Series-page optimization 8, [MySQL FAQ] series-why InnoDB table to recommend the use of self-increment the key 9, [MySQL FAQ] Series-Why turn off query cache and how to close

Http://imysql.com/2015/03/27/mysql-faq-why-should-we-disable-query-cache.shtml

MySQL optimized under