https://linen.dev logo
Join Slack
Powered by
Anyone else having problems with the MySQL and MSS...
# replication-troubleshooting
r
Anyone else having problems with the MySQL and MSSQL connectors after upgrading? I followed the migration guide (schema changes) and upgraded both to the latest version. But since the upgrade the connectors slowed down a lot. Currently the connector is already running for more than 2 days, before the upgrade they ran +- 8 hours for ingesting all the data.
✍️ 1
h
Hey could you share the logs of both the syncs here that will be very helpful
r
Sure, I can at least share a log from an old (faster) run, and share the current log once it's finished because it's still running. The weird thing is also that it has plenty of resources in K8S (high requested and limits), but it just uses a fraction of it.
I'm not sure if I can share the whole log here because it contains a lot of information about the database schema of one of our customers. Are there specific parts of the logs you are interested in? EDIT: I discussed with the customer and they agreed that I share the logs here.
Files are quite huge, it's 72GB ingestion (incremental is no option because CDC is not possible, and most tables don't have a good primary key / timestamp value)
before-upgrade.txtafter-upgrade.txt
u
@[DEPRECATED] Marcos Marx turned this thread into Zendesk ticket 2861 to ensure timely resolution!
u
Hi Ramon, we've seen a few users have these issues. Sometimes it has to do with doing a large upgrade - skipping over many versions of the connector. What were the previous versions of the connectors that were working for you? Sorry to hear you're running into this! Hope we can resolve it quickly.
u
Hi Ramon, we've seen a few users have these issues. Sometimes it has to do with doing a large upgrade - skipping over many versions of the connector. What were the previous versions of the connectors that were working for you? Sorry to hear you're running into this! Hope we can resolve it quickly.
r
For MSSQL we upgraded from 
0.4.17
to 
0.4.20
and for MySQL from 
0.6.8
to 
1.0.4
and they are both about 5 times slower since the upgrade. We followed both migration guides where we had to update 1 records due to the schema changes. Besides that we also did upgraded airbyte from 
0.40.2
to 
0.40.6
.
u
Thanks! So I see in the logs that throughput has dropped from 2K-3K rows per second to 1K rows per second in the MSSQL connector. This is definitely not normal, and 2K-3K rows per second is the norm. Let me ask my team for their input on the best way to debug this further and I'll get back to you! As a temporary workaround I would try either downgrading the connectors or allocating more memory to the conenctions: https://docs.airbyte.com/operator-guides/configuring-connector-resources/#configuring-connection-specific-requirements
r
Tomorrow I'll try if I can downgrade the connectors, I guess I'll have to change the queries in the upgrade guide a little bit to do the downgrade (due to schema change where a string field was changed to an object field). Considering allocating more memory to the connectors, I have the feeling it has plenty of memory already. We are running all worker pods atm with about 4GB and 1 cpu as limits, which seems to be never even close to being used if I look at the k8s workload statistics in GKE. See:
Copy code
Attempting to start pod = source-mssql-read-2005-0-sebsv for airbyte/source-mssql:0.4.20 with resources io.airbyte.config.ResourceRequirements@72d6d7c0[cpuRequest=0.25,cpuLimit=1,memoryRequest=2Gi,memoryLimit=4Gi
Or do you advice to allocate even more resources for this workload?
u
Here's a rundown of how Airbyte allocates memory, have you read this doc before? https://docs.airbyte.com/operator-guides/scaling-airbyte/#memory I will see what else can be done, but so far the consensus is to allocate more memory, unfortunately! In making the connectors more stable we have slowed them down, but I will check with the team if they have any other ideas! Please let me know if allocating more memory helps!
r
Ah check, I wasn't aware of that the JVM without any extra settings uses 1/4th of the available memory. I'll look into these env variables and see if it will improve the performance.
I think I actually already set these variables due to the resources limits/requests in the helm chart, for instance:
Copy code
jobs:
  resources:
    limits:
      cpu: 1
      memory: 4Gi
    requests:
      cpu: 0.25
      memory: 2Gi
And if I look in the env-configmap.yaml I see:
Copy code
JOB_MAIN_CONTAINER_CPU_LIMIT: {{ ((.Values.jobs.resources | default dict).limits | default dict).cpu | default "" | quote }}
  JOB_MAIN_CONTAINER_CPU_REQUEST: {{ ((.Values.jobs.resources | default dict).requests | default dict).cpu | default "" | quote }}
  JOB_MAIN_CONTAINER_MEMORY_LIMIT: {{ ((.Values.jobs.resources | default dict).limits | default dict).memory | default "" | quote }}
  JOB_MAIN_CONTAINER_MEMORY_REQUEST: {{ ((.Values.jobs.resources | default dict).requests | default dict).memory | default "" | quote }}
And if I do a 
printenv | grep MEMORY
in the pod I'm getting:
Copy code
# printenv | grep MEMORY
JOB_MAIN_CONTAINER_MEMORY_LIMIT=4Gi
JOB_MAIN_CONTAINER_MEMORY_REQUEST=2Gi
So I think for now I'm going to do the database migration backwards and downgrade the connectors back to their original version, but it is just a temporary solution.
Maybe this information can be useful to others, to migrate back I used the following queries:
Copy code
# MySQL
update public.actor set configuration =jsonb_set(configuration, '{replication_method}', '"STANDARD"', true)
WHERE actor_definition_id ='435bb9a5-7887-4809-aa58-28c27df0d7ad';

# MSSQL
update public.actor set configuration =jsonb_set(configuration, '{replication_method}', '"STANDARD"', true)
WHERE actor_definition_id ='b5ea17b1-f170-46dc-bc31-cc744ca984c1';
I just started both the connections, I'll give an update later if the performance is back as it used to be. Not sure if it improved, I'll keep monitoring, current speed isn't that promising (2 logs 1 minute apart from eachother):
Copy code
# MySQL
2022-10-18 08:11:30 INFO i.a.w.g.DefaultReplicationWorker(lambda$getReplicationRunnable$6):352 - Records read: 42000 (20 MB)
2022-10-18 08:12:30 INFO i.a.w.g.DefaultReplicationWorker(lambda$getReplicationRunnable$6):352 - Records read: 106000 (63 MB)

(106000 - 42000) / 60 = 1066,66 records per second


# MSSQL
2022-10-18 08:16:30 INFO i.a.w.g.DefaultReplicationWorker(lambda$getReplicationRunnable$6):352 - Records read: 1161000 (587 MB)
2022-10-18 08:17:30 INFO i.a.w.g.DefaultReplicationWorker(lambda$getReplicationRunnable$6):352 - Records read: 1221000 (617 MB)

(1221000 - 1161000) / 60 = 1000 records per second
Maybe it hasn't anything to do with the connectors upgrade, but with the airbyte upgrade from 
0.40.2
to 
0.40.6
? I can try to downgrade that as well.
After downgrading airbyte from 
0.40.6
to `0.40.2`: MSSQL is fixed, and runs around 2/3k records per second. However MySQL connector is still quite slow.
Copy code
# MySQL
2022-10-18 08:56:05 INFO i.a.w.g.DefaultReplicationWorker(lambda$getReplicationRunnable$6):339 - Records read: 387000 (295 MB)
2022-10-18 08:57:05 INFO i.a.w.g.DefaultReplicationWorker(lambda$getReplicationRunnable$6):339 - Records read: 445000 (343 MB)

(445000 - 387000) / 60 = 966
u
Thanks for that writeup! Could you doublecheck the Airbyte version? There is no 
0.40.6
or 
0.40.2
currently - we are on 
0.40.15
. Did you mean 
0.40.02
and 
0.40.06
? If so, have you tried upgrading to the latest version? The problem might arise from there. https://github.com/airbytehq/airbyte/releases
u
Thanks for that writeup! Could you doublecheck the Airbyte version? There is no 
0.40.6
or 
0.40.2
currently - we are on 
0.40.15
. Did you mean 
0.40.02
and 
0.40.06
? If so, have you tried upgrading to the latest version? The problem might arise from there. https://github.com/airbytehq/airbyte/releases
r
For me it shows 0.40.2, basically https://hub.docker.com/layers/airbyte/server/0.40.2/images/sha256-0567e7b930ee8da8292ff40833ef5fbaf794dc75ab7624b8fe2c3f9d5143d53a?context=explore and all other images with this version. And last time I tried to upgrade 0.40.2 to 0.40.14 I ran into this issue on the worker pod.
Copy code
Message: No bean of type [io.airbyte.config.persistence.split_secrets.SecretPersistence] exists for the given qualifier: @Named('secretPersistence')
I already did the minio config migration.
u
What does it show in the bottom left corner of your instance?
r
The screenshot I just send was from the bottom left corner, it says 0.40.2
u
Oh, sorry! Missed that somehow. Let me look into what can be done about that MySQL connector!
r
I managed to be up to date with the latest helm chart / deployment right now 🙂 (we use the helm provider in terraform)
Copy code
resource "helm_release" "airbyte" {
  name       = "aibyte-chart"
  repository = "<https://airbytehq.github.io/helm-charts>"
  chart      = "airbyte"
  version    = "0.40.27"

   ... lots of values.yaml variables being set ...
}
Everything seems to be a lot faster now, I'll get back later if this solved the issue. One weird thing I noticed, maybe it is a setting of the way airbyte saves it's current source/destination container versions? Everytime I do a new helm release, for instance upping the helm chart version from one version to another (happend from 0.40.24 to 0.40.27) for some reason it seems to automatically update the MSSQL destionation to the latest version. So I had to manually pin it back to 
0.4.17
, nothing too bad but just some weird behavior I noticed and was wondering if you have any idea what might cause it. Thanks for all support so far @Nataly Merezhuk (Airbyte) 🙂
n
No problem, @Ramon Vermeulen! I'm not quite sure if this is expected behavior, so I've made a GitHub issue: https://github.com/airbytehq/airbyte/issues/18178 If you've got a minute to add any details to it, I'd really appreciate it!
✅ 1
7 Views
Open in Slack
PreviousNext
Powered by