The main objective for TiDB 3.0 is to provide a distributed database that can achieve stability at a large scale. In this deep dive talk, Ed will share the relevant benchmarks that shows how we are achieving this goal and the shortcomings exposed from these benchmarks that our team is fixing and addressing. (No benchmarketing, we promise!)
In an era where threats and challenges seemingly about protecting your big data and open source workloads, is no longer an optionâ€¦ It is vital to the success of your business. Join Veritas for this information-packed session, where we will discuss the why, how and business impacts of protecting today's business-critical big data and open sources workloads. We will also discuss how to move beyond basic replication to a true data protraction and application recovery strategy.
MySQL is the world's most popular open source database and Kubernetes is the most popular and rapidly-developing project currently. The purpose of this talk is to explain and demonstrate how running a complex stateful application such as a database is made easier using Kubernetes and that there are a number of options available.
MySQL deployment patterns covered will start with explaining simple how to run MySQL with a simple command using a helm chart; onto how a MySQL asynchronous replicated master/slave MySQL pattern works on Kubernetes; onto how several different MySQL operators can be used giving a detailed discussion; and demonstration will showcase the Oracle MySQL Operator which uses group replication and the MySQL router, and makes creating MySQL clusters, backups, and restorations trivial.
Last to be covered will be project Vitess which is used for horizontal scaling of MySQL which has numerous benefits such as built-in sharding and shard management, connection-pooling, query sanitization.
MySQL 8.0 is a major release of new features and capabilities, including a new data dictionary hosted in InnoDB, new REDO logs design, new UNDO logs, a new scheduler, descending indexes, and much more!
Learn all about the changes in InnoDB delivered with MySQL 8.0 and how they affect the performance and the manageability of your database!
Running an analytical (OLAP) workload on top of MySQL can be slow and painful. A specifically designed storage format ("Column Store") can significantly improve analytical queries' performance. There are a number of opensource column store databases around. In this talk, I will focus on two of them which can support MySQL protocol: MariaDB ColumnStore and ClickHouse.
I will show some realtime benchmarks and use cases, and demonstrate how MariaDB ColumnStore and ClickHouse can be used for typical OLAP queries. I will also do a quick demo.
Percona XtraDB Cluster 8.0 (PXC-8.0) is the latest addition to PXC family.
Starting MySQL/PS-8.0 upstream has done a lot of significant changes including atomic DDL, replication channel, locking algorithm changes, security/ encryption, etc....
During this session, we will explore how these changes affect PXC-8.0, what has changed in PXC-8.0, new and deprecated features, important bugs and more. If you are already a PXC user or planning to consider it attend this session to findout more about PXC-8.0.
Building a robust and reliable distributed database is not easy. In TiDB, to ensure our users' data is always safe and the system is always stable, we use Chaos Engineering to help uncover system-level weaknesses before they appear in production environment.
In this talk, I will cover the different types of fault injection techniques our team uses to test TiDB, how we build our own Chaos Engineering platform, and how we integrate various Chaos Engineering techniques into our own automated testing framework, called Schrodinger, to support continuous testing of TiDB.
Are you a user of the world's most popular Cloud provider, and someone who leverages their Database As A Service platforms of RDS or Aurora? Come to this talk to hear how PMM can provide rich visibility of these platforms for MySQL and PostgreSQL. We'll cover:
* Connecting PMM Server to RDS and Aurora for MySQL and PostgreSQL metrics activity
* Accessing the AWS CloudWatch API for fundamental resource consumption monitoring (CPU, Disk, Memory)
* Using PMM's Query Analytics against RDS MySQL or Aurora MySQL
The evolution of MySQL replication shows that there is a lot of effort put into reducing operations cost and minimize administration overhead. Thus MySQL DBAs and DevOps can spend more time expanding their infrastructure, rather than catering for it.
Many different areas have been enhanced. For example security, operations, fail-over, observability, failure detection, consistency, split-brain protection and primary election, flexible replication workflows and more.
This session highlights the new replication features in MySQL 8.0. Those that were released pre and post-GA. Come and learn, directly from the engineers, how the new features help you operate, sustain, and extend your MySQL Replication infrastructure.
MariaDB 10.4 will come with new Galera Replication version 4. This presentation will outline the new features of Galera 4 Replication as present in MariaDB 10.4 and share the early user experiences with it.
Galera is a generic replication plugin, making it possible to deploy synchronous multi-master cluster topologies with database servers supporting Galera Replication plugin API (i.e write set replication, wsrep API) . Currently both MySQL and MariaDB servers have Galera Replication support, and today there are thousands of MySQL and MariaDB based cluster installations, around the world, processing production system loads in bare-metal or cloud deployments.
With Galera 4, MariaDB 10.4 cluster further extends the capabilities of the synchronous Galera replication. The most prominent feature in Galera 4 version, is streaming replication technology, which implements distributed transaction processing within the cluster. With streaming replication, a transaction can be launched to execute in all cluster nodes in parallel. With this, a large transaction can be executed in small fragments due out the transaction life time, and cluster will not choke with the replication of one large transaction write set, as happened in earlier Galera Cluster versions.
Streaming replication works as a foundation for many more features, to be released in short term. e.g. XA transaction support will now be possible thanks to streaming replication technology.
ProxySQL, the high performance, high availability, protocol-aware proxy for MySQL is now GA in version 2.0 .
This version introduces several new features, like causal reads using GTID, better support for AWS Aurora, native support for Galera Cluster, LDAP authentication and SSL for client connections.
This session provides an overview of the most important new features.
Mailchimp has grown from small company to serving millions of micro-businesses, in addition to SMBs and Enterprises. We have a fairly pedestrian approach to MySQL, but we now run hundreds and perhaps soon to be thousands of MySQL instances. Our present state is thanks to great full-stack engineering teamwork. This is a glimpse into what makes Mailchimp tick. Hint, it's not just technology. This talk is about "Momentum" and "Pragmatism", and creating a great outcome for our mission of empowering the underdog.
Vitess has continued to evolve into a massively scalable sharded solution for the cloud. It's is now used for storing core business data for companies like Slack, Square, JD.com, and many others.
This session will cover the high level features of Vitess with a focus on what makes it cloud-native.
We'll conclude with a demo of the powerful materialized views feature that most sharded systems have yet to solve.
This session will be interesting to everyone looking for the latest news about MySQL 8.0 Performance:
- since MySQL 8.0 we moved to "continuous release" model
- so with every update many new improvements are delivered
- but how does it also improve MySQL 8.0 Performance ? ;-)
- the latest benchmark results obtained with MySQL 8.0 will be in center of the talk
- because every benchmark workload for MySQL is a "problem to resolve"
- and each resolved problem is a potential gain in your production !
- many important internal design changes are coming with MySQL 8.0
- how to bring them all in action most efficiently ?
- what kind of trade-offs to expect, what is already good, and what is "not yet" ?
- how well MySQL 8.0 is able to use the latest HW ?
- could you really speed-up your IO by deploying your data on the latest flash storage ?
- these and many other questions are answered during this talk + proven by benchmark results..
- and as usual, some surprises to expect ;-))
Running MySQL in the cloud isn't magic - it can be brilliant, but it can also be a real challenge. We'll learn about some of the big wins that can be had in the cloud (such as elasticity and easy provisioning). But, we'll talk about the dark underbelly as well - and face some of the challenges to run a realistic MySQL installation in the cloud (errrr... performance?).
TiDB is an open-source NewSQL database that speaks the MySQL protocol. However, it does not have any source code in common with MySQL. This presentation will dive into how SQL processing occurs in TiDB, from the initial query parsing to retrieving rows from TiKV as part of execution.
This talk will focus on the self-managed nature of Uber's database monitoring and how we've leveraged our open source time series database M3DB to support massive multi-region scale and high cardinality monitoring.
We'll cover how we monitor applications, databases, and their interactions and how we automatically setup application specific dashboards and alerts. This includes things like the ability to alert on metrics like P99 latency and slow queries for a given application at the per-table and per-query level.
Of course, automated and fine grained monitoring requires the ability to ingest, persist, and query massive amounts of high cardinality time series data. We'll talk about the architecture of M3DB and how we've leveraged it at Uber to scale our monitoring systems to billion of unique time series and 10s of millions of data points per second.
We'll conclude the talk with an overview of our Prometheus and Kubernetes integrations, explaining how you can start leveraging M3DB for your own workloads. Finally, we'll give a brief overview of our plans to evolve M3DB into a general purpose, horizontally scalable event store.
All major considerations and decisions made by the MySQL Query Optimizer may be recorded in an Optimizer Trace. While EXPLAIN will show the query execution plan the Query Optimizer has decided to use, MySQL's Optimizer Trace will tell WHY the optimizer selected this plan.
This presentation will introduce you to the inner workings of the MySQL Query Optimizer by showing you examples with Optimizer Trace. We will cover the main phases of the MySQL optimizer and its optimization strategies, including query transformations, data access strategies, the range optimizer, the join optimizer, and subquery optimization. We will also show how optimizer trace gives you insight into the cost model and how the optimizer does cost estimations.
By attending this presentation you will learn how you can use information from Optimizer Trace to write better performing queries. The presentation will also cover tools that can be used to help process the vast amount of information in an optimizer trace.
GTIDs were introduced to solve replication problems and improve database consistency.
When, accidentally, transactions occur on a replica, this introduces GTIDs on that replica that don't exist on the master. When, on a master failover, this replica becomes the new master, and the corresponding binlogs of the errant GTIDs are already purged, replication breaks on the replicas of this new master, because those missing GTIDs can't be retrieved from the binlogs of this new master.
This presentation will talk about GTIDs and how to detect errant GTIDs on a replica (before the corresponding binlogs are purged) and how to look the corresponding transactions in the binlogs. I'll give some examples of transactions that could happen on a replica that didn't originate from a primary node, explain how this is possible and share some tips on how to avoid this.
Basic understanding of MySQL database replication is assumed.
From the very beginning, TiDB was designed with the combination of Hybrid Transactional and Analytical Processing (HTAP) workloads in mind. However, since TiKV stores data in a row-oriented fashion, you may have been left wondering how well suited that is for analytics?
In this talk, I will introduce TiFlash which is a native extension of TiDB that offers column-oriented storage to speed up heavy-duty OLAP queries. I will then go over the architecture and some of the design decisions, and how you will be able to use it for your next generation data processing needs.
In this presentation, we'll cover the following areas:
1. RDBMS history and landscape (Oracle and MySQL) at PayPal
2. Current and future use cases of MySQL in PayPal
-- back office
-- internally facing applications
-- 3rd party applications
-- PayPal site applications
3. PayPal's effort of making MySQL as a first class data store. We'll cover the active/active and active/passive architectural choices, managing a large number of application to database connections, enforcing security, backup and recovery, etc.
4. Outlook of MySQL in PayPal
Near the end of 2018, Amazon announced they would be completely off Oracle by the end of 2019; replacing Oracle's database products with their own AWS-based services. Interestingly, Amazon's move to run entirely on it's own, internally operated data services is consistent with a â€œdesireâ€ voiced by many large companies we've spoken to over the past 18 mos. A major impediment for these companies to move forward, however, is that they want their databases to operate in their own data-centers, be open-source, and deploy-able/operable as a Cloud-Native, Database-as-a-Service (DBaaS) offering. How can a company deploy/operate data services that satisfy the requirements they currently address with their proprietary database portfolio? In Amazon's case they are moving forward by replacing proprietary database software with a combination of AWS DynamoDB and Aurora for transactions. For data warehousing, Amazon is using a combination of AWS Redshift and Athena. Are their existing alternative, open-source projects for each of these components? In the case of DynamoDB and Aurora, yes, the Apache Cassandra and MySQL/MariaDB projects can be used for this purpose. For data warehousing, PrestoDB and the Apache Impala projects can be used in place of Athena and Redshift. All of these depend on a combination of the following additional data services: local caching, event streams, and a REST-based object store. Enter Rockset's RocksDB-Cloud. For the past year, we have been working to enable MariaDB and Percona's MySQL distribution to extend their respective distributions of Facebook's MyRocks storage. The RocksDB-Cloud library is configured to load the RocksDB-Cloud library, which is binary-compatible RocksDB; extending it with support for local caching as well as maintaining a consistent image over an event stream and an Object Store â€œbucket.â€ An instance of MariaDB/MySQL operates against local storage (aka the "cacheâ€). The instance's WAL is mapped on to an event stream and a consistent copy of its LSM-tree is maintained in the bucket. In 2019 we are looking to extend this approach to Cassandra and Postgres using the PGRocks and Facebook's Rocksandra projects respectively. Why Intel? The local cache and event stream capabilities of the architecture described above map nicely on to Intel's upcoming Optane DC Persistent Memory product. The use of a REST-based object store affords many opportunities to cost-optimize warm data that needs to remain online but accessed with decreasing frequency over time. As for data warehousing, AWS's Redshift and Athena exploit Amazon's s3 object store. The idea here is similar in that the REST-based object store presents an s3-compatible interface to Impala and PrestoDB as well as exploits the solution's local caching capability. What other technologies enable this solution? First and foremost is availability of low cost, relatively low latency, high bandwidth networking that can be employed as scalable cluster interconnect with full bisection bandwidth. Secondly, an Orchestration/Scheduling framework based on Kubernetes (k8s) has emerged as a key enabler. We combine k8s with cluster-wide software defined network (SDN) and software defined storage (SDS) implementations to fully exploit the resources available within the physical cluster. Finally, we employ the Vittes-Based Database-as-a-Service (DBaaS) framework to manage databases operating over the cluster. In this talk, we'll briefly review the architecture. We'll then discuss the current implementation with particular focus on the trade-offs we made for the local cache, the WAL/event-stream, and the REST-based object store. We'll end the talk with a discussion on plans for 2019 and beyond.
Have you struggled with identifying issues in MySQL?
Come listen to how Verisure experienced an issue that was identified and resolved using PMM (Percona Monitoring and Management). Verisure was able to find the offending query/tuning parameter, make modifications, and then observe the impact over time thanks to PMM.
Optimizing MySQL performance and troubleshooting MySQL problems are two of the most critical and challenging tasks for MySQL DBA's. The databases powering your applications need to be able to handle heavy traffic loads while remaining responsive and stable so that you can deliver an excellent user experience. Further, DBA's are also expected to find cost-efficient means of solving these issues.In this presentation, we will discuss how you can optimize and troubleshoot MySQL performance and demonstrate how Percona Monitoring and Management (PMM) enables you to solve these challenges using free and open source software. We will look at specific, common MySQL problems and review the essential components in PMM that allow you to diagnose and resolve them.
Integrating the most suitable highly available multi master, non clustered, freely accessible
relational database management system (RDBMS)
solution for large scale environments is a challenging task; it
resembles evaluating marathon champions trained by different olympic coaches.
This presentation will show the analogies and differences of
MySQL Group Replication and PostgreSQL Bi-Directional Replication (BDR),
two freely accessible and highly-available multi master non clustered RDBMS products
that have been successfully employed in large scale environments requiring high availability.
Both scenarios have demonstrated, through validated prototypes, to be successful,
satisfying data integrity, reliability and scalability
with slightly different strategies and different upcoming pathways.
With the amount of stored data growing quickly, analytics work has turned into a very intensive workload that will hit our MySQL servers in very hard way.
During the last few years we have seen a bunch of new engines designed to digest big portions of data and help with analytical queries. Now there is a new contender in the arena that claims to be MySQL Compatible, High Impact and Open Source Analytics.
During the course of this session we will go through each of these topics and try to answer:
- Compatible - how hard it is to take data out of MySQL and how much lag it may suffer on real workload?
- High Impact - near real time query response, low cost of entry high ROI?
- Open Source - open source only?
Moreover, we will compare the solution against some other popular products already in the market like ColumnStore, Cassandra, Clickhouse and we'll see how TiDB behaves in comparison.
MySQL, as a database, strives on a filesystem but not all filesystems are equal! On Linux, ZFS is gaining attention and there are good reasons for that, especially if you happen to also run MySQL. In this talk, I'll describe the main characteristics and features of ZFS and draw parallels with the architecture of InnoDB. From easy backups to compression and improved caching, you'll see that MySQL has a lot to benefit from ZFS. I'll discuss the configurations of both MySQL and ZFS so they play well together and perform at their best. Finally, cost saving MySQL/ZFS reference architectures using both, bare metal and clouds servers will be presented and reviewed.
Since version 8.0.14, MySQL supports LATERAL derived tables, sometimes called the for each loop of SQL. What are they? How do they work? Why do you need them? What can they do? How can you use them? Should you use them? What is all this talk about for each loops?
In this session we'll explain the concepts, look at examples of how and when to use this new feature, and talk about how LATERAL derived tables are optimized and executed by MySQL.
Would you like MySQL Protocol Compatibility and near real time dashboard analytics with that?
We are hoarding data in the hopes of making meaningful information out of them to make smart business decisions. But we also resort to inflating costs just to satisfy this need (or not). While there have been a number of open source roll your own options, it has been also operationally costly and sometimes stressful to maintain, not to mention the shortcomings of each. If you have been traditionally storing your data with MySQL and have been enduring running your queries from the same set of servers, fret no more - Clickhouse might just be what you need. In this presentation, we will discuss how to deploy, design, and maintain a Clickhouse analytics platform that continuously reads data from your MySQL servers in near real-time*.
* Depends if you have transformations or complex schema.
Do you already run stock PMM in your environment and want to learn how you extend the PMM platform? Come learn about:
1. Dashboard Customizations
How to create custom dashboard from existing graphs, or build Cross Server Dashboards
2. External Exporters - Monitor any service, anywhere!
Adding an exporter, view the data in data exploration, to deploying a working Dashboard
3. Working with custom queries (MySQL and PostgreSQL)
Execute SELECT statements against your database and store in Prometheus
Build Dashboards relevant to your environment
4. Customizing Exporter Options
Enable de-activated functionality that applies to your environment
5. Using Grafana Alerting
How to set up channels (SMTP, Slack, etc)
How to configure thresholds and alerts
6. Using MySQL/PostgreSQL Data Source
Execute SELECT statements against your database and plot your application metrics
The Player Accounts team at Riot Games needed to consolidate the player account infrastructure and provide a single, global accounts system for the League of Legends player base. To do this, they migrated hundreds of millions of player accounts into a consolidated, globally replicated composite database cluster in AWS. This provided higher fault tolerance and lower latency access to account data. In this talk, we discuss this effort to migrate eight disparate database clusters into AWS as a single composite MySQL database cluster replicated in four different AWS regions, provisioned with terraform, and managed and operated by Ansible.
This talk will briefly overview the evolution of the player accounts services from legacy isolated datacenter deployments to a globally replicated database cluster fronted by our account services and outline some of the growing pains and experiences that got us to where we are today.
In this talk, we discuss the use of the open source MySQL Community Edition and Percona Server projects and Intel's Cascade Lake server platform as the primary building blocks for hosting a Database-as-a-Service (DBaaS) deployment. Data demonstrating the advantage of this configuration will be presented. Emphasis will be on deployment of an offering that provides developers with self-service, on-demand delivery of databases that run over shared infrastructure in a multi-tenant environment. Demand for DBaaS from a diverse span of market segments has driven the large Cloud Service Providers to invest in the buildout such offerings.
Today, these offerings are widely available from Multiple Cloud Providers. Until recently, however, organizations wanting to provide DBaaS from within their own data centers were left without good options. This demand is set to be addressed with the emergence of several open source projects. A second, intersecting trend is the imminent release of Intel's next-generation Cascade Lake XEON platform and its support for byte-addressable, persistent memory via the IntelÂ® Optaneâ„¢ DC Persistent Memory product.
Our talk will dive into the intersection of these two trends starting with an overview of the Cloud Native DBaaS model. Next, a concrete description of a deployment model using Percona's MySQL and MyRocks distributions will be introduced. This model will be supported by a two-fold, data-driven discussion on performance and density. First, a performance characterization for both the InnoDB and MyRocks storage engines is presented with the focus on comparing/contrasting the use of NVMe vis-a-vis IntelÂ® Optaneâ„¢ DC Persistent Memory. For the latter, we include IntelÂ® Optaneâ„¢ DC Persistent Memory volumes in fsdax as well as sector modes. Secondly, data on database instance density using a single Intel Cascade Lake server outfitted with NVMe vis-a-vis IntelÂ® Optaneâ„¢ DC Persistent Memory will be presented. The talk will conclude with a discussion on the results and how they have influenced our plans for further work in MySQL CE/Percona Server open source projects going forward.
MySQL and MariaDB have a long list of old, potentially trivial bugs that are annoying if you hit them. No one's really bothered to fix them, so why not you? It doesn't take a large amount of C/C++ knowledge to pick out an old bug, and build/test a MySQL/MariaDB patch to fix an old bug.
I'll talk about what is a sane choice of bug, how to use the existing mtr to help, small test cases, and packaging up and submitting your changes.
But doing this, you'll pay it forwards, for all the positive MySQL/MariaDB experiences you've had.
MySQL JSON Support
Open source reject license
MySQL Group Replication
Distros dropping MongoDB package
This year has seen a good deal of changes and challenges in the MongoDB Space. But where are things going? We are going to cover the most common questions I get asked even today.
What are my risks if I keep using it?
Is MySQL/Postgres Proxy, replication, and HA finally catching up?
Why is JSON support not the only thing to consider?
Will MongoDB lose my data?
Past these I will be discussing the ecosystem as a whole, and how I see the next few years shaping out. While this talk is most helpful for business leaders and architects, for DBAs and engineers it will also help you decide what to focus on in your career for the emerging technology future.
MySQL Shell is the new client for MySQL. It understands standard and X protocol. It also allows to send JS, Python or SQL commands to the server. In this talk I will show you how to first configure the Shell for a nice, good looking experience with MySQL, show some of the basic commands and objects. After this brief overview I will show how it's possible to extend the Shell, and how I hacked it to create an Innotop clone inside the MySQL Shell. This session is a live demo with extra explanation, and the code will be shared during and after the presentation.
The goal of the talk is also to introduce to the Community how to hack the Shell and to call for contributions and feature requests.
Planning to run MySQL, and want to safeguard data via replication? There are two main choices - traditional MySQL replication or Galera clusters. Which should you choose? We can help.
- Come see recorded demos of breaking replication. We'll demonstrate and compare the different ways MySQL's replication technologies can be thwarted, threatened and thrashed. A taste of what you'll see: A single writer stopping all transactions on a Galera cluster. Rendering a slave useless by neglecting primary keys or abusing metadata locks. Creating inconsistent schemas on a Galera cluster, and more!
- Attend this presentation if you need to understand the pitfalls of each replication strategy, and want to best match MySQL's features to your Application developers' needs.
Our demos use PXC-release, a Cloud Foundry project. CF is an OSS Platform as a Service. Our project gives Operators a reliable, automated way to spin up single node, master-slave, and Galera deployments of Percona Server. We'll share what we've learned: what's working, and what we'd do differently next time.
Since we have extra characters, we're also including our working outline for your consideration:
- Exploring the challenges of GTID
- Temporary tables and GTID (fixed by Percona!)
+ This affects some popular ORM libraries like Hibernate which use temporary tables extensively
- Dealing with errant transactions
+ FLUSH commands can generate GTIDs on a read-only slave
+ ... "How can I have different users on master and slave?"
- Ways to result in a useless slave?
+ Missing primary keys!
+ Single-threaded replication too slow / Multi-threaded Slaves (MTS)
+ Long-running SELECT can block replication (metadata locks)
- Rattlesnakes: Out of sync Slaves
+ binlog_format = STATEMENT
+ Missing users on slave ("stored code")
+ Non-transactional table
+ Memory tables
+ InnoDB vs. Binlog consistency
- How crash safe is replication?
+ "Impossible position" problem
+ how to use relay-log-recovery
- Disk space on master
+ Binary logs generating faster than they are purged
Things that affect both:
- Rope swing: Locking up replication by writing extremely large transactions
- Bottomless pit: Causing a Slave to fall infinitely behind: the Primary Key problem
- Examining multitenancy
+ Online DDL: Galera vs MySQL replication
- Quicksand: Locking up a Galera cluster with a long-running DDL
- Scorpions: Dangers of Rolling Schema Upgrade (RSU)
+ RSU = making a change outside of replication
+ Related to Master/Slave: making DDL changes on slave+switchover to avoid master downtime
- Fire: Inconsistent schemas on Galera
- Crocodiles: Galera deadlocks
We will discuss the state of Percona Server for MySQL 8.0, now GA, and current developments around it.
A discussion of different types of encryption as it relates to MySQL and the community, followed by a deep dive into key management with Hashicorp's Vault software and MySQL.
Real world examples, problems, and "what worked" for Empowered Benefits as they embarked on their journey to implement encryption at rest in their health care centric IT environment.
In this presentation, we'll cover the following areas:
- MySQL architecture and application eco system in Venmo
- Scalability challenges of MySQL for Venmo applications for Super Bowl peak traffic
- Short term scalability improvements for peak traffic, including horizontal and vertical scalability approaches.
- Long term directions to scale MySQL databases, including domain isolation, data sharding, and adapting MySQL database to support micro service applications.
- Case studies of MySQL performance tuning. Examples would include modifying application logic to eliminate database queries and working around optimizer bugs to handle multiple-table joins with order by limit clauses.
Data security plays a critical role in PayPal's database infrastructures. In this presentation, we will discuss how PayPal enforces data security. The following areas will be covered:
- SSL encrypted connections between applications and database instances, as well as database to database instances
- Integration of database login with LDAP for user authentication and authorization
- Enterprise auditing for database access and metadata/object modifications
- Securing application login with custom SSL key and password management, password rotations
- Methods to avoid password exposure, such as by using MySQL connection strings
- Challenges of standardization of MySQL to Percona XtraDB in PayPal. How we handled
-- different versions of MySQL on different operating systems
-- application users with super user privileges
-- incompatibilities between MySQL commercial and Percona XtraDB Cluster
Amazon Redshift has been providing scalable, quick-to-access analytics platforms for many years, but the question remains: how do you get the data from your existing datastore into Redshift for processing?
Traditional ETL methods can't keep up with large volumes of data, and can require manual reprocessing when an error occurs. Running queries by record change date puts a load on your MySQL server and pollutes your cache.
Wouldn't it be great if you could replicate your data in real time, filter on the tables and schemas you need, all without putting any extra load on your MySQL server? Wouldn't it also be great if schema changes just flowed through from MySQL to RedShift, without intervention on your part?
Join us as we explain how you can have it all: real-time, secure replication from MySQL/MariaDB/RDS MySQL/Aurora to RedShift, with schema changes replicated and no replaying of jobs needed when errors occur.
We will cover upgrade from MySQL 5.7 to MySQL 8.0 (8.0.15), going from legacy meta data storage to transactional data dictionary. We will cover the new possibilities for automation of upgrade, and the major advances in upgrade speed and reliability, as well as new consistency checks in the MySQL upgarde checker.
MySQL 8.0 has a pluggable error log. We will talk about the traditional error logger and the JSON error logger, which empowers users with advanced filtering.
We store data with an intention to use it: search, retrieve, group, sort... To do it effectively the MySQL Optimizer uses index statistics when it compiles the query execution plan. This approach works excellently unless your data distribution is not even.
Last year I worked on several support tickets where data follows the same pattern: millions of popular products fit into a couple of categories and the rest used the rest. We had a hard time to find a solution for retrieving goods fast. We offered workarounds for version 5.7. However, a new MariaDB and MySQL 8.0 feature - histograms - would work better, cleaner and faster. The idea of the talk was born.
Of course, histograms are not a panacea and do not help in all situations.
I will discuss
- how index statistics physically stored by the storage engine
- which data exchanged with the Optimizer
- why it is not enough to make correct index choice
- when histograms can help and when they cannot
- differences between MySQL and MariaDB histograms
Security is always a challenge when it comes to data, but regulations like GDPR brings a new layer on top. With rules come more and more restrictions to access and manipulate data. Join us in this presentation to check security best practices, and traditional and new features available for MySQL, including features coming with the new MySQL 8.
In this talk, DBA's and sysadmins will walk through the security features available on the OS and MySQL:
- SO security
- Audit Plugin
- MySQL 8 features
- New caching_sha2_password
- Password Management
- FIPS mode
We will share our experience of working with thousands of support customers, help the audience to become familiar with all the security concepts and methods, and give you the necessary knowledge to apply to your environment.
During this talk, I explain how Group Replication replication works.
This is a theoretical talk in which I explain in details what replication is and how it works.
I discuss what certification is and how it's done. What is XCOM and GCS?
Is Group Replication synchronous? What are the four new consistency guarantees and how do they work?
What is the benefit of Single Primary Mode?
What are the caveats of such replication?
Why is Paxos Mencius more efficient than Totem? Is it always?
IPV4 and what about IPV6
After this presentation, the audience should be comfortable with the technical terms and understand how it works.
Don't miss this talk, the magician will reveal his tricks!
- General concepts
- what happens when keyring plugin is loaded
- where keys are stored
â€“ keyring initialization failures
â€“ taking care of core dumps
- how to setup keyring_vault (separation of servers on Vault server)
- list of keys on a server (base64 encoded)
2) How Innodb Master Key encryption internal works:
- where encryption key is stored
- relation between Master Key and tablespace's encryption key
- keyring cooperation
- keyring uninstallation
3) how key rotation works
4) can table be re-encrypted?
5) Encryption threads
- what are encryption threads
- key rotation
6) binlog encryption:
- communication between master and slave
- key rotation
- MySQL / PS encryption (8.0.14)
As we move into the future, data becomes worth stealing and is stolen often. Backups become critical not only to be safe in case of an accident, but safe from people and computers who should not have your data. Security Compliance is here (and there will be more in the future) and both PCI and GDPR have requirements for backups. Plus our data may be what thieves really want so encryption, storage and retention policies must be looked at or used.
Come share your experience and learn from others on how to make databases secure and compliant.
Being hosted in the cloud is not just about having someone to take care of your database servers for you. It's also about how to improve, contribute, and implement best practices of MySQL to better serve customers. Did you know that we changed how disk block sizes work to improve InnoDB general performance? How about how enforcing GTID yields a significantly more resilient replication? Come join me on a journey about how we thoughtfully orchestrated our hardware, Container-Optimized OS (the Kubernetes Operating System), and MySQL itself to deliver world class performance and reliability in our product.
Are you struggling with too much data? Are your MySQL databases able to ingest the influx of new data? Are you considering an alternate database but modifying your application would be challenging? Are you able to query efficiently your data once ingested?
All those questions are raised more often as the trend of connected devices fully deploys. This talk focuses on large metrics-oriented datasets but the ideas presented apply to a wider audience. From experiences gathered as principal architect at Percona helping customers, I'll cover all the data life cycle phases, from the initial ingestion, the staging, compression, analysis, aggregation, and the final archival.
Real world examples and solutions will be given to quickly improve performances and lower costs.
Deploying SSL/TLS with MySQL at Booking.com on thousands of servers is not without issues.
In this session I'll tell you what steps we took, what problems we hit, and how we improved various parts of the MySQL ecosystem while doing so.
To start we go over the basics: Which TLS settings are there in MySQL and MariaDB and how does this differ from HTTPS as used in browsers. And why do we want TLS in the first place? Is TLS and SSL the same thing?
The first set of problems is inside MySQL: YaSSL vs. OpenSSL, verification issues and reloading of certificates.
The second set of problems is inside Connectors: I'll touch on DBD::mysql (Perl), Go-MySQL-Driver, libmysqlclient (C)
Not all connectors have the same options and defaults. I'll go into TLSv1.2 support.
The third set of problems is tools: Using the require_secure_transport option caused issues with Percona Toolkit and Orchestrator.
I'll also cover: RSA v.s EC, security issues I found and how I wrote a Proxy for MySQL
A standard pattern in scaling relational data access is to use replicas for reads, but this compromises read-after-write consistency. Unfortunately, since many use-cases require stronger consistency guarantees, the pattern is of limited utility. At Box much of our relational data access requires strong consistency for the purposes of enforcing enterprise permissions, but we need to scale too! To address this challenge, we have introduced a novel design pattern and a framework implementing it. This framework, currently used at scale to serve production traffic at Box, utilizes real-time replication position analysis to safely direct traffic that requires strong read-after-write consistency to read-only replicas.
Slow queries are the harsh reality of any database. Irrespective of how you build the system, design data, educate developers and control access, it's very hard to prevent slow queries. They negatively affect the performance of a database and thus of any application using it. It's crucial to monitor them. Otherwise, you will find yourself finding them while troubleshooting a production database performance issue.
At ThousandEyes, we have taken a proactive and automated approach to monitoring slow queries in our MySQL fleet. We have built a completely automated pipeline using various open source technologies percona-toolkit, Anemometer and an in-house tool - Slow Query Notifier - to catch these slow queries and notify us. Slow Query Notifier identifies top offenders and poorly designed queries, focusing on the most impactful culprits. It notifies authors of the respective query via our JIRA ticketing system, and is capable of managing a complex JIRA workflow - creating, reopening issues, and updating priorities.
In this presentation, we go over the importance of proactively monitoring slow queries, and share our design and learnings. We share our goals to open source slow-query-notifier, integrate with PMM query analytics, and add support for Mongodb slow queries.
Immutable Database Infrastructure with Percona XtraDB Cluster
In this session, we will discuss how we build Imuutable infrastructure for database.
Yahoo! JAPAN is a most largest web portal in Japan. we have aboult 90 million unique browser access per month.
We have focused on efficient management of many DBs.
If we need change something, We will abandon the database server entirely, and reinstall it from the OS.
In other words, "Immutable Infrastructure" means "Disposable".
This way keep database server clean and fresh always.
In addition, database does not stop in this cycle.
Percona XtraDB Cluster has important functions to achieve this mechanism.
The abstract of the session includes :
- What is "Immutable Infrastructure" ?
- Architecture over view
- Why Percona XtraDB Cluster ?