Posterous theme by Cory Watilo

Yet more Subversion repository polling headaches

Either there is something very wrong with the way our repository is set up (i.e. my fault) or there is something wrong about the polling model for detecting changes in the repository.
This is the second or third time I’ve run into this issue.  Granted, we have not had the most fastidious design for our repository, however, I can’t believe that what we are doing on this small scale is so outside the pale of what other, much larger organizations (such as open-source hosters) are doing.  I just don’t think we’re that special.
The problem manifested itself today in the form of hundreds of authentications per second against Active Directory from a particular account and server.  The server is our Subversion repository server, naturally, and the account is the special build server account set up to monitor and build from our product locations in the repository.
The tide was sustained over a wide range of time and seemed to be contributing to visible load on the domain controller.  If not harmful, this is at the least embarrassing and annoying for our build infrastructure, which I’m very much invested in.  Nothing I know should be causing this kind of traffic, and if it is a consequence intrinsic to the system, I should find a way to make it less demanding.
I’ve run into similar issues with other tools before.  Evidently, polling a repository for changes is demanding on a repository.  We had an application called SVN monitor which was very nice.  Every time a commit was made to the repository, a notification would pop up from the system tray giving you the details, which you could then inspect with a pretty nice repository browser.  The polling interval could be configured as well.
Unfortunately, we would see enormous spikes in CPU usage on the SVN server when a few users would be polling for changes.  Limiting the number of polled locations and frequency of polling helped performance but greatly hampered the usefulness of the tool.  We eventually replaced it with the repository view from Redmine.
Back to tracking down the current issue.  The SVN server (VisualSVN) is configured for secure connections with Windows Authentication.  The authentication requests to AD coming from this server are responses to SVN clients making requests of the server.  Turning off the SVN server verified that it was indeed the source of the authentication requests.  There were only two servers configured to use that poll the subversion server using the account in question…Redmine and TeamCity, our build server.
The Redmine repository view does take some time (and presumably therefore, effort) to refresh its view.  It uses the subversion command line tools to do so.  However, it should only refresh when you visit the page.  So this isn’t a likely culprit unless something is very wrong.
TeamCity, on the other hand, is constantly polling to show you what changes might be pending for the next build.  This was the likely culprit.  Shutting it down quickly killed all of the authentication requests.  That was the winner.
However, what to do about it is an issue.  Obviously, it isn’t necessary to poll every 60 seconds for every VCS root in TeamCity, but there has to be a happy medium.  I reduced the polling interval globally to 10 minutes, which definitely helped.  There are only four VCS roots, and the TeamCity vcs log shows that they are polled individually each minute (by default)…so why the hundreds of authentication requests.  Something about the way TeamCity polls must break up the poll into lots of small requests, each requiring authentication.  This is what is nailing our domain controller.
Aside from reducing the polling interval, I’m not sure what else there is to do at the moment.  I haven’t posted a question on their support forum, but that’s on tap.  Perhaps there is more to come.

SQL Server Performance Monitoring

While working with customer recently, we encountered SQL issues pegging the CPU.  As it turns out, a certain query was causing this performance issue.  However, it was difficult to see that without the use of some SQL management tools.  This is a brief article about some of those tools.

The real key to seeing what was going on was a combination of the Task Manager and SQL Management Studio Express Edition.  Task Manager was needed to show when the performance hit was taking place, and SMSEE was used to show what the active SQL process was doing.

In SQL Management Studio, connect to the database.  Then go to the Monitoring folder in the tree view.  This is the bottom folder.  Under that folder is the Activity Monitor.  Right-click on this and choose to monitor active processes.

You will then see a table of all of the processes connected to the database, including management studio.  This view shows which connections are actively processing and a summary of what command SQL is currently processing for that process.  Note that it is a static view and needs to be periodically refreshed by the Refresh button.

By right-clicking on the active process, you can go to a detail view that shows the full query which is currently being executed.  This view is also static and must be refreshed.

When the CPU was taking a big hit in Task Manager, we would look at the detail view to see the query.  It was a single query which didn’t change for the duration of the CPU activity, so we knew that was the one causing the problems.

Another tool are the performance report dashboard for management studio.  This contains a number of useful canned reports for looking at average and instantaneous top resource consumers (queries) for your database.  These are extremely useful and are available (free) from Microsoft at: http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

