https://linen.dev logo
Join Slack
Powered by
Hey there, question about Airbyte in prod: We have...
# announcements
j
Hey there, question about Airbyte in prod: We have deployed on an EC2 (first t3.small, then t3.large) and ca. 8 Google Sheets Connectors implemented - tiny data volume. Both with the first as well as the second size, CPU usage of the box jumps to 100% around the time the first pulls are scheduled and constantly stays there. In the first instance this lasted for five hours straight - nowhere near the amount of time or frequency we had scheduled extracts - until all CPU credits were consumed and the box was essentially killed. Any hint on what is going on? Is this expected in any way?
m
Hey @jan it isn't expected that the CPU remains at 100%
was the data replicated?
j
Hi @Michel yes, to the _raw format. We are on Redshift and have switched on normalization. It has worked in the past, although I just noticed a timelag of an hour between emittance and normalization.
m
this is not normal
Do you have a way to repo the 100%? I would love to see what you seen in the UI and in docker
(what containers are running when you hit 100%)
it never goes down?
j
Hi @Michel sorry for the delay. Will try to systematically reproduce and report back. It basically only goes down once burst credits are consumed.
h
Hi @Michel it seems that airbyte is starting multiple Redshift target containers, we setup 1 Redshift destination with multiple Google Sheet connectors is that normal behaviour?
docker-ps.txt
m
@Hai To it is the expected behavior today.
to guarantee that one faulty source doesn't affect the other ones
everytime a source runs to replicate data we spin up a destination as well
and as soon as the source is done and the destination is done writing we shut the containers down
are you seeing something different?
j
Hi Michel, yes. We’re seeing redshift connectors running since 4-5 hours straight at ca. 65% CPU.
m
@charles Could that be linked to the timeouts?:
image.png
j
For reference:
Bildschirmfoto 2021-01-26 um 19.59.29.png
@Michel @charles checking the logs it looks like bigqueue is the culprit. This is what we’re seeing in several extracts. I’ll post for brevity first, then a full log dump
Copy code
2021-01-26 17:56:08 ERROR (/tmp/workspace/264/2) LineGobbler(voidCall):69 - WARNING: An illegal reflective access operation has occurred
2021-01-26 17:56:08 ERROR (/tmp/workspace/264/2) LineGobbler(voidCall):69 - WARNING: Illegal reflective access by com.leansoft.bigqueue.page.MappedPageImpl$Cleaner (file:/airbyte/lib/leansoft-bigqueue-0.7.3.jar) to method java.nio.DirectByteBuffer.cleaner()
2021-01-26 17:56:08 ERROR (/tmp/workspace/264/2) LineGobbler(voidCall):69 - WARNING: Please consider reporting this to the maintainers of com.leansoft.bigqueue.page.MappedPageImpl$Cleaner
2021-01-26 17:56:08 ERROR (/tmp/workspace/264/2) LineGobbler(voidCall):69 - WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
2021-01-26 17:56:08 ERROR (/tmp/workspace/264/2) LineGobbler(voidCall):69 - WARNING: All illegal access operations will be denied in a future release
Followed by an explicit zombie process log:
Copy code
021-01-26 18:01:06 WARN (/tmp/workspace/264/2) WorkerUtils(gentleClose):49 - Process is taking too long to finish. Killing it
2021-01-26 18:02:06 WARN (/tmp/workspace/264/2) WorkerUtils(gentleClose):57 - Couldn't kill the process. You might have a zombie (Optional[/usr/bin/docker run --rm -i -v airbyte_workspace:/data -v /tmp/airbyte_local:/local -w /data/264/2 --network host airbyte/destination-redshift:0.1.4 write --config target_config.json --catalog catalog.json])
c
i'm looking at the logs now, jan.
@jan for the replication job that is represented in the logs that you sent, was the data successfully replicated into the raw table?
i am trying to understand if we just have a zombie job issue. if the data wasn't successfully replicated then i'll think about this a little differently.
would it be possible for you to upgrade to the most recent version of airbyte? it looks like you are using a relatively old version of the redshift connector.
j
It was successfully replicated into the raw table yes. But not successfully normalized. Again, full disclosure, we switched on normalization on RS and were surprised to find it working despite the docs saying otherwise. Is this likely the reason for the stalls?
Sure thing, let me do that.
c
thanks.
despite the docs saying otherwise
could you point me to where you saw this in the docs?
sounds out of date. redshift does support normalization.
j
Copy code
Exception in thread "main" java.lang.IllegalArgumentException: No migration found for current version: 0.12.1-alpha
	at com.google.common.base.Preconditions.checkArgument(Preconditions.java:142)
	at io.airbyte.migrate.Migrate.run(Migrate.java:93)
	at io.airbyte.migrate.MigrationRunner.run(MigrationRunner.java:69)
	at io.airbyte.migrate.MigrationRunner.main(MigrationRunner.java:107)
