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”

$ …

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 …

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 …

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 devuser@99.99.99.99;
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON devdb.* to devuser@99.99.99.99;
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 …

Time to install MySQL on my new MacBook.

$ cd /opt
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.45-osx10.4-i686.tar.gz/from/http://mysql.mirrors.hoobly.com/
$ tar xvfz mysql-5.0.45-osx10.4-i686.tar.gz
$ cd mysql-5.0.45-ox10.4-i686
$ scripts/mysql_install_db
Installing MySQL system tables...
071129 22:10:48 [Warning] Setting lower_case_table_names=2 because file system for /opt/mysql-5.0.45-osx10.4-i686/data/ is case insensitive
OK
Filling help tables...
071129 22:10:48 [Warning] Setting lower_case_table_names=2 because file system for /opt/mysql-5.0.45-osx10.4-i686/data/ is case insensitive
OK

bin/mysqld_safe &
[1] 239
macbook:mysql-5.0.45-osx10.4-i686 rbradfor$ chown: /opt/mysql-5.0.45-osx10.4-i686/data/macbook.err: Operation not permitted
Starting mysqld daemon with databases from /opt/mysql-5.0.45-osx10.4-i686/data

$ …

Yes, it may seem rather strange to the readers but MySQL has a booth at this week’s Oracle Open World 2007 and I’m here in San Francisco Wednesday and Thursday. If your in SF come in and say hi. Check out the Official Press Release and MySQL listed Oracle Resources for this conference.

An interesting recent report Oracle Users Indicate Increase in Use of Open Source sheds some light of the significance of MySQL within the Oracle Community.

The Independent Oracle Users Group (IOUG) has released its second major research study focusing on open source adoption trends, “Open Source in the Enterprise: New Software Disrupts the …

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 …

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 …