News

One of our partners recently asked me on what platforms do we usually see MySQL installed out there…

The last 5 years I gave the answer: Typically it is 80% Linux, 10% Windows, 5% Solaris and 5% all others. But this was only the picture of my limited view and I was not sure how objective this was.

This time I really wanted to know it and so I collected the information of about 570 MySQL installations of customers.

The following numbers came out:

Operating System

We did recently a review of one of our customers systems and we found that the Query Cache was disabled even thought it had significant more read than write queries.
When we asked the customer why he has not enabled the Query Cache he mentioned a review that was done a few years ago and which stated that the Query Cache hit ratio was non optimal.
This was verified on a testing system which had the Query Cache enabled by accident.

But we all thought that the Query Cache would make sense in this situation so …

Dear MySQL and MariaDB User,

With this Newsletter you receive the News about FromDual for Summer 2011.

Sie können diesen Newsletter auch auf deutsch lesen oder sich den deutschprachigen Newsletter. abonnieren.

Topics

• Basic- and Silver Support for MySQL and MariaDB
• FromDual Performance Monitor for MySQL with more InnoDB Graphs
• Summer vacation without a MySQL DBA?
• Upcomming Trainings and Workshops
• Technical Information
• German Speaking MySQL User Group founded
• News about MySQL

Basic- and Silver-Support for …

When we analyze our customers systems we see typically a high fragmentation of the query cache after a while. This leads to a less optimal use of the Query Cache than possible.

With the following Query you can see the values for your Query Cache:

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 11328    |
| Qcache_free_memory      | 89442000 |
| Qcache_hits …

We have just released v0.7.1 of the FromDual MySQL Performance Monitor. The new release can be downloaded from here.

In all editions some error messages have been cleaned-up, the fall-back data gather method mysql has been removed and the Maria SE template has been renamed to Aria.

In the edition dedicated to our customers most of the InnoDB graphs which are available with MEM v2.3.3 have been implemented now.

Please let us know, what you want to have monitored in addition and send us your feedback and …

A customer rose a support case with a problem on his Master-Master set-up. The 2^nd Master claims to have a problem:

master2> SHOW SLAVE STATUS<br>G
                ...
    Master_Log_File: master1-bin.000014
Read_Master_Log_Pos: 97975045
   Slave_IO_Running: No
  Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 97975045
      Last_IO_Errno: 1236
      Last_IO_Error: Got fatal error 1236 from master when reading data from
                     binary log: 'Client requested master to start replication …

From August 15^th to 17^th FromDual will have an Advanced MySQL Developer Workshop with Citrus in Helsinki (Finland). The workshop will be held in English. The workshop topics you can find here and the registration form is available here.

Today the German speaking MySQL User Group (DMySQLAG) was officially founded in Berlin. The association advocates:

• the information of the use, the handling and the experience with MySQL and development in the MySQL Eco-System as well as systems which use MySQL.
• the exchange of experience between MySQL users about MySQL and other systems.
• the Advice and Cooperation with Oracle and vendors of other systems.
• the submission of suggestions of the members to Oracle and vendors of other systems.

The intention of …

In our last customer project we had around 600 Gbyte of data in a MySQL database. Because this database consumed a significant amount of our disk space and backups with the InnoDB backup tool took pretty long we wanted to find out if we could get rid of some of the tables.

This application was growing over the last 10 years and it was not clear if some tables are still in use or not.

But how to find out when a table was touched last? MySQL/InnoDB theoretically could know about but does not report this …

A customer who is handling digital certificates had a problem with one of those. So we had to investigate.

Because the certificate is in binary form it is stored in a BLOB and we had to extract it from the database to do some verifications.

What first came to my mind was to extract the certificate with the SELECT INTO OUTFILE command. But the verification tool complained and told us that the certificate has a wrong format.

Luckily I found in the documentation the following statement: If you use INTO …

If you ask around about shared hosting setups with MySQL everybody is frightened. In fact it looks like shared hosting is one of the most difficult setups you can get.

