drop table and add/drop column still slow despite effort in #71800

[kocoten1992]

Is your feature request related to a problem? Please describe.

Hi cockroachdb,

I saw effort in #71800, but despite that running migration in real project still obnoxious slow.

It's mention in the old issue running schema change in cockroachdb vs postgres is about 10 times slower (15 mins vs 2.5 hours).

I'd like to share real number down to milliseconds of database migration in v22.2.2 and discussion which is the actually slowness operation (please open this issue in two tabs for easier comparing number below):

Postgres:

  Dropping all tables ........................................................................ 31ms DONE

   INFO  Preparing database.  

  Creating migration table ................................................................... 18ms DONE

   INFO  Running migrations.  

  2014_10_12_000000_create_a_table ....................................................... 34ms DONE
  2014_10_12_100000_create_b_table ............................................. 22ms DONE
  2014_10_12_200000_add_c_to_a_table ..................................... 4ms DONE
  2017_07_10_071627_create_d_table .................................................... 25ms DONE
  2017_07_10_085906_create_e_table ........................................................ 20ms DONE
  2017_07_14_172047_create_f_table ................................................... 40ms DONE
  2017_07_15_160131_create_g_table .................................................... 46ms DONE
  2017_07_16_123302_create_h_table ....................................................... 21ms DONE
  2017_07_17_064200_create_i_table ........................................................ 38ms DONE
  2017_07_22_113454_create_j_table ............................................... 63ms DONE
  2017_07_22_113519_create_k_table ............................................... 62ms DONE
  2017_07_23_150953_create_l_table ....................................................... 12ms DONE
  2018_01_06_184028_create_m_table .................................................... 25ms DONE
  2018_08_08_100000_create_n_table ........................................... 68ms DONE
  2019_06_22_094113_add_o_to_f_table ........................................... 4ms DONE
  2019_09_23_174547_add_p_to_q_table ............................................ 4ms DONE
  2020_03_01_143743_create_r_tables ................................................. 64ms DONE
  2020_03_01_170110_add_s_to_a_table .............................................. 7ms DONE
  2020_03_06_191438_add_t_to_d_table .............................................. 4ms DONE
  2020_06_10_200527_add_u_to_f_table .............................................. 3ms DONE
  2020_06_10_204017_drop_u_from_f_table ........................................... 3ms DONE
  2020_06_17_161039_add_v_to_h_table ................................... 3ms DONE
  2020_08_12_034727_add_w_to_f_table ........................... 3ms DONE
  2020_08_22_000241_add_x_to_h_table ............................................ 4ms DONE
  2020_08_22_000344_add_y_to_h_table .......................................... 2ms DONE
  2020_08_30_014354_add_z_to_f_table .............................................. 3ms DONE
  2020_12_30_021015_add_aa_to_j_table ..................................... 3ms DONE
  2020_12_30_021024_add_ab_to_k_table ..................................... 3ms DONE
  2021_03_11_090308_add_ac_to_ad_table ..................................................... 4ms DONE
  2021_04_07_153124_add_ae_af_h_table .................................................... 3ms DONE
  2021_05_20_002514_add_af_to_f_table ............................... 4ms DONE
  2021_05_24_003745_add_ag_to_f_table ................................... 2ms DONE
  2021_12_24_083008_add_ah_to_h_table ................................................ 2ms DONE
  2022_08_17_011924_create_ai_table .............................................. 17ms DONE
  2022_08_17_231125_add_aj_to_d_table .................................. 1ms DONE
  2022_09_03_225742_add_ak_to_a_table ............................................ 1ms DONE
  2022_12_12_231842_add_al_to_l_table .................................................. 2ms DONE
  2022_12_13_013011_add_am_to_e_table ................................................... 2ms DONE
  2022_12_15_040551_add_an_to_h_table ................................................ 3ms DONE
  2022_12_19_201853_add_ao_to_h_table ........................................ 2ms DONE
  2022_12_27_052745_add_ap_to_h_table .................................................. 2ms DONE
  2023_01_07_214931_add_aq_to_f_table ........................................... 3ms DONE

