Jean-Jerome SchmidtClusterControl 1.2.9 Released (12.3.2015, 12:43 UTC)

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

Join us for our Release Webinar on March 24th, which includes a live demo of the new ClusterControl 1.2.9.

Highlights of ClusterControl 1.2.9 include:

  • Support for PostgreSQL Servers
  • Advanced HAProxy Configurations and Built-in Stats
  • Hybrid Replication with Galera Clusters
  • Galera Replication Traffic Encryption
  • Encrypted Communication between ClusterControl and MySQL-based systems
  • Query Deadlock Detection in MySQL-based systems
  • Bootstrap Galera Cluster
  • Restore of Backups
  • New UI theme 
  • RPC interface to ClusterControl 
  • Chef Recipe and Puppet Manifest for ClusterControl
  • Zabbix Plugin for ClusterControl

 

For additional details about the release:

 

Support for PostgreSQL Servers: We are excited to announce support for PostgreSQL, probably the world’s most advanced open source RDBMS. Companies using Postgres can today install ClusterControl to have it monitor database metrics, queries and schedule backups. For more details, please see this blog post. With this new addition, ClusterControl now supports MySQL with its main variations MariaDB, Percona XtraDB and Galera Cluster, PostgreSQL and MongoDB.

Advanced HAProxy configuration and built-in stats: When deploying HAProxy load balancers in front of your database nodes, it is now possible to configure advanced settings such as connection timeouts, max number of frontend/backend connections, which backend servers (DB nodes) to include and whether they are deployed as an active or backup instance.

It is no longer needed to launch a separate window into HAProxy’s webadmin application to monitor the HAProxy performance.stats are now displayed directly within ClusterControl. 

Database nodes can now also be disabled from the load balancing set directly from ClusterControl, e.g. if you would like to take a database node offline for maintenance.

Hybrid Replication for Galera Clusters: Combining Galera and asynchronous MySQL replication in the same setup, aka Hybrid Replication, has some useful use cases. An asynchronous slave to a Galera cluster can serve as a remote live backup, or as a loosely coupled reporting server that does not impact Galera performance. 
In one click, it is now

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

Link
Peter ZaitsevEasy query metrics with MySQL Performance Schema (12.3.2015, 07:00 UTC)

The MySQL Performance Schema exposes so much data that it’s not trivial to learn, configure, and use. With recently released Percona Agent 1.0.11 you can get query metrics – like min, max, and average query execution time – with a few clicks:

Configure PCT Query Analytics for Performance Schema

Click “Apply” and about two minutes later you’ll have query metrics from Performance Schema, collected and sent every minute.

Percona Cloud Tools (PCT) and Percona Agent handle all the details. You’ll need MySQL (or Percona Server) 5.6 and Percona Agent 1.0.11 or newer. One caveat at the moment: it only works for local MySQL instances (so not Amazon RDS). This limitation should be fixed soon; we’re already working on it.

Why use Performance Schema? We prefer Query Analytics with MySQL slow logs (especially Percona Server slow logs which expose more metrics) because slow logs expose the most metrics compared to other sources, but sometimes the slow log just isn’t an option, so Performance Schema is the next best choice, but the choice means tradeoffs. For example, Performance Schema does not expose actual query examples (just fingerprints), so EXPLAIN does not work.

For those who’ve been following PCT development, you know that Percona Agent 1.0.5 first introduced support for Performance Schema. What’s new in 1.0.11 is everything – we completely rewrote this part of the agent. It’s so much better that it’s now the required minimum version for using Query Analytics with Performance Schema. Upgrading is really easy: just run the single command line you used to install the agent and it will auto-update.

MySQL Performance Schema exposes a lot of data and insights into the server, but query metrics are perhaps the most important because the primary job of your MySQL database is to execute queries. That’s why Percona Cloud Tools makes Query Analytics with Performance Schema (and slow logs) so easy: to help you focus on the essential and leave the details to the tools.

Percona Cloud Tools is in beta, so it’s still free to sign up and free to use all the tools and features.

