Workaround for UDF memory leak

Question

Apparently there is a memory leak on BigQuery's UDF. We run a simple UDF over a small table (3000 rows, 5MB) and it fails. If we run the same UDF over the first half of the table concatenated with the second half of the table (in the same query), then it works! Namely:
SELECT blah myUDF(SELECT id,data FROM table)
fails.
SELECT blah myUDF(SELECT id, data FROM table ORDER BY id LIMIT 1500),myUDF(SELECT id, data FROM table ORDER BY id DESC LIMIT 1500)
succeeds.

The question is: how do we work around this issue? Is there a way to dynamically split a table in multiple parts, each of equal size and of predefined number of rows? Say 1000 rows at a time? (the sample table has 3000 rows, but we want this to succeed in larger tables, and if we split a 6000 row table in half, the UDF will be failing again on each half).

In any solution, it is important to (a) NOT use ORDER BY, because it has a 65000 row limitation; (b) use a single combined query (otherwise the solution may be too slow, plus every combined table is charged at a minimum of 10MB, so if we have to split a 1,000,000 row table into 1,000 rows at a time we will automatically be charged for 10 GB. Times 1,000 tables = 10TB. This stuff adds up quickly)
Any ideas?

I'm digging into root causes for JS OOM as we speak, stay tuned! Just out of curiosity, why do you think BigQuery has a 65,000 row limit on ORDER BY? Here is a query that orders almost 19 million rows : SELECT [by] FROM [bigquery-public-data:hacker_news.full_201510] order by 1. — thomaspark
Do you have a BigQuery job id you can share for your failing query? I will add it to my repro list. — thomaspark
We found a (prob temporary) fix, so it isn't failing any more. Here is one that did fail, but be aware that we have replaced the UDF with one that works, and the input table is no longer there (not needed after the computation succeeded): academic-diode-113417:bquijob_193cc0f5_1539c3a2c91 — user3688176
On the ORDER BY: we had a failing large query with several subqueries over multiple tables, with one subquery having an ORDER BY and several having GROUP BY. We first used "EACH" on every join, group by and order. I can't find the reference right now, but in my research on why it was failing I read somewhere that ordering may cause the job to be handled by a single worker and thus fail at 65k records. Sure enough, when we removed the order by clause the query worked fine. Before you suggest that "EACH" should not be used, we tried that on a simple and small query and it failed miserably — user3688176
Hm, ORDER BY works for arbitrary numbers of rows, as long as the query can fit underneath the "large results" threshold. Once the "use large results" box is checked, then we can't apply ORDER BY. — thomaspark

thomaspark thomaspark · Accepted Answer · 2016-03-28T20:56:41

This issue was related to limits we had on the size of the UDF code. It looks like V8's optimize+recompile pass of the UDF code generates a data segment that was bigger than our limits, but this was only happening when when the UDF runs over a "sufficient" number of rows. I'm meeting with the V8 team this week to dig into the details further.

In the meantime, we've rolled out a fix to update the max data segment size. I've verified that this fixes several other queries that were failing for the same reason.

Could you please retry your query? I'm afraid I can't easily get to your code resources in GCS.

Workaround for UDF memory leak

1 Answers