Feed aggregator

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 installStarting 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.000Querying 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.59987Draining 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: 4Removing 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...

Am 18. bis 22. Juni findet an der Heinlein Akademie in Berlin ein fünftägiger hands-on Workshop MySQL für Profis statt. Es hat noch Plätze frei!

Anmeldung und weitere Infos unter: http://www.heinlein-support.de/schulung/mysql-fuer-profis

Am 30. und 31. Mai findet im Linux-Hotel in Essen ein MySQL-Cluster Kurs statt. Der Kurs wird durchgeführt und es hat noch Plätze frei!

Weitere Infos unter: http://www.linuxhotel.de/kurs/mysql-cluster/

Anmeldung: http://www.linuxhotel.de/cgi-bin/anmeldung_kurs.pl?veranstaltung=MySQL-C...

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.

FreeBSD 9 seems to have some troubles with MySQL 5.5.20. A customer has moved from MySQL 5.0 on Linux to MySQL 5.5 on FreeBSD 9. He experienced a lot of periodic slow downs on the new, much stronger, system which he has not seen on the old Linux box.

This slow downs were also shown in high CPU system time but we could not see any I/O going on.

When we looked into MySQL we have seen many threads in Opening tables state in the MySQL processlist.

The first idea was to increase table_open_cache to 2048 and later to 4096. This made the Opening tables disappear but then we got a significant amount of threads hanging in Copying to tmp table state in the processlist.

So we suspected that those table are going to disk. But we did not see any I/O (with iostat) and Created_tmp_disk_tables did not change significantly but just Created_tmp_tables.

So I suspect some troubles with Memory allocation on FreeBSD 9 with MySQL.

Then the customer has set table_open_cache = 4!!! And suddenly the problems disappeared. I am a bit confused because this is exactly the opposite of what I expect.

We played a bit around with table_open_cache but as bigger as we make the value as worse the situation became. So be warned if you see similar symptoms...

Anybody any clue what is going on? I have not found any bug in the MySQL bugs database which sounds similar to this...

Weil wir auf einem unserer Server etwas knapp an Diskplatz sind, hatte ich die Idee, das MySQL Feature Datenkompression für InnoDB auszuprobieren. Dieses Feature ist nützlich, wenn Tabellen mit VARCHAR, BLOB oderr TEXT Attributen vorhanden sind.

Um es nicht allzu einfach zu machen ist unsere Tabelle auch noch partitioniert. Sie sieht wie folgt aus:

