| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203 |
- """Reproducible high-cardinality SQLite benchmark. Synthetic, never real-data evidence.
- .venv/bin/python scripts/benchmark_queries.py --rows 4000000 --pipeline-rows 100000
- 4m raw CSV uses the real upload indexer; high-cardinality monthly is an explicit
- SQL fixture, not the result of classifying all 4m rows. Full pipeline is measured
- separately on --pipeline-rows unique declarations. Every phase records its scope.
- """
- import argparse
- import csv
- import hashlib
- import json
- import math
- import os
- import platform
- import resource
- import sqlite3
- import statistics
- import sys
- import time
- from pathlib import Path
- ROOT=Path(__file__).resolve().parents[1]
- sys.path.insert(0,str(ROOT/'tools-service'))
- def rss_mb():
- value=resource.getrusage(resource.RUSAGE_SELF).ru_maxrss
- return round(value/(1024**2 if sys.platform=='darwin' else 1024),2)
- def disk_bytes(folder):return sum(p.stat().st_size for p in folder.rglob('*') if p.is_file())
- def timed_query(conn,sql,args=(),repeats=7):
- timings=[];values=[]
- for _ in range(repeats):
- start=time.perf_counter(); values=[tuple(row) for row in conn.execute(sql,args)];timings.append((time.perf_counter()-start)*1000)
- return dict(sql=sql,parameters=list(args),plan=[r[3] for r in conn.execute('EXPLAIN QUERY PLAN '+sql,args)],
- first_ms=round(timings[0],3),p50_ms=round(statistics.median(timings),3),
- p95_ms=round(sorted(timings)[math.ceil(.95*len(timings))-1],3),rows=len(values),
- result_sha256=hashlib.sha256(json.dumps(values,default=str).encode()).hexdigest())
- def write_csv(path,rows,model_count=None):
- model_count = model_count or max(1,rows//4)
- with path.open('w',encoding='utf-8',newline='') as handle:
- w=csv.writer(handle);w.writerow(['I_E_FLAG','HS_CODE','YEAR','WEEK','MONORSUM_USD','COUNT','G_MODEL','ORIGIN_COUNTRY','TRADE_AREA_CODE','TRADE_COUNTRY','DISTINATE_PORT'])
- for i in range(rows):
- cents=i%1000+1
- w.writerow(['I' if i%5 else 'E',('8532241000','8542321010','8542321090','8542329010')[i%4],
- 2025+(i//52)%2,i%52+1,f'{cents//100}.{cents%100:02}',i%100+1,
- f'品牌:{("MURATA","SAMSUNG","MICRON","SK HYNIX")[i%4]};型号:TEST-{i%model_count:08}',
- f'X{i%197:03}',f'T{i%67:03}',f'D{i%89:03}',f'P{i%2003:04}'])
- def raw_phase(out,n):
- from toolsvc import objects
- from toolsvc.query_indexes import create_query_indexes
- source=out/'raw.csv';start=time.perf_counter();write_csv(source,n);generated=time.perf_counter()-start
- # Production importer, deliberately defer only business-index creation to obtain a baseline.
- original=objects.create_query_indexes;objects.create_query_indexes=lambda *a,**kw:None
- importer=objects.ObjectStore(out/'objects')
- start=time.perf_counter();columns,count=importer._index_raw('bench',source);ingest=time.perf_counter()-start
- objects.create_query_indexes=original
- conn=sqlite3.connect(out/'raw.sqlite'); conn.row_factory=None
- cases={
- 'hs_year_week_seek':('SELECT _row_index,MONORSUM_USD FROM raw WHERE HS_CODE=? AND _num_YEAR=? AND _num_WEEK=? AND _row_index>? ORDER BY _row_index LIMIT 50',('8532241000','2025','1',int(n*.9))),
- 'year_week_seek':('SELECT _row_index,HS_CODE FROM raw WHERE _num_YEAR=? AND _num_WEEK=? AND _row_index>? ORDER BY _row_index LIMIT 50',('2025','1',int(n*.9))),
- 'deep_offset':('SELECT _row_index FROM raw ORDER BY _row_index LIMIT 50 OFFSET ?',(int(n*.9),)),
- 'deep_keyset':('SELECT _row_index FROM raw WHERE _row_index>=? ORDER BY _row_index LIMIT 50',(int(n*.9),)),
- }
- before={k:timed_query(conn,*v) for k,v in cases.items()}
- size_before=(out/'raw.sqlite').stat().st_size
- start=time.perf_counter();indexes=create_query_indexes(conn,'raw',[r[1] for r in conn.execute('PRAGMA table_info(raw)')],raw=True);conn.execute('ANALYZE');conn.commit();index_seconds=time.perf_counter()-start
- after={k:timed_query(conn,*v) for k,v in cases.items()}
- assert all(before[k]['result_sha256']==after[k]['result_sha256'] for k in cases)
- # Register the existing fixture without materializing the entire file in HTTP memory.
- from toolsvc.objects import store,now
- from toolsvc.db import database
- ws=store.create_workspace('query benchmark')
- with database.transaction() as app:
- app.execute('INSERT INTO objects(id,workspace_id,name,kind,media_type,path,sha256,bytes,rows,columns_json,producer,parents_json,meta_json,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
- ('bench_raw',ws['id'],'raw.csv','raw','text/csv',str(source),'sha256:synthetic-v1',source.stat().st_size,n,json.dumps(columns),'benchmark','[]',json.dumps({'sqlite_path':str(out/'raw.sqlite'),'lineage':{'rules_hash':'synthetic-query-fixture-v1','params_hash':str(n)}}),now()))
- app.execute('INSERT INTO tables VALUES(?,?,?,?,?)',('bench_raw','raw','raw',n,json.dumps(columns)))
- from toolsvc.readers import read_rows, QueryTimeout
- start=time.perf_counter();page=read_rows('bench_raw',limit=50,use_cache=False);page_ms=(time.perf_counter()-start)*1000
- assert page['total']==n and page['total_exact'] and len(page['rows'])==50
- first=read_rows('bench_raw',limit=50);second=read_rows('bench_raw',limit=50)
- assert second['cache_hit']
- from toolsvc.config import settings
- deadline_before=settings.query_timeout_ms;settings.query_timeout_ms=100
- start=time.perf_counter()
- try:
- read_rows('bench_raw',aggregate={'group_by':['G_MODEL'],'metrics':[dict(column='MONORSUM_USD',fn='sum')]},limit=20,use_cache=False)
- timeout='completed'
- except QueryTimeout:timeout='interrupted_as_expected'
- finally:settings.query_timeout_ms=deadline_before
- conn.close()
- return dict(scope='production _index_raw streaming path; excludes HTTP multipart buffering and business classification',rows=count,
- declared_distinct_models=n//4,distinct_declarations=n//4,generate_seconds=round(generated,3),indexer_without_business_indexes_seconds=round(ingest,3),
- business_index_seconds=round(index_seconds,3),business_indexes=indexes,sqlite_before_bytes=size_before,sqlite_after_bytes=(out/'raw.sqlite').stat().st_size,
- before=before,after=after,public_metadata_page_ms=round(page_ms,3),deadline_100ms_result=timeout,
- deadline_elapsed_ms=round((time.perf_counter()-start)*1000,3),cache_second_hit=second['cache_hit'])
- def pipeline_phase(out,n):
- from toolsvc.customs import CustomsProcessRequest,CustomsOptions,load_processor
- source=out/'pipeline.csv';write_csv(source,n,model_count=n)
- start=time.perf_counter()
- result=load_processor().process(CustomsProcessRequest(str(source),str(out/'pipeline'),options=CustomsOptions(directions=['I','E'],cutoff_date='2026-12-31')))
- elapsed=time.perf_counter()-start
- from toolsvc.objects import store
- ws=store.create_workspace('pipeline lineage benchmark');parent=store.register_upload(ws['id'],'pipeline.csv',source.read_bytes())
- assert parent['sha256'].removeprefix('sha256:')==result.lineage['input_sha256']
- start=time.perf_counter();registered=store.register_processed(ws['id'],parent['id'],'result',out/'pipeline',result.to_dict());lineage=time.perf_counter()-start
- with sqlite3.connect(out/'pipeline/working.sqlite') as verify:
- distinct=verify.execute('SELECT COUNT(DISTINCT raw_model) FROM audit').fetchone()[0]
- return dict(scope='full actual rules+parser+ingest+original 23 reports+2 query summaries; lineage registration separately timed',
- rows=n,distinct_declarations=distinct,seconds=round(elapsed,3),stats=result.stats,lineage_seconds=round(lineage,3),result_rows=registered['rows'],
- workbook_sheets=23,query_tables=2,output_bytes=disk_bytes(out/'pipeline'))
- def monthly_phase(out,n):
- from toolsvc.customs.engine.storage import DIMENSIONS,connect
- from toolsvc.customs.engine.query_summaries import build_query_summaries
- from toolsvc.customs.engine.numbers import StrictSum
- from toolsvc.query_precision import register_precision
- db=connect(out/'monthly.sqlite')
- db.execute('CREATE TABLE monthly('+','.join(c+' TEXT' for c in DIMENSIONS)+',amount TEXT,quantity TEXT,n INTEGER)')
- insert='INSERT INTO monthly VALUES('+','.join('?' for _ in range(len(DIMENSIONS)+3))+')'
- start=time.perf_counter();batch=[]
- for i in range(n):
- m=(i//4)%24; cents=i%1000+1; cap=i%4==0
- values=['I' if i%5 else 'E',str(2025+m//12),f'{2025+m//12}-{m%12+1:02}',
- '电容器' if cap else '存储器','MLCC' if cap else 'DRAM','' if cap else 'DDR','' if cap else 'DDR4',
- ('MURATA','SAMSUNG','MICRON','SK HYNIX')[i%4],f'TEST-{i%max(1,n//4):08}', '日本' if cap else '韩国',
- f'应用{i%9}',f'产国{i%197}',f'贸国{i%67}',f'启运{i%89}',f'港{i%2003}',f'港国{i%61}',str(int(not cap)),
- None if i%100003==0 else f'{cents//100}.{cents%100:02}',str(i%100+1),1]
- batch.append(values)
- if len(batch)>=5000:db.executemany(insert,batch);batch.clear()
- if batch:db.executemany(insert,batch)
- db.commit();load=time.perf_counter()-start
- cases={
- 'class_year':('SELECT c2,dsum(amount),dsum(quantity) FROM monthly WHERE direction=? AND c1=? AND year=? GROUP BY c2',('I','存储器','2025')),
- 'one_model_timeline':('SELECT month,dsum(amount),dsum(quantity) FROM monthly WHERE direction=? AND brand=? AND model=? GROUP BY month',('I','SAMSUNG','TEST-00000001')),
- 'focus':('SELECT brand,dsum(amount) FROM monthly WHERE direction=? AND year=? AND c1=? AND c3=? GROUP BY brand',('I','2025','存储器','DDR')),
- }
- before={k:timed_query(db,*v,repeats=3) for k,v in cases.items()}
- start=time.perf_counter()
- for suffix,keys in [('class','direction,c1,c2,c3,c4,year'),('model','direction,brand,model,month'),('focus','direction,year,c1,c3,app,brand_country,brand')]:db.execute(f'CREATE INDEX idx_{suffix} ON monthly({keys})')
- db.execute('ANALYZE');db.commit();index_seconds=time.perf_counter()-start
- after={k:timed_query(db,*v,repeats=3) for k,v in cases.items()}
- assert all(before[k]['result_sha256']==after[k]['result_sha256'] for k in cases)
- from toolsvc.query_indexes import create_monthly_indexes
- start=time.perf_counter();create_monthly_indexes(db);db.commit();covering_seconds=time.perf_counter()-start
- final_focus=timed_query(db,*cases['focus'],repeats=3)
- assert final_focus['result_sha256']==after['focus']['result_sha256']
- target=out/'summaries';target.mkdir();sqlite3.connect(target/'stat.sqlite').close()
- start=time.perf_counter();sheets=build_query_summaries(db,target);summary_seconds=time.perf_counter()-start
- stat=sqlite3.connect(target/'stat.sqlite');register_precision(stat);stat.create_aggregate('dsum',1,StrictSum)
- expected=tuple(db.execute('SELECT dsum(amount),dsum(quantity),SUM(n) FROM monthly').fetchone())
- totals={}
- for name in ('q_class_month','q_model_month'):
- totals[name]=stat.execute(f'SELECT dsum("实际金额(美元)"),dsum("成交数量"),SUM(CAST("记录数" AS INTEGER)) FROM {name}').fetchone()
- assert totals[name]==expected
- queries={
- 'class_timeline':('SELECT "年度","月度","实际金额(美元)" FROM q_class_month WHERE "进/出口"=? AND "二级类目"=? ORDER BY "_num_年度","_num_月度",_row_index',('I','DRAM')),
- 'model_timeline':('SELECT "年度","月度","实际金额(美元)" FROM q_model_month WHERE "进/出口"=? AND "品牌"=? AND "型号"=? ORDER BY "_num_年度","_num_月度",_row_index',('I','SAMSUNG','TEST-00000001')),
- 'month_top50':('SELECT "品牌","型号","实际金额(美元)" FROM q_model_month WHERE "进/出口"=? AND "_num_年度"=? AND "_num_月度"=? AND "二级类目"=? ORDER BY "实际金额(美元)" COLLATE DECIMAL_VALUE DESC,_row_index ASC LIMIT 50',('I','2025','1','DRAM')),
- }
- summary_queries={k:timed_query(stat,*v) for k,v in queries.items()}
- counts={s.sheet:s.rows for s in sheets}
- stat.close();db.close()
- return dict(scope='4m high-cardinality parsed monthly SQL fixture, NOT a 4m business parser/pipeline run',rows=n,
- model_cardinality=n//4,monthly_rows=n,load_seconds=round(load,3),existing_three_index_seconds=round(index_seconds,3),
- before=before,after=after,covering_focus=final_focus,covering_index_build_seconds=round(covering_seconds,3),query_summary_build_seconds=round(summary_seconds,3),summary_row_counts=counts,
- summary_queries=summary_queries,exact_summary_totals=totals,missing_amount_policy='global sum is NULL due to deliberate missing members',
- disk_bytes=disk_bytes(out))
- def main():
- parser=argparse.ArgumentParser();parser.add_argument('--rows',type=int,default=4000000);parser.add_argument('--pipeline-rows',type=int,default=100000)
- parser.add_argument('--out',type=Path,default=ROOT/'.run'/('bigdata-'+time.strftime('%Y%m%d-%H%M%S')))
- parser.add_argument('--phase',choices=['all','raw','pipeline','monthly'],default='all');args=parser.parse_args()
- args.out=args.out.resolve();args.out.mkdir(parents=True,exist_ok=True)
- os.environ['CDA_DATA_DIR']=str(args.out/'service-data')
- result=dict(platform=platform.platform(),python=platform.python_version(),sqlite=sqlite3.sqlite_version,os_cache='not flushed; first connection and warm repeats; not cold-cache evidence',rows=args.rows,phases={})
- if args.phase=='all':
- import subprocess
- for phase in ('raw','pipeline','monthly'):
- print('starting '+phase,flush=True)
- subprocess.run([sys.executable,__file__,'--phase',phase,'--rows',str(args.rows),'--pipeline-rows',str(args.pipeline_rows),'--out',str(args.out/phase)],check=True)
- result['phases'][phase]=json.loads((args.out/phase/'report.json').read_text())
- (args.out/'report.json').write_text(json.dumps(result,ensure_ascii=False,indent=2))
- else:
- fn={'raw':raw_phase,'pipeline':pipeline_phase,'monthly':monthly_phase}[args.phase]
- start=time.perf_counter();result['phases'][args.phase]=fn(args.out,args.pipeline_rows if args.phase=='pipeline' else args.rows)
- result.update(phase_wall_seconds=round(time.perf_counter()-start,3),peak_rss_mb=rss_mb(),total_disk_bytes=disk_bytes(args.out))
- (args.out/'report.json').write_text(json.dumps(result,ensure_ascii=False,indent=2))
- print(json.dumps(dict(phase=args.phase,wall_seconds=result['phase_wall_seconds'],peak_rss_mb=result['peak_rss_mb']),ensure_ascii=False),flush=True)
- print('report: '+str(args.out/'report.json'),flush=True)
- if __name__=='__main__':main()
|