The acquisition of MySQL by Sun Microsystems has caused a certain amount of news in 2008 from the Initial announcement at the MySQL ACM, to the completion in just 6 weeks. It has been a very quick transition and while there is the potential for further opportunities with Sun as an employee within the MySQL product line and possibly other areas, I have elected to pursue my professional goals elsewhere.

My leaving MySQL Inc has been met with comments from “Woot!”, “Congratulations”, “Good Luck”, ‘That’s terrible”, “Are you sure”, “I’m shocked, but I don’t blame you” and attempts from multiple people to reconsider and reverse my decision. I was pleased to know that it took no time from my first contacting colleagues and friends within MySQL before the community knew via the grape vine, even before my attempts to contact people.

The most interesting comment was “That’s terrible news”, to which my response was, “Well, not for me”. My best compliment has been “You are a such a great team player and MySQL aficionado.”. I actually had to look aficionado up, which means “an ardent devotee; fan, enthusiast.” I am very proud that my commitment to the MySQL Product, and MySQL Community and while at MySQL Inc/Sun has been noted, if only a small footnote in the success of the company.

There are a number of reasons why I’m moving on, which I’ll not be detailing here. In summary however, first, I have already set my career goals for Ronald Bradford 2008 and this includes the type of work I’m wanting to pursue, the money, and an increase in writing, presenting and speaking on topics in MySQL and other technologies and interests. In particular, I’m working (slowly til now) on consolidating my online presence into an online identity at www.ronaldbradford.com. The second and deciding factor was as a result of the acquisition and transition period. The issues of, the offer and requirements to transfer to Sun played an important part in my decision, being disappointed and unhappy across various things in this process.

At this time I can say that I’ll be leaving the US by the end of March as part of my US visa requirement. I will however not be leaving the MySQL Community, in fact one of my goals is to be able to contribute to the MySQL Community more, as I did prior to joining MySQL Inc in 2006 and I’m finalizing details with a prominent European Open Source company, and I expect to have some news here soon when details can become confirmed. For my friends and colleagues attending the MySQL Conference in just 4 weeks time, I’ll be there, so this will a perfect chance to catch me while I’m in the US.

I am sorry to leave MySQL. I have made many close friends here, and indeed it’s the people that helps make a company and among my peers I have great respect. There is a unique spirit working with an open source company and as I had ties to MySQL prior to joining the company, I’m confident these will continue and grow as my involvement with MySQL continues, just differently and more in line with what I want to do.

Those of you that know me personally, any recommendations via my Linked In Profile at this time is most appreciated.

Recently I got to spend a day with Jan Kneschke at his home in Kiel, Germany. This was an excellent opportunity to interact directly with a fellow MySQL employee and resident expert in several fields. Doing this on my own vacation time enabled me to control what I wanted to achieve.

Jan uses Linux, Windows and Mac OS/X all actively and with Synergy enables a single keyboard and mouse to work seamlessly across his three screens. I knew you could do this, just last year I tried to find out the software that could do it without obvious success.

Of interest was our discussion in English, while he was also communicating on IRC in German during the day. Being multi-lingual is something we don’t often consider and talking in one breath in English, then having to type in German is a little more complex then as I’m writing it here.

One of the discussions was the impact of the Sun acquisition, but I can’t say anything here as it’s all confidential. I don’t want the MySQL legal police after me. It’s business as usual here are MySQL. However we both discussed several concerns we have, a common topic among many MySQL Employees here at MySQL.

I spent some time browsing Jan’s library. Books browsed included “MySQL Database Design and Tuning” (Amazon link). (Why as a MySQL employee I haven’t see this, or been given a copy to review/read/access I don’t know. I had heard of it). “SQL Performance Tuning” (Amazon Link) by Peter Gulutzan and Trudy Pelzer who are now both MySQL employees, and “SQL for Smarties – Advanced SQL Programming” (Amazon Link) by Joe Celko.

Within the MySQL Database Design and Tuning book I came across SQL_MAX_JOIN_SIZE, a handy per connection session variable to limit the size of poorly joined SQL statements. Problem is it’s not part of any resource control restrictions so if you can’t control connections to the database by reporting end users, then what’s the real point. A nice idea that’s only partially implemented for real use.

Part of my time here, is to get answers. Some of my specific questions were around Lua (The scripting language used with MySQL Proxy). One particular question was the ability to process a SQL request and response programmatically at startup (to manage some global data), but the way to achieve this is to piggy-back off the first actual SQL request for the connection.

Another question was in regards to the testing of newer Lua scripts. The solution is to see the test cases which manage the Proxy via Mock up functions.

On Saturday, there was another change of aspect. Jan over the weekend was needing to do some security updates with lighttpd. “You need to still respond to requests from the community or they have the impression the product is dead. It’s a burden, you can’t really step out.” I completely understand as for almost 10 years now I’ve provided support and service to a number of not-for-profit organizations. My usage requirements are now very low, I don’t make any more software or website changes, just the occasional web server issue. (My present primary server uptime is up 560 days, and this was only to do an OS level upgrade from the past x100 days.)

The lighttpd project has up to 6 other committers that add work from time to time. For any successful Open Source project you need to ensure this occurs, otherwise it’s something you can never leave. Interestingly, Lighttpd is still a popular web server as per the Netcraft Survey Results.

