The document discusses MySQL Group Replication, which is a plugin that provides multi-master replication capability for MySQL. It allows data to be replicated between multiple MySQL servers so that they can stay in sync. The replication works by having each server send transaction writesets to other servers through a group communication system, and then each server certifies and applies the changes locally in an asynchronous manner.
MySQL Group Replication - Ready For Production? (2018-04)Kenny Gryp
At the end of 2016, Oracle released a new Plugin called MySQL Group Replication, which is a new MySQL replication method that aims to provide better High Availability, and built-in failover with consistency guarantees.
I evaluated the initial GA versions back in early 2017. I presented my initial findings with several best practices and concerns with the current implementation which made me state that Group Replication was not quite ready yet.
(https://www.slideshare.net/Grypyrg/my-sql-group-replication)
(Un)lucky as I was, a large part of the attendees were Oracle developers and the months after this, many of these bugs and missing features were implemented in both MySQL 8.0 as well as backported to MySQL 5.7. (Thank you!)
This is a followup presentation on my previous analysis, where I will look into the changes since and re-evaluate the readiness of Group Replication for production usage and provide my insights and opinion on the state of GR.
Disaster Recovery with MySQL InnoDB ClusterSet - What is it and how do I use it?Miguel Araújo
MySQL InnoDB ClusterSet brings multi-datacenter capabilities to our solutions and make it very easy to setup a disaster recovery architecture. Think multiple MySQL InnoDB Clusters into one single database architecture, fully managed from MySQL Shell and with full MySQL Router integration to make it easy to access the entire architecture.
This presentation covers:
- The various features of InnoDB Clusterset
- How to setup MySQL InnoDB ClusterSet
- Ways to migrate from an existing MySQL InnoDB Cluster into MySQL InnoDB ClusterSet
- How to deal with various failures
- The various features of router integration which makes connection to the database architecture easy.
Wars of MySQL Cluster ( InnoDB Cluster VS Galera ) Mydbops
MySQL Clustering over InnoDB engines has grown a lot over the last decade. Galera began working with InnoDB early and then Group Replication came to the environment later, where the features are now rich and robust. This presentation offers a technical comparison of both of them.
Since the introduction of replication in MySQL, users have been trying to automate the promotion of a replica to a primary as well as automating the failover of TCP connections from one database server to another in the event of a database failure: planned or unplanned. For over a decade, users and organizations have designed various types of solutions to achieve this. Though, many of these solutions were done manually or were using third party software, mostly open source, to automate and integrate various architectures.
For more than 5 years now, MySQL offers complete and very easy-to-use solutions to set up database architectures that provide High-Availability and recently added Disaster Recovery capabilities. Completely built in-house and supported by Oracle, many enterprises large and small have adopted these solutions into business-critical applications.
Business requirements dictate what type of database architecture is required for your system. Disaster tolerance is key and can be measured at different levels: data loss, data availability, and uptime. In this session, the various MySQL Database Architecture solutions will be covered to help you choose the right solution based on your business requirements
In this tutorial, we cover the different deployment possibilities of the MySQL architecture depending on the business requirements for the data. We also deploy some architecture and see how to evolve to the next one.
The tutorial covers the new MySQL Solutions like InnoDB ReplicaSet, InnoDB Cluster, and InnoDB ClusterSet.
MySQL InnoDB Cluster: Management and Troubleshooting with MySQL ShellMiguel Araújo
MySQL InnoDB Cluster and MySQL Shell session presented at Oracle CodeOne2019.
Abstract:
MySQL InnoDB Cluster provides a built-in high-availability solution for MySQL. Combining MySQL Group Replication with MySQL Router and MySQL Shell into an integrated full-stack solution, InnoDB Cluster provides easy setup and management of MySQL instances into a fault-tolerant database service. MySQL Shell is the “control panel” of InnoDB Cluster, enabling the easy and straightforward configuration and management of InnoDB clusters by providing a scriptable and interactive API: the AdminAPI. Recent enhancements and features added to MySQL Shell make the management of InnoDB clusters even more powerful and smoother. Attend this session to get an overview of the latest developments and improved InnoDB Cluster administration tasks.
Notes:
The slideshow includes a video that cannot be seen in slideshare/PDF. If you're interested in it please check the following blog post: https://mysqlhighavailability.com/mysql-innodb-cluster-automatic-node-provisioning/
MySQL High Availability with Group ReplicationNuno Carvalho
MySQL Group Replication is a multi-master update everywhere replication plugin that provides high availability. It removes the need for handling server failover, provides fault tolerance, and automates group reconfiguration. Transactions are replicated to all group members, with conflicts detected and resolved using a first committer wins rule. Failed members automatically rejoin the group and synchronize with the others transparently. Group Replication uses the standard MySQL and InnoDB architecture, so existing users will feel familiar. It also supports features like auto-increment handling, GTIDs, secure connections, and a new single primary mode.
The document discusses two MySQL high availability solutions: MySQL InnoDB Cluster and MySQL NDB Cluster. MySQL InnoDB Cluster provides easy high availability built into MySQL with write consistency, read scalability, and application failover using MySQL Router. MySQL NDB Cluster is an in-memory database that provides automatic sharding, native access via several APIs, read/write consistency, and read/write scalability using the NDB storage engine. The document compares the two solutions and discusses their architectures and key features.
This document provides an overview of MySQL high availability solutions including InnoDB Cluster and NDB Cluster. InnoDB Cluster allows setting up a highly available MySQL cluster with auto-sharding using Group Replication and MySQL Router for transparent application routing. NDB Cluster is a memory-optimized database for low-latency applications requiring high scalability and availability. MySQL Shell provides a unified interface for deploying, managing and monitoring these MySQL HA solutions.
MySQL Administrator
Basic course
- MySQL 개요
- MySQL 설치 / 설정
- MySQL 아키텍처 - MySQL 스토리지 엔진
- MySQL 관리
- MySQL 백업 / 복구
- MySQL 모니터링
Advanced course
- MySQL Optimization
- MariaDB / Percona
- MySQL HA (High Availability)
- MySQL troubleshooting
네오클로바
http://neoclova.co.kr/
MySQL InnoDB Cluster: High Availability Made Easy!Vittorio Cioe
InnoDB Cluster represents the present and the future of High-Availability technologies for MySQL! It has been developed to remove from the discussion all the "BUT" which used to arised when we were speaking about high-availability with MySQL. Now, with quasi-automated deployment, fully automated failover and conflict resolution, designing, implementing and maintaing your highly-available MySQL infrastructure is a really no-stress operation!
High Availability in MySQL 8 using InnoDB ClusterSven Sandberg
InnoDB Cluster is the built-in and open-source High Availability solution for MySQL 8. It consists of three components. The engine is MySQL Group Replication: the highly available cluster of database servers. This is where your data is safe and available, due to the replicated state machine, relying on the famous Paxos protocol. At the driver's seat is MySQL Shell: the DevOp's multilingual console. Here you can deploy, query, and arrange your cluster using either Javascript or Python, to your taste. Your application is welcomed to join the ride by connecting to MySQL Router: the intelligent, seamless interface to the cluster. We introduce all three components, with a special focus on Group Replication.
The document discusses MySQL Shell and how it can help database administrators (DBAs) with common tasks like deploying architectures, preparing upgrades, dumping and loading data, and managing users. MySQL Shell provides tools like the Admin API for configuring MySQL clusters and replicasets, an upgrade checker utility to validate upgrades to MySQL 8.0, and parallel dump and load functionality to backup, migrate, and reset data.
MySQL InnoDB Cluster and Group Replication in a NutshellFrederic Descamps
This document outlines the agenda and steps for a hands-on tutorial on MySQL InnoDB Cluster and Group Replication. The agenda includes preparing the workstation by setting up virtual machines, an overview of MySQL InnoDB Cluster and Group Replication, migrating from a master-slave topology to Group Replication, monitoring Group Replication, and application interaction with Group Replication. The first lab demonstrates the current master-slave setup. The migration plan involves installing MySQL InnoDB Cluster on a new server, restoring a backup, setting up asynchronous replication on the new server, adding it to the Group Replication group, pointing the application to a new node, and stopping asynchronous replication after catch up.
This document provides an overview and summary of various high availability (HA) solutions for MySQL databases. It begins with an introduction to HA and definitions of key terms. It then discusses MySQL replication, including asynchronous, semi-synchronous, and features in MySQL 5.6 and MariaDB 10.0. Other HA solutions covered include MHA for automated failover, Galera/MariaDB Galera Cluster for synchronous replication, shared disk solutions like DRBD, and MySQL Cluster for in-memory synchronous replication across nodes. The document provides brief descriptions of how each solution works and when it may be applicable.
This is a presentation at Bengaluru TechDay -October2019 for Oracle Database Admin and Architects presented by Karthik P R ( CEO Mydbops ). He explains the possible High Availability options in MySQL ecosystem.
https://www.meetup.com/All-India-Oracle-Users-Group-Bangalore-Chapter/events/265252214/
Group Replication went Generally Available end of 2016, it introduces a 'synchronous' active:active multi-master eplication, in addition to asynchronous and semi-synchronous replication, the latter 2 being available in in MySQL for longtime.
As with any new feature, and especially with introducing active:active multi-master replication, it takes a while before companies are adopting the software in production database environment.
For example, even though MySQL 5.7 has been GA for more than a year, adoption is only starting to increase recently.
We can, and should, expect the same from Group Replication. As with every release, bugs will be found, and with new features, best practises still need to formed out of practical experience.
After giving a short introduction on what Group Replication is, I will cover my experience so far in evaluating Group Replication.
Every website wants to become successful. Few websites however undertake the basic and fundamental steps to build a rock solid foundation to ensure a scalable
Online MySQL Backups with Percona XtraBackupKenny Gryp
Percona XtraBackup is a free, open source, complete online backup solution for all versions of Percona Server, MySQL® and MariaDB®.
Percona XtraBackup provides:
* Fast and reliable backups
* Uninterrupted transaction processing during backups
* Savings on disk space and network bandwidth with better compression
* Automatic backup verification
* Higher uptime due to faster restore time
This talk will discuss the various different features of Percona XtraBackup, including:
* Full & Incremental Backups
* Compression, Streaming & Encryption of Backups
* Backing Up To The Cloud (Swift).
* Percona XtraDB Cluster / Galera Cluster.
* Percona Server Specific features
This document provides an overview of advanced MySQL replication techniques. It begins with an introduction to replication basics like principles, setup, and monitoring. It then discusses challenges with replication including master switchovers during failures and slaves falling behind. The document outlines various replication power techniques and features that can help address these issues like row-based and semi-synchronous replication. It also covers tools for replication and techniques for replication outside of the traditional setup.
Mix ‘n’ Match Async and Group Replication for Advanced Replication SetupsPedro Gomes
The document discusses mixing asynchronous replication and group replication for advanced MySQL replication setups. It provides an overview of asynchronous replication, semi-synchronous replication, multi-source replication, and group replication. It then discusses some basic scenarios for mixing these technologies, such as using asynchronous replication for read scaling beyond the group's 9 member limit or aggregating data from multiple groups. The document also covers migrating from asynchronous to group replication and migrating disjoint servers into a group.
High Availability Using MySQL Group ReplicationOSSCube
MySQL Group Replication is a recent MySQL plugin that brings together group communication techniques and database replication, providing both a high availability and a multi-master update everywhere replication solution.
The PPT provide provide a broad overview of MySQL Group Replication plugin, what it can achieve and how it helps keep your MySQL databases highly available and your business up and running, without fail.
Upgrading MySQL databases do not come without risk. There is no guarantee that no problems will happen if you move to a new major MySQL version.
Should we just upgrade and rollback immediately if problems occur? But what if these problems only happen a few days after migrating to this new version?
You might have a database environment that is risk-adverse, where you really have to be sure that this new MySQL version will handle the workload properly.
Examples:
- Both MySQL 5.6 and 5.7 have a lot of changes in the MySQL Optimizer. It is expected that this improves performance of my queries, but is it really the case? What if there is a performance regression? How will this affect my database performance?
- Also, there are a lot of incompatible changes which are documented in the release notes, how do I know if I'm affected by this in my workload? It's a lot to read..
- Can I go immediately from MySQL 5.5 to 5.7 and skip MySQL 5.6 even though the MySQL documentation states that this is not supported?
- Many companies have staging environments, but is there a QA team and do they really test all functionality, under a similar workload?
This presentation will show you a process, using open source tools, of these types of migrations with a focus on assessing risk and fixing any problems you might run into prior to the migration.
This process can then be used for various changes:
- MySQL upgrades for major version upgrades
- Switching storage engines
- Changing hardware architecture
Additionally, we will describe ways to do the actual migration and rollback with the least amount of downtime.
The document discusses proposed changes to MySQL Server 8.0 and replication defaults. Some key areas discussed include changing the default character set to UTF8MB4, turning on the event scheduler by default, increasing some session buffer sizes, enabling security defaults, and enabling replication features like binary logging and GTIDs by default. The document seeks feedback from users on the proposed changes.
Multi Source Replication With MySQL 5.7 @ VerisureKenny Gryp
Verisure migrated their data warehouse from using Tungsten Replicator to native multi-source replication in MySQL 5.7 to simplify operations. They loaded data from production shards into the new data warehouse setup using XtraBackup backups and improved replication capacity with MySQL's parallel replication features. Some issues were encountered with replication lag reporting and crashes during the upgrade but most were resolved. Monitoring and management tools also required updates to support the new multi-source replication configuration.
Inno db internals innodb file formats and source code structurezhaolinjnu
This document discusses the goals, architecture, and on-disk format of the InnoDB storage engine for MySQL. InnoDB aims to provide transaction support, reliability, and scalability. Its architecture includes buffering, locking, recovery, and efficient I/O mechanisms. The on-disk format is designed for durability, performance through techniques like compression, and compatibility through file format management. Source code is organized into subdirectories corresponding to major subsystems.
Java MySQL Connector & Connection Pool Features & OptimizationKenny Gryp
This talk will give an overview of the different available Java MySQL connectors (Connector/J, MariaDB Java Connector) and connection pools (Commons-DBCP, C3P0, ...).
One of the things with the default configuration of these solutions is that they are very chatty. This can have a noticeable impact on application performance and database load. I've seen many environments where over 50% of the total amount of queries are caused by such behavior. This behavior will be explained through examples seen on production systems as well as recommendations on optimization will be made.
Load balancing and transparent failover solutions will be described for both MySQL's traditional asynchronous replication and Galera based replication (Percona XtraDB Cluster or MariaDB Galera Cluster).
Software developers love tools for coding, debugging, testing, and configuration management. The more these tools improve the How of coding, the more we see that we're behind the curve on improving the What, Why, and When. If you've been on a project that seemed vague, adrift, and endless, this talk can help. Make your projects run SMART.
Just about anyone can write a basic SQL query for a table. Not everyone can write a good query though - that takes practice and knowing how to understand what the optimizer is doing with the query. Learn the basics of query optimization so you keep your application engaging the user rather then showing the progress bar as they wait on the database.
This document provides an overview of troubleshooting replication in MySQL and MariaDB databases. It discusses typical errors seen with the slave IO thread and slave SQL thread, such as replication stopping, the slave lagging behind the master, and the master increasing in resource usage. It also covers replication concepts like the master-slave architecture, binary logging formats, global transaction identifiers, and tools for monitoring replication like SHOW SLAVE STATUS and Performance Schema.
MySQL Group Replicatio in a nutshell - MySQL InnoDB ClusterFrederic Descamps
Group Replication is a plugin that provides multi-master replication for MySQL. It allows transactions to be executed on any node and replicated in a synchronous manner to all other nodes. The changes are delivered in total order to each node using GTIDs to ensure strong consistency across the cluster. Certification and application of the changes occurs asynchronously on each node after the writeset has been synchronously delivered.
The document provides an overview of MySQL InnoDB Cluster and demonstrates how to set up a basic cluster. Key points include:
- MySQL InnoDB Cluster uses Group Replication to provide high availability, fault tolerance and automated recovery.
- The demo deploys 3 sandbox MySQL instances and uses the MySQL Shell to create an InnoDB cluster spanning the instances.
- By default, the cluster operates in single-primary mode, where one node acts as the primary and accepts writes. Multi-primary mode is also demonstrated.
- Status variables and queries are shown to identify the current primary node.
MySQL Group Replication - HandsOn TutorialKenny Gryp
Group Replication is a plugin for MySQL that provides multi-master replication. It works by having each node send write transactions to other nodes through a group communication system. The writes are certified locally in an asynchronous manner to ensure total order of transactions across all nodes. Group Replication uses optimistic locking where local locks are released right after commit, and conflict detection happens during certification rather than at the start of transactions.
The document discusses MySQL InnoDB Cluster, which provides high availability and scaling features for MySQL. It uses Group Replication under the hood, which allows data to be written simultaneously across cluster nodes while maintaining consistency. By default, MySQL InnoDB Cluster runs in Single Primary Mode, where one node acts as the primary/writable node and others act as hot standbys through an automated leader election process.
This document provides an introduction to MySQL InnoDB Cluster, which is MySQL's solution for high availability and scaling. It discusses how Group Replication, the heart of MySQL InnoDB Cluster, allows data to be written simultaneously across cluster nodes while maintaining consistency through techniques like conflict detection and resolution. The document also explains how Group Replication provides automatic recovery from failures and makes high availability easy for users to setup and manage.
Boston meetup : MySQL Innodb Cluster - May 1st 2017Frederic Descamps
MySQL InnoDB Cluster provides an easy-to-use high availability solution for MySQL databases. It utilizes Group Replication, which replicates data across multiple database nodes to provide redundancy and automatic failover. This allows the database to continue operating even if individual nodes fail. MySQL InnoDB Cluster handles replication, provisioning, and failover automatically without complex configuration needed for traditional asynchronous replication topologies.
The document discusses asynchronous MySQL replication and its limitations. Asynchronous replication involves replicas streaming replication logs from a single master, which can lead to lag. Replica provisioning and data consistency must be manually handled. Complex replication topologies can be built but come with challenges around write safety and management overhead. Group replication aims to address some of these issues.
Solving Performance Problems Using MySQL Enterprise MonitorOracleMySQL
The document discusses using MySQL Enterprise Monitor to diagnose performance problems in a Group Replication cluster. It demonstrates creating a 3-node replication cluster using sandbox instances, loading sample data, and issuing problematic queries. It then shows how the Monitor identifies different types of performance issues from the query analyzer and detected events, including queries with full table scans, sorts, long-running queries, and replication outages.
ROLE INTERNAL IP
mysql1 master / app 192.168.56.11
mysql2 replica 192.168.56.12
mysql3 n/a 192.168.56.13
The document outlines steps to migrate an asynchronous MySQL replication setup to a MySQL InnoDB Cluster configuration. It describes cloning data from mysql2 to mysql3, creating an InnoDB Cluster with mysql3, configuring asynchronous replication from mysql1 to mysql3, adding mysql2 to the cluster, and bootstrapping a MySQL Router.
This document summarizes a presentation about MySQL Group Replication. The presentation discusses how Group Replication provides enhanced high availability for MySQL databases by allowing multiple MySQL servers to act as equal masters that can handle writes and remain available even if one server fails. It covers the theory behind Group Replication, how to configure and use it, and management of Group Replication deployments.
UAE MySQL Users Group Meet-up : MySQL Shell Document Store & more...Frederic Descamps
The document provides an overview of the MySQL Shell tool. It discusses how MySQL Shell provides both interactive and batch operations, document and relational models, CRUD APIs via scripting, and traditional and JSON output formats. It also touches on how MySQL Shell supports MySQL standard and X protocols. The document then demonstrates some examples of using MySQL Shell's document store functionality and SQL functionality. It discusses extending MySQL Shell via user-defined reports and plugins.
How to operate MySQL InnoDB Cluster with MySQL ShellFrederic Descamps
This document provides an overview of operating and maintaining a MySQL InnoDB Cluster with MySQL Shell. It discusses node provisioning using the MySQL CLONE plugin for automatic data synchronization of new nodes. It also covers observability of cluster status using the status() method in MySQL Shell or Performance Schema tables. Cluster configuration options that can be checked and changed include global settings as well as group-specific and instance-specific settings for Group Replication. Consistency levels for the cluster are also discussed to guarantee the most up-to-date data is read without stale or dirty reads.
DataOps Barcelona - MySQL HA so easy... that's insane !Frederic Descamps
1. MySQL 8.0 InnoDB Cluster is a new high availability and scaling solution for MySQL that makes setup easy.
2. It uses Group Replication under the hood to allow writing to all nodes simultaneously while maintaining consistency.
3. Key components include MySQL Router for routing and load balancing, and MySQL Shell for administration.
The document discusses installing and configuring MySQL. It explains that option files list specific startup options for MySQL and are commonly located in the MySQL installation directory or /etc/mysql directory. It also provides a generic layout of MySQL binary files on Unix/Linux systems, with client programs and the mysqld server located in the bin and scripts directories.
- Oracle has significantly invested in MySQL by growing the development team to hundreds of engineers, making it the largest team in MySQL's history.
- MySQL 5.6 represents the most innovative release of MySQL in many years, with major performance, scalability, and feature enhancements across InnoDB, replication, and other components.
- Key new capabilities in MySQL 5.6 include optimizations for read-only workloads, online DDL, dump/restore of InnoDB buffer pool, and full text search on InnoDB tables.
Similar to MySQL InnoDB Cluster - Group Replication (20)
MySQL Innovation & Cloud Day - Document Store avec MySQL HeatWave Database Se...Frederic Descamps
Découvrez un nouveau monde où l'on peut gérer ses données sans la moindre ligne de SQL.
MySQL Document Store utilise le nouveau protocol MySQL X, qui est également présent avec MySQL Database Service sur OCI, et permet aux développeurs d'écrire du code simple et efficace.
Mais attention, si nécessaire, MySQL Document Store peut également traiter les document JSON comme s'ils étaient des tables relationnelles et permettre des requêtes très poussées...
This document discusses how MySQL indexes and histograms can speed up queries. It begins with an introduction to the presenter and topic. The goal of reducing query response time is discussed. Methods for identifying inefficient queries are covered, including using the sys schema. The role of the MySQL optimizer in evaluating query plans is then explained. Different types of indexes that can be used to optimize queries are also outlined.
RivieraJUG - MySQL 8.0 - What's new for developers.pdfFrederic Descamps
This document summarizes Frédéric Descamps' presentation on new features in MySQL 8.0 for developers. It introduces Descamps and covers several new features in MySQL 8.0, including the new volcano iterator and optimizer refactoring, EXPLAIN ANALYZE for analyzing query performance, hash joins replacing block nested loops, common table expressions, lateral derived tables, window functions, JSON functions, table value constructors, functional indexes, invisible indexes, check constraints, expressions as default values, lock modes like NOWAIT and SKIP LOCKED, and support for generated invisible columns as primary keys.
This document provides an overview of new features and enhancements in MySQL 8.0 over the last 18 months, from versions 8.0.23 to 8.0.30. It discusses improvements to replication, Group Replication, InnoDB, and primary keys. Some key changes include a new InnoDB redo log architecture, support for disabling the redo log at runtime, parallel index builds, and the ability to add an invisible auto-increment primary key column to tables without a primary key. The document is presented by Frédéric Descamps at the MySQL User Group NL.
Frédéric Descamps presented on the state of MySQL in 2022. Some key points included:
- MySQL 8.0.29 was the latest release with improvements like IF NOT EXISTS for DDL statements.
- MySQL remains the most popular open source database according to surveys.
- MySQL HeatWave on OCI provides high performance for analytics workloads compared to other cloud offerings.
- The MySQL Operator for Kubernetes makes it easier to deploy and manage MySQL on Kubernetes.
- Upcoming certifications for MySQL 8.0 DBA and Developer were announced.
Percona Live 2022 - MySQL Shell for Visual Studio CodeFrederic Descamps
The document discusses MySQL Shell for Visual Studio Code, a new client for developers and DBAs. It provides an overview of the key features and components of the MySQL Shell extension for VS Code, including how to install and use the SQL Notebook editor, MySQL Shell Console, and its integration with Oracle Cloud Infrastructure. The document also covers how MySQL Shell plugins can still be used with the VS Code extension by copying plugins to the appropriate directory.
Percona Live 2022 - The Evolution of a MySQL Database SystemFrederic Descamps
From a single MySQL instance to multi-site high availability, this is what you will find out in this presentation. You will learn how to make this transition and which solutions best suit changing business requirements (RPO, RTO). Recently, MySQL has extended the possibilities for easy deployment of architecture with integrated tools. Come and discover these open source solutions that are part of MySQL.
LinuxFest Northwest 2022 - The Evolution of a MySQL Database SystemFrederic Descamps
At the beginning of a project, the database is just a single MySQL instance (maybe not even running on its own hardware)... but with the evolution of the business requirements, the database must change to also meet the new targets of data loss and uptime. During this session we will follow the journey of a single MySQL server from the simple instance to a High Available Architecture with multi-site Disaster Recovery. We will discover easy manageable native solutions like MySQL InnoDB ReplicaSet, MySQL InnoDB Cluster and MySQL InnoDB ClusterSet. The session is also illustrated with commands and examples.
Open Source 101 2022 - MySQL Indexes and HistogramsFrederic Descamps
Nobody complains that the database is too fast. But when things slow down, the complaints come quickly. The two most popular approaches to speeding up queries are indexes and histograms. But there are so many options and types on indexes that it can get confusing. Histograms are fairly new to MySQL but they do not work for all types of data. This talk covers how indexes and histograms work and show you how to test just how effective they are so you can measure the performance of your queries.
Pi Day 2022 - from IoT to MySQL HeatWave Database ServiceFrederic Descamps
HeatWave is a massively parallel, high performance, in-memory query accelerator for Oracle MySQL Database Service that accelerates MySQL performance by orders of magnitude for analytics and mixed workloads. But how do you collect data from an Internet of Things Environment so you can use HeatWave to process it? In one hour you will see how data collected by a Raspberry PI or other Internet of Things device can be uploaded to the MySQL Database Service and then processed by HeatWave.
D'une simple instance MysQL à une haute-disponibilité multi-sites, voici ce que vous décrouvrirez dans cette présentation. Comment effectuer cette transition et quelles solutions conviennent les mieux aux évolutions des exigences commerciales (RPO, RTO). Récemment, MySQL a étendu les possibilités de déploiement aisé d'architecture avec des outils intégrés. Venez découvrir ces solution Open Source qui font partie de MySQL.
FOSDEM 2022 MySQL Devroom: MySQL 8.0 - Logical Backups, Snapshots and Point-...Frederic Descamps
Logical dumps are becoming popular again. MySQL Shell parallel dump & load utility changed to way to deal with logical dumps, certainly when using instances in the cloud. MySQL 8.0 released also an awesome physical snapshot feature with CLONE.
In this session, I will show how to use these two ways of saving your data and how to use the generated backup to perform point-in-time recovery like a rockstar with MySQL 8.0 in 2022 !
The document discusses the new features of MySQL 8.0. It covers improvements to SQL functionality with common table expressions, window functions, and JSON support. It also discusses performance enhancements including hash joins, faster I/O with the new InnoDB buffer, and group replication for high availability. New features improve security, validation, indexing and usability.
The document is a presentation in French on the new features of MySQL 8.0. It discusses improvements to SQL functionality with Common Table Expressions, Window Functions, and LATERAL queries. It also covers new JSON functions and indexing, improved performance, and new management features in areas like replication, security, and error logging. The presentation provides examples and emphasizes that MySQL now supports both transactional SQL and NoSQL operations on JSON data, combining the benefits of both approaches.
This document provides a summary of updates to MySQL between October 2021 and May 2021. It discusses three releases of MySQL 8.0 (versions 8.0.23, 8.0.24, and 8.0.25) and new features including invisible columns, asynchronous replication connection failover, improved load/dump functionality in MySQL Shell, and the new MySQL Database Service on Oracle Cloud Infrastructure with HeatWave for accelerated analytics.
This document summarizes Frédéric Descamps' journey to add a user to the router_rest_accounts table to authenticate with the MySQL Router REST API. After several failed attempts using generated or external passwords, he learns directly from the MySQL Router development team that the REST API supports using the default MySQL 8.0 authentication string or the modular_crypt_format for password hashes, allowing simple password insertion.
Blockchain and Cyber Defense Strategies in new genre timesanupriti
Explore robust defense strategies at the intersection of blockchain technology and cybersecurity. This presentation delves into proactive measures and innovative approaches to safeguarding blockchain networks against evolving cyber threats. Discover how secure blockchain implementations can enhance resilience, protect data integrity, and ensure trust in digital transactions. Gain insights into cutting-edge security protocols and best practices essential for mitigating risks in the blockchain ecosystem.
Fluttercon 2024: Showing that you care about security - OpenSSF Scorecards fo...Chris Swan
Have you noticed the OpenSSF Scorecard badges on the official Dart and Flutter repos? It's Google's way of showing that they care about security. Practices such as pinning dependencies, branch protection, required reviews, continuous integration tests etc. are measured to provide a score and accompanying badge.
You can do the same for your projects, and this presentation will show you how, with an emphasis on the unique challenges that come up when working with Dart and Flutter.
The session will provide a walkthrough of the steps involved in securing a first repository, and then what it takes to repeat that process across an organization with multiple repos. It will also look at the ongoing maintenance involved once scorecards have been implemented, and how aspects of that maintenance can be better automated to minimize toil.
AC Atlassian Coimbatore Session Slides( 22/06/2024)apoorva2579
This is the combined Sessions of ACE Atlassian Coimbatore event happened on 22nd June 2024
The session order is as follows:
1.AI and future of help desk by Rajesh Shanmugam
2. Harnessing the power of GenAI for your business by Siddharth
3. Fallacies of GenAI by Raju Kandaswamy
this resume for sadika shaikh bca studentSadikaShaikh7
I am a dedicated BCA student with a strong foundation in web technologies, including PHP and MySQL. I have hands-on experience in Java and Python, and a solid understanding of data structures. My technical skills are complemented by my ability to learn quickly and adapt to new challenges in the ever-evolving field of computer science.
For the full video of this presentation, please visit: https://www.edge-ai-vision.com/2024/07/intels-approach-to-operationalizing-ai-in-the-manufacturing-sector-a-presentation-from-intel/
Tara Thimmanaik, AI Systems and Solutions Architect at Intel, presents the “Intel’s Approach to Operationalizing AI in the Manufacturing Sector,” tutorial at the May 2024 Embedded Vision Summit.
AI at the edge is powering a revolution in industrial IoT, from real-time processing and analytics that drive greater efficiency and learning to predictive maintenance. Intel is focused on developing tools and assets to help domain experts operationalize AI-based solutions in their fields of expertise.
In this talk, Thimmanaik explains how Intel’s software platforms simplify labor-intensive data upload, labeling, training, model optimization and retraining tasks. She shows how domain experts can quickly build vision models for a wide range of processes—detecting defective parts on a production line, reducing downtime on the factory floor, automating inventory management and other digitization and automation projects. And she introduces Intel-provided edge computing assets that empower faster localized insights and decisions, improving labor productivity through easy-to-use AI tools that democratize AI.
How Netflix Builds High Performance Applications at Global ScaleScyllaDB
We all want to build applications that are blazingly fast. We also want to scale them to users all over the world. Can the two happen together? Can users in the slowest of environments also get a fast experience? Learn how we do this at Netflix: how we understand every user's needs and preferences and build high performance applications that work for every user, every time.
Interaction Latency: Square's User-Centric Mobile Performance MetricScyllaDB
Mobile performance metrics often take inspiration from the backend world and measure resource usage (CPU usage, memory usage, etc) and workload durations (how long a piece of code takes to run).
However, mobile apps are used by humans and the app performance directly impacts their experience, so we should primarily track user-centric mobile performance metrics. Following the lead of tech giants, the mobile industry at large is now adopting the tracking of app launch time and smoothness (jank during motion).
At Square, our customers spend most of their time in the app long after it's launched, and they don't scroll much, so app launch time and smoothness aren't critical metrics. What should we track instead?
This talk will introduce you to Interaction Latency, a user-centric mobile performance metric inspired from the Web Vital metric Interaction to Next Paint"" (web.dev/inp). We'll go over why apps need to track this, how to properly implement its tracking (it's tricky!), how to aggregate this metric and what thresholds you should target.
Details of description part II: Describing images in practice - Tech Forum 2024BookNet Canada
This presentation explores the practical application of image description techniques. Familiar guidelines will be demonstrated in practice, and descriptions will be developed “live”! If you have learned a lot about the theory of image description techniques but want to feel more confident putting them into practice, this is the presentation for you. There will be useful, actionable information for everyone, whether you are working with authors, colleagues, alone, or leveraging AI as a collaborator.
Link to presentation recording and transcript: https://bnctechforum.ca/sessions/details-of-description-part-ii-describing-images-in-practice/
Presented by BookNet Canada on June 25, 2024, with support from the Department of Canadian Heritage.
Navigating Post-Quantum Blockchain: Resilient Cryptography in Quantum Threatsanupriti
In the rapidly evolving landscape of blockchain technology, the advent of quantum computing poses unprecedented challenges to traditional cryptographic methods. As quantum computing capabilities advance, the vulnerabilities of current cryptographic standards become increasingly apparent.
This presentation, "Navigating Post-Quantum Blockchain: Resilient Cryptography in Quantum Threats," explores the intersection of blockchain technology and quantum computing. It delves into the urgent need for resilient cryptographic solutions that can withstand the computational power of quantum adversaries.
Key topics covered include:
An overview of quantum computing and its implications for blockchain security.
Current cryptographic standards and their vulnerabilities in the face of quantum threats.
Emerging post-quantum cryptographic algorithms and their applicability to blockchain systems.
Case studies and real-world implications of quantum-resistant blockchain implementations.
Strategies for integrating post-quantum cryptography into existing blockchain frameworks.
Join us as we navigate the complexities of securing blockchain networks in a quantum-enabled future. Gain insights into the latest advancements and best practices for safeguarding data integrity and privacy in the era of quantum threats.
How RPA Help in the Transportation and Logistics Industry.pptxSynapseIndia
Revolutionize your transportation processes with our cutting-edge RPA software. Automate repetitive tasks, reduce costs, and enhance efficiency in the logistics sector with our advanced solutions.
What's Next Web Development Trends to Watch.pdfSeasiaInfotech2
Explore the latest advancements and upcoming innovations in web development with our guide to the trends shaping the future of digital experiences. Read our article today for more information.
AI_dev Europe 2024 - From OpenAI to Opensource AIRaphaël Semeteys
Navigating Between Commercial Ownership and Collaborative Openness
This presentation explores the evolution of generative AI, highlighting the trajectories of various models such as GPT-4, and examining the dynamics between commercial interests and the ethics of open collaboration. We offer an in-depth analysis of the levels of openness of different language models, assessing various components and aspects, and exploring how the (de)centralization of computing power and technology could shape the future of AI research and development. Additionally, we explore concrete examples like LLaMA and its descendants, as well as other open and collaborative projects, which illustrate the diversity and creativity in the field, while navigating the complex waters of intellectual property and licensing.
The Rise of Supernetwork Data Intensive ComputingLarry Smarr
Invited Remote Lecture to SC21
The International Conference for High Performance Computing, Networking, Storage, and Analysis
St. Louis, Missouri
November 18, 2021
Are you interested in dipping your toes in the cloud native observability waters, but as an engineer you are not sure where to get started with tracing problems through your microservices and application landscapes on Kubernetes? Then this is the session for you, where we take you on your first steps in an active open-source project that offers a buffet of languages, challenges, and opportunities for getting started with telemetry data.
The project is called openTelemetry, but before diving into the specifics, we’ll start with de-mystifying key concepts and terms such as observability, telemetry, instrumentation, cardinality, percentile to lay a foundation. After understanding the nuts and bolts of observability and distributed traces, we’ll explore the openTelemetry community; its Special Interest Groups (SIGs), repositories, and how to become not only an end-user, but possibly a contributor.We will wrap up with an overview of the components in this project, such as the Collector, the OpenTelemetry protocol (OTLP), its APIs, and its SDKs.
Attendees will leave with an understanding of key observability concepts, become grounded in distributed tracing terminology, be aware of the components of openTelemetry, and know how to take their first steps to an open-source contribution!
Key Takeaways: Open source, vendor neutral instrumentation is an exciting new reality as the industry standardizes on openTelemetry for observability. OpenTelemetry is on a mission to enable effective observability by making high-quality, portable telemetry ubiquitous. The world of observability and monitoring today has a steep learning curve and in order to achieve ubiquity, the project would benefit from growing our contributor community.
Transcript: Details of description part II: Describing images in practice - T...BookNet Canada
This presentation explores the practical application of image description techniques. Familiar guidelines will be demonstrated in practice, and descriptions will be developed “live”! If you have learned a lot about the theory of image description techniques but want to feel more confident putting them into practice, this is the presentation for you. There will be useful, actionable information for everyone, whether you are working with authors, colleagues, alone, or leveraging AI as a collaborator.
Link to presentation recording and slides: https://bnctechforum.ca/sessions/details-of-description-part-ii-describing-images-in-practice/
Presented by BookNet Canada on June 25, 2024, with support from the Department of Canadian Heritage.
Performance Budgets for the Real World by Tammy EvertsScyllaDB
Performance budgets have been around for more than ten years. Over those years, we’ve learned a lot about what works, what doesn’t, and what we need to improve. In this session, Tammy revisits old assumptions about performance budgets and offers some new best practices. Topics include:
• Understanding performance budgets vs. performance goals
• Aligning budgets with user experience
• Pros and cons of Core Web Vitals
• How to stay on top of your budgets to fight regressions
Quantum Communications Q&A with Gemini LLM. These are based on Shannon's Noisy channel Theorem and offers how the classical theory applies to the quantum world.
2. MySQL Group Replication in a nutshell
the core of MySQL InnoDB Cluster
Oracle Open World
September 19th 2016
Frédéric Descamps
MySQL Community Manager
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
2 / 126
3.
Safe Harbor Statement
The following is intended to outline our general product direction. It is intended for
information purpose only, and may not be incorporated into any contract. It is not a
commitment to deliver any material, code, or functionality, and should not be relied up in
making purchasing decisions. The development, release and timing of any features or
functionality described for Oracle's product remains at the sole discretion of Oracle.
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
3 / 126
4. Join us!
MySQL Community Reception @ Oracle
OpenWorld
Celebrate, Have Fun and Mingle with Oracle’s MySQL Engineers & Your Peers
Tuesday, September 20, 7.00 pm
Jillian’s at Metreon: 175 Fourth Street, San Francisco
At the corner of Howard and 4th st.; only 2-min walk fromMoscone Center (same
place as last year)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
4 / 126
5. about.me/lefred
Who am I ?
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
5 / 126
7. get more at the conference
MySQL Group Replication
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
7 / 126
8. Other sessions
CON2907 - MySQL High Availability
MattLord
CON4669 - MySQL High Availability with Group Replication
NunoGomes
CON3373 - The State of the Art of MySQL Replication
LuisSoares
HOL2912 - Building a Highly Available MySQL Database Service with Group Replication
MattLord
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
8 / 126
12. the magic explained
Group Replication Concept
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
12 / 126
13. Group Replication : what is it ?
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
13 / 126
14. Group Replication : what is it ?
MySQL is one of the major components of MySQL InnoDB Cluster
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
14 / 126
15. Group Replication : what is it ?
MySQL is one of the major components of MySQL InnoDB Cluster
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
15 / 126
18. Group replication is a plugin !
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
18 / 126
19. Group replication is a plugin !
GR is a plugin for MySQL, made by MySQL and packaged with MySQL
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
19 / 126
20. Group replication is a plugin !
GR is a plugin for MySQL, made by MySQL and packaged with MySQL
GR is an implementation of Replicated Database State Machine theory
MySQL Group Communication System(GCS) is based on Paxos
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
20 / 126
21. Group replication is a plugin !
GR is a plugin for MySQL, made by MySQL and packaged with MySQL
GR is an implementation of Replicated Database State Machine theory
MySQL Group Communication System(GCS) is based on Paxos
Provides virtually synchronous replication for MySQL 5.7+
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
21 / 126
22. Group replication is a plugin !
GR is a plugin for MySQL, made by MySQL and packaged with MySQL
GR is an implementation of Replicated Database State Machine theory
MySQL Group Communication System(GCS) is based on Paxos
Provides virtually synchronous replication for MySQL 5.7+
Supported on all MySQL platforms !!
Linux, Windows, Solaris, OSX, FreeBSD
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
22 / 126
24. Group Replication : how does it work ?
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
24 / 126
25. Group Replication : how does it work ?
A node (member of the group) send the changes (binlog events) made by a
transaction to the group through the GCS.
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
25 / 126
26. Group Replication : how does it work ?
A node (member of the group) send the changes (binlog events) made by a
transaction to the group through the GCS.
All members consume the writeset and certify it
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
26 / 126
27. Group Replication : how does it work ?
A node (member of the group) send the changes (binlog events) made by a
transaction to the group through the GCS.
All members consume the writeset and certify it
If passed it is applied, if failed, transaction is rolled back on originating server and
discarded at other servers.
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
27 / 126
28. OK... but how does it work ?!
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
28 / 126
29. OK... but how does it work ?!
It's just magic !
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
29 / 126
30. OK... but how does it work ?!
It's just magic !
... no, in fact the writesets replication is synchronous and then certification and apply of
the changes are local to each nodes and asynchronous.
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
30 / 126
31. OK... but how does it work ?!
It's just magic !
... no, in fact the writesets replication is synchronous and then certification and apply of
the changes are local to each nodes and asynchronous.
not that easy to understand... right ? Let's illustrate this...
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
31 / 126
32. MySQL Group Replication (autocommit)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
32 / 126
33. MySQL Group Replication (autocommit)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
33 / 126
34. MySQL Group Replication (autocommit)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
34 / 126
35. MySQL Group Replication (autocommit)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
35 / 126
36. MySQL Group Replication (autocommit)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
36 / 126
37. MySQL Group Replication (autocommit)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
37 / 126
38. MySQL Group Replication (autocommit)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
38 / 126
39. MySQL Group Replication (full transaction)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
39 / 126
40. MySQL Group Replication (full transaction)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
40 / 126
41. MySQL Group Replication (full transaction)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
41 / 126
42. MySQL Group Replication (full transaction)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
42 / 126
43. MySQL Group Replication (full transaction)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
43 / 126
44. MySQL Group Replication (full transaction)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
44 / 126
45. MySQL Group Replication (full transaction)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
45 / 126
46. MySQL Group Replication (full transaction)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
46 / 126
47. MySQL Group Replication (full transaction)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
47 / 126
48. Group Replication : Optimistic Locking
With GR we use what we call optimistic locking, it means we consider a resource free and
this will be confirmed or not during certification.
Let's first have a look at the traditional locking to compare.
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
48 / 126
61. Optimistic Locking
The systemreturns error 149 as certification failed:
ERROR 1180 (HY000): Got error 149 during COMMIT
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
61 / 126
62. Group Replication : requirements
exclusively use of InnoDB tables only
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
62 / 126
63. Group Replication : requirements
exclusively use of InnoDB tables only
every tables must have a PK defined
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
63 / 126
64. Group Replication : requirements
exclusively use of InnoDB tables only
every tables must have a PK defined
only IPV4 is supported
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
64 / 126
65. Group Replication : requirements
exclusively use of InnoDB tables only
every tables must have a PK defined
only IPV4 is supported
a good network with lowlatency is important
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
65 / 126
66. Group Replication : requirements
exclusively use of InnoDB tables only
every tables must have a PK defined
only IPV4 is supported
a good network with lowlatency is important
maximumof 9 members per group
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
66 / 126
67. Group Replication : requirements
exclusively use of InnoDB tables only
every tables must have a PK defined
only IPV4 is supported
a good network with lowlatency is important
maximumof 9 members per group
log-binmust be enabled and only ROWformat is supported
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
67 / 126
68. Group Replication : requirements (2)
enable GTIDs
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
68 / 126
69. Group Replication : requirements (2)
enable GTIDs
replication meta-data must be stored on systemtables
--master-info-repository=TABLE
--relay-log-info-repository=TABLE
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
69 / 126
70. Group Replication : requirements (2)
enable GTIDs
replication meta-data must be stored on systemtables
--master-info-repository=TABLE
--relay-log-info-repository=TABLE
writesets extraction must be enabled
--transaction-write-set-extraction=XXHASH64
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
70 / 126
71. Group Replication : requirements (2)
enable GTIDs
replication meta-data must be stored on systemtables
--master-info-repository=TABLE
--relay-log-info-repository=TABLE
writesets extraction must be enabled
--transaction-write-set-extraction=XXHASH64
log-slave-updatesmust be enabled
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
71 / 126
72. Group Replication : limitations
There are also some technical limitations:
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
72 / 126
73. Group Replication : limitations
There are also some technical limitations:
binlog checksumis not supported
--binlog-checksum=NONE
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
73 / 126
74. Group Replication : limitations
There are also some technical limitations:
binlog checksumis not supported
--binlog-checksum=NONE
Savepoints are not supported
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
74 / 126
75. Group Replication : limitations
There are also some technical limitations:
binlog checksumis not supported
--binlog-checksum=NONE
Savepoints are not supported
SERIALIZABLEis not supported as transaction isolation level
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
75 / 126
76. Group Replication : limitations
There are also some technical limitations:
binlog checksumis not supported
--binlog-checksum=NONE
Savepoints are not supported
SERIALIZABLEis not supported as transaction isolation level
an object cannot be changed concurrently at different servers by two operations,
where one of themis a DDL and the other is either a DML or DDL.
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
76 / 126
77. are we compatible ?
Pre-study
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
77 / 126
78. Is my workload ready for Group Replication ?
Large transactions are more vulnerable because:
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
78 / 126
79. Is my workload ready for Group Replication ?
Large transactions are more vulnerable because:
Large write set may increase the likelihood of certification conflicts
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
79 / 126
80. Is my workload ready for Group Replication ?
Large transactions are more vulnerable because:
Large write set may increase the likelihood of certification conflicts
Transaction taking longer to execute may be more vulnerable to be aborted by a
certified transaction.
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
80 / 126
81. Is my workload ready for Group Replication ?
Large transactions are more vulnerable because:
Large write set may increase the likelihood of certification conflicts
Transaction taking longer to execute may be more vulnerable to be aborted by a
certified transaction.
Small transactions changing a very small set of data (hotspots):
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
81 / 126
82. Is my workload ready for Group Replication ?
Large transactions are more vulnerable because:
Large write set may increase the likelihood of certification conflicts
Transaction taking longer to execute may be more vulnerable to be aborted by a
certified transaction.
Small transactions changing a very small set of data (hotspots):
Concurrently executing themat different sites will trade-off contention with roll
backs due to the optimistic execution
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
82 / 126
83. Therefore, when using Group Replication, we should pay attention to these points:
PK is mandatory (and a good one is better)
avoid large transactions
avoid hotspot
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
83 / 126
84. Scheme verification
We will see nowa list a queries that allows us to analyze our scheme. We will look for:
non InnoDB tables
tables without PK
tables with a bad PK
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
84 / 126
85. Non InnoDB tables
SELECT table_schema, table_name, engine, table_rows,
(index_length+data_length)/1024/1024 AS sizeMB
FROM information_schema.tables
WHERE engine != 'innodb'
AND table_schema NOT IN
('information_schema', 'mysql', 'performance_schema');
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
85 / 126
86. Tables Without Primary Key
select tables.table_schema, tables.table_name,
tables.engine, tables.table_rows
from information_schema.tables
left join (
select table_schema, table_name
from information_schema.statistics
group by table_schema, table_name, index_name
having
sum(
case
when non_unique = 0
and nullable != 'YES' then 1
else 0
end
) = count(*)
) puks
on tables.table_schema = puks.table_schema
and tables.table_name = puks.table_name
where puks.table_name is null
and tables.table_type = 'BASE TABLE' and engine='InnoDB';
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
86 / 126
87. Tables with bad primarky keys
set SQL_MODE="";
select a.TABLE_SCHEMA,a.TABLE_NAME, b.ENGINE, a.COLUMN_NAME,
a.DATA_TYPE, a.COLUMN_TYPE, a.COLUMN_KEY, b.TABLE_ROWS
from information_schema.COLUMNS as a
join information_schema.TABLES as b
on b.TABLE_NAME=a.TABLE_NAME and b.TABLE_SCHEMA=a.TABLE_SCHEMA
where COLUMN_KEY='PRI' and ENGINE="InnoDB"
and DATA_TYPE not like '%int'
and DATA_TYPE not like 'enum%'
and DATA_TYPE not like 'date%'
and DATA_TYPE not like 'time%'
and a.TABLE_SCHEMA not in ('mysql','sys')
group by table_schema, table_name;
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
87 / 126
88. Workload analysis
Nowit's time to play with Performance_Schemato analyze our workload.
The goal is to identify:
transactions with most statements (and most writes in particular)
transactions with most rows affected
largest statements by rowaffected
queries updating most the same PK and therefore having to wait more)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
88 / 126
89. Setup Performance_Schema
We need to enable some consumers and instruments:
update performance_schema.setup_consumers
set enabled = 'yes'
where name like 'events_statement%' or name like 'events_transaction%';
update performance_schema.setup_instruments
set enabled = 'yes', timed = 'yes'
where name = 'transaction';
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
89 / 126
90. Find the transactions with most statements
select t.thread_id, t.event_id, count(*) statement_count,
sum(s.rows_affected) rows_affected,
length(replace(group_concat(
case when s.event_name = "statement/sql/update" then 1
when s.event_name = "statement/sql/insert" then 1
when s.event_name = "statement/sql/delete" then 1
else null end),',',''))
as "# write statements"
from performance_schema.events_transactions_history_long t
join performance_schema.events_statements_history_long s
on t.thread_id = s.thread_id and t.event_id = s.nesting_event_id
group by t.thread_id, t.event_id order by rows_affected desc limit 10;
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
90 / 126
91. Find the transactions with most statements
It's possible to see what are all these statements in one transaction:
set group_concat_max_len = 1000000;
select t.thread_id, t.event_id, count(*) statement_count,
sum(s.rows_affected) rows_affected,
group_concat(sql_text order by s.event_id separator 'n') statements
from performance_schema.events_transactions_history_long t
join performance_schema.events_statements_history_long s
on t.thread_id = s.thread_id and t.event_id = s.nesting_event_id
group by t.thread_id, t.event_id order by statement_count desc limit 1G
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
91 / 126
92. Find the transactions with most rows affected
select t.thread_id, t.event_id, count(*) statement_count,
sum(s.rows_affected) rows_affected,
length(replace(group_concat(
case
when s.event_name = "statement/sql/update" then 1
when s.event_name = "statement/sql/insert" then 1
when s.event_name = "statement/sql/delete" then 1
else null end),',','')) as "# write statements"
from performance_schema.events_transactions_history_long t
join performance_schema.events_statements_history_long s
on t.thread_id = s.thread_id and t.event_id = s.nesting_event_id
group by t.thread_id, t.event_id order by rows_affected desc limit 10;
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
92 / 126
93. Find the transactions with most rows affected
It's again possible the see what are the statements:
select t.thread_id, t.event_id, count(*) statement_count,
sum(s.rows_affected) rows_affected,
group_concat(sql_text order by s.event_id separator 'n') statements
from performance_schema.events_transactions_history_long t
join performance_schema.events_statements_history_long s
on t.thread_id = s.thread_id and t.event_id = s.nesting_event_id
group by t.thread_id, t.event_id order by rows_affected desc limit 1G
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
93 / 126
94. Find the transactions with most rows affected
It's again possible the see what are the statements:
select t.thread_id, t.event_id, count(*) statement_count,
sum(s.rows_affected) rows_affected,
group_concat(sql_text order by s.event_id separator 'n') statements
from performance_schema.events_transactions_history_long t
join performance_schema.events_statements_history_long s
on t.thread_id = s.thread_id and t.event_id = s.nesting_event_id
group by t.thread_id, t.event_id order by rows_affected desc limit 1G
Don't forget to verify the auto_commit ones are they are not returned with the query
above.
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
94 / 126
95. Find largest statements by row affected
select query, db, rows_affected, rows_affected_avg
from sys.statement_analysis
order by rows_affected_avg desc limit 10;
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
95 / 126
96. Find queries that write the most.
Looking for lots of updates/timer_wait to the same PKs.
select *
from performance_schema.events_statements_history_long
where rows_affected > 1 order by timer_wait desc limit 20G
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
96 / 126
97. help me to configure my InnoDB Cluster
Using new cool tools
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
97 / 126
98. MySQL Shell
The MySQL Teamextended MySQL Shell to use the newAdmin API.
NowMySQL Shell is a single unified client for all adminstrative and operatons tasks.
Multi-Language:
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
98 / 126
99. MySQL Shell
The MySQL Teamextended MySQL Shell to use the newAdmin API.
NowMySQL Shell is a single unified client for all adminstrative and operatons tasks.
Multi-Language:
JavaScript
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
99 / 126
100. MySQL Shell
The MySQL Teamextended MySQL Shell to use the newAdmin API.
NowMySQL Shell is a single unified client for all adminstrative and operatons tasks.
Multi-Language:
JavaScript
Python
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
100 / 126
101. MySQL Shell
The MySQL Teamextended MySQL Shell to use the newAdmin API.
NowMySQL Shell is a single unified client for all adminstrative and operatons tasks.
Multi-Language:
JavaScript
Python
SQL
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
101 / 126
102. MySQL Shell
The MySQL Teamextended MySQL Shell to use the newAdmin API.
NowMySQL Shell is a single unified client for all adminstrative and operatons tasks.
Multi-Language:
JavaScript
Python
SQL
Supports both Document and Relational models
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
102 / 126
103. MySQL Shell
The MySQL Teamextended MySQL Shell to use the newAdmin API.
NowMySQL Shell is a single unified client for all adminstrative and operatons tasks.
Multi-Language:
JavaScript
Python
SQL
Supports both Document and Relational models
Exposes full Development and Admin API
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
103 / 126
104. MySQL Shell: Admin API
mysql-js>dba.help()
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
104 / 126
105. MySQL Shell: Admin API
mysql-js>dba.help()
the global variable dbais used to access the MySQL API
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
105 / 126
106. MySQL Shell: Admin API
mysql-js>dba.help()
the global variable dbais used to access the MySQL API
performDBA operations to manage MySQL InnoDB Cluster:
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
106 / 126
107. MySQL Shell: Admin API
mysql-js>dba.help()
the global variable dbais used to access the MySQL API
performDBA operations to manage MySQL InnoDB Cluster:
create cluster
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
107 / 126
108. MySQL Shell: Admin API
mysql-js>dba.help()
the global variable dbais used to access the MySQL API
performDBA operations to manage MySQL InnoDB Cluster:
create cluster
deploy MySQL instances (in sandbox only for the moment)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
108 / 126
109. MySQL Shell: Admin API
mysql-js>dba.help()
the global variable dbais used to access the MySQL API
performDBA operations to manage MySQL InnoDB Cluster:
create cluster
deploy MySQL instances (in sandbox only for the moment)
get cluster info
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
109 / 126
110. MySQL Shell: Admin API
mysql-js>dba.help()
the global variable dbais used to access the MySQL API
performDBA operations to manage MySQL InnoDB Cluster:
create cluster
deploy MySQL instances (in sandbox only for the moment)
get cluster info
start/stop MySQL instances
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
110 / 126
111. MySQL Shell: Admin API
mysql-js>dba.help()
the global variable dbais used to access the MySQL API
performDBA operations to manage MySQL InnoDB Cluster:
create cluster
deploy MySQL instances (in sandbox only for the moment)
get cluster info
start/stop MySQL instances
validate MySQL instances
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
111 / 126
112. mysqlprovision
The mysqlprovision utility is designed to facilitate the management of MySQL Group
Replication, allowing users to start a replica set (a newreplication group), to add/remove
members to/froman existing group. It can also check if an instance meets all the
requirements to successfully create a newgroup or to be added to an existing group. This
utility is also capable of modifying MySQL option files of existing instances in order for
themto meet the requirements of Group Replication.
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
112 / 126
113. let's create our first MySQL InnoDB Cluster
Demo time !
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
113 / 126
115. full HA ?
Application interaction
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
115 / 126
116. MySQL Router
MySQL Shell
MySQL Group Replication
MySQL Server
MySQL InnoDB Cluster
So MySQL InnoDB Cluster is composed by:
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
116 / 126
117. MySQL Router
MySQL Shell
MySQL Group Replication
MySQL Server
MySQL InnoDB Cluster
So MySQL InnoDB Cluster is composed by:
CON2907 - MySQL High Availability - MattLord- Wed 12:15
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
117 / 126
118. MySQL Router
Transparent client connection routing (TCP level 4)
load balancing
connection failover
Native support for InnoDB Clusters
understands Group Replication topology
utilizes metadata schema stored on each members
bootstrap and auto-config
supports multi-master and single primary modes
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
118 / 126
119. MySQL Router & Group Replication
Quick demo !
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
119 / 126
120. MySQL Router & Group Replication
demo
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
120 / 126
121. Any other alternatives ?
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
121 / 126
122. Any other alternatives ?
It's possible to use MySQL Group Replication with other proxies/load-balancers
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
122 / 126
123. Any other alternatives ?
It's possible to use MySQL Group Replication with other proxies/load-balancers
HAProxy : http://lefred.be/content/mysql-group-replication-as-ha-solution/
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
123 / 126
125. Questions ?
Thank you !
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
125 / 126
126. Join us!
Register: http://tinyurl.com/mysqloow16
MySQL Community Reception @ Oracle
OpenWorld
Tuesday, September 20, 7.00 pm
Jillian’s at Metreon: 175 Fourth Street, San Francisco
At the corner of Howard and 4th st.; only 2-min walk fromMoscone Center (same
place as last year)
Copyright @ 2016 Oracle and/or its affiliates. All rights reserved.
126 / 126