Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Performance optimization of GenUpdateSQL with multi updates #8057

Closed
2 tasks
amyangfei opened this issue Jan 11, 2023 · 3 comments
Closed
2 tasks

Performance optimization of GenUpdateSQL with multi updates #8057

amyangfei opened this issue Jan 11, 2023 · 3 comments
Assignees
@amyangfei
Labels
affects-6.5 area/ticdc Issues or PRs related to TiCDC. type/feature Issues about a new feature

Comments

@amyangfei
Copy link
Contributor

amyangfei commented Jan 11, 2023 •
edited
Loading

Is your feature request related to a problem?

In a large update transaction scenario, we found high cpu usage with github.com/pingcap/tiflow/pkg/sqlmodel.GenUpdateSQL (24.45%, 33.79s)

image

cpu profile: cdc.profile.zip

Describe the feature you'd like

Break down sub tasks

  • Add more unit test cases to cover gen multi update scenario
  • Add a GenUpdateSQLFast function to replace GenUpdateSQL, which remove the ast.Restore procedure, and generate SQL with template rendering.

Describe alternatives you've considered

No response

Teachability, Documentation, Adoption, Migration Strategy

No response
@amyangfei amyangfei added area/ticdc Issues or PRs related to TiCDC. type/feature Issues about a new feature labels Jan 11, 2023
@amyangfei amyangfei mentioned this issue Jan 11, 2023
Merged
@amyangfei amyangfei self-assigned this Jan 11, 2023
@lance6716
Copy link
Contributor

lance6716 commented Jan 11, 2023 •
edited
Loading

there's also other ways to avoid CPU consumption, like

  • don't need to generate for columns that are not changed
  • cache the SQL per table, and invalidate it after DDL
  • split into DELETE + INSERT
  • use GenInsertSQL with DMLInsertOnDuplicateUpdate when PK/UK is not updated (as in the comment of GenUpdateSQL)

This was referenced Jan 12, 2023
Merged
Closed
ti-chi-bot pushed a commit that referenced this issue Jan 18, 2023
@amyangfei
pkg(ticdc): add a faster raw string rendering version of GenUpdateSQL (…
7dddcd0 
…#8069)

ref #8057
This was referenced Feb 28, 2023
Merged
Closed
ti-chi-bot pushed a commit that referenced this issue Mar 15, 2023
@amyangfei
sink(ticdc): support multi statements in mysql backend (#8395)
66ffedd 
ref #8057
@amyangfei amyangfei mentioned this issue Mar 16, 2023
Merged
@ti-chi-bot ti-chi-bot mentioned this issue Mar 21, 2023
Merged
amyangfei added a commit to amyangfei/tiflow that referenced this issue Apr 3, 2023
@amyangfei
pkg(ticdc): add a faster raw string rendering version of GenUpdateSQL (…
a22f4b7 
…pingcap#8069)

ref pingcap#8057
@amyangfei amyangfei mentioned this issue Apr 3, 2023
Merged
@nongfushanquan
Copy link
Contributor

/label affects-6.5

@amyangfei amyangfei mentioned this issue Apr 4, 2023
Closed
ti-chi-bot pushed a commit that referenced this issue Apr 4, 2023
@amyangfei
cherry pick multi statement sql feature (#8349) (#8395) (#8554) (#8627)…
1090f91 
… (#8731)

ref #8057
@nongfushanquan nongfushanquan mentioned this issue Apr 18, 2023
Merged
15 tasks
@amyangfei
Copy link
Contributor Author

This issue has been resolved.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@amyangfei amyangfei

Labels
affects-6.5 area/ticdc Issues or PRs related to TiCDC. type/feature Issues about a new feature
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@amyangfei @lance6716 @ti-chi-bot @nongfushanquan