[天极 网]
Careful inspection application index design: can join -Log-slow-queries [= file] to track the analysis application bottleneck in service startup parameters, the easiest way to track the service bottleneck is to view mysql service with mysql's Status Run statistics and show processList to see SQL that is running in the current service, if a SQL often appears in the Process List, one. It is possible to query very much, two. There is no index in the fields affecting the query, three. Returns the result of too many databases is being sorted; so make a script: such as running the following show processlist every 2 second; output the result to the file, see what is querying in eating CPU. Full text search: If the corresponding field does not have a full-text index, the full text search will be a very high consumption of the CPU, because the full text retrieval is the index of the general database, so the corresponding field record traversal. About the full-text index can refer to the introduction of Java-based full-text index engine Lucene. Record cache for front desk applications: For example, a database authentication is often used, if you need to update the user's last login time, it is best to put the user in a cache (set up 2 hours after setting), so if the user is Used to log in again in 2 hours, directly from the cache, avoiding too frequent database operations. The query-priority table should be as far as possible to add indexes in the field in the WHERE and ORDER BY sentence, the database update insertion priority application index is, the better. In summary: More than 1 million optimizations record more than 1 million can be recorded in any database single table, the key is to convert the application into the data upper limit of the database. That is to simplify complex demand into a compared solution. One Optimization The following example is an optimization for a forum application: use Webalizer instead of the original database statistics. Firstly, check the CPU of the mysql service by the TOP command, the memory occupation: 10m, indicating that the index cache of the database has been used, modify the startup parameters, adds -o key_buffer = 32m, a period of time, etc. The occupation does the upper limit. Finally, the cache has been increased to 64M, and the database cache is basically fully used. For a database application, the memory is much practical to the database than the Web service, because the Mysql query speed increases to speed up the WEB application to save the memory resources occupied by the CONVA. Use Show ProcessList; statistically appear SQL: SHOW ProcessList and log: * * * * * (/ home / mysql / bin / mysql -uuser -ppassword > / HOME / CHEDONG / MySQL_ProcessList.log) SHOW_PROCESSLIST.SQL is a sentence: show processlist;, such as possible to filter it from the log: grep where mysql_processList.log If there is a deadlock, be sure to re-examine the database design. For the general situation: The query speed is slow, add the index in the SQL WHERE word, and if it is sorted, add the fields in the ORDER BY sentence plus no indexes. For the% like query, consider the disabled and use full-text index acceleration.
