Wednesday Jul 22, 2015
Thursday Jun 12, 2014
By Rebecca Hansen on Jun 12, 2014
have audited well over 120 billion
events, each of which was processed and stored in a MySQL database. FairWarning is the world's leading
supplier of privacy monitoring solutions for electronic health records, relied
on by over 1,200 Hospitals and 5,000 Clinics to keep their patients' data safe.
In January 2014, FairWarning was
awarded the highest commendation in healthcare IT as the first ever Category Leader
for Patient Privacy Monitoring in the "2013 Best in KLAS: Software &
FairWarning has used MySQL as their solutions’ database from their start in 2005 to worldwide expansion and market leadership. FairWarning recently migrated their solutions from MyISAM to InnoDB and updated from MySQL 5.5 to 5.6. Following are some of benefits they’ve had as a result of those changes and reasons for their continued reliance on MySQL (from FairWarning MySQL Case Study).
Scalability to Handle Terabytes of Data FairWarning's customers have a lot of data:
- On average, FairWarning customers receive over 700,000 events to be processed daily.
- Over 25% of their customers receive over 30 million events per day, which equates to over 1 billion events and nearly one terabyte (TB) of new data each month.
- Databases range in size from a few
hundred GBs to 10+ TBs for
enterprise deployments (data are rolled off after 13 months).
Zero Admin = Few DBAs "MySQL
has not required a lot of administration. After it's been tuned, configured,
and optimized for size on initial setup, we have very low administrative costs.
I can scale and add more customers
without adding DBAs. This has had a big, positive impact on our business.”
- Chris Arnold, FairWarning Vice President of Product Management and Engineering.
Performance Schema As the size of FairWarning's customers has increased, so have their tables and data volumes. MySQL 5.6’ new maintenance and management features have helped FairWarning keep up. In particular, MySQL 5.6 performance schema’s low-level metrics have provided critical insight into how the system is performing and why.
Support for Mutli-CPU Threads MySQL 5.6' support for multiple
concurrent CPU threads, and FairWarning's custom data loader allow multiple
files to load into a single table simultaneously vs. one at a time. As a
result, their data load time has been
reduced by 500%.
MySQL Enterprise Hot Backup Because hospitals and clinics
never stop, FairWarning solutions can’t either. FairWarning
changed from using mysqldump to MySQL Enterprise Hot Backup, which has reduced
downtime, restore time, and storage requirements. For many of their larger customers, restore time has decreased by 80%.
Enterprise Edition and Product Roadmap Provide Complete Solution "MySQL's product
roadmap fully addresses our needs. We like the fact that MySQL Enterprise
Edition has everything included; there's no need to purchase separate modules." - Chris Arnold
- FairWarning MySQL Case Study
- Why MySQL 5.6 is an Even Better Embedded Database for Your Products presentation
- Updating Your Products to MySQL 5.6, Best Practices for OEMs on-demand webinar (audio and / or slides + Q&A transcript)
- MyISAM to InnoDB – Why and How on-demand webinar (same stuff)
- Top 10 Reasons to Use MySQL as an Embedded Database white paper
 2013 Best in KLAS: Software & Services report, January, 2014. © 2014 KLAS Enterprises, LLC. All rights reserved.
