Web site Optimization: Database optimization

Intermediary transaction http://www.aliyun.com/zixun/aggregation/6858.html ">seo diagnose Taobao guest cloud host technology Hall

The current web2.0 program, a big bottleneck is the database swallowing metric. However, how to determine the bottleneck of the system is a database, because only to determine the database is the bottleneck of the entire system, we have to optimize him, after all, there are so many needs to wait for us to do.

How do I determine that a database is a bottleneck?

1 If the programming is good, there is a database operating logic layer, you can see from this level of statistics on each request to spend time, if the average time has not allowed you to tolerate, the database is already a bottleneck.

2 Use the top command on the database server to see if the MySQL server is using the resources, to see the average load of the machine.

If the average server load is already high, MySQL occupies a block of 100% CPU resources, the MySQL server is very busy.

3 on the database server using the Iostat command, look at disk Io, if the block live more operations, indicating that the database operation is too frequent, the disk response is not urgent.

4 It is recommended to open MySQL's slow query log, so grep Select to look at the number of slow queries in the log, if the number of more, indicating that the number of slow queries, need to be adjusted.

5 If one day the database can not be inserted, you need to check if the database table is too large. The maximum capacity of the 32-bit operating system on the previous table is 2^32 so large. However, it is recommended to add a database operation of the logical layer, in the database operation before and after the operation of the time, statistical reporting, the use of monitoring procedures to alarm the relevant 618.html "> responsible person, so that you can know the database is the bottleneck, in advance to make optimization.

Know that the database is the bottleneck, how to optimize it?

1 Our first thought is to see whether the database capacity is too large, if the database table is too large, the index file will be relatively large, each update operation will be more time-consuming. You need to consider the library and the table.

The partition table according to certain rules to store the records in the database, on the one hand can achieve a certain load balance, the request is divided down, each sector to bear alone; On the other hand, a partitioned table allows us to store and manipulate more data.

However, the need for more than a separate library based on library procedures to modify, there are certain risks, therefore, at the beginning of the program design should take into account the needs of the sub-Library, preferably the database operating layer independent, easy to expand and change.

2 If the database table is not very large, but the query is slow, we need to check our SQL query statements, using the MySQL explain statement to see if the index is used, if we do not use the index, then we need to build on the corresponding fields index, repeated use of explain, Find a suitable index.

You need to consider when building an index:

1 the less the index of the database, the better.

Because the index is updated every time an update is required, too much indexing can slow down writing.

2 The narrowest field is placed on the left side of the key.

This improves the cardinality of each point in the index and leads to better index read and write performance.

3 Avoid file sort sorting, temporary tables, and table scans as much as possible.

For large tables, a full table scan results in a large number of disk IO operations, which can result in very slow operations.

4 for large tables, as far as possible not to build the index on the String type column, string matching is very time-consuming, need to pay a high performance costs, if necessary, it is recommended to the string column hash after taking an integer value to index.

3 If the update operation is a bit slow, and read the response requirements do not need to be very timely, you can consider using MySQL master-slave hot standby to share the pressure of reading and writing.

After all, the operation of the database, write less Read more. So, we put the write operations on the database on the main MySQL server, using MySQL's hot standby, we read on the backup database server, because we can have more than one hot standby mysql, so we can distribute the read operation on several hot standby, thus the reading operation is balanced and the performance of read operation is improved.

4 Use of caching

Caching is the root of all background programs, because 80% of the request is corresponding to 20% of the data, we only need a small amount of memory to 20% of the data cache, you can greatly meet our system needs, why not?

1 MySQL set up as much as possible to add key Cache,thread cache, query cache.

2 Add a generic cache such as memcached to the application layer.

3 for a small amount of data, but the table with frequent operation using the memory heap table provided by MySQL, can obtain extremely high write and read speed.

5 Database Design Optimization

For the traditional database design, we pay attention to modeling paradigm, to avoid data redundancy resulting in dirty data. However, in our practical application, we need to use some rules of the third paradigm according to the situation, for some data that frequently need to appear in multiple places, like a forum for users and topics and replies, and so on, if we store the user separately from the topic and the reply, Every time you look at an article or a response, you need to check the user table and the topic table or Reply form. If the amount of data is small, so the performance of the joint check is acceptable, if the table larger, on the 3, more than 400,000 of the data, the speed of the joint investigation will be relatively slow.

The paradigm needs to be normalized, but it is still necessary to design our tables to achieve performance and good design compromises.

Other words:

1 for the operation of the database recommended layered processing, at least two layers, one layer is the logical layer of database operations, one layer is the cache layer of the database.

Consider this from the outset, you can easily in the future to partition the database deployment, the sub-table expansion of the library.

2 increase the MySQL monitor, monitor the MySQL slow query log, monitor the MySQL request.

3 Choose MySQL Storage engine according to your own needs.

MyISAM has a high read/write speed, but cannot be read and written at the same time because of table locking.

InnoDB supports transactions and provides row-level locks, but in order to use transactions, table space is larger and full-text indexing is not supported.

Heap puts the table in memory, suitable for situations where the table is small and needs to be frequently manipulated, such as user information, which reads and writes quickly, but is not durable, and needs to write tools to make it last.

4 MySQL server for some status detection commands.

Show slave Status: You can see the master-slave synchronization.

Show [Full] processlist: You can see the MySQL server request, if you find a lot of lock situation, you need to pay attention.

Show Status: You can see various requests for MySQL server.

My small station http://www.qin3.com