The post Easy query metrics with MySQL Performance Schema appeared first on MySQL Performance Blog.

Link
Jean-Jerome SchmidtMonitoring Galera Cluster for MySQL or MariaDB - Understanding metrics and their meaning (11.3.2015, 08:54 UTC)

To operate any database efficiently, you need to have insight into database performance. This might not be obvious when everything is going well, but as soon as something goes wrong, access to information can be instrumental in quickly and correctly diagnosing the problem.

All databases make some of their internal status data available to users. In MySQL, you can get this data mostly by running 'SHOW STATUS' and 'SHOW GLOBAL STATUS', by executing 'SHOW ENGINE INNODB STATUS', checking information_schema tables and, in newer versions, by querying performance_schema tables.

These methods are far from convenient in day-to-day operations, hence the popularity of different monitoring and trending solutions. Tools like Nagios/Icinga are designed to watch hosts/services, and alert when a service falls outside an acceptable range. Other tools such as Cacti and Munin provide a graphical look at host/service information, and give historical context to performance and usage. ClusterControl combines these two types of monitoring, so we’ll have a look at the information it presents, and how we should interpret it.

If you’re using Galera Cluster (Codership or MariaDB Cluster or Percona XtraDB Cluster), you may have noticed the following section in ClusterControl’s "Overview" tab:

Let’s see, step by step, what kind of data we have here.

The first column contains the list of nodes with their IP addresses - there’s not much else to say about it. 

Second column is more interesting - it describes node status (wsrep_local_state_comment status variable). A node can be in different states: 

  • "Initialized" means the node is up and running, but it’s not a part of a cluster. It can be caused, for example, by network issues; 
  • "Joining" means the node is in the process of joining the cluster and it’s either receiving or requesting a state transfer from one of other nodes;
  • "Donor/Desynced" means the node serves as a donor to some other node which is joining the cluster; 
  • "Joined" describes a node which joined the cluster but its busy catching up on committed write sets; 
  • Finally, "Synced" means the node is working normally.

Next column, "Cluster Status", describes a status of the cluster. It can have three distinct states: 

  • "Primary" means the communication between nodes is working and quorum is present (majority of nodes is available)
  • "Non-Primary" means the node was a part of the cluster but, for some reason, it lost contact with the rest of the cluster. As a result, this node is considered inactive and it won’t accept queries
  • Last possible status is "Disconnected" - the node could not establish group communication.

"WSREP Cluster Size" tells us about a cluster size as the node sees it. Non-Primary components create a cluster with size of 1.

Let’s take a look at the screenshot above, and see what it is telling us about Galera. We can see three nodes. Two of them (10.0.0.201 and 10.0.0.203) are perfectly fine, they are both "Synced" and the cluster is in "Primary" state. The cluster currently consists of two nodes. Node 10.0.0.202 is "Initialized" and it forms "non-Primary" component. It means that this node lost connection with the cluster - most likely some kind of network issues (in fact, we used iptables to block a traffic to this node from both 10.0.0.201 and 10.0.0.203).

At this moment we have to stop a bit and describe how Galera Cluster works internally. We’ll not go into too much details as it is not within a scope of this blog post but some knowledge is required to understand the importance of the data presented in next columns.

Galera is a "virtually" synchronous, multi-master cluster. It mean

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

Link
Peter ZaitsevAdvanced JSON for MySQL (10.3.2015, 10:00 UTC)

What is JSON

JSON is an text based, human readable format for transmitting data between systems, for serializing objects and for storing document store data for documents that have different attributes/schema for each document. Popular document store databases use JSON (and the related BSON) for storing and transmitting data.

Problems with JSON in MySQL

It is difficult to inter-operate between MySQL and MongoDB (or other document databases) because JSON has traditionally been very difficult to work with. Up until recently, JSON is just a TEXT document. I said up until recently, so what has changed? The biggest thing is that there are new JSON UDF by Sveta Smirnova, which are part of the MySQL 5.7 Labs releases. Currently the JSON UDF are up to version 0.0.4. While these new UDF are a welcome edition to the MySQL database, they don’t solve the really tough JSON problems we face.

