LargeSynopticSurveyTelescop
QservFall16LargeScale
VaikunthThukral
DMTR-16
LatestRevision:2017-05-09
Abstract
Thisdocumentcontainsresultsofthelargescaletests
runonCC-IN2P3clusterduringFebruary-March2017.
Forcomparisons,thepreviouslargescaletestcanbefoundDMTR-13.
LARGESYNOPTICSURVEYTELESCOPE
LARGESYNOPTICSURVEYTELESCOPE
QservTestFall2016DMTR-16LatestRevision2017-05-09
Contents
1DatasetInformation1
2Hardware1
3Testingmethodologyandsummary2
3.1Shortqueries..........................2
3.2Fulltablescans,singlequeryatatime...................2
3.3Fulltablejoins,singlequeryatatime...................3
3.4Concurrentscansandstresstest.....................3
3.5Typicalloadtest60LV+10HV(+10%ifpossible)............3.....
3.6Heavyloadtest100LV+20HV(+10%ifpossible)...........6.....
4NotesandObservations7
5QueryTemplatesUsed7
ii
LARGESYNOPTICSURVEYTELESCOPE
QservTestFall2016DMTR-16LatestRevision2017-05-09
QservFall16LargeScaleTests/KPMs
1DatasetInformation
TableRowCount.MYDsize[TB].MYIsize[TB]
Object3,777,968,8804.60.1
Source69,744,689,07233.6
ForcedSource344,034,271,38011.08.7
TotalMySQLdatadirsize:62.4TB
DR1numbersareavailableinDocument-16168under“DataReleases”
ObjectandSourcearenowat~20%ofDR1levelandForcedSource
DR1.
2Hardware
–DELLPowerEdgeR620(DellSpecSheet)fornodes1-25
–DELLPowerEdgeR630(DellSpecSheet)fornodes26-50
•2xProcessorsIntelXeonE5-2603v2@1.80Ghz4core
•10MBcache,6.4GT/s,80W
•Memory16GBDDR-3@1600MHz(2x8GB)
•2xharddrive250GBSATA7200Rpm2,5”-hotplug=>OS
•8xharddrive1TBNearlineSAS6Gbps7200Rpm2,5”
•hotplug=>DATA
•1xcardRAIDH710pwith1GBnvram
1
LARGESYNOPTICSURVEYTELESCOPE
QservTestFall2016DMTR-16LatestRevision2017-05-09
•1xcard1GbE4portsBroadcom®5720Base-T
•1xcardiDRAC7Enterprise
3Testingmethodologyandsummary
Theclusterisdividedintotwosetsof25nodeseach,and
datasetloadedin.TheseKPMtestswereperformedonthesecond
ccqserv125
-
ccqserv149
(DR1 is expected to be on 92 nodes)
Queries are selected from a query pool that is divided into
age patterns as well as expected support for baseline requirements.
number of simultaneous queries to be tested are spawned,
tion to the qserv proxy and repeatedly picking random queries
sleeping when required.
The actual program that we used to
runQueries.py
the testing can be
3.1 Short queries
•Single object selection by ID: 0.18 sec
SELECT
*
FROM
Object
WHERE
deepSourceId = 1696835327229975
•Small spatial area selection from Object: 0.52 sec
SELECT COUNT
( *
F
)
ROM
Object
WHERE
qserv_areaspec_box (42.247
38.874077, 42.273855, 39.034748)
3.2 Full table scans, single query at a time
•Object: ~7 min
SELECT
ra , decl , u_psfFlux , g_psfFlux
FROM
Object
, r_psfFlu
WHERE
y_shapeIxx
BETWEEN
20
AND
20.2
2
LARGE SYNOPTIC SURVEY TELESCOPE
Qserv Test Fall 2016
DMTR-16
Latest Revision 2017-05-09
•Source: ~41 min
SELECT COUNT
( *
F
)
ROM
Source
WHERE
flux \ _sinc
BETWEEN
1
AND
1.1
•ForcedSource: ~26 min
SELECT COUNT
( *
F
)
ROM
ForcedSource
WHERE
psfFlux
BETWEEN
0.
A
1
ND
0.2
3.3 Full table joins, single query at a time
•Object x Source: ~564min
for(See
details)
section
SELECT
o. deepSourceId , s . objectId , s . id , o. ra ,
FROM
Object o, Source
WHERE
o . deepSourceId=s . objectId
AND
s. flux_sinc
BETWEEN
0.
A
3
ND
0.31
•Object x ForcedSource: ~38 min
SELECT
o.deepSourceId ,
FROM
f .Object
psfFlux
o, ForcedSource f
WHERE
o . deepSourceId= f . deepSourceId
AND
f . psfFlux
BETWEEN
0.13
AND
0.14
3.4 Concurrent scans and stress test
•2 Object scans: ~8 min
•5 Object scans: ~8 min (this shows that shared scans implemented
tended)
•Able to run up to 35 (30 on Object, 3 on Source and 2 on ForcedSource)
— Object scans < 1h and Source scans < 1.5h
•Attempt to run 50 scans causes the proxy to fail, solution
ployment in the future
3.5Typicalloadtest60LV+10HV(+10%ifpossible)
•60lowvolumeand10highvolumequeries(4scansforObject,
forForcedSource,2Object-Sourcejoins,1Object-ForcedSource
3
LARGESYNOPTICSURVEYTELESCOPE
QservTestFall2016DMTR-16LatestRevision2017-05-09
query),allrunningsimultaneouslywithappropriate
themixweareaimingfor.Wealsointroduceanewmetric
mance,QueryThroughPut,orQTP
•During24hourswecompleted:
–571,029LowVolumequeriesfinished—Baseline:~10sec
in24h
*AchievedLowVolumeQTP:396queries/minute
–108Objectscans—Baseline:~1hperquery,or96in
–2Sourcescans—Baseline:~12hperquery,or2in24h
–2ForcedSourcescans—Baseline:~12hperquery,or
–4Object-Sourcejoins—Baseline:~12hperqueryor
–2Object-ForcedSourcejoins—Baseline:~12hperquery
–27NearNeighborqueries—Baseline:~1hperquery,
*AchievedHighVolumeQTP:6queries/hour
•Averageoutput:
–OverallsizeofLowVolumeresultswas~70GB:128kB/query
–OverallsizeofHighVolumeresultswas~7GB:48MB/query
•Averagetimes:
–LowVolumequeries3.23sec/query—Baseline:shouldbeunder10sec.1.
–Object15.15scansmin/query—Baseline:shouldbeunder1hour
–Sourcescans2.7hr/query—Baseline:shouldbeunder12hours
–ForcedSourcescans2.7hr/query—Baseline:shouldbeunder12hours
–Object-Sourcejoins2.7hr/query—Baseline:shouldbeunder12hours
–Object-ForcedSourcejoins2.7hr/query—Baseline:shouldbeunder12
–NearNeighborqueries16.6min/query—Baseline:shouldbeunder12hours.
Fig.2.
•Observations:
4
LARGESYNOPTICSURVEYTELESCOPE
QservTestFall2016DMTR-16LatestRevision2017-05-09
Figure1:HistogramforallLowVolumequerytimes,notethelog
Figure2:HistogramforallHighVolumequerytimes,notetheclear
Sourcescans.
5
LARGESYNOPTICSURVEYTELESCOPE
QservTestFall2016DMTR-16LatestRevision2017-05-09
–Testswereabletorunexactly10%fasterthantheattempted
queriesandObjectscans(duetotheshorttimeframe
–ScanschedulingimplementedinW16workswithscans
inapproximatelythesameamountoftime
*Forcorrectscanschedulingandqueryplanning,mysql
erated.Futureworkforthisisplanned
*Issueswereuncoveredwithreturninglargeresult
thisintoaccount
*Duetoorder-of-magnitudeincreaseinoutputsize,
umequerieshasincreasedduetotestsrunningfrom
*Fortheseissues,seeDM-9757,DM-10360,andDM-10366.
3.6Heavyloadtest100LV+20HV(+10%ifpossible)
•100lowvolumeand20highvolumequeries(8scansfor
scansforForcedSource,4Object-Sourcejoins,2Object-ForcedSource
borqueries),allrunningsimultaneouslywithappropriate
enforcethemixweareaimingfor
•During24hourswecompleted:
–814,989LowVolumequeries
–129Objectscans
–4Sourcescans
–4ForcedSourcescans
–8Object-Sourcejoins
–2Object-ForcedSourcejoins
–54NearNeighborqueries
•AverageOutput:
–OverallsizeofLowVolumeresultswas~130GB:160kB/query
–OverallsizeofHighVolumeresultswas~8GB:38MB/query
•Averagetimes:
6
LARGESYNOPTICSURVEYTELESCOPE
QservTestFall2016DMTR-16LatestRevision2017-05-09
–LowVolumequeries9.98sec/query
–Object24scansmin/query
–Sourcescans4.44hr/query
–ForcedSourcescans4.44hr/query
–Object-Sourcejoins4.44hr/query
–Object-ForcedSourcejoins4,44hr/query
–NearNeighborqueries24min/query
•Observations:
–DidnotfinishexpectednumberofObjectscans
–AveragesizeofHighVolumequeryresultslowereddue
scans
–LowVolumequeriesstillfinishedunderthebaseline
4NotesandObservations
•Concurrencymaintained:WithdoublethedatafromS15wesuccessfully
ousqueriesuptoandmorethanthebaselinerequirements
•RobustnessAlthoughimproved:thetestswererunfor24hoursinS15
havebeenperformedmultipletimesandcontinuously
servicesfailingorneedingrestarts
•SharedScanimplementation:Qservisabletohandlesuccessivelylarger
onthesametableandperformthemforasimilarcost/time.
5QueryTemplatesUsed
Theactualquerypoolusedinthesetestsisavailablefromgitrepositoryassociated
thisdocument.Asampleofthequerytypesusedineachcategory
•Trivialquerythatretrievesonerow,usingindex
7
LARGESYNOPTICSURVEYTELESCOPE
QservTestFall2016DMTR-16LatestRevision2017-05-09
SELECT*FROMObjectWHEREobjectId=<objId>
•Counts
SELECTCOUNT(*F)ROMObject
SELECTCOUNT(*F)ROMSource
SELECTCOUNT(*F)ROMForcedSource
•Spatiallyrestrictedquery,smallareaofsky,should
<100)
SELECTCOUNT(*)
FROMObject
WHEREra_PSBETWEEN1AND2
ANDdecl_PSBETWEEN3AND4
•Fulltablescan,usesomecolumninWHEREthatisnotindexes,
ofresultsreturnedissane(egthousands,notmillions)
SELECTobjectId,ra_PS,de\c_PS,<fewothercolumns>
FROMObject
WHEREfluxToAbMag(iFlux_PS)−fluxToAbMag(zFlux_PS)>4
•Aggregation
SELECTCOUNT(*A)Sn,
AVG(ra_PS),
AVG(decl_PS),chunkId
FROMObject
GROUPBYchunkId
•Nearneighbor
SELECTCOUNT(*)
FROMObjecto1,Objecto2
WHEREqserv_areaspec_box(−5−,5,5,−5)
ANDscisql_angSep(o1.ra_PS,o1.decl_PS,o2.ra
8
LARGESYNOPTICSURVEYTELESCOPE
QservTestFall2016DMTR-16LatestRevision2017-05-09
•Joins
SELECTo.objectId,s.sourceId,ra_PS,decl_PS,
FROMObject
JOISNOURCEUSIN(GobjectId)
WHEREfluxToAbMag(iFlux_PS)−fluxToAbMag(zFlux_PS)>4
AND<somerestrictionfromsourcetable>
[1][DMTR-13],Becla,J.,2015,QservSummer15LargeScaleTests,DMTR-13,URLhttps://ls.
st/DMTR-13
[2][Document-16168],LSSTSystemsEngineering,2014,LSSTKeySystemParameters
mary,Document-16168,URLhttps://ls.st/Document-16168
9
Back to top