Hibernate Multi-Table Association Causes CPU Spike

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: hibernate多表关联导致CPU飙升

| username: hughzm

[TiDB Usage Environment] Production Environment
[TiDB Version] V4.0.16
[Reproduction Path] Multi-table join SQL execution

SELECT this_.ID AS ID1_879_8_, this_.AT_EMPLOYEE_IDS AS AT2_879_8_, this_.CC_EMPLOYEE_IDS AS CC3_879_8_, this_.CREATE_DATE AS CREATE4_879_8_, this_.CREATE_NAME AS CREATE5_879_8_
	, this_.CURRENT_DETAIL_ID AS CURRENT6_879_8_, this_.CURRENT_EX_STEP AS CURRENT7_879_8_, this_.CURRENT_INDEX AS CURRENT8_879_8_, this_.EFFECT_TIME AS EFFECT9_879_8_, this_.EMPLOYEEID AS EMPLOYE10_879_8_
	, this_.END_DATE AS END11_879_8_, this_.ENTITY_ID AS ENTITY12_879_8_, this_.EX_CC_EMPLOYEE_IDS AS EX13_879_8_, this_.EXPECT_CURRENT_EMPLOYEE_ID AS EXPECT14_879_8_, this_.FIRST_DETAIL_ID AS FIRST15_879_8_
	, this_.JOB_RECORD_ID AS JOB16_879_8_, this_.MEMO AS MEMO17_879_8_, this_.NAME AS NAME18_879_8_, this_.NEXT_EMPLOYEE_IDS AS NEXT19_879_8_, this_.NEXT_EX_STEP AS NEXT20_879_8_
	, this_.NEXT_INDEX AS NEXT21_879_8_, this_.NEXT_RELATION AS NEXT22_879_8_, this_.PROCESS_EMPLOYEE_IDS AS PROCESS23_879_8_, this_.RECORD_SEQ AS RECORD24_879_8_, this_.STATUS AS STATUS25_879_8_
	, this_.UPDATE_DATE AS UPDATE26_879_8_, this_.UPDATE_NAME AS UPDATE27_879_8_, this_.WORKFLOW_ID AS WORKFLO29_879_8_, this_.WORKFLOW_EX_RELATIONS AS WORKFLO28_879_8_, erpemploye3_.ID AS ID1_209_0_
	, erpemploye3_.BANK_CARD AS BANK5_209_0_, erpemploye3_.BANK_CARD_ADDR AS BANK6_209_0_, erpemploye3_.BANK_CARD_OWNER AS BANK7_209_0_, erpemploye3_.BANK_NAME AS BANK8_209_0_, erpemploye3_.BIRTH AS BIRTH9_209_0_
	, erpemploye3_.BLACKLIST_STATUS AS BLACKLI10_209_0_, erpemploye3_.COLOR_TEST_RESULT AS COLOR11_209_0_, erpemploye3_.COLOR_TEST_RESULT_DATE AS COLOR12_209_0_, erpemploye3_.CRT_DATE AS CRT13_209_0_, erpemploye3_.DEGREE AS DEGREE14_209_0_
	, erpemploye3_.DELAY_REASON AS DELAY15_209_0_, erpemploye3_.DEPART_REGION AS DEPART16_209_0_, erpemploye3_.DEPOSIT_BANK AS DEPOSIT17_209_0_, erpemploye3_.EDUCATION AS EDUCATI18_209_0_, erpemploye3_.EMAIL AS EMAIL19_209_0_
	, erpemploye3_.EMPLOYEE_ID AS EMPLOYEE2_209_0_, erpemploye3_.EMPLOYEE_TYPE AS EMPLOYE20_209_0_, erpemploye3_.ENTRY_TYPE AS ENTRY21_209_0_, erpemploye3_.GRADUATE_SCHOOL AS GRADUAT22_209_0_, erpemploye3_.GRADUATE_TIME AS GRADUAT23_209_0_
	, erpemploye3_.HAS_EXTRA_SUBSIDY AS HAS24_209_0_, erpemploye3_.HAS_RELATIVES_IN_WORK AS HAS25_209_0_, erpemploye3_.HEALTH_CERT_BEGIN AS HEALTH26_209_0_, erpemploye3_.HEALTH_CERT_END AS HEALTH27_209_0_, erpemploye3_.HEALTH_CERT_FEE AS HEALTH28_209_0_
	, erpemploye3_.HEALTH_CERT_NO AS HEALTH29_209_0_, erpemploye3_.HEIGHT AS HEIGHT30_209_0_, erpemploye3_.HOBBY AS HOBBY31_209_0_, erpemploye3_.HOME_ADDR AS HOME32_209_0_, erpemploye3_.ID_ADDR AS ID33_209_0_
	, erpemploye3_.ID_AUTH_ORG AS ID34_209_0_, erpemploye3_.ID_CAT AS ID35_209_0_, erpemploye3_.ID_NUM AS ID36_209_0_, erpemploye3_.ID_VALID_BEGIN AS ID37_209_0_, erpemploye3_.ID_VALID_END AS ID38_209_0_
	, erpemploye3_.INTRODUCER_DPT_ID AS INTRODU39_209_0_, erpemploye3_.INTRODUCER_DPT_NAME AS INTRODU40_209_0_, erpemploye3_.INTRODUCER_ID AS INTRODU41_209_0_, erpemploye3_.INTRODUCER_NAME AS INTRODU42_209_0_, erpemploye3_.IS_PROBATION AS IS43_209_0_
	, erpemploye3_.LABOR_CONTRACT_BEGIN AS LABOR44_209_0_, erpemploye3_.LABOR_CONTRACT_END AS LABOR45_209_0_, erpemploye3_.LAST_ENTRY_DATE AS LAST46_209_0_, erpemploye3_.LAST_JOB_REC_ID AS LAST47_209_0_, erpemploye3_.LAST_LEAVE_DATE AS LAST48_209_0_
	, erpemploye3_.LAST_WORK_REC_ID AS LAST49_209_0_, erpemploye3_.LEGAL_PERSON_TYPE AS LEGAL50_209_0_, erpemploye3_.LOGICAL_TEST_RESULT AS LOGICAL51_209_0_, erpemploye3_.LOGICAL_TEST_RESULT_DATE AS LOGICAL52_209_0_, erpemploye3_.MARRY_STATUS AS MARRY53_209_0_
	, erpemploye3_.MOST_IMPRESSIVE_THING AS MOST54_209_0_, erpemploye3_.NAME AS NAME3_209_0_, erpemploye3_.NATIONALITY AS NATIONA55_209_0_, erpemploye3_.NATIVE_PLACE AS NATIVE56_209_0_, erpemploye3_.NO_PAYROLL AS NO57_209_0_
	, erpemploye3_.ONDUTY_TAG AS ONDUTY58_209_0_, erpemploye3_.PERFORMANCE_CYCLE AS PERFORM59_209_0_, erpemploye3_.PHONE AS PHONE60_209_0_, erpemploye3_.POLITICAL_STATUS AS POLITIC61_209_0_, erpemploye3_.PROBATION_END_DATE AS PROBATI62_209_0_
	, erpemploye3_.PROBATION_FLAG AS PROBATI63_209_0_, erpemploye3_.REL_PEOPLE_NAME AS REL64_209_0_, erpemploye3_.REL_PEOPLE_PHONE AS REL65_209_0_, erpemploye3_.REL_PEOPLE_TYPE AS REL66_209_0_, erpemploye3_.RELATIVES_EMPLOYEE_ID AS RELATIV67_209_0_
	, erpemploye3_.RELATIVES_PHONE_NUM AS RELATIV68_209_0_, erpemploye3_.RELATIVES_RELATION AS RELATIV69_209_0_, erpemploye3_.REPORT_TO AS REPORT70_209_0_, erpemploye3_.REST_PER_MONTH AS REST71_209_0_, erpemploye3_.SENIOR_MODE AS SENIOR72_209_0_
	, erpemploye3_.SERVING_DURATION AS SERVING73_209_0_, erpemploye3_.SEX AS SEX74_209_0_, erpemploye3_.SORT AS SORT75_209_0_, erpemploye3_.SPECIALTY AS SPECIAL76_209_0_, erpemploye3_.STATUS AS STATUS4_209_0_
	, erpemploye3_.UPDATE_DATE AS UPDATE77_209_0_, erpemploye3_.WORK_TIMES AS WORK78_209_0_, erpjobreco4_.ID AS ID1_368_1_, erpjobreco4_.ACCUMULATION_FUND AS ACCUMULA2_368_1_, erpjobreco4_.ACTION_TYPE AS ACTION3_368_1_
	, erpjobreco4_.BEGIN_TIME AS BEGIN4_368_1_, erpjobreco4_.BIG_MEDICAL AS BIG5_368_1_, erpjobreco4_.CITY AS CITY6_368_1_, erpjobreco4_.CITY_TYPE AS CITY7_368_1_, erpjobreco4_.CONTRACT_BEGIN_DATE AS CONTRACT8_368_1_
	, erpjobreco4_.CONTRACT_END_DATE AS CONTRACT9_368_1_, erpjobreco4_.CREATE_BY AS CREATE10_368_1_, erpjobreco4_.CREATE_DATE AS CREATE11_368_1_, erpjobreco4_.CREATE_NAME AS CREATE12_368_1_, erpjobreco4_.DEL_FLAG AS DEL13_368_1_
	, erpjobreco4_.DPT_ID AS DPT15_368_1_, erpjobreco4_.DIRECT_SUPERIOR_EVALUATION AS DIRECT14_368_1_, erpjobreco4_.DUTY_BONUS AS DUTY16_368_1_, erpjobreco4_.DUTY_SUBSIDY AS DUTY17_368_1_, erpjobreco4_.EDITABLE AS EDITABL18_368_1_
	, erpjobreco4_.EMPLOYEE_ID AS EMPLOYE19_368_1_, erpjobreco4_.EMPLOYMENT_SUBJECT AS EMPLOYM20_368_1_, erpjobreco4_.END_TIME AS END21_368_1_, erpjobreco4_.ENTRY_FLAG AS ENTRY22_368_1_, erpjobreco4_.EXTRA_BIG_MEDICAL AS EXTRA23_368_1_
	, erpjobreco4_.EXTRA_CHILD_EDUCATION AS EXTRA24_368_1_, erpjobreco4_.EXTRA_CONTINUING_EDUCATION AS EXTRA25_368_1_, erpjobreco4_.EXTRA_HOUSING_LOAN AS EXTRA26_368_1_, erpjobreco4_.EXTRA_HOUSING_RENT AS EXTRA27_368_1_, erpjobreco4_.EXTRA_SUPPORT_ELDERLY AS EXTRA28_368_1_
	, erpjobreco4_.FIXED_OVERTIME_SALARY AS FIXED29_368_1_, erpjobreco4_.JOB_ID AS JOB30_368_1_, erpjobreco4_.LEAVE_DESCRIPTION AS LEAVE31_368_1_, erpjobreco4_.LEAVE_FLAG AS LEAVE32_368_1_, erpjobreco4_.LEAVE_PARENT_TYPE AS LEAVE33_368_1_
	, erpjobreco4_.LEAVE_REASON AS LEAVE34_368_1_, erpjobreco4_.LEAVE_TYPE AS LEAVE35_368_1_, erpjobreco4_.LEVEL AS LEVEL36_368_1_, erpjobreco4_.MEDICAL AS MEDICAL37_368_1_, erpjobreco4_.PENSION AS PENSION38_368_1_
	, erpjobreco4_.PERFORM_SUBSIDY1 AS PERFORM39_368_1_, erpjobreco4_.PERFORM_SUBSIDY2 AS PERFORM40_368_1_, erpjobreco4_.REASON AS REASON41_368_1_, erpjobreco4_.SALARY AS SALARY42_368_1_, erpjobreco4_.SALARY_GRADE AS SALARY43_368_1_
	, erpjobreco4_.SALARY_TYPE AS SALARY44_368_1_, erpjobreco4_.SEQUENCE_ID AS SEQUENC45_368_1_, erpjobreco4_.SOCIAL_SECURITY_SUBSIDY1_RADIX AS SOCIAL46_368_1_, erpjobreco4_.SOCIAL_SECURITY_SUBSIDY2_RADIX AS SOCIAL47_368_1_, erpjobreco4_.SOURCE_FLAG AS SOURCE48_368_1_
	, erpjobreco4_.TOTAL_SOCIAL_SECURITY AS TOTAL49_368_1_, erpjobreco4_.UNEMPLOYMENT AS UNEMPLO50_368_1_, erpjobreco4_.UPDATE_BY AS UPDATE51_368_1_, erpjobreco4_.UPDATE_DATE AS UPDATE52_368_1_, erpjobreco4_.UPDATE_NAME AS UPDATE53_368_1_
	, erpjobreco5_.ID AS ID1_368_2_, erpjobreco5_.ACCUMULATION_FUND AS ACCUMULA2_368_2_, erpjobreco5_.ACTION_TYPE AS ACTION3_368_2_, erpjobreco5_.BEGIN_TIME AS BEGIN4_368_2_, erpjobreco5_.BIG_MEDICAL AS BIG5_368_2_
	, erpjobreco5_.CITY AS CITY6_368_2_, erpjobreco5_.CITY_TYPE AS CITY7_368_2_, erpjobreco5_.CONTRACT_BEGIN_DATE AS CONTRACT8_368_2_, erpjobreco5_.CONTRACT_END_DATE AS CONTRACT9_368_2_, erpjobreco5_.CREATE_BY AS CREATE10_368_2_
	, erpjobreco5_.CREATE_DATE AS CREATE11_368_2_, erpjobreco5_.CREATE_NAME AS CREATE12_368_2_, erpjobreco5_.DEL_FLAG AS DEL13_368_2_, erpjobreco5_.DPT_ID AS DPT15_368_2_, erpjobreco5_.DIRECT_SUPERIOR_EVALUATION AS DIRECT14_368_2_
	, erpjobreco5_.DUTY_BONUS AS DUTY16_368_2_, erpjobreco5_.DUTY_SUBSIDY AS DUTY17_368_2_, erpjobreco5_.EDITABLE AS EDITABL18_368_2_, erpjobreco5_.EMPLOYEE_ID AS EMPLOYE19_368_2_, erpjobreco5_.EMPLOYMENT_SUBJECT AS EMPLOYM20_368_2_
	, erpjobreco5_.END_TIME AS END21_368_2_, erpjobreco5_.ENTRY_FLAG AS ENTRY22_368_2_, erpjobreco5_.EXTRA_BIG_MEDICAL AS EXTRA23_368_2_, erpjobreco5_.EXTRA_CHILD_EDUCATION AS EXTRA24_368_2_, erpjobreco5_.EXTRA_CONTINUING_EDUCATION AS EXTRA25_368_2_
	, erpjobreco5_.EXTRA_HOUSING_LOAN AS EXTRA26_368_2_, erpjobreco5_.EXTRA_HOUSING_RENT AS EXTRA27_368_2_, erpjobreco5_.EXTRA_SUPPORT_ELDERLY AS EXTRA28_368_2_, erpjobreco5_.FIXED_OVERTIME_SALARY AS FIXED29_368_2_, erpjobreco5_.JOB_ID AS JOB30_368_2_
	, erpjobreco5_.LEAVE_DESCRIPTION AS LEAVE31_368_2_, erpjobreco5_.LEAVE_FLAG AS LEAVE32_368_2_, erpjobreco5_.LEAVE_PARENT_TYPE AS LEAVE33_368_2_, erpjobreco5_.LEAVE_REASON AS LEAVE34_368_2_, erpjobreco5_.LEAVE_TYPE AS LEAVE35_368_2_
	, erpjobreco5_.LEVEL AS LEVEL36_368_2_, erpjobreco5_.MEDICAL AS MEDICAL37_368_2_, erpjobreco5_.PENSION AS PENSION38_368_2_, erpjobreco5_.PERFORM_SUBSIDY1 AS PERFORM39_368_2_, erpjobreco5_.PERFORM_SUBSIDY2 AS PERFORM40_368_2_
	, erpjobreco5_.REASON AS REASON41_368_2_, erpjobreco5_.SALARY AS SALARY42_368_2_, erpjobreco5_.SALARY_GRADE AS SALARY43_368_2_, erpjobreco5_.SALARY_TYPE AS SALARY44_368_2_, erpjobreco5_.SEQUENCE_ID AS SEQUENC45_368_2_
	, erpjobreco5_.SOCIAL_SECURITY_SUBSIDY1_RADIX AS SOCIAL46_368_2_, erpjobreco5_.SOCIAL_SECURITY_SUBSIDY2_RADIX AS SOCIAL47_368_2_, erpjobreco5_.SOURCE_FLAG AS SOURCE48_368_2_, erpjobreco5_.TOTAL_SOCIAL_SECURITY AS TOTAL49_368_2_, erpjobreco5_.UNEMPLOYMENT AS UNEMPLO50_368_2_
	, erpjobreco5_.UPDATE_BY AS UPDATE51_368_2_, erpjobreco5_.UPDATE_DATE AS UPDATE52_368_2_, erpjobreco5_.UPDATE_NAME AS UPDATE53_368_2_, erpemploye6_.ID AS ID1_209_3_, erpemploye6_.BANK_CARD AS BANK5_209_3_
	, erpemploye6_.BANK_CARD_ADDR AS BANK6_209_3_, erpemploye6_.BANK_CARD_OWNER AS BANK7_209_3_, erpemploye6_.BANK_NAME AS BANK8_209_3_, erpemploye6_.BIRTH AS BIRTH9_209_3_, erpemploye6_.BLACKLIST_STATUS AS BLACKLI10_209_3_
	, erpemploye6_.COLOR_TEST_RESULT AS COLOR11_209_3_, erpemploye6_.COLOR_TEST_RESULT_DATE AS COLOR12_209_3_, erpemploye6_.CRT_DATE AS CRT13_209_3_, erpemploye6_.DEGREE AS DEGREE14_209_3_, erpemploye6_.DELAY_REASON AS DELAY15_209_3_
	, erpemploye6_.DEPART_REGION AS DEPART16_209_3_, erpemploye6_.DEPOSIT_BANK AS DEPOSIT17_209_3_, erpemploye6_.EDUCATION AS EDUCATI18