Searching

The JSON UDF provide a number of functions that make working with JSON easier, including the ability to extract portions of a document, or search a document for a particular key. That being said, you can’t use JSON_EXTRACT() or JSON_SEARCH in the WHERE clause, because it will initiate a dreaded full-table-scan (what MongoDB would call a full collection scan). This is a big problem and common wisdom is that JSON can’t be indexed for efficient WHERE clauses, especially sub-documents like arrays or objects within the JSON.

Actually, however, I’ve come up with a technique to effectively index JSON data in MySQL (to any depth). The key lies in transforming the JSON from a format that is not easily indexed into one that is easily indexed. Now, when you think index you think B-TREE or HASH indexes (or bitmap indexes) but MySQL also supports FULLTEXT indexes.

A fulltext index is an inverted index where words (tokens) point to documents. While text indexes are great, they aren’t normally usable for JSON. The reason is, MySQL splits words on whitespace and non-alphanumeric characters. A JSON document doesn’t end up being usable when the name of the field (the key) can’t be associated with the value. But what if we transform the JSON? You can “flatten” the JSON down into key/value pairs and use a text index to associate the key/value pairs with the document. I created a UDF called RAPID_FLATTEN_JSON using the C++ Rapid JSON library. The UDF flattens JSON documents down into key/value pairs for the specific purpose of indexing.

Here is an example JSON document:

{
        "id": "0001",
        "type": "donut",
        "name": "Cake",
        "ppu": 0.55,
        "batters":
                {
                        "batter":
                                [
                                        { "id": "1001", "type": "Regular" },
                                        { "id": "1002", "type": "Chocolate" },
                                        { "id": "1003", "type": "Blueberry" },
                                        { "id": "1004", "type": "Devil's Food" }
                                ]
                },
        "topping":
                [
                        { "id": "5001", "type": "None" },
                        { "id": "5002", "type": "Glazed" },
                        { "id": "5005", "type": "Sugar" },
                        { "id": "5007", "type": "Powdered Sugar" },
                        { "id": "5006", "type": "Chocolate with Sprinkles" },
                        { "id": "5003", "type": "Chocolate" },
                        { "id": "5004", "type": "Maple" }
                ]
}

Flattened:

mysql> select RAPID_FLATTEN_JSON(load_file('/tmp/doc.json'))G
*************************** 1. row ***************************
RAPID_FLATTEN_JSON(load_file('/tmp/doc.json')): id=0001
type=donut
name=Cake
ppu=0.55
id=1001
type=Regular
id=1002
type=Chocolate
id=1003
type=Blueberry
id=1004
type=Devil's Food
type=Devil's
type=Food
id=5001
type=None
id=5002
type=Glazed
id=5005
type=Sugar
id=5007
type=Powdered Sugar
type=Powdered
type=Sugar
id=5006
type=Chocolate with Sprinkles
type=Chocolate
type=with
type=Sprinkles
id=5003
type=Chocolate
id=5004
type=Maple
1 row in set (0.00 sec)

Obviously this is useful, because our keys are now attached to our values in an easily searchable way. All you need to do is store the flattened version of the JSON in another field (or another table), and index it with a FULLTEXT index to make it searchable. But wait, there is one more big problem: MySQL will split words on the equal sign. We don’t want this as it removes the locality of the keyword and the value. To fix this problem you’ll have to undertake the (actually quite easy) step of adding a new collation to MySQL (I called mine ft_kvpair_ci). I added equal (=) to the list of lower case characters as described in the manual. You just have to change two text files, no need to recompile the server or anything, and as I said, it is pretty easy. Let me know if you get stuck on this step and I

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

Link
Jean-Jerome SchmidtHow to migrate ClusterControl to a new server (10.3.2015, 02:24 UTC)

As your needs change, and you start managing more database instances or larger centralized backups with ClusterControl, you might find that your controller host is over-utilized. Or you might need to migrate your infrastructure to the cloud. If this is the case, you can migrate to another instance.

