jeetpatel9
commented
2 years ago @vi3k6i5 Could please help me understand if am I doing something wrong or its just spanner.
Open jeetpatel9 opened 2 years ago
jeetpatel9
commented
2 years ago @vi3k6i5 Could please help me understand if am I doing something wrong or its just spanner.
IlyaFaer
commented
2 years ago @jeetpatel9, looking at this line This method is non-operational in autocommit mode return self.connection.commit(), I suspect migration is executed in autocommit mode, so every DDL statement is done separately. It's not a good practise due to performance issues. We usually recommend to use batches.
As I see, using any transactions API will not work here, as Spanner DDLs are working outside transactions.
@vi3k6i5, looks like our backend needs SchemaEditor class override to set that migration should be done in non-autocommit mode. With this, all the DDL statements will be pumping up on the connection and executed in a batch on commit(). It should fix the problem.
vi3k6i5
commented
2 years ago Thanks @IlyaFaer : Will look into doing that, Thanks @jeetpatel9 from bring it to attention.
For now a solution that I can recommend is running the migration and the spanner instance on GCP. Running Django app on local and running spanner on GCP significantly adds to network delays causing issues.
Also try tuning CONN_MAX_AGE as per your requirement, which will reduce the number of db connection creations.
I will investigate Illya's suggestion more and get back with a solution.
vi3k6i5
commented
2 years ago @jeetpatel9 : can you try with these settings.
AUTOCOMMIT = False CONN_MAX_AGE = 60
I am able to run migrations with the polls Django app location : local (India) to Spanner (us-central1), total migration time 10 mins.
NOTE: For the right value for CONN_MAX_AGE do try with your application what value works best.
@IlyaFaer : I did not see any major gains in performance with AUTOCOMMIT=False or AUTOCOMMIT=True, Django runs a lot of migrations and they take time to complete on spanner.
jeetpatel9
commented
2 years ago @vi3k6i5 I tried with AUTOCOMMIT = False & CONN_MAX_AGE = 60. It worked and took around 7 hours to finish.
I also ran the migrations as a job in Kubernetes cluster which is in the same region as the spanner instance and it still took 7 hours and 15 mins to finish migrating 42 tables.
NOTE: I deleted all the previous migration files and created new one to reduce the number of queries.
The only difference is if AUTOCOMMIT is set to FALSE the migrations does not fail half way.
vi3k6i5
commented
2 years ago Would it be possible to share the table structure which is slowing the progress?
Also try a basic empty django app see if that is also slow for you, this generally wraps up in under 10 mins for me.
jeetpatel9
commented
2 years ago Generally each table is taking significant amount of time but the longest is when the indexes of the tables are being created.
I tried a basic Django app with 4 tables and no indexes at all. It took around 15 mins.
Also, in most cases I get this DeadlineExceeded issue and the migrations are rarely successful. The last time I ran the migrations, this error was thrown while applying below shown migration. Also it doesn't break every time at exactly this point, it throws this error usually after applying 10-14 migrations.
# Generated by Django 3.2.12 on 2022-02-26 14:58
from django.db import migrations, models
import django_spanner
class Migration(migrations.Migration):
initial = True
dependencies = [
]
operations = [
migrations.CreateModel(
name='ExpiredOrders',
fields=[
('id', models.BigAutoField(auto_created=True, default=django_spanner.gen_rand_int64, primary_key=True, serialize=False, verbose_name='ID')),
('created_at', models.DateTimeField(auto_now_add=True)),
('modified_at', models.DateTimeField(auto_now=True)),
('identifier', models.BigIntegerField()),
('is_older', models.BooleanField(default=False)),
('is_out_of_flu_range', models.BooleanField(default=False)),
],
options={
'abstract': False,
},
),
migrations.CreateModel(
name='Orders',
fields=[
('id', models.BigAutoField(auto_created=True, default=django_spanner.gen_rand_int64, primary_key=True, serialize=False, verbose_name='ID')),
('created_at', models.DateTimeField(auto_now_add=True)),
('modified_at', models.DateTimeField(auto_now=True)),
('units', models.IntegerField()),
('buy_price_per_unit', models.FloatField(blank=True, null=True)),
('sell_price_per_unit', models.FloatField(blank=True, null=True)),
('is_market_order', models.BooleanField(default=False)),
('is_crowd_funding', models.BooleanField(default=False)),
('cf_group', models.IntegerField(blank=True, null=True)),
('fluctuation_upper_limit', models.FloatField()),
('fluctuation_lower_limit', models.FloatField()),
('order_trade_fee', models.FloatField()),
('tax_percent', models.FloatField()),
('sold_units', models.IntegerField(default=0)),
('bought_units', models.IntegerField(default=0)),
],
),
migrations.CreateModel(
name='OrderStatus',
fields=[
('id', models.BigAutoField(auto_created=True, default=django_spanner.gen_rand_int64, primary_key=True, serialize=False, verbose_name='ID')),
('name', models.CharField(max_length=20)),
],
),
migrations.CreateModel(
name='OrderType',
fields=[
('id', models.BigAutoField(auto_created=True, default=django_spanner.gen_rand_int64, primary_key=True, serialize=False, verbose_name='ID')),
('name', models.CharField(max_length=20)),
],
),
]
crash-absolutelabs
commented
2 years ago I'm actually seeing the same issue on a brand new deployment. Sorry to bring this old-ish thread back to life
Hello everyone,
I am trying to set up my Django project with
Cloud Spanner. I did everything as told in the documentation like adding the app into installed apps list -INSTALLED_APPS = ['django_spanner',], database configuration insettings.pyafter creating spanner instance & database. Then created a service account with the appropriate permissions & at last set the environment variables with the service account key & project id as shown below.So far I am able to connect to the spanner instance when I spin up my project but when I run
python manage.py migrateit takes like 10-20 mins to create 3-4 tables in the database. I waited long, around 30 mins to see if it could finish but after creating few more table it failed.I don’t know if this makes any difference but the instance I created is in Mumbai region and I am 300 KM away running my application locally inside a docker container. Also the spanner instance is regional & has only 1 node. I think it is good enough to run the migrations which would create around 30 tables.
Here the complete traceback of what I am seeing -