| username: hughzm | Original post link

The data volume of each single table is not large, only in the hundreds of thousands. However, the overall execution time is around 19 seconds. I want to know:
Is the spike in cluster server CPU really caused by this kind of SQL, and how to identify it?

| username: Kongdom | Original post link

You can check the top SQL in the dashboard to see if the one using the most resources is this SQL.

| username: 有猫万事足 | Original post link

ORM is destined to be a failed design. The performance of the generated SQL is uncontrollable, and optimizing the database requires developers to be familiar with both the ORM middleware and the corresponding database features, which greatly increases maintenance costs.

I’m curious why something that history has proven should be thrown into the trash has become popular again recently.

I suspect that the developers who were burned by ORM in the past are now 35+ and have been optimized out. So history begins a new cycle. :joy:

As for your question, I think you should just look at the execution plan, and you should have your answer.

A bunch of outer joins and then a

WHERE 
	workflow1_.MATCH_KEYWORD <>  ?

is suffocating just to look at.

There is a certain probability that this SQL is not the real reason for the CPU spike, but at the very least, this SQL needs to be optimized or you need to delve into why it is written this way.

| username: TiDBer_H5NdJb5Q | Original post link

It’s probably legacy code, given such an old DB version and framework. The 4.x version doesn’t have the top SQL feature, so you can’t use the top SQL mentioned above. Check the slow log to see which queries are slow. The statement itself doesn’t seem to have any issues. Check the execution plan, and if the table isn’t too large, create indexes.