In this blog post, we’ll show you how to migrate ClusterControl to another server. Here is our architecture diagram:

We would like to migrate an existing ClusterControl instance to another server with bigger disk space and RAM. The ClusterControl node currently monitors a two-node Galera cluster, stores centralized database backups under /root/backups directory, runs garbd (Galera arbitrator daemon) as well as an HAProxy instance to load balance connections to the Galera nodes.

ClusterControl Migration

Migrating your ClusterControl instance should not have any detrimental effect on your database cluster. Note the list of things that need to be transferred to the new server:

  • Databases with monitoring data and cluster information - cmon and dcps
  • ClusterControl API token
  • MySQL grants for user cmon
  • ClusterControl files - ClusterControl binaries, CMONAPI and ClusterControl UI
  • If you have garbd, HAproxy or Keepalived running on the ClusterControl node, you need to reinstall (explicitly remove and then install) them again in the new server by using Cluster Actions > Add Load Balancer.
  • If you store your backups on ClusterControl node, you have to manually transfer them to the exact location on the new ClusterControl server

ClusterControl requires the new server to run on the same operating system family (Redhat-based or Debian-based). Further tweaking on the MySQL server running on ClusterControl host can help to improve the overall performance of ClusterControl after migration is completed.

Install new ClusterControl and merge data from old server