The number of users is big, the number of tables huge and the load pattern is completely unpredictable and the queries often very, let us say: non-optimal.

Here one of the DBA wisdoms come into play: Controlling developers is like herding cats.

If you talk to the Shared MySQL Hoster they confirm that this setups are very demanding!

Why is …

FromDual has released today the next version v0.6 of its FromDual Performance Monitor for MySQL.

The most important improvement of the new release is the new monitoring module for DRBD devices which are often used in MySQL High Availability (HA) set-ups.

More information about the new functionality added you can find in the article MySQL Performance Monitor with DRBD monitoring capabilities, MySQL Performance Monitor and in the MySQL HA (high availability) cookbook.

The FromDual Performance Monitor for …

In the following article I have summarized some steps and hints to set-up a MySQL active/passive failover Cluster also sometimes called MySQL HA.

With such a set-up you can achieve a 99.99% (4x9) HA set-up for MySQL (52 minutes downtime per year). The same procedure also works for PostgreSQL, Oracle and other database systems running on Linux (DRBD runs on Linux only).

The concept

The concept of an active/passive failover Cluster is the following:

• You have 2 servers (also called nodes).
• They communicate …

The FromDual Performance Monitor for MySQL has now a new module for monitoring DRBD. Since more an more of our customers want to run MySQL High Availability solutions based on DRBD we have implemented this module.

We gather all information which are reported with the command:

# cat /proc/drbd

version: 8.3.8 (api:88/proto:86-94)
 0: cs:Connected ro:Secondary/Primary ds:UpToDate/UpToDate C r----
    ns:1241240 nr:14311680 dw:15033532 dr:1241281 al:198 bm:52 lo:0 pe:0 ua:0
    ap:0 ep:1 wo:b oos:0

Now you …

Be careful when using SAN (Storage Area Networks) or similar shared storage solutions (and any other virtualization, consolidation or cloud solutions).

This week it happened again: A customer called us because he had some troubles with his on-line shop (note the date!). Everybody in his company complained that the databases are answering slowly.

When looking on the box (with iostat) we have seen some I/O load and some pending reads in InnoDB (SHOW ENGINE INNODB STATUS and SHOW GLOBAL STATUS LIKE …

From the Cooperation of these two companies arises the biggest independent service provider for MySQL and MariaDB in Europe.

Hamburg, Uster – February 28, 2011 - The Hamburg based PrimeBase Technologies and the near Zürich located FromDual are forming a Cooperation for MySQL products and services, starting March 1st, 2011.

This Cooperation enables both companies to offer a complete set of services for all MySQL and MariaDB customers.

The customers of both parties now have he possibility to demand a …

FromDual releases its new version v0.5 of its MySQL Performance Monitor working with Zabbix.

What has changed so far in this release:

• Recommended Location has changed to /usr/local
• FromDual agent log files are rotated now.
• There are now 2 different packages: One for the Agent and one for the Templates.
• Some of the graphs were improved.
• Missing status and system variable information were added and some were fixed.
• Verbosity of logging information was adjusted.
• A module for monitoring additional informations …

Starting and stopping, status and failover is possible now. Script basically works but should be made a bit more sophisticated still…

Let me know your findings and wishes.

One thing I forgot to mention: We lost Cluster B. So we rebuilt Cluster B from Cluster A.

Then we set-up channel ch1 from Cluster A to B. This worked fine. When we wanted to set-up Channel ch3 from Cluster B to A we got troubles starting the Slave.

We finally managed to start the Slave when we truncated the ndb_apply_status table. In the documentation is also something mentioned that a RESET SLAVE should work.

And travelling back from a customer I found some time to write a script to show the status of my …

Hello Ky,

Yes. You got me right! I meant the query on ndb_binlog_index.

With no traffic I mean NO DML statements (INSERT, UPDATE, DELETE, etc.) on that Cluster.

Thanks for asking and clearing this up!

Regards, Oli