Venu Anuganti Blog

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.

Windows Live Writer is of the best blogging tool that I found in recent times. The tool is very convenient to write and it provides all possible options that one needs when writing a blog post. It also integrates with most popular blogging platforms like Windows Live Spaces, SharePoint, WordPress, Blogger, LiveJournal, TypePad, Moveable Type, Community Server, and more. You can also retrieve any published post and re-edit back.

As it integrates with WordPress along with supporting the categories, tagging etc; it is very convenient to post any blog entry. This is the tool that helped me to blog live when am in MySQL user conference 2008

Here is the quick notes from the session scaling heavy concurrent writes in real time by Dathan Pattishall. Its bad that he left Flickr i.e. Yahoo. Hopefully they will find a replacement, if not contact me I have few people who are interested.

• Who am I
  • since 1999 working on mysql
  • scaled many companies (FriendFinder, Friendster, Flickr, now RockYou)
  • Favorites federation, partitioning, shards, RAID-10
• Requirements
  • scale better
  • store data forever
  • associate time with the data
  • allow for change
  • keep it cheap
  • and downtime is not an option
• Spread the data around
  • federate
  • all referrers are owned by the page owner
  • spread data out by that
  • but federate in a different direction
  • add a new column to the global account lookup
• Did not work
  • strings as PK was not good
  • inserts slowed when the table grew larger than memory
  • not enough I/O to handle double the load
• Start over
  • converted URL into a 64-bit ID - conv(substr(md5(url),1,16),16,10) (Dathan had a typo in the function in his slides, thanks to Patrick who tried it when the session is running)
  • 64-bit number, its unique
  • reduced PK size to 8 bytes + owner, object, object-type
• InnoDB and strings
  • index on string takes a lot of space
  • each index has its own 16K page
  • fragmentation across pages was hurting, chewing up I/O
• InnoDB and High Concurrency of string writes
  • requirement: 200 ms for total db access for all apps
  • writes gets slowed down when you cross buffer_pool_size
  • 10 ms to 20 secs sometimes for full transaction
  • replication keeping up
• Buffer the writes
  • java daemon that buffers up to 4000 messages grouped to a transaction and apply serially
  • does not use much memory or CPU
  • written by Zen Java master
• Reduce the use of big strings
• Keep smaller amounts of data
  • Use MyISAM for non-pro users and keep only X weeks of data
  • MyISAM keeps 1/6th the size of InnoDB
  • Migrate the data when they migrate
• Distributed locks
  • GET_LOCK and RELEASE_LOCK on the same server for which a user operates on