benchmark_queries.py 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203
  1. """Reproducible high-cardinality SQLite benchmark. Synthetic, never real-data evidence.
  2. .venv/bin/python scripts/benchmark_queries.py --rows 4000000 --pipeline-rows 100000
  3. 4m raw CSV uses the real upload indexer; high-cardinality monthly is an explicit
  4. SQL fixture, not the result of classifying all 4m rows. Full pipeline is measured
  5. separately on --pipeline-rows unique declarations. Every phase records its scope.
  6. """
  7. import argparse
  8. import csv
  9. import hashlib
  10. import json
  11. import math
  12. import os
  13. import platform
  14. import resource
  15. import sqlite3
  16. import statistics
  17. import sys
  18. import time
  19. from pathlib import Path
  20. ROOT=Path(__file__).resolve().parents[1]
  21. sys.path.insert(0,str(ROOT/'tools-service'))
  22. def rss_mb():
  23. value=resource.getrusage(resource.RUSAGE_SELF).ru_maxrss
  24. return round(value/(1024**2 if sys.platform=='darwin' else 1024),2)
  25. def disk_bytes(folder):return sum(p.stat().st_size for p in folder.rglob('*') if p.is_file())
  26. def timed_query(conn,sql,args=(),repeats=7):
  27. timings=[];values=[]
  28. for _ in range(repeats):
  29. start=time.perf_counter(); values=[tuple(row) for row in conn.execute(sql,args)];timings.append((time.perf_counter()-start)*1000)
  30. return dict(sql=sql,parameters=list(args),plan=[r[3] for r in conn.execute('EXPLAIN QUERY PLAN '+sql,args)],
  31. first_ms=round(timings[0],3),p50_ms=round(statistics.median(timings),3),
  32. p95_ms=round(sorted(timings)[math.ceil(.95*len(timings))-1],3),rows=len(values),
  33. result_sha256=hashlib.sha256(json.dumps(values,default=str).encode()).hexdigest())
  34. def write_csv(path,rows,model_count=None):
  35. model_count = model_count or max(1,rows//4)
  36. with path.open('w',encoding='utf-8',newline='') as handle:
  37. 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'])
  38. for i in range(rows):
  39. cents=i%1000+1
  40. w.writerow(['I' if i%5 else 'E',('8532241000','8542321010','8542321090','8542329010')[i%4],
  41. 2025+(i//52)%2,i%52+1,f'{cents//100}.{cents%100:02}',i%100+1,
  42. f'品牌:{("MURATA","SAMSUNG","MICRON","SK HYNIX")[i%4]};型号:TEST-{i%model_count:08}',
  43. f'X{i%197:03}',f'T{i%67:03}',f'D{i%89:03}',f'P{i%2003:04}'])
  44. def raw_phase(out,n):
  45. from toolsvc import objects
  46. from toolsvc.query_indexes import create_query_indexes
  47. source=out/'raw.csv';start=time.perf_counter();write_csv(source,n);generated=time.perf_counter()-start
  48. # Production importer, deliberately defer only business-index creation to obtain a baseline.
  49. original=objects.create_query_indexes;objects.create_query_indexes=lambda *a,**kw:None
  50. importer=objects.ObjectStore(out/'objects')
  51. start=time.perf_counter();columns,count=importer._index_raw('bench',source);ingest=time.perf_counter()-start
  52. objects.create_query_indexes=original
  53. conn=sqlite3.connect(out/'raw.sqlite'); conn.row_factory=None
  54. cases={
  55. '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))),
  56. '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))),
  57. 'deep_offset':('SELECT _row_index FROM raw ORDER BY _row_index LIMIT 50 OFFSET ?',(int(n*.9),)),
  58. 'deep_keyset':('SELECT _row_index FROM raw WHERE _row_index>=? ORDER BY _row_index LIMIT 50',(int(n*.9),)),
  59. }
  60. before={k:timed_query(conn,*v) for k,v in cases.items()}
  61. size_before=(out/'raw.sqlite').stat().st_size
  62. 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
  63. after={k:timed_query(conn,*v) for k,v in cases.items()}
  64. assert all(before[k]['result_sha256']==after[k]['result_sha256'] for k in cases)
  65. # Register the existing fixture without materializing the entire file in HTTP memory.
  66. from toolsvc.objects import store,now
  67. from toolsvc.db import database
  68. ws=store.create_workspace('query benchmark')
  69. with database.transaction() as app:
  70. 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(?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
  71. ('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()))
  72. app.execute('INSERT INTO tables VALUES(?,?,?,?,?)',('bench_raw','raw','raw',n,json.dumps(columns)))
  73. from toolsvc.readers import read_rows, QueryTimeout
  74. start=time.perf_counter();page=read_rows('bench_raw',limit=50,use_cache=False);page_ms=(time.perf_counter()-start)*1000
  75. assert page['total']==n and page['total_exact'] and len(page['rows'])==50
  76. first=read_rows('bench_raw',limit=50);second=read_rows('bench_raw',limit=50)
  77. assert second['cache_hit']
  78. from toolsvc.config import settings
  79. deadline_before=settings.query_timeout_ms;settings.query_timeout_ms=100
  80. start=time.perf_counter()
  81. try:
  82. read_rows('bench_raw',aggregate={'group_by':['G_MODEL'],'metrics':[dict(column='MONORSUM_USD',fn='sum')]},limit=20,use_cache=False)
  83. timeout='completed'
  84. except QueryTimeout:timeout='interrupted_as_expected'
  85. finally:settings.query_timeout_ms=deadline_before
  86. conn.close()
  87. return dict(scope='production _index_raw streaming path; excludes HTTP multipart buffering and business classification',rows=count,
  88. declared_distinct_models=n//4,distinct_declarations=n//4,generate_seconds=round(generated,3),indexer_without_business_indexes_seconds=round(ingest,3),
  89. business_index_seconds=round(index_seconds,3),business_indexes=indexes,sqlite_before_bytes=size_before,sqlite_after_bytes=(out/'raw.sqlite').stat().st_size,
  90. before=before,after=after,public_metadata_page_ms=round(page_ms,3),deadline_100ms_result=timeout,
  91. deadline_elapsed_ms=round((time.perf_counter()-start)*1000,3),cache_second_hit=second['cache_hit'])
  92. def pipeline_phase(out,n):
  93. from toolsvc.customs import CustomsProcessRequest,CustomsOptions,load_processor
  94. source=out/'pipeline.csv';write_csv(source,n,model_count=n)
  95. start=time.perf_counter()
  96. result=load_processor().process(CustomsProcessRequest(str(source),str(out/'pipeline'),options=CustomsOptions(directions=['I','E'],cutoff_date='2026-12-31')))
  97. elapsed=time.perf_counter()-start
  98. from toolsvc.objects import store
  99. ws=store.create_workspace('pipeline lineage benchmark');parent=store.register_upload(ws['id'],'pipeline.csv',source.read_bytes())
  100. assert parent['sha256'].removeprefix('sha256:')==result.lineage['input_sha256']
  101. start=time.perf_counter();registered=store.register_processed(ws['id'],parent['id'],'result',out/'pipeline',result.to_dict());lineage=time.perf_counter()-start
  102. with sqlite3.connect(out/'pipeline/working.sqlite') as verify:
  103. distinct=verify.execute('SELECT COUNT(DISTINCT raw_model) FROM audit').fetchone()[0]
  104. return dict(scope='full actual rules+parser+ingest+original 23 reports+2 query summaries; lineage registration separately timed',
  105. rows=n,distinct_declarations=distinct,seconds=round(elapsed,3),stats=result.stats,lineage_seconds=round(lineage,3),result_rows=registered['rows'],
  106. workbook_sheets=23,query_tables=2,output_bytes=disk_bytes(out/'pipeline'))
  107. def monthly_phase(out,n):
  108. from toolsvc.customs.engine.storage import DIMENSIONS,connect
  109. from toolsvc.customs.engine.query_summaries import build_query_summaries
  110. from toolsvc.customs.engine.numbers import StrictSum
  111. from toolsvc.query_precision import register_precision
  112. db=connect(out/'monthly.sqlite')
  113. db.execute('CREATE TABLE monthly('+','.join(c+' TEXT' for c in DIMENSIONS)+',amount TEXT,quantity TEXT,n INTEGER)')
  114. insert='INSERT INTO monthly VALUES('+','.join('?' for _ in range(len(DIMENSIONS)+3))+')'
  115. start=time.perf_counter();batch=[]
  116. for i in range(n):
  117. m=(i//4)%24; cents=i%1000+1; cap=i%4==0
  118. values=['I' if i%5 else 'E',str(2025+m//12),f'{2025+m//12}-{m%12+1:02}',
  119. '电容器' if cap else '存储器','MLCC' if cap else 'DRAM','' if cap else 'DDR','' if cap else 'DDR4',
  120. ('MURATA','SAMSUNG','MICRON','SK HYNIX')[i%4],f'TEST-{i%max(1,n//4):08}', '日本' if cap else '韩国',
  121. f'应用{i%9}',f'产国{i%197}',f'贸国{i%67}',f'启运{i%89}',f'港{i%2003}',f'港国{i%61}',str(int(not cap)),
  122. None if i%100003==0 else f'{cents//100}.{cents%100:02}',str(i%100+1),1]
  123. batch.append(values)
  124. if len(batch)>=5000:db.executemany(insert,batch);batch.clear()
  125. if batch:db.executemany(insert,batch)
  126. db.commit();load=time.perf_counter()-start
  127. cases={
  128. 'class_year':('SELECT c2,dsum(amount),dsum(quantity) FROM monthly WHERE direction=? AND c1=? AND year=? GROUP BY c2',('I','存储器','2025')),
  129. 'one_model_timeline':('SELECT month,dsum(amount),dsum(quantity) FROM monthly WHERE direction=? AND brand=? AND model=? GROUP BY month',('I','SAMSUNG','TEST-00000001')),
  130. 'focus':('SELECT brand,dsum(amount) FROM monthly WHERE direction=? AND year=? AND c1=? AND c3=? GROUP BY brand',('I','2025','存储器','DDR')),
  131. }
  132. before={k:timed_query(db,*v,repeats=3) for k,v in cases.items()}
  133. start=time.perf_counter()
  134. 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})')
  135. db.execute('ANALYZE');db.commit();index_seconds=time.perf_counter()-start
  136. after={k:timed_query(db,*v,repeats=3) for k,v in cases.items()}
  137. assert all(before[k]['result_sha256']==after[k]['result_sha256'] for k in cases)
  138. from toolsvc.query_indexes import create_monthly_indexes
  139. start=time.perf_counter();create_monthly_indexes(db);db.commit();covering_seconds=time.perf_counter()-start
  140. final_focus=timed_query(db,*cases['focus'],repeats=3)
  141. assert final_focus['result_sha256']==after['focus']['result_sha256']
  142. target=out/'summaries';target.mkdir();sqlite3.connect(target/'stat.sqlite').close()
  143. start=time.perf_counter();sheets=build_query_summaries(db,target);summary_seconds=time.perf_counter()-start
  144. stat=sqlite3.connect(target/'stat.sqlite');register_precision(stat);stat.create_aggregate('dsum',1,StrictSum)
  145. expected=tuple(db.execute('SELECT dsum(amount),dsum(quantity),SUM(n) FROM monthly').fetchone())
  146. totals={}
  147. for name in ('q_class_month','q_model_month'):
  148. totals[name]=stat.execute(f'SELECT dsum("实际金额(美元)"),dsum("成交数量"),SUM(CAST("记录数" AS INTEGER)) FROM {name}').fetchone()
  149. assert totals[name]==expected
  150. queries={
  151. 'class_timeline':('SELECT "年度","月度","实际金额(美元)" FROM q_class_month WHERE "进/出口"=? AND "二级类目"=? ORDER BY "_num_年度","_num_月度",_row_index',('I','DRAM')),
  152. 'model_timeline':('SELECT "年度","月度","实际金额(美元)" FROM q_model_month WHERE "进/出口"=? AND "品牌"=? AND "型号"=? ORDER BY "_num_年度","_num_月度",_row_index',('I','SAMSUNG','TEST-00000001')),
  153. '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')),
  154. }
  155. summary_queries={k:timed_query(stat,*v) for k,v in queries.items()}
  156. counts={s.sheet:s.rows for s in sheets}
  157. stat.close();db.close()
  158. return dict(scope='4m high-cardinality parsed monthly SQL fixture, NOT a 4m business parser/pipeline run',rows=n,
  159. model_cardinality=n//4,monthly_rows=n,load_seconds=round(load,3),existing_three_index_seconds=round(index_seconds,3),
  160. 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,
  161. summary_queries=summary_queries,exact_summary_totals=totals,missing_amount_policy='global sum is NULL due to deliberate missing members',
  162. disk_bytes=disk_bytes(out))
  163. def main():
  164. parser=argparse.ArgumentParser();parser.add_argument('--rows',type=int,default=4000000);parser.add_argument('--pipeline-rows',type=int,default=100000)
  165. parser.add_argument('--out',type=Path,default=ROOT/'.run'/('bigdata-'+time.strftime('%Y%m%d-%H%M%S')))
  166. parser.add_argument('--phase',choices=['all','raw','pipeline','monthly'],default='all');args=parser.parse_args()
  167. args.out=args.out.resolve();args.out.mkdir(parents=True,exist_ok=True)
  168. os.environ['CDA_DATA_DIR']=str(args.out/'service-data')
  169. 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={})
  170. if args.phase=='all':
  171. import subprocess
  172. for phase in ('raw','pipeline','monthly'):
  173. print('starting '+phase,flush=True)
  174. subprocess.run([sys.executable,__file__,'--phase',phase,'--rows',str(args.rows),'--pipeline-rows',str(args.pipeline_rows),'--out',str(args.out/phase)],check=True)
  175. result['phases'][phase]=json.loads((args.out/phase/'report.json').read_text())
  176. (args.out/'report.json').write_text(json.dumps(result,ensure_ascii=False,indent=2))
  177. else:
  178. fn={'raw':raw_phase,'pipeline':pipeline_phase,'monthly':monthly_phase}[args.phase]
  179. start=time.perf_counter();result['phases'][args.phase]=fn(args.out,args.pipeline_rows if args.phase=='pipeline' else args.rows)
  180. result.update(phase_wall_seconds=round(time.perf_counter()-start,3),peak_rss_mb=rss_mb(),total_disk_bytes=disk_bytes(args.out))
  181. (args.out/'report.json').write_text(json.dumps(result,ensure_ascii=False,indent=2))
  182. 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)
  183. print('report: '+str(args.out/'report.json'),flush=True)
  184. if __name__=='__main__':main()