PlanetMysql.ru — информация о СУБД MySQL

If you’ve been tuning your MySQL database and have wondered what effect the innodb_flush_method settings have on write performance, then this information might help. I’ve recently been doing a lot of baseline load tests to show performance differences between localdisk and the new SAN we’re deploying. Since we run InnoDB for everything in production, and writes are very heavy, I decided to run comparison tests between two identical servers to find the best setting for innodb_flush_method. We have the following specs for the hardware:

• Dell R610
• 24 core Intel Xeon X5670 @ 2.93ghz
• 72GB ECC RAM
• Brocade 825 HBA
• Local disk: RAID-10 15K SAS Ext3 (ugh)
• SAN: Oracle 7420 with four Intel Xeon X7550 @ 2.00GHz, 512GB RAM, 2TB read-cache(SLC-SSD), 36GB write cache (MLC-SSD), 3 disk shelves populated with 60x2TB 7200RM SATA drives setup in mirrored format with striped logs, dual 8Gb FC links to redundant fabric, connected to Brocade DCX 8510-4.
• The my.cnf file being used for the tests: click-click

I’m using the following sysbench command to run the tests. On each server the same commands are used. I ran a 1B row prepare prior to the 1B row test.

sysbench –db-driver=mysql –num-threads=64 –max-requests=1000000000 –max-time=3600 –test=oltp –verbosity=3 –validate=off –oltp-test-mode=complex –oltp-read-only=off –oltp-table-name=sbtest –oltp-table-size=1000000000 –oltp-dist-type=special –mysql-host=localhost –mysql-port=3306  –mysql-table-engine=innodb run

On the server that is utilizing SAN paths there are two LUNS presented for MySQL use. /db/data01 for InnoDB data files, /db/logs01 for InnoDB logs. These filesystems are both formatted as XFS. The server running local-disk tests is running Ext3. I might run some more tests later with the local-disk setup as XFS if time allows.

Here are the results. Clearly a well designed SAN infrastructure is superior to even RAID-10 15K SAS drives. And of course you can see the different performance values from using O_DIRECT for the innodb_flush_method for the different data storage mediums.

1B Row Complex Transactional Test, 64 threads

• SAN O_DIRECT: read/write requests: 31560140 (8766.61 per sec.)
• SAN O_DSYNC: read/write requests: 5179457 (1438.52 per sec.)
• SAN fdatasync: read/write requests: 9445774 (2623.66 per sec.)
• Local-disk O_DIRECT: read/write requests: 3258595 (905.06 per sec.)
• Local-disk O_DSYNC: read/write requests: 3494632 (970.65 per sec.)
• Local-disk fdatasync: read/write requests: 4223757 (1173.04 per sec.)

PlanetMySQL Voting: Vote UP / Vote DOWN

If you’ve been tuning your MySQL database and have wondered what effect the innodb_flush_method settings have on write performance, then this information might help. I’ve recently been doing a lot of baseline load tests to show performance differences between localdisk and the new SAN we’re deploying. Since we run InnoDB for everything in production, and writes are very heavy, I decided to run comparison tests between two identical servers to find the best setting for innodb_flush_method. We have the following specs for the hardware:

• Dell R610
• 24 core Intel Xeon X5670 @ 2.93ghz
• 72GB ECC RAM
• Brocade 825 HBA
• Local disk: RAID-10 15K SAS Ext3 (ugh)
• SAN: Oracle 7420 with four Intel Xeon X7550 @ 2.00GHz, 512GB RAM, 2TB read-cache(SLC-SSD), 36GB write cache (MLC-SSD), 3 disk shelves populated with 60x2TB 7200RM SATA drives setup in mirrored format with striped logs, dual 8Gb FC links to redundant fabric, connected to Brocade DCX 8510-4.
• The my.cnf file being used for the tests: click-click

I’m using the following sysbench command to run the tests. On each server the same commands are used. I ran a 1B row prepare prior to the 1B row test.

