Coffee|Code : Dan Scott

Running libraries on PostgreSQL: PGCon 2012 talk

dan@coffeecode.net (Dan Scott) — Sun, 20 May 2012 13:57:45 -0400

On Friday, May 18th I gave a talk at the PGCon 2012 conference on the use of PostgreSQL by the Evergreen project. My talk fell in the case study track, which meant that I had been asked to describe to PostgreSQL developers what Evergreen was, why it was a project they might want to care about, enumerate the advantages that Evergreen gets from using PostgreSQL, and where our project has some difficulties with PostgreSQL.

I have given a lot of talks before, but I’m used to being on the developer side of the discussion. In this case, the tables were turned; with noted PostgreSQL contributors like Josh Berkus, Chris Brown, Simon Riggs, and Robert Treat in the audience, I was a user talking to the developers of something that I was very much dependent on and which I understood at a much more basic level than they did. This was both liberating and humbling; it definitely adds some perspective to my experiences as a developer in the Evergreen project.

Along with my slides, the whole talk has been professionally recorded - both video and audio - thanks to Heroku’s sponsorship, so you will be able to relive each and every word if you really want to. I’ll summarize the main points that I wanted to convey to the PostgreSQL developers:

I was quite candid that most libraries can’t afford dedicated database administrators, and that therefore the more that PostgreSQL can provide reasonable out-of-the-box configuration settings, the better. For example, results from the survey that I sent out at the last minute (THANK YOU to the nine sites that responded!) showed many sites running with a default statistics target of 50, whereas the default had been increased to 100 back in PostgreSQL 8.1 and much higher settings are often recommended to help the planner make its decisions. That said, my survey didn’t ask for table-level statistics settings (did you know that you could change the statistics for particular tables?), so perhaps some sites are using higher statistics levels for particular tables and a lower default threshold.
It was probably hokey, but I noted that as libraries are often called the heart of their community, that PostgreSQL was effectively the heart of Evergreen — and I invited the PostgreSQL community to help our heart beat faster. With the Evergreen Oversight Board contemplating a strategic investment fund for initiatives that will have a long-term benefit to Evergreen, this might be an avenue for getting PostgreSQL experts to assist us on areas that represent particular bottlenecks (beyond helping us out of the goodness of their own hearts). As well, I invited the PostgreSQL community to join in advocacy efforts to get their local libraries to consider adopting Evergreen.
I described, at a high-level, many of the PostgreSQL features that Evergreen relies on (full-text search, stored procedures, Hstore, inheritance) and tried to convey why our schema takes up 355 tables (and counting) to deal with what, from outside a library perspective, must seem like a relatively simple problem to deal with. And of course I gave most of the credit for Evergreen’s PostgreSQL-savviness on multiple levels to Mike Rylander.

The talk was well-received, based on a number of people who approached me afterward to continue the discussion. Josh called it one of the first times he had seen a presentation designed to solicit assistance directly from the developers in attendance (I probably overplayed the "help us poor harried library system administrators" hand) and thought that it hit the mark for a case study; similarly, Simon was quite interested in Evergreen’s adoption patterns with (I suspect) an eye towards offering possible consulting in administration and optimization efforts.

On the "immediate takeaways" from that talk:

For straightforward connection pooling, pgbouncer is the current recommendation over the more flexible but more complicated pgpool-II.
Recent versions of Slony have lifted limitations that bit us in the past, like the inability to replicate a TRUNCATE command.
Solr, as a potential alternative to PostgreSQL’s full-text search, is seen as fast but brittle to manage, and adds in overhead to maintain consistency with the contents of the database. (I’m not so sure about the brittleness, given Hathitrust’s ability to run a massive Solr index, but it is worth following up on…)
Streaming replication in 9.1 has improved significantly over 9.0, although you’ll still want to have WAL archiving in case of disaster.

I have a lot more to say about the intersection of the PostgreSQL and Evergreen communities in general, but on the whole I think that a closer relationship has been long overdue. I was delighted that Ben Shum and Robin Isard were both able to attend the conference, and I firmly believe that building more PostgreSQL development and administration expertise within the Evergreen community is critical to our long-term success. While I have long been an advocate of pointing community members to the documentation of the underlying infrastructure components for specific administration recommendations, I believe that effective PostgreSQL tuning and administration is so critical to the successful implementation of a production Evergreen site that we should add a section to the Evergreen documentation containing a small set of considerations and/or processes for going into production—and I hope to start that relatively soon.

The State / Stats of Evergreen development: 2011-2012

