Friday Jan 18, 2013

Achieve the Highest Levels of MySQL Scalability, Security & Uptime

Oracle's MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to help you reduce the cost, risk & time to deploy and manage your MySQL applications.

Access our Resource Kit to discover:

  • How to boost scalability by 20x with MySQL Enterprise Scalability
  • How to automatically detect and recover from failures with MySQL Enterprise High Availability
  • How to eliminate security vulnerabilities, improve replication and optimize performance with the MySQL Enterprise Monitor
  • How MySQL Enterprise Backup reduces the risk of data loss with online "Hot" backups of your databases
  • How to make the most of your MySQL deployments with Oracle Premier Support for MySQL

Learn how to achieve the highest levels of MySQL scalability, security and uptime, either on-premise or in the cloud.

Access demos, white papers and case studies in our Resource Kit now!

ISVs & OEMs will find out how they can increase differentiation and customer satisfaction with MySQL Enterprise Edition.

Wednesday May 23, 2012

MySQL 5.6 Replication: FAQ

On Wednesday May 16th, we ran a webinar to provide an overview of all of the new replication features and enhancements that are previewed in the MySQL 5.6 Development Release – including Global Transaction IDs, auto-failover and self-healing, multi-threaded, crash-safe slaves and more.

Collectively, these new capabilities enable MySQL users to scale for next generation web and cloud applications.

Attendees posted a number of great questions to the MySQL developers, serving to provide additional insights into how these new features are implemented. So I thought it would be useful to post those below, for the benefit of those unable to attend the live webinar (note, you can listen to the On-Demand replay which is available now).

Before getting to the Q&A, there are a couple of other resources that maybe useful to those wanting to learn more about Replication in MySQL 5.6

On-Demand webinar

Slides used during the webinar

For more detail on any of the features discussed below, be sure to check out the Developer Zone article: Replication developments in MySQL 5.6

So here is the Q&A from the event 

Multi-Threaded Slaves

The results from recent benchmarking of the Multi-Threaded Slave enhancement were discussed, prompting the following questions

Q. Going from 0 - 10 threads, did you notice any increase in IOwait on CPU?

A. In this case, yes. The actual amount depends on a number of factors: your hardware, query/transaction distribution across databases, server general and InnoDB specific configuration parameters.

Q. Will the multiple threads work on different transactions on the same database, or each thread works on a separate database?

A. Each worker thread works on separate databases (schemas).

Q. If I set the slave-parallel-workers to less than the number of databases, can I configure which databases use which worker threads?

A. There is no such configuration option to assign a certain Worker thread to a database. Configuring slave-parallel-workers to less than the number of databases is a good setup. A Worker can handle multiple databases.

Q. If I create 4 threads and I have 100 databases, can I configure which databases use which threads?

A. There won't be 1 worker to a mapping of exactly 25 databases, but it will be very close to that type of distribution.

Q. Thank You. I ask as we have about 8 databases that have a lot of transactions and about 30 that are used less frequently. It would be nice to have the ability to create 9 workers, 1 for each if the heavy databases and 1 for all the others

A. The current design enables relatively equal distribution of transactions across your databases, but it could be that 9 workers will fit anyway.

Q. Does multi-thread slave still work if there is foreign key across database?

A. MTS preserves slave consistency *within* a database, but not necessarily *between* databases. With MTS enabled and replication ongoing, updates to one database can be executed before updates to another causing them to be temporarily out of sync with each other.

Q. How is auto-increment managed with the multi-thread slave?

A. MTS makes sure Worker threads do not execute concurrently on the same table. Auto-increment is guaranteed to be handled in the same way as the single threaded "standard" slave handles auto-increment

Q. Can you use semi-synchronous replication on one of the slaves when using MTS?

A. Semi-sync is friendly to MTS. MTS is about parallel execution - so they cooperate well.

Optimized Row Based Replication

Q. If you only store the PK column in the Before Image for updates, does this mean you don't care about the slave's data potentially being out-of-sync? Will we be able to control how much data is stored in the binary logs?

A. The rule is that we ship a *PK equivalent* so that the slave is always able to find a row. This means:
  1. if master table has PK, then we ship the PK only
  2. if master table does not have PK, then we ship the entire row

Global Transaction IDs and HA Utilities

Q. Would the failover utility need to sit on a 3rd party host to allow arbitration?

A. The utility would typically run on a client, not on the hosts it is monitoring

Q. Can you explain the upgrade process to move to MySQL 5.6? I am assuming that the slave(s) are upgraded first and that replication would be backwards compatible. And after the slave(s) are upgraded, the master would be next. But then how do you turn on the GTID?

A. Right: the slave(s) are upgraded first. After upgrading Slaves they would be started with --gtid-mode=on (technically a couple of other options are needed as well). And then the same process would be followed for the upgraded Master.

Q. For failover functionality, if I had a setup like this: Server1 replicates to Server2, Server2 replicates to Server3, Server4, and Server5. If Server2 were to fail, can I have it configured so that Server1 can become the new master for Server3/4/5?

A. Yes - you can either configure the failover to the most recent slave based on GTID, or list specific candidates. What will happen is that Slave 1 will temporarily become a slave of 3, 4 and 5 to ensure it replicates any more recent transactions those slaves may have, and then it will become the master

Replication Event Checksums

Q. What is the overhead of replication checksums?

A. It is minimal - we plan to publish benchmarks over the summer to better characterize any overhead

