improving mysql performance: some notes and links

• NDB mysql clusters. Handles transactions, is about as fast as memory tables, can do replica / master-master clusters, but because it's multi-server it's fundamentally more scalable as a transaction engine.
  

• Patches from Google to improve replication stability, mysql/innodb performance with > 4 cores

• Caching InnoDB memory pages into a Memcached server as a way to dramatically improve innodb performance and expand the memory available for it to use. It's a very small patch to mysql.

• Mysql + Hadoop: recently there has been a lot of noise about using them in conjunction as a way to dramatically scale the types of read operations you can do in a replicated relational database environment. E.g., either the data in the tables is enormous, and you want to do complex operations over it all at once that would typically cause a single database server (even though the data might all fit) to blow up. Not sure if someone has a setup working out of the box, but the idea is that you load a "snapshot" into hadoop as you would a new replica, then process binlogs (row replication updates are ideal for this) to update the dataset in the hadoop cluster. Would work pretty smoothly with, say, our ML database. Wonder if the INVERSE could be done too (take a dataset in a hadoop cluster, compute some set of updates, and then generate the appropriate row updates to send to your live DB cluster.

ec2 security groups restrictions within the cluster

According to the Ec2 security groups documentation, within your ec2 cluster of servers, it is not possible to setup access rules within the cluster that reference both multiple groups of servers (by aws account or security groups) and ports/protocols. You authorize between multiple groups of servers, but it must be for all ports and protocols. If you are restricting by CIDR (ip addres) you can restrict by port and protocol, but not within your own cluster.

When authorizing a user/group pair permission, GroupName, SourceSecurityGroupName and SourceSecurityGroupOwnerId must be specified. When authorizing a CIDR IP permission, GroupName, IpProtocol, FromPort, ToPort and CidrIp must be specified. Mixing these two types of parameters is not allowed.

http://docs.amazonwebservices.com/AWSEC2/latest/APIReference/index.html?ApiReference-soap-AuthorizeSecurityGroupIngress.html

http://docs.amazonwebservices.com/AWSEC2/latest/CommandLineReference/index.html?ApiReference-cmd-AuthorizeSecurityGroupIngress.html

However, this documentation is straight up WRONG. It is indeed possible, although completely undocumented, to add access restrictions that reference amazon accounts, security groups, protocols, and ports all together. You can only use the SOAP API (as well as the command line tool ec2-authorize, which uses the SOAP api) to do this, not the Query API (what the amazon-ec2 and right-aws gems both use):

~> ec2-authorize backendservers -P tcp -p 8080 -u $AWS_USER_ID -o frontendservers
GROUP backendservers PERMISSION frontendservers ALLOWS tcp 8080 8080 FROM USER (redacted) GRPNAME frontendservers

Someday this will likely showup in the Query API, but until then, we're stuck coding for SOAP or referencing the command line tools to make use of this lovely feature that you'd expect would be standard. This feature has been live for well over a year (I started using it in spring of 2008), so it's really surprising that it's not available in all of the apis.

collectd versus munin

http://collectd.org/

* more efficient C interface than munin's cron/perl-based polling mechanism
* cleaner C interface versus munin's perl
* nice interfaces for ruby, java, C, erlang http://collectd.org/related.shtml
* lots of options for graphing, data manipulation/utilization
* similar default out-of-the-box plugins for all the major stuff; all the other ones had to be customized for munin anyhow.