Just thought those of you who follow MySQL and Java together might be interested in a video interview I did with Ed Ort during the MySQL Conference in April 2009. I cover some performance tips for deploying an application against MySQL.

You can watch the video and see the associated resources at http://blogs.sun.com/SDNChannel/entry/mysql_tips_for_java_developers

We know that the open source + paid support business model works for software products that have extremely large numbers of users, but does it scale to products with medium or small numbers of users?

Software is often described as a stack (see the simplified version below).

Vertical Applications [Installed base = small]
————————–
Middleware (e.g. Database) [Installed base of MySQL = 12 Million]
————————–
Operating System [Installed base of Windows = 1Billion+]

Companies in the operating system layer typically have large numbers of users with a small license fee per user. This dynamic makes it relatively painless for a company to forgo a small license fee in exchange for a larger userbase, and then make money on support. For example, an open source company might forgo the $100 license fee, but charge $15 a seat for support. With large numbers of users, the …

Seem’s the year Sun had for improving MySQL, and with an entire new 5.4 branch the development team could not fix the autoconf and compile dependencies that has been in MySQL for all the years I’ve been compiling MySQL. Drizzle has got it right, thanks to the great work of Monty Taylor.

I’m working on the Wafflegrid AWS EC2 AMI’s for Matt Yonkovit and while compiling 5.1 was straight forward under Ubuntu 8.10 Intrepid, compiling 5.4 was more complicated.

For MySQL 5.1 I needed only to do the following:

apt-get install -y build-essential
apt-get install libncurses5-dev
./configure
make
make install

For MySQL 5.4, I …

Before I get into details here is the bottom line. If you start MySQL on Solaris as a non-root (ie, mysql) user and for some reason you need to adjust the file descriptor resource limit for the parent shell, never use 'ulimit -n'. This will set both the soft and hard limit and may cause MySQL to adjust the max_connections and table_open_cache configuration variables upon next startup.

Use either:

 ulimit -S -n 1024

or something like:

prctl -n process.max-file-descriptor -t basic -v  1024 -r -i process $$

The Details

The default 'basic' privilege value for the resource control process.max-file-descriptor is 256. This control represents the soft ulimit for file descriptors per process. The default 'privileged' privilege is set to 65535, which represents the hard ulimit. A non-root user can adjust the soft limit down or up to the hard limit. Unless it has PRIV_SYS_RESOURCE a …

Just spotted the following posting from Paul DuBois on the internals mailing list:

I'm engaged in a project (WL#3403) to compile information that will provide better information about our errors and error messages:

• What an error means
• Likely causes of the error
• How to rectify or work around the error

The general idea is to provide our users something more than a list of error codes and the messages from errmsg.txt, such as the manual currently includes here:

…

In many applications, MyISAM can be used successfully if the proportion of write operations is only a small fraction of the read operations. As the tables grow, the 4 GB limitation in the key_buffer size caused performance issues and other strategies. Wait… did I miss something… From a recent comment to a post from Matt I re-read the MySQL documentation and realized I missed a big change that occurred with versions 5.0.52 and 5.1.23 (http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_key_buffer_size)

As of MySQL 5.1.23, values larger than 4GB are allowed for 64-bit platforms

So, key_buffer as large or even larger than 32 GB are possible. That is an important design consideration that needs to be taken into account when tuning …

I have been asked this numerous times ever since I posted my InnoDB sample configuration files, Do I have sample configuration parameters for a MyISAM setup? We are seeing less and less people use MyISAM, but it is still popular ( especially in prepackaged form i.e. wordpress). So i figured why not adjust my sample InnoDB configs and make them suitable for MyISAM.

The biggest limitation to MySIAM is used to be the default key buffer only can could only be sized up to 4GB ( This was fixed in 5.0.52) . While you can create separate key buffers and assign indexes to them, it’s not very common ( Common as in present in low-end shops who need 5 minute dba help) in part because I think people do not fully understand it and you do have to plan for it. Setting up secondary caches is a database/application specific setup, it can not really be generalized, …

Before I get into details here is the bottom line. If you start MySQL on Solaris as a non-root (ie, mysql) user and for some reason you need to adjust the file descriptor resource limit for the parent shell, never use 'ulimit -n'. This will set both the soft and hard limit and may cause MySQL to adjust the max_connections and table_open_cache configuration variables upon next startup.

Use either:

 ulimit -S -n 1024

or something like:

prctl -n process.max-file-descriptor -t basic -v  1024 -r -i process $$

The Details

The default 'basic' privilege value for the resource control process.max-file-descriptor is 256. This control represents the soft ulimit for file descriptors per process. The default 'privileged' privilege is set to 65535, which represents the hard ulimit. A non-root user can adjust the soft limit down or up to the hard limit. Unless it has PRIV_SYS_RESOURCE a …

Before I get into details here is the bottom line. If you start MySQL on Solaris as a non-root (ie, mysql) user and for some reason you need to adjust the file descriptor resource limit for the parent shell, never use 'ulimit -n'. This will set both the soft and hard limit and may cause MySQL to adjust the max_connections and table_open_cache configuration variables upon next startup.

Use either:

 ulimit -S -n 1024

or something like:

prctl -n process.max-file-descriptor -t basic -v  1024 -r -i process $$

The Details

The default 'basic' privilege value for the resource control process.max-file-descriptor is 256. This control represents the soft ulimit for file descriptors per process. The default 'privileged' privilege is set to 65535, which represents the hard ulimit. A non-root user can adjust the soft limit down or up to the hard limit. Unless it has PRIV_SYS_RESOURCE a …

Lately there has been quite some talk about security on MySQL, and I've decided to GPL a package I wrote, implmenting Roles on MySQL. This technology has been available on other databases for quite some time, but hasn't quite yet made it to MySQL's feature list and apart from this tool, the only solution I know of is google's patches for MySQL 5.0.