mysql performance tuning script

you to optimize the SQL statement for join. Also, the fields that are used for join should be of the same type. For example, if you want to join a DECIMAL field with an INT field, MySQL cannot use its index. For those string types, you also need to have the same character set. (The character set of two tables may be different) "Avoid select *" "always set an ID for each table" We should set an ID for each table in the database as its primary key, a

add the following line to the boot auto mount in the/etc/fstab file: /dev/sdb1/ssd ext3 defaults00 Now we move MySQL to the new diskFirst stop the MySQL service: #service mysqld stop I suggest you?? Stop Apache/nginx at the same time to prevent any attempts to write to the database: #service httpd stop #service nginx stop Now copy the entire

, where sending data accounts for 99% of the time. Still more than the initial page load time. Finally, instead of innerjoin the table with the select method to remove the relevant data, leftjoin the table data, by looping the select result set, the data is obtained separately. After the modified development environment page load time is 0.2~0.3s, the server environment is 0.4~0.5s. Although not ideal, performance is acceptable for small systems withi

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,

1. Macro-tuning can be considered three parts, respectively, hardware, network, software, the main consideration here is software tuning(1) Software tuning includes: table Design (Paradigm, field type, data storage engine), SQL statement Index, configuration file parameters, file system, operating system, MySQL version

MyBatisRemoving all the columns will make the optimizer unable to complete such optimizations as overwriting index scans, such as HibernateHowever, retrieving the query cache for all columns is more beneficial than multiple independent query caches that only get partial columnsEvery time I see a SELECT * Please wonder if you really need to take it all outDuplicate query for the same data: Please cache this data, for example, in the session.The simplest of three metrics to measure query overhead

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

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

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

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

· InnoDB Storage Engine 14. Using an Object-relational mapper (relational Mapper) With ORM (Object relational Mapper), you can gain reliable performance gains. All the things an ORM can do, can be written manually. However, this requires a senior expert. The most important thing about ORM is "Lazy Loading", that is to say, only when the need to take the value of the time to really do. But you also need to be careful about the side-effects of this mec

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

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

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

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

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

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