Benchmarks & experience from prod?

[igrigorik]

Trilogy is a client library for MySQL-compatible database servers, designed for performance, flexibility, and ease of embedding.

Would y'all be willing to share some real-world experience (benchmarks, other wins) from running in production at GitHub? What motivated this, and what problems did it solve relative to the previous (mysql2?) library.

🙇🏻

[eileencodes]

Would y'all be willing to share some real-world experience (benchmarks, other wins) from running in production at GitHub?

To be honest we've been using Trilogy for so long in production (since 2012) any benchmarks comparing mysql2 and trilogy in production are lost. However there is an isolated benchmark here https://github.com/github/trilogy/blob/9380a5844c844dd13ddd6dc761895898b17186a8/contrib/ruby/script/benchmark that compares mysql2 and trilogy.

What motivated this, and what problems did it solve relative to the previous (mysql2?) library.

The main motivation was building a library that didn't depend on libmysql. We also have added some features to Trilogy that aren't available in mysql2 like GTID tracking.

In addition, the mysql2 library is currently not well-maintained so we figured this was a great time to open source trilogy. The adapter for Rails will be open sourced soon, we are in the process of cleaning up a few things.

[brianmario]

👋 @igrigorik! Been a while! 😄

What motivated this, and what problems did it solve relative to the previous (mysql2?) library.

I can speak to this, somewhat anyway. I was one of the original creators of this library back when I still worked at GitHub. I had the idea of this library in my head for years, before having the opportunity to finally sit down and write it (with the generous help and direction of @haileys).

To be completely hoenst, one of my longest standing frustrations with maintaining mysql2 was around compilation issues folks had linking against libmysqlclient. It didn't matter the platform, it was a huge pain point and we went to great lengths in extconf.rb to try and make it less painful. Which only seemed to help for the most common installations. (cc @sodabrew)

On top of that, the concept of "non-blocking" with libmysqlclient was a bit of a undocumented hack. The library itself doesn't (safely) expose a non-blocking API and I wanted to be able to be as compatible with the Ruby GVL as we could, let alone other runtimes. I liked the direction libdrizzle was going, but it - at least at the time - was unmaintained.

The high level goals of what I wanted were:

• Expose a protocol-level API for parsing/building MySQL (client side) packets. That way an author could use whatever IO backend they wanted.
• Expose a fully non-blocking client API. The goal being that the author could use something like libev or libevent to create the socket and wait on socket readability/writability. Maximizing compatibility in runtimes like node. The Ruby bindings use this API and rb_wait_for_single_fd for readability/writability and compatibility with the GVL.
• Expose a higher-level blocking client API, for convenience. We went with poll(2) for this API as that seemed to be the most portable.
• Be written in C99 for portability.
• Be as close to zero-copy as possible.
• A Makefile target that combined everything into a single .h and .c file to make it as easy as possible to embed into another project without linking requirements. I'm not sure this target exists yet, but we do something similar when building the Ruby bindings.
• If at all possible, be Windows-compatible with all of the above.

For all intents and purposes, this project supersedes mysql2 for me. It's the culmination of my learnings from mysql2, with a flexible backend to allow for everything I wanted to do with it but couldn't because of libmysqlclient.

Would y'all be willing to share some real-world experience (benchmarks, other wins) from running in production at GitHub?

As for this, if GitHub would be willing to share any stats specific to the execution of this library's code that would be awesome. Assuming they exist. If memory serves, the execution and allocation performance once deployed were slightly better than mysql2 and libmysqlclient. As it is currently designed, a trilogy_conn_t* instance uses a single shared buffer for reading/writing packets. I haven't scoured this code since it was released open source, but if memory serves that buffer is the only place allocations happen (lazily).

That all said, as far as I know this code has been in production for the main web app (and background job queue) since around when @eileencodes said - 2012 or 2013 by the time it was fully rolled out I think? - and has likely processed trillions of MySQL packets by now. Including the ones used to serve this page 😎

[igrigorik]

Thank you both, this is all really helpful context and pointers. Perhaps the only thing I'll add is that it might be useful to capture some of the above in the readme. I suspect others would benefit and won't know to look into closed issues.

For all intents and purposes, this project supersedes mysql2 for me. It's the culmination of my learnings from mysql2, with a flexible backend to allow for everything I wanted to do with it but couldn't because of libmysqlclient.

❤️

[tobi]

Great library. Thank you for sharing. This is important infrastructure work and it's really appreciated.