Reporting LIVE from OSBridge...we had the PostgreSQL BOF at the same time as our monthly PDXPUG meeting. 6 regulars, a new person, and a couple of out-of-towners were in attendance. Josh B gave us a quick overview of 8.4. 293 (or 297!) new features. In addition to the big stuff (like windowing functions) there are a ton of little administrative tweaks. I am particularly excited about the new functionality of \df - displays only user-defined fuctions.

Announcements:
PgDay is Saturday Sept 19 in Athens, GA! Make your plans now.

Michael Brewer highly recommends the use of a tuba as a booth prop. Ask him to tell you the story.

Selena & I are working on PostgreSQL equivalents for the queries in Stephan Faroult's _Refactoring SQL Applications_.

I was disappointed to learn that TrustTheVote is running on MySQL. Granted, it's an improvement over current voting software, but maybe we want to talk to them about running on a different, say, more elephantine database?

Future plans:
Josh told us about the patch review process. You don't have to be able to read C! If you can read the spec, apply the patch, verify that it works, proof the docs - you can be a reviewer! All you need to do is write a report. Webb suggested PDXPUG have a patch review party - sounds like a great idea to me!

June's meeting will be at the usual time (7pm), but will be held at OSBridge. BoF format.

http://opensourcebridge.org/sessions/274

(You don't have to be a registered conference attendee for the BoF, so come on out!)

This month, Len Shapiro talked with us about the Intro to Databases class that he teaches at PSU. This class is a requirement for all CS students. Len uses real-life data - a version of the FEC database - to give the students a more realistic experience with how databases function in the real world. The class is taught with PostgreSQL as the DBMS, but students may use a database and scripting language of their choice for their class project, a database-backed web application.

Len takes a different approach by teaching SQL first, then relational algebra. The idea is to mimics the way the DBMS works: it translates SQL into relational algebra. The students work with the same database throughout the class session, adding keys, dealing with NULLs, creating indexes, working with crappy or missing data, troubleshooting slow queries - all problems they might encounter in the real world.

Thanks for the great discussion, Len!

The "secret word" for next month's meeting is: COBOL.

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!

Where: FreeGeek
When: Thurs May 21, 7pm

Beer afterwards at the Lucky Lab (a mere 2-3 blocks away!)

What Len's going to talk about:

I'll survey how I teach the introductory database course at PSU. My goal for the talk is to elicit suggestions for how I could do a better job. The theme of the course is "transforming data into Information". I use a 200 Meg database, hosted on PostgreSQL, instead of the one-slide databases used in typical intro courses, to illustrate the principles of the course. The database is Federal Elections Commission data re donations to candidates, so queries often reflect real questions about the real data.

Bio: Len Shapiro has been a professor at PSU for 23 years. His research interests are primarily in query processing.

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

We had 20 people at this month's meeting, nearly an SRO crowd!

John won the PUG ticket to Bridge http://opensourcebridge.org/. As usual, Wheeler won the introductions.

Then we were on to our feature presentation: Chris May on the challenges he faced with a very large MySQL database which he interited. (Hint: This isn't something you want Aunt Mabel to leave you in her will.) This was probably the scariest presentation we've had to date. Granted, some of the issues were version-related, and there were some people-handling speedbumps (trust between sysadmins & DBAs, no F2F time, etc.), but overall this presentation just crushed me. For starters, Chris discovered that row-level locking on his version of MySQL was per-server-instance: users of the other database on the same server were locked out when he was working in his sandbox database. Then he had to wait 3 days for a rollback. It's enough to drive one to drink. In fact, I believe I'm going to go pour myself something & let Chris's slides handle the rest of this.

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.

PDXPUG
Where: FreeGeek, 1731 SE 10th
When: 16 April 2009, 7pm
What: Tales from the Crater: Or how PostgreSQL can solve your MySQL woes

Chris will present real-life experiences maintaining a large transaction-based MySQL database and efforts to port the data to PostgreSQL. He'll discuss helpful features in both database solutions and workarounds for those that are missing. Who knows - we may even find some areas PostgreSQL can improve to be more competitive!

As always, beer afterwards at the Lucky Lab, 3 blocks away.

Who: Chris May
I am a database expert for NVIDIA, working in Beaverton. I consult with other programmers to achieve better performance and reliability for internal database-driven tools. I have worked extensively with MS SQL over the last 14 years and to a lesser extent with MySQL and PostgreSQL. I currently maintain a mix of databases using all three solutions. I speak three languages fluently, which has actually helped my ability to learn programming languages (It's all just vocabulary and syntax, after all). It also adds depth to my store of swear words when I debug particularly annoying code.

Chris's haiku bio:
Cali transplant true
lives the life of a true geek
make my SQL sing