After publishing in May a benchmark report about InnoDB Dirty pages & Log size impact I received several very interesting comments, and one of them pointed to the purge lag problem and InnoDB innodb_max_purge_lag parameter created specially for such purpose! So, I was very curious to know how it may help me in my workload...

The following study is about how innodb_max_purge_lag is working now and what may be done to make things better :-))

I'm not pretending here on any absolute truth :-) My goal is to understand what's going on with purge on my workload and see if there is something else possible to improve..

Benchmark scenario

My benchmark scenario will be exactly the same as …

One of the things I really like about Solaris (and OpenSolaris) is the Services Management Framework (SMF). And one of the things I like about my job is the opportunity to learn and work with new stuff. And, of course, the best way to learn something "non-lethal" is to try it yourself and do something with it.
So, how about using SMF for managing a MySQL Cluster setup? Something simple like two data nodes, one mgmt node, and one MySQL server node running on one OS instance? Sounds like a good exercise and, from a quick search, I couldn't find where it had been done before. Loaded up the latest release of OpenSolaris (2009.06) into a Virtual Box VM, downloaded the latest tar ball of …

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 …

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 …

Currently several probe InnoDB code improvements were done by our MySQL Team. I was happy to test them with db_STRESS workloads but on Solaris/SPARC server (M5000). Then discussing with Mikael  I was surprised he saw much less improvement from the latest probe builds on his Linux/AMD64 box... And it was unclear why the performance improvement gap is more important on SPARC: due SPARC itself? due Solaris? due a test case?.. To bring more lights and understand better what's going differently on an AMD box I've preferred to avoid to change too many things on the same time :-) So, once one of the latest 32cores AMD server (X4600-M2) was available, I was curious to test it under Solaris10 and connected to the same storage box as M5000 before. And here are my results...

My intention is to replay exactly the same tests as previously on M5000 but on the newest X4600 (8CPU AMD …

Following my previous post (where I've also told about a significant performance gain by using a bigger InnoDB log size ), I'd like to present you some results obtained on the Read+Write workload and log size equal to 1024MB. I've labeled it on graphs as "FF" (Fast & Furious :-)) as within a such configuration MySQL server may go very fast until it'll meet a "furious flushing" to free some space within a log file... :-)

However, the speed-up is quite important, so if you don't worry too much if time to time your production activity may have a short drop on performance - you have to consider this option as one of the first to test to improve your throughput! :-)

As you may see from the following graphs, most of engines performing with 1024M redo log even better than previously tested MySQL 5.4 …

We are pleased to announce the release of High Availability Cluster software for OpenSolaris 2009.06! If you've been following along, this release is the fruit of project Colorado. Open HA Cluster 2009.06 is based on Solaris Cluster 3.2, including many of the features from the most recent update. Additionally, Open HA Cluster 2009.06 contains the following new features:

• The ability to use Crossbow VNICs as …

We are pleased to announce the release of High Availability Cluster software for OpenSolaris 2009.06! If you've been following along, this release is the fruit of project Colorado. Open HA Cluster 2009.06 is based on Solaris Cluster 3.2, including many of the features from the most recent update. Additionally, Open HA Cluster 2009.06 contains the following new features:

• The ability to use Crossbow VNICs as …

We are pleased to announce the release of High Availability Cluster software for OpenSolaris 2009.06! If you've been following along, this release is the fruit of project Colorado. Open HA Cluster 2009.06 is based on Solaris Cluster 3.2, including many of the features from the most recent update. Additionally, Open HA Cluster 2009.06 contains the following new features:

• The ability to use Crossbow VNICs as …