sysbench –db-driver=mysql –num-threads=64 –max-requests=1000000000 –max-time=3600 –test=oltp –verbosity=3 –validate=off –oltp-test-mode=complex –oltp-read-only=off –oltp-table-name=sbtest –oltp-table-size=1000000000 –oltp-dist-type=special –mysql-host=localhost –mysql-port=3306  –mysql-table-engine=innodb run

On the server that is utilizing SAN paths there are two LUNS presented for MySQL use. /db/data01 for InnoDB data files, /db/logs01 for InnoDB logs. These filesystems are both formatted as XFS. The server running local-disk tests is running Ext3. I might run some more tests later with the local-disk setup as XFS if time allows.

Here are the results. Clearly a well designed SAN infrastructure is superior to even RAID-10 15K SAS drives. And of course you can see the different performance values from using O_DIRECT for the innodb_flush_method for the different data storage mediums.

1B Row Complex Transactional Test, 64 threads

• SAN O_DIRECT: read/write requests: 31560140 (8766.61 per sec.)
• SAN O_DSYNC: read/write requests: 5179457 (1438.52 per sec.)
• SAN fdatasync: read/write requests: 9445774 (2623.66 per sec.)
• Local-disk O_DIRECT: read/write requests: 3258595 (905.06 per sec.)
• Local-disk O_DSYNC: read/write requests: 3494632 (970.65 per sec.)
• Local-disk fdatasync: read/write requests: 4223757 (1173.04 per sec.)

PlanetMySQL Voting: Vote UP / Vote DOWN

By now you have probably heard about the MySQL thread pool plugin and API, but you may not have fully processed the details. Here’s the quick summary:  With the new thread pool plugin, there is now an alternative way to handle connection threads in MySQL Enterprise Edition.  With the plugin, MySQL connection threads are shared like an extraordinarily well managed timeshare in Hawaii.  When one connection is “idle”, asking nothing of and expecting nothing from the database, another connection can use that same thread for its database requests.  Threads are released by each connection as soon as the request is completed and  go back into the pool for re-use – just like the theoretical timeshare is up for grabs on the weeks you are not there.

In the older, and still default connection thread model, threads are dedicated to a single client  for the life of the connection and there are as many threads as there are clients currently connected to the database.  This has some disadvantages when the server workload must scale to handle large numbers of connections, and the overhead can be signficant. This occurs for several reasons:

• Lots of threads use lots of memory and can make the CPU cache ineffective
• Too many active threads trying to execute in parallel may cause a high level of resource contention and be inappropriate for the amount of parallelism available

The new thread pool plugin offers an alternative thread pool implementation, and focuses on limiting the number of concurrent, short running statements to mazimize performance and reduce overhead.  By limiting the number of concurrent, short running statements and sharing threads, we can control the number of active threads at any one time.  Thread management has been revamped and by managing these threads in a highly efficient manner, we end up reducing overhead and often increasing performance. 

Here are the mechanics:  In the new plugin, threads are organized into groups (16 by default but configurable up to 64 on server startup).  Each group starts with one thread and can increase to a maximum of 4096 threads.  Additional threads are created only when necessary.  Each incoming connection request is assigned to a group by round robin. Each group has one listener thread that listens for incoming statement requests.

When a statement request comes in, it is executed immediately by the group’s listener thread if it is not busy and there are no other statement requests waiting.  If the statement request finishes quickly, the listener thread then efficiently returns to listening and is available to execute the next incoming request, preventing the need for a new thread to be created.   If the request does not finish quickly, it runs to completion but another thread is  created as the new listener.

If the listener thread is busy, the request is queued.  There will be a very brief time (configurable with the thread_pool_stall_limit system variable which defaults to 60 ms) while we wait to see if the currently executing statement will finish quickly or not. If it finishes quickly (under thread_pool_stall_limit), we can re-use this thread for the next request in the queue, eliminating the overhead of creating a new thread or having too many short statement trying to execute in parallel .

You can see how this thread pool design strives to have one thread executing per group at any time . The number of groups (thread_pool_size_variable) is very important, because it approximates the number of short running statements that will be executing concurrently at any one time.  Long running statements are prevented from causing other statements to wait, since if they go beyond the thread_pool_stall_limit, another thread will be started and the next request in the queue will execute on it.