dan@coffeecode.net (Dan Scott) — Sun, 29 Apr 2012 20:56:56 -0400

On Thursday, April 26, I was part of The State of Evergreen talk, organized by Grace Dunbar, that also included sections by the dynamic combo of Kathy Lussier, Ben Hyman, and Tara Robertson. We opened the Evergreen 2012 conference and lead into the day's featured keynote speaker Mr. Jono Bacon (who, by the way, gave a good talk about community at an important time in Evergreen's growth).

My assigned mission was, with a time limit of 5 minutes, to give the audience an update on the progress in Evergreen development since the 2011 conference. Naturally, I turned to gource to generate a visualization of the changes committed to the Evergreen git repository since April 2011.

With the visualization running in the background, I ran over the following numbers (statistics is probably too strong of a word) with the audience...

Let’s go with Stats of Evergreen development

Code contributors

Over the past year, we have seen:

2209 commits from a total of 29 different authors (8 active core committers)
9 contributors outside of the core committer group with 5 or more commits:
- Jason Stephenson - 48
- Michael Peters - 26
- Scott Prater - 20
- Joseph Lewis - 19
- James Fournie - 16
- Robin Isard - 12
- Liam Whalen - 6
- Ben Shum - 6
- Steven Callender - 5
One female contributor - Sarah Chodrow (More, please!)

Source code visualization

Features

Autosuggest for searches
TPAC - a sane, fast, functional catalogue
- Print & email & SMS record details
- Opt-in circulation & hold history
Authentication proxy - with example support for LDAP authentication in JSPAC
Custom library hierarchies, library visibility, and copy location groups
Staff client enhancements: secondary sorting columns, row numbers, double-clickery, configurable toolbars
Patron statistical categories: defaults, freetext control, required-ness
Acquisitions, MARC Batch Import/Export, and serials UI enhancements
Circulation limits

Policies and procedures

Master is always stable
- To avoid time-wasting regressions, every commit must be reviewed and tested by a second developer
Timed releases - for predictability
- One major release every six months, starting with 2.2.0
- Patch releases - no timed policy as of yet
Community support policy
- Each major release gets 12 months of full support, followed by 3 months of security patches
- Therefore, sites should plan on one major upgrade per year
Database upgrade script sanity

Communication

Developer mailing list
- 970 messages
Internet relay chat (IRC) channel
- 76,476 lines and other stats
  - tsbere and dbs in a neck-and-neck race with 13,474 and 12,062 lines, respectively
  - 26 people averaged more than one lines per day
Developer IRC meetings
- 19 meetings held

Documentation

Since last year:

12 meetings
200 commits, covering 2.0, 2.1, and 2.2
Conversion from DocBook to AsciiDoc
Single sourcing install documentation and release notes

Kudos to:

Karen Collier for direction and organization
Robert Soulliere for tirelessly formatting and publishing
Yamil Suarez for picking up the torch from Karen
Many other members of the Documentation Interest Group (DIG)

Releases

2.0 series
- April 2011 - 2.0.5
- May 2011 - 2.0.6
- June 2011 - 2.0.7
- August 2011 - 2.0.8, 2.0.9
- October 2011 - 2.0.10, 2.0.10a
2.1 series
- October 2011 - 2.1.0, 2.1.0a
- November 2011 - 2.1.1
2.2 series
- November 2011 - 2.2 alpha1
- March 2012 - 2.2 alpha2, 2.2 alpha3
- April 2012 - 2.2 beta1, 2.2 beta2

Why I donated to the Software Freedom Conservancy

dan@coffeecode.net (Dan Scott) — Mon, 26 Dec 2011 09:15:23 -0500

A few days ago I made a small donation to the Software Freedom Conservancy, a 501(c)(3) non-profit organization registered in the United States. There are many organizations to which I could have donated, and indeed Lynn and I have donated to a number of charities again this year, but I felt it was important to direct some funds to the Conservancy for a number of reasons - which I will attempt to describe and hopefully convince you as well.

First, for those who know that the Evergreen open source integrated library system is a member project of the Conservancy and the the project on which I invest much of my professional and person time, an obvious question might be: "Why didn't you just donate to Evergreen?". Donating to Evergreen does result in a small percentage of those funds being directed to the Conservancy. Currently, Evergreen directs 5% of its income to the Conservancy, but I feel that even with $20,000 passing through the project's hands for the purposes of the 2012 Evergreen conference, that $1,000 that goes to the Conservancy is far below the value our project has received in return in the form of Conservancy services.