There is also the SQL Database Tuning Advisor built into SQL Management Studio (not the Express edition), but it only works against SQL editions greater than Express.  One method of employing this would be to convert one of our test machines from a SQL Express to a standard edition SQL Server and using it with our product long enough to generate the profile data used by the advisor to suggest optimizations.

Another set of tools is the SQL Health and History Tool as well as the SQL Best Practices Analyzer.  While I haven’t had time to investigate these yet, they sound useful as well, especially the BPA.

Evil, thy name is AUTO_CLOSE

Okay, perhaps that’s overstating the case a bit, but on the other hand, perhaps not.

AUTO_CLOSE is the feature in SQL which will “close” a database (ie, clear the cache, write everything to disk, release the file lock, etc, etc) some 300ms after the last connection to the database is closed.  It is turned off by default with all editions of SQL except for SQL Express, which forces it to on.  This is ostensibly to ease XCopy deployment, according to MS.

A customer called with performance problems.  We use SQL Server 2005 Express Edition as part of our product, and we are constantly making single-use, non-overlapping connections to the database.  In this customer’s case, every time a new connection was made to the SQL Server, it would sit and chew on the CPU for a good couple minutes as it was processing.  This is not the normal behavior we expect to see.

So we looked at the usual things.  The two most notable things about his setup, aside from having a database on the large end of things, were that he had a configured memory constraint on the database at around 100MB and that there were a lot of Application Event logs saying “The server resumed execution after being idle %d seconds. This is an informational message only. No user action is required.”  These were accompanied by reports of the clearance of the cachestore.

As far as the memory constraint goes, we had set that manually at some time earlier.  Since the client had increased the system memory in the interim, I put it back at the default, which is somewhere around 2TB.  This is SQL Express, so it still has a 1GB memory limit coded into it somewhere else.  While this improved performance, it was only about a fifth faster.

I was also already familiar with the Application Event log message, although I was puzzled as to why it was there.  We encountered AUTO_CLOSE when we first started using SQL Express, since only SQL Express sets AUTO_CLOSE to True.  Our customers had been chewing our ears off about their log files filling up with useless messages (why, if the message states “No user action is required”, can you not simply disable that log message?).  We dutifully relayed the fact that it was a “harmless” message, but for this reason alone we began configuring the database to not AUTO_CLOSE.  As we find out now, that is not the most important reason to turn off AUTO_CLOSE, by any means.

So there were three puzzles here.  Why were those messages occurring if we don’t configure AUTO_CLOSE, why are there cachestore messages, and are either of them related to the performance problem?

The cachestore messages are the easiest to clear up.  They are a new, related message spit out by the same event, added with SQL SP2.  So that’s where they are coming from.  So at least MS is now telling us there is more to the picture than “No user action is required”.  Harmless?  Apparently they are now telling us there is a cost associated with the automatic closing of the database.

Further research showed that there are two “naughty” settings for performance with SQL Server.  AUTO_CLOSE and AUTO_SHRINK.  Technet states that best practice is to turn these both off.  So Express doesn’t follow best practices.  AUTO_CLOSE is even slated to be removed entirely from SQL server in the future!

As I mentioned, we knew that already, although we didn’t know the associated performance issues.  Now we knew that as well.  But if we didn’t have AUTO_CLOSE on, why were we seeing these things?

It’s a combination of things.  First of all, there is apparently no way to force the SQL Express instance, as a whole, to default that setting to off.  If there were, you could set that and be done with it, at least until you installed a new instance.  Then you’d need to set it off there as well.

Or, if the setting were a permanent part of the database, you could set it there and be done with it.  Microsoft’s documentation would lead you to believe that this is the answer.  In fact, this is the only way to set the attribute, on the database itself.

So that’s what we do.  However, there is a detail.  Apparently this setting isn’t actually set within the database, it is set somewhere more ephemeral.  You can tell because if you detach the database and reattach it, that setting is lost and defaults to True again!  We actually do that procedure quite a bit because we troubleshoot not by operating on live customer databases but rather by following Microsoft’s stated instructions on moving databases between instances, which is to detach and reattach.  There are other processes which require this as well, such as moving the database between customer servers.

Now, if the setting were part of the db, that wouldn’t be a problem.  But apparently it isn’t, because the re-attach resets the AUTO_CLOSE attribute to TRUE.  Blech.

