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

Announcing the Tizra Publishing Webinar Series 2017

This September Tizra is launching a new educational program for its association publishing clients and others interested in digital publication best practices. The free series of three monthly webinars led by publishing expert Thad McIlroy is directed to help you become a more effective publishing manager by examining best practices and new trends in online publishing. The first webinar in the series is " The 5 Top Issues Facing Association Publishing Management and How ToTackle Them! " and it takes place on Thursday, September 28 from 1pm - 2pm ET. Registration is free. Description: It’s never been a better time to be an association publisher. The tools, technologies and formats bring information and knowledge to members in record time and in multiple formats. There are challenges, but, as the saying goes, challenge brings opportunity. When Tizra talks with association publishing managers these are the top issues we hear about: 1. Enabling discovery via se

See Tizra at the Frankfurt Book Fair

Guten Tag! If you are attending the Frankfurt Book Fair and thinking about your digital publishing strategy, we’d like to meet with you to tell you about all the great things happening at Tizra including: Our recent partnership with HighWire to power their Folio ebook platform (see below) How Goodheart-Willcox uses Tizra to create digital first content Exciting new features such as an improved e-reading experience , new mobile responsive design templates , and new APIs for faster uploading and better design integration Plus, some big news we can't tell you about until the show! Find Tizra at: The American Collective Stand Hall 8.0 S31 or email us at carlos.martinez@tizra.com to arrange a meeting. Please join us in congratulating HighWire on the launch of Folio! Built on the HighWire Open Platform and leveraging Tizra for ebook integration, Folio is a flexible, scalable, ebook solution, providing a user-friendly, intuitive reading experience t

The New York Botanical Garden Press Signs with Tizra

Famed for its plant collections, architecture and setting, The New York Botanical Garden is not only an exceptionally beautiful place, but a place for cutting edge research. The New York Botanical Garden Press communicates discoveries made there and elsewhere, and with three journals, five book series and more than 200 books, it is one of the largest publishers at any botanical garden in the world. We're thrilled to announce that this renowned institution has chosen to begin making its publications available online with Tizra's Agile PDF. It's pretty exciting when an organization that's been around since 1896 decides to work with one that's been around since 2006. But consistent with their forward thinking spirit, we get the sense they're pretty excited, too. "I am convinced that online publishing is a key part of our future," said Nate Smith, associate director of the press. "We've wanted to make our books available online for years,