mysql performance tuning parameters

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

Problem:A system, MySQL database, after the data is large. MySQL CPU utilization is very high, a Test side access to the server when MySQL CPU utilization is 15%, 6 test end of the server when the MySQL CPU occupancy rate is 50%~60%.PS 1: Each Test side of the thing is to insert records, but before inserting will first

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

://myprofi.sourceforge.net/ MySQL Network protocol sniffer tool-Mysqlsniffer Http://hackmysql.com/mysqlsniffer MySQL performance diagnostics and tuning related books: "High performance MySQL Second Edition" Optimized

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: 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

MySql Performance Tuning (3) Table Design I. first introduce several concepts:1. full function dependency: in the relational mode R (U) of the property set U, if X-> Y, and for any real subset of X ', if both have x' 2. Pass function dependency: In R (U), if X-> Y, Y-> Z, Z is called the transfer function dependency of X. Ii. Paradigm Relational databases ha

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 effect is, with 2/8 principles, M.F.B. s to solve most things. So kick, limited to the overall adjustment, not involved in specific details.1, InnoDBUsing the InnoDB engine2, Innodb_buffer_poolAdjust and innodb about the parameters, as much as possible in memory and InnoDB instance3. IndexIndex, needless to say.4. CacheQuery caching, such as Redis5. Batch Writesuch as queue cache data, there is a dedicated thread in the idle batch storage6, SQL ca

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

table_cache_sizeIt's going to request_open_files make corrections. // mysql table_cache_size 最小值，400 limit1 = TABLE_OPEN_CACHE_MIN // 根据 requested_open_files 计算 limit2 = (requested_open_files - 10 - max_connections) / 2 limit = max(limit1，limt2); If the configured table_cache_size value is greater than limit, the table_cache_size value is corrected to limit Other cases table_cache_size Preserve configuration values Example The

corrections.// mysql table_cache_size 最小值，400limit1 = TABLE_OPEN_CACHE_MIN // 根据 requested_open_files 计算limit2 = (requested_open_files - 10 - max_connections) / 2limit = max(limit1，limt2); If the configured table_cache_size value is greater than limit, the table_cache_size value is corrected to limit Other cases table_cache_size Preserve configuration values ExampleThe following use cases run under a non-root user参数设置：//

log Log_error[=name]//Log error messages for the Enable/run/stop processQuery log general_log,general_log_file=//Record client connection and query operationsSlow query log slow_query_log,slow_query_log_file=,long_query_time=//record query operations that take a long time or do not use indexesLog Slow query:Slow-query-log//Enable slow querySlow-query-log-file//Specify slow query log fileLong-query-time//queries that exceed a specified number of seconds (default 10 seconds) are loggedLog-queries

variable value can be used to improve performance. By comparing variables in Connections and Threads_created states, you can see the role of this variable. I set it to 80. Key Parameter 9: the number of seconds the wait_timeout server waits for action on a connection before closing it. The default value is 28800. I will change it to 7200. Note: you can modify the parameters by modifying the/etc/my. cnf fil

the number of empty means the cache is used unreasonable (always run out)2.4 MySQL Log type(1) Error log: Log information generated by the database service during startup and operation, enabled by defaultThe Log-error=/var/log/mysqld.log in/ETC/MY.CNF(2) Binlog log: Default is not enabled.(3) query log: Record all executed SQL commands, not enabled by defaultVim/etc/my.cnf[Mysqld]General-log #默认存储位置/var/lib/mysqlDefault name: Host name. log(4) Slow q

Label:Key_buffer_sizeMeaning: The buffer size used for the index block, increasing the index (for all read and multiple writes) that it can get better processing.Impact: The impact on the MyISAM table is not very large, MyISAM uses the system's cache to store the data, so machine memory that uses MyISAM tables is quickly exhausted. However, if the value is set too large (for example, greater than total memory 50%), the system is converted to a page and becomes extremely slow.

Mysql Tomcat C3p0 System Performance Tuning personal summary System Information The application logic is to use c3p0 to query data in the database and return Json data over http. 1. the initial test result JMeter test result before optimization No. Type Original 1000 data bigger 1 500 Connection 250 query/S 63q/S70q/S

Tags: MySQL performance tuning three-table designA First introduce several concepts: 1. Full function dependency: In the relationship mode R (U) on the attribute set U, if x->y, and for any one true subset X ' of X ', there is X ' ≠>y, then Y is dependent on the X full function. 2. Transfer function dependency: in R (U), if x->y,y->z, the Z-to-X transfer functio

Tags: MySQL performance tuning four quick modify table structure1. Modifying the varchar type To meet the requirements, the varchar (6) is now converted to varchar (8). The better approach is not to use altertable modify, but instead take the following steps: A Create a temporary table and set the varchar to varchar (8) as follows: B. Replace the. FRM tab