Shinguz

Taxonomy upgrade extras: performanceperformance monitoringperformance monitormpmmaaswindows

A customer recently was asking why our MySQL Performance Monitor (MPM) is not working on Windows...? The answer is short: It was developed on Linux and never tested on Windows...

But I was wondering how much effort it would take to make it work on Windows as well.

I was quite surprised how fast it was to make the basic functionality working on Windows. It took me less than one hour to install, configure and patch MPM.

Patch MPM

The file FromDualMySQLagent.pm has to be patched at 2 locations. The lock file name must be something understandable by Windows (for example C:\Temp\FromDualMySQLagent.lock. We will fix that in the next MPM release.

40 # Should NOT be hard coded, tofix later!!! 41 # Does not work on Windows! 42 my $lAgentLockFile = '/tmp/FromDualMySQLagent.lock'; 43 # Check if lock file already exists and complain if yes ... 533 # Does not work on Windows! 534 my $lAgentLockFile = '/tmp/FromDualMySQLagent.lock'; 535 if ( ! unlink($lAgentLockFile) ) {

There are at least 2 other parts in the code which make troubles. But they can be circumvented by disabling the modules (server and process) respectively configuring MPM accordingly.

A basic MPM configuration file on Windows

We have used the following basic configuration file:

[default] LogFile = C:\Users\oli\logs\mpm.log Debug = 2 CacheFileBase = C:\Users\oli\cache MaaS = on Hash = <your hash> Methtode = http Url = http://support.fromdual.com/maas/receiver.php [FromDual.Win_laptop] Modules = mpm [FromDual.Win_laptop.win_db]

In your case there is possibly some more configuration needed. For details please look here.

Now we are quite confident that the next MPM release will work more or less with Windows out of the box. If you cannot wait try it out with this hack. More details about installing the MPM on Windows you can find here. If you run into problems please report them in the MPM installation on Windows forum. All paying customers can naturally use our support platform.

When you install a Galera Cluster for MySQL for High Availability (HA) it is not enough to install the Database Cluster to achieve this goal. You also have to make the application aware of this HA functionality. This is typically done with some kind of load balancing mechanism between the database and the application.

We have several possibilities how to make such a load balancing possible:

• We build such a load balancing mechanism directly into the application.
• When we use Java or PHP we can use the fail-over functionality of the connectors (Connector/J, mysqlnd-ms).
• If we cannot touch the application we can put a load balancing mechanism between the application and the database. This can be done with:
  • a Hardware Load Balancer,
  • a Software Load Balancer (Pen, GLB, LVS, HAProxy and others...),
  • or with MySQL-Proxy.

Building the Galera Load Balancer

As an example we look at the Galera Load Balancer (GLB). The documentation about it you can find in the README file.

It can be built as follows:

wget http://www.codership.com/files/glb/glb-0.7.4.tar.gz tar xf glb-0.7.4.tar.gz cd glb-0.7.4 ./configure make make install
Starting the Galera Load Balancer

The Galera Load Balancer will be started as follows:

./glbd --daemon --threads 6 --control 127.0.0.1:4444 127.0.0.1:3306 \ 192.168.56.101:3306:1 192.168.56.102:3306:1 192.168.56.103:3306:1 Incoming address: 127.0.0.1:3306 , control FIFO: /tmp/glbd.fifo Control address: 127.0.0.1:4444 Number of threads: 6, source tracking: OFF, verbose: OFF, daemon: YES Destinations: 3 0: 192.168.56.101:3306 , w: 1.000 1: 192.168.56.102:3306 , w: 1.000 2: 192.168.56.103:3306 , w: 1.000
Querying the Galera Load Balancer

It can be queried as follows:

echo getinfo | nc -q 1 127.0.0.1 4444 Router: ---------------------------------------------------- Address : weight usage conns 192.168.56.101:3306 : 1.000 0.667 2 192.168.56.102:3306 : 1.000 0.500 1 192.168.56.103:3306 : 1.000 0.500 1 ---------------------------------------------------- Destinations: 3, total connections: 4

and

echo getstats | nc -q 1 127.0.0.1 4444 in: 37349 out: 52598 recv: 89947 / 1989 send: 89947 / 1768 conns: 225 / 4 poll: 1989 / 0 / 1989 elapsed: 76.59987
Draining nodes with Galera Load Balancer

Let's assume, we want to take out node 192.168.56.101 from the Load Balancer for maintenance purposes, this can be done as follows:

echo 192.168.56.101:3306:0 | nc -q 1 127.0.0.1 4444 echo getinfo | nc -q 1 127.0.0.1 4444 Router: ---------------------------------------------------- Address : weight usage conns 192.168.56.101:3306 : 0.000 1.000 0 192.168.56.102:3306 : 1.000 0.667 2 192.168.56.103:3306 : 1.000 0.667 2 ---------------------------------------------------- Destinations: 3, total connections: 4
Removing and adding nodes from Galera Load Balancer

If you want to shrink or grow your database cluster, removing and adding nodes works as follows:

echo 192.168.56.103:3306:-1 | nc -q 1 127.0.0.1 4444 echo 192.168.56.103:3306:2 | nc -q 1 127.0.0.1 4444

And now have fun playing around with your Galera Load Balancer...

Taxonomy upgrade extras: performanceenterprise monitormonitoringperformance monitoringnewsperformance monitorreleasegraphgalerampmmaas

On April 2nd 2012 FromDual released the new version v0.9 of its Performance Monitor for MySQL (mpm). The new version can be downloaded from here.

The Performance Monitor for MySQL (mpm) is an agent which is hooked into Zabbix. Zabbix is an integrated Enterprise Monitoring solution which can produce performance graphs and alerting.

The changes in the new release are:

New functionality

• A new server module gathers MySQL database specific server informations. This is especially interesting for the Monitoring as a Service customers.
• You can monitor Galera Cluster for MySQL now. All important items of Galera Cluster for MySQL up to version 2.0 are gathered. The important Triggers and Graphs are available. FromDual Performance Monitor for MySQL becomes your indispensable tool for monitoring Galera Cluster!
• Trigger was added on low open_files_limit

Changed functionality

• Item history was reduced from 90 to 30 days to safe space on disk.
• InnoDB items were added and Graphs improved and cleaned-up.
• MyISAM items were added and Graphs improved.
• Query Cache items were added.
• Some triggers were too verbose or complained when they should not. Should be fixed now.
• MPM v0.9 was tested with Zabbix 1.8.11 and works without any problems.

Fixes

• Some items were not reported correctly. Fixed them.
• Many little bugs in different modules were fixed.

For more detailed informations see the CHANGELOG.

Installation and upgrade documentation can be found here.

If you want to stay tuned about the progess of the next release of mpm follow us on Twitter...

If you find any bug please report them to our bugs database. If you have some questions or if you want to exchange know-how related to the mpm please go to our Forum.