Friday Apr 26, 2013
By Mat Keep on Apr 26, 2013
Last week we ran a live webinar presenting the new MySQL Web Reference Architectures, a set of documented and repeatable best practices for building highly available, scaleable and secure database infrastructure to power new generations of web and mobile services.
The webinar replay is now available on-demand so you can listen in from the comfort of your own desk…or commute.
As a taster - we discuss sizing and design patterns - you can see a sample below:
We received some great questions during the Q&A session which I felt would be useful to you, so I've included them below:
Q. You are talking a lot about MySQL 5.6. We are on MySQL 5.1 - what are the reasons for us to upgrade:
There is a great summary in the MySQL 5.6 developer / DBA guide. You will see the advantage of 5.6 for web and cloud applications in performance, scaling, agility,security, instrumentation and reliability.
Q. When using replication, the master sends only the log file for the slave?
A. The binary log contains all of the database writes on the master. The master then sends the data from this log file to each of the slaves so that they can apply the same writes. It doesn't send the log file itself but sends the changes as they're written to the file (or if a slave disconnects and then reconnects the master will send over all changes that it missed). You can find out more in the introduction to MySQL replication paper
Certainly you could use corosync and pacemaker - in which case you're right that mysqlrpladmin might be the best option as then you only have one brain deciding when to fail everything over. Alternatively you can bind your own scripts into mysqlfailover and you could use those to trigger a VIP failover
Q. What should we do to address any potential replication lag?
A. Fast cores on the slave would help. If you're able split your tables between different schemas (databases in MySQL terms) then you can exploit mutil-threaded slaves (parallel slave apply) in MySQL 5.6. Also take a look at optimized row-based replication in MySQL 5.6
Q. Does MySQL 5.6 offer any replication related improvements over 5.5?
A. You bet. Performance, reliability, flexibility. See the introduction paper referenced in the answer above
Q. Any resources you know of on implementing the Medium-size MySQL web reference architecture with Ruby on Rails apps in the application layer?
A. General best practices outlines in the MySQL web reference architectures guide will apply. There are more specific MySQL / Ruby on Rails best practices here. You can also use community-developed MySQL Ruby connectors
Q. How can we integrate MySQL with Hadoop?
A. You have several options which can be used independently or together. Apache Sqoop provides batch transfers between MySQL and Hadoop, and is also fully bi-directional, so you can replicate the results of Hadoop Map Reduce jobs back to MySQL tables. There is also the new MySQL Applier for Hadoop which enables the streaming of events in real-time from MySQL to Hadoop.
You can learn more about all of the option from the MySQL and Hadoop guide to big data
Q. MySQL Cluster looks interesting, but when we looked at it in the past, it didn't support Foreign Keys, so not sure how you can suggest it as an option for eCommerce apps?
A. There are many eCommerce providers already running MySQL Cluster - Shopatron is a great example. The latest development milestone release of MySQL cluster 7.3 adds native support for Foreign Keys as well - so worth taking another look!
Q. is it possible to customise auto-sharding when using MySQL Cluster?
A. Yes, you can choose which column within the primary key is to be used as the 'sharding key' - that way you can for example make sure that all data for a specific user will be in the same data node; this can help ensure linear scalability. Details in the MySQL Cluster Performance Optimization guide
Q. Any advice on UserID generation for sharding?
A. One design pattern is to just use an auto-increment column and then hash on that. Another option is to use a UUID - you can then shard by ranges or by hashing
Q. Can we choose one : clustering or sharding? which one better for HA?
A. It depends on a number of factors such as what level of availability you need, how your application is architected, etc. A good resource to help you weigh up the pros and cons of the different approaches is the MySQL guide to high availability
Q. My company is using 5.5 with one master and slave for our web application. We are currently looking into a new system infrastructure using VM technology. What would you recommend?
A. There are many options available to you on multiple hypervisors. We have created a VM Template for MySQL running on Oracle VM and Oracle Linux. This is a pre-installed, pre-configured and certified software stack which you download as a single file and then provision to an Oracle VM Server farm. You can learn more about it from the Oracle VM Template for MySQL Enterprise Edition guide
Q. I'm interested in the MySQL monitoring tools and in the audit plug-in - how do I get these?
A. These are available as part of MySQL Enterprise Edition which also packages support, backup, security plug-ins, etc in an annual subscription.
Q. How much would it cost to run MySQL Enterprise Edition on that "perfect server"?
A. Pricing is available in the on-line store. Note that the list price is the same regardless of number of cores unless you exceed 4 CPU *sockets*
Q. i am sorry for this very novice question, but could I have some help regarding references that would allow me to digest the" base" of these technologies presented her?
A. Assuming it is case studies you are after, we have a lot of them published at the MySQL customers page. In the "Industry" drop down box, take a look at Web eCommerce, games, SaaS/Hosting and social networks
Monday Apr 22, 2013
By Mat Keep on Apr 22, 2013
Enabling Real-Time MySQL to HDFS Integration
Batch processing delivered by Map/Reduce remains central to Apache Hadoop, but as the pressure to gain competitive advantage from “speed of thought” analytics grows, so Hadoop itself is undergoing significant evolution. The development of technologies allowing real time queries, such as Apache Drill, Cloudera Impala and the Stinger Initiative are emerging, supported by new generations of resource management with Apache YARN
To support this growing emphasis on real-time operations, we are releasing a new MySQL Applier for Hadoop to enable the replication of events from MySQL to Hadoop / Hive / HDFS (Hadoop Distributed File System) as they happen. The Applier complements existing batch-based Apache Sqoop connectivity.
Replication via the MySQL Applier for Hadoop is implemented by connecting to the MySQL master and reading binary log events as soon as they are committed, and writing them into a file in HDFS.
The Applier for Hadoop uses an API provided by libhdfs, a C library to manipulate files in HDFS. The library comes precompiled with Hadoop distributions.
It connects to the MySQL master to read the binary log and then:
- Fetches the row insert events occurring on the master
- Decodes these events, extracts data inserted into each field of the row, and uses content handlers to get it in the format required
- Appends it to a text file in HDFS.
Databases are mapped as separate directories, with their tables mapped as sub-directories with a Hive data warehouse directory. Data inserted into each table is written into text files (named as datafile1.txt) in Hive / HDFS. Data can be in comma separated format; or any other, that is configurable by command line arguments.
You can learn more about the design of the MySQL Applier for Hadoop from this blog
The installation, configuration and implementation are discussed in detail in this Applier for Hadoop blog. Integration with Hive is also documented.
You can also see it in action from this MySQL Hadoop Applier Video Tutorial
With the growth in big data projects and Hadoop adoption, it would be great to get your feedback on how we can further develop the Applier to meet your real-time integration needs. Please use the comments section to let the MySQL team know your priorities.
Tuesday Feb 05, 2013
By Mat Keep on Feb 05, 2013
- Use-cases such as scaling out on commodity hardware, high availability, geo-redundancy for DR and data placement, and supporting complex analytics alongside high velocity OLTP operations in the same cluster
- Controlling data consistency using different synchronisation models
- Monitoring and managing replication using tools such as the replication dashboard within MySQL Enterprise Monitor
- Configuring replication with master and slave .cnf files, creating users, introducing replication to an existing application, initialisation and checking proper operation, using GTIDs and other new features
- Administration and configuration by checking slave status, suspending replication and viewing binary logs
Tuesday Jan 08, 2013
By Mat Keep on Jan 08, 2013
Global Transaction Identifiers (GTIDs) are one of the key replication enhancements in MySQL 5.6. GTIDs make it simple to track and compare replication across a master - slave topology. This enables:
- Much simpler recovery from failures of the master,
- Introduces great flexibility in the provisioning and on-going management of multi-tier or ring (circular) replication topologies.
A new on-demand MySQL 5.6 GTID webinar delivered by the replication engineering team is now available, providing deep insight into the design and implementation of GTIDs, and how they enable users to simplify MySQL scaling and HA. The webinar covers:
- Concepts: What is a GTID? How does the server generate GTIDs? What is the life cycle of GTIDs? How are GTIDs used to connect to a master?
- Handling conflicts
- How to skip transactions using GTIDs
- What happens when binary logs are purged
- How to provision a new slave or restore from a backup
- MySQL utilities for automated failover and controlled switchover
To whet your appetite, an extract of the Q&A from the webinar is as follows. These, and many other questions were answered during the session:
Q. Which versions of MySQL support GTIDs?
A. MySQL 5.6.5 and above
Q. Is GTID ON by default in 5.6?
A. It is OFF by default
Q. What does the GTID contain?
A. It is made up of a unique ID for the server followed by an ever-increasing counter that's specific to that server
Q: Do GTIDs introduce any increased space requirements?
A: Yes, since GTIDs are stored in the binary log, the binary logs will be larger. However, we expect the overhead to be relatively small. GTIDs are written to the binary log in two places:
(1) A small header is stored in the beginning of the binary log. This contains the variable @@gtid_purged, i.e., a list of previously logged GTIDS. Since the list is range-compressed, this is expected to be small: a small fixed-size header plus 40 bytes times the number of master servers in your topology.
(2) A fixed size header is added before each transaction in the binary log. This is 44 bytes, so will typically be small compared to the transaction itself.
Q. I understand GTID's are associated with Transactions. How do they map to the events within each transaction, or do GTID's map as an event itself in a binlog file?
A. Yes, GTIDs are associated with transactions. In the binary log, the GTID is realized as an event written prior to the events that constitute the transaction. The event is called a Gtid_log_event.
Q What if a transaction spans a filtered out table and a non-filtered out table? How does it get recorded on the slave?
A. If the filters are on the master, then a partly logged transaction will be replicated with its GTID.
If filtering on the slave side, a partial image will be processed on the slave and the original GTID is logged (to the slave's binlog) with the processed transaction.
Q. Prior to GTID, to build a new slave, we use mysqldump --master-data=1 to get the slave starting sync point in the dump. With GTID enabled, does it set the gtid_executed / purged in the dump instead?
A. Yes, mysqldump will detect that the server uses GTIDs and output a SET GTID_PURGED statement. (And there is an option to turn off that, e.g., in case you want to execute the output on an old server).
Q. How do GTIDs enable failover and recovery?
A. GTIDs are using in combination with the MySQL utilities. The mysqlfailover and rpladmin utilities provide administration of GTID-enabled slaves, enabling monitoring with automatic failover and on-demand switchover, coupled with slave promotion. GTIDs make it straightforward to reliably failover from the master to the most current slave automatically in the event of a failure. DBAs no longer need to manually analyze the status of each of their slaves to identify the most current when seeking a target to promote to the new master.
Resources to Get Started
In addition to the webinar, here are some other key resources that will give you the detail you need to take advantage of GTIDs in your most important MySQL workloads:
- Engineering blog: Global Transaction Identifiers – why, what, and how
- Engineering blog: Advanced use of GTIDs
- Documentation: Setting up replication with GTIDs
- Video Tutorial: MySQL replication utilities for auto-failover and switchover
- Engineering Blog: Controlling read consistency with GTIDs
If you have any comments, questions or feature requests, don't hesitate to leave a comment on this blog
Monday Oct 08, 2012
By Mat Keep on Oct 08, 2012
Data is the currency of today’s web, mobile, social, enterprise and cloud applications. Ensuring data is always available is a top priority for any organization – minutes of downtime will result in significant loss of revenue and reputation.
There is not a “one size fits all” approach to delivering High Availability (HA). Unique application attributes, business requirements, operational capabilities and legacy infrastructure can all influence HA technology selection. And then technology is only one element in delivering HA – “People and Processes” are just as critical as the technology itself.
For this reason, MySQL Enterprise Edition is available supporting a range of HA solutions, fully certified and supported by Oracle. MySQL Enterprise HA is not some expensive add-on, but included within the core Enterprise Edition offering, along with the management tools, consulting and 24x7 support needed to deliver true HA.
At the recent MySQL Connect conference, we announced new HA options for MySQL users running on both Linux and Solaris:
DRBD (Distributed Replicated Block Device) is an open source Linux kernel module which leverages synchronous replication to deliver high availability database applications across local storage. DRBD synchronizes database changes by mirroring data from an active node to a standby node and supports automatic failover and recovery. Linux, DRBD, Corosync and Pacemaker, provide an integrated stack of mature and proven open source technologies.
DRBD Stack: Providing Synchronous Replication for the MySQL Database with InnoDB
Download the DRBD for MySQL whitepaper to learn more, including step-by-step instructions to install, configure and provision DRBD with MySQL
Oracle Solaris Cluster provides high availability and load balancing to mission-critical applications and services in physical or virtualized environments. With Oracle Solaris Cluster, organizations have a scalable and flexible solution that is suited equally to small clusters in local datacenters or larger multi-site, multi-cluster deployments that are part of enterprise disaster recovery implementations. The Oracle Solaris Cluster MySQL agent integrates seamlessly with MySQL offering a selection of configuration options in the various Oracle Solaris Cluster topologies.
Putting it All Together
When you add MySQL Replication and MySQL Cluster into the HA mix, along with 3rd party solutions, users have extensive choice (and decisions to make) to deliver HA services built on MySQL
To make the decision process simpler, we have also published a new MySQL HA Solutions Guide.
Exploring beyond just the technology, the guide presents a methodology to select the best HA solution for your new web, cloud and mobile services, while also discussing the importance of people and process in ensuring service continuity.
This is subject recently presented at Oracle Open World, and the slides are available here.
Whatever your uptime requirements, you can be sure MySQL has an HA solution for your needs
Please don't hesitate to let us know of your HA requirements in the comments section of this blog. You can also contact MySQL consulting to learn more about their HA Jumpstart offering which will help you scope out your scaling and HA requirements.
Thursday May 17, 2012
By Mat Keep on May 17, 2012
In the latest episode of our “Meet The MySQL Experts” podcast, Luis Soares, Engineering Manager of MySQL Replication discusses the new Global Transaction Identifiers (GTIDs) that are part of the latest MySQL 5.6 Development Release. We are also joined by Chuck Bell who discusses how the new MySQL HA utilities use GTIDs to create a self-healing replication topology.
In the podcast, we cover how GTIDs and the HA utilities are implemented, how they are configured and considerations for their use.
Of course, GTIDs are just one of the major new features of MySQL replication. For a complete overview, take a look at our DevZone article: MySQL 5.6 Replication - Enabling the Next Generation of Web & Cloud Services.
You can try out MySQL 5.6 and GTIDs by downloading the Development Release (select Development Release tab)
Tuesday Jan 10, 2012
By Hema Sridharan-Oracle on Jan 10, 2012
The MySQL Enterprise Backup (MEB) Team is pleased to announce the release of MEB 3.7.0, with several exciting and advanced features to benefit a wide audience. Included in this release are,
- Redo Log only Incremental Backup
- Incremental Backup without specifying LSN
- Validation of Backup Image using checksums
- Hot Backup of InnoDB .frm files
- Performance Improvements and
- Enhancements for Third-Party Media Managers
The gist and usefulness of all these new features are described in short below,
Redo Log Only Incremental Backup:
This is a new type of incremental backup that copies only the InnoDB redo log accumulated since the previous full or incremental backup. The original incremental backup technique copies from the InnoDB data files only those pages that were modified since the previous backup. This incremental backup technique based on the redo log is much faster in most cases than incremental backups that read the data files, if incremental backups are taken frequently. You can choose this new method by specifying the --incremental-with-redo-log-only option on the mysqlbackup command line.
Incremental Backup without specifying LSN:
The new option --incremental-base=dir:/ helps you to perform incremental backup without needing to specify lsn value. The input to --incremental-base=dir:/ option is location of any previous backup over which incremental backup is to be done.
Performance of backup-related I/O operations is improved, particularly on Windows, by reusing I/O library code and best practices from the MySQL Server product. To avoid memory fragmentation and overhead from frequent malloc() / free() sequences, the mysqlbackup command now does all read, compress, uncompress, and comparison operations within a fixed-size buffer that remains allocated while the command runs.
Validation of Backup Image using Checksums:
The new validate option of the mysqlbackup command tests the individual files within a single-file backup using a checksum mechanism. Validation helps to verify the integrity of the single-file backup image as the image file is moved between servers and thereby ensure that backups are reliable and consistent.
Hot Backup of InnoDB .frm files:
With this new feature, you do not have to manually copy the .frm files to perform restore. The new option --only-innodb-with-frm performs an InnoDB-only backup and backs up even the .frm files of InnoDB tables in non locking mode. Formerly, the InnoDB-only backup required putting the database briefly into a read-only state and copying the .frm files within your own backup script.
Enhancements to Third-Party Media Managers:
To customize the interactions between MySQL Enterprise Backup and media management software (MMS), the --sbt-environment option lets you pass application-specific environment settings to the MMS (for example, Oracle Secure Backup). Each vendor that uses the SBT programming interface could implement its own set of environment variables. The --sbt-environment variable lets you pass environment variable values from any invocation method (for example, a Makefile) rather than setting and unsetting the variables within a wrapper shell script.
For more information about MEB features and examples, please see the MEB documentation located <http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/index.html>. My sincere thanks to Lars Thalmann, Sanjay Manwani and all the MEB team members, who have provided valuable features and improvements for every release.
Download the MEB 3.7.0 package from the Oracle Software Delivery Cloud web site <https://edelivery.oracle.com/>. MySQL Enterprise customers can begin deploying MEB 3.7.0 immediately. Users without a MySQL Enterprise license can evaluate MEB 3.7.0 for free for 30 days; please try it out and send your feedback to firstname.lastname@example.org.
Thursday Sep 29, 2011
By Mat Keep on Sep 29, 2011
Databases are the center of today’s web, enterprise and embedded applications, storing and protecting an organization’s most valuable assets and supporting business-critical applications. Just minutes of downtime can result in significant lost revenue and dissatisfied customers. Ensuring database highly availability is therefore a top priority for any organization.
The new MySQL Guide to High Availability solutions is designed to navigate users through the HA maze, discussing:
- The causes, effects and impacts of downtime;
- Methodologies to select the right HA solution;
- Different approaches to delivering highly available MySQL services;
- Operational best practices to meet Service Level Agreements (SLAs).
As discussed in the new Guide, selecting the high availability solution that is appropriate for your application depends upon 3 core principles:
- The level of availability required to meet business objectives, within budgetary constraints;
- The profile of application being deployed (i.e. concurrent users, requests per second, etc.);
- Operational standards within each data center.
Recognizing that each application or service has different operational and availability requirements, the guide discusses the range of certified and supported High Availability (HA) solutions – from internal departmental applications all the way through to geographically redundant, multi-data center systems delivering 99.999% availability (i.e. less than 5 ½ minutes of downtime per year) supporting transactional web services, communications networks, cloud and hosting environments, etc.
By combining the right technology with the right skills and processes, users can achieve business continuity, while developers and DBAs can sleep tight at night! Download the guide to learn more.
Tuesday Apr 26, 2011
Monday Apr 18, 2011
Monday Apr 11, 2011
By Rob Young on Apr 11, 2011
Tuesday Feb 08, 2011
By Mat Keep on Feb 08, 2011
Get the latest updates on products, technology, news, events, webcasts, customers and more.
- MySQL 5.7 Community Contributor Award Program 2015!
- August - December 2015 -> Events where you can find MySQL
- Learn About Queries, Stored Routines, and More MySQL Developer Skills
- MyNA (MySQL User Group) meeting
- MySQL Enterprise Monitor Query Analyzer Identifies Problem Queries
- The MySQL Central @ OpenWorld Content Catalog is Live
- Gain MySQL DBA Expertise Where You Are
- Getting Started on MySQL Cluster
- Modirum Authenticates Transactions with MySQL
- Beginners on MySQL Should Start Here