Transcription

Who am I? Tatsuro Yamada From Tokyo Japan Work for NTT Open Source Software center Work Database consulting and support for NTT Group companies Performance evaluation: ex. PG95 on HP Superdome X (240core) Just started contributing to oracle fdw Interest Listening to Music (Bossa nova, Jazz samba and Acid Jazz) Skiing, Beer, RaspberryPi3, Android, AutocrossCopyright 2016 NTT corp. All Rights Reserved.0

Copyright(c)2016 NTT Corp. All Rights Reserved.A Challenge of Huge Billing System MigrationTatsuro YamadaNTT OSS CenterPGCon May 20, 2016Copyright 2016 NTT corp. All Rights Reserved.

Purpose of the presentation In this talk, I am going introduce barriers andsolutions for our migration project. I hope to prove that PostgreSQL can be usedin mission-critical fields. I hope to increase PostgreSQL users andthey will share new use cases.Copyright 2016 NTT corp. All Rights Reserved.2

Agenda1. Background of the migration project2. Three major challenges of the project3. Thoughts about the future of PostgreSQL4. ConclusionCopyright 2016 NTT corp. All Rights Reserved.3

1. Background of the migration projectCopyright 2016 NTT corp. All Rights Reserved.4

About NTT OSS Center Who are we? NTT (Nippon Telegraph and Telephone Corporation) Telecommunication Carrier in Japan 900 subsidiaries throughout the world What is NTT OSS Center? promotes and adopts OSS to the NTT group companiesto reduce the TCO. Consulting /supportconsulting service, support desk, product maintenance. R&Ddeveloping OSS and related tools with the community.pg statsinfo, pg hint plan, pg bulkload, etc.Copyright 2016 NTT corp. All Rights Reserved.5

Relevant companies・The Client, the SI and NTT OSS center are cooperation.・We developed the migration system that utilizes the OSS.ClientNTT Communications VPN services Mobile services Cloud services IP Telephone services and so on.CooperationSystems IntegratorNTT ComwareDevelopment Project DevelopmentConsultantNTT OSS Center Consulting/Support service ofOSS Operation, maintenanceCopyright 2016 NTT corp. All Rights Reserved.6

System Outline The backbone billing system of a telecom business which hasseveral millions of customers. Never allowed an unexpected shutdown nor even an error inlife time: Mission-critical system.Corporate CustomersServices1. Subscribe and useseveral millions contracts3. billing informationRelevant System4. processing resultsmore than 100Target SystemChargecalculationCreating billCollectingmanagement2. usage statusAnalysisIn-house user Data History datasome TB05. analysis resultsCopyright 2016 NTT corp. All Rights Reserved.7

Why did we need system migration? 3 reasons for migration on the system Life timeThe legacy system approached end of life. Cost reductionDiscard proprietary software for cost reduction.ex. OS, DBMS Also avoiding vendor lock-ins. Performance ScalabilityUse cloud infrastructure to prepare for future businessexpansion. To adopt new architecture: ex. Java instead of COBOL We decided to rebuild the system, including OS,DBMS, application, table schemas and language.Copyright 2016 NTT corp. All Rights Reserved.8

Comparison of legacy vs. target system Software stack diagramLegacy SystemBatchOnlineTarget MigrationTP aUNIXLinuxOn-premises (UNIX Server)Cloud infrastructureCopyright 2016 NTT corp. All Rights Reserved.9

System requirementsSystem Imagerequirements 24x7, mission criticalrelevant lystsDBMS batch, online andanalysis data 5000 batch jobs/day historicaldata data size is over 1TB etc. havehave toto useusecloudcloudcloud infrastructureDaily Schedulenighttimedaytimeonline (OLTP)analysis (OLAP)batch strictstrict batchbatchperformanceperformancerequirement andrequirementandtime correctnesstimecorrectness OLTPOLTP andandOLAPOLAPworkworktogether duringdaytogetherduringthethedayDeadlineCopyright 2016 NTT corp. All Rights Reserved.10

3. Project ChallengesCopyright 2016 NTT corp. All Rights Reserved.11

Three major challenges in the project1. To run OLTP and OLAP without interferingeach otherdaytimeonline (OLTP)analysis (OLAP)2. To guarantee performance on cloudcloud infrastructure3. To ensure batch performance stability until EOLnighttimeDeadlinebatchCopyright 2016 NTT corp. All Rights Reserved.12

