Skip to main content

Tizra gets faster

Non-technical summary: things are lots faster at Tizra sites and admin tools. There's certainly more to do, but we've got more tricks up our sleeves! Because the big current speed boost is related to one cause, and it took me a while to track down, the geek appendage to this post describes what we found and how we fixed it.

Geekly details

I spent a bunch of time last week looking at system performance. As we've been adding customers and usage, we were beginning to feel the pinch. Performance always varies, but the range of response times was getting wider as things slowed, leading me to think that there might be some systemic issues that would give us a quick improvement (and indeed there was some Linux tuning that helped a bit). But data access seemed to be the real issue, so I spent a bunch of time looking into hibernate, and our caching and querying, and then wound up spending a day or so basically watching all the queries go through Postgres. And you know what? most of them seemed much slower than they should be, even though they are pretty hairy.

Of course, the next step was to check for database indexes, and how the query plans were using them. But in hand testing the plans looked good, and the indexes were sensible. But when run by hand the queries were also significantly faster than when hibernate ran them! This was much easier to see now that we have a live load, which is inevitably different from a test setup. So why the difference? Postgres was ignoring our indexes only when Tizra publisher made the queries.

Turns out that there's an old bug in Postgres where it would ignore indexes on bigint fields in prepared statements unless there was an explicit data type cast. (That type confusion was an obscure result of skew between Postgresql and the SQL standard.) And that was the behavior I was seeing, even though we were using a much more recent vintage of all the software. This was terrible for us, because we have a multi-tenant publishing system for large document collections and we use bigints as primary object identifiers!

So, why the old problem if the bug is gone, and we are not using postgres 7? It turns out that we dynamically build those hairy queries, in HQL (hibernate query language), using the String trick. But nowadays instead of making your indexes work, it breaks them! The differences are invisible in the SQL. It turned out that we were in a version "donut hole." Our database was recent enough so the String trick worked the opposite way (preventing fast queries for our prepared statements), but the JDBC driver wasn't making the calls in the right way to make the old trick work. End result: we're now running the latest JDBC driver with compatibility options set while we update our hairy query generator. And now we can really start tuning our setup!

If the web had not provided the history of the old bug, I would have had a much worse time even knowing where to look to find our somewhat subtle configuration issue. So enjoy the speedup, I sure am!

Comments

Anonymous said…
This is pretty wierd, any chance you can post the exact version numbers of Postgres, Hibernate, and JDBC involved?

Popular posts from this blog

Texas Tech University Center Goes Digital and Reduces Print Budget by 80 Percent

CCFCS curriculum materials hosted by Tizra are winning raves from teachers. After 44 years of empowering teachers with print materials that were aligned with key instructional goals, Texas Tech Curriculum Center for Family and Consumer Sciences (CCFCS) made the bold decision to go 100 percent digital using the Tizra digital publishing platform. At first, the task seemed daunting.  “We printed out a copy of each of the curricula and some of them were four inches thick,” says center director Patti Rambo.  In addition to a massive quantity of materials for its 33 courses with 300-350 teaching strategies per course, the school also needed to meet aggressive revenue goals and appeal to a diverse customer base. The Center’s search for solutions was exhaustive until they were directed to Tizra. “Tizra is flexible enough for us to make up the rules as we go along,” said Rambo. “We were able to design our pages so there’s less scrolling, and we were able to color code the courses

Case Study: ARL Walks the Walk on Accessible Content

The Association of Research Libraries (ARL) provides leadership in public and information policy to 125 research and academic libraries in the U.S. and Canada. A few years ago, in an effort to increase community engagement and reduce costs, ARL made the move from print to digital publications using the Tizra platform. "Our goal was not just to go e-only, but to get there with enhanced functionality," said Publications Program Officer Lee Anne George. When evaluating digital publishing platforms, ARL had some critical requirements including: Support for existing publication formats A full e-commerce platform supporting both free and paid content, as well as password- and IP-authenticated access for individual users or entire organizations Mobile responsive design support Full-text searching including relevancy ranking and linking to specific pages in search and social sharing Support for ARL's digital accessibility mandates After extensive testing, incl

Webinar Sneak Preview: Strategic SEO - Increasing Your Organization's Visibility

Q+A with SEO expert Pam Long of True Digital We caught up with Pam Long and asked her to answer just a few SEO-related questions in advance of her upcoming webinar - Strategic SEO: Increasing Your Organization's Visibility . In this session, Pam will focus on the SEO challenges faced by association and mission-based publishers and organizations, and the Q+A below is just a small taste of the information she has to share, and she'll be taking your questions throughout the webinar! To register for this free webinar which takes place on Thursday, October 12 from 1pm ET - 2pm ET, click here . 1. What's the biggest SEO challenge facing association/mission-based publishers? You need to face the reality that there are organizations out there that have been generating content with the intent of being found far longer than many associations have had digital content available at all. You’re competing against the likes of Google Books and Amazon, who are very, very good