MySQL front-end and backend system optimization

2011-03-30  来源:本站原创  分类:Database  人气:64 

Optimization of the system described in this article, mainly for both front and back (back in the main on the SQL statements and data storage optimized), below, we will introduce some optimization techniques and experience.
1 How to find out the low efficiency of the statement?
In MySQL, the starting parameter set - log-slow-queries = [file name], you can specify the log file takes longer than long_query_time (default is 10 seconds) the SQL statement. You can also modify the configuration file at startup time to long query, such as:
# Set long query time to 8 seconds
long_query_time = 8
(2) how to query a table index?
You can use SHOW INDEX statement, such as:
SHOW INDEX FROM [table name]
3 How to check the index of a statement of use?
EXPLAIN statement can look at the index of a SELECT statement use. If it is UPDATE or DELETE statement, you need to convert the SELECT statement.
4. INNODB engine how to export the contents of the error log file?
We can use SHOW INNODB STATUS command to see the INNODB engine a lot of useful information, such as the current process, transactions, foreign key error, deadlock and other statistical data. How to make the information can be recorded in the log file? Just use the following statement to create innodb_monitor table, MySQL will be every 15 seconds to the system write to the error log file:
CREATE TABLE innodb_monitor (a INT) ENGINE = INNODB;
If you no longer need to export to the error log file, simply delete the table you can:
DROP TABLE innodb_monitor;
5 How to delete large log files on a regular basis?
As long as the startup configuration file to set the expiration time to the log:
expire_logs_days = 10
1 focus on the index to the table TSK_TASK table below an example SQL statement optimization. TSK_TASK table to save the system monitoring tasks, related field and index are as follows:
ID: primary key;
MON_TIME: monitoring time; built index;
STATUS_ID: task status; with SYS_HIER_INFO.ID established a foreign key relationship.
Note MySQL automatically indexes for foreign keys, in this optimization process, found that the foreign key index is automatically created SQL statement would be unnecessary interference with the efficiency, need special attention!
First, we found in the log file, execute the following statement is slower, more than 10 seconds:
# Query_time: 18 Lock_time: 0 Rows_sent: 295 Rows_examined: 88143
select * from TSK_TASK WHERE STATUS_ID = 1064 and MON_TIME> = '2007-11-22 'and MON_TIME <'2007-11-23';

