Google-cloud-dotnet: BigQuery: System.OutOfMemoryException thrown when deleting data from large tables

[jaredhughes1012]

When executing "Delete" queries on large BigQuery tables (100 million+ rows), a System.OutOfMemoryException is being thrown from the BigQuery .NET driver. Occasionally, this will cause the process to terminate unexpectedly instead of throwing an exception.

An example table with this issue has 1.2 billion rows, 26 columns, and 292 GB of data

Environment details

• OS: Ubuntu 18.04
• .NET version: .NET Core 2.1.401
• Package name and version: Google.Cloud.BigQuery.V2 1.3.0-beta05

Steps to reproduce

1. Create a table with at least 100 million rows of data in BigQuery
2. Execute the following code:

var credential = GetGoogleCredential();
using (var client = await BigQueryClient.CreateAsync("projectId", credential)) {
   client.ExecuteQuery("DELETE FROM `projectId.dataset.table` WHERE TRUE");
}

Stack Trace

System.AggregateException: One or more errors occurred. ---> System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown. at System.String.CreateStringFromEncoding(Byte* bytes, Int32 byteLength, Encoding encoding) at System.Text.UTF8Encoding.GetString(Byte[] bytes, Int32 index, Int32 count) at System.Net.Http.HttpContent.ReadBufferAsString(ArraySegment1 buffer, HttpContentHeaders headers) at System.Net.Http.HttpContent.ReadBufferedContentAsString() at System.Net.Http.HttpContent.<>c.<ReadAsStringAsync>b__36_0(HttpContent s) at System.Net.Http.HttpContent.<WaitAndReturnAsync>d__632.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Google.Apis.Services.BaseClientService.d__331.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Google.Apis.Requests.ClientServiceRequest1.d__34.MoveNext()

[jskeet]

I'll look into exactly what's going on here. We may need extra overloads for DML scenarios where we only want a row count in the result.

Thanks for the clear report.

[jskeet]

As a workaround for now, I suspect you could use BigQueryClient.CreateQueryJob to create the job, rather than using ExecuteQuery. You could then wait until the job is complete, without fetching the "query" results of that job. Is that useful?

[jaredhughes1012]

Yes that is useful. I will give this a try and post back here if there is an issue. Thank you for the quick response

[jaredhughes1012]

The workaround failed. The process is now terminating with exit code 9 instead of throwing an exception.

[jskeet]

Do you know where it's dying? That's very odd, for no exception to be thrown...

[jskeet]

I'm still trying to reproduce this (it takes a while for the streaming buffer to flush) but in the meantime, I'm surprised that just ExecuteQuery is failing for you - because that shouldn't be fetching any query results. Are you sure you're not then calling ToList() on the results or something similar? (It looks like the code you've provided isn't the exact code as otherwise it wouldn't compile - you haven't provided an argument for parameters. It would be good to know what the real code looks like that's failing.)

[jskeet]

Okay, I've tried to reproduce this, but haven't managed. I created a new table with 100 million rows in, and deleted it like this:

var results = client.ExecuteQuery($"DELETE FROM {table} WHERE TRUE", null);

I've watched the HTTP traffic, and there's hardly any - in particular, it doesn't return more data just because there were lots of rows.
My suspicion is that your process was already running out of memory, and it so happened that a not-terribly-large response tipped it over the edge.

If you're able to provide a way of reproducing this, we can look into it further - otherwise I don't think we'll be able to help.

[jaredhughes1012]

I've done some investigating and I've discovered the issue is that the BigQuery result is returning every remaining row in the table after executing the delete query. This is partially an issue with the way we are handling the response and I believe we can work around it, but this could probably be safeguarded by the library itself.

The code in the example above did not include where the issue was occurring. The way we are using this code is we have built a generic wrapper around BigQuery that will convert the results of a query to a generic type and return the whole set of results. The problem part of the code was looks like this.

using (var client= await BigQueryClient.CreateAsync(projectId, credential)) {
  var result = await client.ExecuteQueryAsync(query, params);
  foreach (var row in result) {
    // Convert row here
  }
  // Return rows here
}

The issue we're having is the result appears to be returning every row in the table when running a delete query. To test this I executed the following queries on a table with one column named "id" and 10 rows with the ids ascending from 1 to 10

DELETE FROM {table} WHERE id=1;
DELETE FROM {table} WHERE FALSE;

Then I modified the code above for debugging purposes

using (var client= await BigQueryClient.CreateAsync(projectId, credential)) {
  var result = await client.ExecuteQueryAsync(query, params);
  var rows = result.Select(row => row["id"]).ToList();

  Assert.Contains(10, rows);
  Assert.Contains(9, rows);
  // ...
  Assert.Contains(1, rows);
}

When I ran this code with the first query, every assertion except for 1 passed. With the second query every assertion passed

When we are running this with hundreds of gigs of data, it's easy to see how the original code would eventually cause the system to run out of memory.

It seems to me expected behavior for this would be to throw an InvalidOperationException from attempting to iterate over an empty result set, as this is already how result.TotalRows already behaves. Alternatively, it could work where result.TotalRows returns and the result enumerator could be empty, which is the behavior that was expected.

[jskeet]

Iterating over an empty result set is generally fine - TotalRows only throws an InvalidOperationException if that value isn't present in the initial JSON response. (And if I'd known that would ever be missing, I'd have made the property nullable.)

I'm surprised that it's returning the non-deleted rows, but I assume that's deliberate. If you're choosing to then store all of those in memory, then I'm not surprised that you're running out of memory. It sounds like you need to have a second wrapper that executes the query but doesn't return everything.

I'll investigate the behavior about returning non-deleted rows, and check with the server-side team that that's the expected behavior. The way we list the results of a query is to iterate over the result table... if the query job is returning the original table as the "result" table, then that would explain things... I'll need to think about how we handle that.

[jskeet]

Would you mind if I closed this issue, but created a new one of "DML statements return query results over whole table"? We can then work out how we wish to proceed in terms of either documenting it (with tests) or modifying the behavior (which would be a breaking change, so unlikely). I think that would be clearer for anyone reading the issue than trying to understand this one.

[jaredhughes1012]

Yes, we just added a second wrapper that ignores the returned rows, which resolved the issue for us. We hadn't considered that the result of the operation would be the resulting table (though we probably should have), but simply documenting that behavior would probably be enough to prevent others from running into the same issue.

[jskeet]

Great, thanks. I'll add a test to verify that it's the case, and add documentation to that effect - as well as raising it with other languages.