FromDual TechFeed (en)

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

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:

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 …

[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

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

Hi Ronald, hi Fortxun,

Thank you very much for your wishes. I hope we will meet once in a while (again)…

Oli

Hello everybody,

One month earlier than planned we have the great pleasure to announce you that the company called FromDual goes operational today!

We are excited about this step and it is an new era in our personal evolution to get back in full-contact with customers and solve their real life day-to-day MySQL problems.

So we are happy hearing from you and to help you solving your individual MySQL problems…

You can find us at FromDual or you can contact us here.

Regards,
Oli Sennhauser (aka Shinguz) …

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

Hi Shlomi,

Thanks a lot for your input!

I am not a security specialist so I do not know what those guys who implement audit solutions think about your approach. But I can imagine that moving login information away immediately gives less possibilities to manipulate those data.

But as SUPER users are not logged it is halve baked anyway.

Using your approach in combination with the federated or federatedX Storage Engine would give again …

Problem

A customer recently showed up with the following problem:

*With your guidelines
[ 1
] I am now able to send the MySQL error log to the syslog and in particular to an external log server.
But I cannot see which user connects to the database in the error log.

How can I achieve this?*

Idea

During night when I slept my brain worked independently on this problem and in the morning he had prepared a possible solution for it.

What came out is the following:

• We create an UDF which allows an application to …

Today a customer with corrupted data files showed up. When we enquired a bit more he told us that he had a broken I/O controller. This is one of the worst things which can happen to you!

The reason is the following: When a I/O controller starts to die it often does not happen immediately. The controller dies slowly producing more and more corrupt data. When you just write data without checking or reading them it can take days or even weeks until you discover the problem.

But the nasty thing is, that even …

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

Hello kabel,

Thanks for the flowers!

Anyhow, logical backups with such an amount of data is nearly impossible to restore (in a timely manner)!

But if you rely on physical backup methods you do not get rid of the corruption. So one is trapped in this situation. :-(

Do you have an idea why you got the corruptions? Playing around with DRBD or anything similar on file system level? Or just upgraded to 5.1? How does the corruption …

Recently we had a case where a customer got some corrupted blocks in his InnoDB tables. His largest tables where quite big, about 30 to 100 Gbyte. Why he got this corrupted blocks we did not find out yet (disk broken?).

When you have corrupted blocks in InnoDB, it is mandatory to get rid of them again. Otherwise your database can crash suddenly.

If you are lucky only “normal” tables are concerned. So you can dump, drop, recreate and load them again as described in the InnoDB recovery procedure …

Or an active-active failover cluster à la VMware.

Today I have learned about a totally crazy/cool looking architecture where the expensive VMware ESX server was replace by a free/cheap VMware Workstation version in combination with DRBD.

Basically DRBD we name the poor man’s SAN and that is exactly what this customer is doing. He replaced the SAN with DRBD and now he can easily move one VMware instance to the other host. Possibly it is not that flexible and powerful as an ESX Server but also not so …

According to different sources from the web the decision about the Oracle - Sun merger has been approved by the European commission soon. So at least in the West it is clear what is going on. Let us see what the East decides… [ 1 ], [ 2 ].

Oracles arch-enemy Microsoft has already brought its weapons in position against the target with its: Microsoft offers Oracle-phobes MySQL migration tool" [ 3 ], [ 4 ]. So far so good. Nothing new, nothing special.

What made me a bit edgy was the following …

There are 2 different possible situations you can face when you have to deal with MySQL and syslog:

• MySQL is used as back-end for syslog to store the logging information.
  [ 6
  ]
• MySQL itself should report to the syslog.

In this blog article we look at the second situation: How can you make MySQL reporting to the syslog.

Since the version 5.1.20 MySQL is capable to log to the syslog
[ 1
],
[ 2
]. This is done by the MySQL angel process mysqld_safe.

You can enable the syslog when you add the syslog parameter …

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

Hi Brian,

Thanks for the hint. Just added it!

HNY, Oli

iostat is a very handy tool to help you investigating what kind of performance problems you have. Especially your databases can cause a lot of troubles to your I/O system and thus it would be very nice if every DBA has installed iostat on all of his MySQL database servers.

Unfortunately most of the Linux distributions do NOT install iostat by default. This causes often unfortunate situations when you are in a MySQL consulting engagement or have a MySQL support case and ask the customer for the output of …