Percona Live Online

The scalability of your application and your database is only as good as the database design you put behind it. Designing your schema, the database structures, and planning for the future need to happen early and needs to evolve over time. In today's rapid pace development cycles, the database design is often overlooked or even dismissed entirely. Databases and marketing teams tout "Schemaless Designs", database as a service, and new tech that makes caring about databases a thing of the past. I will explain why database design is as important as ever and I will give you the 8 things you need to design on every application regardless of which database or service you use.

In complex projects, the last 10% of the project is often the most difficult part. In this talk, I will share a case study of Box's 4-year effort to get rid of our legacy mapping DB and move the last piece of our legacy monolith MySQL traffic to our data access layer. This talk will cover how to manage technical risk and optimize team execution in a technically complex and operationally distributed environment. This talk will share reflections on useful tactics that led to the successful completion of this four-year migration project for others to learn from and leverage.

As a relatively new Staff engineer, I learned and experimented with building and maintaining a long time-horizon project plan, identifying unknown unknowns, and continually finding ways to de-risk the project at every stage of development.

As the project progressed, I found that successful execution depended not only on these technical strategies, but even more so on how the team operated. In the spirit of Agile and mitigating the isolation of the pandemic, we experimented with almost every aspect of how we worked: how/when we worked together, how our sprints ran, how we evolved designs, and even the minutia of how we retrospected.

In this session, we will have a candid discussion on the technical and organizational strategies that I believe were important to our success, or that were promising enough to warrant more experimentation in the future.

Participants will leave with a few ideas that they should be able to try out within their own teams. Additionally, there are some deeper ideas about team leadership and effectiveness that I hope participants will be able to reflect on going forwards.

This talk demonstrates the steps to repair a corrupted MongoDB Shard.

The main benefit of focusing on the repair of a corrupted shard as opposed to rebuilding the entire sharded cluster is obvious. Any required downtime is limited only to reading/writing of the corrupted shard and should take less time.

For the purpose of this talk, shard corruption is defined as when a shard has a collection with a different UUID as compared to the other shards, and/or different from the UUID in config.collections document for the namespace. Corrupted UUID could be the result of someone dropping the collection directly on the shard, and the restoring the collection to that shard.

A simple Google Search on “mongodb invalid uuid” would demonstrate that this problem has been encountered by multiple mongodb installations world-wide.

Query optimization is one of the most challenging problems in database systems. For many years, creating a query optimizer was considered black art, available only to a limited number of companies and products.
Not any more. Apache Calcite is an open-source framework that allows you to build query engines, and query optimizers in particular, at a significantly lower engineering cost. In this talk, I will present query optimization capabilities of Apache Calcite, including cost-based and heuristic optimization drivers and an extensive library of optimization rules. I will also present several examples of production-grade optimizers based on Apache Calcite.

Plugins are the piece of the software, which provides the additional services. MySQL has the plugins and it was matured a lot on MySQL 8. I am interested to talk all about the features of MySQL server plugins and how we can install and uninstall, how we can retrieve the Plugin information!

**My Agenda:**

1. What is the scope of plugin in MySQL?

- Will explain the role of plugins in MySQL

2. How to install/Uninstall and obtain the plugin information?

- Will explain about the plugin installation
- Will explain about the plugin uninstallation
- Will explain how to obtain the plugin information like (plugin directory, plugin is active or not, information_schema.plugins tables, SHOW PLUGINS command)"

3. Different type of MySQL plugins

- Query rewriter
- DDL rewriter ( MySQL 8 )
- Version token
- Clone plugin ( MySQL 8 )
- MySQL enterprise threadpool"

4. Plugin services:

- Locking services
- Keyring services "

5. Q/A

Storage is always an important issue for cloud applications because the stability of the whole cluster highly depends on the availability of the storage and storage is more fragile and less reliable than other parts of the server.

