segunda-feira, 1 de setembro de 2014

Percona XtraDB Cluster 5.6.20-25.7 is now available - Peter Zaitsev

http://ift.tt/1nnajaI

Percona XtraDB Cluster 5.6.20-25.7Percona is glad to announce the new release of Percona XtraDB Cluster 5.6 on September 1st 2014. Binaries are available from downloads area or from our software repositories.

Based on Percona Server 5.6.20-68.0 including all the bug fixes in it, Galera Replicator 3.7, and on Codership wsrep API 25.7, Percona XtraDB Cluster 5.6.20-25.7 is now the current General Availability release. All of Percona‘s software is open-source and free, and all the details of the release can be found in the 5.6.20-25.7 milestone at Launchpad.

New Features:

  • New session variable wsrep_sync_wait has been implemented to control causality check. The old session variable wsrep_causal_reads is deprecated but is kept for backward compatibility (#1277053).
  • systemd integration with RHEL/CentOS 7 is now available for Percona XtraDB Cluster from our testing repository (#1342223).

Bugs Fixed:

  • Running START TRANSACTION WITH CONSISTENT SNAPSHOT, mysqldump with --single-transaction or mydumper with disabled binlog would lead to a server crash. Bug fixed #1353644.
  • percona-xtradb-cluster-garbd-3.x package was installed incorrectly on Debian/Ubuntu. Bug fixed #1360633.
  • Fixed netcat in SST script for CentOS 7 nmap-ncat. Bug fixed #1359767.
  • TO isolation was run even when wsrep plugin was not loaded. Bug fixed #1358681.
  • The error from net read was not handled in native MySQL mode. This would cause duplicate key error if there was unfinished transaction at the time of shutdown, because it would be committed during the startup recovery. Bug fixed #1358264.
  • The netcat in garbd init script has been replaced with nmap for compatibility in CentOS 7. Bug fixed #1349384.
  • SHOW STATUS was generating debug output in the error log. Bug fixed #1347818.
  • Incorrect source string length could lead to server crash. This fix allows maximum of 3500 bytes of key material to be populated, longer keys will be truncated. Bug fixed #1347768.
  • wsrep consistency check is now enabled for REPLACE ... SELECT as well. This was implemented because pt-table-checksum uses REPLACE .. SELECT during checksumming. Bug fixed #1343209.
  • Client connections were closed unconditionally before generating SST request. Fixed by avoiding closing connections when wsrep is initialized before storage engines. Bug fixed #1258658.
  • Session-level bin

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



from Planet MariaDB http://ift.tt/1lvLCrP

New Book "MariaDB - Beginners Guide"

New book "MariaDB - Beginners Guide" http://ift.tt/1l8na5e

Galera replication – how to recover a PXC cluster - Peter Zaitsev

http://ift.tt/1qi9THr

Galera replication for MySQL brings not only the new, great features to our ecosystem, but also introduces completely new maintenance techniques. Are you concerned about adding such new complexity to your MySQL environment? Perhaps that concern is unnecessarily.

I am going to present here some simple tips that hopefully will let fresh Galera users prevent headaches when there is the need to recover part or a whole cluster in certain situations. I used Percona XtraDB Cluster (project based on Percona Server and Galera library + MySQL extensions from Codership) to prepare this post, but most if not all of the scenarios should also apply to any solution based on MySQL+Galera tandem you actually chose, whether these are binaries from Codership, MariaDB Galera Cluster or maybe your own builds.

Unlike standard MySQL replication, a PXC cluster acts like one logical entity, which takes care about each node status and consistency as well as cluster status as a whole. This allows to maintain much better data integrity then you may expect from traditional asynchronous replication while allowing safe writes on multiple nodes in the same time. This is though for the price of more possible scenarios where database service will be stopped with no node being able to serve requests.

Lets assume the simplest case cluster of nodes A, B and C and few possible scenarios where some or all nodes are out of service. What may happen and what we have to do, to bring them (or whole cluster) back up.

Scenario 1

g1Node A is gracefully stopped. Likely for the purpose of maintenance, configuration change, etc.
In this case the other nodes receive “good bye” message from that node, hence the cluster size is reduced and some properties like quorum calculation or auto increment are automatically changed. Once we start the A node again, it will join the cluster based on it’s wsrep_cluster_address setting in my.cnf. This process is much different from normal replication – the joiner node won’t serve any requests until it is again fully synchronized with the cluster, so connecting to it’s peers isn’t enough, state transfer must succeed first. If the writeset cache (gcache.size), on nodes B and/or C has still all the transactions there were executed during the time this node was down, joining will be possible via (usually fast and light) IST. Otherwise, full SST will be needed, which in fact is full binary data snapshot copy. Hence it may be important here to determine the best donor, as shown in this article. If IST is impossible due to missing transactions in donor’s gcache, the fallback decision is made by the donor and SST is started automatically instead.

Scenario 2

g2Nodes A and B are gracefully stopped. Similar to previous case, cluster size is reduced to 1, hence even the single remaining node C forms a primary component and is serving client requests. To get the nodes back into the cluster, you just need to start them. However, the node C will be switched to “Donor/Desynced” state as it will have to provide state transfer to at least first joining node. It is still possible to read/write to it during that process, but it may be much slower, depending how large state transfers it needs to send. Also some load balancers may consider the donor node as not operational and remove it from the pool. So it is best to avoid situation when only one node is up.

Note though, if you restart A and then B in that order, you ma

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



from Planet MariaDB http://ift.tt/1lvLCrP

How to Manage All Your MySQL or MariaDB Databases - Jean-Jerome Schmidt

http://ift.tt/1pioHRa

September 1, 2014
By Severalnines

According to Forrester, a DBA in a large enterprise manages between 8 and 275 databases, with the industry average being 40 databases to a DBA. Larger databases usually require extra effort around tuning, backup, recovery and upgrade. Cloud, as well as automation and management tools can help improve the number of databases managed by one DBA.  

 

With that background, we were pretty excited to introduce support for management of single-instance MySQL and MariaDB databases in ClusterControl 1.2.6. The majority, if not all of the cluster users out there, use single-instance or master-slave replicated setups along their mission-critical clusters. So today, it is possible to manage an entire MySQL/MariaDB environment from one interface.

 

Ok, so what about Nagios or Zabbix? Unless Nagios is deploying your instances, scaling them, recovering them if they fail, taking backups, upgrading them to newer versions, telling you about slow running queries or suboptimal configuration parameters, we’d argue that you probably ought to look into a management tool. 

 

In today’s blog post, we’ll show you how you set that up.

 

Our setup consists of MySQL servers running different versions (5.1, 5.5 and 5.6) and on different hosts:

 

Installing ClusterControl

 

Deploy ClusterControl on a dedicated host (192.168.197.100) using these simple steps:

  $ wget http://www.severalnines.com/downloads/cmon/install-cc.sh  $ chmod u+x install-cc.sh  $ sudo ./install-cc.sh  

 

This will automate the installation of ClusterControl, but you can read more about this in the ClusterControl Quick Start Guide.

 

Once the installation is complete, login to the ClusterControl UI at http://ift.tt/1pioHR8 using your email address (that you entered during the installation process) and default password ‘admin’. You should see something like below:

 

Preparing the Database Hosts

 

You do not need to install any agent on the database hosts, but ClusterControl needs to be able to SSH into them and connect to the database instance to gather statistics. 

 

read more



from Planet MariaDB http://ift.tt/1lvLCrP

domingo, 31 de agosto de 2014

New Book "MariaDB - Beginners Guide"

New book "MariaDB - Beginners Guide" http://ift.tt/1l8na5e

Putting MySQL Cluster in a container - Daniël van Eeden

http://ift.tt/1lpF2by

To get more familiar with docker and to create a test setup for MySQL Cluster I created docker images for the various components of MySQL Cluster (a.k.a. NDB Cluster)

At first I created a Fedora 20 container and ran all components in one container. That worked and is quite easy to setup. But that's not how one is supposed to use docker.

So I created Dockerfile's for all components and one base image.

The base image:
  • contains the MySQL Cluster software
  • has libaio installed
  • has a mysql user and group 
  • serves as a base for the other images
The management node (ndb_mgmd) image:
  • Has ndb_mgmd as entrypoint
  • Has a config.ini for the cluster config
  • Should be started with "--name=mymgm01"
The data node (ndbmtd) image:
  • Has ndbmtd as entrypoint
  • Uses the connect string: "host=${MGM01_PORT_1186_TCP_ADDR}:1186"
  • Should be started with "--link mymgm01:mgm01" to allow it to connect to the management node.
  • You should create 2 containers of this type to create a nodegroup of 2 nodes.
The API node (mysqld) image:
  • has a my.cnf
  • Runs mysqld_safe
  • Should be started with "--link mymgm01:mgm01" to allow it to connect to the management node.
  • The ndb-connectstring is given as parameter to mysqld_safe as it comes from an environment variable. It's not possible to use environment variables from within my.cnf. Docker is supposed to also update /etc/hosts but that didn't work for me.
  • You should expose port 3306 for your application
The management client (ndb_mgm) image:
  • Runs ndb_mgm as entrypoint
  • Should be started with "--link mymgm01:mgm01" to allow it to connect to the management node.
  • Running the ndb_mgm in a container removes the need to publish port 1186 on the management server. More info here.
  • You can override the entrypoint to run other NDB utilities like ndb_desc or ndb_select_all
The images can be found on http://ift.tt/1tlsWD4
The Dockerfiles can be found on http://ift.tt/1u1AVD5  

Possible improvements
  • Use hostnames in the config.ini instead of IPv4 addresses. This makes it more dynamic. But that means updating /etc/hosts or fideling with DNS.
  • Using VOLUMES in the Dockerfiles to make working with data easier.


from Planet MariaDB http://ift.tt/1lvLCrP

sexta-feira, 29 de agosto de 2014

Galera data on Percona Cloud Tools (and other MySQL monitoring tools) - Peter Zaitsev

http://ift.tt/1n5DCOZ

I was talking with a Percona Support customer earlier this week who was looking for Galera data on Percona Cloud Tools. (Percona Cloud Tools, now in free beta, is a hosted service providing access to query performance insights for all MySQL uses.)

The customer mentioned they were already keeping track of some Galera stats on Cacti, and given they were inclined to use Percona Cloud Tools more and more, they wanted to know if it was already supporting Percona XtraDB Cluster. My answer was: “No, not yet: you can install agents in each node (the regular way in the first node, then manually on the other nodes… and when prompted say “No” to create MySQL user and provide the one you’re using already) and monitor them as autonomous MySQL servers – but the concept of cluster and specially the “Galera bits” has yet to be implemented there.

Except I was wrong.

By “concept of cluster” I mean supporting the notion of group instances, which should allow a single cluster-wide view for metrics and query reports, such as the slow queries (which are recorded locally on the node where the query was run and thus observed in a detached way). This still needs to be implemented indeed, but it’s on the roadmap.

The “Galera bits” I mentioned above are the various “wsrep_” status variables. In fact, those refer to the Write Set REPlication patches (based in the wsrep API), a set of hooks applied to the InnoDB/XtraDB storage engine and other components of MySQL that modifies the way replication works (to put it in a very simplified way), which in turn are used by the Galera library to provide a “generic Synchronous Multi-Master replication plugin for transactional applications.” A patched version of Percona Server together with the Galera libray compose the base of Percona XtraDB Cluster.

As I found out only now, Percona Cloud Tools does collect data from the various wsrep_ variables and it is available for use – it’s just not shown by default. A user only needs to add a dashboard/chart manually on PCT to see these metrics:

adding_wsrep_chart

Click on the picture to enlarge it

Now, I need to call that customer …

Monitoring the cluster

Since I’m touching this topic I thought it would be useful to include some additional information on monitoring a Galera (Percona XtraDB Cluster in particular) cluster, even though most of what I mention below has already been published in different posts here on the MySQL Performance Blog. There’s a succint documentation page bearing the same title of this section that indicates the main wsrep_ variables you should monitor to check the health status of the cluster and how well it’s coping with load along the time (performance). Remember you can get a grasp of the full set of variables at any time by issuing the following command from one (or each one) of the nodes:

  mysql> SHOW GLOBAL STATUS LIKE "wsrep_%";  

And for a broader and real time view of the wsrep_ status variables you can use Jay Janssen’s myq_gadgets toolkit, which he

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



from Planet MariaDB http://ift.tt/1lvLCrP

Percona Server 5.6.20-68.0 is now available - Peter Zaitsev

http://ift.tt/1qLT9Iv

Percona ServerPercona is glad to announce the release of Percona Server 5.6.20-68.0 on August 29, 2014. Download the latest version from the Percona web site or from the Percona Software Repositories.

Based on MySQL 5.6.20, including all the bug fixes in it, Percona Server 5.6.20-68.0 is the current GA release in the Percona Server 5.6 series. All of Percona’s software is open-source and free. Complete details of this release can be found in the 5.6.20-68.0 milestone on Launchpad.

New Features:

  • Percona Server has implemented the MySQL 5.7 SHOW SLAVE STATUS NONBLOCKING syntax for Lock-Free SHOW SLAVE STATUS feature. The existing SHOW SLAVE STATUS NOLOCK is kept as a deprecated alias and will be removed in Percona Server 5.7. There were no functional changes for the feature.
  • Percona Server Audit Log Plugin now supports JSON and CSV formats. The format choice is controlled by audit_log_format variable.
  • Percona Server Audit Log Plugin now supports streaming the audit log to syslog.
  • TokuDB storage engine package has been updated to version 7.1.8.

Bugs Fixed:

  • Querying INNODB_CHANGED_PAGES table with a range condition START_LSN > x AND END_LSN #1202252 (Jan Lindström and Sergei Petrunia).
  • SQL statements of other connections were missing in the output of SHOW ENGINE INNODB STATUS, in LATEST DETECTED DEADLOCK and TRANSACTIONS sections. This bug was introduced by Statement Timeout patch in Percona Server 5.6.13-61.0. Bug fixed #1328824.
  • Some of TokuDB distribution files were missing in the TokuDB binary tarball. Bug fixed #1338945.
  • With XtraDB changed page tracking feature enabled, queries from the INNODB_CHANGED_PAGES could read the bitmap data whose write was in still progress. This would cause the query to fail with an ER_CANT_FIND_SYSTEM_REC and a warning printed to the server error log. The workaround has been to add an appropriate END_LSN-limiting condition to the query. Bug fixed #1193332.
  • mysqld-debug was missing from Debian packages. This regression was introduced in Percona Server

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



from Planet MariaDB http://ift.tt/1lvLCrP

Percona Server 5.5.39-36.0 is now available - Peter Zaitsev

http://ift.tt/1n3zlf8

Percona ServerPercona is glad to announce the release of Percona Server 5.5.39-36.0 on August 29, 2014 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.39, including all the bug fixes in it, Percona Server 5.5.39-36.0 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.5.39-36.0 milestone at Launchpad.

New Features:

Bugs Fixed:

  • Querying INNODB_CHANGED_PAGES with a range condition START_LSN > x AND END_LSN #1202252 (Jan Lindström and Sergei Petrunia).
  • With XtraDB changed page tracking feature enabled, queries from the INNODB_CHANGED_PAGES could read the bitmap data whose write was in still progress. This would cause the query to fail with an ER_CANT_FIND_SYSTEM_REC and a warning printed to the server error log. The workaround has been to add an appropriate END_LSN-limiting condition to the query. Bug fixed #1346122.
  • mysqld-debug was missing from Debian packages. This regression was introduced in Percona Server 5.5.36-34.0. Bug fixed #1290087.
  • Fixed a memory leak in Slow Query Log Rotation and Expiration. Bug fixed #1314138.
  • The audit log plugin would write log with XML syntax errors when OLD and NEW formats were used. Bug fixed #1320879.
  • A server built with system OpenSSL support, such as the distributed Percona Server binaries, had SSL-related memory leaks. Bug fixed #1334743 (upstream #73126).
  • If the bitmap directory has a bitmap file sequence with a start LSN of one file less than a start LSN of the previous file, a debug build would assert when queries were run on INNODB_CHANGED_PAGES table. Bug fixed #1342494.

Other bugs fixed:

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



from Planet MariaDB http://ift.tt/1lvLCrP