Peter ZaitsevMySQL upgrade best practices (19.9.2014, 17:06 UTC)

MySQL upgrade best practicesMySQL upgrades are necessary tasks and we field a variety of questions here at Percona Support regarding MySQL upgrade best practices. This post highlights recommended ways to upgrade MySQL in different scenarios.

Why are MySQL upgrades needed? The reasons are many and include: Access to new features, performance benefits, bug fixes…. However, MySQL upgrades can be risky if not tested extensively beforehand with your application because the process might break it, prevent the application from functioning properly – or performance issues could arise following the upgrade. Moreover, I suggest keeping an eye on new releases of MySQL and Percona Server – check what has changed in the most recent version. Perhaps the latest release has a fix for an issue that you have been experiencing.

Upgrading one major version via SQL Dump:

Upgrading between one major version covers upgrading from Percona Server 5.1 to 5.5 or Percona Server 5.5 to 5.6 and the same implies to Oracle MySQL.

First of all, upgrading between one major version is neither straightforward nor risk-free. Initially you should read “Upgrading from Previous Series” documentation here and here. In that documentation, please place special attention to all of the sections marked “Incompatible Change” and check whether you may be affected by those changes. There might be configuration changes as well as variables renamed, a few older variables obsoleted and new variables introduced – so make sure that you adjust your my.cnf accordingly. For Percona Server specific changes please refer here and here for Percona Server 5.5 & Percona Server 5.6, respectively.

Now there are several possible approaches you may take, where one may be more feasible than the other depending on the current replication topology and total data size – and one might also be safer than another. Let me show you an upgrade procedure… an example upgrading from Percona Server 5.5 to Percona Server 5.6.

In general, there are two types of MySQL upgrades:

  • In place, where you use existing datadir against the new MySQL major version, with just running mysql_upgrade after binaries are upgraded,
  • SQL dump on an old version and then restore it on a new version (using mysqldump utility or alternatives, like mydumper).

Also in general the second type is safer, but as you may expect a much slower MySQL upgrade process.

Theoretically, the safest scenario is:

Here’s a basic procedure (you should stop application writes before starting).

1) Capture users and permissions information. This will backup all your existing user privileges.

$ wget percona.com/get/pt-show-grants;
$ perl pt-show-grants --user=root --ask-pass --flush > /root/grants.sql

2) Produce a logical dump of the 5.5 instance, excluding the mysql, information_schema and performance_schema databases:

$ mysql -BNe "SELECT SCHEMA

Truncated by Planet PHP, read more at the original (another 14797 bytes)

Link
Henrik IngoSelling Open Source 101: Lead Qualification (19.9.2014, 07:49 UTC)

By popular demand I have decided to continue my series on selling open source (Part 1, Part 2).

A couple readers both reacted my previous blog with more or less the same words: This is great, but what about the level of mission criticality of the use case? Surely you should count that as a third variable since it impacts the likelihood of a user becoming a paying customer?

Lead qualification

read more

Link
Peter ZaitsevManaging big data? Say ‘hello’ to HP Vertica (18.9.2014, 14:29 UTC)

Managing big data? Say ‘hello’ to HP VerticaOver the past few months, I’ve seen an increase in the following use case while working on performance and schema review engagements:

I need to store exponentially increasing amounts of data and analyze all of it in real-time.

This is also known simply as: “We have big data.” Typically, this data is used for user interaction analysis, ad tracking, or other common click stream applications. However, it can also be seen in threat assessment (ddos mitigation, etc), financial forecasting, and other applications as well. While MySQL (and other OLTP systems) can handle this to a degree, it is by no means a forte. Some of the pain points include:

  • Cost of rapidly increasing, expensive disk storage (OLTP disks need to be fast == $$)
  • Performance decrease as the data size increases
  • Wasted hardware resources (excess I/O, etc)
  • Impact against other time-sensitive transactions (i.e. OLTP workload)

While there are many approaches to this problem – and often times, the solution is actually a hybrid of many individually tailored components – a solution that I have seen more frequently in recent work is HP Vertica.

At the 30,000 foot overview, Vertica is built around the following principles:

  • Columnar data store
  • Highly compressed data
  • Clustered solution for both availability and scalability

Over the next few weeks, I’ll discuss several aspects of Vertica including:

  • Underlying architecture and concepts
  • Basic installation and use
  • Different data loading techniques
  • Some various maintenance/operational procedures
  • Some comparisons vs. traditional OLTP (MySQL) performance
  • Some potential use-cases
  • Integration with other tools (such as Hadoop)

