Engineering threads within a Data Node
A new version of the white paper “Guide to Optimizing Performance of the MySQL Cluster Database
” has been released; download it here
This paper steps you through:
- Identifying if your application is a good fit for MySQL Cluster
- Measuring performance and identifying problem performance areas to address
- Optimizing performance:
- Access patterns
- Using Adaptive Query Localization for complex Joins
- Distribution aware applications
- Batching operations
- Schema optimizations
- Query optimization
- Parameter tuning
- Connection pools
- Multi-Threaded Data Nodes
- Alternative APIs
- Hardware enhancements
- Scaling MySQL Cluster by Adding Nodes
As well as the kind of regular updates that are needed from time to time, this version includes the extra opportunities for optimizations that are available with MySQL Cluster 7.2 such as faster joins and engineering the threads within a multi-threaded data node.
As a reminder, I’ll be covering much of this material in an upcoming webinar.
MySQL Replication from 1 Master to Multiple Slaves
A new white paper has been published that covers MySQL Replication – background information, how it works, how to use it and what’s new in MySQL 5.5. Simply register for the white paper at mysql.com and you’ll be sent your free copy.
The paper starts by covering the fundamental concepts behind replication such as the difference between synchronous and asynchronous replication and the idea behind semisynchronous replication.
It goes on to describe the common use-cases for replication – scaling out, high availability, geographic redundancy and offloading backups or analytics.
Various replication topologies are discussed from simple master-slave to multi-master rings.
As the title of the paper suggests, the paper covers the significant replication changes introduced in MySQL 5.5:
- Semisynchronous replication: Improved resilience by having master wait for slave to persist events.
- Slave fsync tuning & Automatic relay log recovery: Option to dictate when relay logs are written to disk rather than relying on default operating system behavior; set sync_relay_log=1 to ensure that no more than 1 statement or transaction is missing from the relay log after a crash. The slave can now recover from corrupted relay logs by requesting corrupt entries to be resent from the master. Three new options are introduced (sync-master-info, sync-relay-log and sync-relay-log-info)
- Replication Heartbeat: Automatically checks the status of the connection between the master and the slave(s), allowing a more precise failure detection mechanism. Can detect loss of connection within milliseconds (configurable). Avoid unnecessary relay log rotation when the master is idle.
- Per server replication filtering: When a server is removed from a replication ring, a surviving server can be selected to remove its outstanding replication messages once they’ve been applied by all servers.
- Precise Slave Type Conversions: Allows different types to be used on the master and slave, with automatic type promotion and demotion when using row-based replication (already possible with statement-based replication)
- Individual Log Flushing: Selectively flush server logs when using ‘FLUSH LOGS’ for greater control
- Safe logging of mixed transactions: Replicate transactions containing both InnoDB and MyISAM changes
To be better able to use replication, it helps to understand the basics about how MySQL Replication has been implemented – for example the roles of the binary and relay logs and so this is covered.
Perhaps the most useful sections are those that actually walk through using MySQL Replication – specifically these activities:
- Configuring, running and testing replication
- Migrating from traditional asynchronous replication to semisynchronous replication
- Administering & monitoring replication (including MySQL Enterprise Monitor)
- Failover and recovery
Replication is a little different when using MySQL Cluster; most significantly because you can have multiple MySQL Servers and other application nodes modifying the same database but also because the domains where MySQL Cluster has been used have required extreme High Availability and so there are Cluster-unique features such as active-active replication with conflict detection and resolution. This paper isn’t focussed on MySQL Cluster but a summary of these differences is included.
MySQL Cluster Connection Pooling
This guide explores how to tune and optimize the MySQL Cluster database to handle diverse workload requirements. It discusses data access patterns and how to build distribution awareness into applications, before exploring schema and query optimization, tuning of parameters and how to get the best out of the latest innovations in hardware design.
The Guide concludes with recent performance benchmarks conducted with the MySQL Cluster database, an overview of how MySQL Cluster can be integrated with other MySQL storage engines, before summarizing additional resources that will enable you to optimize MySQL Cluster performance with your applications.
Download the white paper (as always, for free) from: http://www.mysql.com/why-mysql/white-papers/mysql_wp_cluster_perfomance.php
MySQL (part of Sun Microsystems) have just announced the release of MySQL Cluster 7.0. This is a major new version of the database.
The highlights of the release (as well as an overview of the MySQL Cluster architecture) have been published in a new white paper: MySQL Cluster 7.0: Architecture and New Features.
For those in a rush, here are the highlights of the highlights:
- Multi-threaded data nodes. Could previously exploit up to 2 cores/CPUs/threads for a single data node. This is extended to 8 cores by introducing a multi-threaded version of the ndb process. This delivers a very significant performance improvement if running on a host with more than a dual core.
- On-line add node. The ability to add a new node group to an in-service Cluster without impacting service. Includes the ability to repartition the data in existing tables after the new node group has been added.
On-Line Add Node
- Carrier-Grade directory back end. Support for using MySQL Cluster as the data store for a directory server. Allows LDAP access to your data. OpenLDAP will be the first directory to release a driver for this.
- Multi-Threaded Disk Data File Access. Improves the performance for disk-based table data.
- Improved large record handling. Performance/network bandwidth improvements.
- Alpha version of a Windows port. Intended for development use only, not for deployment.
- Snapshot option for backups. Option to have the backup represent the state of the database at the point that the backup was started rather than when it finished. This lets you synchronise it with other, external events such as the backing up of other databases.
- Caching of configuration data. When a node restarts, it will only adopt any changes to the config files if explicitly told to – avoiding the situation where one node restarts and ends up with different config data to the others.
- Transactions for schema changes. Safer way to update the schema that can tolerate node failures mid-operation.