Your predominant storage engine will help determine the number of groups you should have.  For InnoDB, between 16 and 36 groups seems to work well in many cases, but for MyISAM set it much lower (4-8).

There are two queues for waiting statements, low and high priority.  The low priority queue contains:

• all statements for non-transactional storage engines
• all statements if autocommit is enabled
• the first statement in  an InnoDB transaction

These statements do not languish in the low priority queue forever since they will get kicked over to the high priority queue when the thread_pool_kickup_timer times them out. However, there is a maximum number of statements that can be moved per time period to keep things under control.

The high priority queue contains

• any subsequent statements in InnoDB transactions, and
• any statements kicked up from the low priority queue.

You can find the thread pool plugin and other commercial extensions in MySQL 5.5.16 and above, available on http://support.oracle.com. This release contains a plugin library object file which must be placed in the appropriate directory.  The server must then be started with the –plugin-load option. Documentation  and complete install directions for the plugin can be found at http://dev.mysql.com/doc/refman/5.5/en/thread-pool-plugin.html.   There is also a thread pool API available in the Community Edition.

Happy swimming!

Lynn Ferrante has worked with databases in the enterprise for her whole career at MySQL, Oracle, Sybase, and Ingres.  She also worked on an open source project called GenMapp (University of California, San Francisco), and contributed to the development of many database applications in the fields of energy and environment

PlanetMySQL Voting: Vote UP / Vote DOWN

All DBAs understand the importance and priority of quick, reliable database backup and recovery operations.  In fact, dating back to my early days with MySQL, the most commonly requested product features from the MySQL user base have been around online, non-blocking backup solutions for running MySQL servers.  In response, Oracle now provides MySQL Enterprise Backup ("MEB") which performs high performant, online "hot" backups for MySQL databases.  MEB provides all of the backup/recovery features and functionality DBAs expect, all from a scriptable command line interface.  You can learn all about MEB in the related MySQL docs.

My congratulations and appreciation go out to Lars Thalmann and the MySQL Enterprise Backup engineering team for the recent release of MEB 3.6.  While there are many great improvements in this specific release, as an operational DBA I am most excited about the new support for single file streaming and for the SBT interface features, described here:

Single File Streaming - This allows DBAs to offload the footprint of backup images to a different server or storage device without having to store them locally on the MySQL database server.  This removes storage and related overhead from the server being backed up and speeds up total backup time by removing the need to copy local backup images (which even when compressed can be very large) over the network to their ultimate network destination.  You can learn about this specific MEB option along with a good usage example here.

Support for SBT interface - The "Secure Backup to Tape" interface was originally developed by Oracle as a standard way for third-party backup media providers to easily integrate their solutions with Oracle Recovery Manager ("RMAN").  SBT is now supported in MEB 3.6 so MySQL backup images can now be generated by and streamed directly to advanced enterprise backup media management solutions (Oracle Secure Backup, Symantec Netbackup, most others) that are already deployed within an environment.  This simplifies MySQL administration by enabling DBAs to incorporate MySQL backup/recovery operations and media rotation/retention policies into existing standard operating procedures.  You can learn all about this new option, again with a useful example here.

MySQL Enterprise Backup is part of the commercial MySQL Enterprise Edition but like all Oracle products is free to download and use without obligation for 30 days.  This is a great way to try it out to see if it fits your needs.  

You can download and begin working with MEB 3.6 now:

1. Go to Oracle eDelivery.
2. Enter some basic details and click through the agreement.
3. Select "MySQL Product Pack", then your platform, then Go.


I will keep you posted as new MySQL product features and interesting Oracle integrations become available.  As always, thanks for your continued support of MySQL! 
PlanetMySQL Voting: Vote UP / Vote DOWN

So I’ve been doing a fair number of automated load tests these past six months. Primarily with Sysbench, which is a fine, fine tool. First I started using some simple bash based loop controls to automate my overnight testing, but as usually happens with shell scripts they grew unwieldy and I rewrote them in python. Now I have some flexible and easily configurable code for sysbench based MySQL benchmarking to offer the community. I’ve always been a fan of giving back to such a helpful group of people – you’ll never hear me complain about “my time isn’t free”. So, let me know what you want in an ideal testing environment (from a load testing framework automation standpoint) and I’ll integrate it into my existing framework and then release it via the BSD license. The main goal here is to have a standardized modular framework, based on sysbench, that allows anyone to compare their server performance via repeatable tests. It’s fun to see other people’s benchmarks but it’s often difficult to repeat and compare since most tests aren’t fully documented in their blog posts – this could be a solution to that.