Upgrading is unfortunately not working as per https://docs.airbyte.io/tutorials/upgrading-airbyte 😕
The above error is the result of
Copy code
docker run --rm -v /tmp:/config airbyte/migration:0.14.0-alpha --\
  --input /config/airbyte_archive.tar.gz\
  --output /config/airbyte_archive_migrated.tar.gz\
  --target-version 0.14.0-alpha
c
Yeah. That's the upgrade process going forward.
Unfortunately to go from the version you're on to the new version you need to start form scratch.
(removing existing docker volumes)
j
gotcha.
c
did upgrading work out for you?
j
yes, but given i’m in EU time i will only get around setting all gsheet connectors up tomorrow.
Hi @charles, managed to upgrade, but we keep running into the same issues again.
Exclusively using Google Sheets. Logs are not indicative of any single workflow failing and resulting in zombie processes.
c
weird!
so to make sure i understand. you are syncing other sources in to redshift and there are no problems
but when you sync google sheets into redshift just the redshift container never closes, but the google sheets container does close?
if that's the case i should be able to spin this up locally and recreate it. i have already tested manually with redshift and everything looked okay, but i didn't specifically use google sheets so i can add that to the test.
j
we are exclusively syncing google sheets.
c
got it.
j
redshift is now on 0.1.8. i didn’t check docker output upon shutdown earlier, will let it run again for some time.
c
i will recreate this this afternoon.
j
not sure if relevant, but this is the most recent docker ps. note the two containers at the bottom, does that hint to a non-graceful shutdown/ container state?
c
are both zombies?
interesting. possibly.
if you could pull logs again from the UI again that would be helpful.
h
@charles we've created a new EC2 instance and redid the whole deployment with the latest airbyte however we notice that 
airbyte-0.14.1-alpha
still spawns processes with 
connectors-destination-redshift-0.13.1-alpha
is that normal behaviour?
c
let me sanity check this on my side. it seems a little weird, but not insane either.
h
regarding the CPU load keep being 100% it seems to happen when we have spreadsheets with large amount of rows I guess the way the airbyte-redshift connector is directly inserting each row to RS is not ideal for this other connectors like pipelinewise are copying from S3 to RS which is the recommended way for bigger data amounts is there any plan to go with this approach on your side maybe? 🙂
c
great question.
yes we are planning to do this. that being said we do batch writes into redshift and have been able to write significant amounts of data using that connector.
so if the issue is that writes are too slow i think it's a bug and not a fundamental problem with the appraoch.
how big are the sheetS btw? just blappark of num rows.
in the logs i'm seeing a batch of about 9K rows being written. don't know if that's total rows or just what was in that batch.
h
this specific sheet that is causing problem has ~800k rows
👍 1
another thing: we noticed that when we disable & delete the breaking connection (with the 800k rows), there is still a 
connectors-destination-redshift-0.13.1-alpha
process running so we stopped the according docker containers for gsheet source and RS destination, then the CPU load is going down but when we add a new GSheet connection (to another unrelated Gsheet) again a 
connectors-destination-redshift-0.13.1-alpha
process is running at 100% but all connections are not in 
running
state but when we check in the exported 
source_connection.yaml
we still see the deleted connection that was causing the trouble could it be that airbyte is still trying to fetch data for the deleted connection?
checking the logs of the RS destination container confirmed that it is trying to sync a deleted connection, I guess this is not intended behaviour and will create a issue ticket for it
airbyte_logs_deleted_connection.txt
👍 1
c
is it just retrying a job that has failed once?
i'm pretty sure for each job we retry 3 times.
i would not be surprised if that retry logic ignores whether or not the connector is active.
do replication jobs work for everything other than this 800K row table. or they don't work regardless of size?
just tested 1000 rows into redshift and it seems to have worked well with no zombie proceses.
will up to a million rows now.
h
@charles ahh yes it seem to be retrying 3 times, I was wondering already why I cannot reproduce that behaviour again the jobs seem to work fine for other spreadsheets, just the one with the large data set is causing problems, so we excluded that one for now
c
cool. well once google sheets catches up i will hopefully be able to reproduce locally
just leanred that 5 million cells is the max in google sheets.
😄 1
you guys are getting pretty close to that depending on the number of columns 😄
h
yup 🙂 I guess this is also an edge case usually we would not import that amount of data from a spreadsheet I changed severity of the ticket to low since we know now that it only happens 3 times and we can intervene manually thank you very much for the support @charles
👍 1
c
do you have the most recent logs for the big run that failed?
our logging got a lot better in the later version that you're using so if you have the logs for that it would be helpful
going to leave it running but so far now problems
h
@charles unfortunately I do not have the logs of a previous run anymore, but I just started a new connection to that sheet again it looks similar to the previous run that I saw, so there is no direct error, it's just that the replications takes very long but it should eventually finish at some point, I will just leave it running and check later again
logs-66-0.txt
c
it's still running after about 2 hours. it looks like it's about 50% done.
on row 466,924 of 1 million
fwiw, the constraint here seems to be how fast google api is returning data, not writing to redshift.
so it doesn't seem like the way we are uploading the data is the problem.
pulling this data is definitely kinda slow though 😞 but i don't think i can make the google API go faster.
h
@charles ok but now it seemed to have fetched all GS data, but the target connector takes some time to close because there are still buffered records remaining is that a bottleneck in the target or what is keeping this records buffer?
logs-66-0 (1).txt
c
for the sync you're trying to run how many sheets are in it?
definitely looks like that buffer isn't getting written to the db
j
a single one. but it’s big (~800k rows).
c
weird. i transferred a million rows last night from google sheets to redshift.
Screen Shot 2021-01-28 at 5.53.49 AM.png
(ignore the first 2 failures, my internet died in the middle)
j
all else being equal, i guess redshift remains kinda the last culprit?
c
did it fail or is it stuck where the logs endeD?
h
got stuck where the logs ended and I killed the process
c
and it did not stop because you ran out of credits, right?
it seems like the batch writes are very slow.
Copy code
2021-01-28 04:05:35 [32mINFO[m i.a.i.d.b.BufferedStreamConsumer(writeStreamsWithNRecords):194 - {} - Writing stream gs_crm_sales_voucher_recipients. Max batch size: 10000, Actual batch size: 10000, Remaining buffered records: 668832
2021-01-28 04:05:35 [32mINFO[m i.a.i.d.r.RedshiftSqlOperations(insertRecords):59 - {} - actual size of batch: 10000
2021-01-28 04:07:26 [32mINFO[m i.a.i.d.b.BufferedStreamConsumer(writeStreamsWithNRecords):194 - {} - Writing stream gs_crm_sales_voucher_recipients. Max batch size: 10000, Actual batch size: 10000, Remaining buffered records: 658832
2021-01-28 04:07:26 [32mINFO[m i.a.i.d.r.RedshiftSqlOperations(insertRecords):59 - {} - actual size of batch: 10000
it's taking 2 minutes to write 10K records.
above is from the logs that you sent me
here are logs from my machine:
Copy code
2021-01-28 10:13:44 INFO (/tmp/workspace/15/2) LineGobbler(voidCall):69 - 2021-01-28 10:13:44 [32mINFO[m i.a.i.d.b.BufferedStreamConsumer(writeStreamsWithNRecords):194 - {} - Writing stream numbers_big. Max batch size: 10000, Actual batch size: 569, Remaining buffered records: 0
2021-01-28 10:13:44 INFO (/tmp/workspace/15/2) LineGobbler(voidCall):69 - 2021-01-28 10:13:44 [32mINFO[m i.a.i.d.r.RedshiftSqlOperations(insertRecords):59 - {} - actual size of batch: 569
2021-01-28 10:13:47 INFO (/tmp/workspace/15/2) DefaultAirbyteStreamFactory(internalLog):110 - Fetching range numbers_big!919778:919978
2021-01-28 10:13:51 INFO (/tmp/workspace/15/2) DefaultAirbyteStreamFactory(internalLog):110 - Fetching range numbers_big!919979:920179
2021-01-28 10:13:55 INFO (/tmp/workspace/15/2) DefaultAirbyteStreamFactory(internalLog):110 - Fetching range numbers_big!920180:920380
2021-01-28 10:13:56 INFO (/tmp/workspace/15/2) LineGobbler(voidCall):69 - 2021-01-28 10:13:56 [32mINFO[m i.a.i.d.b.BufferedStreamConsumer(writeStreamsWithNRecords):194 - {} - Writing stream numbers_big. Max batch size: 10000, Actual batch size: 625, Remaining buffered records: 0
2021-01-28 10:13:56 INFO (/tmp/workspace/15/2) LineGobbler(voidCall):69 - 2021-01-28 10:13:56 [32mINFO[m i.a.i.d.r.RedshiftSqlOperations(insertRecords):59 - {} - actual size of batch: 625
it's not an apples to apples comparison but each write is taking 2 seconds. granted each batch is only 600 records
maybe let's set up a call for monday and we can just try to talk through what's going on here? i don't think we're being too effecient in getting you problem solved on slack.
h
hey @charles, sorry I didn't check Airbyte Slack in the last days
and it did not stop because you ran out of credits, right?
yes it seemed so the process was just stuck and would not proceed but we do not have any problems currently after we excluded ingestion of that big spreadsheet it would still be great if we could have a call to check why our Redshift writes are so much slower, I would have time today and tomorrow if you are still available
c
sure. i'd be happy to!
i can be available tomorrow anytime between 7:30am-11am or 2pm-6pm San Francisco time. Do either of those time slots overlap with you?
h
yes I would have time from 7:30 - 9:00 feel free to call me via Slack when you have time
👍 1
c
great. will call you then!
👍 1
h
Hey Charles, thank you for the call yesterday, we can confirm that ingestion of bigger Google Spreadsheets still fail. I include the logs of the 1st attempt that failed, it seems that the source stream process was closed after 11h while the Redshift target was still trying to insert data. Please ping me if you need any further information, we would love to help getting insertion to RS faster.
logs-164-0.txt
c
Here are some updates. • The docs for google ads are just out of date. The google ads source does support incremental. • For google analytics and freshdesk, migrating to them incremental is in progress and should be done sometime early next week. (@s we talked about this yesterday, let's make sure we update here if there's any risk of that slipping) • CPU usage - still need to follow up here. I have created this issue to do so. • Speeding up Redshift - I probably can't take a direct look at this until next week but will follow up then. One thing you could try if you're up for it is to change that batch size in the redshift destination and see if that helps. It will require you to re build the connector locally and have airbyte use that custom connector.
s
@Hai To for freshdesk, which API endpoints do you need? we currently support pulling 
"agents", "contacts", "companies", "groups", "roles", "skills", "surveys", "tickets", "time_entries"
h
@charles awesome, thank you for the update, yes I can try to modify the batch size over the next days and give you feedback whether that helped Hi @s, we in addition also use 
"conversations", "satisfaction_ratings"
I've published a fork from the original 
singer-io/tap-freshdesk
in https://github.com/n0rritt/tap-freshdesk/tree/master/tap_freshdesk/schemas with some modifications and better rate-limiting we needed the ability to config that, because our backend team is also sending data to FD API and we have to make sure not to block them
s
do you specifically require that our connector also uses the singer connector under the hood or do you care just about having the same functionality?
our current freshdesk connector does not use singer
h
no it does not matter for us as long as we get the same entities and the ability to config the rate limit we would be super happy 🙂
s
tu great
what’s your ideal timeline on using this connector?
h
hmm well there is no big hurry, we want to decommission or current extractor stack until end of February but until then there are still a lot of other connectors that we would also have to move, either to Airflow or Airbyte
s
The params you need for rate limiting as far as I understand are: • 
rate_limit_requests/seconds
: number of requests allowed to make each N seconds • 
max_retries
• 
backoff_factor
for multiplication with the backoff factor Seems like this max_per_page is unused: https://github.com/n0rritt/tap-freshdesk/blob/master/tap_freshdesk/api.py#L24 do I have this right?
and are all of these params still required?
h
hmm you are right 
per_page
is not really used, the thing that is most important to us are requests per seconds basically all the others are more nice to have
s
tu
for Google Analytics, are you currently using the singer connector?
h
uhm no we are using a Matillion component for that, it's one of the ETL tools we want to get rid of we've tried FiveTrans GA connector and that one worked quite nice since we were able to select our dimensions and measures to create own reports, this is what we would need is that also possible with the Airbyte connector?
I saw that it's a wrapper of the pipelinewise tap (which can ingest custom reports) but your documentation does not mention anything about that also we were wondering how you would avoid running into sampling? in our ETLs we split the ingestion into smaller date chunks to not run into sampling, does your connector do something similar?
s
We haven’t enabled those two functionalities yet. They’re not super straightforward functions to add though so they make take some time for us to implement (especially the chunking piece). I think we can get freshdesk done next week, but GA may have some more volatility with its delivery date — I can keep you updated throughout the week
h
yeah I can imagine that GA API is more tricky 🙂 but thank you very much for looking into it @s, we really appreciate it 🙏 and are looking forward to test the new incremental FD connector
s
@Hai To touching base: Freshdesk should be ready tomorrow or Tuesday at the latest. You can track its status on these PRs: https://github.com/airbytehq/airbyte/pull/2052 https://github.com/airbytehq/airbyte/pull/2058 https://github.com/airbytehq/airbyte/pull/2060 GA still WIP, targeting EOW
Also Hai, how useful is it to have incremental sync in GA i.e: not replicating data that has already been synced?
h
Hi @s, awesome, thank you very much for your update re GA, if incremental sync is not implemented would we always ingest the whole data since the 
start_date
defined in the config if so then incremental sync would be very important
👍🏼 1
s
Got it, yeah that’s what incremental means
@Hai To Freshdesk connector version 
0.1.2
now contains the new streams (conversations, sat ratings) and incremental sync
h
nice, thank you @s we will try it out asap
s
We’re still working to support the reqs/second, track it on this issue: https://github.com/airbytehq/airbyte/issues/1962
h
hmm ok good to know, we will not put it into production yet, but would just test it over night maybe for the last 24h, just to see how it works, great job thank you
s
GA status is: • Supporting custom reports WIP, 90% complete: https://github.com/airbytehq/airbyte/pull/2098 • Supporting chunked syncs to avoid sampling WIP: https://github.com/airbytehq/airbyte/issues/2062 • Supporting incremental sync not yet started, will do next week: https://github.com/airbytehq/airbyte/issues/2106
🙏 2
👍 1
🚀 1
great, let us know if there’s anything off or that can be improved
h
sure we will do that
s
https://github.com/airbytehq/airbyte/pull/2112 minute-level rate limiting has been implemented
https://github.com/airbytehq/airbyte/pull/2098 GA connector now supports custom reports
h
wow incredible job guys 🚀
I've checked out the lastest version, but when I try the Freshdesk connection I only see Full refresh option is there anything else that I need to do / update
screenshot-localhost_8000-2021.02.19-07_52_21.png
s
@Hai To from the admin UI you’ll need to update the freshdesk connector to version 0.1.2
h
ahh got it thx
s
Same for GA, I believe it’s 0.1.8 (afk right now so can’t verify the version)
👍 2
@Hai To GA incremental has been released: upgrade to version 
0.2.2
and you should be good to go!
6 Views
Open in Slack
PreviousNext
Powered by