Skip to content

Commit ef30a6e

Browse files
alxmrsclaude
andcommitted
Run the GPT's CTEs once each on DataFusion, so it trains past one layer
DataFusion plans a CTE again at every place that reads it, so a model whose CTEs read each other more than once has a plan that grows exponentially with its depth (#267). The 1-layer loss took 2.7 s at the demo's size and 108 s at nanoGPT's CPU size, and the 2-layer loss ran out of memory while planning. - The GPT's SQL builders return their CTEs as (name, sql) pairs, and every statement starts with the model's and the loss's CTEs, through _hosts.with_query. - Hosts gain query_with(ctes, select) and operators_with. DataFusion's runs each CTE on its own into an in-memory table that later CTEs read, under TODO(apache/datafusion#26237). It uses collect_partitioned and register_record_batches rather than DataFrame.cache(), which einfold can't yet plan a join of (xqlsystems/einfold#93). - The model is no longer the view logits. Creating a DataFusion view writes its CTEs out, so the 3-layer view didn't fit in 8 GB; ddx, given the CTEs inline, computes each shared one once. apache/datafusion#24780's streaming fan-out example doesn't fit these queries: by its own notes it can deadlock when both sides of a hash join read the shared plan, and doesn't nest. DataFusion 55 parses `AS MATERIALIZED` only in its PostgreSQL dialect, and ignores it. With ddxdb 0.4.4, at batch 4, on an Intel i7-8700: the 2-layer loss takes 0.07 s on DataFusion, and a 6-layer SGD step 2.4 s, against 0.30 s and 5.5 s on DuckDB; every step matches JAX. Co-Authored-By: Claude Opus 5.5 <[email protected]> Claude-Session: https://claude.ai/code/session_01NyN7idACeDMh94qrnFMu6h
1 parent 1361a59 commit ef30a6e

4 files changed

Lines changed: 227 additions & 103 deletions

File tree

‎benchmarks/ml/02_nanogpt.py‎

Lines changed: 150 additions & 93 deletions
Original file line numberDiff line numberDiff line change
@@ -34,9 +34,12 @@
3434
the learning rate schedule as a table
3535
================= ============================================
3636
37-
As in nanoGPT, the model and the loss are separate. The model is the
38-
view ``logits``; the loss is SQL that reads it, as nanoGPT's
39-
``F.cross_entropy`` reads the model's logits.
37+
As in nanoGPT, the model and the loss are separate. The model is a list
38+
of CTEs that ends in ``logits``, and the loss is more CTEs that read it,
39+
as nanoGPT's ``F.cross_entropy`` reads the model's logits. Each statement,
40+
the loss's and each training step's, starts with both. The model isn't a
41+
view: DataFusion writes a view's CTEs out again at every read, and a
42+
3-layer model's view doesn't fit in 8 GB.
4043
4144
Each training step updates every parameter table with its own
4245
statement; the statements share one backward pass. By default the
@@ -91,6 +94,7 @@
9194
import numpy as np
9295
import xarray as xr
9396
from _bench import Report, assert_close, cli, hosts
97+
from _hosts import with_query
9498

9599
import xarray_sql as xql
96100

@@ -143,171 +147,218 @@
143147
# --------------------------------------------------------------------------
144148

145149

146-
def layer_norm(x: str, out: str, gain: str, layer: int | None = None) -> str:
150+
def layer_norm(
151+
x: str, out: str, gain: str, layer: int | None = None
152+
) -> list[tuple[str, str]]:
147153
"""Normalize each position of ``x`` to zero mean and unit variance,
148154
then scale each channel by the table ``gain`` (of ``layer``, if set)."""
149155
of_layer = "" if layer is None else f" AND g.layer = {layer}"
150-
return f"""
151-
{out}_mean AS (
152-
SELECT seq, pos, AVG(val) AS mean FROM {x} GROUP BY seq, pos),
153-
{out}_var AS (
156+
return [
157+
(
158+
f"{out}_mean",
159+
f"SELECT seq, pos, AVG(val) AS mean FROM {x} GROUP BY seq, pos",
160+
),
161+
(
162+
f"{out}_var",
163+
f"""
154164
SELECT x.seq, x.pos, AVG(power(x.val - m.mean, 2)) AS var
155165
FROM {x} x JOIN {out}_mean m ON x.seq = m.seq AND x.pos = m.pos
156-
GROUP BY x.seq, x.pos),
157-
{out} AS (
166+
GROUP BY x.seq, x.pos""",
167+
),
168+
(
169+
out,
170+
f"""
158171
SELECT x.seq, x.pos, x.e, (x.val - m.mean) / sqrt(v.var + 1e-5) * g.w AS val
159172
FROM {x} x
160173
JOIN {out}_mean m ON x.seq = m.seq AND x.pos = m.pos
161174
JOIN {out}_var v ON x.seq = v.seq AND x.pos = v.pos
162-
JOIN {gain} g ON x.e = g.e{of_layer}),"""
175+
JOIN {gain} g ON x.e = g.e{of_layer}""",
176+
),
177+
]
163178