Currently I have the harness doing iterations based on:

• incrementing (choose a global dynamic variable, ie: sync_binlog=0-1000) system values
• storage engine vs storage engine for the same workload
• thread quantity increments for read-only or read+write
• N-nodes in a cluster workloads with WRR traffic distribution (need to code WLC and others)
• QPS testing for connection pool vs open/close connection
• multi-table vs single-table workloads

Outputs available: CSV, XML, JSON for easy integration into any number of the various graphing frameworks available. I’ll probably code up a light weight python http server preloaded with Highcharts and Sparklines so you can see your benchmarks easily without having to roll your own graphs.

Quick now, tell me what you’d like me to code for you!

PlanetMySQL Voting: Vote UP / Vote DOWN

Packt Publishing recently sent me a copy of MySQL for Python to review and after reading through the book I must say that I’m rather impressed at the variety of topics that the book covers.

It starts off with the basics of setting up MySQL for your testing/development needs by going over several of the common installation and configuration methods. After that it’s a quick intro for connection methods and simple error reporting for connections. The author gives a quick intro to CRUD and how it relates to databases and python before heading into the common tasks of simple queries. I was surprised to see some database profiling discussion; which is rather handy for a new coder or a person new to MySQL. Once the basics of Inserts/Selects/Updates/Deletes are covered, which is a rather quick read, there is a welcome discussion of transactions and commit methods – if you do not read this section and are new to MySQL then believe me, you’re missing a very important topic. Most people will gloss over the basics and head right to the more advanced chapters that feature exception handling, the all too common “the mysql server has gone away” error, date&time functions, aggregate functions, and metadata queries. These chapters were the most interesting to me as they covered some great code for python that I have not yet played around with. Previously I’ve done a lot of work on those topics with perl and php so seeing how they were done in python was a great treat. The code is concise, easy to read, and well explained.

A number of topics cover the time saving solutions that no one should be without. Namely, bulk data inserting, data formatting, row iteration, and CSV parsing. Logging methods for access and changes to the database are also covered, and in the end will save your development cycle a lot of time when you are troubleshooting app-to-db interaction.

Two chapters will be of interest to DBAs in particular, and possibly not as interesting to pure developers, of which these are the Disaster Recovery and MySQL Administration topics. The author covers offline backups as well as online hot backups, two sections that no DBA should be without. The code for this type of work is covered in a decent amount of discussion but, along with the other chapters in the book, the theory and background of the topic is also discussed which gives the new reader an understanding of “why” and not just left with the “how”. The administration section of the book covers user creation and permissions management, along with a bit of background on security involved with that task, and also goes into quite a lot of coverage on web-based GUI administration and command line interaction for admin purposes.

Overall I enjoyed the contents of the book and would recommend taking a look if you are new to Python and MySQL or are even looking for a quick reference to the common tasks of database driven application development. This book does not cover the common ORM database interactions you’re likely to see in an app like Django or Pylons, but it will give you a solid foundation on how python and MySQL interact without an abstraction layer. If you are writing quick admin code or building your own database interaction layer, then this book would do well to be in your collection.

You can find the book at Amazon or directly from Packt.

PlanetMySQL Voting: Vote UP / Vote DOWN

So I’m looking for virtual servers in Europe for a new Python+MySQL based application and I found a provider with some good prices (Hosteurope.de). So I select the VM that I desire and go to the order form, but there’s no option for United States of America. I find this odd, so I email their support team to see why I can’t pay for services if I live in the USA. Here is their response: “Unfortunately we cannot accept your order due to internal policies” – What exactly is the internal policy where a company turns down sales specifically because the user is from the USA – I have verifiable credit, a real address, real bank accounts, and a real business. Yet hosteurope.de refuses legitimate business from the USA.