One of those services is the provision of a trusted third-party home for project assets such as the aforementioned finances, but also including domain names, trademarks, logos, and (if desired) copyright. While distributed ownership of these assets is not a problem for projects when everything is going fine, personal disputes, a change of business strategy, or new ownership of a contributing company can lead to severe difficulties for a project. Evergreen's sister project, Koha, found itself forced to change its domain name and fight trademark battles over its very name when one company adopted an aggressive business strategy.

Another service from which Evergreen has thus far derived great benefit is access to legal counsel familiar with software freedom issues. In September the Conservancy added Tony Sebro as General Counsel to offer basic legal assistance to its member projects. The Conservancy was most recently involved in a discussion about Evergreen documentation licensing that evolved from an unfortunately adversarial position to, shortly after the Conservancy became involved, a mutually satisfactory agreement. I believe this result was due not only to Conservancy's legal expertise and familiarity with the specific licenses in question and the general mechanism of granting licenses, but also with their ability to understand the goals of the project and its participants in helping to guide all parties to their desired goals.

The Conservancy also has a wealth of experience to draw upon to offer guidance expertise on many matters that free software projects have in common, but which each project tends to rediscover on its own. For example, the Evergreen project has been able to run conferences on an annual basis for the past three years, but has historically relied on Equinox's willingess to assume the financial risks when signing venue contracts. This year, due to the positive results of the previous conferences, the Conservancy was able to provide the deposit for the Evergreen 2012 conference in Indiana. While personally I deeply appreciate the role that Equinox has played in helping to build such a core part of our community experience, it is an important step for our project that the Conservancy be able to assume this role.

In addition, the Conservancy's experience with various conference management packages and the payment fees associated with online financial services such as Google Checkout and PayPal provided some important guidance early on in the Evergreen conference 2012 planning process. That advice probably paid for itself!

I expect that the Evergreen project will continue to benefit from our membership in the Software Freedom Conservancy as we work towards a mechanism for electing members of the Evergreen Oversight Board and continue growing and evolving the project. The $1,000 or so that the Conservancy has earned as a result of the 5% of revenue that Evergreen directs its way is far below the value that we have derived from our relationship thus far, and that is why I have chosen to donate to the Conservancy again this year.

P.S. As a 501(c)(3) non-profit, donations to the Conservancy are tax-deductible for American citizens. As a Canadian, this particular benefit does not apply to me - however, the rest of the benefits that the Conservancy provides to free software projects are international in scope and deserve to be supported.

What does a system librarian do?

dan@coffeecode.net (Dan Scott) — Tue, 22 Nov 2011 16:50:23 -0500

Preface: I'm talking to my daughter's kindergarten class tomorrow about my job. Exciting! So I prepped a little bit; it will probably go entirely different, but here's how it's going to go in my mind...

My name is Dan Scott. I’m Amber’s dad. I’m a systems librarian at Laurentian University.

Today you’re going to learn what a systems librarian does. Exciting, eh?

I bet you have all been to a library. When you think about a library, what do you think of first?

Books

Yep - books! I do a lot of work with books! Can you guess what sorts of things I do with books?

Choose which books to add to the library: Selection
And when we get new books, where do we put them? : Organization
And when we have too many books, choose which books to give away: Weeding

But there’s more to libraries than books! What else can you think of that are in libraries?

Movies
Music
Magazines
Newspapers
Puppets
- Yes, puppets
Computers

Ah, computers. That’s where I really spend a lot of time. When I was a little boy, I was a voracious reader - I would read anything, including cereal boxes and encyclopedia - and I was fascinated by computers. Completely obsessed. So, naturally, when I went to high school I also got a job at the children’s library as a "computer page". I was a big kid, and I helped all the little kids use the computers at the library.

Now that I’m grown up, I’m still doing pretty much the same thing - except now I’m helping the adults use computers. Except now I’m helping them by making it easier for them to get the books or magazines or music or movies or puppets (yes! puppets!) that they need; and a lot of the time, they don’t even have to come to the library. They can read or watch or listen to whatever they need right on their computers - and sometimes they need help, but that’s what I’m there for.

10 years ago you...

dan@coffeecode.net (Dan Scott) — Thu, 20 Oct 2011 10:48:53 -0400

... walked out of a pub ...
... stood with me before the falls, in front of our friends and family ...
... looked radiant in the harvest sun ...
... said your vows (after I valiantly flicked a ladybug away) ...
... improved my life immeasurably.

Thank you Lynn. You are my sun and moon and stars.