LargeSynopticSurveyTelescop
    QservSummer15LargeScale
    JacekBecla
    DMTR-13
    LatestRevision:2015-08-27
    Abstract
    Thisdocumentcapturesinformationaboutthelargescale
    clusterduringSummer2015.
    Forcomparisons,thepreviouslargescaletestcanbefoundDMTR-12.
    LARGESYNOPTICSURVEYTELESCOPE
    LARGESYNOPTICSURVEYTELESCOPE
    QservTestSummer2015DMTR-13LatestRevision2015-08-27
    Contents
    1DataSet1
    2Hardware1
    3Timingsummary2
    3.1Shortqueries..........................2
    3.2Fulltablescans,singlequeryatatime..................2
    3.3Fulltablejoins,singlequeryatatime..................2
    3.4Concurrentscans.........................2
    3.5Heavyloadtest50LV+5HV......................2
    3.6Heavyloadtest100LV+10HV.....................3
    4NotesandObservations6
    5SampleQueries6
    6Rawoutputfromselectedindividualqueries8
    6.1Counts............................8
    6.2Short-runningqueries........................9
    6.3Fulltablescans..........................10
    6.4Joins.............................11
    6.5Nearneighbor..........................11
    6.6Sharedscans..........................12
    ii
    LARGESYNOPTICSURVEYTELESCOPE
    QservTestSummer2015DMTR-13LatestRevision2015-08-27
    QservSummer15LargeScaleTests
    1DataSet
    tablerowcount.MYDsize[TB].MYIsize[TB]
    Object1,889,695,6152.450.06
    Source34,886,017,76317.132.05
    ForcedSource172,081,115,270‘5.854.61
    TotalMySQLdatadirsize:33.2TB
    SoforObjectandSourceweareatthe~10%ofDR1level.We
    10%ofDR1.
    2Hardware
    •50nodes,DELLPowerEdgeR620
    •2xProcessorsIntelXeonE5-2603v2@1.80Ghz4core
    •10Mocache,6.4GT/s,80W
    •Memory16GBDDR-3@1600MHz(2x8GB)
    •2xharddrive250GBSATA7200Rpm2,5”-hotplug=>OS
    •8xharddrive1TBNearlineSAS6Gbps7200Rpm2,5”
    •hotplug=>DATA
    •1xcardRAIDH710pwith1GBnvram
    •1xcard1GbE4portsBroadcom®5720Base-T
    •1xcardiDRAC7Enterprise
    1
    LARGESYNOPTICSURVEYTELESCOPE
    QservTestSummer2015DMTR-13LatestRevision2015-08-27
    3Timingsummary
    Dataon24nodes(forcomparison,DR1isexpectedtobeon
    3.1Shortqueries
    •singleobjectselectionbyid:0.09sec
    •smallspatialareaselectionfromObject:0.33sec
    3.2Fulltablescans,singlequeryatatime
    •Object~4min
    •Source~18min
    •ForcedSource~15min
    3.3Fulltablejoins,singlequeryatatime
    •ObjectxSource:~23min
    •ObjectxForcedSource:~21min
    3.4Concurrentscans
    •2Objectscans~8min,5Objectscans~16-20min(thisshows
    hasissues,scheduledtobesolvedinW16)
    3.5Heavyloadtest50LV+5HV
    •50lowvolumeand5highvolumequeries(3scansforObject,
    Sourcejoins),allrunningsimultaneouslywithappropriate
    enforcethemixwewereaiming
    2
    LARGESYNOPTICSURVEYTELESCOPE
    QservTestSummer2015DMTR-13LatestRevision2015-08-27
    •During24hourswecompleted:
    –431,597lowvolumequeries(consistentwiththebaseline:
    432,000queriesin24h)
    –73Objectscans(consistentwithbaseline:~1hper
    –3Sourcescans(consistentwithbaseline:~8hperquery,
    –3Object-Sourcejoins(consistentwiththebaseline
    –overallsizeofresultswas6.5GB(~16KBperqueryon
    •Averagetimes:
    –lowvolumequeries:0.91sec(perbaseline,should
    –Objectscan:15min(perbaseline,shouldbeunder1
    –Sourcescan:56min(perbaseline,shouldbeunder8
    –Object-Sourcejoin57min(perbaseline,shouldbe
    •Observations:
    –ioboundduringthetimewhenscanshappenatthesame
    (~750MB/secseen)
    –theaggregateloadontheclusterisshowninFig1.
    3.6Heavyloadtest100LV+10HV
    •100lowvolumeand10highvolumequeries(6scansfor
    2Object-Sourcejoins),allrunningsimultaneously
    queriestoenforcethemixwewereaiming
    •During24hourswecompleted:
    –861,608lowvolumequeries
    –144Objectscans
    –6Sourcescans
    –8Object-Sourcejoins
    •Averagetimes:
    3
    LARGESYNOPTICSURVEYTELESCOPE
    QservTestSummer2015DMTR-13LatestRevision2015-08-27
    Figure1:Aggregateloadonclusterduringheavyloadtest
    4
    LARGESYNOPTICSURVEYTELESCOPE
    QservTestSummer2015DMTR-13LatestRevision2015-08-27
    –lowvolumequeries:5.1sec
    –Objectscan:22min
    –Sourcescan:1h33min
    –Object-Sourcejoin1h22min
    •Observations:
    –theaggregateloadontheclusterisshowninFig.2.
    •Notes:duringthelastpartofthattestwewerereloading
    whichwasimpactedhowtheaverageloadonthecluster
    Figure2:Aggregateloadonclusterduringheavyloadtest100LV
    5
    LARGESYNOPTICSURVEYTELESCOPE
    QservTestSummer2015DMTR-13LatestRevision2015-08-27
    4NotesandObservations
    •Concurrentlygreatlyimproved.Thiswastheveryfirsttimeweeversuccessfully
    than3-4simultaneousqueries(werunupto110).
    •Robustnessgreatlyimproved.Thiswastheveryfirsttimewerancontinuously
    anyfailurefor24hours(itcouldhaveranforlonger,
    •Latencyreduced100x.Inprevioustestsaveragetimetocomplete
    above1sec.Someofthelatestimprovementsinvolvereducing
    overheadofquerydispatchandsendingbackresultdata.
    visible,wewereabletodemonstrate90milisecresponse
    queries-100xbetterthanbefore.
    •Worktodo:
    –Whenwerun5fullscanqueries,somelowvolumequeries
    scheduledforalongtime(minutes),thisneedstobe
    balanceoutbecausefullscanqueriesendbeforeplanned
    time,whenlowvolumequeriescancatchup.
    –Single-tablescaredscansarenotworkingwell.This
    –Multi-nodesharedscansarenotworking.Wedidnot
    toimplementthisinW16
    –Thetestsrevealedproblemwithhandlinglargeresults
    aqueryinvolvesmulti-GBresults,ourmasternode
    amountofmemoryandCPU.(Someofthetestsweranproduced
    to46GBovertheperiodof30hours).Theuncovered
    FY16(DM-3495)
    5SampleQueries
    TheactualprogramthatweusedtodrivethetestingcanberunQueries.py
    Trivialquerythatretrievesonerow,usingindex
    SELECT*FROMObjectWHEREobjectId=<objId>
    6
    LARGESYNOPTICSURVEYTELESCOPE
    QservTestSummer2015DMTR-13LatestRevision2015-08-27
    Counts
    SELECTCOUNT(*) FROMObject
    SELECTCOUNT(*) FROMSource
    SELECTCOUNT(*) FROMForcedSource
    Spatiallyrestrictedquery,smallareaofsky,shouldreturn
    SELECTCOUNT(*)
    FROMObject
    WHEREra_PSBETWEEN1AND2
    ANDdecl_PSBETWEEN3AND4
    Fulltablescan,usesomecolumninWHEREthatisnotindexes,
    resultsreturnedissane(egthousands,notmillions)
    SELECTobjectId,ra_PS,decl_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)
    ANDqserv_angSep(o1.ra_PS,o1.decl_PS,o2.ra_PS,
    Joins
    SELECTo.objectId,s.sourceId,ra_PS,decl_PS,<f
    FROMObject
    7
    LARGESYNOPTICSURVEYTELESCOPE
    QservTestSummer2015DMTR-13LatestRevision2015-08-27
    JOISNOURCEUSIN(GobjectId)
    WHEREfluxToAbMag(iFlux_PS)−fluxToAbMag(zFlux_PS)>4
    AND<somerestrictionfromsourcetable>
    6Rawoutputfromselectedindividualqueries
    Numbersfor24hscalingtestsnotshownduetosizeofthe
    6.1Counts
    select count(*) from Object;
    +----------------+
    | SUM(QS1_COUNT) |
    +----------------+
    | 1889695615 |
    +----------------+
    1 row in set (47.75 sec)
     
    select count(*) from Source;
    +----------------+
    | SUM(QS1_COUNT) |
    +----------------+
    | 34886017763 |
    +----------------+
    1 row in set (40.99 sec)
    select count(*) from ForcedSource;
    +----------------+
    | SUM(QS1_COUNT) |
    +----------------+
    | 172081115270 |
    +----------------+
    1 row in set (48.33 sec)
    8
    LARGE SYNOPTIC SURVEY TELESCOPE
    Qserv Test Summer 2015
    DMTR-13
    Latest Revision 2015-08-27
    6.2 Short-running queries
    SELECT ra, decl FROM Object WHERE deepSourceId = 3306154155315676;
    +------------------+-------------------+
    | ra | decl |
    +------------------+-------------------+
    | 346.444574155259 | -20.0756000206646 |
    +------------------+-------------------+
    1 row in set (0.09 sec)
     
    SELECT ra, decl FROM Object WHERE qserv_areaspec_box(0.95,
    +-------------------+------------------+
    | ra | decl |
    +-------------------+------------------+
    | 0.952155934104298 | 19.1739644910299 |
    | 0.951022182881938 | 19.1744018550878 |
    | 0.979879729932035 | 19.1721286203352 |
    | 0.978531748948322 | 19.173622354719 |
    | 0.975277403624571 | 19.1717082593989 |
    | 0.965659553702501 | 19.1732402376328 |
    | 0.960765770111898 | 19.1728325244272 |
    | 0.956040810381224 | 19.1748876675009 |
    | 0.954389385192787 | 19.1715837046997 |
    | 0.970953770462485 | 19.1732960324755 |
    | 0.988995842261423 | 19.172924537295 |
    | 0.98748403175534 | 19.1744384618428 |
    | 0.990599073289862 | 19.1748218268107 |
    | 0.989373097950412 | 19.1741759125297 |
    | 0.995062781391914 | 19.1726058129962 |
    | 0.993584927322364 | 19.174694023095 |
    | 0.994098536926311 | 19.171425377618 |
    | 0.997942570312296 | 19.1749796823199 |
    | 0.987602654004053 | 19.1743333663937 |
    | 0.988982091888198 | 19.1729311723649 |
    +-------------------+------------------+
    20 rows in set (0.33 sec)
    9
    LARGE SYNOPTIC SURVEY TELESCOPE
    Qserv Test Summer 2015
    DMTR-13
    Latest Revision 2015-08-27
    6.3 Full table scans
    select count(*) from Object where y_instFlux > 5;
    +----------------+
    | SUM(QS1_COUNT) |
    +----------------+
    |0|
    +----------------+
    1 row in set (4 min 7.61 sec)
    select min(ra), max(ra), min(decl), max(decl) from Object;
    +--------------+------------------+-------------------+------------------+
    | MIN(QS1_MIN) | MAX(QS2_MAX) | MIN(QS3_MIN) | MAX(QS4_MAX)
    +--------------+------------------+-------------------+------------------+
    | 0 | 359.999999921199 | -87.8823524031432 | 45.5294117096401
    +--------------+------------------+-------------------+------------------+
    1 row in set (4 min 4.24 sec)
     
    select count(*) from Source where flux_sinc between 1
    +----------------+
    | SUM(QS1_COUNT) |
    +----------------+
    | 3539300 |
    +----------------+
    1 row in set (18 min 8.09 sec)
     
    select count(*) from Source where flux_sinc between 2
    +----------------+
    | SUM(QS1_COUNT) |
    +----------------+
    | 3589961 |
    +----------------+
    1 row in set (17 min 57.38 sec)
     
    select count(*) from ForcedSource where psfFlux between
    +----------------+
    10
    LARGE SYNOPTIC SURVEY TELESCOPE
    Qserv Test Summer 2015
    DMTR-13
    Latest Revision 2015-08-27
    | SUM(QS1_COUNT) |
    +----------------+
    | 67769638 |
    +----------------+
    1 row in set (14 min 58.61 sec)
    6.4 Joins
    select count(*) from Object o, Source s WHERE o.deepSourceId=s.objectId
    s.flux_sinc BETWEEN 0.13 AND 0.14;
    +----------------+
    | SUM(QS1_COUNT) |
    +----------------+
    | 35179 |
    +----------------+
    1 row in set (23 min 1.44 sec)
     
    select count(*) FROM Object o, ForcedSource f WHERE o.deepSourceId=f.deepSourceId
    f.psfFlux BETWEEN 0.13 AND 0.14;
    +----------------+
    | SUM(QS1_COUNT) |
    +----------------+
    | 6749369 |
    +----------------+
    1 row in set (21 min 31.38 sec)
    6.5 Near neighbor
    SELECT count(*)
    FROM Object o1, Object o2
    WHERE qserv_areaspec_box(90.299197, -66.468216, 98.762526,
    scisql_angSep(o1.ra, o1.decl, o2.ra, o2.decl) < 0.015;
    +----------------+
    | SUM(QS1_COUNT) |
    +----------------+
    11
    LARGE SYNOPTIC SURVEY TELESCOPE
    Qserv Test Summer 2015
    DMTR-13
    Latest Revision 2015-08-27
    | 96795152 |
    +----------------+
    1 row in set (11 min 16.02 sec)
    6.6 Shared scans
    Two scans on Object, both finished in ~8.5 min or so. Startup
    QTYPE_FTSObj: 505.703582048 SELECT COUNT(*) FROM Object
    QTYPE_FTSObj: 505.837508917 SELECT MIN(ra), MAX(ra)
    Five scans on Object finished in 16-20 min. Startup was staggered.
    QTYPE_FTSObj: 990.450098038 SELECT MIN(ra), MAX(ra) FROM
    QTYPE_FTSObj: 1168.69941115 SELECT MIN(ra), MAX(ra) FROM
    QTYPE_FTSObj: 1180.72830892 SELECT COUNT(*) FROM Object
    QTYPE_FTSObj: 1178.19018197 SELECT COUNT(*) FROM Object
    QTYPE_FTSObj: 1173.29835892 SELECT MIN(ra), MAX(ra)
    Five scans on Object, without staggering, not much difference:
    QTYPE_FTSObj: 738.438729763 SELECT COUNT(*) FROM Object
    QTYPE_FTSObj: 1162.67162609 left 2437.32837391 SELECT
    QTYPE_FTSObj: 1169.67710209 left 2430.32289791 SELECT
    QTYPE_FTSObj: 1171.61784506 left 2428.38215494 SELECT
    QTYPE_FTSObj: 1171.95623493 left 2428.04376507 SELECT
    chunkId FROM Object GROUP BY
    Five scans: four on Object, one on Source: ~1h10 min per scan
    QTYPE_FTSObj: 4237.70917988 SELECT MIN(ra), MAX(ra) FROM
    12
    LARGE SYNOPTIC SURVEY TELESCOPE
    Qserv Test Summer 2015
    DMTR-13
    Latest Revision 2015-08-27
    QTYPE_FTSObj: 4262.98238802 SELECT COUNT(*) FROM Object
    QTYPE_FTSObj: 4263.39259911SELECT COUNT(*) FROM Object
    QTYPE_FTSObj: 4263.39338088 SELECT COUNT(*) FROM Object
    QTYPE_FTSSrc: 4264.03135395 SELECT COUNT(*) FROM Source
    References
    [1]
    [DMTR-12]
    , Becla, J.,
    Qserv
    2013,
    300 node
    ,DMTR-12,URLhttps://ls.st/DMTR-
    test
    13

    Back to top