Since I have been a home recently, I put some time correcting bugs in WaffleGrid and adding new features. Thanks to gdb, I have been able to understand a silly bug that was affecting WaffleGrid with sysbench but, weird enough, not with dbt2. Everything is in the way connections are established. I will blog more about that soon.

Regarding the new features, it is now possible to choose which tables you want to push to memcached. For that purpose, two new parameters have been introduce:

innodb_memcached_table_filter_enable = 0 | 1   (default to 0)

to enable the filtering and

innodb_memcached_table_list = db1/table1,db2/table2   

to list the tables. This feature is filtering based on the space id so, innodb_file_per_table has to be set. Right now, the association table space_id is done only at startup so, the table has to exist. Also, since an alter table change the space_id… you need to restart MySQL …

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 …

You probably haven’t noticed but I have not blogged since the UC. It is not because I am upset by the perspective of working for Oracle, I have simply been busy tracking down an issue we have with WaffleGrid. We discovered that under high load, with DBT2 on a tmpfs, we end up with error in a secondary index. In the error of MySQL, we have entries like this one:

InnoDB: error in sec index entry update in
InnoDB: index `myidx1` of table `dbt2`.`new_order`
InnoDB: tuple DATA TUPLE: 3 fields;
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000bea; asc     ;;
 2: len 4; hex 80000005; asc     ;;

InnoDB: record PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000bea; asc     ;;
 2: len 4; hex 80000004; asc     ;;

TRANSACTION 14469, ACTIVE 1 sec, process no 7982, OS thread id 2995481488 updating or deleting
mysql tables in use 1, locked 1
26 lock struct(s), heap size 2496, 65 row lock(s), undo log …

One last reminder, we ( Yves and I ) will be appearing all over the place at the UC. Here is a quick run down of our schedule:

• Tuesday 10:50 am - 11:35 pm (Free MySQL Camp) I will be presenting “Learning from others’ MySQL Performance Mistakes” … a journey into the sometimes silly things we all end up doing.

• Tuesday 2-3pm Yves and I are co-presenting “Distributed Innodb Caching with memcached” … this will be an intro into the Waffle Grid project, what it’s about and what it means to you.

• Tuesday 4:25 pm - 5:10 pm (Free MySQL Camp) Yves will be presenting “NBD (MySQL Cluster) performance tuning and pitfalls”

• Wednesday 2-3pm I will be presenting: “SAN Performance on a Internal Disk Budget: The Coming Solid State Disk Revolution” … the title sounded better when I submitted it, because the fact is SSD’s are here! But the big question is what does it …

Recently, I was asked if it is possible to replicate an NDB cluster to a non-NDB MySQL database. So, I tried!

I created the following table on the MySQL master:

Create Table: CREATE TABLE `testrepl` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

and on the slave:

Create Table: CREATE TABLE `testrepl` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Of course, for obvious reasons, NDB only supports row based replication so I configured the master to use row based:

mysql> show global variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

Then I tried and go the following error:

Last_Error: Error 'Incorrect information in file: './mysql/ndb_apply_status.frm'' on opening tables …

At MySQL/Sun we do a lot of high availability setup using Heartbeat but recently I was involved, along with Harold Mayfield (a VCS expert), in project using Symantec VCS on Solaris. Of course, MySQL works perfectly well with VCS, here are a few highlights of the installation.

Apart from the share store resources and the IP resources, MySQL is configured as the following:

        Application insiderDB-app (
                StartProgram = "/san/mysql/bin/mysqld --defaults-file=/etc/my.cnf &"
                StopProgram = "/san/mysql/bin/mysqladmin -u root shutdown"
                MonitorProgram = "/san/vcs_scripts/mysql_monitor"
                )

and the mysql_monitor script is the following:

#!/bin/ksh

STATUS=$(/usr/bin/echo status | /san/mysql/bin/mysql -u root 2>/dev/null |/usr/bin/grep -c Uptime) 

if [ "$STATUS" -eq "1" ]
then
        exit 110
else
        exit 100
fi

In the previous 2 posts of this series, we basically talked about how to execute social networking type queries using SQL IN clause and how handle multiple columns IN clause. In this last post on the topic, I will introduce the notion of NDB API filters, although I don’t consider myself as an NDB API expert. Filters are to NDB API the equivalent WHERE clause in SQL. The point is that the filters can be nested and they are sent to the storage nodes only when the transaction is executed.

As an example, let’s consider the following table:

Create Table: CREATE TABLE `MultiColPK` (
  `region_id` int(11) NOT NULL DEFAULT '0',
  `application_id` int(11) NOT NULL DEFAULT '0',
  `first_name` varchar(30) NOT NULL DEFAULT '',
  `payload` varchar(30) DEFAULT NULL,
  PRIMARY KEY …

I just learned that there will be a Webinar “What’s New in the Next Generation of MySQL Cluster?”, April 30th. From what I know, the webinar is supposed to be at a good technical level, it is not a marketing like introduction. If you are interested, just register at: http://www.mysql.com/news-and-events/web-seminars/display-320.html

I don’t know if it is because of my science background, I am a physicist, I do like graphs, especially when I do performance tuning. With UNIX like operating systems, the vmstat command give you an easy way to grab many essential performance counters but, generating graphs from vmstat output with tools like OpenOffice Calc is time consuming and not very efficient. In order to solve this, I wrote a few scripts using gnuplot but they are not very easy to work with. Then, doing some benchmarks with DBT2, I found the vmplot.sh script and… I like that one. I just hacked it little bit to make it keeps the graph on screen, adding the “-persist” parameters to the gnuplot invocations. The script will produce 7 graphs that will be displayed on screen and save in png format in /tmp. The graphs it produces are the following:

• CPU: graphs idle, user, sys and wait time
• …

As you probably now if you reading this blog, MySQL does not support SQL sequences. Logically, it is very easy to implement something equivalent to a sequence using transaction with InnoDB with a simple 2 columns table, name and value. The only problem is that a sequence update is usually composed of two SQL statements, a SELECT to read the value and an UPDATE to increment it. Fairly you would say, use a transaction to wrap the 2 statements together but then, you are probably aware that you cannot manage transactions in a function with MySQL. A clean sequence function seemed out of reach up to recently, when Brian Morin showed me a trick that solves the issues. Here how to create a get_next_val function to a sequence.

delimiter //
drop function if exists true_function //
create function true_function(p_param int) returns int
  deterministic
  sql security invoker
  return true
//

drop function if exists get_next_value//
create …