While Vertica is by no means the silver bullet that will solve all of your needs, it may prove to be a very valuable tool in your overall approach to managing big data.

The post Managing big data? Say ‘hello’ to HP Vertica appeared first on MySQL Performance Blog.

Link
Jean-Jerome SchmidtTweaking MySQL Galera Cluster to handle large databases - open_files_limit (18.9.2014, 11:42 UTC)
September 18, 2014
By Severalnines

Galera Cluster is a popular choice for achieving high availability using synchronous replication. Though if you are planning to run huge sites with many DB objects (tables), a few tweaks are necessary. 

 

Yes, you might have been successful in loading your 1000s of databases and 1000s of tables, but what happens if you have a node failure and Galera recovery fails?

 

In this blog post we will show you how to determine one common error related to the open_files_limit that MySQL imposes, and also to spot another potential pitfall.

 

Open_files_limit

 

If you are using wsrep_sst_method=xtrabackup or wsrep_sst_method=xtrabackup-v2 then you will find a log file in the data directory of the donor node. This log file is called innobackup.backup.log.

140912 19:10:15  innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
 
innobackupex: Using mysql server version 5.6.16
...
innobackupex: Created backup directory /tmp
...
140912 19:10:16  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/etc/mysql/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp --tmpdir=/tmp --extra-lsndir='/tmp' --stream=tar
innobackupex: Waiting for ibbackup (pid=4274) to suspend
innobackupex: Suspend file '/tmp/xtrabackup_suspended_2'
...
xtrabackup: open files limit requested 65535, set to 65535
...
>> log scanned up to (2145319596)
>> log scanned up to (2145319596)
2014-09-12 19:24:09 7f5d2080a740  InnoDB: Operating system error number 24 in a file operation.
InnoDB: Error number 24 means 'Too many open files'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
InnoDB: Error: could not open single-table tablespace file ./db_2760/t_0.ibd

 

In this case the DONOR failed because of the error:

2014-09-12 19:24:09 7f5d2080a740  InnoDB: Operating system error number 24 in a file operation

At this point you have to increase the open_files_limit in your my.cnf file. Unfortunately, open_files_limit is not a global variable, and you must restart the MySQL server in order for the change to be effective.

 

read more

Link
Peter ZaitsevSyncing MySQL slave table with pt-online-schema-change (17.9.2014, 14:06 UTC)

I recently encountered a situation in which after running Percona Toolkit’s pt-table-checksum on a customer system, 95% of the table on the MySQL master was different on the MySQL slave. Although this table was not a critical part of the infrastructure, from time to time, writes to the table from the master would break replication. Additionally, this table has about 6 million rows, and running pt-table-sync would take sometime. Rebuilding the slave from backup of the master would not be an easy option as well since the slave acts as an archive where it has a lot more data than the master.

So how did we solve it? With pt-online-schema-change and a NOOP ALTER.

pt-online-schema-change --alter 'ENGINE=INNODB' D=dbname,t=tblname

How is it possible? pt-online-schema-change works by creating a shadow copy of the original table and start copying the rows from the latter to the former. An additional set of TRIGGERs also ensures that any additional changes to existing rows after they have been copied to the shadow version will propagate.

There is little one caveat though, the 

binlog_format
  on the master would have to be ROW so the actual ROW images from the master would be copied to the slave. If your binlog_format is set to STATEMENT, you’d still end up with the same inconsistency. Since change statements will be logged as STATEMENT, the NOOP ALTER will operate on the slave table instead of copying from the master. You can configure the binlog_format for the alter process via the –set-vars option i.e. 
--set-vars 'binlog_format=ROW'
  to make it more easier!

The post Syncing MySQL slave table with pt-online-schema-change appeared first on MySQL Performance Blog.

Link
Jean-Jerome SchmidtClusterControl 1.2.8 Released (17.9.2014, 11:33 UTC)
September 17, 2014
By Severalnines

The Severalnines team is pleased to announce the release of ClusterControl 1.2.8. This release contains key new features along with performance improvements and bug fixes. We have outlined some of the key new features below. 

 

Highlights of ClusterControl 1.2.8 include:

  • YUM/APT repositories for ClusterControl
  • Deployment and scaling of single-node MySQL, MariaDB and MongoDB
  • Alerts and incident tracking with PagerDuty 
  • Unified Event Viewer
  • New flexible alarms/email notification system
  • Audit logging - Administrator activity tracking
  • Global MySQL User Management
  • New default dashboards for MySQL/MariaDB
  • Puppet Module for ClusterControl 
  • Improved LDAP support for Active Directory and FreeIPA

 

For additional details about the release:

 