Does anyone know why this anti-USA policy is? And since they clearly don’t want my business, does anyone know of a good (and inexpensive) provider for virtual servers that aren’t anti-america?

—-
Dear Mr Reid

> I would very much like to use your services, however there was no option for
> USA in the country drop down menu when ordering – so I was forced to chose
> Austria. Can you tell me how I can order a virtual server from your company
> while residing in the United States of America? I can pay for a year of
> services in advance. I’m starting a new project and this could turn into a
> large account for your sales team. It would seem odd to turn away perfectly
> good business.
>
> Please let me know as I need to provision a server in Germany as soon as
> possible for my application.

Unfortunately we cannot accept your order due to internal policies. We apologize for the inconvenience.

Kind regards
Mit freundlichen Grüßen
Dominik Antulov

–
Dominik Antulov
Auftragsmanagement
Abteilung Hosting

E-Mail: support@hosteurope.de
Telefon: 0800 467 8387
Fax: +49 180 5 66 3233 (*)

+++ Nützliche Links
Host Europe FAQ (häufig gestellte Fragen): http://faq.hosteurope.de
Forum “Kunden helfen Kunden”: https://kis.hosteurope.de/forum/

PlanetMySQL Voting: Vote UP / Vote DOWN

In my continuing blog series on MySQL 5.5 features (see performance/scale and replication entries) today I covering some of the new InnoDB manageability and efficiency options.  5.5, with the newly re-architected InnoDB, provides better user control over internal InnoDB settings so things like performance, scale and storage can easily be monitored, tuned and optimized for specific use cases and application loads.

Along these lines, some of the key advances and features available in MySQL 5.5 and InnoDB are:
 

• Faster Index Creation - MySQL 5.5 can now add or drop indexes without copying the underlying data of the entire target table.  This improves the efficiency and speed of creating and dropping indexes on InnoDB tables.
• Efficient Data Compression - New InnoDB table compression options significantly reduce the storage requirements for the MySQL database and improve application throughput by reducing I/O workload, all with minimal overhead and operating expense.  Users can specify new ROW_FORMAT and KEY_BLOCK_SIZE parameters in the CREATE TABLE and ALTER TABLE commands to store data pages in 1K, 2K, 4K, 8K or the standard 16K byte compression levels.  InnoDB also provides new INFORMATION_SCHEMA tables around compression so users can monitor and tune their implementations for optimal efficiency (see below). 
• Efficient Storage Options for Large Objects and Variable-Length Columns - MySQL 5.5 improves storage efficiency of large objects by allowing users to create tables using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED.  With these options long column values are stored fully off-page, and the associated clustered index record contains only a 20-byte pointer to the overflow page.

It should be noted that the new compression and large object and variable length column storage options require the use of the new Barracuda file format, so you'll want to reference the docs to understand the benefits (and ramifications) of making that move.

• New INFORMATION_SCHEMA tables - 7 new tables provide information specific to InnoDB compression and transaction locking.

• INNODB_CMP - Contains status information on the efficiency and operations related to compressed tables.

• INNODB_CMP_RESET - Same as above, but reading from this table resets the reported statistics.
• INNODB_CMPMEM - Contains status information on the compressed pages that reside in the buffer pool.
• INNODB_CMPMEM_RESET - Same as above, but reading from this table resets the reported statistics.
• INNODB_TRX - Contains information about every transaction currently executing inside InnoDB, including whether the transaction is waiting for a lock, when the transaction started, and the particular SQL statement the transaction is executing.
• INNODB_LOCKS - Contains a row for each blocked transaction that describes each lock the transaction has requested and what the transaction is waiting on.
• INNODB_LOCK_WAITS - Contains information about transactions that are waiting for a specific lock.

Learn about all of the new MySQL 5.5 and InnoDB performance and scalability enhancements, including how to enable and implement them here.

As always, THANKS for reading and THANKS for your support of MySQL!

PlanetMySQL Voting: Vote UP / Vote DOWN

The convergence of telecommunications onto IP (Internet Protocol) based networks is revolutionizing today's communications industry. Communications Service Providers (CSPs) are challenged to deliver compelling new personalized services with greater agility and lower costs than ever before.

