Hyperion Reactor The Middle Child of History

MySQL by default will always use the best index for a SELECT. MySQL is not perfect and at times will mess this up by using a wrong index. In such cases you can force MySQL to use a specific index or otherwise ask it to ignore a specific index.

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;
 
SELECT * FROM table1 IGNORE INDEX (col3_index)
  WHERE col1=1 AND col2=2 AND col3=3;

You can find more information here: http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

Drupal tries to cater to 80% of the market by taking most decisions for you. Generally this is a good idea. But if you fall in the remaining 20% then you need to get your hands dirty and start hacking around. Drupal by default stores all sessions in the database. This is a good idea if your site does not get a lot of load. But as your site starts growing, this will soon become a major bottleneck. Storing sessions in memcache works best in such situations. Not only is memcache easy to maintain, install and work with, but also provides in depth stats about itself.

I have been playing a lot with Drupal lately. I must say it does feel weird getting back to PHP after so much Ruby; but it is a refreshing change. One of the the things I noticed is that Drupal 6 adds a LOWER() to the column names when it does a select. Not that this is bad but then it confuses MySQL, and instead of using the "name" index it runs through the entire database. This is not a issue if your site has a few hundred users. But now consider a site that has about 5 million users (which is what I am dealing with)

I just finished hacking up this patch, that prevents it from adding the LOWER() to the column names. This boosted something as simple as the login process from 5 seconds to milliseconds. 5 seconds may not sound a lot to you; but then I use a Dell 1950 as my development box. Not that it is the best box in the world, but it is pretty powerful.

Last week David Axmark the co-founder and author of MySQL visited my office. Thanks to Sun for making this meeting happen. We spent some time discussing about MySQL and development of applications in general. These are some of the key "take-away" points from that meeting. A lot of these points may seem obvious, but a lot of companies decide not to implement them for various reasons.

MMM (MySQL Master-Master Replication Manager) is a set of flexible scripts to perform monitoring/failover and management of MySQL Master-Master replication configurations (with only one node writable at any time). The toolset also has the ability to read balance standard master/slave configurations with any number of slaves, so you can use it to move virtual IP addresses around a group of servers depending on whether they are behind in replication.

This was developed by the MySQL High Performance Group at MySQL AB for a client.

Consistent hashing is a scheme that provides hash table functionality in a way that the addition or removal of one slot does not significantly change the mapping of keys to slots. In contrast, in most traditional hash tables, a change in the number of array slots causes nearly all keys to be remapped. By using consistent hashing, only K/n keys need to be remapped on average, where K is the number of keys, and n is the number of slots.

servers = ['memcache1', 'memcache2', 'memcache3', 'memcache4']
servers[ 'product-1'.hash % servers.size ]
 
servers[ 'product-1'.hash % servers.size ] => "memcache4"
servers[ 'product-2'.hash % servers.size ] => "memcache1"

DBAs have spent years developing their expertise in database management best practices, backup/recovery, performance tuning and writing really cool scripts. Today's successful DBA needs skills beyond just the management of their databases. These skills include:

• Operating Systems: Having sufficient knowledge in the operating systems their databases run in.
• Best Practices: Maintaining best practices for database management.
• Automation: Automating your database management.