Venu Anuganti Blog

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.

I do not know if anyone noticed this; but there was an incompatible change from MySQL 5.1.22 to 5.1.23 in mysql_com.h file for NET structure by renaming the members last_errno and last_error to client_last_errno and client_last_error. This is really annoying as it not just breaks the compilation of lot of depending applications; and functionality will be annoy if one uses the wrong client library as there is no protocol version change. The change should have been done in alpha or beta code but not when its tagged as Release Candidate (RC). The change history does not even have a clue of this change.

Now; I noticed that in 5.1.24 the change is reverted back. I guess someone should be very careful when changing any structures related to the protocol as it could break the things silently.