Mysql development practice 8 Q: How many can you hold ?, Mysqlhold

Mysql development practice 8 Q: How many can you hold ?, Mysqlhold
Recently, the project has been heavily reliant on databases. I sorted out the eight representative questions that Mysql encountered during this period. The answers are also biased towards development practices, without DBA expertise and depth, please shoot bricks if there are any discrepancies !...

1. What are the performance-related configuration parameters of Mysql?
2. When the Mysql load is high, how can I find the SQL statements that cause this problem?
3. How to optimize specific SQL statements?
4. Is it difficult to optimize SQL statements? What is the response policy when the request volume continues to increase?
5. How does Mysql synchronize data?
6. How to Prevent database misoperations and perform disaster recovery?
7. Which storage engine does Innodb have?
8. What are the internal structures of Mysql?

========================================================== ====================

1. What are the Mysql read/write performance and what are the performance-related important parameters?

* Here are some simple stress testing experiments.
MACHINE: 8-core CPU, 8 GB memory
Table Structure (simulate business as much as possible): 12 fields (one bigint (20) are auto-incrementing primary key, five int (11), and five varchar (512 ), 1 timestamp)
Experiment 1 (write): insert => 6000/s
Prerequisite: the number of connections is 100. A single record is inserted each time.
Analysis: the CPU runs 50%, and the disk is written in sequence, so the performance is high.
Experiment 2 (write): update (where condition hit index) => 200/s
Premise: the number of connections is 100, and there are 10 million records. Four fields (2 int (11) and 2 varchar (512) of a single record are updated each time ))
Analysis: the CPU runs 2%, and the bottleneck is obvious in random write of IO.
Experiment 3 (read): select (where condition hit index) => 5000/s
Premise: the number of connections is 100, 512 records, and four fields (2 int (11) and 2 varchar () of each select single record ))
Analysis: the CPU runs 6%, and the bottleneck is IO, which is related to the database cache size.
Experiment 4 (read): select (where condition does not hit index) => 60/s
Premise: the number of connections is 100, 512 records, and four fields (2 int (11) and 2 varchar () of each select single record ))
Analysis: the CPU runs to 80%, and all records need to be traversed in each select statement. It seems that the index effect is very obvious!

* Several important configuration parameters can be adjusted according to the actual machine and business characteristics
Max_connecttions: Maximum number of connections
Table_cache: Number of opened tables in the cache
Key_buffer_size: Index cache size
Query_cache_size: query the cache size
Sort_buffer_size: Sorting cache size (the sorted data will be cached)
Read_buffer_size: sequential read cache size
Read_rnd_buffer_size: Read cache size in a specific sequence (for example, query by clauses)
PS: view the configuration method: show variables like '% max_connecttions % ';

========================================================== ====================

2. When the Mysql load is high, how can I find the SQL statements that cause this problem?

* Method: Slow query log analysis (mysqldumpslow)

* The Slow query log example shows the time consumed by each slow query SQL statement:
# User @ Host: edu_online [edu_online] @ [10.139.10.167]
# Query_time: 1.958000 Lock_time: 0.000021 Rows_sent: 254786 Rows_examined: 254786
SET timestamp = 1410883292;
Select * from t_online_group_records;
The log shows that the query took 1.958 seconds and 254786 rows of records were returned. A total of 254786 rows of records were traversed. And the specific Timestamp and SQL statement.

* Use mysqldumpslow to analyze slow query logs
Mysqldumpslow-s t-t 5 slow_log_20140819.txt
Top 5 SQL statements that consume the most time
-S: sorting method, t indicates by time (In addition, c indicates by number, r indicates by number of returned records, etc)
-T: the number of Top items.-t 5 indicates the first five items.
The analysis result is as follows:
Count: 1076100 Time = 0.09 s (99065 s) Lock = 0.00 s (76 s) Rows = 408.9 (440058825), edu_online [edu_online] @ 28 hosts
Select * from t_online_group_records where UNIX_TIMESTAMP (gre_updatetime)> N
Count: 1076099 Time = 0.05 s (52340 s) Lock = 0.00 s (91 s) Rows = 62.6 (67324907), edu_online [edu_online] @ 28 hosts
Select * from t_online_course where UNIX_TIMESTAMP (c_updatetime)> N
Count: 63889 Time = 0.78 s (49607 s) Lock = 0.00 s (3 s) Rows = 0.0 (18), edu_online [edu_online] @ [10.213.170.133]
Select f_uin from t_online_student_contact where f_modify_time> N
Count: 1076097 Time = 0.02 s (16903 s) Lock = 0.00 s (72 s) Rows = 52.2 (56187090), edu_online [edu_online] @ 28 hosts
Select * from t_online_video_info where UNIX_TIMESTAMP (v_update_time)> N
Count: 330046 Time = 0.02 s (6822 s) Lock = 0.00 s (45 s) Rows = 0.0 (2302), edu_online [edu_online] @ 4 hosts
Select uin, cid, is_canceled, unix_timestamp (end_time) as endtime, unix_timestamp (update_time) as updatetime
From t_kick_log where unix_timestamp (update_time)> N
Taking 1st SQL statements as an example, this type of SQL statement (N can take many values, Here mysqldumpslow will be merged) appears August 19 times in the slow query log in 1076100, the total time is 99065 seconds, A total of 440058825 rows of records are returned, which are used by 28 Client IP addresses.
Through slow query log analysis, you can find the most time-consuming SQL, and then analyze the specific SQL

