Yesterday's blog shows you how to find bad code, how to gracefully implement an in-app monitoring program.
Post Address: Backend system performance Optimization (first quarter 2 find bad code)
Of course, after discovering the bad code, we still have to find a way to get rid of it, maybe it will be very stubborn. Today talk about a very important part of performance optimization: SQL optimization
Today is not about writing good, well-performing SQL, these DBAs will be more professional than me. In our company, the SQL,DBA that DBAs can optimize are digested internally and need to be fed back to us, stating that they may be at their wits ' end. It's time for us to get shot.
Insert,update This type of SQL, performance is generally not too slow, I put this one of the problems may arise in one example, composed of a scene: Lock a record, insert 10W Pojo record after the status of the update lock record is completed, using the Oracle database, Each record is required to get a sequence to be inserted as a primary key value, and each record also needs to get a sequence to produce no, not the same as the ID, as in the following code:
+ View Code?
| 1 2 3 |
pojoorder order = Pojoorderdao.lock (Pojoorderid); <br> for (Pojo p:pojolist) { long sequce = Pojodao.gensequce (); &NBSP;&NBSP;&NBSP;&NBSP;&NBSP; p.setid (sequce);<br> long nosequce = Pojodao.gennosequce ();<br> P.setno (nosequce);<br> PojoDao.save (p); <br>}<br>order.setstatus (Status.finish); <br>pojoorderdao.save (order); |
10W Data Loop single insertion is very slow at peak times, while pessimistic locks in the same transaction lock a record for too long, lock waits, database connections are not released, database loads soar, and even the database goes down.
The simple lines above may not be a problem in the business implementation, but from a performance point of view, it is a few lines of bad code, we have made some modifications,
first , no longer go to the database to take the sequence, write the sequence value in the INSERT statement (in this case do not use hibernate save, using native SQL). Similar to insert into Pojo (id,no) VALUES (seq_pojo.nextval,seq_pojo_no.nextval).
second , the single commit is modified to be submitted in bulk every 1000 articles.
again , the three-step operation to lock the pojoorder modified state locks the record--Batch insert --and update to completed as a three-transaction four -step Lock -and-update-COMMIT TRANSACTION BULK INSERT (an exception that updates the status to unhandled) commits a transaction into the completion---updated to the completed commit transaction, which is controlled by three transactions. This approach may be as good as the previous code to ensure atomicity, we sacrifice this consistency to improve performance, reduce risk, as a large number of concurrent Web applications is also very worthwhile. And over the past few years, no such strong consistency has caused the failure, the probability of data inconsistency is very very low.
After the above processing, the performance of the insert has been greatly improved, there is no more frequent lock waiting.
Insert and update on the problem, we encounter less, the most annoying thing is some complex query, in order to meet the requirements of the business, have to write some query statements to obtain the basic data, this time poor performance of SQL optimization we need to according to different circumstances to different optimization scheme, A few more common scenarios are listed below.
1, the association changed to a single table. Database tables may often have some dictionary tables to describe the meaning of a value, and sometimes there are several dictionary tables in a business table, and queries need to be associated with the properties of the dictionary table rather than the dictionary ID. Such as:
?
| 1 |
select m.* from m m inner join c C on m.c_id = c.id inner join b B on m.b_id = b.id where C.CW =? and b.px=? and m.status =? and m.create_time > sysdate-10 |
B,c Two tables are small table, the amount of data is not large, C.CW and c.id one-to-one relationship, B.PX and b.id a one-to-one relationship. The Association of such queries is actually completely unnecessary. Can be changed directly into
?
| 1 |
select m.* from M m where m.c_id = ? and m.b_id = ? and m.status = ? andm.create_time = sysdate -10; |
Where the values of C.CW and C.id and the b.id and B.PX values are mapped, a simple use of the cache can be done. is b,c two tables of data are put into the cache, need to be directly from the cache to take.
2, execute too frequent query SQL to reduce the frequency of execution. This scenario everyone should be able to think of itself to deal with this scenario is very simple, use cache can, cache believe many students will use, but to use good cache is also very fastidious, cache expiration strategy is need to be cautious, now I mostly use the expiration policy has active expiration ( Delete cache operation when data is updated) and timed out (set a cache time that expires automatically after the time). Caching is good indeed can greatly reduce the pressure of the database, but the use of bad, it is likely to cause the problem of data dirty reading.
Caching is a good way to mitigate execution frequency, But what happens when you have to meet very frequent read-real-time data on some key business? The following is a very simple SQL
?
| 1 |
select * from A a wherea.id = ? |
This SQL itself is not much of a performance problem, and execution is very fast, but the DBA is very serious about telling us that it has to reduce its frequency of execution. We monitored this SQL and found that there were 20K calls per minute at peak times, and the frequency of calls was very high. After examining the code, many of these simple queries are found in loops. To batch queries, increase the cache where you can increase the cache , and the SQL execution frequency is reduced to 2k-5k times per minute.
DBAs also provide us with a way to reduce the number of parsing of a single SQL by increasing hint. Here is a more detailed introduction: http://www.dbafree.net/?p=778
?
| 1 2 3) 4 5 |
select /**1*/ * from A a where a.id = ? select /**2*/ * from A a where a.id = ? select /**3*/ * from A a where a.id = ? select /**4*/ * from A a where a.id = ? select /**5*/ * from A a where a.id = ? |
3, the partition table increases the partition key value condition. This is a good understanding, our Oracle data tables are partitioned tables, after the addition of partition key values, Oracle data Lookup can quickly locate the area, to avoid the full table scan. such as: Select A.id,a.account from a a where a.register_time > sysdate-10; If the partition key value of the a table is create_time. If the SQL does not increase the create_time condition, all the extents will be scanned to find data for register_time > sysdate-10 conditions, and if the condition of the partition value is increased, The corresponding partition will be scanned according to the partition key value, and the other partitions will not be scanned.
4. Remove the select of unwanted fields. In our actual application, for various reasons, query a record directly on the SELECT * or all the fields are listed after the query returned. This kind of writing can improve the efficiency of coding to a certain extent, improve the reusability, but in the performance, we do not advocate this kind of writing, in order to find out the two field values of 30 fields, OK, you are too green. We have a table with a field value that holds a large string, and then one day we find that the data is very slow when we look at the table, the DBA does not have much problem checking the index, and then we find that the value of storing this very large string is also detected, and because of the network transmission and so on, the return result is very slow. Most abhorrent of all, the business does not use this large string. We then removed this field and some unwanted fields from the select list, and we were happy.
5, the deadlock, is the system performance of the enemy, of course, to completely avoid it. Before the business volume was not so high, our system has been a deadlock, the specific business scenario is:
Record A in table A is first locked by the method m, pessimistic lock get B table of record B when the B record is locked by another thread's N method, n method to obtain the lock of record a in table A, the a record is locked by the method M, the two threads wait for the lock to release each other. In this scenario, the lock is not released.
?
| 1 2 3 4 5 6 |
M方法线程 select * from A where id = 1 for update // 这条记录锁定的时候,N方法的 B表记录也被锁定 select * from B where aid = 1 for update //等待获取B表记录的锁 N方法线程 select * from B where aid = 1 for update //锁定 select * from A where id = 1 for update //等待M方法线程释放锁 |
There are a lot of deadlock scenarios (two different threads can also cause deadlocks), the solution principle of each scenario is the same, do not need to lock the removal of the lock, it is unavoidable, the order of adjustment lock is consistent or changed to pessimistic lock + retry mechanism. As the above scenario can be changed to the same lock order (note: According to their business to see if they are suitable for themselves)
?
| 1 2 3 4 5 6 |
M方法线程 select * from B where aid = 1 for update select * from A where id = 1 for update N方法线程 select * from B where aid = 1 for update select * from A where id = 1 for update |
There is also a deadlock in the same method for two different threads, usually in the loop update operation, if the method is called concurrently, and the order of the elements in the collection is not the same.
?
| 1 2 3 4 |
for(Pojo p:pojoList){ pojoDao.update(p); }//想象一下如果第一条线程更新的时候是a记录在第一条,n记录在最后一条,而另外一条线程同时更新的时候n在第一条而a记录在最后一条。 |
Of course, many times callers may tell you that there is no possibility of concurrent calls, but from the point of view of the server, we need to avoid the possibility of deadlocks altogether . In this case, the elements in the collection are sorted according to a rule before updating.
In short, in peacetime work, we will encounter a variety of SQL-induced performance problems, SQL optimization space is very small, the means of optimization is relatively fixed. After the optimization of the effect is not the same, some of the effect is good, most of the effect is general, if some SQL is not optimized space and every day by the DBA Chase, perhaps consider a different business processing scheme can be used to avoid this SQL.
If we can think more about performance when we need to do something, we can use the database without using it. In fact, many developers think more fixed, do not make full use of the cache, memory awareness and concept, that everything must exist in the database, everything from the database is the reliable. Perhaps we can also use other methods to achieve the same reliability as the database.
Next section, talk about the third of System performance optimization: Call chain optimization