Therefore, emulating a storage fault (e.g. disk broken and filesystem corruption) or degradation (e.g. the slowed distributed file system) is significantly helpful to make sure applications are able to sustain failure scenarios. Injecting these errors could help developers understand and predict the behavior of these applications when the volume doesn’t work perfectly so that applications could be prepared for these disasters.

In this talk, Keao Yang will introduce IOChaos, a custom resource developed by Chaos Mesh team, and explain how it makes emulating a storage error for applications running on Kubernetes easy and painless. Also, he will illustrate how it could be used in other applications.

When one’s app is challenged with poor performances, it’s easy to set up a cache in front of one’s SQL database. It doesn’t fix the root cause (e.g. bad schema design, bad SQL query, etc.) but it gets the job done. If the app is the only component that writes to the underlying database, it’s a no-brainer to update the cache accordingly, so the cache is always up-to-date with the data in the database.

Things start to go sour when the app is not the only component writing to the DB. Among other sources of writes, there are batches, other apps (shared databases exist, unfortunately), etc. One might think about a couple of ways to keep data in sync i.e. polling the DB every now and then, DB triggers, etc. Unfortunately, they all have issues that make them unreliable and/or fragile.

You might have read about Change-Data-Capture before. It’s been described by Martin Kleppmann as turning the database inside out: it means the DB can send change events (SELECT, DELETE and UPDATE) that one can register to. Just opposite to Event Sourcing that aggregates events to produce state, CDC is about getting events out of states. Once CDC is implemented, one can subscribe to its events and update the cache accordingly. However, CDC is quite in its early stage, and implementations are quite specific.

In this talk, I’ll describe an easy-to-setup architecture that leverages CDC to have an evergreen cache.

