MySQL usage and optimization summary 1

Content from http://my.oschina.net/u/142836/blog/169415

Optimization using and optimizing the DB

Establish the appropriate index:

Try to get all queries indexed, this effect is very obvious.

Table Space Optimization

On a table that is frequently deleted or updated, if you include fields such as varchar,text, you need to perform table space optimizations on a regular basis, optimaize tables XXX, defragment disk fragments, Reclaim table data and free space occupied by index data;

Configuration parameter optimization

Innodb_buffer_pool_size InnoDB the memory buffer size of the table data and index data is critical to effectively reduce disk IO. Innodb_flush_log_at_trx_commit determines how the transaction log is logged, which is also critical for performance improvements, which can be considered when writing data in bulk on-line, with a setting of 0. Or a situation where write operations are frequent but a small amount of data is lost when a failure is allowed.
Query_cache This parameter is somewhat tricky because the query cache is invalidated when any data in the data table is modified, and may degrade performance for tables that are frequently written. For read-oriented tables, the effect is still obvious, but usually we all rely on the front-end cache, so for this parameter setting, it depends on the specific business scenario.
Max_connections control the number of concurrent connections, not too large, or the consequences are serious.

Splitting and Scaling:

Library Split: The general is the same instance of the database divided into multiple instances to share the pressure (this is relatively simple, do a copy, the application end of the IP on the line), or a library inside the partial table is placed in another instance of the library (this is more troublesome, need to apply the end of the application to modify the program).
Table split: Also divided into two, one is to remove some of the fields into the new table, such as by business, or like text, such as large pieces of the split. The other is that the number of table records is too large to exceed the single-table tolerance and needs to be scaled horizontally to multiple tables. Table splitting is cumbersome and requires application-side mates to modify the program.

Optimization of SQL

Try to use the index, you can use the primary key query the best
As far as possible to narrow the scanning range, the classic scene is the limit page offset optimization, in fact, in the actual business scenario there are many similar scenarios, we can completely by ID number or time limit to significantly reduce the query scan range
Minimize table connection queries, preferably single-table queries (table connections may use temporary tables, the DB consumes a lot, and a single-table query can be quickly returned, putting the calculation operations into the front-end application, reducing the db pressure). Poor-Performance table connection queries may drag the DB if the front-end concurrency is not well-controlled
Try to do the equivalent query, unequal condition query and reverse query do not go index
Use union instead of or, in operation
Do not make a front-facing fuzzy query, do not walk the index
Sorting and grouping operations are done as far as possible on the application side, reducing the CPU pressure on the DB
No function operation on query columns: SELECT concat (' foo ', ' Bar ') as str from XXX;

Optimization of applications

Try not to do operations on the DB side, do things on the application side do not rely on the DB
Data such as Text/blob is not stored in DB as much as possible, and can be used in other Key/value type storage
Large SQL split into small SQL check, do not make Table connection
Use a good connection pool to reduce connection overhead (pay attention to the configuration of connection pool idle time and database idle time)
With hot and cold data scenes, try to divide the pressure
Consider read and write separation (here to pay attention to slave delay, master write frequent cases, slave delay is another person very headache, data consistency sensitive application scenarios are hidden)
Finally, PreparedStatement, the biggest function is to prevent SQL injection. The precompilation feature can also be tried, but it's good to turn on the front-end SQL cache, and this is a scenario where most Internet applications don't have to rely on this feature to improve performance.

Simple Troubleshooting tips

Slow Query troubleshooting

Log view: Slow.log, this is set in the MySQL configuration file, to open.

Live View: SELECT * from Information_schema.processlist where time > 2;

Processing method:

Slow Query Log Analysis tool: Mysqlsla,mysqldumpslow, such as the best remedy for SQL optimization;

A real-time slow query can kill the query thread directly if it affects the response of the application. Execute kill [thread_id].

Lock Case Troubleshooting

Make sure the database has a lock situation look at two places, in the processlist can see the state that column has lock-related status, here can only see a state, the most important is to view the show InnoDB engine status through the following command, Detailed locks and information about the transactions occur are displayed here. As for how to solve, depends on the application of how to control.

Slave Delay troubleshooting

Perform show slave status on the slave instance to view the state of slave, focusing on the following three:

Slave_io_running:yes//The thread that is responsible for reading Binlog is running correctly
Slave_sql_running:yes//The thread responsible for executing SQL on Slave is running correctly
seconds_behind_master:0//slave time delay than Master, unit: Seconds
If the IO and SQL thread status is no, it means that the slave synchronization has stopped and you can see the latest error by Last_error. If you want to restore slave, generally two operations: first, redo the slave, to ensure that the data more accurate; one is to skip the error sql,stop slave;set global Sql_slave_skip_counter=1;start slave; This can cause slave data to be inconsistent by skipping one SQL or skipping too many bars.

Monitoring

Built-in commands
Status
Show Global Status
Show variables
External monitoring
A third-party monitoring tool that can provide a graphical interface. Cacti,ganglia and other open source software provide a plugin to monitor MySQL.

Simply put, MySQL is highly available

Two different ways:
Mode one: Use MySQL Cluster: Read scalability is good, write performance will have a certain decline. Not very mature, the online use of caution.
Mode two: Master + slave with virtual IP + LVS + keepalived to achieve a simple high-availability, the hidden danger of this scheme is: virtual IP switching gap will be short-lived, slave ascension to Master will have a failure;
At last
Database is generally stored in the application of key data, can be said to be a company's product life, so the security of the data is also very important, to do the right to control (strict control of permissions, as far as possible to prevent misoperation caused by data loss), timely backup data (offsite, multi-engine room), for the core sensitive data to do a good job

Recommended reading:

MySQL usage and optimization summary 1