So the answer is to reset this attribute every time we do the kind of support that requires us to copy the database.  It also means we have a lot of customers out there who have unwittingly had that setting changed by our troubleshooting procedures.  Thanks MS!  Sorry for the sarcasm, this is just one particularly tricky set of circumstances that don’t seem to be well thought out.  I am not above that either.

Fortunately or unfortunately, the result with the customer is that they did not see an enormous increase in speed with this change, so it apparently isn’t the major culprit I thought it might be in the first place.  Still, it would be nice for SQL Express to have a better story on this.

D’oh – VMware conversion and static IPs

So we converted all of our testbed servers from the venerable VMware Server 1.0 product to the sexy, but also free, ESXi product.  Server was getting a bit a bit long in the tooth, but we definitely had reason to be wary of the VMware Server 2.0 product based on our experience with the beta causing what appeared as disk hardware errors.  That is another story.
The point is, moving to ESXi is beneficial in a lot of ways, mostly to do with the serious hardware we put underneath it.  Multiple snapshot support is another nice benefit to mention.  However, the conversion process had one gotcha up its sleeve…network adapter changes.
The network adapter is definitely part of the virtualization performed by VMware, drivers for which are included with the VMware tools installed on the guest.  During the conversion process, I failed to notice that these were replaced with the ESXi tools, which caused the OS to recognize a new network adapter.
All of our testbed systems are individual Domain Controllers of their own domains, along with Exchange being installed.  This configuration didn’t break at first, thankfully.  The big issue is that if you are doing domain stuff with these boxes anywhere outside them, such as adding a computer to the domain, the other machine needs to know the DC.  Normally this is gotten through DHCP and DNS.  In this case, we have loads of these separate DCs on the same network, so they can’t very well all be DHCP servers.  Instead the DC has to be hardcoded into the client as the DNS server.  This is why all of the testbeds have static IPs assigned.
Until you replace their network cards and forget to reassign the static IP.
So remember this if you end up converting VMs with static IP configs.

Project Milestones

Too often in the midst of a project, I grapple with making sense of what’s been done, what’s to do, what foggy ideas have sprung to mind and how to call them back to mind, and where I am in the whole mess.  While there is no substitute for clear and simple thinking, I’m not always blessed with that.  The devil is in the details of any project, and what starts as a simple goal with just a few steps often ends up teetering on all of the realities which come to light along the way.
While this may sound dramatic, I’m sure you can relate to the occasional feeling of inertia midway through an important project.  If you’re like me, you’ve been employing healthy doses of action and only minor doses of planning and tracking.  While it makes sense to spend the valuable resource of time on actually accomplishing your goal, you want to keep your footing sure along that path.  In this case, having been sick for a week has done a number on my ability to come back in full stride, so this seems like an opportune time to think about tools and processes to help get and keep things on track.
One great motivator for me is understanding how much time I’ve spent on things.  I’m always pushing along more than one project.  And sometimes I like to call it a day at 5 when I really haven’t put in a full day’s work.  And when it comes to things I like to ignore out of convenience, time-tracking is usually the first to go.
So I’m raising my consciousness in that area once again.  In the past I’ve used Excel spreadsheets to track time.  The handy Ctrl-: (Ctrl-Shift-;) puts in timestamps automatically, and lots of pretty formulas and graphs are a few clicks away.  I keep things simple, so I just track time in and out, not really per-project.
The issue with this is that it’s handy if I want to compare my log to my clocked hours on my paycheck, but it’s not a big motivator and it’s like the lawn…it grows and needs trimming and management periodically.  It’s not a motivator because it’s not easy to see the difference between a day worked hard and a day worked only so-so.  Something a bit more graphical might be useful for that.
I’m trying Klok for that reason.  Lifehacker readers give it the time-tracking five nod, so it can’t be all bad.  So far, it’s simple enough to make it easy to track a few projects at once.  It’s graphical, so it’s easy to see how hard I’ve been working which is nice.  It stays out of the way.  I’m looking forward to seeing how well it puts data out for timesheets/excel/tracking on issues.  It’s not an issue-tracking application, it’s not a project management application, which are reasons I like it so far.  I like applications that stick to doing one thing well and offer ways to integrate with my existing apps.
As far as project planning goes, I’ve got Redmine and some other project planning applications, but nothing I’m really happy with for just showing the overall train of things and where I am.  Today I broke out XMind and made a simple flow for one of my projects using the tree view, which allows for some order.  It’s not really meant for such things, but it’s good because it’s simple and visual, flexible to rearrange and has good status icons.  Not shareable enough though.  Their online mindmap sharing is pretty weak.  I’d like to post an editable, viewable version to Redmine’s wiki, but there’s currently no way to do that and not looking good on the horizon.  In the meantime I’ve decided to just save a copy of the picture to the netdrive to link from the Redmine wiki.  I added a lot more explanation of what the whole process is on the wiki page itself.  So the picture is kind of the “milestones” and where I am, whereas the page text is the more detailed plan.  It might be appropriate to link some of the items on the page to actual issues in the tracking system.  Of course, the overview is still useful (although maybe I should check out the Redmine Gantt?), so despite the fact that it’s not integrated it’s handy to make a unified overview picture.  I think I’ll keep it.
Still trying to get the right balance of planning, action and tracking.

