You are here

english

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.

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.

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?

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?

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.

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)

Why does MySQL Cluster takes so long for --initial?

Taxonomy upgrade extras: 

This week we had a very interesting problem at a customer: They complained, that their MySQL Cluster takes about 1 hour for a --initial start-up. After some tuning on the hardware and the config.ini they brought it down to around 40 minutes. But this is still unacceptable long...

This sounds a little strange to me. But let us have a look at their config.ini first. It looked more or less like the following (which is already tuned!):

MySQL Multi-Master – Single-Slave – Replication (Episode 2)

Introduction

One of the features that make MySQL so great is its easy replication set-up. If you are experienced and know-how to do it, it takes you about 15 minutes to set-up a slave. What you have in the end is a replication from one master to one or several slaves. So you can build a top-down data stream pyramid and spread your data on many slaves.

MySQL Replication

Test application for MySQL high availability (HA) set-up

When I set-up a MySQL HA environment for customers I usually do some final fail over tests after configuring the whole beast.

To check if the application behaves like expected I always run my little test application (test.sh) from the server(s) where the customers application runs. It displays "graphically" how the application behaves and you can show to the customer immediately what is going on...

Make sure, that you point it to the virtual IP (VIP) or the load balancer (LB).

Citation of the week

Taxonomy upgrade extras: 

Das dreieckige Rad hat gegenüber dem viereckigen einen gewaltigen Vorteil: Ein Rumms weniger pro Umdrehung!

Translation:

The triangular wheel has one enormous advantage over the quadrangular: One knock less per revolution!

Maybe not new, but I have not heard it yet and I love it. It was about reinventing functionality in a well known product...

Pages

Subscribe to RSS - english