Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

First, problem phenomenon

There was a code update in the test environment today. After the upgrade, someone in the group reported that the system access was slow. O&m personnel report that the server CPU usage is high.

Not long after the o&M restart, someone reported slow access to the system. At this time, the O&M personnel said that a large number of Full GC was generated.

Second, global monitoring

TOP first and find that the single CPU % US is always at 100%

When executing TOP, it is customary to press 1 to see the usage of each CPU. If you do not press 1, you will see the average of all cpus.

Through global monitoring, it is found that the Java process with PID 7313 consumes more CPU.

Directional monitoring

1. Analyze GC

Real-time viewing of GC status:

jstat -gcutil 7313 1000
Copy the code

As you can see from the figure above, the JVM has been FGC frequently. New generation memory is full. Old age memory full! What’s going on? Do you create large objects and never recycle them?

Moving from single-CPU high to looking at the JVM’s GC takes into account the single-CPU high situation that would result for serial FGC.

Expanding knowledge:

  • Common JDK command-line tools for performance monitoring

2. Analyze application logs

SQL > query SQL > query SQL > query SQL

Because there is stack information in the log, related to SQL, and the heap is full as seen in Jstat, the next step is to look at heapdump.

Heap dump analysis

Prints the heap dump for the related process. Start to use MAT tool for analysis.

Command:

jmap-dump:format=b,file=test.hprof 7313
Copy the code

ExecuteThread: ‘0’ for queue: ‘weblogic.kernel.Default(self-tuning)’ takes up 1.4GB of memory

Take a look at Threadstack for suspicious problem points:

The focus here is to look at the lines associated with the business code to find the call point.

As you can see from the figure above, executing the program code PreparedSQLQuery causes problems. This is SQL code, can you locate the specific SQL statement?

As you can see from the figure above, one Thread consumed 1.5 GIGABytes of content. Expand to see the SQL in progress.

From the property values on the left side of the figure above, you can see the SQL currently being executed.

4. Application analysis

Combined with the background logs indicating a large number of SQL problems, you can determine that this SQL is the key to the problem.Now that you have the specific SQL, go to the corresponding library to have a look!

Take the SQL and execute it in the corresponding database.

SQL query data volume is 537755 rows, the total amount of data in table 1 is 810093, the total amount of data in Table 2 is 537755 rows.

But based on the actual business rules, this SQL should query for a record. What’s going on here?

Take a look at the structure of the two tables (only columns and indexes involved in SQL are listed here) :

Table 1:

Table 2:

Let’s look at the original SQL execution plan:Explain the knowledge related to the execution plan:Combined with the table structure and the execution plan, the first table has a unique index scan and the second table has a quick index scan, which seems to be fine, but the cost and rows are high and are mostly from the second table.

After analyzing the original SQL, we found that the associated table lacked the association condition.

Fourth, application optimization

Optimize the SQL, the query is indeed a record, the execution time is about 180ms.

In terms of the execution plan, the cost value is greatly reduced by adding the association condition.

After communicating with the developer, they did adjust the SQL in the test environment, and the test environment returned to normal. Understand that the corresponding SQL statement is correct in the production environment.

Five, the summary

After this example, we should realize the importance of performance analysis thinking, basic knowledge is very important, practice is also very important. Only through continuous accumulation, continuous practice, in order to transform the theoretical system into their own ability system.