News

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. …

With one of its partners FromDual plans to offer an Advanced MySQL DBA Workshop. The first workshop should run in November 2010.

To offer the best possible contents to the participants we want your feedback about the proposed topics, about the missing topics and what you think in general about such a workshop.

Please let us know your opinion. Either as comment on our web-site or for our eyes only at Feedback.

Thank you for your participation.

FromDual launches its database Performance Monitoring solution for MySQL, Percona Server and MariaDB. This solution provides monitoring capabilities for most MySQL Storage Engines (Aria, InnoDB, MyISAM, NDB, PBXT and XtraDB). Further you can monitor MySQL Master - Slave replication set-ups, UNIX processes (mysqld, ndbd) and MySQL specific information.

More details you can find on the page Performance Monitor for MySQL.

InnoDB is known to have crash-recovery capabilities and thus is called a crash safe storage engine (in contrary to MyISAM). Nevertheless under certain circumstances it seems like InnoDB pages can get corrupt during a crash and then a manual crash-recovery is needed.

Oracle/MySQL blames in such cases the Operating System, the I/O system or the hardware. What we have seen is that such incidents occur more often on Windows systems and when people are running their databases in a virtualized environment …

MySQL Cluster is mainly an in-memory database. Nevertheless it requires a good I/O system for writing various different information to disk.

The information MySQL Cluster writes to disk are the:

• Global Checkpoints (GCP) which are the transactions.
• Local Checkpoints (LCP) which is a dirty image of the data.
• Backup.

In the following schema (a 2-node Cluster) you can see what is related to each other:

Please find here the meaning of each parameter:

Henrik Ingo from the Open Database Alliance (ODBA) did an interview with Oli Sennhauser of FromDual about the European MySQL database landscape and its future. If you want to know more about our opinion in this matter please read here.

For more technical insight see also our former presentation: MySQL, where are you going?.

At a customer we had a nice example of how the MySQL Optimizer is cheating when used in combination with the MySQL Cluster. The customer had queries running not too slow in the development environment but when he tried them on the acceptance test environment (with more data) the query was running much too long which was unacceptable because this query can occur many times per second.

What has happened?

First of all we had a look at the execution plan of the query generated by the MySQL Optimizer:

EXPLAIN …

Uster, Switzerland – April 26, 2010 – FromDual has signed the Service Provider Partnership Agreement of the Open Database Alliance (ODBA).

FromDual is the first official ODBA consulting partner in Europe. The growing number of downloads and use of MariaDB, an improved and enhanced derivation of the MySQL database, also requires consultancy services for MariaDB in Europe.

We are excited to be working with ODBA as their first consulting partner in Europe says Oliver Sennhauser, Owner of FromDual. …

Our presentation MySQL, where are you going? of March 25 at the OpenExpo in Bern is now available in German and English.

When you have missed it, you can download it now from here…

The video recording should be available as well soon.

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hi Ivan

Thanks a lot for your wishes!

Indeed it started very interesting yet… :)

Oli

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hello Henrik,

Thank you very much for your wishes and all you have done already!

Oli