FromDual TechFeed (en)

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 …

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 …

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 …

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

Hi Krishna,

Why do you think we should be carful using log_slave_updates in circular replication set-up’s?

Regards, Oli

A few days ago I had to deal with MySQL Cluster replication. I did not do this for a while so I was prepared to expect some interesting surprises once again.

For those who MySQL Cluster - Cluster circular replication is the daily business they may skip this article. For all the others they possibly can profit from our learnings.

I am talking about the following MySQL Cluster set-up:

More detailed information about such set-ups you can find in the MySQL Cluster documentation.

Situations that lead to a …

I forgot to mention in the main post that I did these tests with 5.1.50.

Settings for MyISAM this time:

key_buffer_size         = 8M
sort_buffer_size        = 2M
read_buffer_size        = 128k
myisam_sort_buffer_size = 8M

Settings for InnoDB:

# plugin not built-in
innodb_file_per_table          = 1
innodb_log_file_size           = 128m
innodb_buffer_pool_size        = 384m
innodb-flush_log_at_trx_commit = 0
innodb_log_files_in_group      = 3
innodb_support_xa              = 0

InnoDB table WITHOUT …

In the old days everything was simpler. We had one maker of our favourite database management system and possibly the choice between different Storage Engines. Mostly the decision has to be taken between MyISAM and InnoDB. When you care about your data integrity you have chosen InnoDB.

Nowadays it is more complex. We have several different makers of our favourite database management system: Oracle/MySQL, Monty Program AB and Percona with their products: MySQL, MariaDB and Percona Server.

We have different …

It is generally well known that indexes help a lot to speed up database queries (especially SELECT but also UPDATE and DELETE). It is less known, that indexes also have some disadvantages.

One of these disadvantages is, that indexes require space in memory and on disk. An other disadvantage of indexes is, that they slow down DML statements like INSERT and DELETE.

We often see at our customers that they do not realized this behavior. Now I found the time to show the impact of (too) many indexes graphically. …

A while ago I did some research about MySQL audit functionality and logon a and logoff triggers. MySQL and MariaDB provide a logon trigger in the form of the init_connect variable but no logoff trigger where most of the work for auditing would be done. When we would have a logoff trigger we could track the login and possibility some activity of a user and implement auditing functionality.

Yesterday when I was looking into the code for an answer to the question of one of my customers this research came into …

Introduction

In his presentation about PBXT at the DOAG Conference 2010 Paul McCullagh was speaking about memory resident PBXT tables. They will be available in version 1.1 of the PBXT Storage Engine Plugin. Memory resident PBXT tables should have similar characteristics like normal MySQL MEMORY</span> tables</a>. But in addition to the MEMORY tables they are transactional and can handle BLOB</span> and TEXT attributes.

An alternative to this feature would be the MySQL dynamic heap row …

This week I showed one of my customers our MyEnv. He was very interested in it and suggested to make it known in public. In fact MyEnv is available for download already several years…

But I did not have the heart yet to announce it more publicly because it was not end user ready at all. So I used the weekend to make it nicer, consolidated some of the code, dropped old stuff etc. Now I think it is acceptable to use for public but not perfect.

So what is MyEnv?

MyEnv is a set of scripts to run …