This document provides an overview and agenda for a presentation on MySQL 5.6 performance tuning and best practices. The presentation covers analyzing MySQL workload and internals, performance improvements in MySQL 5.6 and 5.7, benchmark results, and pending issues. It emphasizes the importance of monitoring systems to understand performance bottlenecks and the need for an iterative process of monitoring, tuning, optimizing, and improving database performance over time.
MaxScale uses an asynchronous and multi-threaded architecture to route client queries to backend database servers. Each thread creates its own epoll instance to monitor file descriptors for I/O events, avoiding locking between threads. Listening sockets are added to a global epoll file descriptor that notifies threads when clients connect, allowing connections to be distributed evenly across threads. This architecture improves performance over the previous single epoll instance approach.
SSL certificates in the Oracle Database without surprisesNelson Calero
Presentation delivered on UKOUG conference in December 2019.
Abstract: Nowadays database installations are required to use secure connections to communicate with clients, from connecting to the database listener to interact with external services (for example to send emails from the database).
Also since a couple of years ago, it has been required to use stronger protocols like TLS 1.2 (SHA2 algorithm), which requires extra configuration in older database releases.
This presentation shows how SSL certificates work from a DBA perspective, which tools are available and examples of configuring and troubleshooting their usage from the Oracle database. It also explores the implications and how to implement TLS 1.2 and common errors found in real life usage.
Understanding ProxySQL internals and then interacting with some common features of ProxySQL such as query rewriting, mirroring, failovers, and ProxySQL Cluster
Getting Started with Apache Spark on KubernetesDatabricks
Community adoption of Kubernetes (instead of YARN) as a scheduler for Apache Spark has been accelerating since the major improvements from Spark 3.0 release. Companies choose to run Spark on Kubernetes to use a single cloud-agnostic technology across their entire stack, and to benefit from improved isolation and resource sharing for concurrent workloads. In this talk, the founders of Data Mechanics, a serverless Spark platform powered by Kubernetes, will show how to easily get started with Spark on Kubernetes.
Cluster API is a Kubernetes sub-project that provides declarative APIs and tooling to simplify provisioning, upgrading, and operating multiple Kubernetes clusters on any infrastructure. It works by having core Cluster API components along with plugins for different bootstrap, control-plane and infrastructure providers like Openstack, AWS, GCP etc. The presentation discusses Cluster API integration with Openstack, considerations for using it in production including separate internal and public connections and reusing Openstack networking, and proposes a time-saving deployment model leveraging various Cluster API and Gardener projects.
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
Presentation given at Open Source Summit Japan 2016 about the state of the cloud native technology (Cloud Native Computing Foundation) and the standardization of container technology (Open Container Initiative)
1. The document summarizes a presentation about parallel query in AWS Aurora. It discusses Aurora architecture, parallel query features and implementation steps, use cases, prerequisites, and provides examples testing performance with and without parallel query enabled.
2. Parallel query allows SQL queries to execute in parallel across multiple Aurora nodes, improving performance for queries with certain characteristics like equal, in, and range filters.
3. Test results show parallel query significantly reducing query execution time from hours to minutes for large analytical queries on a 255GB database.
Deep Dive on ClickHouse Sharding and Replication-2202-09-22.pdfAltinity Ltd
Join the Altinity experts as we dig into ClickHouse sharding and replication, showing how they enable clusters that deliver fast queries over petabytes of data. We’ll start with basic definitions of each, then move to practical issues. This includes the setup of shards and replicas, defining schema, choosing sharding keys, loading data, and writing distributed queries. We’ll finish up with tips on performance optimization.
#ClickHouse #datasets #ClickHouseTutorial #opensource #ClickHouseCommunity #Altinity
-----------------
Join ClickHouse Meetups: https://www.meetup.com/San-Francisco-...
Check out more ClickHouse resources: https://altinity.com/resources/
Visit the Altinity Documentation site: https://docs.altinity.com/
Contribute to ClickHouse Knowledge Base: https://kb.altinity.com/
Join the ClickHouse Reddit community: https://www.reddit.com/r/Clickhouse/
----------------
Learn more about Altinity!
Site: https://www.altinity.com
LinkedIn: https://www.linkedin.com/company/alti...
Twitter: https://twitter.com/AltinityDB
MySQL InnoDB Cluster / ReplicaSet - TutorialKenny Gryp
Tutorial on MySQL InnoDB Cluster and ReplicaSet, a fully integrated product built on MySQL technology, by MySQL.
MySQL InnoDB Cluster and ReplicaSet provide failover/high availability and scaling features baked in; providing an integrated end-to-end solution that is easy to use.
This document provides an overview of Terraform and infrastructure as code using Terraform. It discusses what Terraform is, how to get started with Terraform including initializing a Terraform configuration, planning and applying changes, variables, modules, providers and resources. It also covers Terraform state and locking state for multi-user collaboration.
2 hour session where I cover what is Apache Camel, latest news on the upcoming Camel v3, and then the main topic of the talk is the new Camel K sub-project for running integrations natively on the cloud with kubernetes. The last part of the talk is about running Camel with GraalVM / Quarkus to archive native compiled binaries that has impressive startup and footprint.
The document discusses Oracle Autonomous Database and provides an agenda for a presentation. The agenda includes:
1. An overview of what Autonomous Database is and how it provides self-driving, self-securing, and self-repairing capabilities.
2. Key use cases for Autonomous Transaction Processing such as for transactional applications and mixed workloads.
3. How Autonomous Database can be used with microservices architectures.
4. Use cases for Autonomous Data Warehouse such as for data marts, warehouses, sandboxes, and machine learning.
5. How Autonomous Data Warehouse integrates with Oracle Analytics Cloud.
6. A demonstration of getting hands-on with
... or why Oracle still cares about CMAN and why you should do it too
The Oracle Connection Manager (CMAN) is the Swiss-army knife for database connections. It can be used for security, routing, high availability, single-point of contact... Starting with Oracle 18c, it has been extended with the new Traffic Director Mode (CMAN TDM), that allows transparent failover for applications that do not implement it natively.
In this session I will introduce briefly what CMAN is capable of, how to configure it in a high availability environment, and how the new release achieves a higher protection level.
Smart monitoring how does oracle rac manage resource, state ukoug19Anil Nair
An important requirement for HA and to provide scalability is to detect problems and resolve them quickly before the user sessions get affected. Oracle RAC along with its Family of Solutions work together cohesively to detect conditions such as "Un-responsive Instances", Network issues quickly and resolve them by either redirecting the work to other instances or redundant network paths
This document describes how to set up monitoring for MySQL databases using Prometheus and Grafana. It includes instructions for installing and configuring Prometheus and Alertmanager on a monitoring server to scrape metrics from node_exporter and mysql_exporter. Ansible playbooks are provided to automatically install the exporters and configure Prometheus. Finally, steps are outlined for creating Grafana dashboards to visualize the metrics and monitor MySQL performance.
Troubleshooting common oslo.messaging and RabbitMQ issuesMichael Klishin
This document discusses common issues with oslo.messaging and RabbitMQ and how to diagnose and resolve them. It provides an overview of oslo.messaging and how it uses RabbitMQ for RPC calls and notifications. Examples are given of where timeouts could occur in RPC calls. Methods for debugging include enabling debug logging, examining RabbitMQ queues and connections, and correlating logs from services. Specific issues covered include RAM usage, unresponsive nodes, rejected TCP connections, TLS connection failures, and high latency. General tips emphasized are using tools to gather data and consulting log files.
Spring Boot to Quarkus: A real app migration experience | DevNation Tech TalkRed Hat Developers
Running a Spring Boot application but still want to benefit from Quarkus and its supersonic, subatomic Java capabilities? Me too! With a “hello world” everything looks simple, but what about a real app? Will it be easy? Or fun? In this session we’ll show our experience migrating a Spring Boot app to Quarkus. Technologies involved in the app include Hibernate, Prometheus, REST endpoints, and more. Be prepared to listen to a journey of reality, failure, and wins in the Quarkus universe.
Using HashiCorp’s Terraform to build your infrastructure on AWS - Pop-up Loft...Amazon Web Services
Using Terraform to automate your infrastructure on AWS. What is Terraform and how is it different from Ansible. How to control cloud deployments using Terraform.
The document discusses Oracle database performance tuning. It covers reactive and proactive performance tuning, the top-down tuning methodology, common types of performance issues, and metrics for measuring performance such as response time and throughput. It also compares online transaction processing (OLTP) systems and data warehouses (DW), and describes different architectures for integrating OLTP and DW systems.
MySQL Performance Tuning. Part 1: MySQL Configuration (includes MySQL 5.7)Aurimas Mikalauskas
Is my MySQL server configured properly? Should I run Community MySQL, MariaDB, Percona or WebScaleSQL? How many innodb buffer pool instances should I run? Why should I NOT use the query cache? How do I size the innodb log file size and what IS that innodb log anyway? All answers are inside.
Aurimas Mikalauskas is a former Percona performance consultant and architect currently writing and teaching at speedemy.com. He's been involved with MySQL since 1999, scaling and optimizing MySQL backed systems since 2004 for companies such as BBC, EngineYard, famous social networks and small shops like EstanteVirtual, Pine Cove and hundreds of others.
Additional content mentioned in the presentation can be found here: http://speedemy.com/17
The document discusses the architecture and transaction handling of the InnoDB storage engine in MySQL. It describes InnoDB database files, tablespaces, pages, rows, indexes, transactions, locking, logging and replication. InnoDB provides transactional support with ACID properties, consistent reads, and mechanisms to avoid phantoms and maintain consistency under concurrent access.
This document discusses MySQL performance tuning and various MySQL products and features. It provides information on MySQL 5.6 including improved scalability, new InnoDB features for NoSQL access, and an improved optimizer. It also discusses MySQL Enterprise Monitor for performance monitoring, and the Performance Schema for instrumentation and monitoring internal operations.
This document summarizes a presentation comparing PostgreSQL and MySQL databases. It outlines the strengths and weaknesses of each, including PostgreSQL's strong advanced features and flexible licensing but lack of integrated replication, and MySQL's replication capabilities but immature security and programming models. It also discusses common application types for each database and provides an overview of the EnterpriseDB company.
This document compares the two major open source databases: MySQL and PostgreSQL. It provides a brief history of each database's development. MySQL prioritized ease-of-use and performance early on, while PostgreSQL focused on features, security, and standards compliance. More recently, both databases have expanded their feature sets. The document discusses the most common uses, features, and performance of each database. It concludes that for simple queries on 2-core machines, MySQL may perform better, while PostgreSQL tends to perform better for complex queries that can leverage multiple CPU cores.
The document discusses developing plugins for the MySQL INFORMATION_SCHEMA by creating custom tables. It provides steps to create a simple "Hello World" plugin that defines a table with two columns and fills it with sample data. The document also describes how to build and install the plugin so it can be queried from INFORMATION_SCHEMA like a regular table.
This document provides an overview of MySQL for Oracle DBAs, covering topics such as MySQL architecture, backup and recovery strategies, managing space and tables, and connecting MySQL to Oracle. The key points discussed include MySQL's product architecture and internal memory structures, filesystem layout for binaries, data and log files, InnoDB and MyISAM storage engines for managing space, and using tools like mysqldump, mysqlhotcopy, and mysqlbinlog for backups and point-in-time recovery.
Introduction to MySQL Enterprise MonitorMark Leith
The document is a presentation on MySQL Enterprise Monitor (MEM) by Mark Leith of Oracle. It introduces MEM as a distributed monitoring system for MySQL with a central Service Manager and agents installed on monitored hosts. The presentation includes sections on MEM architecture showing its core components, and a demo of features in the MEM UI like viewing instances, advisors, events, graphs, and query analysis.
Getting to Know MySQL Enterprise MonitorMark Leith
MySQL Enterprise Monitor is the monitoring and management solution for DBAs and developers delivered as part of MySQL Enterprise Edition. It provides background monitoring, alerting, trending, and analysis of the MySQL database and the statement traffic that is running within it.
View this session to learn how to install/configure, customize, and use MySQL Enterprise Monitor to suit your environment. Whether you use a single server or have hundreds of instances, MySQL Enterprise Monitor can provide great insights into how your environment is performing.
The document describes Performance Schema and ps_helper. Performance Schema is a feature in MySQL that collects runtime performance data and ps_helper is a tool that makes Performance Schema data easier to understand. It provides views, functions and stored procedures to summarize Performance Schema data for common use cases like analyzing user activity and statements.
The document outlines changes and new features in MySQL versions 5.7 through upcoming releases. Key points include:
- MySQL 5.7 development follows a milestone release process to stabilize new features before general availability. Four development milestone releases have been completed so far.
- Notable 5.7 features include statement timeouts, change replication without stopping SQL threads, and performance improvements like optimized UNION ALL queries.
- Some existing functionality will change in 5.7, like making replication more durable by default and producing errors for queries with only partial GROUP BY clauses.
- Ongoing efforts include refactoring and improving InnoDB, the optimizer, and other components for better performance and scalability. New features in development
The document discusses NoSQL APIs in MySQL. It provides an overview of the memcached caching system and the history of the HandlerSocket protocol. It then describes the NoSQL interface introduced in MySQL 5.6, which allows for memcached-style operations on MySQL data. It notes that MySQL 5.7 further improved the performance and scalability of this interface.
Performance Schema for MySQL TroubleshootingSveta Smirnova
The Performance Schema in MySQL provides tables and instruments for troubleshooting issues like locks, I/O bottlenecks, slow queries, memory usage, and replication failures. It contains over 500 instruments in MySQL 5.6 and over 800 in 5.7. The tables provide visibility into the internal workings of MySQL to analyze and optimize performance.
This document discusses various methods for optimizing performance of MySQL databases, including upgrading hardware and software, optimizing configuration settings, optimizing queries, and optimizing database schemas. It provides an example of using EXPLAIN plans and adding indexes to optimize queries on a database table to improve performance. The author recommends focusing on query optimization as the best method, using profilers and slow query logs to identify queries to optimize.
This document discusses the Performance Schema in MySQL, which records instrumentation data to help profile and monitor database activity. It provides an overview of the Performance Schema's components and tables, how it has evolved between MySQL versions to include more metrics and functionality, and examples of how to query the tables to analyze wait events, statements, stages and other performance data.
The MySQL sys schema was integrated fully into MySQL Server from version 5.7.7 and has been improved in MySQL 8.0. Whether you are a DBA trying to determine where the resources are being used on your database instance and by whom, or a developer trying to figure out why your MySQL statements are running too slowly, the MySQL sys schema can help. Join this session to learn how to better use the MySQL sys schema to answer your day-to-day questions—from the original developer of the MySQL sys schema.
MySQL 5.7 proposes several changes to improve performance and consistency including:
1. Making replication durable by default by setting sync_binlog and repository options.
2. Deprecating features like INNODB monitor tables and ALTER IGNORE TABLE in favor of newer standards.
3. Simplifying and restricting SQL modes to encourage stricter querying and remove ambiguous options. Explanations for errors and modes will also be improved.
The Proper Care and Feeding of a MySQL Database for Busy Linux Admins -- SCaL...Dave Stokes
If you are a Linux administrator and ALSO have to take care of a MySQL databases, this presentation if for you, While it will not turn you instantly into a DBA it will help you understand how to properly care and feed your instances
Proper Care and Feeding of a MySQL Database for Busy Linux AdministratorsDave Stokes
Do you 'also' have MySQL DBA responsibilities as part of your job but have no DBA training? This presentation covers a lot of the DBA level info for those who have a Linux admin background but are not DBAs
MariaDB / MySQL tripping hazard and how to get out again?FromDual GmbH
The document discusses common pitfalls and mistakes when using MariaDB/MySQL databases and how to avoid or recover from them, including issues related to different versions and forks of MariaDB and MySQL not being fully compatible, keeping implementations simple to avoid unnecessary complexity, and problems that can occur from table locking, disk space usage, and other operational concerns.
The Peoper Care and Feeding of a MySQL Server for Busy Linux AdminDave Stokes
Webcast 16 September 2015 with a big thanks to SolarWinds. This is a collection of best practices for Linux Admin who 'also have' database responsibility but are not DBAs
This document summarizes a presentation about designing systems to handle high loads when Chuck Norris is your customer. It discusses scaling architectures vertically and horizontally, RESTful principles, using NoSQL databases like MongoDB, caching with Memcached, search engines like Sphinx, video/image storage, and bandwidth management. It emphasizes that the right technology depends on business needs, and high-load systems require robust architectures, qualified developers, and avoiding single points of failure.
Linuxfest Northwest Proper Care and Feeding Of a MySQL for Busy Linux AdminsDave Stokes
This document provides tips and best practices for properly configuring and maintaining a MySQL server for busy Linux administrators. It recommends focusing on hardware resources like memory, storage, and networking. It also emphasizes the importance of backups, replication, monitoring tools, and security configurations. Proper configuration of these areas can help keep MySQL databases running smoothly and prevent issues that waste administrators' time.
This document provides a summary of a presentation on practical MySQL tuning. It discusses measuring critical system resources like CPU, memory, I/O and network usage to identify bottlenecks. It also covers rough tuning of MySQL parameters like the InnoDB buffer pool size, log file size and key buffer size. Further tuning includes application optimizations like query tuning with EXPLAIN, index tuning, and schema design. The presentation also discusses scaling MySQL through approaches like caching, sharding, replication and optimizing architecture and data distribution. Regular performance monitoring is emphasized to simulate increased load and aid capacity planning.
XPages Blast - Ideas, Tips and More.
This session will take you on a roller-coaster ride through the "best of the best" ideas and time-saving techniques for creating world-class XPages applications.
Thirty all new top tips - this is going to be fast-paced and packed with loads of information you will refer to time and time again! Everything from introductory tips on getting started with XPages, to complex tips - such as making use of Java. Also, the support app showing all of the tips and tricks.
The document provides an overview of how to perform basic troubleshooting and tuning for MySQL performance issues as a "5 Minute DBA". It recommends first checking server versions, hardware resources like CPU, memory, and I/O using tools like sar, iostat, vmstat, and top. If issues persist, it suggests reviewing configuration settings and database queries/indexes before further optimizing the database. The document stresses that quick fixes may not always be optimal and assumes a Linux/Unix environment.
Doctrine is a PHP library that provides persistence services and related functionality. It includes an object relational mapper (ORM) for mapping database records to PHP objects, and a database abstraction layer (DBAL). Other libraries include an object document mapper (ODM) for NoSQL databases, common annotations, caching, data fixtures, and migrations. The presentation provides an overview of each library and how to use Doctrine for common tasks like mapping classes, saving and retrieving objects, and schema migrations. Help and contribution opportunities are available on Google Groups, IRC channels, and the project's GitHub page.
This document discusses various profiling tools that can be used to analyze MySQL performance, including Oprofile, perf, pt-pmp, and the MySQL Performance Schema. It provides examples of how these tools have been used to identify and resolve specific MySQL performance bugs. While the Performance Schema is useful, it does not always provide sufficient detail and other system-wide profilers like Oprofile and perf are still needed in some cases to pinpoint performance issues.
Dynamic tracing of MariaDB on Linux - problems and solutions (MariaDB Server ...Valeriy Kravchuk
Linux with kernels 2.6+. provides different ways to add user probes to almost every other line of code dynamically, and collect the resulting trace and profiling data in a safe and efficient way. This session discusses basic use of ftrace, perf, bcc tools and bpftrace utility, highlights typical problems MariaDB DBAs and developers may hit while trying to apply them, as well as solutions to some of them.
How MySQL can boost (or kill) your application v2Federico Razzoli
This document provides tips for optimizing MySQL performance for applications. It discusses good practices for the MySQL configuration file such as enabling the slow query log and performance schema. It also covers using indexes appropriately, avoiding N+1 queries, performing operations like counting and deleting in SQL rather than application code, and properly using transactions.
This document discusses using Azure DevOps and Snowflake to enable continuous integration and continuous deployment (CI/CD) of database changes. It covers setting up source control in a repository, implementing pull requests for code reviews, building deployment artifacts in a build pipeline, and deploying artifacts to development, test, and production environments through a release pipeline. The document also highlights key Snowflake features like zero-copy cloning that enable testing deployments before production.
Linux /proc filesystem for MySQL DBAs - FOSDEM 2021Valeriy Kravchuk
Tools and approaches based on /proc sampling (like 0x.tools by Tanel Poder or ad hoc scripts) allow to measure individual thread level activity in MySQL server on Linux, like thread sleep states, currently executing system calls and kernel wait locations. If needed you can drill down into CPU usage of any thread or the system as a whole. Historical data can be captured for post factum analysis, without much impact on the system and no need to install or change anything in its configuration. In this presentation I am going to summarize what's possible with /proc and show useful examples for MySQL DBAs.
01 demystifying mysq-lfororacledbaanddeveloperv1Ivan Ma
This document provides an overview of MySQL for Oracle DBAs and developers, presented by Ivan Ma. It covers installing and securing MySQL, performance tuning techniques like using the Performance Schema and MySQL Enterprise Monitor tools. It also discusses using MySQL for NoSQL workloads through technologies like Memcached and MySQL Cluster, which provide scalable in-memory access and integration with the relational database. The document aims to help Oracle experts understand and get the most out of MySQL.
The document discusses using the PERFORMANCE_SCHEMA in MySQL 5.6 to analyze and troubleshoot performance issues. It provides an overview of the PERFORMANCE_SCHEMA, how to configure what is instrumented and collected, and examples of using it to find bottlenecks like long wait times on InnoDB mutexes. The PERFORMANCE_SCHEMA provides visibility into where time is spent and which resources are most used through its set of in-memory tables.
Hadoop Operations: Keeping the Elephant Running SmoothlyMichael Arnold
Pune Hadoop Admins Meetup
From its beginnings years ago at large Internet sites, Hadoop is spreading everywhere. There are multitudes of cool and interesting things that Hadoop allows your organization to do, but running the actual infrastructure may not be as sexy as the application(s) running on top. Operations can be pure grunt-work, exacerbated by the fact that there is usually one machine out of dozens (or more) that is throwing a wrench in the works. In this talk, I will cover my experiences of running Hadoop, provide some recommended practices to simplify your days and nights in the trenches, and highlight some of the lessons learned along the way.
Query Optimization with MySQL 5.6: Old and New TricksMYXPLAIN
The document discusses query optimization techniques for MySQL 5.6, including both established techniques and new features in 5.6. It provides an overview of tools for profiling queries such as EXPLAIN, the slow query log, and the performance schema. It also covers indexing strategies like compound indexes and index condition pushdown.
This document discusses MySQL indexing best practices. It explains that indexes can improve query performance but also waste space and time if unnecessary. The right balance must be found to achieve fast queries using an optimal index set. It provides examples of how to create indexes and how they can be used to optimize WHERE, JOIN, ORDER BY, and other clauses. It also discusses avoiding unnecessary or redundant indexes.
Advanced Query Optimizer Tuning and AnalysisMYXPLAIN
The document discusses techniques for identifying and addressing problems with a database query optimizer. It describes how to use tools like the slow query log, SHOW PROCESSLIST, and PERFORMANCE SCHEMA to find slow queries and examine their execution plans. The document provides examples of analyzing queries, identifying inefficient plans, and determining appropriate actions like rewriting queries or adjusting optimizer settings.
This document provides an overview of indexing in MySQL. It begins with definitions of terminology like B-Trees, keys, indexes, and clustering. It then covers topics like primary keys, compound indexes, and optimization techniques. Use cases are presented to demonstrate how to design indexes for different querying needs, such as normalization, geospatial queries, and pagination. The document aims to explain indexing concepts and help the reader design efficient indexes.
This document summarizes an introduction to advanced MySQL query and schema tuning techniques presented by Alexander Rubin. It discusses how to identify and address slow queries through better indexing, temporary tables, and query optimization. Specific techniques covered include using indexes to optimize equality and range queries, ordering fields in composite indexes, and avoiding disk-based temporary tables for GROUP BY and other complex queries.
Are You Getting the Best of your MySQL IndexesMYXPLAIN
The document discusses how to optimize the use of indexes in MySQL databases. It explains what indexes are, the different types like simple, composite, and full text indexes. It covers how MySQL uses indexes to match WHERE clauses, eliminate rows, and help with sorting and grouping. The document also discusses factors that cause MySQL to ignore indexes, like functions, joins, and queries with multiple clauses not fully covered by one index. It provides guidance on using EXPLAIN to check index usage and tips for index optimization.
This document discusses how to design indexes for databases using an analogy to a telephone book index. It explains that the order of columns in an index matters, as an index on last name and first name would help queries searching for a specific last name or last name and first name, but not queries searching just for a first name. The document also discusses how indexes can help with range queries and sorting, but the sorting must be on the column immediately following the search criteria columns in the index.
MySQL Indexing - Best practices for MySQL 5.6MYXPLAIN
This document provides an overview of MySQL indexing best practices. It discusses the types of indexes in MySQL, how indexes work, and how to optimize queries through proper index selection and configuration. The presentation emphasizes understanding how MySQL utilizes indexes to speed up queries through techniques like lookups, sorting, avoiding full table scans, and join optimizations. It also covers new capabilities in MySQL 5.6 like index condition pushdown that provide more flexible index usage.
MySQL 5.6 introduces several new query optimization features over MySQL 5.5, including:
1) Filesort optimization for queries with a filesort but a short LIMIT, improving performance over 2x in one example.
2) Index Condition Pushdown which pushes conditions from the WHERE clause into the index tree evaluation, improving a query over 5x faster by reducing the number of rows accessed.
3) Other optimizations like Multi-Range Read which improve performance of queries that access multiple ranges or indexes in a single query. The document provides examples comparing execution plans and performance between MySQL 5.5 and 5.6 to demonstrate the benefits of the new optimization features.
The index satisfies all three stars for this query:
1. movie_id and role_id columns satisfy equality predicates (★1)
2. nr_order column satisfies ordering (★2)
3. Index contains all selected columns (★3)
So this index would be very effective for this query.
The document discusses the EXPLAIN statement in MySQL. It provides examples of using the traditional EXPLAIN output and the new JSON format for EXPLAIN. The JSON format provides more detailed information about the query plan and execution in a structured format. It allows seeing things like how conditions are split and when subqueries are evaluated.
The document discusses how to use EXPLAIN to optimize SQL queries. EXPLAIN shows how tables are joined, indexes used, and records examined. It returns information like the number of tables, join types, and data access methods. The fastest strategies are const, which uses a primary or unique key to lookup at most one value, and eq_ref, which joins on a unique index. EXPLAIN helps identify inefficient queries to improve performance.
The document discusses improvements to the EXPLAIN statement in MySQL. Some key points discussed include:
- EXPLAIN was expanded in MySQL 5.6 to provide information for data modifying statements like INSERT, UPDATE, DELETE in addition to SELECT statements.
- A structured JSON output for EXPLAIN was introduced to make the query execution plan more easily readable.
- Optimizer traces were added to provide insight into how the query planner selected a particular execution plan.
This document discusses improving MySQL query performance through better use of indexes. It presents a six step process for identifying, analyzing and optimizing queries: 1) Capture relevant queries, 2) Identify worst offenders by response time or frequency, 3) Confirm execution plans, 4) Analyze execution plans and indexes used, 5) Optimize indexes and queries, 6) Verify performance improvements. Global application logging and MySQL's general query log are introduced as methods for capturing queries to analyze.
The document is about explaining the MySQL EXPLAIN statement. It provides an overview of EXPLAIN, how to read the query execution plan (QEP) produced by EXPLAIN, examples of QEPs, and limitations of the MySQL optimizer.
The document discusses covering indexes and provides examples of how they can improve query performance. A covering index contains all the columns needed to satisfy a query, avoiding the need to access table data stored on disk. Case studies show how adding a covering composite index on (subscription, name) improved a query to retrieve names by subscription date and order by name from over 3 seconds to under 0.01 seconds. Covering indexes are very beneficial for I/O-bound workloads by reducing disk access.
The document discusses the MySQL query optimizer. It covers logical transformations performed by the optimizer like negation elimination and equality propagation. It also discusses cost-based optimization where the optimizer assigns costs to different execution plans and joins/access methods to select the most efficient plan. The optimizer characteristics are also summarized, noting it produces a single left-deep linear query plan to minimize resource usage.
The document summarizes several case studies on optimizing large SQL queries. Case study 1 examines a query that runs slowly due to a column used as a bitmap being compared with a bitwise operator rather than being indexed directly. Case study 2 shows a query that could use indexes but data type mismatches prevent it. Case study 3 involves a dependent subquery that is rewritten as a left join to better use indexes.
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.
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.
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.
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
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.
Data Protection in a Connected World: Sovereignty and Cyber Securityanupriti
Delve into the critical intersection of data sovereignty and cyber security in this presentation. Explore unconventional cyber threat vectors and strategies to safeguard data integrity and sovereignty in an increasingly interconnected world. Gain insights into emerging threats and proactive defense measures essential for modern digital ecosystems.
What Not to Document and Why_ (North Bay Python 2024)Margaret Fero
We’re hopefully all on board with writing documentation for our projects. However, especially with the rise of supply-chain attacks, there are some aspects of our projects that we really shouldn’t document, and should instead remediate as vulnerabilities. If we do document these aspects of a project, it may help someone compromise the project itself or our users. In this talk, you will learn why some aspects of documentation may help attackers more than users, how to recognize those aspects in your own projects, and what to do when you encounter such an issue.
These are slides as presented at North Bay Python 2024, with one minor modification to add the URL of a tweet screenshotted in the presentation.
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.
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.
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
The DealBook is our annual overview of the Ukrainian tech investment industry. This edition comprehensively covers the full year 2023 and the first deals of 2024.
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.
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
In this follow-up session on knowledge and prompt engineering, we will explore structured prompting, chain of thought prompting, iterative prompting, prompt optimization, emotional language prompts, and the inclusion of user signals and industry-specific data to enhance LLM performance.
Join EIS Founder & CEO Seth Earley and special guest Nick Usborne, Copywriter, Trainer, and Speaker, as they delve into these methodologies to improve AI-driven knowledge processes for employees and customers alike.
3. The following is intended to outline our general product
direction. It is intended for information purposes 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 upon in making
purchasing decisions. The development, release, and timing
of any features or functionality described for Oracle’s
products remains at the sole discretion of Oracle.
4. Are you Dimitri?..
§ Yes, it's me :-)
§ Hello from Paris! ;-)
§ Passionated by Systems and Databases Performance
§ Previous 15 years @Sun Benchmark Center
§ Started working on MySQL Performance since v3.23
§ But during all that time just for fun only ;-)
§ Since last years officially @MySQL Performance full time now
§ http://dimitrik.free.fr/blog / @dimitrik_fr
5. Agenda
§ Overview
§ Analyzing MySQL Workload
§ Analyzing and Understanding of MySQL Internals
§ Performance improvements in MySQL 5.6 (and 5.7)
§ Benchmark results
§ Pending issues..
§Q&A
11. Why benchmarking MySQL?..
●
A good benchmark testing may help you understand ahead the
resistance of your solution to incoming potential problems ;-)
12. Why benchmarking MySQL?..
●
But keep it in mind:
●
Even a very powerful solution but
leaved in wrong hands may still be
easily broken!... :-)
13. The Main MySQL Performance Tuning
#1 Best Practice is... ???..
14. The Main MySQL Performance Tuning
#1 Best Practice is... ???..
USE YOUR BRAIN !!! :-)
15. The Main MySQL Performance Tuning
#1 Best Practice is... ???..
USE YOUR BRAIN !!! :-)
AND THIS IS THE
AND THIS IS THE
MAIN SLIDE! ;-))
MAIN SLIDE! ;-))
16. Before we started..
●
Please, keep in mind:
●
NOBODY knows everything ;-))
●
There is no absolute true in any topic around..
●
The best answer in most cases will be probably “It depends..” ;-))
●
So, again, “USE YOUR BRAIN!” is the best advice and the best option
●
Also, knowledge and understanding of problems are changing all the
time..
●
And probably even what I'll tell you today is already obsolete. ;-))
●
Enjoy thinking and digging problems deeply ;-))
●
MySQL Performance is a very fun topic (specially current days ;-))
17. Different Approach for different problems
●
You are discovering a production workload..
●
●
You are trying to understand why your production is running
slower time to time..
●
●
Tracing, debugging, analyzing, discovering of new problems ;-)
You are looking for a new platform for existing production
workload (or new apps under dev.)..
●
●
Full discovery..
Workload simulation, benchmarking, discovering of the next level
issues..
etc...
18. They all have something in common!
●
Monitoring !..
●
Choose a tool you're familiar with (or install one and become familiar)
●
Use a tool you can completely trust ;-)
●
●
●
●
Keep in mind that sometimes you may need a 5-10sec interval
measurements (or even less).. - not every tool is allowing..
Keep a history of your monitoring to be able to compare “good” and
“bad” cases..
When something is starting to go wrong, usually it'll be not in the place
which was always problematic, but in the place started to have a
different behavior.. - and your goal is to find it ;-)
Always monitor your HW and OS !!!
19. MySQL Enterprise Monitor (MEM) v.3.0
●
Absolutely fantastic product!
●
Try it! (and buy it if you like it! ;-) - improve your daily work experience!)
20. Monitoring & Analyzing with dim_STAT (as you ask ;)
●
All my graphs are built with it (download: http://dimitrik.free.fr)
●
All System load stats (CPU, I/O, Network, RAM, Processes,...)
●
Manly for Solaris & Linux, but any other UNIX too :-)
●
Add-Ons for Oracle, MySQL, PostgreSQL, Java, etc.
●
MySQL Add-Ons:
–
–
mysqlLOAD : compact data, multi-host monitoring oriented
–
mysqlWAITS : top wait events from Performance SCHEMA
–
InnodbSTAT : most important data from “show innodb status”
–
innodbMUTEX : monitoring InnoDB mutex waits
–
●
mysqlSTAT : all available data from “show status”
innodbMETRICS : all counters from the METRICS table
And any other you want to add! :-)
21. Think “Database Performance” from the beginning!
●
Server:
●
●
OS is important! - Linux, Solaris, etc.. (and Windows too!)
●
●
Having faster CPU is still better! 32 cores is good enough ;-)
Right malloc() lib!! (Linux: jemalloc, Solaris: libumem)
Storage:
●
●
SSD helping random access! (index/data) more and more cheaper
●
FS is important! - ZFS, UFS, QFS, VxFS, EXT3, EXT4, XFS, etc..
●
O_DIRECT or not O_DIRECT, AIO or not AIO, and be aware of bugs! ;-)
●
●
Don't use slow disks! (except if this is a test validation goal :-))
Do some generic I/O tests first (Sysbench, IObench, iozone, etc.)
Don't forget network !! :-) (faster is better, 10Gbit is great!)
22. Seek for your best option..
Performance
Lower Price
Security
23. What to monitor on Linux?..
●
First of all use the best Linux for you!
●
●
Install & use “jemalloc”; if XFS has problems, use EXT4 (nobarrier!)
●
●
Or ORACLE Linux if you don't know which one to choose ;-)
Use AIO + O_DIRECT, don't use “cfq” IO scheduler!..
Always keep an eye on:
●
RunQueue(!), CPU, RAM, Swap in/outProcesses: vmstat, top, psSTAT
●
Storage level: iostat, ..
●
Network: netLOAD, nicstat, …
●
Overall system activity: # perf top -z
–
●
perf: excellent profiler!
IMPORTANT : system monitoring usually helps to dig DB issues!
24. Know/ test/ check your platform limits / “features”..
●
My backup is finished on Linux faster than on Solaris same HW
●
●
●
Be sure first there is really no more I/O activity once backup is “finished”
Keep in mind Linux buffering..
Linux distro: MySQL Performance has x4 regression! Fix it!
●
How did you see it? – Our QA test is taking x4 times more time..
●
Which engine? – InnoDB..
●
What is innodb_flush_log_at_trx_commit value? – set to 1.. why?
●
Tried innodb_flush_log_at_trx_commit=2 ?.. – Oh! You fixed it!! Thanks!!
●
Wait, what did you “improve” recently in distro? – FS flushing, why?..
●
Well, the test in fact is proving that you did not “sync” on every fsync()
before, that's all.. But now in your FS flushing you get it fixed ;-)
25. The Infinitive Loop of Database Tuning...
Application
Application
DB Engine
DB Engine
OS
OS
Server
Server
Storage
Storage
#1 Monitoring
●#1 Monitoring
●#2 Tuning
●#2 Tuning
●#3 Optimization
●#3 Optimization
●#4 Improvement(s)
●#4 Improvement(s)
●#5 …
●#5 …
●...
●...
●goto #1
●goto #1
●
26. The Infinitive Loop of Database Tuning...
Even if in
Even if in
95% cases
95% cases
the problem
the problem
Is here!!! :-)
Is here!!! :-)
Application
Application
DB Engine
DB Engine
OS
OS
Server
Server
Storage
Storage
#1 Monitoring
●#1 Monitoring
●#2 Tuning
●#2 Tuning
●#3 Optimization
●#3 Optimization
●#4 Improvement(s)
●#4 Improvement(s)
●#5 …
●#5 …
●...
●...
●goto #1
●goto #1
●
28. MySQL Design
●
Multi-Threaded database
●
●
Simplified data access!
●
●
Fast context switch!
Concurrent access?.. Scalability?..
Storage Engines
●
Initially: MyISAM only
●
Then, with InnoDB: started to match expectations of a “true RDBMS” ;-)
●
Many other engines (MEMORY, CSV, NDB, PBXT, etc.)
●
CREATE TABLE ... ENGINE=<NAME_OF_ENGINE>
●
ALTER TABLE ... ENGINE=<NAME_OF_ENGINE>
●
Did you choose a right Engine?..
29. MyISAM Engine (since 1994)
●
Non-transactional! / No fast recovery! :-)
●
Cache
●
●
Data => FS cache
●
●
Index only
mysql> flush tables;
Single Writer @Table
●
Main bottleneck! => single writer
●
Solutions: delayed inserts, low priority
●
Query plan: Index forcing may be necessary (hint)
●
Extremely simple and lightweight
30. Why MySQL + MyISAM was successful ?..
●
Full Text search queries out-of-the-box!
●
SELECT count(*) ... :-))
●
Extremely SIMPLE!
●
my.conf => configuration parameters; mysql.server start / stop
●
Database => directory
●
Table => directory/Table.MYD, Table.MYI, Table.frm
●
$ cp Base1/Table.* /other/mysql/Base2
●
Data binary compatibility! (ex: reports via NFS)
●
Replication ready!
●
Very FAST! (until some limit :-))
●
RW workload is killing.. (but on 2CPU servers it was ok ;-))
32. InnoDB changing the game (since 2001)
●
Row-level locking
●
Index-only reads
●
True transactions / UNDO
●
Auto recovery
●
Double write / Checksums
●
Tablespaces or File-per-Table option
●
Buffer pool
●
Multi-threaded
●
Currently the fastest transactional disk-based MySQL Storage
Engine!
33. MySQL Performance (traditionally, in the past)
●
Choose the right Engine for each of your table/database
●
Read-Only / Text search => MyISAM
●
Read+Write / Transactions => InnoDB
●
Short/Small Transactions + DB fits in RAM => NDB
●
Tune / Optimize your queries
●
Once scalability limit is reached => go for Distributed:
●
●
Master / Slave(s) => role-based workload
●
●
Sharding
Any other similar :-)
Scalability = Main Performance Problem!...
●
But with Big Users on that time anyway: Google, Facebook, Amazon..
34. Things are changing constantly, stay tuned ;-)
●
MySQL/InnoDB Scalability:
●
●
2008 : up to 4CPU cores
●
2009 : up to 16CPU cores (+Sun)
●
2010 : up to 32CPU cores (+Oracle)
●
2012 : up to 48CPU cores..
●
2014 : …?? ;-)
●
●
2007 : up to 2CPU...
NOTE: on the same HW performance is better from version to version!
InnoDB today:
●
At least x4-8 times better performance than 2-3 years ago ;-)
●
Capable of over 100K 300K 500K QPS(!) + FTS & Memcached
38. How easy is to see the same
in Production now?.. ;-)
39. Starting points
●
What are your network limits?..
●
●
●
Latency? Max throughput? What CPU% is spent just for network?
Do you use prepared statements? (reducing traffic)
Can you use persistent connections?
●
●
Greatly improved in 5.6, yet more in 5.7 (55K Connect/s in 5.7 currently)
●
Higher QPS if more queries executed before disconnect!
●
●
Connect / Disconnect has its limits..
Thread cache size matters!
Do you use transactions on read-only requests?..
●
●
QPS is improved since 5.6 and yet more in 5.7
But you cannot get a rid from a traffic overhead due BEGIN / COMMIT
exchanges
40. Analyzing MySQL Workload
●
Understand the load first :
●
Hot queries <== could be improved?..
●
Hot tables / files <== storage ok? DB design?..
●
Bad query execution plans.. <== improve, force index, etc.
●
Row Lock contentions due Application Design <== will not scale..
●
Deadlocks due Application Logic.. <== will not scale..
●
NOTE: be sure you're not hitting some HW / OS limits (and MySQL is in
fact out of scope ;-))
41. Performance Schema since MySQL 5.6: Gold Mine!
●
Query digest (enabled by default) :
●
●
SELECT all queries having > N rows read
●
●
SELECT all queries with execution time > N ms
SELECT queries having table scans, not using indexes, etc..
FILE_IO (enabled by default) :
●
●
●
Time spent on every IO operation for every database file
Amount of each kind of IO operations for every file
Table Locks (enabled by default) :
●
●
See which tables are the most accessed
==> Just with these 3 metrics you already have an idea if things
are still going well or not.. - and MEM is excellent here! ;-)
42. Classic MySQL Monitoring
●
SHOW Commands:
●
●
mysql> show global status ;
●
mysql> show processlist ;
●
mysql> show engine innodb status ;
●
mysql> show engine innodb mutex ;
●
●
mysql> status ;
INFORMATION_SCHEMA.* , InnoDB METRICS table, etc..
Important :
●
only PFS instrumentation / query is truly lock free..
●
every query during its execution uses 1 CPU core full time!
●
excessive requesting may significantly lower an overall performance!
43. So far, what do you have to look on?..
●
MySQL Server general:
●
●
Query/sec, Select/sec, Commit/sec, Connect/sec, Connections, Abort...
InnoDB:
●
BP usage/ dirty%/ page hit%
●
Checkpoint Age, REDO logs rates (MB/sec, Writes/sec, Sync time/sec)
●
Adaptive Flushing rates, Sync Flushing rates, Sync Flushing waits
●
LRU Flushing stats, User Threads LRU Flushing, ..
●
History List Length (purge)
●
Mutex Waits (InnoDB, PFS)
●
File IO Waits (PFS)
●
etc...
44. Suspecting a problem?.. - Benchmark!
●
Have a clear goal!
●
●
Otherwise: I've obtained all these results, and now... so what?..
Want to simulate your production workload?..
●
●
●
Then just simulate it! (many SW available, not always OSS/free)
Hard to simulate? - adapt some generic tests
Want to know capacity limits of a given platform?
●
●
Still try to focus on the test which are most significant for you!
Want just to validate config settings impacts?
●
●
●
Focus on tests which are potentially depending on these settings
Or any, if the goal to prove there are not depending ;-)
Well, just keep thinking about what you're doing ;-)
45. Test Workload
●
Before to do something complex...
●
Be sure first you're comfortable with
“basic” operations!
●
●
Many tables?
●
Short queries?
●
●
Single table?
Long queries?
Remember: any complex load just
represents a mix of simple operations..
●
So, start from as simple as possible..
●
And then increase complexity progressively..
46. Popular “Generic” Test Workloads @MySQL
●
Sysbench
●
●
OLTP, RO/RW, 1-table, since v0.5 N-table, lots load options, deadlocks
DBT2 / TPCC-like
●
●
●
OLTP, RW, very complex, growing db, no options, deadlocks
In fact using mostly only 2 tables! (thanks Performance Schema ;-))
dbSTRESS
●
●
linkbench (Facebook)
●
●
OLTP, RO/RW, several tables, one most hot, configurable, no deadlocks
OLTP, RW, very intensive
DBT3
●
DWH, complex heavy query, loved by Optimizer Team ;-)
47. MySQL Performance: No Silver Bullet !!!
Internal Limits..
Internal Limits..
There is
There is
No Silver
No Silver
Bullet!!!
Bullet!!!
MySQL
MySQL
Configuration Settings..
Configuration Settings..
InnoDB
InnoDB
Server, OS, FS
Server, OS, FS
Query Optimization..
Query Optimization..
Storage
Storage
BBU, SSD
BBU, SSD
Application Contentions..
Application Contentions..
48. MySQL Config settings
●
Ask yourself right questions and start with some basic params:
●
●
Double write buffer? Checksums?
●
innodb_flush_log_at_trx_commit= 1 / 2 ??
●
Flush Method = O_DIRECT + ASYNC
●
Binlog Sync? - binlog group commit is since MySQL 5.6 only!
●
File per table?
●
IO capacity = 2000
●
●
Buffer Pool size / Buffer Pool Instances
Etc..
Then adapt then to discovered your HW/OS and MySQL
Internal limits!..
51. Example: Buffer Pool Instances
●
RW intensive workload:
●
BP instances = 1/ 2/ 4/ 8
52. Workload: Read-Only oriented
●
Bigger Buffer Pool (BP) is better
●
BP < dataset = IO-bound
●
TRX list (kernel_mutex, since 5.6: trx_sys mutex)
●
Read view
●
Auto-commit or transactions?..
●
●
Prepared statements
●
●
Grouping many queries within a single transaction may also largely
reduce MDL locking, but still keep them short ! (check with PFS)
Observed 10% performance improvement in 5.6 (while Parser time is
not more than 3% according to profiler)..
Read-Only transactions!
53. InnoDB: Read-Only Transactions in 5.6
●
Sysbench OLTP_RO Point-Selects:
●
Concurrent user sessions: 1, 2, 4 .. 1024
●
Using of transactions in sysbench = 0 / 1
54. InnoDB: Read-Only Transactions in 5.6 (Apr.2013)
●
Sysbench OLTP_RO Point-Selects:
●
Concurrent user sessions: 1, 2, 4 .. 1024
●
Using of transactions in sysbench = 0
55. InnoDB : false sharing of cache-line = true killer
●
RO or RW Workloads
●
Same symptoms in 5.5 & 5.6 : no QPS improvement between 16 and 32
user sessions:
56. InnoDB : false sharing of cache-line fixed!
●
RO or RW Workloads
●
“G5” patch! :-)
●
Over x2(!) times better on Sysbench OLTP_RO,
●
x6(!) times better on SIMPLE-Ranges!
●
NOTE: the fix is not applicable on 5.5..
57. MySQL Internals: “killer” LOCK_open mutex
●
MySQL 5.5 and before:
●
Keep “table_open_cache” setting big enough!
●
Monitor global status for '%opened%'
●
●
Once this contention become the most hot – well, time to upgrade to
5.6 ;-))
Since MySQL 5.6:
●
Fixed: several table open cache instances
●
But it doesn't mean you can use a small “table_open_cache” either ;-)
●
Monitor PFS Waits!
●
Monitor “table_open_cache%” status variables!
●
Keep “table_open_cache_instances” at least bigger than 1
58. MySQL 5.6 Internals : low table_open_cache
●
MySQL 5.6 :
●
Not big enough “table_open_cache” setting
59. MySQL 5.6 Internals : low table_open_cache (2)
●
MySQL 5.6 :
●
Not big enough “table_open_cache” setting
●
PFS Waits monitoring: LOCK_table_cache become the most hot:
●
Table_open_cache% status:
60. MySQL 5.6 Internals : table_open_cache_instances
●
MySQL 5.6 :
●
When LOCK_table_cache wait is on top, the gain is usually well visible:
61. Workload: Read-Write
●
RW activity
●
●
Updates only? Insert? Delete? R/W %ratio?
Bigger Buffer Pool (BP) is still better
●
BP < dataset = IO-bound Reads(!) or R+W
●
BP > dataset = CPU-bound or IO-bound Writes(!)
●
REDO size matters a lot! (up to 2TB in 5.6)
●
Adaptive Flushing matters a lot!
●
LRU flushing matters a lot as well!
●
Tip: Neighbor Pages flushing = off / on
62. But let me tell you now the
whole story first! ;-)
63. Jan.2009 : Long RW Intensive Test
●
RW Workload:
●
128 concurrent users, 500M REDO, dirty pages= 15%
●
But let's get a look on the real state of BP:
64. InnoDB Internals: Dirty pages
●
How does it work?..
●
●
SQL> show innodb
status G
But why my dirty
pages% setting
is ignored?...
●
●
Buffer pool
> free
> data
innodb_buffer_pool_size = M
> dirty
innodb_max_dirty_pages_pct = 15%
Mystery?...
All votes:
it's impossible ;-)
REDO
DATA / INDEX
innodb_log_file_size = 500M
65. InnoDB Internals: Dirty pages and REDO?..
●
What if I'll reduce REDO size now?..
●
REDO: 500M => 128M
Buffer pool
> free
> data
innodb_buffer_pool_size = M
> dirty
innodb_max_dirty_pages_pct = 15%
REDO
DATA / INDEX
innodb_log_file_size = 128M
66. InnoDB Internals: Dirty pages and REDO?..
●
What if I'll reduce REDO size now?..
●
●
REDO: 500M => 128M
Forcing lower
Dirty Pages Amount!
Buffer pool
> free
> data
innodb_buffer_pool_size = M
> dirty
innodb_max_dirty_pages_pct = 15%
REDO
DATA / INDEX
innodb_log_file_size = 128M
67. Any Changes on RW Test now?..
●
REDO = 500M
●
REDO = 128M
68. Fine, but..
●
Remained questions:
●
Why finally Dirty Pages% setting is completely ignored?...
●
While, after all, any dangers to have many dirty pages?...
●
And what is the impact of REDO logs size?..
71. InnoDB Internals: Impact of REDO size
●
RW Intensive Load
●
●
Result: 6000 TPS => 8000 TPS! 30% better!!!
●
●
REDO size = 128M => 1024M
For such an improvement we may ignore Dirty Pages% ;-))
But : WHY these TPS drops?...
72. InnoDB Internals: Analyzing the code..
●
Master thread logic:
Master Thread
loop: //Main loop
...
if( dirty pct > limit)
flush_batch( 100% IO);
...
do {
pages= trx_purge();
if( 1sec passed ) flush_log();
} while (pages);
...
goto loop;
Buffer pool
> free
> data
> dirty
REDO
DATA / INDEX
73. InnoDB Internals: Analyzing the code..
●
Master thread may never leave purge loop!!!
Master Thread
loop: //Main loop
...
if( dirty pct > limit)
flush_batch( 100% IO);
...
do {
pages= trx_purge();
if( 1sec passed ) flush_log();
} while (pages);
...
goto loop;
Buffer pool
> free
> data
> dirty
REDO
DATA / INDEX
74. InnoDB Internals: Analyzing the code..
●
But if Master thread is never leave purge loop...
●
Who is then flushing Dirty Pages?...
Buffer pool
> free
> data
> dirty
REDO
DATA / INDEX
75. InnoDB Internals: Analyzing the code..
●
But if Master thread is never leave purge loop...
●
●
Who is then flushing Dirty Pages?...
Redo log constraints:
●
●
●
●
Cyclic, need free space
Checkpoint Age: diff between the
current LSN in redo and the oldest
dirty page LSN
Checkpoint Age cannot out-pass the max
checkpoint age (redo log size)
Buffer pool
> free
> data
> dirty
If Checkpoint Age >= 7/8 of Max Age
=> Flush ALL dirty pages regardless IO capacity!!!
REDO
(“Furious Flushing”)
DATA / INDEX
76. InnoDB Internals: Introducing Purge Thread
●
Purge Thread is the MUST !!!
Master Thread
loop: //Main loop
...
sleep( 1 );
...
if( dirty pct > limit)
flush_batch( 100% IO);
...
flush_log();
...
goto loop;
Purge Thread
loop:
sleep( ... );
do { pages=
trx_purge();
} while (pages);
goto loop;
Buffer pool
> free
> data
> dirty
REDO
DATA / INDEX
79. InnoDB Purge since MySQL 5.5
●
Purging has a cost! (similar to Garbage Collecting)
●
●
●
Since MySQL 5.5: single purge thread (off by default)
Since MySQL 5.6: several purge thread(s) (up to 32)
However, Purge may lag and do not follow workload..
●
●
Ex.: On aggressive RW got 400GB of undo records within few hours(!)
●
●
This is very bad when happens...
Then it took days to reach zero in History Length..
The main problem is the past – how to dose purging?..
●
●
Since 5.6: with many threads, Purge become auto-stable itself
Still missing a dynamic config option to say how many purge threads to
run in parallel right now (but it'll be fixed soon ;-))
80. InnoDB : Purge improvement in 5.6
●
Several Purge Threads :
●
NOTE: activation is auto-magical (I'm serious ;-))
81. InnoDB : Purge improvement in 5.6
●
Fixed max purge lag code!
●
●
●
innodb_max_purge_lag
innodb_max_purge_lag_delay <= configurable!
Setting innodb_max_purge_lag=1M:
82. InnoDB Internals: “Furious Flushing”
●
Direct dependence on REDO log size
●
NOTE:
●
●
●
●
No direct dependence
on amount of dirty
pages and REDO size!
Depends on workload!
Buffer pool
> free
> data
innodb_buffer_pool_size = M
> dirty
innodb_max_dirty_pages_pct = N
However, bigger REDO
allows more dirty pages..
And recovery is way
faster today!
REDO
DATA / INDEX
innodb_log_file_size = L
83. InnoDB: REDO log constraints
●
REDO log constraints: (Always monitor Checkpoint Age!!!)
●
●
●
Cyclic, need free space
Buffer pool
Checkpoint age: diff between the current LSN in REDO
> free
and the oldest dirty page LSN
Checkpoint age cannot out-pass the max checkpoint
age (redo log size)
●
●
●
If Checkpoint age >= 7/8 of Max age => Flush ALL dirty!
> data
> dirty
=> AKA “furious flushing”...
Adaptive Flushing:
●
Keep REDO under Max age
●
Respecting IO capacity limit
REDO
DATA / INDEX
84. InnoDB: Adaptive Flushing
●
MySQL 5.5:
●
●
●
Estimation based
Sometimes works ;-)
MySQL 5.6 :
●
●
●
Based on REDO write rate + I/O capacity Max
Involving batch flushing with N pages to flush (progressive, depending
on REDO %free) + page age limit (according REDO rate)
Tuning:
●
innodb_io_capacity / innofb_io_capacity_max
●
innodb_adaptive_flushing_lwm / innodb_max_dirty_pages_pct_lwm
●
ALL are dynamic!
●
Monitor Checkpoint Age..
87. InnoDB Adaptive Flushing: Fine Tuning
●
Monitor your Flushing rate / capabilities..
●
Adapt IO capacity and REDO size :
88. InnoDB and I/O Performance
●
Keep in mind the nature of I/O operation!
●
●
Sequential Read (SR)
●
Random Write (RW)
●
●
Sequential Write (SW)
Random Read (RR)
InnoDB
Buffer pool
> free
> data
●
Data files <= SW,SR,RW,RR
●
Redo log <= SW
●
Bin log <= SW
●
Double write <= SW
> dirty
BINLOG
DATA / INDEX
double write buffer
REDO
89. InnoDB and I/O Performance
●
Avoid a hot-mix of I/O operations!
●
Random Read (RR) <= most painful & costly!!!
●
Place REDO on different LUNs/disks
●
●
Place BINLOG on a separated
storage array!
I/O Settings
●
I/O write threads
●
> data
I/O capacity
●
Buffer pool
> free
I/O read threads
> dirty
BINLOG
DATA / INDEX
double write buffer
REDO
90. InnoDB: Doublewrite Buffer
●
Protecting from partially written pages
●
Data first written into Doublewrite buffer (sys.tablespace)
●
Then flushed to the datafiles
●
●
On recovery: if partially written page discovered => use its image from
doublewrite buffer
What is the cost?..
●
Doublewrite I/O is sequential, so should be fast
●
Writes will do less sync calls:
–
Instead of sync on every page write
–
Sync once on doublewrite buffer write
–
Then once on the datafile(s) for the same chunk of pages
91. InnoDB: Doublewrite buffer real impact?
●
Usually:
●
●
●
performance remains the same (or better)
+ recovery guarantee!
In some cases:
●
Up to 30% performance degradation...
●
Why?...
92. InnoDB: Doublebuffer and I/O dependency
●
Random Reads are killing!
●
RR = ~5ms wait per operation on HD
●
Example:
–
Application is doing 30.000 IO op/s
–
All operations are SW/RW
–
Now 5% of Writes become RR
–
What about performance?..
Buffer pool
> free
> data
> dirty
BINLOG
DATA / INDEX
double write buffer
REDO
93. InnoDB: Doublebuffer and I/O dependency
●
Random Reads are killing!
●
RR = 5ms wait per operation
●
Example:
–
Application is doing 30.000 IO op/s
–
All operations are SW/RW
–
Now 5% of Writes become RR
–
Performance => 10.000 IO ops/s...
–
x3 times degradation!
–
100 SW= 100 x 0.1ms = 10ms
–
95 SW + 5 RR = 9.5ms + 25ms
Buffer pool
> free
> data
> dirty
BINLOG
DATA / INDEX
double write buffer
REDO
94. InnoDB: Doublebuffer and I/O dependency
●
Workaround: move doublewrite buffer on REDO disks
●
Have to set innodb_file_per_table initially for DB
●
Move system tablespace on REDO disks:
$ mv /DATA/ibdata1 /LOG
$ ln -s /LOG/ibdata1 /DATA
Buffer pool
> free
> data
●
Or just use SSD !!! ;-)
> dirty
BINLOG
DATA / INDEX
double write buffer
REDO
95. User Concurrency scenarios
●
Single user?..
●
●
●
With a bigger code path today 5.6 simply cannot be faster than 5.5
But then, why you're not considering Query Cache? ;-)
More users?..
●
●
●
Up to 8-16 concurrent users all internal contention are not yet hot
So, 5.6 will not be better yet..
More than 16 users?..
●
●
●
Then you'll feel a real difference, but if you have at least 16cores ;-)
Or if you have really a lot of concurrent users
But don't forget other 5.6 improvements either!
●
On-line DDL, Binlog group commit, Memcached, etc..
96. High Concurrency Tuning
●
●
If bottleneck is due a concurrent access on the same data (due
application design) – ask dev team to re-design ;-)
If bottleneck is due MySQL/InnoDB internal contentions, then:
●
If you cannot avoid it, then at least don't let them grow ;-)
●
Try to increase InnoDB spin wait delay (dynamic)
●
Try innodb_thread_concurrency=N (dynamic)
●
CPU taskset / prcset (Linux / Solaris, both dynamic)
●
Thread Pool
●
NOTE: things with contentions may radically change since 5.7, so stay
tuned ;-)
97. InnoDB Spin Wait Delay
●
RO/RW Workloads:
●
With more CPU cores internal contentions become more hot..
●
Bind mysqld to less cores helps, but the goal is to use more cores ;-)
●
Using innodb_thread_concurrency is not helping here anymore..
●
So, innodb_spin_wait_delay is entering in the game:
98. Tune InnoDB Spin Wait Delay
●
Notes :
●
is the max random delay on “sleep” within a spin loop in wait for lock..
●
Ideally should be auto.. while the same tuning works for 5.5 as well ;-)
●
General rule: default is 6, may need an increase with more cores
●
Test: 32-HT/ 32/ 24/ 16cores, spin delay = 6 / 96 :
99. Thread Pool @MySQL
●
None of these solutions will help to increase performance!
●
●
it'll just help to keep the peak level constant (and you yet need to
discover on which level of concurrency you're reaching your peak ;-))
ThreadPool in MySQL 5.5 and 5.6 is aware if I/O are involved!
●
So, better than innodb thread concurrency setting or taskset
●
May still require spin wait delay tuning!
●
The must for high concurrency loads!
●
●
May still start to show a difference since 32-128 concurrent users! (all
depends on workload)..
Keep in mind that OS scheduler is not aware how to manage user
threads most optimally, but ThreadPool does ;-)
103. InnoDB High Concurrency: AHI
●
Adaptive Hash Index (AHI)
●
Helps a lot on Read-Only workloads
●
In fact it helps always until itself become not actively modified
●
AHI contention is seen as its btr_search_latch RW-lock contetnion
●
So, on Read+Write become a huge bottleneck..
●
In many cases on RW the result is better with AHI=off..
●
NOTE: there is still a big mystery around AHI when it's having
btr_search_latch contention even when there is no changes at all (pure
RO in memory).. - expected to be fixed in 5.7 ;-)
104. Testing Apples-to-Apples...
●
Comparing MySQL 5.6 vs 5.5 :
●
Don't have G5: dead..
●
Don't have open table cache instances: bad..
●
Don't have improved Adaptive Flushing; bad..
●
Don't have fixed Purge & Lag: danger!..
●
Don't have binlog group commit and use binlog: dead..
●
Etc. etc. etc.
●
●
NOTE: some “improvement” are also fixes which are making stuff
working properly, but coming with additional overhead (like Purge)..
NOTE: when comparing 5.6 and 5.5 keep in mind that Performance
Schema is enabled by default in 5.6, and not in 5.5, so think to disable it
in both (as 5.5 also has a way less PFS instrumentation)..
115. MySQL 5.6: Pending issues
●
Index lock..
●
Lock_sys contention..
●
Trx_sys contention..
●
MDL scalability..
●
Flushing limits..
●
LRU flushing..
●
Design bug on block locking.. (was here from the beginning)
●
Not able yet to use 100% I/O capacity on a powerful storage..
●
“Mysterious” contentions on dbSTRESS..
●
etc..
116. MySQL 5.7: Work in progress.. ;-)
●
Index lock.. <== fixed !
●
Lock_sys contention.. <== lowered !
●
Trx_sys contention.. <== improved a lot !!!
●
MDL scalability.. <== in progress..
●
Flushing limits.. <== in progress..
●
LRU flushing.. <== in progress..
●
Design bug on block locking.. (was here from the beginning)
●
Not able yet to use 100% I/O capacity on a powerful storage..
●
“Mysterious” contentions on dbSTRESS..
●
Etc.. <== well, ALL in progress / investigation ;-)
117. MySQL 5.7: DMR2 (Sep.2013)
●
OLTP_RO Point-Selects 8-tables: 500K QPS !!!
●
UNIX socket, sysbench 0.4.8 (older, using less CPU)
118. MySQL 5.7: DMR2 (Sep.2013)
●
OLTP_RO Point-Selects 8-tables: 440K QPS
●
IP port, sysbench 0.4.13 (“common”, using more CPU)
119. MySQL 5.7: DMR2 (Sep.2013)
●
OLTP_RO Point-Selects-TRX 8-tables: 200K QPS
●
IP port, sysbench 0.4.13 (“common”, using more CPU)
120. MySQL 5.7: DMR2 (Sep.2013)
●
OLTP_RO Point-Selects 8-tables: Scalability..
●
UNIX socket, sysbench 0.4.8 (older, using less CPU)
121. MySQL 5.7: DMR2 (Sep.2013)
●
OLTP_RO Point-Selects 8-tables: Scalability..
●
IP port, sysbench 0.4.13 (using more CPU)
122. MySQL 5.7: DMR2 (Sep.2013)
●
OLTP_RO Point-Selects-TRX 8-tables: Scalability..
●
IP port, sysbench 0.4.13 (using more CPU)
123. MySQL 5.7: DMR2 (Sep.2013)
●
OLTP_RO 8-tables: 280K QPS
●
IP port, sysbench 0.4.13 (“common”, using more CPU)
124. MySQL 5.7: DMR2 (Sep.2013)
●
OLTP_RO 1-table: lower than 5.6...
●
Due higher MDL contentions, work in progress..
125. MySQL 5.7: DMR2 (Sep.2013)
●
OLTP_RW 8-tables: 265K QPS
●
IP port, sysbench 0.4.13 (“common”, using more CPU)
126. MySQL 5.7: DMR2 (Sep.2013)
●
OLTP_RW 1-table: lower than 5.6
●
MDL contention, work in progress..
127. THANK YOU !!!
●
All details about presented materials you may find on:
●
http://dimitrik.free.fr - dim_STAT, Benchmark Reports
●
http://dimitrik.free.fr/blog - Articles about MySQL Performance