The Thinking Person’s Guide to Data Warehouse Design Presentation

1 TheThinkingPerson sGuidetoDataWarehouseDesignRobinSchumacherVPProductsCalpont 2 Agenda Buildingalogicaldesign Transitioningtoaphysicaldesign Monitoringandtuningthedesign 3 Buildingalogicaldesign 4 Whycareaboutdesign 5 Whatisthekeycomponentforsuccess Inotherwords whatyoudowithyourMySQLServer intermsofphysicaldesign schemadesign andperformancedesign willbethebiggestfactoronwhetheraBIsystemhitsthemark PhilipRussom NextGenerationDataWarehousePlatforms TDWI 2009 6 First get useamodelingtool 7 ThelogicaldesignforOLTP 8 Simplereportingdatabases OLTPDatabase ReadShardOne ReportingDatabase ApplicationServers EndUsers ETL Justusethesamedesignonadifferentbox Replication 9 Horrorstorynumberone 10 Thelogicaldesignforanalytics datawarehousing 11 LogicalDesignConsiderations Datatypesaremoregenerallydefined notdirectedtowardadatabaseengineEntitiesaren tdesignedforperformancenecessarilyRedundancyisavoided butsimplicityisstillagoalBottomline youwanttomakesureyourdataiscorrectlyrepresentedandiseasilyunderstood newclassofusertoday 12 Manualhorizontalpartitioning Modelingtechniquetoovercomelargedatavolumes 13 ManualVerticalPartitioning Modelingtechniquetoovercomewidetables rows 14 Pro s con stomanualpartitioning MoretablestomanageMorereferentialintegritytomanageMoreindexestomanageJoinsoftentimesneededtoaccomplishqueryrequestsOftentimes aredesignisneededbecausetherows columnsyouthoughtyou dbeaccessingtogetherchange it shardtopredictad hocquerytraffic LessI OifdesignholdsupEasytopruneobsoletedataPossiblylessobjectcontention Pro s Con s 15 Thebottomlineonlogicalmodeling UseamodelingtooltocaptureyourdesignsDonotutilizeathird normalformdesignforanalytics keepitsimpleandunderstandableManualpartitioningisOKinsomecases but Letthedatabaseenginedotheworkforyou 16 Transitioningtoaphysicaldesign 17 SQLorNoSQL RoworColumndatabase Howtoscale ShouldIworryaboutHighavailability Indexorno HowshouldIpartitionmydata Isshardingagoodidea 18 GenerallistoftopBIdatabasedesigndecisions Generalarchitecture dataorientationStorageengineselectionPhysicaltable IndexpartitioningIndexingcreationandplacementOptimizingdataloads 19 Divide conqueristhebestapproach WhetheryouchoosetogoNoSQL ShardwithnormalorspecialMySQLengines useMPPstorageengines orsomethingsimilar divide conquerisyourbestfriendYoucanscale upanddivide conquertoapoint butyouwillhitdisk memory orotherlimitationsScalingupandoutisthebestfutureproofmethodology 20 Divide conquerviasharding 21 Whattechnologiesyoushouldbelookingat PhilipRussom NextGenerationDataWarehousePlatforms TDWI 2009 22 Roworcolumn basedengine 23 Columnvs roworientation Acolumn orientedarchitecturelooksthesameonthesurface butstoresdatadifferentlythanlegacy row baseddatabases 24 Example InfiniDBvs Leading rowDB InfiniDBtakesup22 lessspace InfiniDBloadeddata22 faster InfiniDBtotalquerytimeswere65 less InfiniDBaveragequerytimeswere59 less Noticenotonlyarethequeriesfaster butalsomorepredictable Testsrunonstandalonemachine 16CPU 16GBRAM CentOS5 4with2TBofrawdata 25 Whynotuseboth Youcancreateahybridsystemwhereyouuserow basedtablesandcolumn basedtablesinthesameinstanceandsamedatabaseUseInnoDBforOLTPorMyISAMforcertainreadoperationsUsecolumn basedtablesforanalytics datamarts orwarehousesYoucanscaleoutwithcolumntablesanduserow basedtableslocally 26 Whynotuseboth 27 MyISAM Archive Memory CSV High speedquery insertengineNon transactional tablelockingGoodfordatamarts smallwarehouses Compressesdatabyupto80 FastestfordataloadsOnlyallowsinserts selectsGoodforseldomaccesseddata MainmemorytablesGoodforsmalldimensiontablesB treeandhashindexes CommaseparatedvaluesAllowsbothflatfileaccessandeditingaswellasSQLquery DMLAllowsinstantaneousdataloads Also Mergeforpre 5 1partitioning MostusedDWStorageenginesinternaltoMySQL 28 WhataboutNoSQLoptions StandardmodelisnotrelationalTypicallydon tuseSQLtoaccessthedataTakeupmorespacethancolumndatabasesLackspecialoptimizers featurestoreduceI O Reallyarerow orientedarchitecturesthatstoredatain columnfamilies whichareexpectedtobeaccessedtogether rememberlogicalverticalpartitioning IndividualcolumnscannotbeaccessedindependentlyWillbefasterwithindividualinsertanddeleteoperationsWillnormallybefasterwithsinglerowrequestsWilllagintypicalanalytic datawarehouseusecases Partitioning not if but how mysql CREATETABLEpart tab c1int c2varchar 30 c3date PARTITIONBYRANGE year c3 PARTITIONp0VALUESLESSTHAN 1995 PARTITIONp1VALUESLESSTHAN 1996 PARTITIONp2VALUESLESSTHAN 1997 PARTITIONp3VALUESLESSTHAN 1998 PARTITIONp4VALUESLESSTHAN 1999 PARTITIONp5VALUESLESSTHAN 2000 PARTITIONp6VALUESLESSTHAN 2001 PARTITIONp7VALUESLESSTHAN 2002 PARTITIONp8VALUESLESSTHAN 2003 PARTITIONp9VALUESLESSTHAN 2004 PARTITIONp10VALUESLESSTHAN 2010 PARTITIONp11VALUESLESSTHANMAXVALUE mysql createtableno part tab c1int c2varchar 30 c3date Load8millionrowsofdataintoeachtable mysql selectcount fromno part tabwherec3 date 1995 01 01 andc3selectcount frompart tabwherec3 date 1995 01 01 andc3 date 1995 12 31 count 795181 1rowinset 3 88sec 90 ResponseTimeReduction Partitioning StripeyourPartitions CREATETABLET1 col1INT col2CHAR 5 col3DATE ENGINE MYISAMPARTITIONBYHASH col1 PARTITIONP1DATADIRECTORY appdata1 data PARTITIONP2DATADIRECTORY appdata2 data PARTITIONP3DATADIRECTORY appdata3 data PARTITIONP4DATADIRECTORY appdata4 data Notethatstripingonlyworksforsomeengines e g MyISAM Archive andforonlycertainoperatingsystems e g theoptionisignoredonWindows YoucanusetheREORGANIZEPARTITIONcommandtomovecurrentpartitionstonewdevices Partitioning SmartDataPruning mysql deletefromt2where c3 date 1995 01 01 andc3 date 1995 12 31 QueryOK 805114rowsaffected 47 41sec Mostdatawarehouseshavepruningorobsoletedataoperationsthatremoveunwanteddata Usingpartitioningallowsyoutomuchmorequicklyandefficientlyremoveobsoletedata mysql altertablet1droppartitionp1 QueryOK 0rowsaffected 0 03sec Records 0Duplicates 0Warnings 0 VS TheDROPPARTITIONisADDLoperation whichrunsmuchfasterthanaDMLDELETE 32 IndexCreationandPlacement Ifquerypatternsareknownandpredictable anddataisrelativelystatic thenindexingisn tthatdifficultIfthesituationisaveryad hocenvironment indexingbecomesmoredifficult MustanalyzeSQLtrafficandindexthebestyoucanOver indexingatablethatisfrequentlyloaded refreshed updatedcanseverelyimpactloadandDMLperformance Testdroppingandre creatingindexesvs doingin placeloadsandDML Realize though anyquerieswillbeimpactedfromdroppedindexesIndexmaintenance rebuilds etc cancauseissuesinMySQL locking etc Remembersomestorageenginesdon tsupportnormalindexes Archive CSV Rememberthatabenefitof most columndatabasesisthattheydonotneedoruseindexes 33 Optimizingfordataloads Thetwobiggestkillersofloadperformanceare 1 verywidetablesforrow basedtables 2 manyindexesonatable Statingtheobvious LOADDATAINFILEandthehigh speedloadersofcolumn basedenginesarethefastestwaytoloaddatavs singletonorarrayinsertstatementsColumn basedtablestypicallyloadfasterthanrow basedtableswithloadutilities howevertheywillexperienceslowerinsert deleteratesthanrow basedtablesLoadingdatainprimarykeyformathelpssomeengines e g InnoDB 34 Optimizingfordataloads Movethedataasclosetothedatabaseaspossible avoidhavingapplicationsonremotemachinesdodatamanipulationsandsenddataacrossthewirearowatatime perhapstheworstwaytoloaddataOftentimesgoodtocreatestagingtablesthenuseprocedurallanguagetododatamodificationsand orcreateflatfilesforhighspeedloadersLoadingdataviatime basedorderhelpssomecolumndatabaseslikeInfiniDB logicalrangepartitioningisthenpossible 35 Monitoringandtuningthedesign 36 Threeperformanceanalysismethods Bottleneckanalysis Workloadanalysis Ratioanalysis 37 Bottleneckanalysis Thefocusofthismethodologyistheanswertothequestion whatamIwaitingon WithMySQL unfortunately itcanbedifficulttodeterminelatencyinthedatabaseserverLockcontentionrarelyanissueindatawarehousesNewMySQLperformanceschemahasawaystogoinmyopiniontobetrulyusefulforbottleneckanalysisProblemsfoundinbottleneckanalysistranslateintobetterlockhandlingintheapp partitioningimprovements betterindexing orstorageenginereplacement 38 Workloadanalysis Thefocusofthismethodologyistheanswertothreequestions 1 Who sloggedon 2 Whataretheydoing 3 Howismymachinehandingit Monitoractiveandinactivesessions KeepinmindidleconnectionsdotakeupresourcesI Oand hotobjects akeyareaofanalysisKeyfocusshouldbeonSQLstatementmonitoringandcollection somethingthatgoesbeyondstandardpre productionEXPLAINanalysis 39 Horrorstorynumbertwo 40 ThepainofslowSQL PhilipRussom NextGenerationDataWarehousePlatforms TDWI 2009 41 Workloadanalysis SQLanalysisbasicallybecomesbottleneckanalysis becauseyou reaskingwhereyourSQLstatementisspendingitstimeOnceyouhavecollectedandidentifiedyour topSQL thenextstepistodotracingandinterrogationintoeachSQLstatementtounderstanditsexecutionHistoricalanalysisisimportanttoo aquerythatranfinewith5millionrowsmaytankwith50millionorwithmoreconcurrentusersDesignchangesusuallyinvolvedatafilestriping indexing partitioning orparallelprocessingadditions 42 Ratioanalysis Leastusefulofalltheperforma