mysql tuning script

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

time should not be set too long, otherwise the meaning is not very good, preferably within 5 seconds, if you need a microsecond level of slow query, Consider a patch for MySQL: Http://www.percona.com/docs/wiki/release:start, remember to find the corresponding version.Turning on the slow query log may have a little impact on system performance, and if your MySQL is a master-slave structure, you can consider

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 have six paradigms: 1NF, 2NF, 3NF, BCNF, and 4N

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 index of the update split, avoid invalid index, n

This article does not involve complex underlying data structure, through explain interpretation of SQL, and according to the possible situation, to do the specific optimization, so that millions, TENS data Table Association query first page results can be completed in 2 seconds (real business alarm system optimization results). Hope that the reader can understand the SQL execution process, and according to the process optimization, to embark on their own "into gold road" needs to optimize the qu

Tags: my.cnf configuration tuningOptimized for pre_thread_buffers (can be understood as the memory allocated for each user process connected to MySQL): Read_buffer_sizeThis parameter represents the sequential scan of the table, which represents the size of the buffer allocated by each thread. In the case of full table scan, the data will be sequentially read in the order of storage, each time the database is read read_buffer_size, when the buffer

The MySQL lock is available in the following ways:1. Table-level lock, low overhead, lock fast, no deadlock, lock granularity is high, the probability of lock conflict is highest, and the concurrency is the lowest. The MyISAM engine belongs to this type.2. Row-level Lock: High overhead, locking slow, deadlock, locking granularity is minimal, the probability of lock collision is the lowest, and the concurrency is the highest. The InnoDB engine belongs

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 tuning (i) Select the type

environment that contains copies of production environment data.MySQL backup process:87. Backup on a level two replication server. 88, the backup process to stop the replication of data to prevent data dependencies and foreign key constraints inconsistent. 89, after completely stop MySQL, and then back from the data file. 90. If you are backing up with MySQL dump, back up the binary logs at the same time-e

multiple 5000 lines (you can use the idea of paging)2. Binary log transfer time between Master and slaveBetween master and slave as far as possible in the same room or region.The log format is changed to mixed, and the log format of the set line is not minimal, as described in the log format above.3. Reduce the time to restore logs from the librarySQL multithreading can be allocated using logical clocks after the MySQL5.7 version.Set logic clock: slave_parallel_type= ' logical_clock ';Set Numbe

1m-2m size, and then set it individually on demand in each session. 4, Read_rnd_buffer_size For sorting and order by operations, it is best to set it to 1M and then set it as a session variable to a larger value in the session. (v) Slow query log A slow query log is a useful feature of MySQL. 1, Log_slow_queries MySQL parameter in the log_slow_queries parameter set it in the My.cnf file, set it to on, by d

Label:DbaDatabase server???? Responding to client connection requests is particularly slow Hardware configuration is too low--cpu, memory, hard disk Network transfer speed Excessive client access, busy database server Software problem--version performance of data Service software No A problem with the SQL statements written by the programmer ?Hard TuningSoft tuning--operating parameters of the service runtime?1. The clien

original character set, Latin1 is promoted to be the UTF8 character set. Original table: old_table (Default charset=latin1), new table: new_table (Default Charset=utf8).First step: Export old dataMysqldump--default-character-set=latin1-hlocalhost-uroot-b my_db--tables old_table > Old.sqlStep two: Convert the encodingIconv-t utf8-f latin1-c old.sql > New.sqlHere, it is assumed that the original data is latin1 encoded by default.Step Three: ImportModify Old.sql, add an SQL statement: "SET NAMES U

First, data type optimizationData type Integer Number types: integers and realstinyint (8), smallint (16), Mediuint (24), int (32), bigint (64) numbers indicate the corresponding maximum number of storage bits, such as tinyint ( -127---), tinyint unsigned Indicates that negative numbers are not allowed, the range is (0--255).an int (11) in a regular database simply means that the number of control display characters is 11, and int (1) and int (20) are stored and computed the same,

Tags: Database tuning database performance optimizing MySQL performanceVi. other optimization methods of MySQL Database In the last chapter, we talk about some optimization methods that are not covered before. 1. My.ini configuration file Here are only two of the most important, but the others are more. 1) Configure the maximum number of connections The default i

configuration file from MySQLVim/usr/local/mysql_slave/my.cnfServer-id 1 changed to server-id 111Create a MySQL library and copy the same table as the master MySQL" CREATE DATABASE DB1 "Mysql-s/tmp/mysql_slave.sock 123. sqlLog in from MySQLMysql-s/tmp/mysql_slave.sockStop the operation first,> slave stop;> change Master to master_host='127.0.0.1', m

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

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

Tags: des style blog http color os ar for strong1.yumYum-y install mysql-server MySQL2.RPM Installationhttp://dev.mysql.com/downloads/Download RPM Package, please confirm the server version, my is Red Hat 5.9, download is the following version, bundle is a collection packageMysql-5.6.21-1.rhel5.x86_64.rpm-bundle.tar[Email protected] ~]# cd/soft/[[email protected] soft]# ll Total608028-rwxr--r--1Root root310988800 Ten- - -: -

Tags: MySQL performance tuning six InnoDB engine and MyISAM engine performanceThe choice of the two storage engines depends on the application characteristics of the project, and for the complex application system, it is possible to select a combination of various storage engines according to the actual situation. But this method is not recommended to be taken. MyISAM supports full-text indexing, which is a