Venu Anuganti Blog

I know that this was talked a lot and recently Mark Callaghan also gave a session in MySQL user conference 2008 about the real bottlenecks.

Other day I was testing my thread pool stuff with MySQL 5.1.24 + InnoDB plugin 1.0.1 along with other miscellaneous benchmark tests by making them CPU bound by keep the working set completely in memory to gauge the performance of threads overhead; and on 8-core box InnoDB seems to be doing better than 4-core. And then immediately I started few tests with mysqlslap by keeping complete data set in the buffer pool to get the proper timing on locking overhead.

Here is the comparison of performance on 8-core box with innodb_thread_concurrency is set to 32 and 0 for variable threads on 64-bit Redhat Linux 4 . The same box is used as 4-core by limiting the cores.

[read more…]

Since few weeks I was working on patching the MySQL 5.1 with libevent support to have thread pooling in server end as it is needed by few properties where they do not need a persistent connection and needed to scale server with thousands of connections as each call just does a simple query execution by connecting and disconnecting and pooling seemed to be a right choice.

When I brought this idea to Monty; he pointed me to 6.0 where it is in preliminary stage. Now I took the same design and implemented in 5.1 to be compatible with how its working in 6.0; and things so far seems to be running fine, except few misc glitches that am trying to solve now.

It uses the same thread_handling=one-thread-per-connection (default) and new thread_handling=pool_of_threads and thread_pool_size controls how many will be initialized and kept at the startup.

| thread_handling            | pool-of-threads |
| thread_pool_size           | 5010            |

Few things that are missing from the 6.0 is the status of thread pools (number of threads currently in use, max used so far from pool etc) are all added now.

I am currently benchmarking the performance between the regular persistent to pool-of-threads model to see how it works out. I will post more details once I have the graphs and numbers along with working model.

This is a bit surprise when we encountered a case where InnoDB is not releasing its row lock when there is an error condition within the transaction. And I verified with Falcon, Oracle, SQL Server and Sybase; all seemed to work as expected.

For example; just open a transaction in a session and execute a error statement (lets say duplicate key) and on the other new session try to get a row lock on the same record (use where clause with FOR UPDATE) and you will notice that InnoDB blocks on this statement until you issue a explicit rollback or commit. But remember there is nothing happened on the first session other than duplicate error on that row. So, InnoDB should implicitly unlock the row when there is an error; and looks like it is not doing that.

Here is the scenario:

First create a single column table and populate some rows (lets say 20 rows in this case) on any version of MySQL/InnoDB.

mysql> create table t1(c1 int not null auto_increment primary key)Engine=InnoDB;
Query OK, 0 rows affected (0.14 sec)

mysql> insert into t1 values(),(),(),(),(),(),(),(),(),();
Query OK, 10 rows affected (0.12 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> insert into t1 values(),(),(),(),(),(),(),(),(),();
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

and then execute the following statements; first in session-I and then in session-II and notice that session-II select statement hangs till you explicitly release the transaction in session-I.

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(10);
ERROR 1062 (23000): Duplicate entry ‘10′ for key ‘PRIMARY’

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where c1=10 for update;

Even though am not really sure whether this is a bug or feature in InnoDB (I suspect this as a bug); but Oracle, SQL Server or Sybase will not block the select and releases the lock on the duplicate error. Even Falcon engine in MySQL 6.0 does seem to release the lock appropriately.

Last week I wrote an article on InnoDB plugin, which explains how to explore all the new features in the plugin along with comparing different row formats.

The article is live now from here:
http://www.innodb.com/wp/wp-content/uploads/2008/05/venu-anuganti-article-april-29-2008.html

Thanks to Ken Jacobs, who took the initiative to post the article on the web site.

Update: here is the nicely formatted version:

http://venublog.com/exploring-new-features-in-innodb-plugin-10/

This is a continuation post on InnoDB plugin row format performance with few more test scenarios to cover based on the feedback and questions that I got in my Inbox.

This time the initial table looks like this

CREATE TABLE `sbtest` (
  `id` int(10) unsigned NOT NULL,
  `k` int(10) unsigned NOT NULL DEFAULT ‘0′,
  `c` char(120) NOT NULL DEFAULT ”,
  `pad` char(60) NOT NULL DEFAULT ”,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

This time, the table does not have a secondary index in the create time, instead it will be added after the table is populated with the data to test the performance of the new faster index support from the plugin. I also added a new scenario to test 2K compression as we noticed 4K compression seems to be a bottleneck in the earlier case.

Same test environment is chosen as of the first one, RHEL-4 64-Bit having MySQL-5.1.24 with InnoDB plugin 1.0 statically linked.

[read more…]

Here is a quick comparison of the new InnoDB plugin performance between different compression, row formats that is introduced recently.

The table is a pretty simple one:

CREATE TABLE `sbtest` (
  `id` int(10) unsigned NOT NULL,
  `k` int(10) unsigned NOT NULL DEFAULT ‘0′,
  `c` char(120) NOT NULL DEFAULT ”,
  `pad` char(60) NOT NULL DEFAULT ”,
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

The table is populated with 10M rows with average row length being 224 bytes. The tests are performed for Compact, Dynamic and Compressed (8K and 4K)  row formats using MySQL-5.1.24 with InnoDB plugin-1.0.0-5.1 on Dell PE2950  1x Xeon quad core with 16G RAM, RAID-10 with RHEL-4 64-bit.

Here are the four test scenarios:

1. No compression, ROW_FORMAT=Compact
2. ROW_FORMAT=Compressed with KEY_BLOCK_SIZE=8
3. ROW_FORMAT=Compressed with KEY_BLOCK_SIZE=4
4. ROW_FORMAT=Dynamic

All the above tests are repeated with innodb_buffer_pool_size=6G and 512M to make sure one fits everything in memory and another one overflows. The rest of the InnoDB settings are all default except that innodb_thread_concurrency=32.

Here is the summary of the test results:

[read more…]

Looks like MySQL build team should add a test scenario to cover this in the automation. Even if you specify –without-server with the latest 5.1.24; it still builds all most all plugins. This is really bad deal. Forget about mandatory plugins like MyISAM, Heap and Merge; it also builds rest of the plugins unless you skip them using –without-<name> or –without-plugin-<name>. The mandatory check should also be relaxed when one uses –without-server. The configure currently throws an error with you try to skip any mandatory plugins with –without-server too.

When one need clients (directory client) and its libraries (directories like libmysql, libmysql_r and mysys, mystrings, dbug) we do not need to build the whole ‘SQL‘ dir and no need to enter the ‘storage‘ directory at all. I patched the configure script to escape all these cases now including skipping of mandatory plugins; and I can see that everything works as expected after the patch. Even though I could not find a easy way to strip ‘ndbclient’ out of the ‘ndb’ engine. But this will allow me to build on systems with gcc2 where we only need client programs : (