| username: DBAER | Original post link

Check the execution plan, or look at the logs and search for the keyword “expensive”.

| username: hughzm | Original post link

Execution Plan: TiFlash was built later, previously it was using TiKV, basically no impact.

Result_8.html (22.8 KB)

| username: zhaokede | Original post link

Looking at the execution plan to optimize SQL, theoretically, 19 seconds is not considered slow for a large SQL query. Is this executed very frequently?

| username: 不想干活 | Original post link

It’s best to upgrade the version.

| username: hughzm | Original post link

It can no longer be considered the last generation. The Hibernate version is still 4, and with the cascading references in JSP, it’s impossible to finish modifying it, and no one dares to touch it.

| username: hughzm | Original post link

Hello, please help me check if I found the right location. The rpc_time here took so long, is it corresponding to the associated table _this? Then why is the rpc_time for this table so prominent?

| username: hughzm | Original post link

In planning…

| username: 林夕一指 | Original post link

Monitoring shows that machines 100 and 101 are high, so these two are probably TiDB nodes. Your SQL query cannot be pushed down to TiKV at all. The general execution logic is that TiKV scans the entire table and submits the data to the TiDB nodes, which then perform a hash-join algorithm and sort to retrieve the data. Unless you change the code, modify the table structure to implement field redundancy, and vertically scale the TiDB nodes.

The issue with TiFlash is likely due to an outdated version, as the MPP feature was only introduced after version 5. Upgrade the version. :joy:

| username: zhanggame1 | Original post link

This SQL will definitely be slow. The WHERE condition is too sparse. Try to add more WHERE filter conditions to push the operators down to TiKV. Otherwise, with hundreds of thousands of rows in a table, a large amount of data will be read into TiDB for join and sorting, which will be extremely slow.

| username: 小于同学 | Original post link

Take a look at the execution plan.

| username: hughzm | Original post link

The image you provided is not visible. Please provide the text content for translation.

| username: hughzm | Original post link

Actually, I have already rewritten it using fewer associations + mandatory validation conditions to solve this problem. Moving forward, I will upgrade first and then systematically address such issues. Thank you.