This is the recommended way to migrate, as it installs the latest version of ClusterControl and loads your settings and historical data. The steps include:

  1. Install ClusterControl on the new server by using install-cc.sh script
  2. Export the cmon and dcps databases using mysqldump
  3. Copy the old CMON configuration files: /etc/cmon.cnf and /etc/cmon.d/* (if any)
  4. Replace any occurrences of old IP address in the new server
  5. Restore the cmon and dcps databases
  6. Transfer CMONAPI token value
  7. Setup passwordless SSH by copying the SSH key to all nodes
  8. Setup grant for cmon user on all MySQL nodes

Migration steps

1. On new server, install ClusterControl by using the installation script. This will install and configure all dependencies like MySQL, PHP and Apache web server:

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

** In the installation wizard, specify same password for MySQL root and cmon password with the old server.

2. Once the installation is done, stop the CMON service on the old server and the new server to get consistent data:

root@oldserver$ service cmon stop
root@newserver$ service cmon stop

3. On the existing server, take a MySQL dump of cmon and dcps databases from the current server with --replace, --no-create-info

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

Link
Peter Zaitsev5 free handy tools for monitoring and managing MySQL replication (9.3.2015, 15:50 UTC)

MySQL Replication is very simple to set up. In this post I’ll discuss its importance and five handy tools for monitoring and managing MySQL replication.

What is MySQL Replication? It’s the process of copying the (real-time events) data from one master instance to another slave instance and maintaining the redundant consistent data in a different machine. This enables a distributed database system that shares the same level of information.

In MySQL the replication works based on the three threads as shown below.

1) I/O thread on the slave server:  To start on receiving replication events, an I/O thread starts on the slave server and connects to the master server.

2) Master connection handler thread:  As a connection handier, master starts a thread whenever a replication slave connects to a master. The master server sends the events from its binary log file to the slave I/O thread, notifying slave about newly written events to its binary log. The slave I/O thread which records them to in the slave’s relay log file.

3) Slave SQL thread:  When it starts, immediately reads the events from the relay log and applies on the Slave DB. Once it finishes the processing of every relay log and if the I/O thread is writing the events to a new relay log file then it deletes the processed one. Suppose if the  I/O thread is writing  the events on a relay log and which is the same file SQL thread is reading then the SQL thread pauses until more events are available in the relay log.

MySQL replication (slave) redundant instances is an excellent method of improving data performance and stability. It ensures the availability of another copy of a database whenever there arises any issues with the master server. One of the other advantages is the report query (select) offloading to a slave server, which is a common practice to reduce the workload of the master DB server as there are multiple servers that are able to respond to the queries. The third advantage is to schedule the backup from the slave server, etc.

All the benefits discussed above are smart and effective only if replication is up and running and the data is in sync with the master.

Let us see the set of very useful tools from Percona Toolkit which help you in monitoring and managing the MySQL replication (Slaves).

5 free handy tools for monitoring and managing MySQL replication1) pt-heartbeat: Tool measures/monitor replication lag on a MySQL in real time. It is important to have a replication monitoring system to confirm that replication is up and running and lag is current.

In typical way of monitoring, we use “SHOW SLAVE STATUS” to find out the information like Slave_IO_Running: Yes, Slave_SQL_Running: Yes and Seconds_Behind_Master: 0 etc, but is not reliable as  Seconds_Behind_Master shows difference between last timestamp read in the binlogs and current time. Many reasons like slow network, long running queries, blocking operations or a 2nd level slaves(Master > slave1> slave2) etc. can produce an irrelevant value for the variable.

So I recommend to use pt-heartbeat, which depends only on the heartbeat record being replicated to slave instead of the above said unreliable method of finding the lag. pt-heartbeat will insert/update a row in the master and the time delay is calculated depending on when the data was inserted and when it became available to read in the slave. It works at any depth in the replication hierarchy. For example, it will reliably report how far a slave lags its original master (master’s master).

Example :

On Master:
[root@Tst1Master ~]#pt-heartbeat --daemonize -D test --update -h<IP address> --create-table
On Slave:
[root@Tst1Slave ~]#pt-heartbeat -D test --monitor --master-server-id 1
0.00s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]

We used to schedule the backup from the slave to avoid the additional load with the master server. In this case it is important to confirm the slave is current with the master to ascertain the backup is having the recent data. Here is a simple script you can use to verify the replication status on a periodical basis(cron) and to know the status just before the backup scheduled.

#!/bin/bash
#     <300 - [Good]
#     300> <600 - [Warning]
#     > 

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

Link
Erkan YanarGalera for Mesos (7.3.2015, 19:05 UTC)
This time it is nothing like a linkt to another Blog:  Galera on Mesos


Ok as a fact I was kinda involved. Even the work is done by Stefan all alone.
We meat for a day in a coworking space and discussed about Galera and Mesos.

In the end Stefan produced this incredible blogpost and pushed Mesos forward.

Whats the fun about this post?

We already now Galera is already the standard in a lot of architectures. For example OpenStack.
Doing consultant work for Docker also I encourage to use Galera for all this infrastructures Docker runs on. 

Mesos is about to run easy on 1000 nodes. It has a nice abstraction of nodes and framework. Companies like Airbnb, Paypal, eBay, Groupon use Mesos. Having a Galera poc for Mesos is going to make it likely to have MySQL (etc.) being a native part in Mesos installations.

There had been another customer I was allowed to help to deploy Galera on CoreOS \o/.

At least I plan to help deploy Galera on other cluster or multi-node solutions for Docker also :)

Stay tuned as there is a plan to have a little series about Galera@Docker on the coderships website too.

Have Fun
Erkan Yanar

Link
Peter ZaitsevWhat stopped MySQL? Tracing back signals sent to MySQL (6.3.2015, 16:59 UTC)

Have you ever had a case where you needed to find a process which sent a HUP/KILL/TERM or other signal to your database? Let me rephrase. Did you ever have to find which process messed up your night? ;) If so, you might want to read on. I’m going to tell you how you can find it.

Granted, on small and/or meticulously managed systems tracking down the culprit is probably not a big deal. You can likely identify your process simply by checking what processes have enough privileges to send mysqld a HUP/KILL/TERM signal. However, frequently we see cases where this may not work or the elimination process would be too tedious to execute.

We recently had a case where a process was frequently sending SIGHUPs to mysqld and the customer asked us to see if we could get rid of his annoyance. This blog is the direct result of a discussion I had with my colleague Francisco Bordenave, on options available to deal with his issue. I’m only going to cover a few of them in this blog but I imagine that most of you will be able to find one that will work for your case. Note that most tracing tools add some overhead to the system being investigated. The tools presented in the following are designed to be lightweight so the impact should be well within acceptable range for most environments.

DISCLAIMER: While writing this blog I discovered that David Busby has also discussed one of the tools that I’m going to cover in his article. For those who have read the article note that I’m going to cover other tools as well and I will also cover a few extra SystemTap details in this blog. For those who haven’t yet had chance to read David’s blog, you can read it here.

All right, let’s see what “low hanging tools” there are available to us to deal with our issue!

Linux

  • SystemTap: widely available on Linux but usually not enabled by default. You need to install debuginfo and devel kernel packages and systemtap itself. Similar to DTrace.
  • Perf: although not quite written for generic tracing, due to its ability to trace system calls we can use it to our advantage if we trace sys_enter_sigkill.
  • Audit: generic system auditing platform. Given its nature, we can use it to track down many things, including rogue processes sending HUP signals to our poor mysqld!
  • Code!: Given that MySQL is opensource, you could customize the signal handler to obtain extra information. See more in sigaction(2) and the SA_SIGINFO flag. I’m not sure if this should be listed as a more efficient solution but it’s an option nevertheless. I guess one could also preload/inject his own singal handler via an LD_PRELOAD trick and a custom library but that’s beyond the scope what I intend to cover. However, for certain signals (most notably, SIGSEGV) you may not need to write your own tools as the OS may already come with libs/tools that can assist you. See Ulrich Drepper’s catchsegv or /usr/lib64/libSegFault.so, for instance.
  • Debuggers: These may be efficient to use in some cases but I won’t cover them this time, either.

FreeBSD/Solaris

  • DTrace: a very decent, stable tracing platform. Included in most recent kernels by default for the mentioned platforms (FreeBSD 9.2+, FreeBSD 10+, Solaris 10+).

In this article I’m going to focus on Linux as that’s what people in the MySQL community seem to care about most nowadays. The tools that I will discuss will be SystemTap, Perf and Audit. If you feel that you would like to read about the rest, let me know and I will cover the rest of the options in a followup article.

SystemTap

I’m going to set up SystemTap on a recent, 64 bit CentOS 7 box. I will only cover basic install, you can find more about how to install SystemTap here.

The strength of SystemTap is definitely its flexibility, potentially the best tool for solving our problem on the Linux platform. It’s been around for some time and is generally regarded mature but I would recommend to test your “tapscripts” in dev/qa b

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

Link
Oli SennhauserRename MySQL Partition (6.3.2015, 14:00 UTC)
Taxonomy upgrade extras: 

Before I forget it and have to search again here a short note about how to rename a MySQL Partition:

My dream:

ALTER TABLE history RENAME PARTITION p2015_kw10 INTO p2015_kw09;

In reality:
ALTER TABLE history
REORGANIZE PARTITION p2015_kw10 INTO (
PARTITION p2015_kw09 VALUES LESS THAN (UNIX_TIMESTAMP('2015-03-02 00:00:00'))
);

Caution: REORGANIZE PARTITION causes a full copy of the whole partition!

Hint: I assume it would be very easy for MySQL or MariaDB to make this DDL command an in-place operation...

MySQL Partitioning was introduced in MySQL 5.1.

Link
Chris CalenderMariaDB 10.1.3 Overview and Highlights (6.3.2015, 02:14 UTC)

MariaDB 10.1.3 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.1.3/

This is the 1st beta, and 4th overall, release of MariaDB 10.1, so there are a lot of new changes, functionalities added, defaults changed, and many bugs fixed (I counted 420 – 117 in 10.1.2 & 637 in 10.1.1, fwiw).

Since it’s beta, I’ll only cover the major changes and additions, and omit covering general bug fixes (feel free to browse them all here).

To me, these are the highlights of the new features:

Of course it goes without saying that do not use this for production systems as it is only the 1st beta release. However, I definitely recommend installing it on a test server and giving it a go, test-drive th

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

Link
LinksRSS 0.92   RDF 1.
Atom Feed