YUM/APT repositories for ClusterControl: We are excited to announce the availability of YUM/APT repos for ClusterControl, making new releases of ClusterControl easily accessible using YUM or APT package managers. The repo is found at http://repo.severalnines.com. For step by step instructions on how to use the repo, see this blog post.

 

Deployment of single-node MySQL, MariaDB and MongoDB: It is now possible to deploy single database nodes from ClusterControl. Once a single node is deployed, it can then be managed from the ClusterControl interface. Single nodes can be scaled into clusters with a single click of a button. MySQL and MariaDB are turned into Galera Clusters, and MongoDB into a ReplicaSet. So you can now start small, and easily add scalability/HA when the business or your development/test environment requires it.

 

ClusterControl integration with PagerDuty - Alert and Incident Tracking: PagerDuty is an operations performance management platform used by thousands of Ops teams. You can now integrate ClusterControl’s alerting with your PagerDuty account. By integrating with PagerDuty, you can be alerted when alarms are triggered within your ClusterControl environment. 

 

read more

Link
Peter ZaitsevPercona Live London: Top Ten reasons to attend Nov. 3-4 (16.9.2014, 07:00 UTC)

Percona Live London 2014Percona Live London 2014 is fast approaching – November is just around the corner. This year’s conference, November 3-4, will be even bigger and better than last year thanks to the participation of leading MySQL experts the world over (including you!).

The Percona Live London MySQL Conference is a great event for users of any level using any of the major MySQL branches: MySQL, MariaDB or Percona Server. And this year we once again host a star-studded group of keynote speakers from industry-leading companies in the MySQL space.

We’ll also be welcoming leading MySQL practitioners from across the industry (and from all corners of the world) who will speak on topics that matter to you now  – see the full conference schedule here:

Monday starts early with a full day of tutorials and a fun evening at the community dinner.  Attendees will be arriving in true London style on a double-decker bus! Tuesday morning will kick-off with a series of keynotes followed by interactive breakout sessions – wrapping things up at the end of the day with a fun post-conference reception (a great chance to make new friends and reconnect with old ones).

Here’s a sneak peek at some of the must-see events this year:

To recap, here are the Top Ten reasons to attend Percona Live London this November 3-4:

10. Advanced Rate Pricing ends October 5th
9. Hear about the hottest current topics and trends.
8. Network! Meet face-to-face in the “hallway track” and make lasting connections.
7. Learn how to make MySQL work better for you – regardless of your expertise.
6. Have a blast at the community dinner!
5. Discuss your unique challenges with experts and discover options for solving them.
4. Engage with the sponsors at their tabletop exhibits.
3. Listen to top industry leaders describe the future of the MySQL ecosystem
2. Learn what works, and what doesn’t, from leading companies using MySQL
1. And the Number 1 reason to attend Percona Live London 2014: ALL of the above!

I look forward to seeing you in London this November and don’t forgot that Advanced Rate Pricing pricing ends October 5 so be sure to register now!

The post Percona Live London: Top Ten reasons to attend Nov. 3-4 appeared first on MySQL Performance Blog.

Link
Henrik IngoSelling Open Source 101: Why it makes a difference to understand what you're doing (13.9.2014, 19:36 UTC)

