MySQL Tech-Feed (en)

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 …

When you are working with MyISAM [ 1 ] tables MySQL provides a feature called the Midpoint Insertion Strategy [ 2 ]. You can enable it with the parameter key_cache_division_limit [ 3 ].

By default, the key cache management system uses a simple LRU [ 4 ] strategy for choosing key cache blocks to be purged:

When using the Midpoint Insertion Strategy feature, the LRU chain is divided into two parts:

• the hot sub list

and

• the warm sub list.

The division point between those two parts is not fixed, but the …

Bug #57532: Warm key blocks in MyISAM key_cache structure could be reported

And for MariaDB: https://lists.launchpad.net/maria-developers/msg03663.html

We recently had the case with one of our customers where we got externally generated random hash values (up to 70 bytes) and they were used as Primary Keys in InnoDB.

As we know, this is not a very good idea because the size of all secondary indexes becomes large and because a random hash value as a Primary Key gives us a bad locality of our rows in the table ¹.

If we do not specify a Primary Key and have no Unique Key InnoDB generates a hidden Clustered Index based on the Row ID which is a 6 byte field. …

Coming soon…

Have a look at the Performance Monitor Documentation.