Q. Are you exposing the checksum to the plugin api so we can add our own checksum types?

A. We prepared some of interfaces, i.e. checksum methods are identified by a specific code byte (1-127) values. But it's not really a plugin at this point.

Q. Do checksums verify both replicated data and the data on slave?

A. Yes - checksums are implemented across the entire path - so you can check for issues in replication itself, or in the hardware or network

Q. I think, it is better to turn on checksums at the relaylog to avoid overhead on the master, but if we do that and checksum fails (i.e. not matching the master's data) then what happens – will the slave throw an error

A. I agree, it's better to relax the Master, which verifies the checksum only optionally when the Dump Thread reads from the binlog prior to the replication event being sent out to the Slave. The slave mandatorily checks the checksummed events when they are sent across the network, and optionally when they are read from Relay-log. In either case, an error is thrown.

Q. Are checksums optional? In some cases we don't care for huge data loads

A. Yes, checksums are optional.

Time Delayed Replication

Q. Is Time delayed replication applied at the database level only and not for the entire slave?

A. Applied for the slave as execution is global.

Informational Log Events

Q. When we configure information log event, does it show meaningful query log for any binlog format? (Row based especially)

A. When using row based replication, you get the original query in human readable format... obviously you don’t want to see all the rows modified in a table of size, which can be huge

Q. Will the binlog include user id?

A. User id is replicated in some cases for Query-log-event - namely user id of the invoker when a stored routine is called.

Remote Binlog Backup

Q. What level of access does the remote binlog backup need?

A. Replication slave

Summary

As you can see, our Engineering team was kept busy with questions over the course of the webinar. Be sure to check out the MySQL 5.6 Replication webinar replay and if you have further questions, please don’t hesitate to use the comments below!

Thursday Sep 15, 2011

New Commercial Extensions for MySQL Enterprise Edition

MySQL 5.5 GA and MySQL 5.6 Development Milestone Releases have delivered many new compelling features to the MySQL users and community for testing, feedback and use.

In addition, commercial customers have access to a number of commercial extensions already included in MySQL Enterprise Edition:

•    MySQL Enterprise Monitor
•    MySQL Enterprise Backup

Continuing the business model of MySQL, we are adding three new commercial extensions to MySQL Enterprise Edition:

  • MySQL Enterprise Scalability
    • Thread Pool
  • MySQL Enterprise High Availability
    • Oracle VM Template for MySQL Enterprise Edition
    • Windows Clustering for MySQL Enterprise Edition
  • MySQL Enterprise Security
    • External Authentication for PAM
    • External Authentication for Windows


MySQL Enterprise Scalability: Thread Pool

To meet the sustained performance and scalability of ever increasing user, query and data loads MySQL Enterprise Edition provides thread pooling.  Thread Pool provides a highly scalable thread-handling model designed to reduce overhead in managing client connections and statement execution threads. The result is improved scalability and sustained performance for high-traffic online applications that service ever-growing numbers of client connections.  Thread Pool is a user configurable option that provides an efficient, alternate thread-handling model designed to fully exploit the processing power of today’s multi-core systems.  MySQL internal SysBench OLTP benchmarks show that the Thread Pool provides a significant improvement in sustained performance and scalability for applications that service a high number of concurrent connections, specifically on 16-core and higher systems.

Learn more and review the MySQL internal SysBench OLTP benchmarks.

MySQL Enterprise High Availability

MySQL Enterprise Edition offers a range of solutions for database high availability, to automatically detect and recover from failures, as well as minimize downtime resulting from scheduled maintenance activities.

Oracle VM Template for MySQL Enterprise Edition - Ensures rapid deployment and helps eliminate configuration efforts and risks by providing a pre-installed and pre-configured virtualized software image, taking advantage of Oracle VM’s features to deliver high availability.

Windows Server Failover Clustering for MySQL Enterprise Edition - With the certification and support of MySQL with Windows Server Failover Clustering (WSFC), organizations can safely deploy business-critical applications demanding high levels of availability, powered by MySQL Enterprise Edition and using native Windows clustering services.

Learn more about these new MySQL Enterprise Edition features.

MySQL Enterprise Security

MySQL 5.5 introduced a pluggable authentication API that allows users to be authenticated using external libraries, directories, etc.  Developers can use this API to build their own custom modules that integrate MySQL into an existing security infrastructure.  MySQL Enterprise Edition leverages this same API to provide ready to use external authentication modules that authenticate users via Pluggable Authentication Modules (“PAM”) or native Windows OS services.  Each is described below:

External Authentication for PAM - Enables you to configure MySQL to use PAM to authenticate users on LDAP, Unix/Linux, Kerberos, and other systems.

External Authentication for Windows – Enables you to configure MySQL to use native Windows services to authenticate client connections.  Users who have logged in to Windows can connect from MySQL client programs to the server based on the token information in their environment without specifying an additional password.

Learn more about these new MySQL Enterprise Edition features and the technical details of the MySQL 5.5 pluggable authentication API.


Download

Existing commercial customers who are entitled to a MySQL Enterprise Edition subscription can log into MyOracleSupport: and download these immediately.
For others who want to try these new capabilities, we will make them available shortly, via the 30 day
free trial of MySQL Enterprise Edition
.

As always, thanks for your continued support of MySQL!

About

Get the latest updates on products, technology, news, events, webcasts, customers and more.

Twitter


Facebook

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
5
6
9
10
11
12
13
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today