MySQL reporting to syslog

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

1. MySQL is used as back-end for syslog to store the logging information. [6]
2. 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 to the MySQL configuration file (my.cnf) in the mysqld_safe section:

[mysqld_safe]
syslog

Currently MySQL is not capable to log to more than one logging facility at the same time. So you have to decide if you want to log either to the error log or to the syslog.

If you specify both, syslog and error-log, at the same time you will receive an error message if you start mysqld like this:

bin/mysqld_safe --defaults-file=/etc/my.cnf

But I assume that most of the MySQL users are using some kind of start/stop wrapper script like the mysql.server as follows:

/etc/init.d/mysql start

or

rcmysql start

So you will never see the error message indicating you having a conflict between the syslog and the error log. And you are possibly wondering why it is not logging to the syslog. For this problem I have filed a bug report [3].

If you cannot wait for the fix, this excerpt of mysqld_safe should help [8]:

364 if [ $want_syslog -eq 1 ]
365 then
366 # User explicitly asked for syslog, so warn that it isn't used
367 logging=file # This line you have to add!
368 log_error "Can't log to error log and syslog at the same time. Remove all --log-error configuration options for --syslog to take effect."
369 fi

Logging to an other logging facility than daemon

Mysqld_safe uses the logger command to log the error messages to the syslog. With ps you will find a command which looks like this:

logger -t mysqld -p daemon.error

The logger command uses 2 parameters -t for tag and -p for priority. The tag can be influenced with the syslog-tag parameter [4].
The priority parameter configures into which facility and on which level the message should be logged.

An exceprt from the logger man page [7]:

Enter the message with the specified priority. The priority may be specified numerically or as a ''facility.level'' pair. For example, ''-p local3.info'' logs the message(s) as informational level in the local3 facility. The default is ''user.notice.''

Unfortunately the logging facility is hard-coded in the the mysqld_safe script as daemon.error and daemon.notice. It would be nice to have this parameter configurable as well thus I have filed a feature request for it [5].

When you cannot wait, this code snippets from mysqld_safe possibly will help you [8]:

26a27
> syslog_facility=daemon
110c111
<>&2
---
> log_generic $syslog_facility.error "$@" >&2
114c115
<> log_generic $syslog_facility.notice "$@"
128c129
< cmd="">&1 | logger -t '$syslog_tag_mysqld' -p daemon.error"
---
> cmd="$cmd 2>&1 | logger -t '$syslog_tag_mysqld' -p '$syslog_facility'.error"
189a191
> --syslog-facility=*) syslog_facility="$val" ;;
366a369
> logging=file

With the modified mysqld_safe you can change your MySQL configuration file (my.cnf) file as follows:

[mysqld_safe]
syslog
syslog-facility = local3

to log to the local3 logging facility for example.

To activate this facility you possibly have to adapt your syslog configuration file (in my case: /etc/rsyslog.d/50-default.conf, in other cases: /etc/syslog.conf) as follows:

local3.* /var/log/database

To make these changes active you have to restart the syslog daemon:

kill -KILL $(cat /var/run/rsyslogd.pid)

In my case kill -HUP was not strong enough because it did NOT display configuration errors in the log file.

After the restart of the syslog daemon you should find the created empty log file and you can test if the logging works with the following command:

logger -p local3.info test

Now MySQL should log everything to your syslog facility local3.

How to make MySQL logging to the error log AND the syslog facility?

Under normal circumstances mysqld_safe can not log to more than one logging facility.

With the following syslog configuration you can make MySQL logging to both facilities, the error log AND the syslog. Add the following lines to your syslog configuration file:

$FileOwner mysql
$FileGroup dba
local3.* /home/mysql/product/mysql-5.1.42/data/error.log

# Set the owner and group back to its original values
$FileOwner syslog
$FileGroup adm
local3.* /var/log/database

Then restart the syslog daemon as described above and you will see logging to both location. The only drawback is, that the messages in the MySQL error log look like the typical syslog messages and not like the typical MySQL error log messages any more. But this should be somehow configurable with the syslog parameters in its configuration file [9].

If you need more assistance with logging to the syslog please feel free and drop me a line.

Literature

[1] MySQL error log
[2] mysqld_safe syslog parameter
[3] Bug #50083: error-log and syslog conflict in mysqld_safe is not reported to the log file.
[4] mysqld_safe syslog-tag parameter
[5] Bug #50080: syslog priority cannot be configured
[6] Writing syslog messages to MySQL
[7] Logger man page
[8] Modified mysqld_safe for advance syslog logging
[9] Examples

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

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:

• Query and change InnoDB spin_wait_delay: udf_spin_wait_delay-5.1.30-linux-i686-glibc23.so (md5 807c6bc09b5dc88a8005788519f2483a)


• Send message to the MySQL error log: udf_log_error-5.1.30-linux-i686-glibc23.so (md5 dc8ef3e91cf6dec84ab3ad95626ec9b5)


• ...

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

The complete details 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.

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:

mysql> CREATE FUNCTION spin_wait_delay RETURNS INTEGER SONAME "udf_spin_wait_delay.so";

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

udf_spin_wait_delay.so (md5 807c6bc09b5dc88a8005788519f2483a)

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

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.

MySQL licenses for dummies

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: enterprise.mysql.com -> OEM Software)
Classic (OEM license, -MEM -InnoDB)
Pro (= Classic +InnoDB)
Advanced (= Pro +Partitioning)

MySQL Community Sever (Download: www.mysql.com -> Downloads)
Community (GPL, -NDB)

MySQL Enterprise Server (Download: enterprise.mysql.com -> Enterprise Software)
Pro (GPL or commercial, -NDB +InnoDB +MEM, Basic + Silver customer, MRU + QSP)
Advanced (= Pro +Partitioning, Gold + Platinum customer)

MySQL Cluster (Download: http://dev.mysql.com/downloads/cluster/)
Community Edition (GPL, all features)
Com (ex CGE?) (OEM or commercial, -InnoDB +NDB)
Com-Pro (Com, all features)
Standard Edition (= Com, -NDB-API -Cluster-Repl, -LDAP)

Upgrade

EP customer should follow the QSP trail unless it is critical for them to install an MRU to get a quick bugfix to hold them over until the next QSP is released.

Month   version / release
    0   5.1.30
    1   5.1.30-MRU1
    2   5.1.30-MRU2
    3   5.1.31
    4   5.1.31-MRU1 and 5.1.30-QSP
    5   5.1.31-MRU2
    6   5.1.32
    7   5.1.32-MRU1 and 5.1.31-QSP

Legend

CE  - Community Edition
EP  - Enterprise Edition (why not EE?)
MRU - Monthly Rapid Update (EP only)
QSP - Quarterly Service Pack (EP only)
OEM - Original Equipment Manufacturer
MEM - MySQL Enterprise Monitior
CGE - Carrier Grade Edition

Please correct me, if I am wrong. And when you have more questions let me know and I try to clear this.