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 …

Times of change are upon the database market. The major established database companies are being challenged by open source upstarts like MySQL and PostgreSQL. For years, Open Source Databases (OSDBs) have been quietly increasing their penetration, but until recently they have lacked the capabilities to seriously threaten proprietary databases like Oracle, IBM's DB2, and Microsoft's SQL Server.

All that has changed. OSDBs now boast the necessary features and robustness to support commercial databases hundreds of Gigabytes in size. And a growing trickle of competitive benchmark results shows them …

Times of change are upon the database market. The major established database companies are being challenged by open source upstarts like MySQL and PostgreSQL. For years, Open Source Databases (OSDBs) have been quietly increasing their penetration, but until recently they have lacked the capabilities to seriously threaten proprietary databases like Oracle, IBM's DB2, and Microsoft's SQL Server.

All that has changed. OSDBs now boast the necessary features and robustness to support commercial databases hundreds of Gigabytes in size. And a growing trickle of competitive benchmark results shows them …

Times of change are upon the database market. The major established database companies are being challenged by open source upstarts like MySQL and PostgreSQL. For years, Open Source Databases (OSDBs) have been quietly increasing their penetration, but until recently they have lacked the capabilities to seriously threaten proprietary databases like Oracle, IBM's DB2, and Microsoft's SQL Server.

All that has changed. OSDBs now boast the necessary features and robustness to support commercial databases hundreds of Gigabytes in size. And a growing trickle of competitive benchmark results shows them …

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 …

Last few days one of our customers (one of the largest Ruby on Rails sites on the Net) was struggling to solve some really strange problem - once upon a time they were getting an error from ActiveRecord on their site:

They have innodb_lock_wait_timeout set to 20 seconds. After a few hours of looking for strange transactions we were decided to create s script to dump SHOW INNODB STATUS and SHOW FULL PROCESSLIST commands output to a file every 10 seconds to catch one of those moments when this error occurred.

Today we’ve got next error and started digging in our logs…

After a couple of hours of looking in logs, parsing binary logs from MySQL …

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

$ bin/mysqladmin …

I’m returned from my 1-week vacation today and want to say - I’ve never been so productive as I was there Blue ocean, hot sun and white sand really helped me to finish my work on the first release of one really awesome project.

Today I’m proud to announce our first public release of the Data Recovery Toolkit for InnoDB - set of tools for checking InnoDB tablespaces and recovering data from damaged tablespaces or from dropped/truncated InnoDB tables.

This release already has a pretty decent set of features:

• Supports both REDUNDANT (pre mysql 5.0) and COMPACT (mysql 5.0+) versions of tablespaces
• Works with single tablespaces and file-per-table tablespaces
• Able to recover data even when processed InnoDB page has been reassigned …