Double Driver backs up and restores vendor-specific drivers

Whether you are reinstalling Windows on your machine or just have a bad driver update, Double Driver can save your bacon.
I recently applied an ATI driver update through FileHippo, which is usually a no-brainer.  In this case, however, either ATI or FileHippo messed up and my machine booted to a black screen.  While I had to reinstall the old drivers through Windows Safe Mode, I was wishing I had used Double Driver.
I also plan to use it to back up the old drivers from an install before reinstalling Windows.  You can use Double Driver to both back up as well as restore the drivers for your particular system.  For example, even if I’ve been good about keeping all of my laptop’s original driver zip files from the manufacturer’s downloads, there’s a lot to keep track of.  Do I remember which of the arcanely named files contains which driver?  Which ones did I really need versus which were optional?  Which ones installed annoying applications that take over during startup?  Where are the CDs for the extra peripherals I’ve added?
While you still may want to remember which of those annoying applets you want to install (DD won’t do that for you), you can be sure you have the right drivers and just the drivers within a few moments of running it.
The way it works is to, like the device manager, list all the devices on your system.  It automatically knows which ones are Microsoft-supplied.  The default backup behavior is to extract all of the non-Microsoft (so presumably, the ones you had to supply) drivers to the directory of your choosing.  One button (and a directory selection) backs them up, and one button restores them.  Simple.
I don’t plan on formatting home without it.

Windows Explorer replacement replacement

I’m not a big fan of the default Windows Explorer file manager.  I always want to see the tree view, but you always have to open it manually.  I like being able to see my source and destination folders in one window (dual-pane), but Explorer makes you use the tree view or open two windows.  There are a number of other little features it could use (tabs, favorites/bookmarks, etc).  Things are better if you move up to Vista or Windows 7, but that’s not an option for me.
Thankfully, there is no shortage of Explorer replacements, including:
  • Xplorer2 Lite
  • Nomad.NET
  • FreeCommander
  • TotalCommander
  • FARCommander
etc, etc, the list goes on.  Most of these are free although some are lite versions of commercial products.
I haven’t been completely happy with any of the replacements I tried.  Most were overly complicated.  Whatever a replacement did, for me it had to resemble the original explorer enough that I might recommend it to less technical friends.  Many of them changed the way things worked enough that they no longer worked the same way as exploreer for, say, file selection.  That’s a no-go.
I had finally settled on Xplorer2 Lite but I wasn’t completely happy.  It had a good tree view, panes, tabs, file preview and a reasonable interface.  However, there were always a number of nag screens and teasers about the commercial version.  It worked for me for a long time, but the kicker was finally the fact that switching between the panes and tree view was just annoying.  The implementation only required a couple extra clicks, but it’s something you do so frequently that it gets on your nerves.
I finally gave UltraExplorer a good once-over.  I believe I had tossed it before because of a cluttered interface.  While it is still cluttered, it’s the last kid on the block, so I’m willing to deal with that if it works more smoothly.  Fortunately it does.  After rooting through the options, I have a good dual-pane manager set up the way I like it.  The tree view tracks that panes, the back button works when I’m in the tree view, and it has a good command line window and drop stack integrated in.  Although the UI is slow sometimes on my machine, and the preview mode is fairly worthless, it’s replaced Xplorer2 as my preferred explorer replacement.

Windows Explorer replacement replacement

I’m not a big fan of the default Windows Explorer file manager.  I always want to see the tree view, but you always have to open it manually.  I like being able to see my source and destination folders in one window (dual-pane), but Explorer makes you use the tree view or open two windows.  There are a number of other little features it could use (tabs, favorites/bookmarks, etc).  Things are better if you move up to Vista or Windows 7, but that’s not an option for me.