CREATE TABLE `history_str` ( `itemid` mediumint(8) unsigned NOT NULL DEFAULT '0', `clock` int(11) unsigned NOT NULL DEFAULT '0', `value` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY RANGE (clock) (PARTITION p2012_kw05 VALUES LESS THAN (1328482800) ENGINE = InnoDB, PARTITION p2012_kw06 VALUES LESS THAN (1329087600) ENGINE = InnoDB, PARTITION p2012_kw07 VALUES LESS THAN (1329692400) ENGINE = InnoDB, PARTITION p2012_kw08 VALUES LESS THAN (1330297200) ENGINE = InnoDB, PARTITION p2012_kw09 VALUES LESS THAN (1330902000) ENGINE = InnoDB, PARTITION p2012_kw10 VALUES LESS THAN (1331506800) ENGINE = InnoDB, PARTITION p2012_kw11 VALUES LESS THAN (1332111600) ENGINE = InnoDB, PARTITION p2012_kw12 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

Und die Partitionen verbrauchen wie folgt Diskplatz (nicht wirklich viel, ich weiss!):

-rw-rw---- 1 mysql mysql 184549376 Mar 7 00:43 history_str#P#p2012_kw05.ibd -rw-rw---- 1 mysql mysql 209715200 Mar 14 00:11 history_str#P#p2012_kw06.ibd -rw-rw---- 1 mysql mysql 234881024 Mar 21 00:47 history_str#P#p2012_kw07.ibd -rw-rw---- 1 mysql mysql 226492416 Mar 23 16:39 history_str#P#p2012_kw08.ibd -rw-rw---- 1 mysql mysql 234881024 Mar 19 18:22 history_str#P#p2012_kw09.ibd -rw-rw---- 1 mysql mysql 289406976 Mar 19 18:22 history_str#P#p2012_kw10.ibd -rw-rw---- 1 mysql mysql 281018368 Mar 23 16:39 history_str#P#p2012_kw11.ibd -rw-rw---- 1 mysql mysql 213909504 Mar 23 17:23 history_str#P#p2012_kw12.ibd

Die Tabelle wurde mit den folgenden Werten komprimiert:

ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

Anschliessend schaut die Lage wie folgt aus:

-rw-rw---- 1 mysql mysql 7340032 Mar 23 17:33 history_str#P#p2012_kw05.ibd -rw-rw---- 1 mysql mysql 7340032 Mar 23 17:34 history_str#P#p2012_kw06.ibd -rw-rw---- 1 mysql mysql 8388608 Mar 23 17:36 history_str#P#p2012_kw07.ibd -rw-rw---- 1 mysql mysql 75497472 Mar 23 17:49 history_str#P#p2012_kw08.ibd -rw-rw---- 1 mysql mysql 104857600 Mar 23 17:44 history_str#P#p2012_kw09.ibd -rw-rw---- 1 mysql mysql 125829120 Mar 23 17:51 history_str#P#p2012_kw10.ibd -rw-rw---- 1 mysql mysql 125829120 Mar 23 17:57 history_str#P#p2012_kw11.ibd -rw-rw---- 1 mysql mysql 134217728 Mar 23 18:11 history_str#P#p2012_kw12.ibd

Wir erhalten somit eine Platzersparnis von 40 - 60%! Nicht allzu schlecht.

Aber spannend wäre auch noch den Einfluss auf den Speicher zu sehen:

SHOW GLOBAL STATUS LIKE 'innodb_buffer%'; +---------------------------------------+----------------------+ | Variable_name | Value | +---------------------------------------+----------------------+ | Innodb_buffer_pool_pages_data | 10769 | | Innodb_buffer_pool_pages_dirty | 6613 | | Innodb_buffer_pool_pages_free | 644 | | Innodb_buffer_pool_pages_misc | 18446744073709549802 | | Innodb_buffer_pool_pages_total | 9599 | +---------------------------------------+----------------------+

Diese Zahlen machen nicht wirklich Sinn. Zudem sind wir noch auf einen bekannten MySQL Bug #59550: Innodb_buffer_pool_pages_misc" goes wrong gestossen.

Ein paar nette Grafen InnoDB Buffer Pool activity

Weil unser InnoDB Buffer Pool zu gross war, haben wir ihn etwas kleiner gemacht. Um das Barracuda File Format einzuschalten, haben wir anschliessend die Datenbank neu gestartet. Und dann sind die Zahlen amok gelaufen...

InnoDB compression time

Das erste mal, dass wir InnoDB Compression Time in unserem Monitor sehen... \o/

InnoDB Row operations

Und hier sehen Sie, wie wir die Konvertierung technisch gelöst haben... :-)

Because we are a bit short off disk space on one of our servers I had the idea to try out the MySQL feature Data Compression for InnoDB. This feature is useful if you have tables with VARCHAR, BLOB or TEXT attributes.

To not make it not too simple our table is partitioned as well. Our table looks like this:

CREATE TABLE `history_str` ( `itemid` mediumint(8) unsigned NOT NULL DEFAULT '0', `clock` int(11) unsigned NOT NULL DEFAULT '0', `value` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY RANGE (clock) (PARTITION p2012_kw05 VALUES LESS THAN (1328482800) ENGINE = InnoDB, PARTITION p2012_kw06 VALUES LESS THAN (1329087600) ENGINE = InnoDB, PARTITION p2012_kw07 VALUES LESS THAN (1329692400) ENGINE = InnoDB, PARTITION p2012_kw08 VALUES LESS THAN (1330297200) ENGINE = InnoDB, PARTITION p2012_kw09 VALUES LESS THAN (1330902000) ENGINE = InnoDB, PARTITION p2012_kw10 VALUES LESS THAN (1331506800) ENGINE = InnoDB, PARTITION p2012_kw11 VALUES LESS THAN (1332111600) ENGINE = InnoDB, PARTITION p2012_kw12 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

And the partitions use the following space on disk (not really much, I know!):

-rw-rw---- 1 mysql mysql 184549376 Mar 7 00:43 history_str#P#p2012_kw05.ibd -rw-rw---- 1 mysql mysql 209715200 Mar 14 00:11 history_str#P#p2012_kw06.ibd -rw-rw---- 1 mysql mysql 234881024 Mar 21 00:47 history_str#P#p2012_kw07.ibd -rw-rw---- 1 mysql mysql 226492416 Mar 23 16:39 history_str#P#p2012_kw08.ibd -rw-rw---- 1 mysql mysql 234881024 Mar 19 18:22 history_str#P#p2012_kw09.ibd -rw-rw---- 1 mysql mysql 289406976 Mar 19 18:22 history_str#P#p2012_kw10.ibd -rw-rw---- 1 mysql mysql 281018368 Mar 23 16:39 history_str#P#p2012_kw11.ibd -rw-rw---- 1 mysql mysql 213909504 Mar 23 17:23 history_str#P#p2012_kw12.ibd

After the table was compressed with the following values:

ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

The space on disk was used as follows:

-rw-rw---- 1 mysql mysql 7340032 Mar 23 17:33 history_str#P#p2012_kw05.ibd -rw-rw---- 1 mysql mysql 7340032 Mar 23 17:34 history_str#P#p2012_kw06.ibd -rw-rw---- 1 mysql mysql 8388608 Mar 23 17:36 history_str#P#p2012_kw07.ibd -rw-rw---- 1 mysql mysql 75497472 Mar 23 17:49 history_str#P#p2012_kw08.ibd -rw-rw---- 1 mysql mysql 104857600 Mar 23 17:44 history_str#P#p2012_kw09.ibd -rw-rw---- 1 mysql mysql 125829120 Mar 23 17:51 history_str#P#p2012_kw10.ibd -rw-rw---- 1 mysql mysql 125829120 Mar 23 17:57 history_str#P#p2012_kw11.ibd -rw-rw---- 1 mysql mysql 134217728 Mar 23 18:11 history_str#P#p2012_kw12.ibd

So we got a reduction of used disk space by 40 - 60%! Not too bad.

But we also want to see what impact it has on memory:

SHOW GLOBAL STATUS LIKE 'innodb_buffer%'; +---------------------------------------+----------------------+ | Variable_name | Value | +---------------------------------------+----------------------+ | Innodb_buffer_pool_pages_data | 10769 | | Innodb_buffer_pool_pages_dirty | 6613 | | Innodb_buffer_pool_pages_free | 644 | | Innodb_buffer_pool_pages_misc | 18446744073709549802 | | Innodb_buffer_pool_pages_total | 9599 | +---------------------------------------+----------------------+

Those numbers do not really make sense. We also hit a known MySQL Bug #59550: Innodb_buffer_pool_pages_misc" goes wrong.

Some fancy graphs InnoDB Buffer Pool activity

Because our InnoDB Buffer pool was too big we have reduced it a bit. For enabling the Barracuda file format we restarted the database afterwards. An then the numbers went amok...

InnoDB compression time

The first time we can see InnoDB compression time in our Monitor... \o/

InnoDB Row operations

And here you can find out how we solved it technically... :-)

Helsinki, Finland, Uster, Switzerland – February 18, 2012 – Codership, the provider of Galera Cluster for MySQL, and FromDual, a MySQL consulting company, today announced collaboration to offer Galera Cluster technology and related support and consulting services for Galera users all over the world, especially in German speaking countries Germany, Austria and Switzerland (DACH). Galera Cluster is a synchronous, true multi-master replication cluster for MySQL using the well known InnoDB storage engine. Customers can deploy Galera Cluster locally in LAN environments, as geo-clusters over the WAN or as virtual cluster in the cloud.

Galera Cluster for MySQL is offered as open source software. It can be downloaded freely from www.codership.com. Many of Codership customers are using Galera Cluster for business critical applications. FromDual will be offering consulting and support services for Galera users, especially in German speaking countries. ”FromDual has years of competence in MySQL consulting and support. They have a deep understanding of clustering and replication technologies and how to implement them into production. FromDual is the perfect partner for Codership” said Seppo Jaakola, CEO of Codership.

"With Galera we get a great high availability (HA) product for MySQL which removes all the pain we had with the existing MySQL HA solutions. Our customers are always looking for better ways to solve MySQL replication, scalability and management issues to secure 24/7 business availability and growth for the future business. Galera Cluster does exactly that. We are happy to add Galera Cluster to our consulting and support portfolio with the top level support from Codership" says Oli Sennhauser, Senior MySQL consultant and CEO of FromDual.

Galera Cluster is used by users who need highly available MySQL database back-ends with very fast fail-over time for business critical applications like Social networks, Gaming platforms, Telecom/VoiP solutions, Media and entertainment sites, Business Software as a Service (SaaS), Platform as a Service (PaaS), ERP systems, web-shops, e-commerce solutions or similar critical applications.

About FromDual GmbH

FromDual is a global acting, neutral and vendor independent consulting, support and training company for MySQL, Percona Server, MariaDB and Galera Cluster with numerous customers all over the world in the Telecom, Media, Public Service, Internet and Industry sector. FromDual is Oracle Silver Partner and Open Database Alliance (ODBA) Silver Partner. In addition to its MySQL services FromDual distributes its Performance Monitor for MySQL for local installations or as Software as a Service solution. For more information about FromDual, their products, consulting and support services, please visit www.fromdual.com.

Press contact:

FromDual GmbH
Oli Sennhauser, CEO
E-Mail: oli.sennhauser@fromdual.com
Cell: +41 79 830 09 33

About Codership Oy

Codership develops fundamentally new replication and clustering solutions for open source databases, adopting novel ideas from latest DBMS and distributed computing research. The founders of Codership have long experience in developing several widely used MySQL clustering solutions before starting Galera development in 2007. Already thousands of users have chosen Galera Cluster solution – the Codership's flagship open source product and Codership is working actively for and with this growing user community. For more information about Codership, products, consulting and support services, please visit www.codership.com.

Press contact:

Codership OY
Seppo Jaakola, CEO
E-Mail: seppo.jaakola@codership.com
Cell: +358 405 105 938

Helsinki, Finnland, Uster, Schweiz – 18. Februar 2012 – Codership, Anbieter des Galera Clusters für MySQL, und FromDual, eine MySQL Beratungsfirma, kündigen eine Zusammenarbeit bei der Verbreitung von Galera Cluster Technologie und dazugehörigen Support- und Beratungs-Dienstleistungen für Galera Anwender an. Galera Cluster ist ein synchroner, echter Multi-Master Replikations-Cluster für MySQL, welcher die weit verbreitete InnoDB Storage Engine nutzt. Anwender können Galera Cluster lokal in LAN-Umgebungen, als Geo-Cluster über das WAN oder als virtualisierten Cluster in der Cloud einsetzen.

Galera Cluster für MySQL wird als Open Source Software angeboten. Er kann kostenlos von der Website www.codership.com heruntergeladen werden. Viele der Codership Kunden nutzen Galera Cluster für geschäftskritische Anwendungen. FromDual bietet Beratungs- und Support-Dienstleistungen für Galera-Anwender, insbesondere in den deutschsprachigen Ländern Deutschland, Österreich und Schweiz (DACH) an. „FromDual Mitarbeiter haben jahrelange Erfahrung in MySQL-Beratung und -Support. Sie haben ein tiefes Verständnis von Cluster- und Replikations-Technologien und wie diese im Betrieb umgesetzt werden müssen. FromDual ist der perfekte Partner für Codership“, sagt Seppo Jaakola, CEO von Codership.

„Mit Galera haben wir ein grossartiges Hochverfügbarkeits-Produkt für MySQL, welches alle Schmerzen beseitigt, welche wir bisher mit bestehenden MySQL HA-Lösungen gehabt haben. Unsere Kunden sind immer auf der Suche nach Wegen, um ihre MySQL Replikations-, Skalierungs- und Betriebs-Abläufe zu optimieren und um ihre 7x24 h Verfügbarkeit und ihr Geschäftswachstum sicherzustellen. Galera Cluster macht genau das. Wir schätzen uns glücklich, Galera Cluster zu unserem Beratungs- und Support-Angebot hinzufügen zu können“, sagt Oli Sennhauser, Senior MySQL Berater und CEO von FromDual.

Galera Cluster wir von Anwendern genutzt, welche hoch verfügbare MySQL Datenbank-Backends mit sehr kurzen Failover-Zeiten für geschäftskritische Applikationen wie Telekom/VoIP-Lösungen, ERP-Systeme, Web-Shops, e-Commerce-Lösungen, Geschäftsanwendungen as a Service (SaaS), Platform as a Service (PaaS), Medien- und Unterhaltungs-Websites, Spieleplattformen, soziale Netzwerke oder ähnlich kritische Applikationen benötigen.

Über FromDual GmbH

FromDual ist eine global agierende, neutrale und Hersteller unabhängige Beratungs-, Support- und Schulungs-Firma für MySQL, Percona Server, MariaDB und Galera Cluster mit zahlreichen Kunden aus der ganzen Welt aus den Sektoren: Telekommunikation, Medien, Öffentlicher Dienst, Internet und Industrie. FromDual ist Oracle Silber Partner und Open Database Alliance (ODBA) Silber Partner. Zusätzlich zu seinen MySQL-Dienstleistungen vertreibt FromDual seinen Performance Monitor für MySQL als lokale Installation oder als Software as a Service-Lösung. Weitere Informationen über FromDual-Produkte, Beratungs- und Support-Dienstleistungen finden Sie unter www.fromdual.com.

Pressekontakt:

FromDual GmbH
Oli Sennhauser, CEO
E-Mail: oli.sennhauser@fromdual.com
Mobil: +41 79 830 09 33

Über Codership Oy

Codership entwickelt Replikations- und Cluster-Lösungen für Open Source Datenbanken unter Einbezug neuartiger Ideen modernster Datenbanksysteme und Forschungsresultate aus dem Bereich verteilter Rechner-Netze und setzt diese zu neuen Hochverfügbarkeits-Lösungen um. Die Gründer von Codership haben langjährige Erfahrung bei der Entwicklung mehrerer weit verbreiteter MySQL Cluster-Lösungen gesammelt, bevor sie die Entwicklung von Galera im Jahr 2007 begonnen haben. Tausende von Anwendern haben die Galera Cluster Lösungen – das Codership Open Source Flaggschiff – bereits für ihre geschäftskritischen Anwendungen gewählt. Codership arbeitet aktiv für diese wachsende Anwendergemeinschaft. Mehr Informationen über Codership-Produkte, Beratungs- und Support-Dienstleistungen finden Sie unter www.codership.com.

Pressekontakt:

Codership OY
Seppo Jaakola, CEO
E-Mail: seppo.jaakola@codership.com
Mobil: +358 405 105 938

Many of you may monitor their databases for different purposes. Beside alerting it is often good to also make some graphs from MySQL performance counters to see what is actually happening on your database.

The following graphs where made with our FromDual Performance Monitor for MySQL as a Service (MaaS) set-up. If you do not have the time to install a performance monitoring yourself please feel free to contact us for our MaaS solution.

Overview

First of all it is a good idea to have an overview of all the settings in you different databases and if they are compliant to your standards.

Here it looks like two of our databases are still running with Statement Bases Replication (SBR). Further the MySQL General Query Log is enabled which is non optimal for write performance and the default Storage Engine is still set to MyISAM which is not wanted in our case.

InnoDB

This server is mostly an InnoDB Server. We can see that we have some write traffic because the InnoDB Buffer Pool has constantly 15 - 20% of dirty pages. Further we see that we have a very constant level of dirty pages:

Here I guess, the database was restarted on Wednesday:

We can clearly see the positive impact of MySQL partitioning:

Read and write are here more or less equally high with some strong spikes:

If we have a closer look we can see that one typical spike is always at 06:00 in the morning. It is read and write so it is possibly NOT a backup but more likely a reporting or maintenance job:

This system does mostly write with a heavy read phase during midnight:

The read starts at 23:00 and ends at 03:30. It could be some nightly reporting?

Extreme read and write spikes. Not good for a system that likes to have close to real time behavior:

The consequences we can see immediately, Locking:

When we look at the InnoDB Locking we can see that this job at 06:00 in the morning causes some lockings. Up to 2.5 seconds. If this causes troubles we really have to investigate what is running at that time:

If we look at the last 14 days we can see a huge read spike some time ago, what has happened there? This has possibly influenced the whole system as well:

Here we have a special InnoDB read pattern. Can you see it? Every 3 hours at xx:30 it happens. We should try to find out what it is:

From time to time we can see some big transactions:

MyISAM

Currently and over the last few days our MyISAM key buffer was mostly empty. But the high water mark Key_blocks_used indicates that they were used in the past. So we should try to find out if this key buffer is still used and if not if we can free this memory (about 2.4 Gbyte):

From time to time we see some MyISAM key reads from memory. This could be caused by MyISAM table indexes or by temporary tables gone to disk. So not really much MyISAM traffic at all and it is mostly happening during week days:

This is a MyISAM mostly system but never the less we do not have MyISAM table locking issues:

Connections

We are not far from hitting max_connections. So either we increase this value or we think about why we have so many connections open concurrently. On February 5th we had many concurrent running threads. That was surely not good for the whole system performance:

On February 12th there was possibly something wrong with the application: We see many aborted clients what never had before:

It looks like thread cache was always big enough:

On this server we definitly had a problem and we hit the roof with the amount of connections. I am wondering if it would not be more useful to even lower max_connections here?

Network traffic

Network traffic was growing a bit last week but now is stable again. Our network should not have a problem with this load:

This is a read mostly database. The patter comes from sorts and handler_read_rnd (see below):

Handler Interface

Here we see that we do many handler_read_next and handler_read_rnd_next operations. This is equivalent to index range scans and full table scans. Our spike at February 11th seems to come from such an index range scan:

This server does mostly read by a full table scan! There is a huge potential for optimization so the server could cover more load if the queries were done more optimal:

It seems there were some UPDATEs involved on 8th:

We see many INSERTs but few to no DELETEs. This means the database size is steadly growing. We should think about archiving old data?

OK. This seems to be a maintenance job. Luckily it was set off-peak hours (08:00):

Somebody does evil things hear: handler_read_rnd is bad for performance. And it pretty much correlates with the network traffic from above:

Sort

Sort behavior seems to have changed significantly on Monday. We should find out if application has released a new version:

On this server we see some sort_merge_passes which is a sign of a too small sort buffer or just huge sorts:

Queries

The majority of queries sent against this database are SELECT's:

Temporary Tables

The use of temporary tables has changed. This is a sign again that something in the application was modified:

If we look a bit closer we can see that the use of temporary disk tables has increased. We should keep an eye on this:

Here we most probably have a problem with temporary tables on disk. It happens quite periodical and predictable so we can investigate who causes those:

If you look closer at it we can see that it is a similar pattern as with the data reads from above:

MySQL Process information

The number of page faults has changed dramatically over the weekend. So what was changed there?

Query Cache

There are no query cache hits but Query Cache is enabled! There are several reasons for this. We should investigate or disable Query Cache at all:

We have some low memory prunes. Shall we increase Query Cache size?

Hmmm. It is already quite big. Better to defragment it from time to time to get rid of the free memory:

Table Definition Cache

The Table Definition Cache is much to big here. A value of 256 (default) would be far more than enough. Better to release the resources:

This is the opposite case. Here the Table Definition Cache is much too small at least when the job at 23:00 is running:

Table (Open) Cache

Same situation here with the Table Open Cache. A value of 3 - 4k would be enough. Better to release the resources. There are some cases known where a too big table cache causes performance issues:

Same here. Too small at midnight jobs:

Binary logging (Master)

The Binary Log has a size of about 100 Mbyte (Debian/Ubuntu?) and is filled up every hour (ca. 25 kybte/s). During the night we have some less traffic, during day some more traffic:

From time to time we have some spikes in binary log traffic. But binary log cache seems to be always big enough:

Replication Slave

The Slave was not lagging very often and only for short time:

If you want such performance graphs as well from your system, just let us know!

In my set-ups I have different MySQL versions (MySQL 5.0, 5.1, 5.5 and 5.6, Percona Server 13.1 and 24.0, MariaDB 5.2.10, 5.3.3, Galera 1.0, 1.1 and 2.0) running in parallel at the same time.
Up to now I have not found a practical way yet to do this with RPM or DEB packages. If anybody knows how to do it I am happy to hear about it.

So I love and need only binary tar balls. Installation and removal is done within seconds and no remainings are left over after a removal. To operate the whole I use myenv.
Some software providers unfortunately do not provide binary tar balls at all or not in the form I want and need them. Thus I was thinking about how to get those by extracting them from packages. Up to now I have not had the time to write this down. But today was the right time...

RPM rpm2cpio galera-22.1.1-1.rhel5.x86_64.rpm | cpio -vidm tar czf galera-22.1.1-1.rhel5.x86_64.tar.gz usr rm -rf usr

Extract with:

tar xf galera-22.1.1-1.rhel5.x86_64.tar.gzDEB ar vx galera-22.1.1-amd64.deb mv data.tar.gz galera-22.1.1-amd64.deb.tar.gz rm debian-binary control.tar.gz

Extract with:

tar -mxf galera-22.1.1-amd64.deb.tar.gz

The packages look quite the same in size:

-rw-r--r-- 1 oli oli 6725416 2012-02-08 13:49 galera-22.1.1-1.rhel5.x86_64.rpm -rw-r--r-- 1 oli oli 6769606 2012-02-08 14:18 galera-22.1.1-1.rhel5.x86_64.tar.gz -rw-r--r-- 1 oli oli 1386762 2011-12-12 17:12 galera-22.1.1-amd64.deb -rw-r--r-- 1 oli oli 1385994 2012-02-08 14:18 galera-22.1.1-amd64.deb.tar.gz

so I assume that there is nothing lost.

The differences in size between DEB and RPM seems to come from the packaging itself:

usr_deb/lib/galera/libgalera_smm.so: ELF 64-bit (SYSV), dynamically linked, stripped usr_rpm/lib64/galera/libgalera_smm.so: ELF 64-bit (SYSV), dynamically linked, not stripped

So nothing to worry. The programs itself worked without any problems after the first tests. So I am optimistic that this is a good workaround until I can convince the software vendor to make good binary tar balls...