You are here

english

PrimeBase Technologies and FromDual form a Service-Cooperation for MySQL products

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 24x7 support service from their provider.

MySQL Cluster - Cluster circular replication with 2 replication channels

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:

How can I find what InnoDB version I am using?

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.

Impact of indices on MySQL

Taxonomy upgrade extras: 

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.

MySQL logon and logoff trigger for auditing

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.

MyEnv for MySQL Multi-Database set-ups

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.

How many warm MyISAM key blocks do you have?

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:

MyISAM key buffer

FromDual plans Advanced MySQL DBA Workshop

Taxonomy upgrade extras: 
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.

Which table is hit by an InnoDB page corruption?

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

MySQL Cluster Local Checkpoint (LCP) and Global Checkpoint (GCP)

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:

lcp_gcp.png

MySQL, where are you going?

Taxonomy upgrade extras: 

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

Logging users to the MySQL error log

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.

Can you trust your MySQL backup?

Taxonomy upgrade extras: 

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.

What is CHECK TABLE doing with InnoDB tables?

Taxonomy upgrade extras: 

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.

MySQL on VMware Workstation/DRBD vs. VMWare ESX Server/SAN

Or an active-active fail-over 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 expensive...

The architecture looks as follows:

The battle against Oracle is probably over but has the real war begun yet?

Taxonomy upgrade extras: 

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

MySQL reporting to syslog

Taxonomy upgrade extras: 

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.

My wish for the New Year: MySQL DBA's, please install iostat on your servers!

Taxonomy upgrade extras: 

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.

MySQL useful add-on collection using UDF

I really like this new toy (for me) called UDF. So I try to provide some more, hopefully useful, functionality.

The newest extension I like is the possibility to write to the MySQL error log through the application. Oracle can do that since long. Now we can do this as well...

A list of what I have done up to now you can find here:

Using MySQL User-Defined Functions (UDF) to get MySQL internal informations

In one of my previous posts I was writing about how to read other processes memory [ 1 ]. As an example I tried to get the value of the hard coded MySQL internal InnoDB variable spin_wait_delay (srv_spin_wait_delay).

In this example we were using gdb or the operating system ptrace function to retrieve this value. This method has the disadvantage that it is pretty invasive.

Determine in MySQL if we are in summer time or winter time (daylight saving time, DST)

Recently a colleague at Sun was asking me if MySQL can tell him to determine if we are currently in summer time or winter time. He was doing some data analysis of his house where he has installed solar panels.

I am not aware of what he wants to do exactly, but possibly he wants all the data in solar time. So UTC could help him because UTC does not change much over time.

Next thing which came to my mind is, that possibly the good place to do such mathematical calculations is the application code and not the database.

Reading other processes memory

Taxonomy upgrade extras: 

As you probably have experienced yet MySQL does not always provide all internal information as you might want to have them and as you are used to have from other RDBMS.

MySQL plans to improve this implementing the/a performance schema and its probably already partly done in MySQL 5.4. But who knows when this will be finished and what it contains at all...

MySQL licenses for dummies

Taxonomy upgrade extras: 

The following summary shows my personal understanding of MySQL 5.1 licenses, packages and products. It does not necessarily reflect 100% the way MySQL understands it. But after all the discussions I hope it is as close as possible to the reality:

MySQL Embedded Database Server

(Download -> OEM Software)

Pages

Subscribe to RSS - english