* Configuration parameters related to slow queries
Log_slow_queries
Long_query_time: the number of seconds that the query time exceeds is considered as a slow query, which is generally set to 1 S.
Log_queries_not_using_indexes: Indicates whether to write records that are not indexed to slow query logs.
Slow_query_log_file: log storage path for slow Query

========================================================== ====================

3. How to optimize specific SQL statements?

* Use Explain to analyze the SQL statement execution plan
Mysql> explain select * from t_online_group_records where UNIX_TIMESTAMP (gre_updatetime)> 123456789;
+ ---- + ------------- + ------------------------ + ------ + --------------- + ------ + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------------------------ + ------ + --------------- + ------ + --------- + ------ + ------------- +
| 1 | SIMPLE | t_online_group_records | ALL | NULL | 47 | Using where |
+ ---- + ------------- + ------------------------ + ------ + --------------- + ------ + --------- + ------ + ------------- +
1 row in set (0.00 sec)
As shown in the preceding example, pay attention to type, rows, and Extra:
Type: indicates the category used. Whether or not the index is used. The result value ranges from good to bad:...> range (used to index)> index> ALL (full table scan). Generally, the query should reach the range level.
Rows: Number of SQL Execution check records
Extra: Additional information about SQL Execution. For example, "Using index" indicates that only index columns are used for queries, and you do not need to read tables.

* Use Profiles to analyze the SQL statement execution time and resource consumption
Mysql> set profiling = 1; (start profiles, which is disabled by default)
Mysql> select count (1) from t_online_group_records where UNIX_TIMESTAMP (gre_updatetime)> 123456789; (execute the SQL statement to be analyzed)
Mysql> show profiles;
+ ---------- + ------------ + Response +
| Query_ID | Duration | Query |
+ ---------- + ------------ + Response +
| 1 | 0.00043250 | select count (1) from t_online_group_records where UNIX_TIMESTAMP (gre_updatetime)> 123456789 |
+ ---------- + ------------ + Response +
1 row in set (0.00 sec)
Mysql> show profile cpu, block io for query 1; (you can see the time consumption and resource consumption of SQL in each link)
+ ---------------------- + ---------- + ------------ + -------------- + --------------- +
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+ ---------------------- + ---------- + ------------ + -------------- + --------------- +
...
| Optimizing | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| Statistics | 0.000020 | 0.000000 | 0.000000 | 0 | 0 |
| Preparing | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| Executing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000076 | 0.000000 | 0.000000 | 0 | 0 |
...

* SQL optimization skills (only some common business problems)
1. Key: Index to avoid full table scan.
Perform slow query analysis on the affected items and find that the top 10 items basically forget to add the index or the index is improperly used. For example, adding a function to the index field causes the index to fail (for example, where UNIX_TIMESTAMP (gre_updatetime)> 123456789)
+ ---------- + ------------ + ----------------------------------------- +
| Query_ID | Duration | Query |
+ ---------- + ------------ + ----------------------------------------- +
| 1 | 0.00024700 | select * from mytable where id = 100 |
| 1 | 0.27912900 | select * from mytable where id + 1 = 101 |
+ ---------- + ------------ + ----------------------------------------- +
In addition, many users prefer to use select xx from xx limit or to pull full table data in batch. In fact, this SQL is used for full table scanning every time, we recommend that you add an auto-increment id for indexing. Change the SQL statement to select xx from xx where id> 5000 and id <6000;
+ ---------- + ------------ + ----------------------------------------------------- +
| Query_ID | Duration | Query |
+ ---------- + ------------ + ----------------------------------------------------- +
| 1 | 0.00415400 | select * from mytable where id> = 90000 and id <= 91000 |
| 2 | 0.10078100 | select * from mytable limit |
+ ---------- + ------------ + ----------------------------------------------------- +
Using indexes properly should solve most SQL problems. Of course, the more indexes, the better. Too many indexes will affect the write operation performance.
2. select only the required fields to avoid select *
+ ---------- + ------------ + ----------------------------------------------------- +
| Query_ID | Duration | Query |
+ ---------- + ------------ + ----------------------------------------------------- +
| 1 | 0.02948800 | select count (1) from (select id from mytable) a |
| 1 | 1.34369100 | select count (1) from (select * from mytable) a |
+ ---------- + ------------ + ----------------------------------------------------- +
3. filter as early as possible to minimize the amount of data for subsequent operations such as Join or Union.
4. It refers to the logic layer for processing, such as data sorting and time function computing.
5 ,......

