I Optimized the Interface From 20 Seconds to 500 Milliseconds | by Dwen | Jul, 2022

Interface performance optimization

Photo by UX Store on Unsplash

The interface performance problem is an inescapable topic for students engaged in back-end development. To optimize the performance of an interface, you need to start from multiple aspects.

This article will talk about how I optimize a slow query interface from a practical point of view on the topic of interface performance optimization.

Last week, I optimized the online batch scoring query interface, and optimized the interface performance from the initial 20s to the current 500ms.

In general, it is done in three steps.

Before going to work every morning, we will receive an online slow query interface summary email, which will display the interface address, number of calls, maximum time, average time, traceId, and other information.

I saw that there is a batch scoring query interface, the maximum time is the 20s, and the average time is 2s.

Using skywalking to view the call information of the interface, it is found that in most cases, the response of the interface is relatively fast. In most cases, it can be returned in about 500ms, but there are also a few requests that exceed 20s.

This phenomenon is very strange.

Is it about the data?

For example, it is very fast to check the data of a certain organization. However, if you want to query the platform, that is, the root node of the organization, in this case, the amount of data to be queried is very large, and the interface response may be very slow.

But it turned out not to be the reason.

But soon a colleague gave the answer.

They requested this interface in batches on the statement list page, but the amount of data that he passed parameters was very large.

What’s going on?

The requirement originally mentioned is that this interface is called for the paging list page. The size of each page is 10, 20, 30, 50, or 100, and the user can choose.

In other words, calling the batch evaluation query interface can query up to 100 records at a time.

But the reality is: that the statement list page also contains many orders. Basically, for each settlement, there are multiple orders. When calling the batch evaluation query interface, you need to combine the data of the settlement document and the order.

The result is that when calling the batch evaluation query interface, a lot of parameters are passed in at one time, and the input parameter list may contain hundreds or even thousands of pieces of data.

If hundreds or thousands of IDs are passed in at one time, it is good to query data in batches, and you can use the primary key index, and the query efficiency will not be too bad.

But that batch scoring query interface, the logic is not simple.

In fact, in the real scene, the code is much more complicated than this. Here, it is simplified for everyone to demonstrate.

There are two most important points:

  • Another interface is called remotely the interface.
  • The data needs to be queried in a for a loop.

The first point is that another interface is called remotely in the interface, and this code is necessary.

Because if an organization code field is redundant in the evaluation table, in case the organization code in the organization table is modified, you have to use some mechanism to notify us to synchronously modify the organization code of the evaluation table, otherwise, it will appear. Data inconsistency problem.

Obviously, if you want to adjust in this way, the business process has to be changed, and the code changes are a bit big.

So, let’s keep calling it remotely in the interface first.

In this way, the only places that can be optimized are query data in the for a loop.

Due to the need in the for loop, each record needs to query the desired data according to different conditions.

Since the business system does not pass id when calling this interface, it is not easy to use id in (...) in the where condition. This method is used to query data in batches.

In fact, there is a way to solve the requirements without looping the query, using the or keyword splicing, for example:

(org_code='001' and category_id=123 and business_id=111 and business_type=1) or (org_code='002' and category_id=123 and business_id=112 and business_type=2) or (org_code='003' and category_id=124 and business_id=117 and business_type=1)...

In this way, the SQL statement will be very long and the performance will be poor.

There is another way of writing:

where (a,b) in ((1,2),(1,3)...)

However, in this kind of SQL, if the amount of data queried at one time is too large, the performance is not very good.

Since it cannot be changed to batch query, the execution efficiency of single query SQL can only be optimized.

Start with the index first, because the retrofit cost is the lowest. So our first optimization is also index optimization.

An ordinary index of the business_id field is established before the evaluation table, but the efficiency is not ideal at present.

Since I decisively added a joint index:

alter table user_score add index  `un_org_category_business` (`org_code`,`category_id`,`business_id`,`business_type`) USING BTREE;