So, yesterday I wrote about what the sales funnel looks like when selling open source software, compared to what it used to look like when we sold closed source software. In this post I will build on that theory with some practical conclusions. (I assume you've read the first post.)

Why modeling your business matters

When running a business we need to do budgeting and other planning related activities. If you don't, you'll probably run out of money at some point. Also the point of planning is to capture as much of the business potential out there as possible. For example, to sell 5MEUR next year, do we need 5 sales managers or 6? (...and, can we afford 6?)

read more

Link
Peter ZaitsevOpenStack Live 2015: Call for speakers open through November 9 (12.9.2014, 17:32 UTC)
OpenStack Live 2015: Call for speakers open through November 9

OpenStack Live 2015: Call for speakers open through Nov. 9

I am proud to announce OpenStack Live, a new annual conference that will run in parallel with the Percona Live MySQL Conference & Expo at the Santa Clara Convention Center in Silicon Valley. The inaugural event, OpenStack Live 2015, is April 13-14, 2015. We are lining up a strong Conference Committee and are now accepting tutorial and breakout session speaking proposals through November 9.

OpenStack Live will emphasize the essential elements of making OpenStack work better with emphasis on the critical role of MySQL and the value of Trove. You’ll hear about the hottest current topics, learn about operating a high-performing OpenStack deployment, and listen to top industry leaders describe the future of the OpenStack ecosystem. We are seeking speaking proposals on the following topics:

  • Performance Optimization of OpenStack
  • OpenStack Operations
  • OpenStack Trove
  • Replication and Backup for OpenStack
  • High Availability for OpenStack
  • OpenStack User Stories
  • Monitoring and Tools for OpenStack

The conference features a full day of keynotes, breakout sessions, and Birds of a Feather sessions on April 14 preceded by an optional day of tutorials on April 13. A Monday reception will be held on the exhibit floor and joint lunches with both conferences offer you the opportunity to network with both the OpenStack and MySQL communities from both conferences. The OpenStack Live conference is a great event for users of any level.

As a bonus, OpenStack Live attendees may attend any Percona Live MySQL Conference session during the days of the OpenStack event. Conference only passes are available for April 14 and conference and tutorial passes are available for both April 13 and 14.

If you are using OpenStack and have a story to share – or a skill to teach – then now is the time to put pen to paper (or fingers to keyboard) and write your speaking proposal for either breakout or tutorial sessions (or both). Submissions will be reviewed by the OpenStack Live Conference Committee, which includes:

  • Mark Atwood: Director – Open Source Evangelism for HP Cloud Services
  • Rich Bowen: OpenStack Community Liaison at Red Hat
  • Jason Rouault: Senior Director OpenStack Cloud at Time Warner Cable
  • Peter Boros: Principal Architect at Percona

Presenting at OpenStack Live 2015 is your chance to put your ideas, case studies, best practices and technical knowledge in front of an intelligent, engaged audience of OpenStack users. If selected as a speaker by our Conference Committee, you will receive a complimentary full conference pass.

Public speaking not your thing or just want to learn about the latest and greatest OpenStack technologies, deployments and projects? Then register now and save big with our early bird discount. OpenStack Live 2015 is an ideal opportunity for organizations to connect with the community of OpenStack enthusiasts from Silicon Valley and around the world. The Percona Live MySQL Conference this past April had over 1,100 registered attendees from 40 countries and the OpenStack Open Source Appreciation Day on the Monday before the conference was fully booked so don’t delay, register today to save your seat!

We are currently accepting sponsors. You can learn more about sponsorship opportunities here.

I hope to see you at OpenStack Live 2015 next April! And speakers, remember the deadline to submit your proposals is November 9. In the meantime you can learn more by visiting the official OpenStack Live 2015 website.

The post OpenStack Live 2015: Call for speakers open through November 9 appeared first on MySQL Performance Blog.

Link
Peter ZaitsevPercona XtraBackup 2.2.4 is now available (12.9.2014, 13:47 UTC)

Percona XtraBackup for MySQL Percona is glad to announce the release of Percona XtraBackup 2.2.4 on September 12th 2014. Downloads are available from our download site here and Percona Software Repositories.

Percona XtraBackup enables backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backup.

New Features:

  • Percona XtraBackup has implemented support for Galera GTID auto-recovery. Percona XtraBackup retrieves the GTID information, after backing up a server with backup locks support, from the InnoDB trx header on recovery and creates the xtrabackup_galera_info during that stage.

Bugs Fixed:

  • Percona XtraBackup is now built with system zlib library instead of the older bundled one. Bug fixed #1108016.
  • apt-get source was downloading older version of Percona XtraBackup. Bug fixed #1363259.
  • innobackupex would ignore the innobackupex --databases without innobackupex --stream option and back up all the databases. Bug fixed #569387.
  • rsync package wasn’t a dependency although it is required for the innobackupex --rsync option. Bug fixed #1259436.
  • innobackupex --galera-info was checking only for non-capitalized wsrep_* status variables which was incompatible with MariaDB Galera Cluster 10.0. Bug fixed #1306875.
  • Percona XtraBackup would crash trying to remove absent table from InnoDB data dictionary while preparing a partial backup. Bug fixed #1340717.
  • Percona XtraBackup now supports MariaDB GTID. Bugs fixed #1329539 and #1326967 (Nirbhay Choubey).
  • MariaDB 10.1 is now added to the list of supported servers. Bug fixed #1364398.
  • Percona XtraBackup would fail to restore (copy-back) tables that have partitions with their own tablespace location. Bug fixed #1322658.

Other bugs fixed: #1333570, #1326224, and #1181171.

Release notes with all the bugfixes for Percona XtraBackup 2.2.4 are available in our online documentation. Bugs can be reported on the launchpad bug tracker

The post Percona XtraBackup 2.2.4 is now available appeared first on MySQL Performance Blog.

Link
LinksRSS 0.92   RDF 1.
Atom Feed