Venu Anuganti Blog

Recently Yahoo! released a new beta messenger (code name Diablo) for Windows Vista. Initially I tried during the alpha days and did not had a chance to try it again; and I installed it today during the free time and surprised to see that it offers almost all the existing features of regular messenger with much more enhancements.

The good thing about the new Diablo is;  it is completely re-written messenger on top of  Windows Presentation Foundation (WPF) for Vista to get the rich GUI look and feel. It has number of interesting features like zooming of contacts to the desired level, multiple color choice, searching of contacts, auto spell checker, phone support, call history etc. This is for sure much better than the currently existing one; and need to see what new features the team will add in the coming days especially towards the social networking of friends as part of YOS.

The only thing that I found missing is the archive of messages; that’s one of the feature that I use a lot by archiving all the messages; so that I can refer back to them at any time.

The screenshots of the messenger can be found from here:

[read more…]

Today I received a SMS message from evite for one of the birthday function. This is because my phone is configured with the evite account. The content is simple with 4 lines of text; which contains title, host, when and where. That is normal.

But I was surprised by the footer of the text message with the following line…

*Sponsored by The All-New Toyota Corolla

So, this seems to be a really cool idea; and we have to appreciate evite in order to incorporate the text ads as part of their SMS messages for evite invitations; this could generate some revenue for the company.

In India all incoming and outgoing SMS is free along with incoming calls, even though am not sure how mobile carriers are making money out there; but in USA that’s not the case even for SMS (some times its free as its part of the media packages).

Even in USA, it is possible for mobile carriers (like AT&T Cingular, Verizon and T-Mobile) to offer free SMS service to all end users by incorporating sponsored text or visual ads (if the phone can support media messages) as part of the message; that could be another adwords on Mobile SMS. Not only this offers a good revenue stream to the mobile carriers; but also can get more users to use the text or media message service as it will be free.

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.

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…]

Yesterday I attended the SQL Server Data Services - Software Design Review event in the Microsoft mountain view campus. Thanks for Ryan Dunn who accepted my invitation to attend this as this is only for selected people.

I had a real good time not only was able to understand on how SSDS is built on top of SQL Server, it was pleasure meeting few key folks like:

• Istvan Cseri, Architect of SSDS
• Tudaor Toma, Group Program Manager of SSDS
• Nigel Ellis, Architect and Development Lead of SSDS
• Ryan Dunn, Technical Evangelist for SSDS
• and rest of the team…

Thanks for your answers for all my questions, I appreciate that. Even though I did not like the name "SQL Server Data Services" as it does not sound as good as Amazon "SimpleDB", but they already did a great ground work within a short period of time and need to see how they will take the services to the people now.

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 : (

Now I figured out the reason for MySQL Server 5.1.24 crash when used with InnoDB plugin 1.0. As I had a older my.cnf in the path and it had innodb_flush_method=fdatasync as the default flush method. But from 5.1.24 onwards, fdatasync is not supported as the flush method (not sure why we have such a change in the final stage of RC code, but  …)

Even though I get an error in the mysqld.err log that InnoDB failed to register; but server starts and loads rest of InnoDB information schema plugins without the main InnoDB plugin.

InnoDB: Unrecognized value fdatasync for innodb_flush_method
080423 22:36:04 [ERROR] Plugin ‘InnoDB’ init function returned error.
080423 22:36:04 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

As the server started with rest of the InnoDB plugins; upon querying any of them; the server simply crashes…

mysql> show plugins;

+---------------------+--------+--------------------+---------+---------+

| Name                | Status | Type               | Library | License |

+---------------------+--------+--------------------+---------+---------+

| binlog              | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

| partition           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

| ARCHIVE             | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

| BLACKHOLE           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

| CSV                 | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

| FEDERATED           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

| MEMORY              | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

| INNODB_TRX          | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_LOCKS        | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_LOCK_WAITS   | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_CMP          | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_CMP_RESET    | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_CMPMEM       | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

| MyISAM              | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

| MRG_MYISAM          | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

+---------------------+--------+--------------------+---------+---------+

16 rows in set (0.00 sec)

mysql> select * from Information_schema.Innodb_locks;

ERROR 2013 (HY000): Lost connection to MySQL server during query

You can crash in the same way if you have any invalid InnoDB variable in the config file. It looks like InnoDB needs to have dependency on the main plugin when it is loading the information schema plugins or at least it should have validation checks up on querying the information schema tables for the main plugin. I will file a bug report on this.

Good thing is I was able to get InnoDB plugin to work with MySQL 5.1.24 and started doing some benchmarks, and results seemed to be very positive. I will post the updated results later tomorrow.

Looks like the InnoDB plugin is completely broken with MySQL-5.1.24; at least there is no luck for me so far to set it up right. First, the Makefile is broken. Second, I tried to do a static build of InnoDB plugin with MySQL server (–enable-innodb and –with-plugin-innobase) resulted in partial InnoDB plugins (Information schema) missing the main one as shown below:

   1:  mysql> show plugins;

   2:  +———————+——–+——————–+———+———+

   3:  | Name                | Status | Type               | Library | License |

   4:  +———————+——–+——————–+———+———+

   5:  | binlog              | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

   6:  | partition           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

   7:  | ARCHIVE             | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

   8:  | BLACKHOLE           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

   9:  | CSV                 | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

  10:  | FEDERATED           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

  11:  | MEMORY              | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

  12:  | INNODB_TRX          | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

  13:  | INNODB_LOCKS        | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

  14:  | INNODB_LOCK_WAITS   | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

  15:  | INNODB_CMP          | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

  16:  | INNODB_CMP_RESET    | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

  17:  | INNODB_CMPMEM       | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

  18:  | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |

  19:  | MyISAM              | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

  20:  | MRG_MYISAM          | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |

  21:  +———————+——–+——————–+———+———+

  22:  16 rows in set (0.00 sec)

As you can see the main InnoDB plugin is missing and rest of the InnoDB information schema plugins for compression and locks are present. So, I built a dynamically loadable plugin separately and tried to install it using INSTALL PLUGIN, but it fails.

   1:  mysql> INSTALL PLUGIN INNODB SONAME ‘ha_innodb.so’;

   2:  ERROR 1126 (HY000): Can‘t open shared library ‘/home/y/lib64/mysql/plugin/ha_innodb.so‘ (errno: 2

   3:                                        cannot open shared object file: No such file or directory)

   4:  mysql> INSTALL PLUGIN INNODB SONAME ‘ha_innodb.so‘;

   5:  ERROR 1123 (HY000): Can’t initialize function ‘INNODB’; Plugin initialization function failed.

You can see the plugin .so should be in lib/mysql/plugin directory as opposed to top lib/mysql directory. And when I tried to select Information_schema.InnoDB_locks on a fresh database, the server crashes..

   1:  mysql> select * from Information_schema.Innodb_locks;

   2:  ERROR 2013 (HY000): Lost connection to MySQL server during query

The backtrace looks like ..

/mysql/libexec64/mysqld(get_schema_tables_result(JOIN*, enum_schema_table_state)+0x142) [0x6e9d32]

/mysql/libexec64/mysqld(JOIN::exec()+0x760) [0x64b480]
/mysql/libexec64/mysqld(mysql_select(THD*, Item***, TABLE_LIST*...) [0x64d267]

/mysql/libexec64/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x181) [0x64dab1]

I decided not to file any bugs yet as InnoDB team looks like working on getting this to work with 5.1.24. Now, I started a new build with 5.1.23 to see how it goes.