Feed Aggregator

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

Hi Roland,

Ups! It seems like I did not look into it well enough… :( Thanks for your hint!

Just found the [http://www.mysqludf.org/ UDF repository] on your site. Great stuff…

Windows? What is this? No honestly: I did not try this out. There is no such system close to me nor have I a Win compiler or such stuff… Sorry!

[http://www.blogger.com/profile/13365137747952711328 Roland Bouman] said…

Hi there,

I did this too once (http://rpbouman.blogspot.com/2008/07/writing-to-mysql-error-log.html)

I’m just wondering, does your implementation work in case you’re running under windows as a service (bug #35916) ?

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, BIN … kbyte)
• Send message to the MySQL error log: udf_log_error-5.1.30-linux-i686-glibc23.so (md5 dc8ef3e91cf6dec84ab3ad95626ec9b5, BIN … kbyte)
• …

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

The complete details you can find here.

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

I was told that spin_wait_delay is exposed to public with the InnoDB pluggable storage engine 1.0.4. Seems I have to look for an other hidden variables now. :)

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

Hi Papagnome,

You are absolutely right, UDF’s came in in 3.23 already (I was told). What was in my mind was more the pluggable stuff. This feature is very nice an pretty new. But somehow I did not manage to express what I wanted to… :)

Regards, Oli

[http://www.blogger.com/profile/04939369461678423022 papagnome] said…

Just FWIW, UDFs existed in 4.1 and I’m pretty sure in 4.0 as well.

Thanks for the nice writeup!

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

Hi Mark,

Thanks a lot for your feedback. And how is the correct way to make it version IN-dependent and thus better portable?

[http://www.blogger.com/profile/15864507044869250062 Mark Robson] said…

I also tried to use mysql UDFs to call internal MySQL APIs. It works.

All you really have to do is include the relevant headers, call the appropriate functions, and it just works.

But it is a bit dangerous, as your compiled UDF will then be dependent on the ‘‘’exact’’’ MySQL version that you’re using.

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

The code for the UDF looks as follows:

/*

  CREATE FUNCTION spin_wait_delay
  RETURNS INTEGER SONAME "udf_spin_wait_delay.so";
  SELECT …

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

Andrew pointed me to the following blog entry of Brian: [http://krow.livejournal.com/481313.html Daemon Example (deamon plugin)]

Thanks!

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

Hi Björn,

Thanks a lot for you post. Much better than mine! :)

Shinguz

[http://www.blogger.com/profile/12606038649636876830 arathorn2005] said…

Nice approach!

Try this:

SELECT IF(TIMESTAMPDIFF(HOUR , UTC_TIMESTAMP(), SYSDATE()) = 2, ‘summer time’, ‘winter time’) AS TIME;

The keywords are longer, but all in all this maybe slightly easier to read.

Björn

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.

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

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 …

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

A colleague pointed out that I was missing some information:

The operative worklog task is WL#2360 and its dependencies, WL#4034 is a raw-idea bin item of no current significance.

The complete specification of the performance schema can be found here:

[http://forge.mysql.com/worklog/task.php?id=2333 WL#2333: SHOW ENGINE … LOCK STATUS] [http://forge.mysql.com/worklog/task.php?id=2360 WL#2360: Performance Schema] [http://forge.mysql.com/worklog/task.php?id=2515 WL#2515: Performance statements] [http://forge.mysql.com/worklog/task.php?id=3249 WL#3249: SHOW PROCESSLIST should show memory] [http://forge.mysql.com/worklog/task.php?id=4674 WL#4674: PERFORMANCE_SCHEMA Setup For Actors] [http://forge.mysql.com/worklog/task.php?id=4678 WL#4678: PERFORMANCE_SCHEMA Instrumenting File IO] [http://forge.mysql.com/worklog/task.php?id=4813 WL#4813: PERFORMANCE_SCHEMA Instrumenting Stages] [http://forge.mysql.com/worklog/task.php?id=4816 …

[http://www.blogger.com/profile/07820955267400574921 Morgan] said…

I just read what I wrote, sorry for not making sense. What I meant:

The ‘‘‘default’’’ INNODB STATUS method of showing statistics is for anywhere between 0 seconds to 60 seconds makes it difficult. 0-10 seconds is usually useless because it averages too much, and it’s entirely unpredictable what interval InnoDB uses.

Your script allows me to just get the current value (not an average). Brilliant!

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

Hi Morgan,

Do not get this. With the script read_process_memory.sh you can sample at a specific interval with a timestamp in the record and write a *.csv. That must be ideal for Cacti or any other monitoring/graphing tool, is it not? You could even extend it to write directly to a database…

Please elaborate a bit more your concern.

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 retrieve 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 existing to do the work. But gdb is not installed on …

[http://www.blogger.com/profile/07820955267400574921 Morgan] said…

Very nice! I can see the real use with some of the InnoDB stats. The random sampling interval makes it very difficult to get good numbers to use for cacti/RRDTool graphs.

[http://www.blogger.com/profile/07511835531671315487 Daniel] said…

knetknight

I’m not to hot on MySQL but I would have thought 5. repair table would be avoided except as a last result?

If there is corrupted data would it not be better to restore from a backup and apply logs?

[http://www.blogger.com/profile/07820955267400574921 Morgan] said…

You might want to add that Community Edition has partitioning.