CockroachDB:

  Dropping all tables ..................................................................... 1,321ms DONE

   INFO  Preparing database.

  Creating migration table ................................................................... 25ms DONE

   INFO  Running migrations.

  2014_10_12_000000_create_a_table ....................................................... 37ms DONE
  2014_10_12_100000_create_b_table ............................................. 30ms DONE
  2014_10_12_200000_add_c_to_a_table ..................................... 165ms DONE
  2017_07_10_071627_create_d_table .................................................... 37ms DONE
  2017_07_10_085906_create_e_table ........................................................ 161ms DONE
  2017_07_14_172047_create_f_table ................................................... 56ms DONE
  2017_07_15_160131_create_g_table .................................................... 125ms DONE
  2017_07_16_123302_create_h_table ....................................................... 112ms DONE
  2017_07_17_064200_create_i_table ........................................................ 120ms DONE
  2017_07_22_113454_create_j_table ............................................... 175ms DONE
  2017_07_22_113519_create_k_table ............................................... 183ms DONE
  2017_07_23_150953_create_l_table ....................................................... 99ms DONE
  2018_01_06_184028_create_m_table .................................................... 24ms DONE
  2018_08_08_100000_create_n_table ........................................... 76ms DONE
  2019_06_22_094113_add_o_to_f_table ........................................... 216ms DONE
  2019_09_23_174547_add_p_to_q_table ............................................ 173ms DONE
  2020_03_01_143743_create_r_tables ................................................. 136ms DONE
  2020_03_01_170110_add_s_to_a_table .............................................. 416ms DONE
  2020_03_06_191438_add_t_to_d_table .............................................. 152ms DONE
  2020_06_10_200527_add_u_to_f_table .............................................. 179ms DONE
  2020_06_10_204017_drop_u_from_f_table ........................................... 209ms DONE
  2020_06_17_161039_add_v_to_h_table ................................... 154ms DONE
  2020_08_12_034727_add_w_to_f_table ........................... 188ms DONE
  2020_08_22_000241_add_x_to_h_table ............................................ 245ms DONE
  2020_08_22_000344_add_y_to_h_table .......................................... 202ms DONE
  2020_08_30_014354_add_z_to_f_table .............................................. 187ms DONE
  2020_12_30_021015_add_aa_to_j_table ..................................... 145ms DONE
  2020_12_30_021024_add_ab_to_k_table ..................................... 144ms DONE
  2021_03_11_090308_add_ac_to_ad_table ..................................................... 289ms DONE
  2021_04_07_153124_add_ae_af_h_table .................................................... 339ms DONE
  2021_05_20_002514_add_af_to_f_table ............................... 191ms DONE
  2021_05_24_003745_add_ag_to_f_table ................................... 193ms DONE
  2021_12_24_083008_add_ah_to_h_table ................................................ 186ms DONE
  2022_08_17_011924_create_ai_table .............................................. 132ms DONE
  2022_08_17_231125_add_aj_to_d_table .................................. 138ms DONE
  2022_09_03_225742_add_ak_to_a_table ............................................ 177ms DONE
  2022_12_12_231842_add_al_to_l_table .................................................. 178ms DONE
  2022_12_13_013011_add_am_to_e_table ................................................... 181ms DONE
  2022_12_15_040551_add_an_to_h_table ................................................ 267ms DONE
  2022_12_19_201853_add_ao_to_h_table ........................................ 207ms DONE
  2022_12_27_052745_add_ap_to_h_table .................................................. 208ms DONE
  2023_01_07_214931_add_aq_to_f_table ........................................... 199ms DONE

Combine all above, postgres finished in 9.451s while cockroachdb finished in 1.165s.

As you can see above, create table operation is blazingly fast (still slower than postgres - but it fast).

However, drop table and add/drop column could be 40 to 100 times slower compare to postgres.