The original record in the 88,143 eligible to be found 295 records, of course, slow. EXPLAIN statement quickly look at the index with the use of it:
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | TSK_TASK | ref | FK_task_status_id_TO_SYS_HIER_INFO, TSK_TASK_KEY_MON_TIME | FK_task_status_id_TO_SYS_HIER_INFO | 9 | const | 276168 | Using where |
It can be seen, there are two indexes available FK_task_status_id_TO_SYS_HIER_INFO, TSK_TASK_KEY_MON_TIME, and eventually execute a statement on the use of STATUS_ID foreign key index.
Take a look TSK_TASK table index of it:
| Table | Key_name | Column_name | Cardinality |
| TSK_TASK | PRIMARY | ID | 999149 |
| TSK_TASK | FK_task_status_id_TO_SYS_HIER_INFO | STATUS_ID | 16 |
Oracle or other relational database in the next, WHERE conditions in the field order of the index selection plays an important role. We adjust the field order, the STATUS_ID on the back, and then EXPLAIN this:
EXPLAIN select * from TSK_TASK WHERE MON_TIME> = '2007-11-22 'and MON_TIME <'2007-11-23' and STATUS_ID = 1064;
However, no effect, MySQL, or use the system established STATUS_ID foreign key index.
Careful analysis, it appears Cardinality properties (ie the index number of unique values) of the index selection played an extremely important role, MySQL index value is selected a small number of unique values ​​of that index as a whole statement index.
For this statement, if you do use FK_task_status_id_TO_SYS_HIER_INFO index, TSK_TASK many days of data stored in the table, then scan the number of records will be a lot slower. Can have the following optimization program:
If the small number of tasks a day, we remove the index FK_task_status_id_TO_SYS_HIER_INFO, then MySQL will use index TSK_TASK_KEY_MON_TIME, then in the days of data in the scan STATUS_ID 1064 records, that speed is not slow;
If the number of multi-day task, then we need to remove the index FK_task_status_id_TO_SYS_HIER_INFO and TSK_TASK_KEY_MON_TIME, then create STATUS_ID, MON_TIME joint index, so that efficiency will certainly be very high.
Therefore suggested that the number of those records more than a table, it is recommended not to use foreign keys in order to avoid serious degradation of performance efficiency.
(2) try to control the number of records in each table a table when a large number of records, the management and maintenance will be a lot of trouble, such as index maintenance will take a long time, and thus the normal operation of the system will cause great interference.
Over time the amount of data on the growing table, we can distinguish between real-time data and historical data, you can use daemons periodically moving real-time data in the table to the history table, to control the number of records in real-time table to improve query and operational efficiency. But pay attention to each movement of time short enough not to affect the normal process of data writing. If you take too long, may cause a deadlock.
3 data hash (partition) strategy when the number of clients reaches a certain size, a single database will not be able to support more concurrent access, time to consider the customer data hash (partition) to multiple databases to share the load, improve overall system performance and efficiency.

  • MySQL front-end and backend system optimization 2011-03-30

    Optimization of the system described in this article, mainly for both front and back (back in the main on the SQL statements and data storage optimized), below, we will introduce some optimization techniques and experience. Skills: 1 How to find out

  • How to system optimization 2010-11-05

    Two days before the discussion with my colleagues, talking about how to do optimization of highly concurrent systems, referred to this issue, he said he was a bit dazed, a little do not know where to start. I thought the past few years a variety of s

  • MySQL Front is very annoying Row XX doesn't contain data for all columns error 2010-12-09

    MySQL Front Version 5.1 B2.7 Phenomenon: when the text import TXT (3W multi-line) frequent Row XX doesn't contain data for all columns error Solving process: Because too many rows, it is difficult to determine what is in the end reason, the data init

  • Ubuntu system optimization 2011-06-14

    Ubuntu8.04 Series II - system optimization articles Welcome to reprint articles, please indicate the source : Yuan Jiajun ORIGINAL :,15151058.shtml Ubuntu8.04 Talked about the following steps t

  • xfs file system optimization 2010-04-07

    Online collection of xfs file system optimization program, to be a summary of their own. Xfs formatted partition mkfs.xfs first parameter: mkfs.xfs -f -i size=512 -l size=128m,lazy-count=1 -d agcount=16 /dev/sdb1 -I size = 512: The default value is 2

  • Mysql Front program keys, 5.0 and 5.1 2011-04-26

    Mysql Front expire will be prompted to: Program Registration time has expired! Program will run in restricted mode. Obviously, the need to crack the program. . The following procedures to collect two sets of Mysql Front key, 5.0 and 5.1. Mysql Front

  • Mysql Front KeyGen RI 2011-10-09

    Mysql Front KeyGen RI

  • linux system optimization notes 2010-07-21

    Today, finally resigned. Report tomorrow to the new owner. Name is the technical director, the real me a leader without any followers. Oh, but a few days will find a few people. The original company to do technology all gone. The original system desi

  • MYSQL Official Handbook - doc document Performance Optimization 2010-10-28

    Optimization is a complex task because ultimately requires understanding of the whole system to be optimized. Although local optimization without the need to understand the system or application, in order to optimize better, you need to know more inf

  • MySQL: Where clause with the joint optimization of the index 2011-06-01

    This site has on-line system, the amount of data has unwittingly on to 500M, almost 8W records. Are involved in the basic database operations become very slow, people will feel impatient with the fire ~ ~ then the situation where one group, including

  • MySQL uses the classic (9 )---- performance optimization and the use of techniques 2010-08-19

    This paper is summarized from the work out. As a matter of time and can not be considered sufficiently comprehensive, so this will be a very long time to complete, the following will continue to give content to share with you ^ _ ^ In windows, the co

  • linux system optimization 2011-05-25

    Overview 1. "/ Etc / profile" file 2. "Bdflush" parameter 3. "Ip_local_port_range" parameter 4. "/ Etc / nsswitch.conf" file 5. "/ Proc" file system 6. "Ulimit" parameter 1. "/ Etc / profile

  • Residential and special places in front of the anti-follow system 2011-06-08

    In recent days there has been followed in many parts of China to open the door to enter the high incidence of robberies, murders people, some of the requirements of the customer access control system to continuously improve existing products of a sin

  • (R) centos 5 system optimization services 2010-12-27

    Transfer: E7% B3% BB% E7% BB% 9F% E6% 9C% 8D% E5% 8A% A1% E4% BC% 98% E5% 8C% 96.htm centos minimal installation, the default on many servers, many of which run the server is useless, usually close the

  • windows system optimization and speed 2011-03-21

    <! - Google_ad_client = "pub-2416224910262877"; google_ad_width = 728; google_ad_height = 90; google_ad_format = "728x90_as"; google_ad_channel = ""; google_color_border = "E1771E"; google_color_bg = "FFFFFF

  • Oracle.Db2.SqlServer.MySQL database into the current system time 2011-08-17

    For instance, table table, table has two fields: name, makedate Into the system time should be sysdate: insert into table (name, makedate) values ​​('test', sysdate); 2.Db2: Into the system time should be current timestamp and timestamp dat

  • A large number of concurrent users to access the linux system optimization under 2010-04-01

    Concurrent access in a time when large numbers of users may result in tomcat's too many open file exceptions, this part should be optimized to handle tomcat, where I will not be specifically described. I want to produce in time may result in concurre

  • Services for the Windows 2003 operating system optimization program! 2010-08-29

    Following this program is for Windows 2003 and installed IIS server, Symantec family of software and services Serv-U system settings program, if you did not install all three software-related services would not exist. Just a few points need to explai

  • SQL related system optimization 2010-10-17

    Department had a meeting today, mainly on the current server performance for a discussion of optimization, most recently a query server, subscriber growth problems encountered, mainly in the database, the growth rate is actually very gentle, but the

  • Mysql as a Key / Value Storage optimization 2010-10-25

    This morning saw the article "Using MySQL as a NoSQL - A story for exceeding 750,000 qps on a commodity server". Major is about to Mysql as a KVS and Memcache a contrast. See a new way of thinking. Mysql is probably the result of the beginning o