Jan wakes up to the sound of the coffee machine in the morning, literally. It’s rather a loud and predicable process.

Not all work, we attended the Nubit 2008 event by NetUSE AG in the evening for networking, dinner and drinks. In his spare time on Saturday I caught him playing Hellgate.

Part of interacting with other professionals is you come across tools and sites they use to benefit their productivity or functionality, and this type of viral exposure helps greatly in your experiences and knowledge transfer.

And just in closing, two bizarre technology occurrences here (just before the full moon). The first, my computer become intelligent. I must have found a magic key combination in vim because it was doing interactive spelling correction while typing. It was even doing correcting of variables names. It was a little unnerving and even Jan was surprised (so it’s probably not just a German thing). The second, my Timex watch of at least 15 years (with many band and battery replacements) reset it’s self for the first time ever. I looked and it had 12:05am, Mon 1.1 – Weird.

Jan will also be speaking at the upcoming 2008 MySQL Conference, you can read his profile.

Stay tuned for Parts 2 and 3 with Michael Zinner – Architect of MySQL Workbench and Paul McCullaugh – Architect of the PBXT Storage Engine .

You have to love error messages some times. Today in MySQL is was “ERROR 1289 (HY000): The ‘UNKNOWN’ feature is disabled;”

What was even more interesting is the error message and indeed the error number changed across different versions. I took the time to also review the error in the current versions of 5.0, 5.1 and 6.0.

5.0.37/5.0.51a – ERROR 1289 (HY000): The ‘InnoDB’ feature is disabled; you need MySQL built with ‘InnoDB’ to have it working
5.1.19/5.1.23/6.0.4 – ERROR 1286 (42000): Unknown table engine ‘InnoDB’
6.0.0 – ERROR 1289 (HY000): The ‘UNKNOWN’ feature is disabled; you need MySQL built with ‘UNKNOWN’ to have it working

You can read more at Bug #29373

As already reported the deal is done (see Sun Press Release, Kaj – Ambassador to Sun comments). I’ve had a look at the Official Sun-MySQL website.

Here is a review of the interesting words and images on the new site.

• Overview – Ultimate Scalability for the Web Economy (dolphin & sunset)
• Features – It Just works (windsurfer with island view)
• Tech Specs – Celebrate the Possibilities (skydiver on snowboard)
• Perspectives – Open, Fast, and Free Just Got Better (snowboarders viewing the mountain scape)
• Support – Unbeatable Duo – Open Source and Global Support (couple showing the V symbol)
• Training – Get Trained. Get Ahead. (Cyclists riding into the sunset)
• Get It – 60,000 downloads a day (surfers on a wave)

Well, they have very colorful images!

Words and images remain the property of Sun Microsystems, Inc. Copyright 1994-2008.
Comments on this site reflect the personal opinion of the author and may not reflect the opinion of any present or past employers.

I have been noticeably absent on my comments in this topic, something I was pinged about yet again today by another colleague.
Did I have nothing to say? No. It’s just I’ve not been able to say much, or actually do anything in the past 7 days due to strong bought of the flu which has kept me in bed near 5 days straight (coincidently aligning perfectly with MySQL’s recent ACM. Go figure that luck).

So what are my thought’s here. (Ok, I’m going to waffle a bit with my point of view, but my key technical points for those of you that just want that, scroll down to the next section heading)

Overall I believe it’s a good thing, on the surface and at the moment. As mentioned MySQL was on an IPO path, I’d would have liked the option to buy my own MySQL shares, be part of a company that got to that point (have worked for 2 failed startup Internet companies previously). I think there would have been many a proud MySQL employee,ex-employee, and user that day, and that’s a shame for the pinnacle victory of Open Source, to become something of worth, to see the light at the end of the tunnel, but be tempted by the fruits enroute. MySQL, like every startup has investors, they have invested in MySQL for quite some time, I’d think probably up to 10 years now, they obviously want their money too.

But the market out there has many larger players, those less understanding of Open Source, more of the corporate giants with larger shares $15 Billion in RDBMS revenues, and willing to either run over, or kill off a smaller weaker competitor by $ size. There has been some press saying it’s a a bad thing, Sun is dead, what a waste of money etc. If Sun did not purchase MySQL I’m sure there was a line that would. It doesn’t take a rocket scientist to think of names such as IBM, Microsoft & Oracle. All competitors ironically. One mentioned that was interesting was Yahoo? Had not thought of that, but in that line of thought, would Google do good, or evil.

I’ve worked more closely with Sun equipment in 2007. Indeed my longest engagement and Californian Client recently mentioned in the press as co customer of Sun & MySQL was a very tough engagement stressing both Solaris 10 and MySQL 5 (with custom built binary). (1) Stressing me too very much, regardless of the New York to San Francisco each week for a decent time. I was exposed to Dtrace, I was exposed to zero skills in MySQL, well perhaps one, but he would not return phone calls or emails, that’s just plain unprofessional even to a colleague. I was exposed the warts of legal not allowing me to get Sun help on DTrace even when that was offered by sUN and was to the clear benefit of MySQL. Luckily cooler heads, weeks (more then we had) and a lot of pain prevailed on this said point, but to my point, what a darn unnecessary pain to start with.

I see more of Sun around. I know of other consulting clients (of course we can’t mention), other large sites of Community collegues, e.g. Fotolog are Sun, be it MySQL 4.x days however. I’ve heard the Sun/MySQL relationship is not that good, that MySQL doesn’t work as well on Sun as it could, that there are clear performance gains that can be made under the Solaris OS.

And historically, I stated working with SUN in the early 90s, it might be good to return after my time with SunOS before there was Solaris. I think Solaris started at 2.5.

(1) I mention custom built binary because many people don’t realize, there are actually three paths of getting features into MySQL. Both the Community and Enterprise path are subject to Engineering and the constraints of “Feature Freeze” for example. A Custom built binary, can include a specific feature or features into a supported version of MySQL just for you. MySQL Professional Services has dedicate staff that work in these areas. Our consulting offerings are listed here

My Key Points (Some personal)

• Access to a benchmarking group, that’s gotta help. MySQL Inc the company has a number of shortcomings, and little movement to excite me, this is one of them. I’m sure Sun will want to provide MySQL this detail, and this can only be a key strength. (Sun people reading this, I want to help)
• Access to H/W. Ok, so I can’t get any benchmark results out of MySQL, I can’t even get access to several consistent machines to test stuff myself anyway (as if I had that time), but if I’d got allocated a few days a month of dedicated access to a bank of services I’d sure use it. Especially in the area of RAID performance, LVM/SAN Backup & Recovery. (Again Sun people, pick me, I especially want just a dedicated server to test multiple performance tests with different RAID configurations, and MOST IMPORTANTLY, provide figures of how long to recover a degraded system under load. Want the same under a SAN, but that become more political.
• Access to Dtrace resources. I see this tool as key in my next level of internal knowledge of the server. This will grant me better access to resources, Luckily my Macbook with 10.5 also has Dtrace, a recent new discovery, so that will make getting Solaris x86 Parallels VM a secondary priority. (Sun people listening, a Parallels/VMWare Solaris is on my wish list)
• Access to facilities. I live in New York. While I have no desire to want to go into an office day by day, to know now I can, say have meeting a room, an area to work for a few days distracted etc, is golden, and a plus for a larger company.
• Access to facilities for community events. This is gotta help or fledgling MySQL User Groups and MySQL Camp events. I really hope it can actually flow onto helping significantly in event organization taking the MySQL User Conference from it’s present limitations into bigger and better
• Access to a better expense system. MySQL’s system sucks. Sorry, no other word for it. Scorn me later. I’m at a disadvantage over many other MySQLer’s, a) I’m one of the few that travel all the time, b) I’m of even less few that live and travel not in my country or origin and so I’m confined to the limitations of said countries financial systems. Specifically I own property in Australia, have 20K CC, can’t get a CC to cover a month’s expeneses here, actually can’t get them to cover a week (already have a few). I can’t use my Australian ones effectively here, two problems, one MySQL could never sought out how to cover my “additional expenses” and I was paying out $150 per month on my own dime, plus I had to physically go to a bank and physically transfer money to Australia (all under a magical $10K AUD limit). When you travel every week, and when I had a brutal travel schedule for a while I was not able to actually get to bank for three weeks.
• If SUN doesn’t provide a corporate card, or handle expense of the two largest ticket items, airfares and accommodation, there will be hell to pay if I’m not re-reimbursed in very timely fashion. I have a corporate card with MySQL, a sore point actually, it took 12 months to get one, to no fault of my own.
• Employment Visa. As I mentioned my live at MySQL is more complex then others, I work here in the US under the E3 visa, that is tied to an employer. This will need to change, and that I’m sure is no trivial task.
• Changed Vacation Time (for the worse). MySQL has a very good Swedish Vacation policy, this goes. For an employee under 2 years my vacation time I hear is like a week less then 2+, and overall vacation time in general is less. They say more US based holidays and forced Christmas/New Year. I’m not the family guy, a day scattered here or there doesn’t work for me, I need blocks of time, the world is out there.
• Java. My core programming expertize is Java. (sh/awk etc would argue a strong case). My strongest non-scripting language is Java, I’ve missed not being able to remain closer to application development, even stack technology, this may provide some opportunity where consulting engagements are MySQL and development related and I can refresh this skills. Going to another Java One again would be bonus
• Additional Development/Testing Resources. Sun is a company 100 times the size of MySQL. Does this grant us dedicate resources that may be able to help the bleeding of MySQL. (Bleeding the time to market for features, quality therefore, and flexibility for growth). All things I consider still going backwards at MySQL. Additional procedures may make things worse, but MySQL I’m sure will remain a key different component for a long time.
• Community I’m going to end my points on this. MySQL in my opinion does not do right by the community. I have some specific views I’ll be posting soon on this point. How will community function under SUN, will be allowed to thrive, or will it’s path be dictated as it is now, buy a sales and marketing driven focus. There are some things that need to change in MySQL, this is one of them. Will Marten Mickos, the present CEO the head of the MySQL group at Sun see this and have better direction in store. What I’d expect is to see Sales and Marketing be re-organized (this seems most logical), and then that leaves community to return to a key component such as Engineering & Services and not a side thought. I came from the community before I joined MySQL, I am active (less so) in the community while at MySQL, I stand up for the community at MySQL (again more on this soon), and I’ll be part of the community long after I leave MySQL and/or Sun.

I’ve finally decided to work on a number of improvements in Instrumentation within the MySQL Server I’ve wanted for the first year, See What is the optimal thread specific buffer size?. It’s been a while since I’ve compiled from source, and from these issues, the first under Ubuntu 7.10 (a fresh install). Here are some of the problems, and solutions overcome, just for some others that may experience them.

I should have simply read my own notes from years ago in Compiling MySQL, specifically the pre-requisites list, but it sometimes helps to remember why things are so.

In summary, I needed the following:

apt-get install automake libtool g++ ncurses-dev

Thanks Miademora, I meant to say that, forgot

First error, “aclocal: not found”

$ ./BUILD/compile-pentium-debug
BUILD/check-cpu: Oops, could not find out what kind of cpu this machine is using.
+ make -k distclean
make: *** No rule to make target `distclean'.
+ true
+ /bin/rm -rf */.deps/*.P config.cache innobase/config.cache bdb/build_unix/config.cache bdb/dist/autom4te.cache autom4te.cache innobase/autom4te.cache
+ path=BUILD
+ . BUILD/autorun.sh
+ aclocal
BUILD/autorun.sh: 1: aclocal: not found
+ die Can't execute aclocal
+ echo Can't execute aclocal
Can't execute aclocal
+ exit 1

This needed automake.

$ apt-get install automake
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  liblzo1
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
  autoconf autotools-dev m4
Suggested packages:
  autoconf2.13 autobook autoconf-archive gnu-standards autoconf-doc
  automake1.10-doc
Recommended packages:
  automaken
The following NEW packages will be installed:
  autoconf automake autotools-dev m4
0 upgraded, 4 newly installed, 0 to remove and 24 not upgraded.
Need to get 1085kB of archives.
After unpacking 3899kB of additional disk space will be used.
Do you want to continue [Y/n]? y
WARNING: The following packages cannot be authenticated!
  m4 autoconf autotools-dev automake
Install these packages without verification [y/N]? y
Get:1 http://archive.ubuntu.com gutsy/main m4 1.4.10-0ubuntu2 [207kB]
Get:2 http://archive.ubuntu.com gutsy/main autoconf 2.61-4 [448kB]
Get:3 http://archive.ubuntu.com gutsy/main autotools-dev 20070306.1 [61.6kB]
Get:4 http://archive.ubuntu.com gutsy/main automake 1:1.10+nogfdl-1 [369kB]
Fetched 1085kB in 2s (515kB/s)
Selecting previously deselected package m4.
(Reading database ... 93930 files and directories currently installed.)
Unpacking m4 (from .../m4_1.4.10-0ubuntu2_i386.deb) ...
Selecting previously deselected package autoconf.
Unpacking autoconf (from .../autoconf_2.61-4_all.deb) ...
Selecting previously deselected package autotools-dev.
Unpacking autotools-dev (from .../autotools-dev_20070306.1_all.deb) ...
Selecting previously deselected package automake.
Unpacking automake (from .../automake_1%3a1.10+nogfdl-1_all.deb) ...
Setting up m4 (1.4.10-0ubuntu2) ...

Setting up autoconf (2.61-4) ...

Setting up autotools-dev (20070306.1) ...
Setting up automake (1:1.10+nogfdl-1) ...

Second error, “libtoolize: not found”.

./BUILD/compile-pentium-debug
BUILD/check-cpu: Oops, could not find out what kind of cpu this machine is using.
+ make -k distclean
make: *** No rule to make target `distclean'.
+ true
+ /bin/rm -rf */.deps/*.P config.cache innobase/config.cache bdb/build_unix/config.cache bdb/dist/autom4te.cache autom4te.cache innobase/autom4te.cache
+ path=BUILD
+ . BUILD/autorun.sh
+ aclocal
+ autoheader
+ test -f /usr/bin/glibtoolize
+ libtoolize --automake --force
BUILD/autorun.sh: 1: libtoolize: not found
+ die Can't execute libtoolize
+ echo Can't execute libtoolize
Can't execute libtoolize
+ exit 1

Using a nice Ubuntu feature, you can find the right package by typing the command.

$ libtoolize
The program 'libtoolize' is currently not installed.  You can install it by typing:
sudo apt-get install libtool
bash: libtoolize: command not found

This needed the libtool package.

$ apt-get install libtool
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  liblzo1
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
  libc6-dev linux-libc-dev
Suggested packages:
  glibc-doc manpages-dev libtool-doc g77 fortran77-compiler gcj
Recommended packages:
  libltdl3-dev
The following NEW packages will be installed:
  libc6-dev libtool linux-libc-dev
0 upgraded, 3 newly installed, 0 to remove and 24 not upgraded.
Need to get 4277kB of archives.
After unpacking 18.3MB of additional disk space will be used.
Do you want to continue [Y/n]? y
WARNING: The following packages cannot be authenticated!
  linux-libc-dev libc6-dev libtool
Install these packages without verification [y/N]? y
Get:1 http://archive.ubuntu.com gutsy-updates/main linux-libc-dev 2.6.22-14.47 [653kB]
Get:2 http://archive.ubuntu.com gutsy-proposed/main libc6-dev 2.6.1-1ubuntu10 [3287kB]
Get:3 http://archive.ubuntu.com gutsy/main libtool 1.5.24-1ubuntu1 [337kB]
Fetched 4277kB in 4s (900kB/s)
Selecting previously deselected package linux-libc-dev.
(Reading database ... 94193 files and directories currently installed.)
Unpacking linux-libc-dev (from .../linux-libc-dev_2.6.22-14.47_i386.deb) ...
Selecting previously deselected package libc6-dev.
Unpacking libc6-dev (from .../libc6-dev_2.6.1-1ubuntu10_i386.deb) ...
Selecting previously deselected package libtool.
Unpacking libtool (from .../libtool_1.5.24-1ubuntu1_i386.deb) ...
Setting up linux-libc-dev (2.6.22-14.47) ...
Setting up libc6-dev (2.6.1-1ubuntu10) ...
Setting up libtool (1.5.24-1ubuntu1) ...

Third error, “preprocessor “/lib/cpp” fails sanity check”

./BUILD/compile-pentium-debug
...
checking how to run the C++ preprocessor... /lib/cpp
configure: error: C++ preprocessor "/lib/cpp" fails sanity check
See `config.log' for more details.
$ more config.log
configure:5336: gcc -c -Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -
W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunus
ed-function -Wunused-label -Wunused-value -Wunused-variable -Woverloaded-virtual
 -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor  -felide-construc
tors -fno-exceptions -fno-rtti  -DUNIV_MUST_NOT_INLINE -DEXTRA_DEBUG -DFORCE_INI
T_OF_VARS -DSAFEMALLOC -DPEDANTIC_SAFEMALLOC -DSAFE_MUTEX    conftest.cpp >&5
gcc: error trying to exec 'cc1plus': execvp: No such file or directory

This needed the package g++.

$ apt-get install g++
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  liblzo1
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
  g++-4.1 libstdc++6-4.1-dev
Suggested packages:
  g++-multilib g++-4.1-multilib gcc-4.1-doc libstdc++6-4.1-doc
The following NEW packages will be installed:
  g++ g++-4.1 libstdc++6-4.1-dev
0 upgraded, 3 newly installed, 0 to remove and 24 not upgraded.
Need to get 3730kB of archives.
After unpacking 13.7MB of additional disk space will be used.
Do you want to continue [Y/n]? y
WARNING: The following packages cannot be authenticated!
  libstdc++6-4.1-dev g++-4.1 g++
Install these packages without verification [y/N]? y
Get:1 http://archive.ubuntu.com gutsy/main libstdc++6-4.1-dev 4.1.2-16ubuntu2 [1129kB]
Get:2 http://archive.ubuntu.com gutsy/main g++-4.1 4.1.2-16ubuntu2 [2600kB]
Get:3 http://archive.ubuntu.com gutsy/main g++ 4:4.1.2-9ubuntu2 [1440B]
Fetched 3730kB in 4s (871kB/s)
Selecting previously deselected package libstdc++6-4.1-dev.
(Reading database ... 95367 files and directories currently installed.)
Unpacking libstdc++6-4.1-dev (from .../libstdc++6-4.1-dev_4.1.2-16ubuntu2_i386.deb) ...
Selecting previously deselected package g++-4.1.
Unpacking g++-4.1 (from .../g++-4.1_4.1.2-16ubuntu2_i386.deb) ...
Selecting previously deselected package g++.
Unpacking g++ (from .../g++_4%3a4.1.2-9ubuntu2_i386.deb) ...
Setting up g++-4.1 (4.1.2-16ubuntu2) ...
Setting up libstdc++6-4.1-dev (4.1.2-16ubuntu2) ...
Setting up g++ (4:4.1.2-9ubuntu2) ...

Fourth error “checking for termcap functions library… configure: error: No curses/termcap library found”

This needed the package nurses-dev.

This got a successful compile. Again, should have started with the pre-requisites list. A review of the pre-requisites from previously showed gmake no longer necessary, the following is.

automake --version
autoconf --version
libtool --version
m4 --version
gcc --version
bison -version

So you want to know what join_buffer_size, sort_buffer_size, read_buffer_size and read_rnd_buffer_size values for your application should be? These MySQL thread specific buffers are variables I can never get right because there are insufficient metrics, instrumentation or even abstract details with the present MySQL Versions. These are important because Memory is a resource that you want to maximum towards your database data (the System Global Area), and not towards the Process Global Area in which there is no limitations.

I’ve wanted to know this answer for some time, I’ve asked many people including internal MySQL resources, I’d hoped that when joining MySQL more details would be available, but I’ve never been able to get an answer. I’ve always been meaning to work this out, it’s now 2008 and well the time has now come to do something about it.

The questions I want to answer include?

• When are these buffers used, i.e. which SQL Statements trigger these. Ok, you can work this out when you review the SQL + Schema but I want something that works for clients.
• I want to know if the buffers are fully allocated or incrementally allocated. My understanding is that for these 4 buffers, there are pre-allocated unlike internal temporary tables, but since reading some of the code I’m no longer completely convinced.
• I want to know what size of the buffer (if fully allocated) is actually used.

My initial goal is to add the following new status variables.

• sort_buffer_count, sort_buffer_usage
• read_buffer_count, read_buffer_usage
• read_rnd_buffer_count, read_rnd_buffer_usage
• join_buffer_count, join_buffer_usage

Now, it’s likely there will be some overlap, for example sort_scan, but nothing like introducing some consistency here.

There per session status variables will allow you when reviewing individual SQL statements to see the impact. Ultimately I’d like to add more smarts into MySQL, because things like “gathering all SQL Statements” can be difficult and often not possible in production. Combined with leverage MySQL Proxy we will get there.

I’m looking forward to getting in the bows of the MySQL Source Code, it’s going to be a steep curve as I do little development these days, less in C++ but nothing like a challenge and questions to a lot of close colleagues for small snippets of help.

What is the optimal OS partition layout for a database server?

I’ve seen so many different configurations for OS partitions of recent time, none to my satisfaction.
Historically, in Unix days 20 years agao, long before RAID and SAN’s all my experiences were for strongly defined partitions.
That is separate partitions for the OS /, /boot, /tmp, /usr, /var and then seperate partitions for effectively application and data with /home and /opt

Today what is optimal for an OS configuration on a database server.
I’m seeking the input and experiences of the community. I’m making the assumption of at least RAID 1 or better for all disks.

It’s obvious that the database partition must be separate, and given snapshot capabilities both the data and binary logs should be specified on the same partition for consistency.
It’s also obvious the /tmp filesystem should not be with the / file system. You never want anything stupid that is using the /tmp filesystem to affect your operational system.

I’ll make the following assumptions.

• Is a production database server
• You are not installing new software often, therefore /usr should remain relatively static.
• You have correctly configured MySQL not to place an data in /var.

This leaves /boot, /usr and /var for the OS. Do these require separate partitions? I would like to see it but do people care. With the amount of disk space available does a large amount bypass the need?

Any comments would be appreciated.

I came across a problem on site yesterday. In moving the development environment to a new server and creating more appropriate permissions for users (they were using ALL on *.*) I found that the Java application would crash with a NullPointerException. The permissions were standard, and calling the Stored Procedure worked via the mysql prompt.

CREATE USER [email protected];
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON devdb.* to [email protected];
CALL sp_test()

You can spend a lot of time looking into problems, luckily this development configuration had taken my advice to enabled the General Query Log. (Something everybody should do to know your SQL).

In closer inspection the following command was being sent to the MySQL Server. SHOW CREATE PROCEDURE sp_test; Attempting to run this command via the mysql prompt works.

SHOW CREATE PROCEDURE sp_test;
+-----------+----------+------------------+
| Procedure | sql_mode | Create Procedure |
+-----------+----------+------------------+
| sp_test   |          | NULL             |
+-----------+----------+------------------+

On closer inspection, the Create Procedure content contains NULL, seems suspicious. Running this command as the ‘root’ MySQL user I get the content of the procedure.

RFTM sheds light that this command requires privileges to mysql.proc. Running the following was necessary to get operation.

 GRANT SELECT ON mysql.proc to [email protected];

This requirement is rather stupid in my book. I’ve yet to determine why SHOW CREATE PROCEDURE is called, probably some requirement via Connector/J but this permission problem definitely needs fixing.

The MySQL Table Checksum, part of the MySQL Toolkit (having to be renamed soon) is an invaluable community tool for use with MySQL. Most sites or installations of any volume will use MySQL Replication for one purpose or another, be it for read scalability, backups, testing, upgrading etc.

Why is it needed?
There are two primary compelling reasons. First, MySQL replication is an asynchronous process and there is no absolute guarantee that the Master Database and the Slave Database are the same (By definition that can be different). Second, MySQL does not provide any tools relating to checking, managing, reporting differences. Luckily the community has addressed this present lack of product feature in current versions of MySQL. Special thanks to Baron who has made this happen.

Should I be concerned?
Yes, you should. While some people will not like that I’ve made this alarming comment, a lot of organizations use MySQL Replication for backups. It would be unprofessional to be ignorant to make the assumption.

How to I get it?
Download MySQL Table Checksum. It is recommended that you download all components in the toolkit as there are many valuable tools then just this one.

MySQL Table Checksum will require Perl which is generally always installed with any Linux distribution by default (For Windows you need to do yourself). You will also require the Perl Modules DBI and DBD::mysql. Use CPAN for easy installation of these modules.

These are the steps I did (NOTE: this is a specific version, you should always check for newer versions)

wget http://internap.dl.sourceforge.net/sourceforge/mysqltoolkit/mysqltoolkit-1204.tar.gz
tar xvfz mysqltoolkit-1204.tar.gz
cd mysqltoolkit-1204/bin
./mysql-table-checksum

If everything is installed correctly, you should see.

Usage: mysql-table-checksum [OPTION]... HOST [HOST...]

Errors in command-line arguments:
  * No hosts specified.

mysql-table-checksum checksums MySQL tables efficiently on one or more HOSTs.
Each HOST is specified as a DSN and missing values are inherited from the first
HOST.  If you specify multiple HOSTs, the first is assumed to be the master.
For more details, please use the --help option, or try 'perldoc
mysql-table-checksum' for complete documentation.

How do I use it?
Start with the documentation, RTFM

perldoc mysql-table-checksum

There are a number of ways to run MySQL Table Checksum and different means of using arguments etc. This is what I do.

By Default:

./mysql-table-checksum u=root,p=sakila,h=localhost

DATABASE TABLE       CHUNK HOST      ENGINE      COUNT       CHECKSUM TIME WAIT STAT  LAG
xxx      table1          1 localhost InnoDB       NULL     1678710928    0    0 NULL NULL
xxx      table2          1 localhost InnoDB       NULL     3023415523    0    0 NULL NULL
xxx      table3          1 localhost InnoDB       NULL     1692517818    0    0 NULL NULL
xxx      table4          1 localhost InnoDB       NULL              0    0    0 NULL NULL
xxx      table5          1 localhost InnoDB       NULL     2295061143    0    0 NULL NULL
xxx      table6          1 localhost InnoDB       NULL     2238111875    0    0 NULL NULL
xxx      table7          1 localhost InnoDB       NULL      823770692    0    0 NULL NULL
xxx      table8          1 localhost InnoDB       NULL     2313561225    3    0 NULL NULL
xxx      table9          1 localhost InnoDB       NULL     3524358173    0    0 NULL NULL
xxx      table10         1 localhost InnoDB       NULL              0    0    0 NULL NULL

The output will provide a checksum via the MySQL CHECKSUM function. As this doesn’t provide a row count, the COUNT column is NULL.

A better method is to use the –replicate function. This provides the results into a Database Table (handy for lots of things) as well as getting table counts. This requires a pre-requisite table. For this example I’ve added it to the test database schema.

$ mysql -uroot -psakila test
mysql > CREATE TABLE checksum (
                db         char(64)     NOT NULL,
                tbl        char(64)     NOT NULL,
                chunk      int          NOT NULL,
                boundaries char(64)     NOT NULL,
                this_crc   char(40)     NOT NULL,
                this_cnt   int          NOT NULL,
                master_crc char(40)         NULL,
                master_cnt int              NULL,
                ts         timestamp    NOT NULL,
                PRIMARY KEY (db, tbl, chunk)
             );

My execution for this using this table

./mysql-table-checksum u=root,p=sakila,h=localhost --replicate=test.checksum

DATABASE TABLE    CHUNK HOST      ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
xxx      table1       1 localhost InnoDB       2236 cdd6689dbb23ff547540561c4815c717b3d01bf3    0 NULL NULL NULL
xxx      table2       1 localhost InnoDB       3157 f4d538a7a83168acb2cf3374a6edc4949809e723    0 NULL NULL NULL
xxx      table3       1 localhost InnoDB         99 daa0d8403feb200beac5db6456e92c86de6a9b84    0 NULL NULL NULL
xxx      table4       1 localhost InnoDB          0                                     NULL    0 NULL NULL NULL
xxx      table5       1 localhost InnoDB       1006 118b996e10f76153b786479b15f134c08cd21b62    0 NULL NULL NULL
xxx      table6       1 localhost InnoDB    2499397 6a60e07f45d5980bb5de3bd75e1b2ce1e5be25b8   29 NULL NULL NULL
xxx      table7       1 localhost InnoDB      11879 00929c2d6b9278f585b6b7346816bb7a0e483b9e    0 NULL NULL NULL
xxx      table8       1 localhost InnoDB        675 e8e17f9ed15986965ff678ca0c26cd5397ea272d    0 NULL NULL NULL
xxx      table9       1 localhost InnoDB    1730498 373e7fb3fec77b1d0edcfb6a5e28619e9c91d5e4   25 NULL NULL NULL
xxx      table10      1 localhost InnoDB    8557931 7913803bf65bbcf18e679255c657dba53045b88e   99 NULL NULL NULL

The screen output is great, I actually run the following command keeping the output. See later for more info,

./mysql-table-checksum u=root,p=sakila,h=localhost > checksum.mysql4.replicate.`date +%y%m%d.%H%M` &

The table contents are a little different.

mysql> select * from checksum limit 1G
*************************** 1. row ***************************
        db: xxx
       tbl: table20
     chunk: 1
boundaries: 1=1
  this_crc: f7c4d19f6342105f97a4b2770ee7b9094e57a6f2
  this_cnt: 2236
master_crc: f7c4d19f6342105f97a4b2770ee7b9094e57a6f2
master_cnt: 2236
        ts: 2007-11-08 15:37:16

What is most impressive is that in a Master/Slave environment these commands are all replicated, and a simple comparison of data in this table on each slave will confirm if your database is in sync. The docs also confirm, the structure of the tables are not compared so a slave may indeed have some changes.

Migration?
I’ve been able to use the checksum to test a MySQL 4 to MySQL 5 migration. I’ve been able to confirm via checksums as an initial verification that the data has indeed been loaded correctly.

The issue was using the log files for comparison between to instances is the TIME column value changed. As the format of the rows is spaced, it was not possible to easily cut as each schema name was a different length. A small inconvience for the benefit in the results.

I have found however that optimizing the schema in 5, e.g. INT for BIGINT and TIMESTAMP for DATETIME did cause the checksums to fail.

Conclusion
This is a quick introduction, this tool has a lot to offer and is only one in a whole toolkit.

I needed to start mysql without privileges after a database restore today, and while confirming the correct option which was –skip-grant-tables I came across an option which made me laugh.

$ mysqld --verbose --help
...
  --sporadic-binlog-dump-fail
                      Option used by mysql-test for debugging and testing of
                      replication.
...

And here is the Official Manual Entry

If you didn’t get your proposal in for MySQL Conference 2008 , that’s too bad.

I often wondered from past conferences why submissions were needed so early, like 5 months before. Well, as being invited to be part of the MySQL Conference committee this year I now know why, and have a greater appreciation. With near 300 submissions, it takes time to review them, and this is just the first step in a number to get to a completed schedule for next years conference.

Also getting a sneak preview of what’s to come is really cool! I’m already excited.

This month I continued my Performance Analysis talks at the Local NY MySQL Meetup. Previous discussions can be found here.

Our focus was a more in-depth look at gathering and reviewing MySQL Status and your applications SQL statements using MySQL Proxy. Even after preparing the slides over the weekend Jan added more functionality that was particularly interesting. So today while addressing a client issues I further extended this work to do even more funky monitoring.

Today’s monitoring.lua script does:

• Logs to file, Date/Time, Query Time, Response Time, Rows Affected, Normalized SQL and Actual SQL for each query
• Has histogram of tables used with read/write figures.
• Has histogram of SQL with avg and max execution times.

You get the best of both worlds, you can see SQL access live, get statistics of this, and then be able to drill down to every instance of a given SQL statement for more information.

You can get a copy of my slides Here. You can get a full tar of my demo work here. This includes MySQL Proxy, My Bench for some benchmarks and the Sakila Sample Database.

I am of the attitude, the day you stop learning something is the day you die. I’m not prepared to induce MySQL into both sides of that equation, however some days it never ceases to amaze me what little thing I didn’t know about MySQL.

Today I saw in reviewing SQL statements for an application SELECT LAST_INSERT_ID(). No big deal, that is expected, however I then saw UPDATE … SET id=LAST_INSERT_ID(id+1) WHERE …

Having never seen this syntax I was forced to review it’s usage. See MySQL Documentation

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

Seems like a layman’s idea of Sequence support, but you are still restricted with the problems of a manual implementation. Transactions support, and if you use MyISAM, table level locking.

I was asked a question today, “DATETIME vs TIMESTAMP. When to use which & why?”

It’s a good MySQL introduction question, here are some general considerations for choosing one.

Do you need Date values other then an EPOCH value? (i.e. before 1970) If the answer is yes, then DATETIME is required.
If you do not however, then TIMESTAMP is the best choice for a few reasons.

1. The TIMESTAMP columns uses 4 Bytes to record it’s value, while DATETIME uses 8 bytes. Using the smallest storage is always a best practice for all columns.
2. The TIMESTAMP column supports the CURRENT_DATE syntax in the CREATE TABLE command. This enables the column to have a default value for INSERT or for UPDATE, but not both. Indeed this is the only data type that allows for any default value that is not a constant.
3. All date functions (at least the ones I use) work equally as well with TIMESTAMP and DATETIME.

I have yet to find any benchmarking to indicate any performance differences of not selecting TIMESTAMP.

And just for a piece of trivia, the DATE datatype is 3 bytes, the TIME datetype is 3 bytes, so why is the DATETIME 8 bytes?
Yes, for those that intend to reply I do know the answer, however others readers may not. Comments please!

If you have not been to the www.mysql.com website, it’s a new look.

Yesterday I attempted to get a working MySQL environment to support the number of utilities we all use including mytop, innotop, mybench, mysqltoolkit. These products require a number of Perl Dependencies, and while that may be a rather trivial task under Linux and with the power of cpan, working on Solaris is a whole different story.

For the record, I’m working with Solaris 9 SPARC 64bit.

I won’t detail you with how hard it was to get to this point, except to say thanks to Jeremy, Baron and Frank so far. Here is where I’m at.

You need a number of pre-requisites, most from sunfreeware.com

Pre-Requisites

$ wget ftp://ftp.sunfreeware.com/pub/freeware/sparc/9/libiconv-1.11-sol9-sparc-local.gz
$ wget ftp://ftp.sunfreeware.com/pub/freeware/sparc/9/gcc-3.4.6-sol9-sparc-local.gz
$ wget ftp://ftp.sunfreeware.com/pub/freeware/sparc/9/make-3.81-sol9-sparc-local.gz
$ gunzip libiconv-1.11-sol9-sparc-local.gz gcc-3.4.6-sol9-sparc-local.gz make-3.81-sol9-sparc-local.gz
$ pkgadd -d libiconv-1.11-sol9-sparc-local
$ pkgadd -d gcc-3.4.6-sol9-sparc-local
$ pkgadd -d make-3.81-sol9-sparc-local

$ wget http://search.cpan.org/CPAN/authors/id/A/AB/ABURLISON/Solaris-PerlGcc-1.3.tar.gz
$ gunzip Solaris-PerlGcc-1.3.tar.gz
$ tar xvf Solaris-PerlGcc-1.3.tar
$ cd Solaris-PerlGcc-1.3
$ perl Makefile.PL
$ make install
$ wget http://search.cpan.org/CPAN/authors/id/M/MS/MSCHWERN/ExtUtils-MakeMaker-6.36.tar.gz
$ gunzip ExtUtils-MakeMaker-6.36.tar.gz
$ tar xvf ExtUtils-MakeMaker-6.36.tar
$ cd ExtUtils-MakeMaker-6.36
$ perl Makefile.PL
$ make install

Back to installing

$ PATH=/usr/local/bin:$PATH;export PATH
$ PATH=/usr/perl5/5.6.1/bin/:$PATH;export PATH

$ perlgcc Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for Term::ReadKey
netcontrol1:/tmp/TermReadKey-2.30# make install
make: *** No rule to make target `/usr/perl5/5.6.1/lib/sun4-solaris-64intConfig.pm', needed by `Makefile'.  Stop.

This is where the trail ends as my friend google has not helped me past this point, so I’m turning the community. If anybody can assist please let me know.