The intro question of the night was "what is your favorite cupcake flavor?" The majority of answers were chocolate with blue frosting and sprinkles, which Dan Colish brought that night. Dan tried to distract us by talking about materialized views while people were munching on cupcakes. He successfully gathered people's attention by talking about materialized view's benefits for decision support, OLAP, replication, load balancing, and controlling data access.

Reminder: There will be no meeting in Decembers, but everyone is invited to the Winter Coders' Social Tuesday, December 8, 2009 from 6–11pm at NedSpace Old Town. Details for signing up are here: http://calagator.org/events/1250457765

So previously I attempted to survey the effects of checkpoint_completion_target. As Greg Smith explains, we want to more closely examine the response times rather than the throughput, so lets look at the data.

Making tables in this blog is hard, the table tags aren't "redone" well. Sorry, I'm not sure how to make it look better...

So just to have some sanity, let's just make sure the throughput numbers aren't out of whack.

checkpoint completion target throughput (notpm) length of checkpoint % of checkpoint_timeout

0.1 14908.38 2:42 4.5%

0.2 14929.24 3:16 5.4%

0.3 13713.73 3:49 6.4%

0.4 14102.67 4:21 7.2%

0.5 15002.47 4:50 8.1%

0.6 14898.07 5:22 8.9%

0.7 14898.82 5:53 9.8%

0.8 14619.40 6:23 10.6%

0.9 14970.14 7:46 12.9%

Don't know what happened when checkpoint_completion_target was set to 0.3. Otherwise there isn't a dramatic change in the throughput. Another piece of data that may be of interest is the percentage of the checkpoint_timeout that the checkpoint actually took place over.

So now lets take a look at the 90th percentile response times for each transaction. Greg was pointing out the effects on the maximum response time, but I thought it would be easier to report the 90th percentile response times, which may or may not be more interesting. It was certainly easier to copy. :)

checkpoint completion targetDelivery New Order Order Status
Payment Stock Level

0.1 6.88 6.93 6.78 6.78 6.60

0.2 7.51 7.53 7.35 7.33 7.26

0.3 20.21 20.24 19.96 19.96 19.82

0.4 9.33 9.24 9.10 9.14 8.93

0.5 7.44 7.57 7.26 7.29 7.11

0.6 8.04 8.16 7.97 7.94 7.84

0.7 8.45 8.47 8.29 8.24 8.12

0.8 9.21 9.26 9.03 9.01 8.93

0.9 8.70 8.67 8.50 8.47 8.36

It sort of looks like (except for the 1 obviously bad result) that the 90th percentile of response times are increasing as the checkpoint_completion_target is increased. I'm not sure this is what is expected. Maybe the results are fluctuating too much for this to be useful...

Here are the links to the raw data:
0.1
0.2
0.3
0.4
0.5
0.6
0.7
0.8
0.9

The slides from the 6th presentation given at Portland State University on May 14, 2009 are available online. This presentation surveys some tuning parameters used with DBT-2.

This concludes the speaker series at this time. It was a blast. Thanks to PSU and everyone for coming!

The fifth presentation about the Linux filesystems given at Portland State University on April 9, 2009 is now online.

The next presentation is Thursday May 14th, at 7pm, and will survey a few PostgreSQL tuning parameters and the effects on an OLTP workload.

Thursday, May 14 - 7:00 PM
Portland State University, 1900 SW 4th Avenue,
Fourth Avenue Building – Room 86-01

The slides from the 5th presentation given at Portland State University on April 9, 2009 are available online. One line summary would be that it presents data on various Linux filesystems and hardware RAID configurations in a way that you might want to see if your system behaves the same way.

The fourth presentation about the DBT-2 workload details given at Portland State University on March 12, 2009 is now online. It is a live demo of using the DBT2-2 kit and looking at the results generated.

The next presentation is Thursday April 9th, at 7pm, and will look at basic Linux i/o performance data including topic such as disk configuration, software and hardware RAID, as well as a few other items.

Thursday, April 12 - 7:00 PM
Portland State University, 1900 SW 4th Avenue,
Fourth Avenue Building – Room 86-01

Kristin Tufte will be speaking at the next PDXPUG meeting at FreeGeek
(at 7:00pm -
1731 SE 10th Avenue, Portland, OR).

-----

PORTAL is a 900 GB PostgreSQL archive of transportation-related data.
Data in portal includes: freeway speed and volume data (received in
real-time from the Oregon Department of Transportation), weather data,
bus data, accident data, and more. The PORTAL schema was developed
years ago and needs to be revamped. Further, PORTAL has performance
problems likely caused partly by the fact that it lives on a shared
SAN. For this meeting, I.ll show the PORTAL schema as it is now and
then we can have some fun tearing it apart and figuring out how it
could be

Looking forward to seeing everyone there....and of course, drinks at
the Lucky Lab (http://www.luckylab.com/ ) at 915 SE Hawthorne Blvd.
afterwards.

The third presentation about the DBT-2 workload details given at Portland State University on February 12, 2009 is now online, as well as the slides.

The next presentation is Thursday March 12, at 7pm, will be a live demo of the dbt-2 kit. Slides have be prepared early:

THURSDAY, MARCH 12 - 7:00 PM
PORTLAND STATE UNIVERSITY, 1900 SW 4th AVENUE,
FOURTH AVENUE BUILDING – ROOM 86-01

This is a reminder that the 3rd presentation about DBT-2 performance at Portland State University will be next week, Thursday March 12th, 2009. This presentation will be a live demo on how to use the kit to test your system and product a report or results. This is a live demo so slides will be shown. But I will provide some slides now to outline some of the things covered in this kit. People who are able to attend may wish to download and print out a set for notes. (See embedded links for viewing the slides online and downloading the slides.)

Catching up on presenting some data (you can see these were run earlier in the month) about the effects of the checkpoint_completion_target:

checkpoint_completion_target: notpm
.6: 8955.68
.7: 9041.58
.8: 8912.40
.9: 9014.41

Changing the checkpoint_completion_target doesn't seem to be significant, but I'm am wondering if any checkpoint tuning parameters will be significant while the transaction logs remain on the same devices at the rest of the database. We will be testing that soon...