First challenge1. To run OLTP and OLAP without interferingeach other2. To guarantee performance on cloud3. To ensure batch performance stabilityuntil EOLCopyright 2016 NTT corp. All Rights Reserved.13

OLTP and OLAP without interfering Challenge Real-time analysis No negative effect on OLTP performance Our solution Divided OLTP and OLAP into separate databases Used Stream Replication with this setting: eliminate query cancels caused by WAL replayon the read replica.max standby streaming delay -1Copyright 2016 NTT corp. All Rights Reserved.14

Why did we decide it?Legacy DesignOur essingInformationAnalysisBatchProcessing OLTP and OLAP scrambles for resources,brings to negative effect to each other. OLTP and OLAP can use own resources,There is no effect to each other.BadGoodCopyright 2016 NTT corp. All Rights Reserved.15

Problem happened! We thought it would be easy to create and run aread replica, but we ran into a few problems. Problem WAL replay suddenly stopped on the read replica. No real-time analysis (query results staled)Copyright 2016 NTT corp. All Rights Reserved.16

Investigation of the problem Cause? Many long queries, WAL replay was waiting for finishedquery execution on the read replica. There is a side effect,In the case of max standby streaming delay -1. Solution add hot standby feedback ON to the settings. the Master database does not send a WAL which causes conflictagainst the running query on the read-replica. The complete solution for OLAP is: max standby streaming delay -1 hot standby feedback ONCopyright 2016 NTT corp. All Rights Reserved.17

Challenges1. To run OLTP and OLAP without interferingeach other2. To guarantee performance on cloud3. To ensure batch performance stabilityuntil EOLCopyright 2016 NTT corp. All Rights Reserved.18

Second Challenge1. To run OLTP and OLAP without affectingeach other2. To guarantee performance on the cloud3. To ensure batch performance stabilityuntil EOLCopyright 2016 NTT corp. All Rights Reserved.19

To guarantee performance on the cloud Challenge Usually resources(I/O, CPU and memory) are notguaranteed on a cloud environment Need to finish batch jobs before deadline What should we do then?Copyright 2016 NTT corp. All Rights Reserved.20

What should we do? Our Solution: Performance evaluationSteps1. Find the highest time zone of the resource usage2. Pick out the specific jobs from the job schedule3. Create a testing program and measure the performance.resource usageLoad1. FindJobsjob schedule2. Pick upcommon processingTesting ProgramJob AJob BJob CJob DJob EJob F3. Create and Test.Legacy vs PG.time series(*) Job: One unit of programs that make up the batch processing.It is composed of a large number of SQL. Copyright 2016 NTT corp. All Rights Reserved.21

Results of performance evaluation Results I/O usage was larger than expected. We recognized the necessary number of I/O (IOPS). We were able to tell required IOPS to the cloud vendor. They promised us to guarantee the IOPS. We could manage to finish batch processingbefore deadline on the cloud.Copyright 2016 NTT corp. All Rights Reserved.22

Challenges1. To run OLTP and OLAP without interferingeach other2. To guarantee performance on cloud3. To ensure batch performance stabilityuntil EOLCopyright 2016 NTT corp. All Rights Reserved.23

Third Challenge1. To run OLTP and OLAP without affectingeach other2. To guarantee performance on cloud3. To ensure batch performance stabilityuntil EOLCopyright 2016 NTT corp. All Rights Reserved.24

Why is batch performance stability needed? Want to know the timing to enhance servers. The batch time window can’t extend. The linear trend is ideal, It’s’ easy to predict future batchperformance. able to add extra resources before running out.2M1Mtime series (months)Difficult to predict thefuture processing timeStable (Linear trend is fine)3M2M1Mcontract number3Mprocessing time (hours)Not Stablecontract numberprocessing time (hours)Examplestime series (months)Easy to predict itCopyright 2016 NTT corp. All Rights Reserved.25

To ensure batch performance stability Analyze the batch jobs The batch execution time is almost equal tothe query execution time on the system.The batch execution timeAP:x%The query execution time:over 80% We Focus on the query execution time. What does “Query execution time” depend on? The execution plan .Copyright 2016 NTT corp. All Rights Reserved.26

Basics of query execution How was the plan created and chosen? Record numbersSQLSQLSQLStatistics Most common valueSQLSQLSQL Distinct value, 画plan a :cost 1:plan z :cost 100 The planner makes a mistake sometimes.Copyright 2016 NTT corp. All Rights Reserved.27

What has been done to get a efficient plan? We did these in each phase.Design Redesign ER modelImplementation/Unit Testing SQL coding rule Plan check Index tuningIntegration/System Testing Performance Test Rewriting SQL Modify Application Table statistics management Vacuum managementOperationThe majority of queries/Maintenancemet the requirements and were stable.Copyright 2016 NTT corp. All Rights Reserved.28

What happened? Problem By changing a values of search criteria,query execution time increased from several minutesto several days! The table record had not been increased. The cardinality of the value was confirmed to be unchanged. Cause? The execution plan is inefficient.The problem came from the planner.Copyright 2016 NTT corp. All Rights Reserved.29

The decision If you were us, which would you choose forthe solution ? The premise: worked variously to get the efficient plan. a few days to release the system Selection list1. Rewrite query and application2. Wait for 2 years: new PG version would improve planner.3. Give up the migration project.4. Use the forbidden fruit.Copyright 2016 NTT corp. All Rights Reserved.30

What is the “forbidden fruit”? A special tool brewed in-house: This tool can control a plan using optimizer hints. The name is pg hint plan. tool for if the planner doesn't give you desired plans. able to control individual part of the plan!set enable * parameters can not do that. Examples /* IndexScan(foo) */ /* Leading((foo bar)) */ /* HashJoin(foo bar) */Copyright 2016 NTT corp. All Rights Reserved.31

pg hint plan provides a lot of Hints! Around 20 kinds of hints.Copyright 2016 NTT corp. All Rights Reserved.32

Dev. Community doesn’t like it We know the advantage /disadvantage of it.Copyright 2016 NTT corp. All Rights Reserved.33

Decision to use hints discussed the advantage /disadvantageand decided to use Hints on the project!Disadvantage Poor application code maintainability: Doesn’t matter hints in queries require massive refactoring . Does not scale with data size:Doesn’t matter the hint that's right when a table is small is likely to be wrongwhen it gets larger.Advantage Prevents optimizer failure:It matters Implementation failure (known issues ) Theoretical failure (estimation limits, n 2 correlation problem )※This is part of an excerpt of the list on the wiki.Copyright 2016 NTT corp. All Rights Reserved.34

We solved using the hints. The inefficient plans were revised to theefficient plans and were stable. several days: bad - several minutes: good We were able to achieve stable batchperformance. The system could accomplish batchperformance stability until EOL.Copyright 2016 NTT corp. All Rights Reserved.35

pg hint plan in our cases Our problem is classified into the 2 categories. Planner Row-Count Errors1. Can't See Through WITH2. Join selectivity doesn't know about cross-tablecorrelationsThanks to Rovert Haas to share the planner error list.https://sites.google.com /site /robertmhaas /query performance /planner-row-count-errorsCopyright 2016 NTT corp. All Rights Reserved.36

pg hint plan in our cases: Case11. Row count error by WITH This test case is a example for row count error by WITH. The difference is the limit rows: 199 or 200. We can expect same plan and execution time.Limit199explain analyze with x as (select * from t1 limit 199)select * from(select * from t1 where a in (select a from x)) tmp,t2where tmp.a t2.a;Limit200explain analyze with x as (select * from t1 limit 200)select * from(select * from t1 where a in (select a from x)) tmp,t2where tmp.a t2.a; These sql just to illustrate the problem.Copyright 2016 NTT corp. All Rights Reserved.37

Row count error by WITH (cont.) Results of Explain analyze The plan and execution time are -----------------------------------------Nested Loop (cost 9.76.17157.31 rows 199 width 16)(actual time 0.234.195.086 rows 199 loops 1)(snip)Execution time: 195.155 ---------------------------------------Hash Join (cost 30839.81.48153.89 rows 500000 width 16)(actual time 249.394.303.259 rows 200 loops 1)(snip)Execution time: 303.335 msx 1.5?! By the Row count error,the plan and time are changed. Why?Copyright 2016 NTT corp. All Rights Reserved.38

Row count error by WITH (cont.) Cause? The CTE doesn’t have statistics since it is a temporarytable. The Rows estimation is calculated using default values. The Planner can’t optimize the plan. Solution pg hint plan can control the plan manually. /* Leading( (t1 x) ) Hashjoin(t1 x) */Copyright 2016 NTT corp. All Rights Reserved.39

Row count error by WITH (cont.) The result of query using the ------------------------------------------Hash Join (cost 30839.81.48153.89 rows 500000 width 16)(actual time 249.394.303.259 rows 200 loops 1)(snip)Execution time: 303 .335 msSame rowsafterLimit200 ----------------------------Nested Loop (cost 10000000012.31.10000258778.89rows 500000 width 16)(actual time 0.333.197.523 rows 200 loops 1 )(snip)Execution time: 197 .628 ms※ Limit 199: 195.155msBy changing the plan,the Time decreased.Copyright 2016 NTT corp. All Rights Reserved.40

pg hint plan in our cases: Case22. Row count error by cross-table correlationsexecution time(seconds) TPC-h’s Q9 is a famous example. We can reduce the execution time using the hints.The tuning effect in the query by pg hint plan4,000時 Tuning by hintsチューニング後 -3 scale factor 24 HP DL380 G7 32GB PostgreSQL 9.4.1 Shared buffers 3GBCopyright 2016 NTT corp. All Rights Reserved.41

Row count error bycross-table correlations (cont.) Cause? The planner can’t use Join selectivity with cross-tablecorrelations. It excessively expected a small number of joined rows. In this situation, a Nested Loop is not efficient. Ex. Estimated rows: 1, Actual rows: 10000000 Solution pg hint plan can provide the efficient plan. Cf. this problem will be fixed soon.Keep going! Tomas, Horiguchi and hackers! http://www.postgresql.org /message-id /[email protected] 2016 NTT corp. All Rights Reserved.42

Challenges1. To run OLTP and OLAP without interferingeach other2. To guarantee performance on cloud3. To ensure batch performance stabilityuntil EOLCopyright 2016 NTT corp. All Rights Reserved.43

Summary: challenges and solutions1. To run OLTP and OLAP without interferingeach other Creating the read-replica using SR easily.Make sure 2 parameters for OLAP.2. To guarantee performance on the cloud Verifying I/O performance is the key to success.PostgreSQL can use on the cloud infrastructure.3. To ensure batch performance stability until EOL In the case of the Optimizer failure,must use pg hint plan for controlling a plan.It can become a strong weapon for users.Copyright 2016 NTT corp. All Rights Reserved.44

Project Goals achieved The migration challenges were successful. The system keeps working stably sinceMay 2015.Copyright 2016 NTT corp. All Rights Reserved.45

4. Thoughts about the future of PostgreSQLCopyright 2016 NTT corp. All Rights Reserved.CopyrightTatsuro Yamada46

Thoughts about the future of PostgreSQL Hints vs. Planner improvements The planner’s estimation is sometimes wrong sincelimitation /specification. By reducing the mistake, many users can reduce the systemdevelopment costs. PostgreSQL has “set enable *” parameters.but it’s not useful to revise an inefficient plan.Because application scope is too broad – affects all nodesof a given type in the plan. pg hint plan is able to control individual part of the plan. Should we implement “the Optimizer Hints” toPostgreSQL core?Copyright 2016 NTT corp. All Rights Reserved.47

Thoughts (cont.)ORCopyright 2016 NTT corp. All Rights Reserved.48

Requests Planner improvements I have 2 ideas for the planner1. Feedback loop for planningWe can get a new efficient plan using a past plan result:“Actual rows” , “Actual time” and so on.It is similar to PDCA cycle.2. Plan cache /Plan tableChoosing and freezing an efficient plan from a plancache /table is useful for stably performance.This feature provide a plan management to user.Copyright 2016 NTT corp. All Rights Reserved.49

1. Feedback loop for planning The use cases of feedback loopExamples1.1. Creating an alternative planif the plan has huge differences betweenthe estimation records and the actual records.The planner should create an alternative plan.Nested loop - Hash join1.2. Validating the Statistics for planningThe planner doesn’t know the statistics are accurate.If the statistics have Correctness factor/Risk factorwhich is results of validating.The planner can use these factors to consideralternative plans.Copyright 2016 NTT corp. All Rights Reserved.50

2. Plan cache/Plan table The use cases of Plan cache /tableExamples2.1. Monitoring a plan using Plan historyWe looked at log to check when plan changes.The plan cache /table can investigate it easily.2.2. Choose plan from Plan historyYou can choose a plan which you desired manually,then the planner will use the plan always.In addition, We can possible to get suggestion ofefficient plan using analyzed the historical dataautomatically.Copyright 2016 NTT corp. All Rights Reserved.51

5. ConclusionCopyright 2016 NTT corp. All Rights Reserved.52

5. Conclusion In this talk, I have shared my experience onthe migration project in NTT and my thoughtsabout the future of PostgreSQL. I hope that I was able to prove that PostgreSQLcan be used in your mission-critical fields.Time to migrateCopyright 2016 NTT corp. All Rights Reserved.53

Thank youPlan to succeedNTT OSS Center"elephants beach walk" by Senorhorst Jahnsen is licensed under CC BY 2.0Copyright 2016 NTT corp. All Rights Reserved.54

Q&AAny Questions?Copyright 2016 NTT corp. All Rights Reserved.Copyright55Tatsuro Yamada

References NTT OSS Center on GitHub https://github.com /ossc-db pg reorgpg rmanpg bulkloadpg hint planpg dbms statspg store plansdblink plusdb syntax diffsyncdb SourceForge https://sourceforge.net /projects /pgstatsinfo / pg statsinfo pg stats reporterCopyright 2016 NTT corp. All Rights Reserved.56

Appendix Optimizer Hints Discussion Row count error by WITHCopyright 2016 NTT corp. All Rights Reserved.57

Optimizer Hints Discussion https://wiki.postgresql.org /wiki /OptimizerHintsDiscussion DemeritsNo.SubjectDetail1Poor application codemaintainabilityhints in queries require massive refactoring.2Interference with upgradestoday's helpful hints become antiperformance after an upgrade.3Encouraging bad DBA habits slap ahint on instead of figuring out thereal issue.-4Does not scale with data sizethe hint that's right when a table is small islikely to be wrong when it gets larger.5Failure to actually improve queryperformancemost of the time, the optimizer is actuallyright.6Interfering with improving thequery plannerpeople who use hints seldom report thequery problem to the project.Copyright 2016 NTT corp. All Rights Reserved.58

Optimizer Hints Discussion MeritsNo.SubjectDetail1"One-shot" issues, such as annualor one-time reports, for whichmaintainability is not a concern-2Ability to "test" various executionpaths in detail and see how theoptimizer is working (or not)-3Optimizer failureImplementation failure (known issues)Theoretical failure (estimation limits, n 2correlation problem)Copyright 2016 NTT corp. All Rights Reserved.59

Optimizer Hints Discussion discussed the Fit /Gap on the list. Demerits of optimizer hintNo.DemeritsFIT/GAP and Reasons1Poor application code maintainabilityGAPOnly using a few hints.2Interference with upgradesGAPDo not upgrade if there is no fatal bug.34Encouraging bad DBA habits slap ahint on instead of figuring out the realissue.GAPDoes not scale with data sizeGAPWe used the hint on some queries which couldnot be improved.Confirmed by performance tests using the dataamount of EOL time of the system.56Failure to actually improve queryperformanceGAPInterfering with improving the queryplannerGAPImportant for performance to be stable whilemeeting the performance requirements than thepeak performanceNo problem because it is shared by PGCon or Copyright 2016 NTT corp. All Rights Reserved.hackers.60

Optimizer Hints Discussion The hint gives the merit to the project. Merits of optimizer hintNo.MeritsFIT/GAP and Reasons1"One-shot" issues, such as annual orone-time reports, for whichmaintainability is not a concernGAPAbility to "test" various executionpaths in detail and see how theoptimizer is working (or not)FITPrevents optimizer failureFIT23-We use Hints for tuning the planWe’d like to prevent optimizer failure. Decided to use the hint.Copyright 2016 NTT corp. All Rights Reserved.61

Row count error by WITHcreate table t1 (a int, b int);insert into t1 (select a, random() * 1000 fromgenerate series(0, 999999) a);create index i t1 a on t1 (a);analyze t1;create table t2 (a int, b int);insert into t2 (select a, random() * 1000 fromgenerate series(0, 999999) a);create index i t2 a on t2 (a);analyze t2;Copyright 2016 NTT corp. All Rights Reserved.62

Row count error by WITH explain analyze with x as (select * from t1 limit 199 ) select * from(select * from t1 where a in (select a from x)) tmp,t2 wheretmp.a t2.a;Q U E RY P L A N- - - - - - - -- -- -- - -- -- -- -- -- - -- -- -- -- -- - -- -- -- -- -- -- - -- -- -- -- -- - -- -- -- -- -- -- - -- -- -- -- -- - -- -- -- -- -- -- - -- -- -- -- -- - -- -- -- -- -- -- - N es t e d Lo o p ( c o s t 9 . 7 6 . . 1 7 1 5 7 . 3 1 r o w s 1 9 9 w i d t h 1 6 ) ( a c t u a l t i m e 0 . 2 3 4 . . 1 9 5 . 0 8 6 r o w s 1 9 9 l o o p s 1 )CTE x- Limit (cost 0.00.2.87 rows 199 width 8) (actual time 0.008.0.079 rows 199 loops 1)- Seq Scan on t1 t1 1 (cost 0.00.14425.00 rows 1000000 width 8 )(actual time 0.007.0.044 rows 199 loops 1)- Hash Semi Join (cost 6.47.17058.68 rows 199 width 12 )(actual time 0.224.194.404 rows 199 loops 1)Hash Cond: (t1.a x.a)- Seq Scan on t1 (cost 0.00.14425.00 rows 1000000 width 8 )(actual time 0.014.92.474 rows 1000000 loops 1)- Hash (cost 3.98.3.98 rows 199 width 4) (actual time 0.201.0.201 rows 199 loops 1)B u c ke t s : 1 0 2 4 B a t c h e s : 1 M e m o r y U s a g e : 1 5 k B- CTE Scan on x (cost 0.00.3.98 rows 199 width 4 )(actual time 0.010.0.158 rows 199 loops 1)- In d e x S c a n u s i n g i t 2 a o n t 2 ( c o s t 0 . 4 2 . . 0 . 4 7 r o w s 1 w i d t h 8 )(actual time 0.003.0.003 rows 1 loops 199)In d e x C o n d : ( a t 1 . a )Planning time: 0.401 msE xe c u t i o n t i m e : 1 9 5 . 1 5 5 m s(14 rows)Copyright 2016 NTT corp. All Rights Reserved.63

Row count error by WITH explain analyze with x as (select * from t1 limit 200 ) select * from(select * from t1 where a in (select a from x)) tmp,t2 wheretmp.a t2.a;Q U E RY P L A N- - - - - - - -- -- -- - -- -- -- -- -- - -- -- -- -- -- - -- -- -- -- -- -- - -- -- -- -- -- - -- -- -- -- -- -- - -- -- -- -- -- - -- -- -- -- -- -- - -- -- -- -- -- - -- -- -- -- -- -- - Hash Join (cost 30839.81. 48153.89 rows 500000 width 16) (actual time 249.394.303.259 rows 200loops 1)Hash Cond: (x.a t1.a)CTE x- Limit (cost 0.00.2.88 rows 200 width 8) (actual time 0.015.0.064 rows 200 loops 1)- Seq Scan on t1 t1 1 (cost 0.00.14425.00 rows 1000000 width 8 )(actual time 0.013.0.037 rows 200 loops 1)- N e s t e d Lo o p ( c o s t 4 . 9 2 . . 1 6 5 3 . 0 0 r o w s 5 0 0 0 0 0 w i d t h 1 2 )(actual time 0.230.0.584 rows 200 loops 1)- H a s h Ag g r e g a t e ( c o s t 4 . 5 0 . . 6 . 5 0 r o w s 2 0 0 w i d t h 4 )(actual time 0.217.0.255 rows 200 loops 1)G r o u p Ke y : x . a- CTE Scan on x (cost 0.00.4.00 rows 200 width 4 )(actual time 0.017.0.124 rows 200 loops 1)- In d e x S c a n u s i n g i t 2 a o n t 2 ( c o s t 0 . 4 2 . . 8 . 2 2 r o w s 1 w i d t h 8 )(actual time 0.001.0.001 rows 1 loops 200)In d e x C o n d : ( a x . a )- Hash (cost 14425.00.14425.00 rows 1000000 width 8 )(actual time 248.859.248.859 rows 1000000 loops 1)B u c ke t s : 1 3 1 0 7 2 B a t c h e s : 1 6 M e m o r y U s a g e : 3 4 7 1 k B- Seq Scan on t1 (cost 0.00.14425.00 rows 1000000 width 8 )(actual time 0.009.92.095 rows 1000000 loops 1)Planning time: 0.611 msE xe c u t i o n t i m e : 3 0 3 . 3 3 5 m s(16 rows)Copyright 2016 NTT corp. All Rights Reserved.64

Row count error by WITH explain analyze /* Leading( (t1 x) ) Hashjoin(t1 x) */ with x as(select * from t1 limit 200) select * from (select * from t1 where a in(select a from x)) tmp,t2 where tmp.a t2.a ;Q U E RY P L A N- - - - - - - -- -- -- - -- -- -- -- -- - -- -- -- -- -- - -- -- -- -- -- -- - -- -- -- -- -- - -- -- -- -- -- -- - -- -- -- -- -- - -- -- -- -- -- -- - -- -- -- -- -- - -- -- -- -- -- -- - N es t e d Lo o p ( c o s t 1 0 0 0 0 0 0 0 0 1 2 . 3 1 . . 1 0 0 0 0 2 5 8 7 7 8 . 8 9 r o w s 5 0 0 0 0 0 w i d t h 1 6 ) ( a c t ua ltime 0.333.197.523 rows 200 loops 1)CTE x- Limit (cost 0.00.2.88 rows 200 width 8) (actual time 0.008.0.062 rows 200 loops 1)- Seq Scan on t1 t1 1 (cost 0.00.14425.00 rows 1000000 width 8 )(actual time 0.007.0.029 rows 200 loops 1)- Hash Join (cost 9.00.18186.00 rows 500000 width 12 )(actual time 0.315.196.816 rows 200 loops 1)Hash Cond: (t1.a x.a)- Seq Scan on t1 (cost 0.00.14425.00 rows 1000000 width 8 )(actual time 0.016.93.165 rows 1000000 loops 1)- Hash (cost 6.50.6.50 rows 200 width 4) (actual time 0.290.0.290 rows 200 loops 1)B u c ke t s : 1 0 2 4 B a t c h e s : 1 M e m o r y U s a g e : 1 6 k B- H a s h Ag g r e g a t e ( c o s t 4 . 5 0 . . 6 . 5 0 r o w s 2 0 0 w i d t h 4 )(actual time 0.206.0.248 rows 200 loops 1)G r o u p Ke y : x . a- CTE Scan on x (cost 0.00.4.00 rows 200 width 4 )(actual time 0.012.0.139 rows 200 loops 1)- In d e x S c a n u s i n g i t 2 a o n t 2 ( c o s t 0 . 4 2 . . 0 . 4 7 r o w s 1 w i d t h 8 )(actual time 0.003.0.003 rows 1 loops 200)In d e x C o n d : ( a t 1 . a )Planning time: 0.398 msE xe c u t i o n t i m e : 1 9 7 . 6 2 8 m s(16 rows)Copyright 2016 NTT corp. All Rights Reserved.65

pg hint plan exampleNested Loop Hash Join# EXPLAIN SELECT#FROM pgbench branches b#JOIN pgbench accounts a ON b.bid a.bid#ORDER BY ---------------# /* Nested 定。#HashJoin(ab)(cost 0.29.5748.31rows 100000width 4)#SeqScan(a)Join Filter: (b.bid a.bid)# */- Index Scan using pgbench accounts pkey on pgbench accounts# EXPLAIN SELECT- a(cost 0.29.4247.29 rows 100000 width 8)Materialize# FROM pgbench branchesb(cost 0.00.1.01rows 1 width 4)# JOIN pgbench accounts a ON b.bid a.bid- Seq Scan on pgbench branchesb (cost 0.00.1.01 rows 1 width 4)# ORDER BY -----------------------------------------Sort (cost 12320.84.12570.84 rows 100000 width 4)Sort Key: a.aid- Hash Join(cost 1.02.4016.02 rows 100000 width 4)Hash Cond: (a.bid b.bid)- Seq Scan- Hashon pgbench accountsa(cost 0.00.2640.00 rows 100000 width 8)(cost 1.01.1.01 rows 1 width 4)- Seq Scan on pgbench branchesb(cost 0.00.1.01 rows 1 width 4)Copyright 2016 NTT corp. All Rights Reserved.66

Our Solution: Performance evaluation Steps 1. Find the highest time zone of the resource usage 2. Pick out the specific jobs from the job schedule 3. Create a testing program and measure the performance. What should we do? Testing Program Job A Job B Job C Job D Job E Job F ng (*) Job: One unit of programs that make up the batch processing.