Whether they are looking to deploy new Web/Telco 2.0 applications to mobile Internet users or consolidating subscriber data within the network to support greater service personalization and targeted communications, the database plays a key role in enabling new services.

We believe MySQL is pretty much unique in the industry by providing the insight and experience to marry the innovation and speed of the Web with the proven capabilities of the carrier network. MySQL is deployed in 9 of the top 10 most trafficked sites on the web [1] including Google, Facebook and YouTube.  MySQL is also extensively deployed in the network domain, powering real-time subscriber and service delivery solutions for the likes of Alcatel-Lucent, BT Plusnet and Telenor where carrier-grade availability and performance are critical.

And the fact that MySQL can do this with open-source technology means that you can reduce costs while you improve services and deliver them to market faster.

This combination of web-scale performance and carrier-grade availability is what lead Nokia to select MySQL to power its data platform for large-scale, mission-critical Web-based services accessed via a variety of smartphone applications. Yekesa Kosuru, Distinguished Architect at Nokia won the Oracle award for MySQL Developer of the Year 2010 as a result of this project 

You can learn more about how MySQL is used in telecommunications from our upcoming webinar scheduled on December 15th.  Don't worry if you can't attend live - by registering you will be automatically notified when the on-demand event replay is available

To understand the adoption and impact of open source software within the telecommunications industry, IDC has also published a whitepaper which we've made freely available by registering for the download here 

As Service Providers reposition themselves to become the central enabler of social and business communications services, so the value delivered by IT solutions in general and data management technology in particular becomes increasingly critical.  As the examples above demonstrate, you weren't go far wrong in looking at MySQL as a data platform for new service innovation.  You might even win a nice award as well !

[1] http://www.alexa.com/topsites

PlanetMySQL Voting: Vote UP / Vote DOWN

In my continuing MySQL 5.5 blog series, today I am covering what's new on the replication front.  MySQL replication is my favorite server and what drew me to MySQL during my tenure with Embarcadero Technologies.  Others seem to agree as based on community and customer surveys, MySQL replication is the most popular and widely used database feature.  Mostly because it is easy to set up and ease, it enables scalability and provides a pretty robust solution for data redundancy, backup and overall availability.  In MySQL 5.5 replication has been enhanced in response to user requests that MySQL replication:

• Ensure data consistency between master and slave servers
• Immediately detect if replication is not working
• Allow a crashed slave to automatically recover from the master relay log
• Allow users to filter events for specific servers
• Correctly convert data types between master and slave servers

MySQL 5.5 replication includes the following enhancements that support users in these key areas:
 
Semi-synchronous Replication

MySQL replication is asynchronous by default meaning that a master and its slave/slaves are autonomous when it comes to data consistency.  Asynchronous replication provides optimal performance because a master is free to service other inbound transactional requests after
writing updates to its Binlog without waiting to verify that updates have been replicated to at least one slave in the topology.  While fast, this comes with a high risk of master/slave data inconsistency or even data loss at recovery if there is a failure on either end.  

MySQL 5.5 introduces semi-synchronous replication to ensure data consistency and redundancy between master and at least one slave in the calling chain.  In this configuration, a master and any number of its replicant slaves are configured so that at least one slave in the replication topology must acknowledge updates have been received and written to its relay log before the parent master commits the transaction.  In the event of a time-out, the originating master will temporarily switch to asynchronous replication until at least one of the slaves set up for semi-synchronous replication catches up.

Semi-synchronous replication must be enabled on both the master and slave servers, otherwise the master defaults to asynchronous replication.  MySQL 5.5 uses a new plug-in architecture to enable semi-synchronous replication.  To this end, the following commands and variable settings are used to enable 5.5 masters and slaves.  Static settings can also be added to the my.* configuration files:

To enable the semi-synchronous replicator on the master:

INSTALL PLUGIN 'rpl_semi_sync_master' SONAME 'semisync_master.so';
SET rpl_semi_sync_master_enabled=1;
SET rpl_semi_sync_master_timeout=1000; (1s, default 10s)

