Final GSoC revision of SIGAR I_S plugin

The GSoC is about to end up and it is time to sum up what has been done within the last weeks.

News

*Error handling added. Plugin uses the following mechanism to gather and report errors. When we can't load/open SIGAR library or get any information from it we pass the error to user informing about that. On the other hand when only some information is not available we rather print warning about that to MySQL .err file.
Also we check for return value for each schema_store_record function.

*Plugin has been merged into the MySQL 6 server code. Now you can download and compile the MySQL server with SIGAR plugin already being there.

Downloads

Standalone SIGAR plugin is available at lp:~m.ch/mysql-server/sigar-plugin.
SIGAR plugin merged into the MySQL server is available at lp:~m.ch/mysql-server/mysql-6.0-sigar-plugin.

Updated installation guideline and documentation is available in README file.

I hope you will like it and enjoy using it.

First working revision of SIGAR information schema plugin is available

Intro

The first working version (0.4.3) of SIGAR I_S plugin is now available on Launchpad. You are welcome to download and give it a try:
bzr branch lp:~m.ch/mysql-server/sigar-plugin
As this product is still in development stage you probably don't want to install it on you production server. I'd rather suggest to play with it on some non-important MySQL server instance.
So what actually this plugin does. It uses Hyperix SIGAR library to gather and analyze operating system information. As SIGAR library is OS independent we can gather information on different unix platforms as well as on Windows family OS.
What this plugin is not. It's not a way to start or stop OS services, interrupt its work or affect it in any way.

Features

1. You don't need to install or compile SIGAR library yourself as the source files come already with SIGAR files and do all the dirty work for you.
2. At the moment plugin comes as a standalone program (in the future it's going to be merged into the latest MySQL server revision).
3. It's distributed with autotools support so that you can compile and install it easily on almost any unix machine (standalone installation is not supported on Windows so far).
4. The following plugins are available with this revision:
OS_SUMMARY
OS_USERS
OS_MEMORY_RAM_SWAP
OS_CPU
OS_DISK_USAGE
OS_FILESYSTEM
OS_RESOURCE_LIMITS
OS_NETWORK_INTERFACES
OS_NETWORK_INTERFACES_STAT
OS_NETWORK_CONNECTIONS
OS_NETWORK_ROUTES
OS_PROCESSES
OS_PROCESSES_STAT

Installation

Installation details could be found in README file.

If you think something should be improved or you find anybug don't hesitate to leave a comment or e-mail me.

First plugin implemented

This week the first plugin OS_INFO was successfully compiled and build. Soon it will be available on Launchpad and everyone will be able to download the source code using Bazaar VCS client.
For the upcoming week I'm going to implement the next five plugins according to my tables design http://uosis.mif.vu.lt/~much1973/tables_revised_210508.pdf.
And here goes a report on what was done last week.

Report [2008-06-09 - 2008-06-16):

KEY ACCOMPLISHMENTS LAST WEEK
-- Because of my hardware issues had to move to a new one. Reinstalled OS (Windows and Ubuntu dualboot), SAndbox, MySQL servers. So that now everything is building and running cleanly. The main development is going on Ubuntu and I VirtualBox on Windows will be used to test plugin on other OS.
-- Investigated loader plugin crashes and learnt how to use gdb. I was posting to internals about the crashes and from the feedback I received it seems that MySQL doesn't support autoloading/unloading plugins. So for now I'll just implement all the tables I designed and later if there would time left I'll work on autoloading issue.
-- I switched from NetBeans IDE to GNU autotools. I had to spend some time learning the basics of autotools, but it was worth doing that. Now if someone would like to get, compile and build code himself all that he would need is gcc compiler automake and autoconf. No heavyweight Java or NetBeans framework. So I have a simple framework and the plugin's code could be compiled and installed with three commands: autoreconf --install, ./configure and make && make install.
-- Implemented OS_INFO plugin. It gathers information for x86 linux.

KEY TASKS THAT STALLED LAST WEEK
The issue with loader plugin. I'll leave it alone for now and work on the common plugins.

KEY CONCERNS
Posted to internals about my concerns and got feedback on that. So now none.

TASKS IN THE UPCOMING WEEK
Implement the following plugins:
-- OS_USERS
-- OS_MEMORY_AND_SWAP
-- OS_CPU
-- OS_HARD_DRIVES
-- OS_FILESYSTEMS

GSoC Week 2

KEY ACCOMPLISHMENTS LAST WEEK

I set up and started using Acunote. Have to admit it is a really great project planning tool. Also I created two accounts for Mark and Colin so that they can track how the things are going any time they want and probably leave comments. I encourage everybody to try it out. Moreover the service is absolutely free and has no limitations for GSoCers. All you have to do is sign up here - "http://www.acunote.com/open-source/summer-of-code-howto".

I set up Bazaar version control system and hosted project on Launchpad. As soon as there would be the first stable version available everybody could get the source code. To do that you will have to issue one simple command - "bzr branch http://bazaar.launchpad.net/~m.ch/+junk/sigar-plugin". For those interested in Bazaar here is a mini-tutorial - "http://doc.bazaar-vcs.org/latest/en/mini-tutorial/index.html".

Set up Sandbox and installed MySQL 5.1 and 6.04. As plugins become available in MySQL only since version 5.1 I don't need some older versions. Regarding 6.06 I still wasn't able to build it (building errors below). Giuseppe Maxia produced a really great tool for developers and should be credited for that. If you are using Sandbox here are some hints. First, if you prefer connecting to MySQL instance using GUI tools (as I do), in the Connection Manager's Details section you should change socket name. Usually Sandbox creates socket named mysql_sandbox_version_number in /tmp/. Another thing, if your MySQL instance crashes (like mine does:) and you want to restart it, you would want to go to Sandbox ./data directory and delete *.pid file. Otherwise when you run ./start command Sandbox will keep telling you that MySQL server is already started.

Now it is clear that the building errors were caused by bad hardware. I installed VirtualBox and tried compiling and building MySQL server under Fedora/Debian/FreeBSD. All attempts came up with segmentation fault or BSOD (the VirtualBox was installed on Windows). The same time I found the comment about segfault errors when compiling on Athlon XP processors (the same that I've got) with optimization -O3 turned on. I tried playing with it - turning optimization off -O0 or setting it to -O2. There were no segfaults but some time after building process was started the OS just stopped responding. At last i tried building on my laptop and there were absolutely no errors.

Plugin loader implementation was not so simple as I imagined. Although I am able to compile and run plugin loader it works not the way I want it to do. My idea was to put some instructions in the plugin's init() functions, which would load another plugins. I tried two different approaches. 1. Using mysql_install_plugin() function, which is defined in sql_plugin.h and 2. using simple system("install plugin ...") function. Both of them fail when put into the init() function. But when we put them into plugin's fill_table() function they seem to work just fine.

KEY TASKS THAT STALLED LAST WEEK

The plugin loader implementation needs to be done the right way.

KEY CONCERNS

None this week.

TASKS IN THE UPCOMING WEEK

-- Get the stack trace of the server crash when triyng to install plugin loader with instructions to load another plugins put into init() function. Then I'll post the stack trace to internals mailing list and hope to get feedback on that.-- While waiting for the response on server crashes start implementation of simple plugins. I'm going to implement as much of them as I can this week. I'll be starting with the first one which is OS_INFO.-- I'm going to use autotools instead of Netbeans and Java framework. I'm not experienced with that so I will have to study some manuals on automake and autoconf.

GSoC Week 1

This week I had to face one but very weird problem. Each and every time I tried to compile and build MySQL server on Ubuntu 7.10 from source files the process ended up with errors. Most commonly it was Segmentation fault error but sometimes the OS just stopped responding (something like BSOD on Windows).

I followed the instructions on MySQL Installation Using a Source Distribution. The tools I use are newer than suggested there so I doubt they cause such an error. Also i tried to ./configure with different keys as suggested but with no luck. Now below you will see that the error occurs while compiling different sources. My concern is that the issue is with OS or my hardware. I was running memtest86 for a couple of hours and it says the RAM is ok. So after dozen of times doing cleaning and building I'm really close to give up and to reinstall the OS, but I hope there exist some more elegant workaround.

Actually I was able to get the header files I need and install MySQL server from binary but they are of version 6.0.4 and my intention was to use the most recent source files from development tree.

And here go the logs just before the error occured for MySQL 6.0.4 alpha (which actually should compile & build with no problems) and MySQL 6.0.6.


g++ -DHAVE_CONFIG_H -D_REENTRANT -D_PTHREADS -DENGINE -DSTORAGE_ENGINE

-DNAMESPACE=Nfs -I. -I. -I../../include -I../../include

-I../../include -I../../regex -I../../sql -I. -I./TransformLib

-DMYSQL_DYNAMIC_PLUGIN -O3 -fno-implicit-templates -fno-exceptions

-fno-rtti -fimplicit-templates -Wno-invalid-offsetof -fexceptions -MT

ha_falcon_la-ha_falcon.lo -MD -MP -MF .deps/ha_falcon_la-ha_falcon.Tpo

-c ha_falcon.cpp -fPIC -DPIC -o .libs/ha_falcon_la-ha_falcon.o

ha_falcon.cpp: In destructor 'StorageInterface::~StorageInterface()':

ha_falcon.cpp:395: internal compiler error: Segmentation fault

Please submit a full bug report,with preprocessed source if appropriate.

See <http://gcc.gnu.org/bugs.html> for instructions.For Debian GNU/Linux specific bug reporting instructions,see <file:///usr/share/doc/gcc-4.1/README.Bugs>.

The bug is not reproducible, so it is likely a hardware or OS problem.

make[3]: *** [ha_falcon_la-ha_falcon.lo] Error 1

make[3]: Leaving directory `/home/muslim/Desktop/mysql-6.0.4-alpha/storage/falcon'

make[2]: *** [all-recursive] Error 1

make[2]: Leaving directory `/home/muslim/Desktop/mysql-6.0.4-alpha/storage/falcon'

make[1]: *** [all-recursive] Error 1

make[1]: Leaving directory `/home/muslim/Desktop/mysql-6.0.4-alpha/storage'

make: *** [all-recursive] Error 1





The same happens if I try to compile the source files from development tree for MySQL 6.0.6.

gcc -DDEFAULT_CHARSET_HOME=\"/usr/local\" -DDATADIR=\"/usr/local/var\" -DDEFAULT_HOME_ENV=MYSQL_HOME -DDEFAULT_GROUP_SUFFIX_ENV=MYSQL_GROUP_SUFFIX -DDEFAULT_SYSCONFDIR=\"/usr/local/etc\" -DSHAREDIR=\"/usr/local/share/mysql\" -DUNDEF_THREADS_HACK -DDONT_USE_RAID -I. -I../include -I../include -I../include -O3 -DUNIV_LINUX -MT ctype-utf8.lo -MD -MP -MF .deps/ctype-utf8.Tpo -c ctype-utf8.c -fPIC -DPIC -o .libs/ctype-utf8.o
ctype-utf8.c: In function 'my_casedn_str_utf8mb3':
ctype-utf8.c:3217: internal compiler error: Segmentation fault
Please submit a full bug report,
with preprocessed source if appropriate.
See for instructions.
For Debian GNU/Linux specific bug reporting instructions,
see .
The bug is not reproducible, so it is likely a hardware or OS problem.
make[2]: *** [ctype-utf8.lo] Error 1
make[2]: Leaving directory `/home/muslim/Desktop/bk-client2.0/mysql6a/libmysql'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/home/muslim/Desktop/bk-client2.0/mysql6a/libmysql'
make: *** [all-recursive] Error 1


Report [2008-05-26 - 2008-05-02):
KEY ACCOMPLISHMENTS LAST WEEK
-- I spent most of the time setting up the operating system (Ubuntu 7.10), getting packages and tools that i need. After that I was trying to compile and build MySQL from source files and had some issues there. Anyway I was able to biuld and install my first plugin yay and it worked. But it was done a really weird way so I need to find another workaround. -- I have set up the blog and started posting there. And today that would be my third post. Not too much but it's only the beginning.
KEY TASKS THAT STALLED LAST WEEK
If you look at my tasks from the last week you see that I was hoping to move a little further than I am right now and end up this week with simple plugin loader. But unfortunately setting things up took more time than I expected. This only means that I have to work harder and spend more time on my project.
KEY CONCERNS
Now my concern (I'll be posting that to internals list) is about MySQL server building. After trying to build different versions I'm almost sure the problem is not with MySQL sources but with OS or my hardware (hope it's not the hardware). Everytime I try to compile and build it I get the weird segmentation fault error or the OS just stops responding.
TASKS IN THE UPCOMING WEEK
-- The most important is to make everything building without any unnecessary overhead. I hope I get a response from the community on that.-- Set up bazaar repository and get it working.-- Blogging and hopefully get the community feedback.-- Build the plugin loader.-- Post my issue with compilation to internals mailing list.-- Try out sandbox.

Intersection in MySQL

Recently I had to deal with one task which required to do two sets intersection. For example, if we have set A = {a, b, c} and set B = {b, c, d} the result should be set C = {b, c}. So the question was how do we do that with MySQL. My little investigation and few minutes of googling showed that there is no special command for set intersection in MySQL (like INTERSECT) or set difference, although there is one for set union (UNION. Now how come that there is one for union, bot none for other set operations. Unions are used more often?!). Instead, there is some tricky workaround using different kinds of JOINs.
Now for my task, I had one simple table words shown below:
-------------------------
word_field \\ link_field
-------------------------
a \\ link1
a \\ link2
b \\ link1
b \\ link3
c \\ link1
---------------------

Suppose, given some "word_field" values, we need to find all "link_field" values which are common for them. To make it clear, given "a" and "b" the answer would be "link1". I was able to produce some ugly looking query:

SELECT DISTINCT w1.link FROM
(SELECT * FROM words WHERE word = "a" OR word = "b") AS w1
JOIN words w2 ON w1.link = w2.link AND w1.word <> w2.word
AND (w2.word = "a" OR w2.word = "b");

I do strongly believe there exist some nice solution, which even could get rid of join, but the query above just worked for me and gave the correct results. With the lack of time and exams approaching I hadn't any chance to optimize it.
Now my concern is that how useful would be the implementation of "INTERSECT" command for MySQL. Let's say tables R and S both are on disk then it would take the same amount of disk I/O for JOIN and INTERSECT as they both will need to bring all the tuples of those two tables. On the other hand, the query
(SELECT ...) INTERSECT (SELECT ...)
makes more sense than
SELECT (JOIN (JOIN)).

The Week Before

The end of May - such a lovely time of the year and a bit of a hell for each and every student. But this year it is completely different for me in the way I was selected for GSoC (omg still cannot believe I was). Anyway it is exactly one week left before the coding for GSoC 2008 starts. The right time to analyze what has been done so far and what should be done during the summer.
The first step that I took the day I was selected I started reading the MySQL documentation. No way! I let myself to relax and enjoy the life for a few days. And after that the work on the project started. And there are few things that i just have to point out. The most important one is when you start hacking into something large and new for you is a proper documentation. From this point of view MySQL documentation is just a perfect example of how things should be done. You can find almost everything related to MySQL server under documentation and internals links. The coding rules are nice and clear - exactly how I like.
The next step was investigating SIGAR API. The class structures their methods and return types are well documented. So the guys from Hyperic really deserve a big thanks, as they are doing a really great job and what is most important made my life much easier. :)
After years of coding in OO languages, going back to old C philosophy is a really hard trial. Actually for the first time you will feel yourself surrounded with enemy structures and functions which never fire an exceptions and never care about cleaning garbage after themselves but instead prefer to amuse you with unexpected stack overflows. But after you spend some time coding C you start feeling the real power of this language and the spirit of ancient code guru standing behind you. But seriuosly when we deal with DBMS the main thing we consider is how fast it operates. And nobody cares how nice your code is if it takes two hours to execute simple SELECT statement. At this point I said bye to Java and installed Netbeans C/C++.
And the last but very important thing is the research on the information_schema plugins which was done by Mark Leith and Roland Bouman. Their work helped me to better understand the basics of plugin implementation.
And now that's what you have been waiting for - the report itself. :)

Report [2008-04-21 - 2008-05-19]:
KEY ACCOMPLISHMENTS LAST WEEK
Reading, reading and after that reading again. I spend a lot of time reading the MySQL Internals guide and the MySQL 6.x server documentation. That let me gain basic concepts of information_schema database, plugins API and on how the coding is being done with MySQL. After that I made an investigation on SIGAR API. Keeping apart all the "hardest" work like IDE installation and MySQL source code downloading, I have been mostly playing with creating a simple Hello World plugins. I came up with the basic table model that is available here. (http://uosis.mif.vu.lt/~much1973/table_design.pdf)

KEY TASKS THAT STALLED LAST WEEK
Fortunately none.

KEY CONCERNS
I like the code I create to be OS independent. That's actually one of the things why I adore Java. When I started hacking deeper into the plugins implementation, I noticed that plugins cannot be used under the Windows OS. Well actually they can, but to do that you will certainly need to compile plugin's sources along with the server source code. So every time when plugin's code is being changed you will need to recompile them both. That means one thing - plugins could not be loaded at runtime they have to be built-in once and forever. The good news are that MySQL team is working on changing the way MySQL interacts with Windows API.
My next concern is about loading multiple tables. Each plugin corresponds to exactly one table in the information_schema database. As you can see above the table design includes multiple table instances. So we really need to find a way to load them all with a single "install" command (as from the end-user point of view you want the things to be done quick and without too much overhead). Fortunately, there is a very simple and nice workaround (suggested by my mentor) . We simply load the so-called loader plugin, which in turn installs all the plugins/tables we need.

TASKS IN THE UPCOMING WEEK
The primary target is finalizing the table model. As I pointed out above it is almost ready and what it needs - just a few final touches and ok from my mentor Mark Leith. The columns need to be logically grouped and sorted, a new table os_resources should be introduced and mysql_info should be converted to instance-like representation.
The second goal is to prepare myself for the actual beginning of coding. As I certainly need to have a starting point, around which the rest of the code will grow. For now I consider implementing the "loader" plugin to be that point. It will become absolutely clear by the end of this week, so you can expect an extra report with an update on that.