164179

165180
# GELU's tanh approximation: x/2 · (1 + tanh(√(2/π) · (x + 0.044715 x³))).
166181
GELU_SCALE = np.sqrt(2 / np.pi)
167182

168183

169-
def attention(layer: int, x: str, out: str) -> str:
184+
def attention(layer: int, x: str, out: str) -> list[tuple[str, str]]:
170185
"""nanoGPT's ``CausalSelfAttention``: each position of ``x`` attends to
171186
itself and the positions before it, in each head, and ``out`` is the
172187
heads' outputs, projected back to the residual stream."""
173188
l = f"l{layer}"
174-
projections = "".join(
175-
f"""
176-
{l}_{name} AS (
189+
projections = [
190+
(
191+
f"{l}_{name}",
192+
f"""
177193
SELECT x.seq, x.pos, p.h, p.c, SUM(x.val * p.w) AS val
178194
FROM {x} x JOIN w{name} p ON x.e = p.e
179195
WHERE p.layer = {layer}
180-
GROUP BY x.seq, x.pos, p.h, p.c),"""
196+
GROUP BY x.seq, x.pos, p.h, p.c""",
197+
)
181198
for name in ("q", "k", "v")
182-
)
183-
return f"""{projections}
184-
{l}_scores AS (
199+
]
200+
return projections + [
201+
(
202+
f"{l}_scores",
203+
f"""
185204
SELECT q.seq, q.h, q.pos AS i, k.pos AS j,
186205
SUM(q.val * k.val) / {np.sqrt(HEAD)} AS s
187206
FROM {l}_q q JOIN {l}_k k ON q.seq = k.seq AND q.h = k.h AND q.c = k.c
188207
WHERE q.pos >= k.pos
189-
GROUP BY q.seq, q.h, q.pos, k.pos),
190-
{l}_weights AS (
208+
GROUP BY q.seq, q.h, q.pos, k.pos""",
209+
),
210+
(
211+
f"{l}_weights",
212+
f"""
191213
-- The max only keeps exp() from overflowing: it cancels out of p / z.
192214
SELECT seq, h, i, j,
193215
exp(s - MAX(s) OVER (PARTITION BY seq, h, i)) AS p
194-
FROM {l}_scores),
195-
{l}_norm AS (
196-
SELECT seq, h, i, SUM(p) AS z FROM {l}_weights GROUP BY seq, h, i),
197-
{l}_heads AS (
216+
FROM {l}_scores""",
217+
),
218+
(
219+
f"{l}_norm",
220+
f"SELECT seq, h, i, SUM(p) AS z FROM {l}_weights GROUP BY seq, h, i",
221+
),
222+
(
223+
f"{l}_heads",
224+
f"""
198225
SELECT a.seq, a.i AS pos, a.h, v.c, SUM(a.p / n.z * v.val) AS val
199226
FROM {l}_weights a
200227
JOIN {l}_v v ON a.seq = v.seq AND a.h = v.h AND a.j = v.pos
201228
JOIN {l}_norm n ON a.seq = n.seq AND a.h = n.h AND a.i = n.i
202-
GROUP BY a.seq, a.i, a.h, v.c),
203-
{out} AS (
229+
GROUP BY a.seq, a.i, a.h, v.c""",
230+
),
231+
(
232+
out,
233+
f"""
204234
SELECT a.seq, a.pos, p.e, SUM(a.val * p.w) AS val
205235
FROM {l}_heads a JOIN wo p ON a.h = p.h AND a.c = p.c
206236
WHERE p.layer = {layer}
207-
GROUP BY a.seq, a.pos, p.e),"""
237+
GROUP BY a.seq, a.pos, p.e""",
238+
),
239+
]
208240

