Lessons learned optimizing MySQL

This week I’ve been quite entertained at work optimizing a big MySQL database, 18M rows and about 10Gb of table data for a high traffic web site (11M unique views per month). Not optimizing for perfomance is overkill in this case, so I have been learning and enjoying a lot during these days.

The first thing to note is that database servers are complex beasts (don’t underestimate them) and each and every database provider has its own strengths but also its own caveats.

MySQL logoSooner or later you’ll face a behavior that may seem counter-intuitive and there’s when you must read a lot of documentation. In this case, MySQL Performance Blog and MySQL documentation have been of great help, as well as the now unmantained-but-still-useful Hack MySQL site, which provides great tips very well explained.

Second. As always, doing an 80% optimization consumes 20% of the time and most of it is quite straightforward. EXPLAIN, review results, create index, EXPLAIN again. This way you can do a good job just knowing some indexing basics. Your indexes may be a bit fat, but will do the job.

Then you’ll need to be patient, creating indexes is not always fast, in fact the number, size and heterogeneusy of the columns involved will increase the time it takes to create them. While you wait, the best is to read good books about MySQL or relational databases like: Relational Database Index Design and the Optimizers.

You’ll need to be organized. Creating indexes randomly just looking a couple of slow SELECTs is something, but you must look at the big picture: make an inventory of all the application queries, no matter how unimportant look like because if they attack a big table with a WHERE or an ORDER BY they will perform bad. Fact. This list will help you organize the indexes and see which ones are used and which not and where.

Learn as well what the configuration parameters mean, MySQL has a bunch of them. Just increasing numbers will not always solve your bottlenecks. If you see something strange, there might be a parameter misconfigured and causing bad results.

Sometimes you’ll find yourself frustrated by something that you can’t understand. I found that almost anything in this field has an explanation, even a reasonable one.

Finally, don’t overlook other thinks. Database performance is important but there may be a lot of room to improve in your code, don’t blame MySQL as the root of all problems!

Simple Mini Profiler Glimpse plugin

I recently published on Github a simple small project (miniprofiler-glimpse-plugin) to integrate the results collected by the excellent MVC Mini Profiler created by the people at Stackoverflow with Glimpse, the Firebug for ASP.NET and MVC applications.

Creating Glimpse plugins is really easy (just implement IGlimpsePlugin and decorate it with GlimpsePluginAttribute), implement the methods and reference the plug-in assembly in the project where you are using Glimpse. MEF will do the gluing and the JSON.NET will do its best to deserialize the results in a human readable way.

To use this plug-in, just compile it and reference the assembly in your Glimpse-enabled project. I you have questions, suggestions or bug reports, you can use Github’s own issue reporting system, drop me a line o fork the project and make your improvements.

So, I hope you like it.

Link: miniprofiler-glimpse-plugin [github.org]

Troubleshooting MSDTC, RPC and NServiceBus issues

At first it seemed easy, but it’s been a long distance hurdle race. At the end, I have solved the problems I’ve found and my painful experience can be useful for other people, so this is it.

First of all, my topology is the following: a client, in this case for testing purposes, my machine, which is running an ASP.NET web application and a NServiceBus host that processes all the messages. This has been configured following the official documentation and looking at the samples (check out the full NServiceBus source code, you’ll need it). It’s a bit messy but with some trial and error you can make sense of it. There are some obscure properties that have to be googled to know what they mean.

Processing the messages is not trivial for the bus host. Some of them attack the database and need to use MSDTC to manage the transactions. This means that MSDTC must be up and running without problems.

One trick I learned after a lot of researching is the Runner.exe tool from NServiceBus. This comes with NServiceBus source code (and I think that also with paid version) and ends up in build\tools\MsmqUtils if you build it with build.bat or build-net4.bat. Just open a command line as administrator and run:

Runner.exe /i

This will install MSDTC and configure it properly. I don’t think you’ll need anything else but check the MSDTC configuration in Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator -> Local DTC. Right click to go to properties and ensure everything is configured like in this Troubleshooting DTC post.

At this point, I recommend you to disable the Windows Firewall on both machines, at least for domain network, just to be sure it’s not interfering and breaking the tests, we will solve this later.

Then, DTCPing is your friend. This small tool will check the DTC connectivity between two machines. It’s a bit awkward. You have to start the tool in both machines, then put the name of the other and click ping on each one. This will make a test and if everything goes well, you’ll see a detailed log without errors:

++++++++++++Validating Remote Computer Name++++++++++++
Please refer to following log file for details:
	C:\tools\DTCPing\LOCALMACHINE2928.log
Invoking RPC method on REMOTEMACHINE
RPC test is successful
++++++++++++RPC test completed+++++++++++++++
++++++++++++Start DTC Binding Test +++++++++++++
Trying Bind to REMOTEMACHINE
Received reverse bind call from REMOTEMACHINE
Binding success: LOCALMACHINE-->REMOTEMACHINE
++++++++++++DTC Binding Test END+++++++++++++
Please send following LOG to Microsoft for analysis:
	Partner LOG: REMOTEMACHINE5928.log
	My LOG: LOCALMACHINE2928.log
++++++++++++Start Reverse Bind Test+++++++++++++
Received Bind call from REMOTEMACHINE
Trying Reverse Bind to REMOTEMACHINE
Reverse Binding success: LOCALMACHINE-->REMOTEMACHINE
++++++++++++Reverse Bind Test ENDED++++++++++

Here started my nightmare, I got the damned: RPC error 5 (Access denied). After hours googling for the problem I found this post about MSDTC problems (I was looking for RPC problems, which actually was the root cause). The cause is that remote RPC is disabled in Windows workstations by default.

I opened for the N-th time the registry and added the RPC key to HKLM\Software\Policies\Microsoft\Windows NT and the RestrictRemoteClients DWORD value to 0. Rebooted without much hope. Magically, DTCPing worked and life seemed better and brighter.

Then, the Windows Firewall must be configured. In Windows Server 2008 (or Windows 7, it’s the same interface), go to Control Panel -> System and Security -> Windows Firewall (or just execute firewall.cpl at the command prompt). Go to Advanced Configuration to open a new window. Go to inbound rules and look for the Distributed Transaction Coordinator. If they are disabled, enable them. On the workstation the firewall can be enabled. Beware that DTCPing may not work, because the Firewall is only allowing connections to/from svchost.exe and msdtc.exe (if you want it to work, add a rule for that process).