Thankfully, there is no shortage of Explorer replacements, including:

  • Xplorer2 Lite
  • Nomad.NET
  • FreeCommander
  • TotalCommander
  • FARCommander

etc, etc, the list goes on.  Most of these are free although some are lite versions of commercial products.

I haven’t been completely happy with any of the replacements I tried.  Most were overly complicated.  Whatever a replacement did, for me it had to resemble the original explorer enough that I might recommend it to less technical friends.  Many of them changed the way things worked enough that they no longer worked the same way as exploreer for, say, file selection.  That’s a no-go.

I had finally settled on Xplorer2 Lite but I wasn’t completely happy.  It had a good tree view, panes, tabs, file preview and a reasonable interface.  However, there were always a number of nag screens and teasers about the commercial version.  It worked for me for a long time, but the kicker was finally the fact that switching between the panes and tree view was just annoying.  The implementation only required a couple extra clicks, but it’s something you do so frequently that it gets on your nerves.

I finally gave UltraExplorer a good once-over.  I believe I had tossed it before because of a cluttered interface.  While it is still cluttered, it’s the last kid on the block, so I’m willing to deal with that if it works more smoothly.  Fortunately it does.  After rooting through the options, I have a good dual-pane manager set up the way I like it.  The tree view tracks that panes, the back button works when I’m in the tree view, and it has a good command line window and drop stack integrated in.  Although the UI is slow sometimes on my machine, and the preview mode is fairly worthless, it’s replaced Xplorer2 as my preferred explorer replacement.

Great Software, Part 2

This is actually part 3 since I already mentioned Inkscape and WinSplit Revolution, but I guess that’s even wrong since I talk about great software in almost all of my posts. No matter.  This is number 2 with that title.
I’ll mention three this time: Acoustica Mixcraft, Super Flexible File Synchronizer soon to be known as ExtremeSync and Launchy.
First, Mixcraft.  If you are a PC-wielding musician, or fancy yourself to be one, such as myself, then you turn green with envy at your Mac brethren who not only have fantastic and simple multi-track recording application in GarageBand.  To add insult to injury, they get it free with their Mac!  I guess that’s why they pay through the nose just to get the entry-level model (thumb at nose).  Disclaimer: I think Macs are great.  I don’t own one and probably won’t again any time soon, but I recommend them to some of my friends, especially musicians.
Mixcraft is a GarageBand clone, which means that it is beginner-oriented but sophisticated enough to do some great recording.  I couple mine with a PreSonus AudioBox USB to do home recording.  I’m extremely happy with the combo.
Mixcraft is an eye-pleasing multi-track recording application with loops, effects, instruments, just tons of stuff to play with.  After having used it for several months, I can say it is quality all the way.  And the kicker is that it’s ultra-cheap for an app in this segment…just $65.  If you’re looking to get into recording on the PC, just get it.  Now.
While messing around with this post, I (finally) started synchronizing my 33GB of music from my laptop to my desktop.  This is no small undertaking for my hard drives.
This leads me to SFFS.  Super Flexible File Synchronizer (forgive the name, it’s worth it and it’s going to be renamed to the more svelte ExtremeSync anyway) is a file synchronizer deluxe.  While a task like this could be accomplished by free software such as the worthy SyncToy or Unison, some of the features in SFFS make it the heavyweight champion of file synchronization.  I’ll modify that statement a bit: I did see other solutions that claimed to have the same features for the most part.  I did not evaluate all of them.
SFFS has a number of different advanced features I’ve found useful.  It can be run as a Windows service, which means it can be an automated file mirroring service.  Appropriately, it emails you the results of its job.
It also is as smart as you need it to be.  Two features useful for keeping repositories of large file in sync are move detection and delta syncing.  It can detect moves of files from one directory to another, saving a network copy, and it can also scan files and only transfer the differences between the files.  Also useful to keep network traffic down.  Since I sometimes keep multi-gigabyte files in sync (virtual machines), both are features that appeal to me.
Finally, Launchy.  I’ve written about Launchy before, but I just need to plug it again since I use it so much.  It’s a keystroke launcher that pops up with the Alt-Spacebar combination and indexes your entire Start menu and Favorites.  It’s the modern day equivalent of tab-completion at the command line.  Note that it is a menu-indexer, not a command-line prompt, however.  If you have a lot of programs or are just into accelerating your computer usage, it’s awesome.