PS: for SQL optimization, there are already enough articles on KM, so I won't talk about it too comprehensively. I just want to focus on my one feeling: Index! Basically because of the index!

========================================================== ====================

4. Is it difficult to optimize SQL statements? What is the response policy when the request volume continues to increase?

* The following are several methods that I can think of. Each method is a big article, so I will not start it here.
Database/table sharding
Use a cluster (master-slave) for read/write splitting
Add service cache Layer
Use connection pool

========================================================== ====================

5. How does Mysql synchronize data?

* Replication)
The master node transmits the write operation of the master node to the slave through the binlog mechanism to generate relaylog. The slave then redo the redo log to keep the data of the master database and slave database synchronized.

* Copy the related three Mysql threads
1. I/O threads on slave: request data from master
2. Binlog Dump thread on the master: Read the binlog event and send the data to the slave I/O thread
3. SQL thread on slave: Reads and executes relay logs and updates the database.
It belongs to the slave active request PULL mode.

* Problems that may occur during actual use
Data inconsistency: asynchronous replication is used by default, and the data of master and slave may be delayed (called the master-slave synchronization distance, generally <1 s)
Higher master-slave synchronization distance: It may be caused by high DB write pressure, high server load on the slave machine, and network fluctuations. Specific problems

* Related monitoring commands
Show processlist: displays Mysql process information, including the current status of three synchronization threads.
Show master status: view master configuration and current replication Information
Show slave status: View slave configuration and current replication Information

========================================================== ====================

6. How to Prevent database misoperations and perform disaster recovery?

* What should be done on the business side:
Manual modification of important DB data requires 2 points before the operation: 1 first in the test environment 2 backup data
Scheduled Backup Based on business importance, taking into account the system's tolerable recovery time
It is necessary to conduct disaster recovery drills.

* Mysql backup and recovery operations
1. Backup: Use mysqldump to export data
Mysqldump-u username-p database name [Table name]> exported file name
Mysqldump-uroot-p test mytable> mytable.20140921.bak. SQL
2. Restoration: Import backup data
Mysql-uroot-p test <mytable.20140921.bak. SQL
3. Restore: Write Operations sent after the backup data is imported. Use mysqlbinlog to export the write operation SQL statement (based on the time point or location)
For example, export the binlog after 09:59:59:
Mysqlbinlog -- database = "test" -- start-date = "09:59:59"/var/lib/mysql/mybinlog.000001> binlog. data. SQL
For example, export the binlog after the start id is 123456:
Mysqlbinlog -- database = "test" -- start-position = "123456"/var/lib/mysql/mybinlog.000001> binlog. data. SQL
Finally, import the binlog to the database.
Mysql-uroot-p test <binlog. data. SQL

========================================================== ====================

7. Which storage engine does Innodb have?

* Storage engine Introduction
Plug-in storage engine is an important feature of Mysql. Mysql supports multiple storage engines to meet the needs of users in a variety of application scenarios.
The storage engine solves the problem: how to organize mysql Data to be efficiently read in the media should consider the storage mechanism, index design, concurrent read/write lock mechanism, etc.
The storage engines supported by Mysql5.0 include MyISAM, InnoDB, Memory, and Merge.

* Differences between MyISAM and InnoDB (focus only)
1. InnoDB
Default engine after Mysql5.5.
1. Support for row locks: Good concurrency Performance
2. transaction support: InnoDB is called a transaction storage engine. It supports ACID and provides transaction security with the ability to commit, roll back, and crash recovery.
3. Foreign keys supported: The only engine that currently supports foreign keys
2. MyISAM
Default engine before Mysql5.5.
1. Table locks supported: Fast insert and query speeds and slow update and deletion speeds
2. transactions are not supported.

* Use show engines to view the storage engine details currently supported by Mysql.


========================================================== ====================

8. What are the internal structures of Mysql?

* Non-professional DBAs. Here, only a structure diagram is provided. Mysql is an open-source system. Its design ideas and source code are all from the hands of Daniel. You can learn it if you are free.

1 Connectors: connector. Receive Client interaction in different languages
2 Management Serveices & Utilities: system Management and control tools
3 Connection Pool: Connection Pool. Manage user connections
4. SQL Interface: SQL Interface. Accept your SQL commands and return the results to be queried.
5 Parser: Parser. Verify and parse SQL statements into internal data structures
6 Optimizer: Query Optimizer. Select an appropriate execution path for the query statement
7 Cache and Buffer: Query Cache. Cache the query results, which can be directly returned if hit occurs.
8 Engine: storage Engine. Mysql Data is finally organized and stored as a specific file

PHP + MySQL + Dreamweaver 8 dynamic website development from Foundation to practice news and publishing system background management password?

You have a try. the username and password are both admin. Your login. php code will be uploaded to help you find it?

Php + mysql dynamic website development from basic to practice who has an electronic version of Jiang linmei?

I have never heard of this book. For php, I suggest you read php carefully, and the php and mysql authoritative guide. It must be better than yours.