The joint index consists of four fields: org_code, category_id, business_id, business_type.

After this optimization, the effect is immediate.

The maximum time-consuming of the batch evaluation query interface has been shortened from the initial 20s to about 5s.

Due to the need in the for loop, each record needs to query the desired data according to different conditions.

Querying data in only one thread is obviously too slow. So, why can’t it be changed to a multi-threaded call?

Therefore, in the second optimization, the query database was changed from single thread to multi-thread.

However, since the interface is to return all the queried data, it is necessary to obtain the query results.

Using multi-threaded calls, and to get the return value, this scenario is very suitable to use CompleteFuture in java8.

The code is adjusted to:

The essence of CompleteFuture is to create thread execution. In order to avoid generating too many threads, it is necessary to use a thread pool.

It is recommended to use the ThreadPoolExecutor class first, and we customize the thread pool.

Thread pools can also be created using the ThreadPoolTaskExecutor class:

After this optimization, the interface performance has also been improved by 5 times, from about 5s to about 1s.

But the overall effect is not ideal.

After the previous two optimizations, the performance of the batch query evaluation interface has been improved, but the time consumption is still more than 1s.

The root cause of this problem is that too much data is queried at one time.

So, why don’t we limit the number of records per query?

Therefore, the third optimization is to limit the number of records for the one-time queries. In fact, it has also been limited before, but the maximum is 2000 records, and the effect is not good from the current point of view.

The interface is limited to checking only 200 records at a time. If more than 200 records are exceeded, an error message will be reported.

If this interface is directly restricted, it may cause an exception to the business system.

In order to avoid this situation, it is necessary to discuss the optimization plan with the business system team.

There are mainly two options.

On the statement list page, only one order is displayed for each statement by default, and redundant paging queries are made.

In this case, if the maximum number of records per page is 100, the settlement statement and order can only query up to 200 records at a time.

This requires the front-end of the business system to do paging, and the back-end interface needs to be adjusted to support paging queries.

But the current situation is that there are no redundant development resources on the front end.

Due to the shortage of manpower, this plan can only be put on hold for now.

The backend of the business system used to call the evaluation query interface at one time, but now it is called in batches.

For example, when 500 records were queried before, the business system only called the query interface once.

Now it is changed to the business system that only checks 100 records at a time, and is called in 5 batches, and a total of 500 records are queried.

Doesn’t that make it slower?

Answer: If the five batches of operations that call the evaluation query interface are in a single-threaded sequence in a for loop, the overall time-consuming process may of course be slower.

However, the business system can also be changed to multi-threaded calls, as long as the results are finally aggregated.

At this point, some people may ask the question: Is the multi-threaded call of the server evaluating the query interface the same as the multi-threaded call in other business systems?

Why not increase the maximum number of threads in the thread pool in the server that evaluates the query interface in batches?

Obviously, you’re missing one thing: online applications are generally not deployed as a single point. In most cases, in order to avoid a single point of failure because the server is down, at least 2 nodes are basically deployed. In this way, even if a node hangs, the entire application can be accessed normally.

In other words, through the multi-threaded call interface in the business system, the traffic load of the access interface can be balanced for different nodes.

They also use 8 threads to divide the data into batches of 100 records each and finally aggregate the results.

After this optimization, the interface performance doubled again.

From about 1s, shortened to less than 500ms.

A warm reminder, whether it is to query the database in the batch query evaluation interface, or to call the batch query evaluation interface in the business system, using multi-threaded calls is only a temporary solution and not perfect.

The reason for this is mainly to solve the problem quickly first, as this solution change is minimal.

To fundamentally solve the problem, this set of functions needs to be redesigned, the table structure needs to be modified, and maybe even the business process needs to be modified. However, because multiple business lines and multiple business systems are involved, it can only be done slowly.

Thank you for reading this article.

Stay tuned for more.

News Credit

%d bloggers like this: