Transcript Tuning Database Configuration Parameters with iTuned

TUNING DATABASE
CONFIGURATION PARAMETERS
WITH ITUNED
Vamsidhar Thummala
Collaborators: Songyun Duan, Shivnath Babu
Duke University
Performance Tuning of Database Systems
2

Physical design tuning
 Indexes
[SIGMOD’98, VLDB’04]
 Materialized
 Partitioning



views [SIGMOD’00, VLDB’04]
[SIGMOD’82, SIGMOD’88, SIGMOD’89]
Statistics tuning [ICDE’00, ICDE’07]
SQL Query tuning [VLDB’04 ]
Configuration parameter or Server parameter
tuning [This talk]
2
Database Configuration Parameters
3

Parameters that control
 Memory
distribution
 shared_buffers,
 I/O
work_mem
optimization
 wal_buffers,
checkpoint_segments,
checkpoint_timeout, fsync
 Parallelism
 max_connections
 Optimizer’s
cost model
 effective_cache_size,
random_page_cost
default_statistics_target, enable_indexscan
3
Need for Automated Configuration
Parameter Tuning (1/2)
4
4
Need for Automated Configuration
Parameter Tuning (2/2)
5

Number of threads related to configuration
parameter tuning vs. other under “PostgreSQL
performance” mailing list
Recently, there has been some effort from
community to summarize the important parameters
[PgCon’08]
5
Typical Approach: Trial and Error
6
1 User*:
Hi, list. I've just upgraded pgsql from 8.3 to
8.4. I've used pgtune before and everything
worked fine for me. And now i have ~93%
cpu load. Here's changed values of config:
default_statistics_target = 50
maintenance_work_mem = 1GB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 22GB
work_mem = 192MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 7680MB
max_connections = 80
My box is Nehalem 2xQuad 2.8 with RAM
32Gb, and there's only postgresql working on
it.
What parameters I should give more
attention on?
2
Response:
All the values seem quite reasonable to me.
What about the _cost variables? I guess one
or more queries are evaluated using a
different execution plan, probably sequential
scan instead of index scan, hash join instead
of merge join, or something like that. Try to
log the "slow" statements - see
"log_min_statement_duration". That might
give you slow queries (although not
necessarily the ones causing problems), and
you can analyze them. What is the general
I/O activity? Is there a lot of data
read/written to the disks, is there a lot of I/O
wait?
PS: Was the database analyzed recently?
6
*http://archives.postgresql.org/pgsql-performance/2009-07/msg00323.php, 30th Jul 2009
Doing Experiments to Understand the
Underlying Response Surface
7

TPC-H 4 GB database, 1 GB memory, Query 18
7
Challenges
8

Large number of configuration parameters
Total ~ 100
 10-15 are important depending on OLTP vs. OLAP
 Brute-Force will not work



Results in exponential number of experiments
Parameters can have complex interactions
Sometimes non-monotonic and counterintuitive
 Limits the one-parameter-at-a-time approach


No holistic configuration tuning tools

Existing techniques focus on specific memory related
parameters or recommend default settings
8
Our Solution: iTuned
9

Practical tool that uses planned experiments to tune
configuration parameters
 An
adaptive sampling algorithm to plan the sequence
of experiments (Planner)
 A novel workbench for conducting experiments in
enterprise systems (Executor)
 Features for scalability like sensitivity analysis and use
of parallel experiments to reduce
 Total
number of experiments
 Per experiment running time/cost
9
Outline of the talk
10




iTuned Planner
iTuned Executor
Evaluation
Conclusion
10
Problem Abstraction
11

Given



A database D and workload W
Configuration Parameter Vector X = <x1, x2, …, xm >
Cost Budget R

Goal: Find high performance setting X* subject to the budget
constraint
Problem: Response surface y = (X) is unknown

Solution Approach:



Conduct experiments to learn about the response surface
Each experiment has some cost and gives sample <Xi, yi >
11
iTuned Planner
12

Uses an adaptive sampling algorithm
1
Boot Strapping:
Conduct initial set of
experiments
Latin Hypercube Sampling
k-Furthest First
2
Stopping
Criteria:
Based on
cost
budget, R
Sequential Sampling:
1.Select NEXT experiment,
XNEXT based on previous
samples
A. Calculate the
improvement, IP(X)
of each candidate
sample and select
the sample with
highest
improvement as
XNEXT
12
Improvement of an Experiment
13

Improvement IP(X) is defined as:




y(X*) – y(X) if y(X) < y(X*)
0
otherwise
Issue: IP(X) is known only after y(X) is known, i.e., an
experiment has to be conducted at X to measure y(X)
We estimate IP(X) by calculating the Expected Improvement,
EIP(X)
p y( X * )
EIP( X )
 ( y( X
*
)  p) pdfyˆ ( X ) ( p)dp
p  
Improvement at each
configuration setting

Probability density function of
(Uncertainty estimate)
yˆ ( X )
To calculate EIP(X), we need to approximate pdfyˆ ( X )( p)
13
Conducting Experiment at XNEXT using
Expected Improvement
y
6
8
14
2
4
EIP(X)
0

4
Projection on 1D

6
8
10
12
x1
Conduct NEXT
experiment here
14
Generating pdf through Gaussian
Process
15

We estimate the performance
as


yˆ ( X )  f t ( X ) Z ( X )




f t ( X )
Where
is a regression model, Z ( X ) is the residual of
the model, captured through Gaussian Process
Gaussian Process,
surface
Z(X )
captures the uncertainty of the
is specified by mean and covariance functions
 We use zero-mean Gaussian process
 Covariance is a kernel function that inversely depends on
the distance between two samples Xi and Xj
 Z(X )

Residuals at nearby points exhibit higher correlation
15
Calculating Expected Improvement
using Gaussian Process
16

Lemma: Gaussian Process models yˆ ( X ) as a univariate Gaussian with mean
and variance
as

Theorem: There exists a closed form for EIP(X)

[See paper for proof and details]
16
Tradeoff between Exploration vs.
Exploitation
Settings X with high EIP
are either
to known good
settings
highly uncertain
regions
 Assists
in exploration
Gaussian Process tries to
achieve the balance between
exploration vs. exploitation
6
6
4 4
 In
EIP(X)
22
in exploitation
y y
 Assists
8
 Close
00

8
17
4
6
8
10
12
x1
x1
17
Outline of the talk
18




iTuned Planner
iTuned Executor
Evaluation
Conclusion
18
Goal of the Executor
19

To conduct experiments
 Without
impacting production system
 As close to real production runs as possible

Traditional choices
 Production
 May
 Test
system itself
impact running applications
system
 Hard
to replicate exact production settings
 Manual set-up
19
iTuned Executor
20

Exploits the underutilized resources to conduct
experiments
 Production
systems, Stand-by systems, Test systems, On
the cloud

Design:
 Mechanisms:
Home & garage containers, efficient
snapshots of data
 Policies: Specified by admins
 If
CPU, memory, disk utilization is below 20% for the past
10 minutes, then 70% resources can be taken for
experiments
20
Example Mechanism set-up on Stand-by
System using ZFS, Solaris, and PITR
21
Standby Environment
Production Environment
Clients
Clients
Clients
Home
Middle Tier
DBMS
Apply WAL
continuously
Write Ahead Log
shipping
Apply
DBMS
WAL
Database
Standby
HomeMachine
Garage
Workbench for conducting
experiments
DBMS
DBMS
Copy on Write
Database
Policy Manager
Interface
Experiment Planner & Scheduler
Engine
21
Outline of the talk
22




iTuned Planner
iTuned Executor
Evaluation
Conclusion
22
Empirical Evaluation (1)
23



Two database systems, PostgreSQL v8.2 and
MySQL v5.0
Cluster of machines with 2GHz processor and 3GB
RAM
Mixture of workloads
 OLAP:
Mixes of TPC-H queries
 Varying
#queries, #query_types, and MPL
 Varying scale factors (SF = 1 to SF = 10)
 OLTP:

TPC-W and RuBIS
Number of parameters varied: up to 30
23
Empirical Evaluation (2)
24

Techniques compared
Default parameter settings shipped (D)
 Manual rule-based tuning (M)
 Smart Hill Climbing (S)



Brute-Force search (B)



State-of-the-art technique
Run many experiments to find approximation to optimal setting
iTuned (I)
Evaluation metrics
Quality: workload running time after tuning
 Efficiency: time needed for tuning

24
Comparison of Tuning Quality
25
Simple Workload
with one TPC-H
Query (Q1)
Complex Workload
with mix of TPC-H
Queries (Q1+Q18)
25
iTuned’s Efficiency and Scalability
26


Run experiments in parallel
Abort low-utility experiments early
26
iTuned’s Sensitivity Analysis
27

Identify important parameters quickly
 Use
Sensitivity Analysis to reduce experiments
27
Related work
28

Parameter tuning
1.
2.
3.



Focus on specific classes of parameters (mainly memory
related buffer pools) [ACM TOS’08, VLDB’06]
Statistical Approach for Ranking Parameters [SMDB’08]
Brute force approach to experiment design
Tools like DB2 Configuration advisor and pg_tune
recommend default settings
Adaptive approaches to sampling [SIGMETRICS’06]
Work related to iTuned’s executor


Oracle SQL Performance Analyzer [SIGMOD’09, ICDE’09]
Virtualization, snapshots, suspend-resume
28
Conclusion
29

iTuned automates the tuning process by adaptively
conducting experiments
 Our

initial results are promising
Future work
 Apply
database-specific knowledge to keep optimizer
in loop for end-to-end tuning
 Query
plan information
 Workload
compression
 Experiments in cloud
29
Questions?
30

Thank You
30