209241

210-
def mlp(layer: int, x: str, out: str) -> str:
242+
def mlp(layer: int, x: str, out: str) -> list[tuple[str, str]]:
211243
"""nanoGPT's ``MLP``: each position of ``x`` through a hidden layer
212244
four times as wide, and GELU, back to the residual stream as ``out``."""
213245
l = f"l{layer}"
214-
return f"""
215-
{l}_fc AS (
246+
return [
247+
(
248+
f"{l}_fc",
249+
f"""
216250
SELECT x.seq, x.pos, p.f, SUM(x.val * p.w) AS val
217251
FROM {x} x JOIN w_fc p ON x.e = p.e
218252
WHERE p.layer = {layer}
219-
GROUP BY x.seq, x.pos, p.f),
220-
{l}_hidden AS (
253+
GROUP BY x.seq, x.pos, p.f""",
254+
),
255+
(
256+
f"{l}_hidden",
257+
f"""
221258
SELECT seq, pos, f,
222259
0.5 * val * (1 + tanh({GELU_SCALE} * (val + 0.044715 * val * val * val)))
223260
AS val
224-
FROM {l}_fc),
225-
{out} AS (
261+
FROM {l}_fc""",
262+
),
263+
(
264+
out,
265+
f"""
226266
SELECT x.seq, x.pos, p.e, SUM(x.val * p.w) AS val
227267
FROM {l}_hidden x JOIN w_proj p ON x.f = p.f
228268
WHERE p.layer = {layer}
229-
GROUP BY x.seq, x.pos, p.e),"""
269+
GROUP BY x.seq, x.pos, p.e""",
270+
),
271+
]
230272

231273

232-
def residual(x: str, y: str, out: str) -> str:
274+
def residual(x: str, y: str, out: str) -> list[tuple[str, str]]:
233275
"""``out = x + y``, channel by channel: a residual connection."""
234-
return f"""
235-
{out} AS (
276+
return [
277+
(
278+
out,
279+
f"""
236280
SELECT x.seq, x.pos, x.e, x.val + y.val AS val
237-
FROM {x} x JOIN {y} y ON x.seq = y.seq AND x.pos = y.pos AND x.e = y.e),"""
281+
FROM {x} x JOIN {y} y ON x.seq = y.seq AND x.pos = y.pos AND x.e = y.e""",
282+
)
283+
]
238284

239285