In old issue, there a mention of tweaking cluster setting (but I'm not doing that here), the reason is: when someone new try out cockroachdb, if test suit from postgres run 10 times slower on cockroachdb could create a false image database being slow and they stop consider cockroachdb instead of tweaking it.

Describe the solution you'd like
If we could improve on drop table, add/drop column time, it might greatly improve cockroachdb adoption among frameworks

Side note: in local env, there is no add/drop index operations, I could not provide data on that (it might or might not be another source of slowness).

Jira issue: CRDB-24568

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-schema (found keywords: drop table,schema change)
• @otan (author of sql: SQL schema changes are too slow for ORM tests to adopt #71800)
• @rafiss (commented on sql: SQL schema changes are too slow for ORM tests to adopt #71800)
• @ajwerner (commented on sql: SQL schema changes are too slow for ORM tests to adopt #71800)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[ajwerner]

We'll look into whether there are any performance regressions on our side. Some of this is likely due to the online nature of schema changes in cockroach. Do you notice this being slower than 22.1?

[kocoten1992]

I remember it was getting better overtime (may be due to #71800 and many optimization before that) and do not recall it get slower (actually I don't just "remember" :), test suite run show that migration indeed run faster overtime).

[ajwerner]

How do you run your test suite? On what type of machines? With what settings? Have you tried disabling fsync?

[kocoten1992]

Hello, thank you having interest in this,

I'll explain in detail (I believe most people already have this problem - they just didn't report it yet):

How do you run your test suite?

1. To guarantee code must run on production, I've to run test with actual database touching (mocking database is unfortunately will not be good enough).
2. In order to test with high precision, I must also control data in database, usually the recommended way to do this is: drop table if exists, create table, insert data, run test, then drop table (drop/create database - instead of table, usually a hassle because of user config/permission on database will be loss - so I don't think a lot of people will adopt this approach, I believe most framework do not).
3. However as you can see above, drop table is currently a very slow operation (1,321ms vs 31ms), enter my first hack, I've to rewrite drop table in framework to instead delete all records in all tables, but hack are bad (framework do not support it).
4. The second thing is: application evolve over time (maybe add a column to a table and drop a column no longer needed), but that knowledge is something I would love to keep around forever (like git), framework usually called migration, you can read about it more here https://articles.wesionary.team/integrating-migration-tool-in-gin-framework-golang-139676bc4af7 (I realize in golang, there is not a culture about migration hence I need to explain clearly about this).

As you can see, add/remove column is also a hassle because it slow - currently on my test, the majority of running test is the time running add/remove column. Of course if the number of change is too big, postgres would struggle too, some framework support migration squash (https://laravel.com/docs/10.x/migrations#squashing-migrations) (https://docs.djangoproject.com/en/4.1/topics/migrations/#migration-squashing). Up until of this writing, I've not using any migration squash (I've only a couple of column).

What type of machines?

The machine running is pretty old (but comparison still fair because postgres run on the same machine).

CPU: AMD Athlon(tm) X4 860K Quad Core Processor
memory speed: sysbench memory --memory-block-size=1M --memory-total-size=100G --num-threads=1 run

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Running memory speed test with the following options:
  block size: 1024KiB
  total size: 102400MiB
  operation: write
  scope: global

Initializing worker threads...

Threads started!

Total operations: 102400 (13799.86 per second)

102400.00 MiB transferred (13799.86 MiB/sec)


General statistics:
    total time:                          7.4170s
    total number of events:              102400

Latency (ms):
         min:                                    0.07
         avg:                                    0.07
         max:                                    0.40
         95th percentile:                        0.09
         sum:                                 7384.40

Threads fairness:
    events (avg/stddev):           102400.0000/0.00
    execution time (avg/stddev):   7.3844/0.00

With what settings? Have you tried disabling fsync?

Linux server (ubuntu) with default setting (cockroachdb default setting too), no I've not disabling fsync.

[ajwerner]

Have you seen https://www.cockroachlabs.com/docs/stable/local-testing.html? I think those settings may help dramatically.

[kocoten1992]

Thank you! It does help, from 9s to average 4.{5..9}s now.

But only the change --store=type=mem dramatically reduce it, setting cluster does not seem to do much..

Interestingly, I try the same technique on postgres (create tmpfs filesystem on memory and let postgres run on it), the result DOES NOT CHANGE for postgres - I guess they already optimize to fit on memory, just how scary postgres optimazation is 🤮

I guess we could close this then 😃

[kocoten1992]

My appologize, I was using this script https://blog.vergiss-blackjack.de/2011/02/run-postgresql-in-a-ram-disk/, I was lazy and not read through the whole script.

Redo the experiment with postgres it does make a different from 1.2s to consistently 0.5s, just don't want to spread wrong information.

Sorry and good day.