performance mysql

Lab Environment:db1:172.16.1.100Db2:172.16.1.101vrrip:172.16.1.99Steps:Yum-y install MySQL1. Modify the MySQL configuration file for DB1Server-id = 2 #DB1和DB2的id must be different log-bin=mysql-bin #开启mysql的二进制日志功能relay-log = mysql-relay-bin #

1. Write operations in the transaction, four isolation levels, all add exclusive locks.2. Read operations in the transaction, the first three isolation levels, no lock, the last isolation level, will be a shared lock.3. The above write and read

The most important and distinctive feature of MySQL is his storage engine architecture, which is designed to separate query processing (precessing) and its system tasks (Server Task) from the storage/extraction of data.1.1 MySQL Logic

Partition table:A partitioned table is a separate logical table that is implemented by multiple physical tables.The way MySQL implements partitioned tables is to encapsulate the underlying tables. This means that there is no global index, and the

Replication capabilities not only build highly available applications, but are also the basis for high availability, scalability, disaster recovery, backup, and data warehousing.MySQL supports two ways of replicating: statement-based replication and

Limitations of Union:MySQL cannot push the limit condition from the outer to the inner layer.Such as:Can be optimized to:Parallel execution:MySQL cannot use multi-core features to execute queries in parallel.Hash Association:MySQL does not support

Basic principles: Smaller is usually better: consumes less disk memory and CPU cache. Simple: For example, shaping is less expensive than character type. Use date types to store dates instead of strings. Use shaping to store IP

Logical architectureMySQL's logical architecture is divided into 3 layers, Connection thread processing. The core functionality of the server, query parsing, parsing, optimization, caching, and all of the inside to functions.

MySQL supports a very high number of data types, and choosing the right data type is critical to gaining performance.Several principles for choosing a data type1, smaller is usually better2, simple is good, such as using Date,time,datetime to store

1. mysql System architectureThe system structure that displays MySQL content, the entire system is divided three times:1) network connection and thread processing layer, this layer handles client connection request, authentication and threading,

An index is a data structure that the storage engine uses to quickly find records. Index optimization is the most effective way to optimize query performance. Indexes can easily improve query performance by a few orders of magnitude. Creating an

Specific statementsSelectdfm.id,dfm.member_code,dfm.member_name,dfm.recommend_code,dfm.member_rank,dfm.create_time,dfm.account, Dfm.have_account,dfm.member_img,dfmr.rank_name,dfmr.grade_commission_ratio,dfm1.member_name as

1.2 Concurrency control1.2.1 Read/write lockWhen dealing with concurrent reads or writes, the problem is solved by implementing a locking system consisting of two types of locks. These two types of locks are often referred to as shared locks and

Mysql's InnoDB storage engine is not a simple row-level lock, the implementation of MVCC, multi-version concurrency control, you can understand a variant of the line-level lock.The MVCC of InnoDB is achieved by saving two hidden columns behind each

Zabbix Monitoring Redishttp://www.21yunwei.com/archives/4195Zabbix monitoring MySQL Performancehttp://www.ttlsa.com/zabbix/zabbix-monitor-mysql/Zabbix monitoring MySQL loading stateHttp://www.linuxidc.com/Linux/2016-04/130436.htmZabbix Monitoring

MySQL Logical architecture diagram:First-tier clientSecond tier (service layer): the parts that can be publicly extracted for all types of storage engines. This is where the rest of the storage engine is pumped away. Such as: query parsing, analysis

Create high-Performance Indexes 1An index is a data structure used by the storage engine to quickly find records, which is the basic function of an index.Indexes are important for good performance.Index optimization should be the most effective way

For high-performance database operation, it is not enough to design the optimal library table structure, and to establish the best index, but also need reasonable design query. If the query is poorly written, high performance cannot be achieved even

1. LockWhy do I need a lock?because the database wants to solve concurrency control problems. At the same time, there may be multiple clients working on the same row of records in the table, such as some reading the row data, and other attempts to

Interface Tools:Msql Workbench http://www.mysql.com/products/workbench sqlyog http://www.webyog.com phpMyAdmin http://sourcefor Ge.net/projects/phpmyadmin [Not recommended] adminer phpMyAdmin better alternatives, http://www.adminer.org[not