240-
def block(layer: int, x: str, out: str) -> str:
286+
def block(layer: int, x: str, out: str) -> list[tuple[str, str]]:
241287
"""nanoGPT's ``Block``: ``mid = x + attention(ln_1(x))``, then
242288
``out = mid + mlp(ln_2(mid))``."""
243289
l = f"l{layer}"
244-
return "".join(
245-
[
246-
layer_norm(x, f"{l}_ln1", "ln_1", layer),
247-
attention(layer, f"{l}_ln1", f"{l}_attn"),
248-
residual(x, f"{l}_attn", f"{l}_mid"),
249-
layer_norm(f"{l}_mid", f"{l}_ln2", "ln_2", layer),
250-
mlp(layer, f"{l}_ln2", f"{l}_mlp"),
251-
residual(f"{l}_mid", f"{l}_mlp", out),
252-
]
253-
)
290+
return [
291+
*layer_norm(x, f"{l}_ln1", "ln_1", layer),
292+
*attention(layer, f"{l}_ln1", f"{l}_attn"),
293+
*residual(x, f"{l}_attn", f"{l}_mid"),
294+
*layer_norm(f"{l}_mid", f"{l}_ln2", "ln_2", layer),
295+
*mlp(layer, f"{l}_ln2", f"{l}_mlp"),
296+
*residual(f"{l}_mid", f"{l}_mlp", out),
297+
]
254298

255299

256-
def model() -> str:
257-
"""The model, as the view ``logits``: for each position of each
300+
def model() -> list[tuple[str, str]]:
301+
"""The model, as CTEs ending in ``logits``: for each position of each
258302
sequence in ``batch``, a score for every character that could come
259-
next. A DataFusion view reads the tables that existed when it was
260-
created (DuckDB's looks them up by name), so it is recreated whenever
261-
the batch or the parameters are."""
262-
blocks = "".join(
263-
block(layer, f"x{layer}", f"x{layer + 1}") for layer in range(LAYERS)
264-
)
265-
return f"""
266-
CREATE OR REPLACE VIEW logits AS
267-
WITH x0 AS (
303+
next."""
304+
embed = """
268305
SELECT b.seq, b.pos, te.e, te.w + pe.w AS val
269306
FROM batch b
270307
JOIN wte te ON b.tok = te.tok
271-
JOIN wpe pe ON b.pos = pe.pos AND te.e = pe.e),
272-
{blocks}
273-
{layer_norm(f"x{LAYERS}", "final", "ln_f").rstrip(",")}
274-
SELECT x.seq, x.pos, te.tok, SUM(x.val * te.w) AS val
275-
FROM final x JOIN wte te ON x.e = te.e
276-
GROUP BY x.seq, x.pos, te.tok
277-
"""
308+
JOIN wpe pe ON b.pos = pe.pos AND te.e = pe.e"""
309+
unembed = """
310+
SELECT x.seq, x.pos, te.tok, SUM(x.val * te.w) AS val
311+
FROM final x JOIN wte te ON x.e = te.e
312+
GROUP BY x.seq, x.pos, te.tok"""
313+
return [
314+
("x0", embed),
315+
*(
316+
cte
317+
for layer in range(LAYERS)
318+
for cte in block(layer, f"x{layer}", f"x{layer + 1}")
319+
),
320+
*layer_norm(f"x{LAYERS}", "final", "ln_f"),
321+
("logits", unembed),
322+
]
278323

279324

280325
# The loss, separate from the model, as in nanoGPT: the mean cross-entropy
281326
# of the model's logits against each next character of the batch.
282327
# Subtracting each row's max only keeps exp() from overflowing: it cancels
283328
# out of the log-sum-exp.
284-
#
285-
# The loss reads the model's view once, into the CTE ``predicted``, and
286-
# uses that twice: a host expands a view at every read, but DuckDB
287-
# computes a CTE that is read twice only once.
288-
LOSS = """
289-
predicted AS (
290-
SELECT seq, pos, tok, val FROM logits),
291-
shifted AS (
329+
LOSS = [
330+
(
331+
"shifted",
332+
"""
292333
SELECT seq, pos, tok, val,
293334
MAX(val) OVER (PARTITION BY seq, pos) AS m
294-
FROM predicted),
295-
log_sum_exp AS (
335+
FROM logits""",
336+
),
337+
(
338+
"log_sum_exp",
339+
"""
296340
SELECT seq, pos, MAX(m) + ln(SUM(exp(val - m))) AS lse
297-
FROM shifted GROUP BY seq, pos),
298-
loss AS (
341+
FROM shifted GROUP BY seq, pos""",
342+
),
343+
(
344+
"loss",
345+
"""
299346
SELECT AVG(s.lse - l.val) AS l
300347
FROM log_sum_exp s
301348
JOIN batch b ON s.seq = b.seq AND s.pos = b.pos
302-
JOIN predicted l ON l.seq = b.seq AND l.pos = b.pos AND l.tok = b.next)"""
349+
JOIN logits l ON l.seq = b.seq AND l.pos = b.pos AND l.tok = b.next""",
350+
),
351+
]
303352

