There's always a catch, right? Of course there is. One thing to take into account with database optimizations is that you don't optimize the database over the system it's running on. In other words, don't let it take up so much memory that the system is swapping memory to and from the hard drive swap space constantly.

I ran into this problem a few weeks ago with BanLink. The system was having all kinds of difficulties and then noticed that it'd gobbled up so much memory that it was spending half the time swapping the database cache from the disk cache.

Another catch, at least in my case, but I've read of a few others, is that MySQL will not *always* restart after you've changed the database settings. In other words, it shuts down fine, but just refuses to start. The solution I've found to this is to blank out the configuration, start it up, apply an updated configuration, and restart. It doesn't *always* work, and I don't even really understand why it should work, but it's about all I have to go on.

I experienced this a few weeks ago, only I couldn't get my new settings to take. No matter what I tried, the database refused to start with any optimized settings. So I just let it start up unoptimized. That worked. The stats on the site weren't even all that bad, so I let it run for a while.

That was short-lived, however. A good test of your optimizations are to run it without them for a while. Ok, that's a really poor test. Averaging around 10 seconds to respond was not a pretty thing. But I finally managed to get some of my new settings pushed into the database. We're back to sub-second responses, and we're not eating up all the memory of the system to do so.

The point? A good DBA pays attention to their database and doesn't let it win the battle…and it will fight you.

Ok, time for another of my wonderful BanLink updates.  I haven't done this in a while, but I should.  Even if all of you hate it, it still is nice to keep track of what's going on.  It'll be fun to look back on at least.

The database, thank goodness, has been running pretty smoothly lately.  I did have to push in a few additional memory tweaks last Wednesday to make sure it's caching as best it can, but so far the load on the server has been minimal.  This is a very good sign considering we've more than tripled the amount of traffic that the site gets in the last month.

The latest work has been completely web related.  I'm excited and looking forward to the BanLink 2.0 web release.  And yes, it really is coming.  I've spent a lot of time rebuilding the core of the system and am now putting it within a pretty interface.  Using all the modern technologies available, like AJAX routines, the site is becoming much easier to navigate and hopefully will provide for a much nicer user experience overall.

When I built the original site, it was done in a fairly rushed fashion just to get the web and SL to interoperate.  The more interactions we needed, the more was added to the site.  This is what accounts for the lack of cohesiveness in a few of the screens.  It's also why the system seems so slow at times.  Communicating back into Second Life via RPC is slow, especially if the sim that the RPC box is housed in is running a little sluggish.  It'll get there, but it may take a few seconds.  Add to that the sites that have 20 boxes to communicate with, and you find yourself sitting and waiting and *hoping* things are working.  The new site will have a much more logical layout, meaning less need to hop between screens to do similar things.  It's also going to help with communications as they will now work in parallel.  And you'll get a status message to keep you up to date on what's happening.  Isn't that nice?  Yes, it is.

There's good stuff to come.  Thanks to everyone who has supported the BanLink system.  It's nice that we're hearing more and more success stories as of late.  It helps to keep us going when we know the system is providing use to folks out there.

A sneak peak, you say?

Continue reading

It's been pointed out to me that I should remind everyone that these techniques can really only be applied when you have complete control over your MySQL instance. Many users in a hosted environment don't have this control. However, most users won't need to worry about this sort of tuning. It is important, however, when you have a heavily used system, such as BanLink, where you're getting constant transactions 24×7.

So this is part two in our MySQL Database Tuning series.  Last time we looked at the number of opened tables versus the number of tables cached. Next we're going to look at the index buffer. The trick is that you want to give as much memory as you can (go figure) to the index buffer without allocating all of the memory in your system.

The rule of thumb is to set it to a quarter to half of the available memory in your system, provided MySQL is the primary use of the system. How can you tell when you've got enough memory and things are working great, well we again return to the extended-status that we talked about last time. There are two status variables that we'll need to look at: key_read_requests and key_reads. You'll want to divide key_reads by key_read_requests. You wan that resulting number to be less than 0.01. If it's not, bump up the key_buffer value in your my.cnf file.

A companion to these values are key_writes and key_write_requests. As above, divide key_writes by key_write_requests. This time you want the result to be less than 0.1. Again, this is affected by the key_buffer setting in your my.cnf file. Once you have it tweaked to where both your reads and writes are optimal, your database will be nicely humming along.

It's time for a BanLink update. We've been working pretty hard on this system the past few weeks. Travis is gearing up for a new in-world release, and I've been continuing with the back-end optimizations. The plan is to get the database into working in a more streamlined fashion, and then work on updating the website. I know those of you that use the website do not think it's ideal, and it's not. The website was more or less thrown together at the start of this whole project and bits and pieces were put together as it grew. Now that we know more of how the site can and needs to be used, we can update it and make it more functional. We have already received a list of enhancement requests from you folks that have been using the system, much of those had been on our own list of enhancements since the beginning. Now, finally, they will come. Thanks for waiting.

Back to the latest bit of work, though.
Continue reading

Well, happy and sad for me, actually.

The server tweaks that I've done have really made a difference, but I'm a bit confused by what I'm seeing, I have to admit.

You see, the improvements really did work on the server as a whole. The load average stays pretty low most of the time. The average in a 24 hour period is 1.2, but during light times it hangs out at 0.5. This I like. What's rather surprising, however, is that the execution time of the controller script is at around 1 second. I really expected it to drop to sub-second execution. In fact, with it running for so long, I really expected the server load to be higher. This is what's bothering me. How is it that the app is running without a lot of improvement, but the server load has improved dramatically? My suspicion before was that the DB was the bottleneck in the whole process and once that was improved, the app would return immediately. I guess it really is time to fix that program and see where the hangup is.

Speaking of evil. Tuning Databases is right up there. Sure, you get to run through and check table reads, index caching, memory allocation, etc. It's like a big puzzle that you get to analyze and put together. You definitely have to be in the right mood for it or it can just be a world of suck. But the bigger problem is when you make changes and then the database refuses to restart. Sure it exits just fine, but the important part is starting back up. Twice I've had the database refuse to start after making some tweaks. And it doesn't really complain about any of the changes. To make matters worse, untweaking doesn't help. So far what I've found is that I have to replace its configuration file with one that's empty, start the server, replace it with a tweaked config file, then restart. Things then seem to go just dandy. I don't make the rules, so don't blame me if that sounds silly.