Wednesday, December 30, 2009

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

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 iostat. In some cases they are not willing or allowed to install iostat on their production systems on the fly (even though I never have seen it causing troubles during or after the installation).

Further iostat is also not too easy to find because is is hidden in the sysstat package. But iostat can be easily post-installed as follows:

shell> sudo apt-get install sysstat
shell> yum install sysstat
shell> rpm -i sysstat-<version>.rpm
shell> dpkg -i sysstat-<version>.deb
shell> emerge -avq sysstat
shell> ./configure ; make ; make install

So my second wish for the New Year is: Linux distributions, please add sysstat to the default installation/base packages!

My preferred way running iostat is:

shell> iostat -kx 1

More about what iostat tells you later on this channel...

For more details go here:

[1] sysstat
[2] iostat man pages

Monday, October 19, 2009

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:

If you have some more suggestions, please let me know.

The complete details you can find here.

Thursday, October 15, 2009

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.

When I was working on a customer support case I had the idea to solve this by the much less invasive method of User-Defined Functions (UDF).

UDF were introduced in MySQL 5.0 [2]. They provide the feasibility to enlarge the MySQL functionality by adding external code.

The clue is now that you also can use this external code to do some MySQL internal stuff.

My idea was now, instead of using gdb/ptrace to get the value of spin_wait_delay, to write and UDF to get and set this value.

More details about the UDF itself, how to compile and load it you can find on my website [3].

Then the UDF has to be loaded and activated in the database:


To remove the UDF again you can use the following command:

mysql> DROP FUNCTION spin_wait_delay;

To check if an UDF is installed or to see which ones are installed the following command gives you the right answer:

mysql> SELECT * FROM mysql.func;
| name            | ret | dl                     | type     |
| spin_wait_delay |   2 | | function |

When the UDF is compiled and properly loaded into the database you can get the value of spin_wait_delay as follows:

mysql> SELECT spin_wait_delay();
| spin_wait_delay(5) |
|                  5 |

And now the real nice thing is that you can even set this value as follows:

mysql> SELECT sping_wait_delay(8);
| spin_wait_delay(8) |
|                  8 |

With this function we can make a static hard coded InnoDB value dynamically changeable. To make it permanent also after a database restart possibly the functionality of init_file could help you further [4].

With this concept we can think about implementing many missing things without touching the MySQL code itself or recompiling MySQL. Please let me know what is missing in your opinion and I can try to implement it. Because I am not a programer the help of those guys would be very appreciated.

If anybody sees a problem with this method please let me know. I do not know about such things like thread safe and mutexes etc. But I think at least reading should not harm.

Caution: When you have a crash in your UDF the whole MySQL server will crash. So be careful and test it intensively!

Binary (md5 807c6bc09b5dc88a8005788519f2483a)

Friday, October 2, 2009

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 math calculations is the application code and not the database.

But never the less I was interested in how to solve this IN the database.

By default your MySQL server relies on your servers time zone. [1]

So if your server is set-up correctly you should be capable to determine if you are in summer time or winter time by your current time, UTC time and the offset you have to UTC.
mysql> SELECT IF(ROUND(TIME_TO_SEC(SUBTIME(TIME(SYSDATE()), UTC_TIME())) / 3600, 0) = 2, 'summer time', 'winter time') AS time;
Have fun calculating how much power is produced by your solar panels according to winter or sumer time...

If you have smarter solutions please let me know.

[1] Time zone support
[2] Date and time functions

Friday, August 14, 2009

Reading other processes memory

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

What is not provided to me I want to gather myself... But how? Other RDBMS provide interfaces to attach applications directly to their memory to retreive information. But MySQL does not. So I was looking for a way to read an other process memory.

I have no clue about programming and thus changing MySQL code was out of focus. Further I am looking for a solution you can use immediately on a running systems at consulting gigs. Some tries to read /proc/<pid>/mem with a little php script failed.

An article by Domas M. helped me. I do not have to write something myself I can use a tool already exsting to do the work. But gdb is not installed on every machine and usually not at all on production machines. Further gdb is probably an overkill to just read memory of other processes.

But an other application to do this job I did not find. I just found some comments that ptrace is the way to do it. Ptrace (man ptrace) is not a program (as for example strace) but an operating system function call.

When you are interested how I found out how to do it please continue reading here.