Article Title: five simple methods for optimizing LAMP (below ). Linux is a technology channel of the IT lab in China. Including desktop applications, Linux system management, kernel research, embedded systems, open source, and other basic categories to optimize your database
Database optimization will soon become a cutting-edge topic, and I have almost no space to do this fairly here. However, if you are looking to optimize the speed of your database, you should first take some steps, which should be helpful for common problems.
Store the database on your own machine
Database queries can become very intense. Generally, a simple SELECT statement is executed on a dataset of a reasonable size, and the time limit is set to 100% of the CPU. If your web server and database server are using the CPU time on a single machine, this will undoubtedly slow down your request speed. Therefore, I think the first step is to put the web server and database server on a separate machine to ensure that your database server is more robust (the database server prefers a large amount of memory and multiple CPUs ).
Properly design and compile tabulation Indexes
The biggest problem with database performance may be caused by poor database design and missing indexes. SELECT statements are usually the most common query types running in typical web applications. They are also the most time-consuming queries run on the database server. In addition, these types of SQL statements are the most sensitive to appropriate indexing and database design, so check the following instructions to get the best performance tips.
· Ensure that each table has a primary key. This provides a default order and quick way for tables to join other tables.
· Make sure that the index of any foreign key in one table (that is, the key of the record linked to another table) is properly compiled. Many databases automatically impose constraints on these keys so that the values actually match one record in another table, which helps to get rid of this difficulty.
· Try to limit the number of columns in a table. It takes longer to scan too many columns in a table than to query only some columns. In addition, if you have a table that contains multiple columns that is not commonly used, you are also using the NULL Value Field to waste disk space. This is also true for variable size fields such as text or blob, where the increase in table size can far exceed the requirement. In this case, you should consider dividing other columns into different tables and associating them with the primary key of the record.
Analyze queries running on the server
The best way to improve database performance is to analyze what queries are running on your database server and how long it takes to run them. Almost every database has a tool with this function. For MySQL, you can use slow query logs to find problematic queries. To use it, set slow_query_log to 1 in the MySQL configuration FILE, set log_output to FILE, and record them to the FILE hostname-slow.log. You can set the long_query_time threshold to determine how many seconds the query must run before it is considered as a "Slow query ". I would like to recommend that you set this threshold to 5 seconds first and reduce it to 1 second over time, depending on your dataset. If you explore this file, you will see a detailed query similar to listing 1.
[1] [2] Next page
