search

google / starthinker

Reference framework for building data workflows provided by Google. Accelerates authentication, logging, scheduling, and deployment of solutions using GCP. To borrow a tagline.. "The framework for professionals with deadlines."
Apache License 2.0
165 stars 49 forks source link

Performance Tuning: DV360 Bulk Editor Recipe Run Times Lagging #53

Open sopwilso opened 3 years ago

sopwilso commented 3 years ago

For users who filtered on a non-TEST DV360 advertiser, and only 1 campaign under selected advertiser, when they went to Load Insertion Orders & Line Items, it was taking 60+ minutes to barely load the first tab of Insertion Orders, let alone all the other tabs. However, users who filtered on the TEST advertiser had theirs complete within normal running time.

The following will show you that it was taking 19+ minutes to read the Advertisers tab in my sheet. recipe_running

Not sure if we can pin point what might have been the cause. Since everyone filtered on an Advertiser AND a Campaign, I can’t imagine the Campaign under non-TEST advertisers were significantly larger, if anything the opposite, because our TEST campaigns have a ton of IOs / Line Items loaded under them from QA.

UPDATE: User confirmed the following day that recipe eventually Errored and shared the following log:

Hour Task Output / Error
done_outline   13 dataset - 1 JOB_END 19 Hours 31 Minutes Ago TASK: dataset DATE: 2021-04-22 HOUR: 13 DATASET starthinker-dev-0561-1465-0921 DV_Editor_Dasher_Computing_Bulk_Editor DATASET CREATE DATASET ACCESS
done_outline   13 drive - 1 JOB_END 19 Hours 31 Minutes Ago TASK: drive DATE: 2021-04-22 HOUR: 13 Drive Drive Copy https://docs.google.com/spreadsheets/d/18G6cGo4j5SsY08H8P53R22D_Pm6m-zkE6APd3EDLf2c/ DV Editor Dasher Computing Bulk Editor Drive: File exists.
error   13 dv_editor - 1 JOB_ERROR 15 Hours 6 Minutes Ago TASK: dv_editor DATE: 2021-04-22 HOUR: 13 COMMAND: Load Insertion Orders and Line Items DISCOVERY FETCH: https://displayvideo.googleapis.com/$discovery/rest?version=v1&key= SHEETS CLEAR DV Editor Dasher Computing Bulk Editor Creatives B2:Z DISCOVERY FETCH: https://displayvideo.googleapis.com/$discovery/rest?version=v1&key= BIGQUERY JSON TO TABLE:  starthinker-dev-0561-1465-0921 DV_Editor_Dasher_Computing_Bulk_Editor DV_Creatives SHEETS READ DV Editor Dasher Computing Bulk Editor Advertisers A2:A BigQuery Buffer Size 230077472 Uploaded 100% BIGQUERY JOB WAIT: job_Tt3Xj9r4GoaU-hmc7IEiNw9loKJ9 .....JOB COMPLETE: starthinker-dev-0561-1465-0921:US.job_Tt3Xj9r4GoaU-hmc7IEiNw9loKJ9 SHEETS WRITE DV Editor Dasher Computing Bulk Editor Creatives B2 BIGQUERY QUERY: starthinker-dev-0561-1465-0921 DV_Editor_Dasher_Computing_Bulk_Editor HTTP ERROR: [Errno 32] Broken pipe HTTP RETRY / WAIT: 3 31 Traceback (most recent call last):   File "/home/cjones8/starthinker/util/google_api/init.py", line 91, in API_Retry     data = job.execute()   File "/home/cjones8/starthinker_virtualenv/lib/python3.7/site-packages/googleapiclient/_helpers.py", line 134, in positional_wrapper     return wrapped(*args, *kwargs)   File "/home/cjones8/starthinker_virtualenv/lib/python3.7/site-packages/googleapiclient/http.py", line 909, in execute     headers=self.headers,   File "/home/cjones8/starthinker_virtualenv/lib/python3.7/site-packages/googleapiclient/http.py", line 204, in _retry_request     raise exception   File "/home/cjones8/starthinker_virtualenv/lib/python3.7/site-packages/googleapiclient/http.py", line 177, in _retry_request     resp, content = http.request(uri, method, args, kwargs)   File "/home/cjones8/starthinker_virtualenv/lib/python3.7/site-packages/google_auth_httplib2.py", line 201, in request     uri, method, body=body, headers=request_headers, kwargs)   File "/home/cjones8/starthinker_virtualenv/lib/python3.7/site-packages/httplib2/init.py", line 1709, in request     conn, authority, uri, request_uri, method, body, headers, redirections, cachekey,   File "/home/cjones8/starthinker_virtualenv/lib/python3.7/site-packages/httplib2/init.py", line 1424, in _request     (response, content) = self._conn_request(conn, request_uri, method, body, headers)   File "/home/cjones8/starthinker_virtualenv/lib/python3.7/site-packages/httplib2/init.py", line 1347, in _conn_request     conn.request(method, request_uri, body, headers)   File "/usr/lib/python3.7/http/client.py", line 1260, in request     self._send_request(method, url, body, headers, encode_chunked)   File "/usr/lib/python3.7/http/client.py", line 1306, in _send_request     self.endheaders(body, encode_chunked=encode_chunked)   File "/usr/lib/python3.7/http/client.py", line 1255, in endheaders     self._send_output(message_body, encode_chunked=encode_chunked)   File "/usr/lib/python3.7/http/client.py", line 1069, in _send_output     self.send(chunk)   File "/usr/lib/python3.7/http/client.py", line 991, in send     self.sock.sendall(data)   File "/usr/lib/python3.7/ssl.py", line 1015, in sendall     v = self.send(byte_view[count:])   File "/usr/lib/python3.7/ssl.py", line 984, in send     return self._sslobj.write(data) BrokenPipeError: [Errno 32] Broken pipe   During handling of the above exception, another exception occurred:   Traceback (most recent call last):   File "/home/cjones8/starthinker/task/dv_editor/run.py", line 168, in     dv_editor()   File "/home/cjones8/starthinker/util/project/init.py", line 475, in from_parameters_wrapper     func()   File "/home/cjones8/starthinker/task/dv_editor/run.py", line 81, in dv_editor     creative_load()   File "/home/cjones8/starthinker/task/dv_editor/creative.py", line 124, in creative_load     rows   File "/home/cjones8/starthinker/util/data/init.py", line 315, in put_rows     destination['sheets'].get('append', False),   File "/home/cjones8/starthinker/util/sheets/init.py", line 131, in sheets_write     valueInputOption=valueInputOption   File "/home/cjones8/starthinker/util/google_api/init.py", line 386, in execute     self.response = API_Retry(self.job)   File "/home/cjones8/starthinker/util/google_api/init.py", line 129, in API_Retry     return API_Retry(job, key, retries - 1, wait 2)   File "/home/cjones8/starthinker/util/google_api/init.py", line 91, in API_Retry     data = job.execute()   File "/home/cjones8/starthinker_virtualenv/lib/python3.7/site-packages/googleapiclient/_helpers.py", line 134, in positional_wrapper     return wrapped(args, **kwargs)   File "/home/cjones8/starthinker_virtualenv/lib/python3.7/site-packages/googleapiclient/http.py", line 915, in execute     raise HttpError(resp, content, uri=self.uri) googleapiclient.errors.HttpError: <HttpError 502 when requesting https://sheets.googleapis.com/v4/spreadsheets/1tic_KyD0WmNU8v1QKKQ3lIfu0363txetEuIVYAm_zIA/values/Creatives%21B2?valueInputOption=RAW&alt=json returned "Bad Gateway">
sopwilso commented 3 years ago

Hoping to get this one addressed by next week as we are currently blocked in our ability to pull in advertisers of significant size.