sql: schema changes can be very slow #47790
Assignees
Labels
A-schema-changes
C-enhancement
Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
Comments
Offline discussion with @spaskob to sidestep 2. above by instead having the
|
spaskob
added a commit
to spaskob/cockroach
that referenced
this issue
May 8, 2020
Touches cockroachdb#47790. Release note (performance improvement): Before this a simple schema change could take 30s+. The reason was that if the schema change is not first in line in the table mutation queue it would return a re-triable error and the jobs framework will re-adopt and run it later. The problem is that the job adoption loop is 30s. To repro run this for some time: ``` cockroach sql --insecure --watch 1s -e 'drop table if exists users cascade; create table users (id uuid not null, name varchar(255) not null, email varchar(255) not null, password varchar(255) not null, remember_token varchar(100) null, created_at timestamp(0) without time zone null, updated_at timestamp(0) without time zone null, deleted_at timestamp(0) without time zone null); alter table users add primary key (id); alter table users add constraint users_email_unique unique (email);' ``` Instead of returning on retriable errors we retry with a exponential backoff in the schema change code. This pattern of dealing with retriable errors in client job code is encouraged vs relying on the registry beacuse the latter leads to slowness and additionally to more complicated test fixtures that rely in hacking with the internals of the job registry,
spaskob
added a commit
to spaskob/cockroach
that referenced
this issue
May 8, 2020
Touches cockroachdb#47790. Release note (performance improvement): Before this a simple schema change could take 30s+. The reason was that if the schema change is not first in line in the table mutation queue it would return a re-triable error and the jobs framework will re-adopt and run it later. The problem is that the job adoption loop is 30s. To repro run this for some time: ``` cockroach sql --insecure --watch 1s -e 'drop table if exists users cascade; create table users (id uuid not null, name varchar(255) not null, email varchar(255) not null, password varchar(255) not null, remember_token varchar(100) null, created_at timestamp(0) without time zone null, updated_at timestamp(0) without time zone null, deleted_at timestamp(0) without time zone null); alter table users add primary key (id); alter table users add constraint users_email_unique unique (email);' ``` Instead of returning on retriable errors we retry with a exponential backoff in the schema change code. This pattern of dealing with retriable errors in client job code is encouraged vs relying on the registry beacuse the latter leads to slowness and additionally to more complicated test fixtures that rely in hacking with the internals of the job registry,
|
This is a simple way to reproduce the slowness: |
|
It turns there's a short term fix that seems to work pretty well. The reason for the slowness is that if the schema change is not first in line in the table mutation queue it would return a re-triable error and the jobs framework will re-adopt and run it later. The problem is that the job adoption loop |
|
I will still work on the job improvements to prevent future regressions and simplify the jobs framework. This PR #48600 is an example of what is coming next. |
craig bot
pushed a commit
that referenced
this issue
May 9, 2020
48045: sql: enable indexing and ordering on arrays of orderable and indexable types r=rohany a=rohany Fixes #17154. Fixes #35707. This PR enables arrays to be ordered and indexed by introducing an ordered key encoding for arrays. Once this exists, the rest of the SQL infrastructure is ready to handle indexing and ordering on arrays. To encode an array of elements `ARRAY[a, b]`, we create the following encoding. Let `AM` = a marker byte for arrays and let `AT` be a terminator byte. `enc(ARRAY[a, b]) = [AM, enc(a), enc(b), AT]` The key is that the terminator is less than the element marker. This allows for the "prefix matching" style comparison that arrays support. Release note (sql change): This PR adds support for indexing and ordering of arrays of indexable and orderable inner types. 48608: schemachange: speed up slow schema changes r=spaskob a=spaskob Touches #45150. Fixes #47607. Touches #47790. Release note (performance improvement): Before this a simple schema change could take 30s+. The reason was that if the schema change is not first in line in the table mutation queue it would return a re-triable error and the jobs framework will re-adopt and run it later. The problem is that the job adoption loop is 30s. To repro run this for some time: ``` cockroach sql --insecure --watch 1s -e 'drop table if exists users cascade; create table users (id uuid not null, name varchar(255) not null, email varchar(255) not null, password varchar(255) not null, remember_token varchar(100) null, created_at timestamp(0) without time zone null, updated_at timestamp(0) without time zone null, deleted_at timestamp(0) without time zone null); alter table users add primary key (id); alter table users add constraint users_email_unique unique (email);' ``` Instead of returning on re-triable errors we retry with exponential backoff in the schema change code. This pattern of dealing with re-triable errors in client job code is encouraged vs relying on the registry because the latter leads to slowness and additionally to more complicated test fixtures that rely on hacking with the internals of the job registry, Co-authored-by: Rohan Yadav <[email protected]> Co-authored-by: Spas Bojanov <[email protected]>
spaskob
added a commit
to spaskob/cockroach
that referenced
this issue
May 9, 2020
Touches cockroachdb#47790. Release note (performance improvement): Before this a simple schema change could take 30s+. The reason was that if the schema change is not first in line in the table mutation queue it would return a re-triable error and the jobs framework will re-adopt and run it later. The problem is that the job adoption loop is 30s. To repro run this for some time: ``` cockroach sql --insecure --watch 1s -e 'drop table if exists users cascade; create table users (id uuid not null, name varchar(255) not null, email varchar(255) not null, password varchar(255) not null, remember_token varchar(100) null, created_at timestamp(0) without time zone null, updated_at timestamp(0) without time zone null, deleted_at timestamp(0) without time zone null); alter table users add primary key (id); alter table users add constraint users_email_unique unique (email);' ``` Instead of returning on retriable errors we retry with a exponential backoff in the schema change code. This pattern of dealing with retriable errors in client job code is encouraged vs relying on the registry beacuse the latter leads to slowness and additionally to more complicated test fixtures that rely in hacking with the internals of the job registry,
|
Points 2 and 3 were subsumed by #48621. Points 4 and 5 are less high priority. |
|
Hi, schema change has improve speed, but there still an issue CPU usage is still extremely high (like 90%-100% of an 4 cores machine) when schema change |
How much data are in the tables and how many schema changes are you running? If you’d be willing to provide any sort of scripts to reproduce or cpu profiles, that would be very helpful |
spaskob
added
C-enhancement
|
We are also experiencing extremely high CPU usage (>90%) as well as bandwidth usage in our cockroach cloud 3-node production cluster (ID 36691cbd-9927-438f-83af-cdc3c06a2b20). Is this regression truly resolved? |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Describe the problem
We've had a number of reports of schema changes being slow lately (##38111, #47607, #47512). This is a meta-issue for a problem which is theorized here and to track reproduction and evaluation. It also includes proposed steps.
Theory
We’ve exposed the job adoption cycle to some schema changes even if they’d happen super fast.
Alternative Consideration
We scan the entire jobs table in the job adoption loop, as this table gets big, that's probably slow.
Proposals
1. Understand the exact implications with regards to user-visible latency for schema changes
We should write some tests which are hella slow and fix that
2. Prioritize the jobs we adopt in the adoption loop
We really want to adopt just about anything over top of GC Jobs
We really don't want to adopt jobs that have unmet mutation dependencies
We could create a simple ranking mechanism where we prioritize
I'm not exactly sold on the type prioritization but at least it's easy.
For the others, we could inject a function per type that controls its ranking.
It doesn't need to be very tightly coupled.
3. Start jobs immediately after finishing a job.
Right now we wait for the adoption loop before picking up another job. If you have a number of queued mutations, it may take a long time for the subsequent jobs to get picked up.
Really easy
4. Stop creating jobs that just wait.
Right now we create jobs that make no sense as jobs.
There is no value if another node adopts the job except for coordinating the waiting.
These excess jobs probably exacerbate the above problems.
5. Create an index on the status column of jobs.
Junk piles up and then the query gets very slow.
Adding an index should be an easy migration.
Environment:
The text was updated successfully, but these errors were encountered: