Tuning Database Configuration Parameters with iTuned. Vamsidhar Thummala Collaborators: Songyun Duan, Shivnath Babu Duke University. Performance Tuning of Database Systems. Physical design tuning Indexes [SIGMOD’98, VLDB’04] Materialized views [SIGMOD’00, VLDB’04]
Collaborators: Songyun Duan, Shivnath Babu
Recently, there has been some effort from community to summarize the important parameters [PgCon’08]
All the values seem quite reasonable to me. What about the _costvariables? 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 analyzedrecently?
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% cpuload. Here's changed values of config:
maintenance_work_mem = 1GB
constraint_exclusion = on
effective_cache_size = 22GB
work_mem = 192MB
shared_buffers = 7680MB
max_connections = 80
My box is Nehalem 2xQuad 2.8 with RAM32Gb, and there's only postgresql working on it.
What parameters I should give more attention on?
*http://archives.postgresql.org/pgsql-performance/2009-07/msg00323.php, 30th Jul 2009
Based on cost budget, R
Conduct initial set of experiments
Latin Hypercube Sampling
Improvement at each configuration setting
Probability density function of
Projection on 1D
Conduct NEXT experiment here
Gaussian Process tries to achieve the balance between exploration vs. exploitation
DBMSExample Mechanism set-up on Stand-by System using ZFS, Solaris, and PITR
Apply WAL continuously
Workbench for conducting experiments
Write Ahead Log shipping
Copy on Write
Experiment Planner & Scheduler
Simple Workload with one TPC-H Query (Q1)
Complex Workload with mix of TPC-H Queries (Q1+Q18)