The more companies strive to make sense of their (big) data and use it to generate insights or enhance their products, the more developers face the inability of popular databases, query engines, and developer tools to provide low latency and high concurrency for analytical queries. In this talk, you'll learn how to overcome the limitations and architectural nuances of said tools and get operational analytics capabilities for your applications regardless of the underlying data store and data volume. We'll explore how you can bootstrap performant APIs with [Cube.js](https://cube.dev?ref=percona-live), an [open-source](https://github.com/cube-js/cube.js) analytical API platform which works with any SQL-enabled database or query engine. In the end, we'll use reproducible performance testing to prove the viability of the suggested approach and reassure you that your analytical APIs can perform with sub-second latency under heavy load.

Apache Superset is a modern, open source BI platform that can talk to just about any SQL speaking database. While many BI tools opted for the technical strategy of building highly native connector libraries to support each database, the Superset project decided to bet on the SQLAlchemy ORM and the Python DB-API 2.0 spec to support many databases without much custom code.

I provide an overview of these ideas in this blog post (https://preset.io/blog/building-database-connector/) but I'd like to go much, much deeper in this conference talk and hopefully convince more database creators out there to bet on these same platforms and specs. To really drive the point home, I will also live code and add in support for a new database in Superset "on stage".

MariaDB sees about one new release each year, and each adds new features that may go unnoticed. This session will be a high-level overview of new and underused features included in recent MariaDB releases. There are far too many to cover in detail, but we will go through some you should know about if you are developing on MariaDB.

Some examples include:
* The CONNECT storage engine in 10.0
* IF EXISTS, IF NOT EXISTS, and OR REPLACE clauses in 10.1
* The MyRocks storage engine in 10.2
* System versioned tables 10.3
* Temporal tables in 10.4
* INSERT and REPLACE ... RETURNING in 10.5
and much more!

In 2015, Facebook introduced GraphQL, a front-end query framework designed to shield users from the intricacies of the various backends one would find in modern data stacks. As with ORMs before then, most backend code ended up being databases, adding layers of abstraction that ended up being inefficient and required immense investments to scale out for performance.

Additionally, most app developers are not very familiar with SQL and go to great lengths to avoid learning it. This has created several problems like n+1 queries, inefficient queries, minimal use of database features, etc. While Postgres is growing in popularity with this audience the large majority of advanced features like JSON support, Recursive CTE’s, Window functions are never used. Often developers are unfamiliar with even simple features like the various types of JOINS and choose inefficient solutions like multiple queries instead.

GraphJin, an open-source project, was developed to solve this disconnect by putting all the power in the hands of the UI/UX developer and freeing up the backend developer to focus on the truly hard problems and optimize the queries to take full advantage of the advanced features of Postgres.

GraphJin is a compiler written in Go that can convert the GraphQL describing the data needed into a single efficient SQL query optimized for Postgres or MySQL. It discovers the schema and relationship graph of the database to help it build efficient queries and provide the frontend developer an auto-complete enabled GraphQL query builder to quickly fetch the data needed.

https://github.com/dosco/graphjin

At Box, we have a fairly uncommon combination of business requirements that, when taken together, means that our relational data access layer must implement cross-shard move operations and orchestration. These moves can be large, and often need to be split across multiple asynchronous transactions. In the middle of this asynchronous orchestration, objects that would ordinarily live on the same shard, may be split across two shards. Our mapping database must faithfully record where each object currently resides, as well as the intended destination.

Viewed more generally, we have a system described by the following:
* A sharded data store;
* With a tree of relationships between object types that can be traversed upwards and downwards;
* With denormalized data that is propagated through the graph (in our case, the target shard id);
* Where the denormalized data is mutable, and might need to be updated in response to a move operation higher up in the tree;
* Where the application needs to control when and how the denormalized data is updated;
* And the application does not need to use the denormalized data in a relational fashion (it doesn't need to be indexed, used in a WHERE clause, etc.)

We recently finished developing and deploying an enhancement to our mapping system, to be able to store the denormalize data in a sparse data structure, with high read performance. When moves are not in progress, no additional data storage is needed besides the graph itself, and reads on the denormalized data are made efficient via caching. When moves are in progress, "transit node" rows are inserted into the mapping database in order to precisely record the new state of objects that have moved already, but while retaining the state of the objects that haven't moved yet. After the moves, the transit node rows can be garbage collected.

The transit node concept was carefully designed with a number of invariants, which make it very safe to cache values without worrying about cache corruption or cache invalidation. We designed the concept for ourselves to store shard IDs, but can theoretically be used for other kinds of denormalizations that match the above generalization.

We will briefly cover the context of sharding at Box, to provide the motivation for the transit node concept. The rest of the talk will present the semantics, invariants, and behaviors of transit nodes, and some results from our deployment. My hope is that the concept can be more broadly useful beyond what we originally designed it for.

Joining and contributing to an open-source project often involves a significant effort and learning curve and can often end up as a challenging experience.

Helping newcomers who may be junior or experienced developers get up to speed quickly with minimal changes to their local development environment is a desirable path and can help bring in a new generation of developers and hobbyists to sustain and grow the open source communities.

Crave Cloud is a free service that allows developers to submit pull requests to their favorite open-source projects, and receive a private build for testing and download in a fraction of the time it usually takes to build the entire project. All with minimal setup and changes to your local development environment and without taking up the precious cycles of your local machine. Using the elastic capacity of the cloud, Crave can automatically submit your changes, accelerate the build by 6-10x and return a private binary for your testing.

With Crave OSS, we hope to encourage developers to join open source projects, learn by tinkering with code and effortlessly submit changes without making changes to the core code. Join us for a fast-paced introduction to Crave Developer Cloud and get access to a free cloud sponsored by EquinixMetal, Nutanix to support CNCF projects.

HammerDB is the leading open source database benchmarking software for commercial and open source databases. Hosted by the industry-standard benchmarking body the TPC, HammerDB supports workloads derived from the transactional TPC-C and Analytic TPC-H benchmark specifications.
In this session the lead developer of HammerDB will explain what it does, how it works and how it has been designed to avoid the pitfalls so common to other database benchmarking software to deliver high performance and scalability.
Using PostgreSQL and MySQL we will walk through practical transactional benchmarking scenarios looking at the operating system and database configuration tuning and analysis giving insights into benchmarking skills that can be deployed in your own environment.
Finally, we will look at where HammerDB is going with future development and features planned for 2021 and beyond and how you can get involved in the HammerDB community to help make comparing and contrasting database performance open to all.

DevOps is about improving processes to develop and deliver quality software with both speed and stability. At face value, it's a simple concept. However, fear of instability and the desire to control databases are preventing many organizations from updating their process.
 
The fear of changing processes and automating is understandable. Databases have never been more important because data has never been more important. Every disaster nightmare a DBA, compliance officer, or PR team can imagine is wrapped around ensuring the database is safe.

In this talk, Kristyl Gomes and Robert Reeves will demonstrate why implementing standardization and automation is necessary to achieve what every team wants—speed and stability, with control.

GraphQL is a perfect language to query OLAP databases and make BI analytics on top of data warehouses (DWH). We at Bitquery built API based on GraphQL, allowing users to easily query DWH without knowledge on underlying low-level things like servers, databases, cubes and metrics.

We will share our approach, experience, tools that we used, pro and cons of this approach. Our experience will be useful for the developers and users of OLAP, DWH and BI solutions.

Customers expect to have nuanced journeys in their interaction with several aspects of sales, including ordering, shipping and payments. For example, a customer may want to order using a voice channel, send a pinned location on a map as a delivery location and rely on self-service for returns and payments. These ordering journeys are characterized by a reliance on a mesh of API-driven apps. With Dgraph, you get out-of-the-box support for GraphQL APIs.

Another unique aspect of these journeys is the iterative style of development involved. Developers rely directly on feedback from active users, and constantly update the coding artifacts involved. A major impediment to rapid iterations is the time taken by developers to implement changes made to the data model. Developers tend to make changes to the database, and then refactor the API to accommodate the changes across the Create, Read, Update and Delete (CRUD) actions. In this talk, you will learn modeling techniques using GraphQL and Dgraph that support these rapid iteration needs.

At MessageBird we love APIs and our customers love them even more! One of our APIs allows our customers to send messages in bulk and we were able to cope with that for many years. As the number of large customers increases, so does the amount of bulk messages sent by our customers. How do you scale a system that's receiving 300 messages per second and, unexpectedly, receives 4 million messages? As the API team at MessageBird started to improve the performance of their APIs it became apparent that also the database required an overhaul.

Our ultimate goal was to move this workload to a sharded system (Vitess or DIY shards) but that required an extensive overhaul that could take months to complete. The focus of this talk is the steps we took before our move towards sharding. How can you extend the life of an existing system and buy time to work on the sharding step? We will cover the topics of RFCs, read-offloading, parallel replication but also topics like understanding the implications of UUIDs on the normal workload when a customer starts pushing 4 million messages through.

Database management systems (DBMS) expose dozens of configurable knobs that control their runtime behavior. Setting these knobs correctly for an application's workload can improve the performance and efficiency of the DBMS. But such tuning requires considerable efforts from experienced administrators, which is not scalable for large DBMS fleets. This problem has led to research on using machine learning (ML) to devise strategies to optimize DBMS knobs for any application automatically. The OtterTune database tuning service from Carnegie Mellon uses ML to generate and install optimized DBMS configurations. OtterTune observes the DBMS's workload through its metrics and then trains recommendation models that select better knob values. It then reuses these models to tune other DBMSs more quickly.

In this talk, I will present an overview of OtterTune and discuss the challenges one must overcome to deploy an ML-based service for DBMSs. I will also highlight the insights we learned from real-world installations of OtterTune to tune MySQL, PostgreSQL, and Oracle.

In this talk we will review how to deploy PostgreSQL environments, to be able to have any version of PostgreSQL running within minutes... or even seconds! We will show you what cool tools we use in the Percona Support team to efficiently deploy from standalone servers to more complex replication and HA topologies. After attending, you will have all the knowledge you need to start testing your applications against fully functional PostgreSQL instances... fast!