304353

305354
def step_statements(lr: float) -> list[str]:
306355
"""One SGD step: each parameter table's next version, as a statement."""
307356
return [
308-
f"WITH {LOSS} SELECT {', '.join(dims)}, "
309-
f"w - {lr} * grad(loss, w) AS w FROM {table} "
310-
f"ORDER BY {', '.join(dims)}"
357+
with_query(
358+
model() + LOSS,
359+
f"SELECT {', '.join(dims)}, w - {lr} * grad(loss, w) AS w "
360+
f"FROM {table} ORDER BY {', '.join(dims)}",
361+
)
311362
for table, dims in PARAMS.items()
312363
]
313364

@@ -317,8 +368,11 @@ def gradient_statements() -> list[str]:
317368
the table ``{name}_grad``'s rows. The statements share one backward
318369
pass."""
319370
return [
320-
f"WITH {LOSS} SELECT {', '.join(dims)}, grad(loss, w) AS g "
321-
f"FROM {table} ORDER BY {', '.join(dims)}"
371+
with_query(
372+
model() + LOSS,
373+
f"SELECT {', '.join(dims)}, grad(loss, w) AS g "
374+
f"FROM {table} ORDER BY {', '.join(dims)}",
375+
)
322376
for table, dims in PARAMS.items()
323377
]
324378

@@ -360,7 +414,7 @@ def adamw_statement(table: str, step: int) -> str:
360414

361415

362416
def loss_statement() -> str:
363-
return f"WITH {LOSS} SELECT l FROM loss"
417+
return with_query(model() + LOSS, "SELECT l FROM loss")
364418

365419

366420
# --------------------------------------------------------------------------
@@ -553,7 +607,7 @@ def main():
553607
report = Report("nanogpt", "batch", args.reps)
554608
text = shakespeare()
555609
vocab = len(set(text))
556-
lines = len(model().splitlines()) + len(loss_statement().splitlines())
610+
lines = len(loss_statement().splitlines())
557611
count = sum(
558612
v.size for v in init_params(vocab, np.random.default_rng(0)).values()
559613
)
@@ -582,22 +636,25 @@ def main():
582636

583637
for step, batch in data:
584638
host.load("batch", batch)
585-
host.execute(model())
586639
tok, nxt = batch["tok"].values, batch["next"].values
587640

588641
rows = report.time(
589642
f"loss, step {step}",
590643
host,
591644
size,
592-
host.query,
593-
loss_statement(),
645+
host.query_with,
646+
model() + LOSS,
647+
"SELECT l FROM loss",
594648
)
595649
want = jax_loss(reference, tok, nxt)
596650
report.check(
597651
assert_close("loss", rows["l"][0].as_py(), want, rtol=1e-9)
598652
)
599653
report.note("loss", round(float(want), 6))
600-
report.note("operators", host.operators(loss_statement()))
654+
report.note(
655+
"operators",
656+
host.operators_with(model() + LOSS, "SELECT l FROM loss"),
657+
)
601658

602659
train = f"train ({args.optimizer}), step {step}"
603660
if not host.differentiates:

0 commit comments

Comments
 (0)