To enable the semi-synchronous replicator on a slave or slaves:

INSTALL PLUGIN 'rpl_semi_sync_slave' SONAME 'semisync_slave.so';
SET rpl_semi_sync_slave_enabled=1;
START SLAVE;

Once enabled semi-synchronous replication exposes new system and status variables that can be used to check on configuration and operational status.  The values for each are exposed using SHOW VARIABLES and SHOW STATUS.  These include:
 
On master:

• Rpl_semi_sync_master_status - indicates status of when master is using asynchronous or semi-sycnhronous replication.
• Rpl_semi_sync_master_clients - shows how many slaves are configured for semi-synchronous replication.
• Rpl_semi_sync_master_yes_tx - shows number of successfully acknowledged commits by slaves.
• Rpl_semi_sync_master_no_tx - shows number of unsuccessfully acknowledged commits by slaves.

On Slave:

• Rpl_semi_sync_slave_status - indicates if semi-synchronous replication is enabled on slave.

Replication Heartbeat

MySQL 5.5 provides a new replication heartbeat option that helps users know immediately when replication stops working.  The heartbeat is a message sent at regular intervals from a master node to slave nodes.  The slave can be configured to automatically check connection and message status; if the message is not received by the slave the slave knows that a connection to the master node has failed in some way. 

Replication heartbeat is an optional configuration and is enabled on the 5.5 slave using:

STOP SLAVE;
CHANGE MASTER TO master_heartbeat_period= milliseconds;
START SLAVE;

The following status variables can then be monitored to easily detect when a master is idle and to get a finer-grained estimate on slave seconds behind master for recovery purposes:

SHOW STATUS like 'slave_heartbeat period'
SHOW STATUS like 'slave_received_heartbeats'


Automatic Relay Log Recovery

MySQL 5.5 ensures master/slave consistency on a restart by allowing replication users to optionally configure slaves to automatically discard its own unprocessed relay logs and then recover pending transactions from the originating master.  This can be used after a slave crash to ensure that potentially corrupted relay logs are not processed.   For compatibility the default for this is disabled, but can be set using the new relay_log_recovery=value to 1 on the slave to be recovered.

Replication Per Server Filtering

Circular, or multi-master replication, provides a highly available deployment that ensures redundancy of data in the case any of the servers in a topology ring fails or is removed.  In this configuration master servers are configured so that each is also a slave of another server in the topology.  Updates written to any of the masters are then replicated around the ring until the transaction reaches the originating server which acts as the terminator of its own events.  In the event of a node failure the affected server is removed from the topology and its slave is simply redirected to another master in the ring and processing then continues.

In previous versions when a server is removed from the ring due to failure, maintenance, etc. users needed to manually ensure that all of its updates were terminated from the new calling chain.  MySQL 5.5 provides a new set of time-saving commands that allow users to easily filter out any events related to a removed server.




In the above case, when Server A is removed from the topology, users can now easily filter any Server A related events by entering the following command on the next server in the calling chain:

Server B> CHANGE MASTER TO MASTER_HOST=D ...
IGNORE_SERVER_IDS=(A)
 

Replication Slave Side Data Type Conversions

In MySQL 5.1 precise data type conversions between master and slave are supported for statement-based replication only.  In this configuration column types could be different in the master and slave tables as long as the underlying data had high level compatibility (INT to TINYINT for example).  MySQL 5.5 now provides precise data type conversions between master and slave for both statement-based and row-based operations.   Conversions within integer, decimal, string, binary, BIT, ENUM and SET domains are supported.
 
A new SET variable in 5.5 enables the conversion, and requires that the slave be restarted to take effect.  The settings and what they enable are:

• SET SLAVE_TYPE_CONVERSIONS="ALL_LOSSY' - enables conversions to types with smaller domain (INT to TINY for example)
• SET SLAVE_TYPE_CONVERSION="ALL_NON_LOSSY" - enables conversions to types with larger domain (TINY to INT for example)

The above is not exhaustive so you can learn more about all of the new MySQL 5.5 replication features, including how to enable, monitor and tune them, here.


Thanks for reading!
 
PlanetMySQL Voting: Vote UP / Vote DOWN