mysql query performance tuning

MySQL Management: Performance tuning, high availability and monitoring mini-bookmysql5.5.x Main Improvements1. Use InnoDB storage engine by default2. Make full use of CPU multi-core processing capability3, improve the number of brush write dirty pages and merge the number of inserts, improve I/O4, let Innodb_buffer_pool buffer pool of heat data to survive longer,

Buffer"->: "10.4.3 Sort Buffer and Join Buffer" From ochefRecommendation sequence 2Original article: "Blog: http://www.orawh.com /"->: "Blog: http://www.ixdba.com /" P18Original article: "such as update, delte, insert, create"->: "Such as update, delete, insert, and create" Latest Update: 2009.10.19 From: Jasper CheP108Original article: "TL_WRITE_ALLOW_WRITE"->: "WRITE_ALLOW_WRITE"Original article: "P108 L7 concurrent Current read lock is the lock type of Read_NO_INSERT"->: "At the same time, C

Tags:/etc/serve linux show via experience performance help insertionMySQL restricts the size of packets accepted by the server according to the configuration file.Sometimes large inserts and updates are limited by the Max_allowed_packet parameter, resulting in failure.View current configurationShow VARIABLES like '%max_allowed_packet% ';The results shown are:+--------------------+---------+| variable_name | Value |+--------------------+---------+| Max

upper limit, but the query performance could not be improved. 5. use jconsole to check the thread status in tomcat. the multi-thread mode is blocked on java. util. logging. the console is originally a thread that has been waiting for log writing to be blocked. Comment out all the statements that write logs. The OK system is finally flying. Up to 250q/S. To check: CPU Memory

competitors in the database field, one in the past. As a result, the database market structure will change significantly, can MySQL bring more surprises to everyone under the guidance of a company like ORACLE that has a deep understanding of the database system? Let's wait and see! Renewal and revitalizationNetease high-level DBAAuthor of MySQLBlog: http://zhaizhenxing.blog.51cto.com/ Note: The copyright of this referral order belongs to to revitaliz

(1,23), (2,34), (4,33);mysql> inserts into a values (8,26), (6 ;mysql> UNLOCK TABLES; The main speed difference is that the index buffer is purged only once to the disk, after all INSERT statements have been completed. There is generally an index buffer flush that takes the same as a different insert statement. If you can insert all the rows with a single statement, the lock is not required. Locking also r

Original: http://bbs.landingbj.com/t-0-246222-1.html http://bbs.landingbj.com/t-0-245851-1.htmlOptimization of the MyISAM storage engine in MySQL performance tuning.The points to consider in optimizing the MyISAM storage engine are as follows:As far as possible index, MyISAM only cache index does not cache data;Adjust read-Write priority according to actual demand;Delay insertion, use Insert delay, reduce a

Tags: creating overrides efficient possibilities setting performance BBS HTML nbspOriginal: http://bbs.landingbj.com/t-0-245452-1.html For the optimization of the index, we first need to find the appropriate field, the second create the index to find the appropriate order, the third to find the appropriate proportion, and four is to do the appropriate maintenance. First you need to find the right field. Methods are: Improve filtering, reduce the inde

higher the better. Iv. innodb_additional_mem_pool_size (Default 8M) Innodb_additional_mem_pool_size sets the amount of memory space that the InnoDB storage engine uses to hold data dictionary information and some internal data structures, so when we have a very large number of database objects in MySQL instance, It is necessary to adjust the size of this parameter appropriately to ensure that all data can be stored in memory for increased access eff

reading or writing to the same table, and the read and write operations of other processes are performed only after the write is released."Example":Open another session:Session 2 will wait until the session is released with a lock.Simultaneous session 2 execution:Two Demo of row LocksThe InnoDB storage engine is implemented by locking the index entries, which means that the InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise INNODB will use a table lock.1. Row

Time Type:1.5 time types are supported in MySQL, namely date, hour, DATETIME, timestamp, and year.In the daily build table we want to use the timestamp type first, it has the function of automatic update time, example:You can also set not to update automatically, only the default value is empty. Such as:2. Year (2) type is automatically converted to year (4) typeMySQL Performance

the index name"Created November 27, 2016"Mysql5.7/percona Version 5.7 Support2) The online DDL modifies the varchar field property when the table is not locked"created November 27, 2016"MySQL 5.7 has been enhanced for DDL functionality, and modifying the varchar field has not locked the table.* Algorithm for specifying the algorithm to create or delete an index* Copy means to create a temporary table and copy the data in full form, with the original

A, sub-tableHorizontal DivisionVertical DivisionSecond, read and write separationThird, choose a reasonable data type, especially the primary keyIv. files, pictures and other large files using file system storageFive, database parameter configurationNote: The maximum number of max_connections connections is usually set at around 1000 to 2000 to the limit.Query_cache_size Query Cache SizeSix, reasonable hardware resources and operating systemMySQL Data