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.
Get the best out of MySQL Cluster, presentation covers:
- Tuning and optimization to exploit the auto-sharded, distributed design of MySQL Cluster
- Using Adaptive Query Localization to scale cross-shard JOINs
- Data access patterns, schema and query optimizations
- Recommended tuning parameters
Tune in to the on-demand webinar: http://www.mysql.com/news-and-events/on-demand-webinars/display-od-719.html
This document provides an overview of PostgreSQL and instructions for installing and configuring it. It discusses using the initdb command to initialize a PostgreSQL database cluster and create the template1 and postgres databases. It also explains that the template1 database serves as a template that is copied whenever new databases are created.
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.
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.
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.
MySQL InnoDB Cluster - Advanced Configuration & OperationsFrederic Descamps
The document discusses various methods for provisioning and monitoring new members joining a MySQL InnoDB cluster. It describes the incremental recovery and clone-based provisioning processes. It provides guidance on forcing the use of clone over incremental recovery for both provisioning and recovery scenarios. The document also discusses using MySQL Shell commands and Performance Schema tables to monitor the provisioning and recovery processes, as well as the overall health and performance of the cluster.
Understanding MySQL Performance through BenchmarkingLaine Campbell
The document discusses using benchmarks to measure database performance. It explains that benchmarks help identify bottlenecks, understand system behavior, and plan for growth. The mysqlslap tool is introduced for performing database benchmarks. Mysqlslap can run tests to measure throughput, latency, and stability under different conditions like load, concurrency, and spikes. Examples are provided for how to use mysqlslap to benchmark different engines, schemas, and workloads.
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.
Replication in PostgreSQL tutorial given in Postgres Conference 2019Abbas Butt
This document provides an overview of replication in PostgreSQL, including the various methods and configurations. It discusses replication at both the physical and logical levels. At the physical level, it covers disk-based replication using NAS, file system based replication using DRBD, and log shipping based approaches at both the file and block levels. It also covers logical replication using trigger-based replication with Slony-I, statement-based replication with pgpool-II, and logical decoding-based approaches. Details are provided on setting up and configuring each method, including performing failovers.
ClickHouse Data Warehouse 101: The First Billion Rows, by Alexander Zaitsev a...Altinity Ltd
Columnar stores like ClickHouse enable users to pull insights from big data in seconds, but only if you set things up correctly. This talk will walk through how to implement a data warehouse that contains 1.3 billion rows using the famous NY Yellow Cab ride data. We'll start with basic data implementation including clustering and table definitions, then show how to load efficiently. Next, we'll discuss important features like dictionaries and materialized views, and how they improve query efficiency. We'll end by demonstrating typical queries to illustrate the kind of inferences you can draw rapidly from a well-designed data warehouse. It should be enough to get you started--the next billion rows is up to you!
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.
This document discusses MySQL Enterprise Monitor, which is a monitoring and performance tool for database administrators (DBAs). It provides an overview of MySQL Enterprise Monitor's architecture and features, and how it can help DBAs monitor MySQL performance, identify issues, and ensure high availability. The document also provides examples of how organizations like Big Fish Games have used MySQL Enterprise Monitor to improve database performance.
The document summarizes several industry standard benchmarks for measuring database and application server performance including SPECjAppServer2004, EAStress2004, TPC-E, and TPC-H. It discusses PostgreSQL's performance on these benchmarks and key configuration parameters used. There is room for improvement in PostgreSQL's performance on TPC-E, while SPECjAppServer2004 and EAStress2004 show good performance. TPC-H performance requires further optimization of indexes and query plans.
MySQL InnoDB Cluster and Group Replication in a Nutshell: hands-on tutorialFrederic Descamps
If you are trying to migrate from asynchronous Master-Slave environment to a full HA solution using MySQL InnoDB Cluster 8.0, this tutorial shows you how to proceed and how to manage and monitor your cluster.
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.
Better than you think: Handling JSON data in ClickHouseAltinity Ltd
Robert Hodges shows how ClickHouse, a relational database with tables, can offer high-performance analysis of JSON data. This talk provides a cookbook of schema design, indexing, data loading, and query tricks we gave learned over years of helping users build analytical apps for servicds logs, observability data, financial transactions, and other types of semi-structured data. Robert Hodges is CEO of Altinity and a certified database geek.
https://altinity.com
https://www.meetup.com/San-Francisco-Bay-Area-ClickHouse-Meetup
This document provides an overview of Oracle 12c Pluggable Databases (PDBs). Key points include:
- PDBs allow multiple databases to be consolidated within a single container database (CDB), providing benefits like faster provisioning and upgrades by doing them once per CDB.
- Each PDB acts as an independent database with its own data dictionary but shares resources like redo logs at the CDB level. PDBs can be unplugged from one CDB and plugged into another.
- Hands-on labs demonstrate how to create, open, clone, and migrate PDBs between CDBs. The document also compares characteristics of CDBs and PDBs and shows how a non-C
Sharding in MongoDB allows for horizontal scaling of data and operations across multiple servers. When determining if sharding is needed, factors like available storage, query throughput, and response latency on a single server are considered. The number of shards can be calculated based on total required storage, working memory size, and input/output operations per second across servers. Different types of sharding include range, tag-aware, and hashed sharding. Choosing a high cardinality shard key that matches query patterns is important for performance. Reasons to shard include scaling to large data volumes and query loads, enabling local writes in a globally distributed deployment, and improving backup and restore times.
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.
The document describes the MySQL SYS schema, which provides views, procedures, and functions to help database administrators, developers, and operations teams perform common debugging and tuning tasks. It includes summary views that breakdown user activity by I/O usage, stages, and statement details. The SYS schema also includes views and functions for analyzing I/O performance and retrieving the latest file I/O events. It can be installed on MySQL servers to provide a standardized way of accessing performance data.
Instrumenting plugins for Performance SchemaMark Leith
This document discusses how to instrument plugins for the MySQL Performance Schema to provide visibility into plugin operations and avoid "black holes" in performance data. It covers the main interfaces for instrumenting threads, file/memory/network operations. An example audit plugin is provided that instruments mutexes, files, stages. The Performance Schema output shows the staged, waited events for a query.
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.
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.
Performance Schema and Sys Schema in MySQL 5.7Mark Leith
MySQL 5.7 now includes the Sys Schema by default, which builds upon the awesome instrumentation framework laid by Performance Schema.
Performance Schema has had 23 worklogs completed in 5.7 alone, such as memory instrumentation, tying in transactions and stored programs in to the current statement/stage/wait instruments and wait graph, prepared statement instruments, metadata lock information, improved session status and variable reporting, the new structured replication tables, and more.
The Sys schema builds upon this strong foundation with easy reporting views and functions, as well as procedures to help both set up and manage the configuration of Performance Schema, and help diagnose performance issues with your database instances on the whole.
Come along and hear from the original developer of the Sys schema about all of these exciting improvements in MySQL instrumentation for the upcoming MySQL 5.7 release!
MySQL Backup and Security Best PracticesLenz Grimmer
MySQL Backup and Security discusses best practices for securing MySQL servers and backing up MySQL data. It provides guidance on improving MySQL security through password protection, access controls and encryption. It also reviews various backup methods like logical backups using mysqldump, physical backups of data files, and techniques like MySQL replication and file system snapshots for backups with reduced downtime.
- Database backups are important to prevent data loss from hardware failures, user errors, or application issues. Regular backups should include the database contents, log files, and configuration information.
- Common backup methods include using mysqldump to dump databases into SQL files, copying table files, making delimited text file backups, and enabling binary logging for incremental backups by replaying log files.
- Backups should be stored in multiple locations including on separate disks, servers, or cloud storage. A backup strategy includes performing full and incremental backups on a schedule as well as before and after structural changes.
The document discusses various stages in architecting an application for the cloud as it grows in scale and complexity.
Stage 1 involves a simple architecture suitable for startups with low overhead. Stage 2 adds redundancy as the business grows. Stage 3 requires the addition of load balancers and more servers as publicity increases load. Stage 4 requires database replication and partitioning as single databases can no longer handle the load. Later stages involve rearchitecting the application and databases to support further scaling through techniques like data partitioning, database clustering, and optimizing code and resources.
in LATVIAN language: Viens no galvenajiem datubāzes administratora uzdevumiem ir veikt datubāzes backup un prast no tā atjaunot datubāzi. Mysql bezmaksas versija nepiedāvā datubāzes administratoram ļoti daudz izvēles. Sava prezentācija es pastāstīšu par šādiem rīkiem:
-- MySQLdump
-- Percona XtraBackup
-- Mysql enterprise backup (MEB)
--Un citiem rīkiem kas palīdz man veikt db backup
The document provides an overview of MySQL database including:
- A brief history of MySQL and descriptions of some early and modern storage engines.
- Explanations of the physical and logical architectures of MySQL, focusing on InnoDB storage engine components like the tablespace, redo logs, and buffer pool.
- An overview of installing, configuring, and optimizing MySQL for production use, including storage engine, server variable, and hardware recommendations.
- Descriptions of MySQL administration tools and methods for monitoring performance and activity.
- Explanations of MySQL replication including configuration, best practices, and use of global transaction identifiers.
- Discussions of backup strategies including logical dumps and binary backups.
This document provides an introduction to relational database management systems (RDBMS) through a series of slides. It covers topics such as installing MySQL, connecting to databases, using SQL commands to retrieve and manipulate data, and designing databases. The slides introduce fundamental RDBMS concepts like tables, rows, columns, keys, and relationships. It also demonstrates how to use the MySQL command line interface to issue queries and explore database structure. Examples are provided for common SQL statements like SELECT, CREATE, INSERT and more.
This document provides an introduction to relational database management systems (RDBMS) through a series of slides. It covers topics such as installing MySQL, connecting to databases using SQL, database design with MySQL Workbench, and using biological databases with RDBMS. Instructions are provided for installing MySQL on Linux, Windows, and Mac operating systems. The key components of RDBMS and SQL are defined, including creating databases and tables, retrieving and sorting data.
MySQL Server Backup, Restoration, And Disaster Recovery Planning PresentationColin Charles
This document discusses MySQL server backup, restoration, and disaster recovery planning. It covers when backups are needed, what to back up, best practices for performing backups, storage locations, and backup tools and methods. Key points include backing up databases, logs, and configurations regularly using tools like mysqldump, the binary log, and file copying with FLUSH TABLES WITH READ LOCK. Restoring requires both backups and binary logs to recover to a point-in-time.
This document describes how to configure MySQL database replication between a master and slave server. The key steps are:
1. Configure the master server by editing its configuration file to enable binary logging and set the server ID. Create a replication user and grant privileges.
2. Export the databases from the master using mysqldump.
3. Configure the slave server by editing its configuration file to point to the master server. Import the database dump. Start replication on the slave.
4. Verify replication is working by inserting data on the master and checking it is replicated to the slave.
Freezer is an OpenStack backup and restore service that allows users to automate backup processes. It includes components like an API, scheduler, and agent. The scheduler retrieves backup jobs from the API and executes them via the agent. Freezer supports different backup types including file system, Cinder volume, and MySQL backups. It can store backups in OpenStack Swift, locally, or remotely using SSH. Backups can be restored locally or by recreating volumes/instances in Cinder/Nova.
OpenStack Tokyo Meeup - Gluster Storage DayDan Radez
November 2012 Tokyo OpenStack meetup was dedicated to using Gluster storage. This presentation showed the fuse mount method to integrating gluster into OpenStack. There are new drivers that have been developed that make mounting gluster volumes to instances more efficient. This presentation doesn't show how to use them.
This document provides instructions for various MySQL backup and restore operations using mysqldump and other tools. It discusses:
- Backing up and restoring a single database or multiple databases using mysqldump.
- Backing up all databases, restoring databases, and restoring a single database.
- Backing up a specific table.
- Importing data into MySQL from text files using mysqlimport or LOAD DATA LOCAL INFILE statements.
- Additional topics covered include MySQL backups on Linux and Windows, different backup types, dumping databases to other servers, and loading SQL files and CSV files into MySQL.
The document discusses how to use the mysqldump utility in MySQL to back up and restore databases. It explains how to back up individual tables or entire databases using mysqldump and various options. It also discusses how to restore databases from backup files by running the SQL statements in the backup file. The document emphasizes that binary log files should also be used to update the restored database to the most recent state after the backup was performed. It provides examples of restoring data directly from binary log files or exporting the binary log data to a text file first before restoring.
MySQL is a widely used open-source relational database management system. The presentation covered how to install, configure, start, stop, and connect to MySQL. It also discussed how to load and view data, backup databases, set up user authentication, and where to go for additional training resources. Common MySQL commands and tools were demonstrated.
The document provides instructions on installing, configuring, and using the MySQL database on Linux. It explains how to download and install MySQL, set the root password, secure the installation, and start the MySQL daemon. Advanced SQL topics like unions, subqueries, and ranking functions are also briefly mentioned.
The document describes how to configure and run multiple MySQL database instances on a single server. It involves:
1. Creating separate data directories and configuration files for each instance, configured to use different ports;
2. Installing the database tables into each data directory;
3. Starting each MySQL instance using its customized configuration file.
This allows running two instances on ports 3307 and 3308, each with its own isolated data and configuration. The server processes are started and the ports are verified to confirm the instances are running independently.
This document provides an overview of the mydumper/myloader utilities for backing up and restoring MySQL databases. It describes how mydumper offers parallelism, manageability, consistency and flexibility compared to mysqldump. Example usage shows how to schedule mydumper backups and myloader restores using shell scripts. The utilities produce separate files for table data, schemas and binary logs. Mydumper backups complete much faster than mysqldump while using less system resources.
The document provides information about MySQL, including that it is an open source database software that is widely used. It describes how to install and configure MySQL on Linux, and provides examples of common SQL queries like creating tables, inserting/updating/deleting data, and exporting/importing databases. Key topics covered include the benefits of MySQL, installing it on Linux, basic configuration, and using SQL statements to define schemas and manipulate data.
This document provides an overview of the MySQL sys schema. It discusses how sys schema provides views and functions on top of the Performance Schema to implement common DBA and developer use cases. It covers installing sys schema, the various formatting and helper functions it includes, and the summary views it provides for analyzing user activity, I/O, schema objects and more.
The MySQL sys schema was integrated fully into MySQL Server from version 5.7.7. 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. The MySQL sys schema was integrated fully into MySQL Server from version 5.7.7. 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.
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.
MySQL 5.7: Performance Schema ImprovementsMark Leith
This document discusses improvements to the Performance Schema instrumentation in MySQL 5.7. It provides an overview of what Performance Schema is, how it has evolved from versions 5.5 to 5.6, and key improvements in 5.7, including better memory instrumentation, metadata locking instrumentation, and replication monitoring capabilities.
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.
This presentation discusses MySQL administration and monitoring. It covers basic command line tools for MySQL, internal schemas like mysql and information_schema, backup and recovery options like mysqldump and replication. GUI tools like MySQL Workbench are presented for administration. MySQL Enterprise Monitor is discussed for distributed monitoring of MySQL environments.
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.
This document summarizes MySQL's monitoring mechanisms and how they have evolved over time. It discusses tools like SHOW statements, INFORMATION_SCHEMA, slow/general query logs, and EXPLAIN that provided limited visibility in past versions. MySQL 5.5 introduced the Performance Schema framework for detailed instrumentation. Subsequent versions have expanded instrumentation to provide more developer-focused statistics on statements, stages, I/O, locks and more. New INFORMATION_SCHEMA tables in 5.6 provide additional InnoDB statistics on data dictionary, buffer pool, transactions and compression. The optimizer trace exposes query transformations. Enhanced EXPLAIN now supports more statement types and future improvements will provide a structured EXPLAIN output.
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 5.5 and 5.6 releases of MySQL introduce several new mechanisms that provide improved monitoring and performance tuning functionality. Examples are performance schemas, InnoDB metrics tables, optimizer trace, and extended explain functionality. This session outlines the vision for monitoring-related functionality in MySQL and presents an overview of the new mechanisms. It shows how these are integrated with MySQL management tools. Furthermore, it discusses how these mechanisms can be utilized by application developers, DBAs, and production engineers for tracking down performance issues and monitoring production systems.
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
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.
Paradigm Shifts in User Modeling: A Journey from Historical Foundations to Em...Erasmo Purificato
Slide of the tutorial entitled "Paradigm Shifts in User Modeling: A Journey from Historical Foundations to Emerging Trends" held at UMAP'24: 32nd ACM Conference on User Modeling, Adaptation and Personalization (July 1, 2024 | Cagliari, Italy)
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.
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.
Quality Patents: Patents That Stand the Test of TimeAurora Consulting
Is your patent a vanity piece of paper for your office wall? Or is it a reliable, defendable, assertable, property right? The difference is often quality.
Is your patent simply a transactional cost and a large pile of legal bills for your startup? Or is it a leverageable asset worthy of attracting precious investment dollars, worth its cost in multiples of valuation? The difference is often quality.
Is your patent application only good enough to get through the examination process? Or has it been crafted to stand the tests of time and varied audiences if you later need to assert that document against an infringer, find yourself litigating with it in an Article 3 Court at the hands of a judge and jury, God forbid, end up having to defend its validity at the PTAB, or even needing to use it to block pirated imports at the International Trade Commission? The difference is often quality.
Quality will be our focus for a good chunk of the remainder of this season. What goes into a quality patent, and where possible, how do you get it without breaking the bank?
** Episode Overview **
In this first episode of our quality series, Kristen Hansen and the panel discuss:
⦿ What do we mean when we say patent quality?
⦿ Why is patent quality important?
⦿ How to balance quality and budget
⦿ The importance of searching, continuations, and draftsperson domain expertise
⦿ Very practical tips, tricks, examples, and Kristen’s Musts for drafting quality applications
https://www.aurorapatents.com/patently-strategic-podcast.html
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.
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.
Sustainability requires ingenuity and stewardship. Did you know Pigging Solutions pigging systems help you achieve your sustainable manufacturing goals AND provide rapid return on investment.
How? Our systems recover over 99% of product in transfer piping. Recovering trapped product from transfer lines that would otherwise become flush-waste, means you can increase batch yields and eliminate flush waste. From raw materials to finished product, if you can pump it, we can pig it.
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.
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.
Hire a private investigator to get cell phone recordsHackersList
Learn what private investigators can legally do to obtain cell phone records and track phones, plus ethical considerations and alternatives for addressing privacy concerns.
1. MySQL for Oracle DBAs
Mark Leith
Support Engineer, MySQL AB
mleith@mysql.com
1
2. Introduction
9 years database experience, starting with
Oracle
5 years MySQL experience
Extensive experience with database tools
Working within the MySQL Support Group,
for the Americas region
2
3. Agenda
MySQL Architecture
Backup and Recovery
Managing space and tables
Managing Memory
Tracing Sessions / Auditing
Monitoring Tools and Tips
Connecting MySQL to Oracle
Questions
3
7. Filesystem Layout - Binaries
Windows default: C:Program FilesMySQLMySQL Server x.xbin
Tarball default: /usr/local/mysql/bin
RPM default: /usr/lib/mysql, /usr/bin & /usr/sbin (depending on RPM)
MySQL Server executables
mysqld, mysqld-debug, mysqld-max
MySQL Client Utilities
mysql, mysqldump, mysqlcheck, mysqlbinlog ..
Upgrade and Security scripts
mysql_upgrade, mysql_fix_privilege_tables,
mysql_secure_installation ..
7
8. Filesystem Layout - Datafiles
Windows default: C:Program FilesMySQLMySQL Server x.xdata
Tarball default: /usr/local/mysql/data
RPM default: /var/lib/mysql
Databases use “datadir”
Default “mysql” database (SYSTEM Tablespace)
One directory per database
Table frm, data and index files within respective database directories
InnoDB uses “innodb_data_home_dir”
Single tablespace file (ibdata1) located in “datadir” by default
Set multiple datafiles with “innodb_datafile_path”
frm files still created within each database directory under datadir
Respect datadir conventions when using “innodb_file_per_table”
8
9. Filesystem Layout - Log files
Windows Default: C:Program FilesMySQLMySQL Server x.xdata
Tarball default: /usr/local/mysql/data
RPM default: /var/log & /var/lib/mysql
Error Log (Alert Log)
Set path with “log_error”, defaults to [hostname].err in “datadir”
Binary Logs (Archive Redo Logs)
Set with “log_bin”
Record of all INSERT/UPDATE/DELETE activity
<=5.0 Statement Based
>=5.1 Statement Based and Row Based
InnoDB Transaction logs (Online Redo Logs)
ib_logfile[x] set with “innodb_log_files_in_group” (default 2)
Provides “Durability” to ACID compliance
Control “checkpoint” times by altering the size of
“innodb_log_file_size”
9
10. Permissions / Ownership
No concept of “ownership”
Permissions stored in the “mysql” database
“schema” == “database” rather than “users objects”
mysql.user
Username, host, password hash, global privileges, grant option
mysql.db
Database level privileges
mysql.tables_priv
Table level privileges
mysql.columns_priv
Column level privileges
mysql.procs_priv
Procedure / Function privileges
10
11. Backup and Recovery
Cold Backup
Filesystem Snapshot (cp / rsync)
Warm Backup
mysqldump
Hot Backup
mysqldump, mysqlhotcopy
Using Slaves
Recovery tips
11
12. Backup - Cold Backup
Full cold backup
All database directories and contents (including “mysql”)
my.cnf/ini file
If using InnoDB
All InnoDB datafiles
All InnoDB Transaction Redo Log files
Incremental Cold Backup
All of the above as a starting point
Binary logging enabled (”log-bin”)
Only requires the latest binary logs since the last snapshot
Beware of recovery times - applying the binary logs is a lot
slower than file copying
12
13. Backup - Warm Backup
mysqldump
Uses FLUSH TABLES WITH READ LOCK
Locks all other sessions
/* Backup db1, db2 and db3 to the same file, mixed MyISAM / InnoDB / other engines */
mysqldump -u root --opt --databases db1, db2, db3 > /backup/file.sql
/* Backup all databases to seperate files per database schema */
#!/bin/bash
DATABASES=`mysql -u root -N -e 'SHOW DATABASES'`
OPTIONS1='--extended-insert --no-autocommit --quick --set-charset'
OPTIONS2='--disable-keys --skip-add-locks --skip-comments'
DUMPOPTIONS=quot;$OPTIONS1 $OPTIONS2quot;
for D in $DATABASES ;
do
echo dumping $D ;
echo quot;use $D;quot; > $D-data.sql
echo quot;set autocommit=0;quot; >> $D-data.sql
mysqldump -u root $DUMPOPTIONS --databases $D
>> $D-data.sql ;
done
13
14. Backup - Hot Backup
mysqldump
InnoDB only
Uses transactions and multi-versioning
/* Backup InnoDB databases to seperate files per database schema */
#!/bin/bash
DATABASES=`mysql -u root -N -e 'SHOW DATABASES'`
OPTIONS1='--extended-insert --no-autocommit --quick --set-charset --disable-keys'
OPTIONS2=' --skip-add-locks --skip-comments --single-transaction --master-data'
DUMPOPTIONS=quot;$OPTIONS1 $OPTIONS2quot;
for D in $DATABASES ;
do
echo dumping $D ;
echo quot;use $D;quot; > $D-data.sql
echo quot;set autocommit=0;quot; >> $D-data.sql
mysqldump -u root $DUMPOPTIONS --databases $D
>> $D-data.sql ;
done
14
15. Backup - Hot Backup
mysqlhotcopy
MyISAM and ARCHIVE tables only
LOCK TABLES ..; FLUSH TABLES; [cp | scp]; UNLOCK TABLES;
Requires Perl DBI
/* Hot Backup MyISAM / ARCHIVE table files to seperate directories per database
schema */
#!/bin/bash
DATABASES=`mysql -u root -N -e 'SHOW DATABASES'`
DUMPOPTIONS='-u root --method=cp --noindices --flushlog'
for D in $DATABASES ;
do
echo copying $D ;
DIRNAME=$D-`date '+%d%m%y'`
mkdir ~/backup/$DIRNAME
mysqlhotcopy $DUMPOPTIONS $D ~/backup/$DIRNAME
done
15
16. Backup - Slave Hot Backup
Requires no downtime on the master system
Allows full cold backups easily, giving faster recovery times
Adds a server that can also be used to spread load
Master Slave
cp
scp
rsync
server-id 1 server-id 2
LVM
Application Users
mysqldump
mysqlhotcopy
(Dataguard)
Reporting Users
16
17. Recovery Tips
mysqlbinlog
Perform point in time recovery directly to MySQL
mysqlbinlog bin.000001 bin.000002 bin.000003 --stop-datetime=’2006-03-26 12:00:00’ |
mysql -u root -p
Output statements for single databases directly to MySQL
mysqlbinlog bin.000001 bin.000002 bin.000003 --stop-datetime=’2006-03-26 12:00:00’
--database sakila | mysql -u root -p
Output statements to plain text SQL files
mysqlbinlog bin.000001 bin.000002 bin.000003 --stop-datetime=’2006-03-26 12:00:00’
--database sakila > /tmp/sakila-binlog-dump.sql
17
18. Recovery Tips
Always use binary logging if possible
On recovery, make an initial copy of all current database files
Work on original set, leaving copy as secondary backup
Take regular full backups, to minimize recovery time
InnoDB Log file size controls full checkpoint activity
If using mysqlhotcopy, with --noindices, to rebuild indexes:
mysqlcheck --all-databases --auto-repair --use-frm
18
19. Managing Space and Tables
MyISAM Storage Engine
Gathering stats, managing space
InnoDB Storage Engine
Gathering stats, managing space
Partitioning
Partition Management
19
20. Managing MyISAM Space
All table files stored within specific database directory
Row data held within [tablename].MYD
Index data held within [tablename].MYI
Table structure held within [tablename].frm
medusa:/usr/local/mysql/data/sakila root# ls -l film_text*
-rw-rw---- 1 mysql wheel 119616 Apr 4 20:51 film_text.MYD
-rw-rw---- 1 mysql wheel 205824 Apr 4 20:51 film_text.MYI
-rw-rw---- 1 mysql wheel 8642 Apr 4 20:51 film_text.frm
Deleted row space retained for future INSERT/UPDATE
Supports FULLTEXT Indexes (Oracle Text)
Table level locking (SELECT = Shared, INSERT, UPDATE, DELETE = Write)
Data warehousing storage engine
20
21. Managing MyISAM Space
SHOW TABLE STATUS
mysql> SHOW TABLE STATUS LIKE 'film_text'G
*************************** 1. row ***************************
Name: film_text
Variable Length rows (VARCHAR etc.)
Engine: MyISAM
“Fixed” = CHAR etc. static sized
Version: 10
Row_format: Dynamic
Data size in bytes (~117KB)
Rows: 950
Avg_row_length: 119
Data_length: 119616 Max size for the internal row pointer
Max_data_length: 281474976710655
Index_length: 205824 Index size in bytes (~201KB)
Data_free: 6136
Auto_increment: NULL Space reserved but unused
Create_time: 2006-04-03 14:16:02 (~6KB)
Update_time: 2006-04-04 20:51:02
Check_time: 2006-04-04 20:51:17
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
21
22. Managing MyISAM Space
INFORMATION_SCHEMA TABLES (DBA_ / USER_ tables)
SELECT s.schema_name,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length)) /1024/1024,2),0.00),quot;Mbquot;)
total_size,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/
1024/1024,2),0.00),quot;Mbquot;) data_used,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),quot;Mbquot;) data_free,
IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free)) /((SUM
(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used,
COUNT(table_name) total_tables
FROM INFORMATION_SCHEMA.SCHEMATA s
LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema
WHERE s.schema_name = quot;sakilaquot;
GROUP BY s.schema_name
ORDER BY pct_used DESCG
*************************** 1. row ***************************
schema_name: sakila
total_size: 6.62Mb
data_used: 6.62Mb
data_free: 0.01Mb
pct_used: 99.91
total_tables: 22
1 row in set (0.08 sec)
22
23. Managing MyISAM Space
INFORMATION_SCHEMA TABLES (DBA_ / USER_ tables)
SELECT table_name,
ROUND(((t.data_length+t.index_length)-t.data_free)/(t.data_length+t.index_length)*100) pct_used
FROM information_schema.tables t
WHERE table_schema = 'sakila'
HAVING pct_used < 99
ORDER BY table_nameG
*************************** 1. row ***************************
table_name: film_text
pct_used: 98
1 row in set (0.05 sec)
To reclaim space to the filesystem run “OPTIMIZE TABLE”
Creates a copy of the table, drops the original, and renames the copy
Requires a table lock
If frequent activity on the table run “ANALYZE TABLE”
Requires a table lock
23
24. Managing MyISAM Space
myisamchk -dvv
Should not be used whilst table is being used
LOCK TABLE .. WRITE; or shutdown
medusa:/Users/markleith/mysql/mysql-5.1.7/data/sakila root# ../../bin/myisamchk -dvv film_text
MyISAM file: film_text
Record format: Packed
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2006-04-18 15:57:45
Status: changed
Data records: 1000 Deleted blocks: 0
Datafile parts: 1000 Deleted data: 0
Datafile pointer (bytes): 6 Keyfile pointer (bytes): 6
Datafile length: 119616 Keyfile length: 205824
Max datafile length: 281474976710654 Max keyfile length: 288230376151710719
Recordlength: 782
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 2 unique short 1 29696 1024
2 5 254 fulltext ? packed 0 49152 1024
1 4 float 0
Field Start Length Nullpos Nullbit Type
1 1 1
2 2 2 no zeros
3 4 767 varchar
4 771 10 1 1 blob
24
25. Managing InnoDB Space
Row level locking
Supports all transaction isolation levels
Tables, Indexes and rollback created in a single tablespace
Multiple datafiles, with last datafile allowed to autoextend
medusa:/usr/local/mysql/data root# ls -l ib*
-rw-rw---- 1 mysql wheel 5242880 Apr 8 21:25 ib_logfile0
} Redo Logs
-rw-rw---- 1 mysql wheel 5242880 Apr 8 21:25 ib_logfile1
-rw-rw---- 1 mysql wheel 18874368 Apr 8 21:25 ibdata1
} Data Files
-rw-rw---- 1 mysql wheel 10485760 Apr 8 21:25 ibdata2
Clustered Indexes (Index Organized Tables)
Pages and extents managed like a filesystem
Filesystem InnoDB Oracle
Disk Partition Tablespace Tablespace(s)
File Segment (Table/Index) Segment (Table/Index)
Inode Inode
Filesystem Block Extent (64 pages, 1Mb) Extent
Disk Block Page (UNIV_PAGE_SIZE 16Kb) DB_BLOCK_SIZE
25
26. Managing InnoDB Space
SHOW TABLE STATUS
mysql> SHOW TABLE STATUS LIKE 'rental'G
*************************** 1. row ***************************
Name: rental
New 5.0 InnoDB compact row format
Engine: InnoDB
Version: 10
Row_format: Compact
Data size in bytes (~1.5Mb)
Rows: 16305
Avg_row_length: 97
Data_length: 1589248 No Maximum (64Tb for tablespace)
Max_data_length: 0
Index_length: 1261568 Index size in bytes (~1.2Mb)
Data_free: 0
Auto_increment: 16050 Unused space not reported by table
Create_time: 2006-04-03 14:16:02
Update_time: NULL
Check_time: NULL Freespace reported by tablespace
Collation: utf8_general_ci (4Mb)
Checksum: NULL
Create_options:
Comment: InnoDB free: 4096 kB; (`customer_id`)
REFER `sakila/customer`(`customer_id`) ON
1 row in set (0.10 sec)
26
27. Managing InnoDB Space
InnoDB Table and Tablespace Monitors
Dumped to the error log, or console (stdout) if no log-error
Dump the InnoDB internal Data Dictionary information
Switched on with special InnoDB tables
InnoDB Table Monitor
create table innodb_table_monitor (a int) engine = innodb;
InnoDB Tablespace Monitor
create table innodb_tablespace_monitor (a int) engine = innodb;
27
28. Managing InnoDB Space
InnoDB Tablespace Monitor
================================================
060403 14:20:31 INNODB TABLESPACE MONITOR OUTPUT
================================================
FILE SPACE INFO: id 0
size 1152, free limit 832, free extents 3
not full frag extents 4: used pages 202, full frag extents 2
first seg id not used 0 114
SEGMENT id 0 86 space 0; page 209; res 28 used 28; full ext 0
fragm pages 28; free extents 0; not full extents 0: pages 0
SEGMENT id 0 87 space 0; page 209; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 88 space 0; page 209; res 16 used 16; full ext 0
fragm pages 16; free extents 0; not full extents 0: pages 0
SEGMENT id 0 89 space 0; page 209; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 90 space 0; page 209; res 16 used 16; full ext 0
fragm pages 16; free extents 0; not full extents 0: pages 0
SEGMENT id 0 91 space 0; page 209; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 92 space 0; page 209; res 13 used 13; full ext 0
fragm pages 13; free extents 0; not full extents 0: pages 0
......
28
29. Managing InnoDB Space
InnoDB Tablespace Monitor
......
SEGMENT id 0 84 space 0; page 2; res 96 used 53; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 21
SEGMENT id 0 84 space 0; page 2;
The Internal data dictionary id for the tablespace segment
“space 0” is the system tablespace (ibdata files)
“page 2” is where the inode of the segment is
res 96 used 53;
“res” is total number of reserved pages
“used” is the number of pages with data allocated
full ext 0
The number of extents that are completely used
fragm pages 32;
The first 32 pages are fully allocated
free extents 0;
The number of extents that have no pages at all used
not full extents 1: pages 21
The number of extents that have pages used but are not full
“pages” is how many pages are used within these extents
29
30. Managing InnoDB Space
InnoDB Table Monitor
TABLE: name sakila/rental, id 0 26, columns 11, indexes 5, appr.rows 16305
COLUMNS: rental_id: DATA_INT len 4 prec 0; rental_date: DATA_INT len 8 prec 0; inventory_id: DATA_INT len 3 prec
0; customer_id: DATA_INT len 2 prec 0; return_date: DATA_INT len 8 prec 0; staff_id: DATA_INT len 1 prec 0;
last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8
len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
INDEX: name PRIMARY, id 0 44, fields 1/9, type 3
root page 207, appr.key vals 16305, leaf pages 53, size pages 97
FIELDS: rental_id DB_TRX_ID DB_ROLL_PTR rental_date inventory_id customer_id return_date staff_id last_update
INDEX: name rental_date, id 0 45, fields 3/4, type 2
root page 208, appr.key vals 17655, leaf pages 28, size pages 29
FIELDS: rental_date inventory_id customer_id rental_id
INDEX: name idx_fk_inventory_id, id 0 46, fields 1/2, type 0
root page 210, appr.key vals 4467, leaf pages 16, size pages 17
FIELDS: inventory_id rental_id
INDEX: name idx_fk_customer_id, id 0 47, fields 1/2, type 0
root page 211, appr.key vals 589, leaf pages 16, size pages 17
FIELDS: customer_id rental_id
INDEX: name idx_fk_staff_id, id 0 48, fields 1/2, type 0
root page 212, appr.key vals 1, leaf pages 13, size pages 14
FIELDS: staff_id rental_id
FOREIGN KEY CONSTRAINT sakila/fk_rental_staff: sakila/rental ( staff_id )
REFERENCES sakila/staff ( staff_id )
FOREIGN KEY CONSTRAINT sakila/fk_rental_inventory: sakila/rental ( inventory_id )
REFERENCES sakila/inventory ( inventory_id )
FOREIGN KEY CONSTRAINT sakila/fk_rental_customer: sakila/rental ( customer_id )
REFERENCES sakila/customer ( customer_id )
FOREIGN KEY CONSTRAINT sakila/fk_payment_rental: sakila/payment ( rental_id )
REFERENCES sakila/rental ( rental_id )
30
31. Managing InnoDB Space
InnoDB Table Monitor
TABLE: name sakila/rental, id 0 26, columns 11, indexes 5, appr.rows 16305
COLUMNS: rental_id: DATA_INT len 4 prec 0; rental_date: DATA_INT len 8 prec 0; inventory_id:
DATA_INT len 3 prec 0; customer_id: DATA_INT len 2 prec 0; return_date: DATA_INT len 8 prec 0; staff_id:
DATA_INT len 1 prec 0; last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0;
DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
“len” is column length in bytes, “prec” is the columns precision (DECIMAL etc.)
“DB_ROW_ID”, “DB_TRX_ID” and “DB_ROLL_PTR” internal columns for transactions /
multi-versioning
INDEX: name PRIMARY, id 0 44, fields 1/9, type 3
root page 207, appr.key vals 16305, leaf pages 53, size pages 97
FIELDS: rental_id DB_TRX_ID DB_ROLL_PTR rental_date inventory_id customer_id return_date staff_id last_update
“fields 1/9” is fields specifically specified in the index (1) and total fields in the index (9)
“type” is index type: 3 = PRIMARY, 2 = UNIQUE, 0 = KEY, 1 = AUTO GENERATED PK
“leaf pages 53” is the number of pages with data allocated (”used” in tablespace monitor)
“size pages 97” is the total number of allocated pages (”res” in tablespace monitor)
Rows: 16305
Avg_row_length: 97 16,384 * 97 = 1,589,248 (1.5Mb)
Data_length: 1589248
Max_data_length: 0 16,384 * (29+17+17+14) = 1,261,568 (1.2mb)
Index_length: 1261568
16,384 * (97-53) = 720,896 (~700Kb)
Data_free: 0
31
32. Managing InnoDB Space
Allocates the first 32 pages individually
After 32 pages allocated, allocates 1Mb extents
To reclaim space to the tablespace run “OPTIMIZE TABLE”
Reorganization process similar to MyISAM
Requires a table lock
“ANALYZE TABLE” rarely needed, as InnoDB updates stats
Not Uniform extent allocation, fragmentation highly possible
To reorganize the tablespace:
mysqldump all tables
Stop MySQL
mv the InnoDB tablespace datafiles and log files to a backup location
Start MySQL
Load the mysqldump
32
33. Partitioning (5.1)
No extra costs
All storage engines support partitioning
“Local” indexes only
Indexes only created locally to each partition
“Global” indexes scheduled for 5.2
Indexes created across the entire partitioned table
Partition pruning already available at the optimizer level
Allow far greater table maintenance flexibility, with partition
level locks
33
34. Partitioning (5.1)
RANGE partitioning: (As in Oracle 8i RANGE partitions)
Assigns rows to partitions based on column values falling within a given range.
LIST partitioning: (As in Oracle 9i LIST partitions)
Similar to partitioning by range, except that the partition is selected based on
columns matching one of a set of discrete values.
HASH partitioning: (Not possible in Oracle)
A partition is selected based on the value returned by a user-defined expression
that operates on column values in rows to be inserted into the table. The function
may consist of any expression valid in MySQL that yields a non-negative integer
value.
KEY partitioning: (As in Oracle 8i HASH partitions)
Similar to partitioning by hash, except that only one or more columns to be
evaluated are supplied, and the MySQL server provides its own hashing function.
These columns can contain other than integer values, since the hashing function
supplied by MySQL guarantees an integer result regardless of the column data
type.
Subpartitioning: (As in Oracle 9i composite partitions)
Subpartition tables that are partitioned by RANGE or LIST. Subpartitions may use
either HASH or KEY partitioning.
34
35. Partitioning (5.1)
Moving to Partitioned tables
PARTITION key must be a part of the PRIMARY KEY, if any, if not, any column
PARTITION key must be on an INT column, or expression returning an INT
Do not support FOREIGN KEY constraints
CREATE TABLE `RENTAL` (
`rental_id` int(11) NOT NULL auto_increment,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime default NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`),
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
KEY `idx_fk_inventory_id` (`inventory_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `idx_fk_staff_id` (`staff_id`),
CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON
UPDATE CASCADE,
CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON
UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
35
36. Partitioning (5.1)
Moving to Partitioned tables
DROP TABLE rental_parts;
CREATE TABLE `rental_parts` (
`rental_id` int(11) NOT NULL auto_increment,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime default NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`, `rental_date`),
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
KEY `idx_inventory_id` (`inventory_id`),
KEY `idx_customer_id` (`customer_id`),
KEY `idx_staff_id` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(`rental_date`))(
PARTITION rental_2002 VALUES LESS THAN (2003),
PARTITION rental_2003 VALUES LESS THAN (2004),
PARTITION rental_2004 VALUES LESS THAN (2005),
PARTITION rental_2005 VALUES LESS THAN (2006),
PARTITION rental_2006 VALUES LESS THAN MAXVALUE
);
INSERT INTO rental_parts (SELECT * FROM rental);
DROP TABLE rental;
RENAME TABLE rental_parts TO rental;
36
38. Partitioning (5.1)
Partition Management - ALTER TABLE
REBUILD PARTITION (No Oracle equivalent)
Used to rebuild/reorganize a partition
ALTER TABLE rental REBUILD PARTITION rental_2005;
OPTIMIZE PARTITION (No Oracle equivalent)
Used like OPTIMIZE TABLE for each storage engine, at the partition level
ALTER TABLE rental OPTIMIZE PARTITION rental_2004;
ANALYZE PARTITION (analyze table emp partition(p1) compute statistics;)
Reads and stores the key distributions for partitions
ALTER TABLE rental ANALYZE PARTITION rental_2005;
CHECK PARTITION (No Oracle equivalent)
Checks specific partitions for errors (like CHECK TABLE)
ALTER TABLE rental CHECK PARTITION rental_2003;
REPAIR PARTITION (No Oracle equivalent)
Used for MyISAM partitions, if they become corrupt (like REPAIR TABLE)
ALTER TABLE rental REPAIR PARTITION rental_2003;
38
39. Partitioning (5.1)
Partition Management - ALTER TABLE
ADD PARTITION (Same as Oracle)
Add a partition to the table (to the end of the
ALTER TABLE rental ADD PARTITION
(PARTITION rental_2007 VALUES LESS THAN (2008);
DROP PARTITION (Same as Oracle)
Remove a partition from the table
ALTER TABLE rental DROP PARTITION rental_2002;
REORGANIZE PARTITION (ALTER TABLE .. SPLIT / MERGE PARTITION)
Split a partition in to multiple partitions, or merge several partitions in to one
ALTER TABLE rental REORGANIZE PARTITION rental_2002 INTO (
PARTITION rental_pre_2002 VALUES LESS THAN (2002),
PARTITION rental_2002 VALUES LESS THAN (2003));
ALTER TABLE rental REORGANIZE PARTITION rental_pre_2002, rental_2002
INTO (PARTITION rental_pre_2003 VALUES LESS THAN (2003));
39
40. Managing Memory
SHOW [GLOBAL | SESSION] STATUS
MyISAM Key Cache
InnoDB Buffer Pool
Query Cache
Thread Buffers
40
41. Managing Memory
SHOW [GLOBAL | SESSION] STATUS [LIKE ‘string’]
Provides session and global level server statistics
Gives information about various aspects of the server such as locks,
memory usage, disk usage, network activity etc.
Akin to various of the V$ views in parts
Reset statistics with “FLUSH STATUS”
mysql> SHOW STATUS;
+-------------------------------------+-----------------+
| Variable_name | Value |
+-------------------------------------+-----------------+
| Aborted_clients |0 |
| Aborted_connects |0 |
| Bytes_received | 155372598 |
| Bytes_sent | 1176560426 |
| Connections | 30023 |
...
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 8340 |
| Created_tmp_files | 60 |
41
42. Managing Memory
MyISAM Key Cache
Caches only MyISAM index data
Key status indicators:
Key_reads - Index blocks read from disk
Key_read_requests - Index blocks read from cache
Key_writes - Index blocks written on disk
Key_write_requests - Index blocks written within cache
Monitor overall usage with a hit ratio indicator
SHOW GLOBAL STATUS LIKE ‘key_read%’;
100-((key_reads/key_read_requests)*100)
Set with “key_buffer_size” in my.cnf, or interactively:
SET GLOBAL key_buffer_size = 50*1024*1024;
Current maximum 4Gb
42
43. Managing Memory
InnoDB Buffer Pool
Caches InnoDB table and index data
Key status indicators (5.0 or greater):
Innodb_buffer_pool_reads - Pages read from disk
Innodb_buffer_pool_read_requests - Pages read from the cache
Innodb_buffer_pool_wait_free - Number of waits to create or flush page
Innodb_buffer_pool_read_ahead_seq - Incremented for full table scans
Monitor overall usage with a hit ratio indicator
SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_read%’;
100-((Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100)
Set with “innodb_buffer_pool_size” in my.cnf
Not a dynamic variable (requires a restart)
Typically 50-80% of dedicated server’s available RAM
43
44. Managing Memory
Query Cache
Caches query result sets as pre-formed network packets
Key status indicators:
Qcache_hits - Queries satisfied from the cache
QCache_inserts - Query result sets added to the cache
Qcache_lowmem_prunes - Query results flushed due to lack of memory
Qcache_free_memory - Bytes free
Monitor overall usage with a hit ratio indicator
100-((Qcache_inserts/Qcache_hits)*100)
Set with “query_cache_size” and “query_cache_type”
SET GLOBAL query_cache_size = 32*1024*1024;
Control block allocation size with “query_alloc_block_size”
Control size of result sets cached with “query_cache_limit”
44
45. Managing Memory
Per Thread Buffers
Possible to set dynamically at the session level with “SET SESSION variable = ..”
sort_buffer_size
Allocated per thread for each sort operation (ORDER BY / GROUP BY)
Values larger than 2-4Mb rarely benefit performance
Monitor for high “Sort_merge_passes” in SHOW GLOBAL STATUS
read_rnd_buffer_size
Used when reading sorted rows after an ORDER BY on an indexed column
Larger result sets get better performance from larger buffer size
Leave GLOBAL default and use SET SESSION when needed for large queries
read_buffer_size
Allocated per thread for each full MyISAM table scan
Values larger than 2-4Mb rarely benefit performance
Monitor for high “Handler_read_rnd_next” / “Select_scan”
join_buffer_size
Allocated per thread for each JOIN causing a full table scan
Possible many allocated per thread with complex/multiple joins
Values larger than 2-4Mb rarely benefit performance
45
46. Managing Memory
Per Thread Buffers
tmp_table_size
Allocated per thread for each complex GROUP BY or subquries etc.
Monitor with “Created_tmp_tables” and “Created_tmp_disk_tables”
32-64Mb depending on memory available
binlog_cache_size
Also allocated per thread
Caches Binary Log entries for multiple statement transactions
Monitor with “Binlog_cache_use” and “Binlog_disk_cache_use” satus variables
Limit with “max_binlog_cache_size” system variable
thread_cache_size
GLOBAL level variable
Caches threads/sessions with an already allocated stack for faster connections
When sessions disconnect they have their buffers flushed and are re-added
Monitor with ratio of “Threads_created” to “Connections”
46
47. Tracing Sessions / Auditing
SHOW FULL PROCESSLIST (V$SESSION)
PROCESS privilege required to view the full session list
Use FULL keyword to include currently executing SQL
mysql> SHOW FULL PROCESSLIST;
+----+------+-----------------------+---------+------------------+--------------+-------------+----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------------+---------+------------------+---------------+-------------+---------------------------------------+
| 1 | root | localhost:32820 | merlin | Sleep | 1| | NULL |
| 3 | root | localhost:32822 | merlin | Sleep | 507 | | NULL |
| 4 | root | localhost:32823 | merlin | Sleep | 507 | | NULL |
| 5 | root | localhost:32824 | merlin | Sleep | 506 | | NULL |
| 6 | root | localhost:32826 | merlin | Sleep | 21207 | | NULL |
| 7 | root | localhost:32827 | merlin | Query | 0 | Sending data | SELECT data_id,value |
FROM `dc_INTEGER` WHERE `begin_time` =(SELECT MAX(`begin_time`) FROM `dc_INTEGER` |
WHERE `collect_id`=90) AND `collect_id`=90 FOR UPDATE |
| 16 | root | localhost:33447 | NULL | Sleep | 3| | NULL |
| 19 | root | localhost:48885 | merlin | Sleep | 7| | NULL |
| 27 | root | localhost | NULL | Query | 0 | NULL | SHOW FULL PROCESSLIST |
+----+------+-----------------------+---------+------------------+--------------+-------------+----------------------------------------+
9 rows in set (0.00 sec)
47
48. Tracing Sessions / Auditing
PROCESSLIST states
Sleeping - The thread is idle
Opening Tables - Trying to open a new table
Copying to tmp table - Creating in memory temporary table
Copying to tmp table on disk - Creating disk based temporary table
Sorting for group - Performing a GROUP BY operation
Sorting for order - Performing an ORDER BY operation
Sending Data - Processing and returning SELECT results
Updating - Currently updating row values
Writing to net - Server is writing packets to the client
Locked - The session is currently blocked by another sessions lock(s)
Killed - Kill flag set, waiting for next opportunity to release thread
39
49. Tracing Sessions / Auditing
General Query Log
Records all connections and statements, successful or failed
Set with the “log” server variable
log=/var/log/mysql-query.log
Does incur a slight performance overhead
[markleith@medusa:~] $ sudo tail -n 15 mysql/mysql-5.1.7/data/general.log
Password:
./mysql-5.1.7/bin/mysqld-debug, Version: 5.1.7-beta-debug-log. started with:
Tcp port: 5017 Unix socket: /Users/markleith/mysql/mysql-5.1.7.sock
Time Id Command Argument
060419 11:58:49 1 Connect root@localhost on
060419 11:58:57 1 Query SELECT DATABASE()
1 Init DB sakila
060419 11:59:00 1 Query show tables
060419 11:59:19 1 Query desc staff
060419 12:00:04 1 Query select staff_id, first_name, last_name, email, username, password
from staff
49
50. Tracing Sessions / Auditing
New in 5.1 (True V$SESSION, V$SQLTEXT, sys.aud$)
INFORMATION_SCHEMA.PROCESSLIST
mysql.general_log - Set with “--log-output=TABLE,FILE” (or either)
mysql.slow_log - Set with “--log-output=TABLE,FILE” (or either)
mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
+----+---------+--------------+---------+----------------+---------+--------------+------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+---------+--------------+---------+----------------+---------+--------------+------------------------------------------------------------------------------------+
| 2 | root | localhost | | Query |0 | preparing | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST |
| 1 | root | localhost | sakila | Sleep | 2434 | | |
+----+---------+--------------+---------+----------------+---------+--------------+------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT p.id, g.event_time, p.user, p.host, g.command_type, replace(g.argument,'n', '') statement
-> FROM information_schema.processlist p
-> JOIN mysql.general_log g ON p.id = g.thread_id
-> WHERE p.id = 1 AND g.event_time > NOW() - INTERVAL 3 HOUR ORDER BY event_time;
+----+----------------------------+------+--------------+----------------+------------------------------------------------------------------------------------------------------------+
| id | event_time | user | host | command_type | statement |
+----+----------------------------+------+--------------+----------------+------------------------------------------------------------------------------------------------------------+
| 1 | 2006-04-19 11:58:49 | root | localhost | Connect | root@localhost on |
| 1 | 2006-04-19 11:58:57 | root | localhost | Query | SELECT DATABASE() |
| 1 | 2006-04-19 11:58:57 | root | localhost | Init DB | sakila |
| 1 | 2006-04-19 11:59:00 | root | localhost | Query | show tables |
| 1 | 2006-04-19 11:59:19 | root | localhost | Query | desc staff |
| 1 | 2006-04-19 12:00:04 | root | localhost | Query | select staff_id, first_name, last_name, email, username, passwordfrom staff |
+----+----------------------------+------+--------------+----------------+------------------------------------------------------------------------------------------------------------+
51
51. Tracing Sessions / Auditing
Triggers
DML triggers availble
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
System triggers such as “LOGON” not yet available
Only one trigger of each type allowed (i.e. BEFORE UPDATE)
Can perform multiple actions between BEGIN ... END blocks
CREATE TABLE audit_payment (
audit_id INT AUTO_INCREMENT PRIMARY KEY,
payment_id INT,
new_staff_id TINYINT,
old_staff_id TINYINT,
new_amount DECIMAL(5,2),
old_amount DECIMAL(5,2),
updated_by VARCHAR(64),
update_time DATETIME
);
CREATE TRIGGER tr_audit_payment AFTER UPDATE ON payment
FOR EACH ROW
INSERT INTO audit_payment VALUES
(NULL,OLD.payment_id,NEW.staff_id,OLD.staff_id,NEW.amount,OLD.amount,CURRENT_USER(),NOW());
52
56. Connecting Oracle to MySQL
Heterogeneous Services
Connects Oracle to MySQL via MyODBC
Can create database links from Oracle
Oracle -> MySQL only
Install and configure MyODBC
Create and edit $ORACLE_HOME/hs/admin and add:
/* Replace quot;MySQLquot; below with your DSN specified within your MyODBC setup */
HS_FDS_CONNECT_INFO = MySQL
HS_FDS_TRACE_LEVEL = OFF
Edit $ORACLE_HOME/network/admin/listener.ora and add:
(SID_DESC =
(PROGRAM = hsodbc)
(ORACLE_HOME = oracle/product/92) /* Your $ORACLE_HOME */
(SID_NAME = MySQL) /* Your DSN */
)
57
57. Connecting Oracle to MySQL
Heterogeneous Services
Add the following to the tnsnames.ora file:
MYSQL =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=MYSQL))
(HS=OK)
)
Reload your Oracle listener (lsnrctl reload) and then log
onto to the Oracle database. To set up the database link:
CREATE DATABASE LINK mysql
CONNECT TO quot;my_userquot; IDENTIFIED BY quot;my_passwordquot;
USING 'mysql';
58
58. Connecting Oracle to MySQL
Heterogeneous Services
SQL> COUNT(*) FROM film@mysql;
COUNT(*)
----------
1000
SQL> DESC film@mysql5;
Name Null? Type
----------------------------------------- -------- ----------------------------
film_id NUMBER(10)
category_id NOT NULL NUMBER(10)
title NOT NULL VARCHAR2(27)
description LONG
rental_duration NOT NULL NUMBER(3)
length NUMBER(10)
rating CHAR(5)
SQL>INSERT INTO film@mysql5 VALUES (1000000,1,'test','test',1,1,'PG');
1 row created.
---change prompts---
mysql> USE sakila
mysql> SELECT max(film_id) FROM film;
+------------------+
| max(film_id) |
+------------------+
| 1000000 |
+------------------+
59