I went away on holiday with the servers running very well. Our average CPU was hovering about 4% and the peak was close to 10%. When I came back, the average was at about 40% and the peak was in the neighborhood of 90%. I was not out of the office that long and this kind of a change was something I did not expect so I began to ask questions about what was done while I was out.
If you look back at some of my previous posts, you will see that we use a scaled out architecture that utilizes read and write servers. Our read servers were running 'hot'. The load on our write servers was higher then expected, but not yet in the 'hot' range in my opinion. Replication between servers was also running slower. As a system, something was not quite right. We asked our operations people to help out and run a trace on the SQL boxes to see if there was something causing this problem. They immediately came back and said there was. They recommended a non-clustered index on one of our critical tables which already had a clustered index on the same table and here is why:
The table being hit was being queried against an index, which was clustered. If you're not familiar with the workings of a DB, this means that the index lives in the database on the same physical page as the data the index references. This is pretty common and in most cases, a very efficient way to create an index. It is so common that the default creation of a primary key in SQL is to make it a clustered index.
By contrast, a non-clustered index is built almost like a separate table in the server. It only contains the minimal amount of data needed to find the data you are looking for. Since this is not a complete copy of the data, you can represent much more data per page of 'memory' used. This is important in a couple of ways.
1. You can create 'mini views' of your data that are actually stored on disk that the query optimizer will use in some cases without ever looking at the table. This concept is known as 'coverage' and is a great way to improve performance for some types of queries.
2. You can reduce the number of pages of data that need to be traversed while doing an index scan.
It is this last optimization that was important in this case. When we looked at the query plan being generated, the query was using an index scan to locate the data we were interested in. The index it was using was a clustered index. This means that the index scan was physically equivalent to a table scan. Now there is a time and place for table scans and this situation was not one of them.
After adding the non-clustered index to the 'hot' tables, the CPU dropped back down to where it was prior to my holidays. The impact this had on the rest of the servers was the cpu on the write server(s) dropped and the time for replications dropped. This was a small amount of work with a huge impact.
An now the question that remains is why didn't this happen before the holidays? Well that was easy to figure out. A large amount of data was migrated to the new system while I was out of office. This was enough data that it changed how SQL was caching things. This problem was there before, but it was masked by having lots of ram and a smaller amount of data.
If you found this information useful, you can thank a member of our team that asked why this helped. I figured that if one person might ask this kind of question, I am sure others will as well and decided to post it to the blog.
Yesterday I posted a way to do negative DB unit testing. Essentially you wrapped your negative case in a TRY/CATCH block in SQL. There is another way to do this that is a little bit more work but, in my opinion, a little bit more correct. In the example I gave, there was a call to an API to create a user record that had no parameters. The test was created in a database test class called 'apiTest_createUser.cs'. Create a new test in this class and call it 'noDataSpecified'. Insert your code that you expect to error. In my case, I will use this:
EXEC Api_CreateUserRecords
Save your test. Goto the solutions explorer and right-mouse button on the test class you just saved (apiTest_CreateUser). You will see the option to 'view code'. Select that and you will see the code the designer is managing for you. Find the method that corresponds to the name of the test you created (noDataSpecified). The method will be prefixed with '[TestMethod()]'. Add another line below that which looks something like this:
[
ExpectedException(typeof(System.Data.SqlClient.SqlException), "There were no parameters specified")]
That's it. I am not sure which approach is the "right" way to do this type of DB testing, but both seem to work. My preference is to use the attribute on the test method, but this seems to take more time to do and if you have many of tests to create, time is an important factor.
Rescently I began using VS testing facilities to create test cases. Its pretty nice, but there is one thing that bothered me when I discovered it wasn't in the UI.
Here is the case:
You add a new database unit test to your project. You open the designer for the new test case and find a defaulted test condition (for inconclusive). You create a test case to test a stored proceedure for a negative case where you expect it to raise an error. You mouse over to the test conditions and look for the 'Error Raised' option but find its not there. You scratch your head wondering how you are supposed to specify that the intended result of this test is an error. You use your favorite Internet search engine to see if you can find an example of how its supposed to be done. Nothing. Now what?!
Well it turns out the answer is not that hard. You write a small bit of code to wrap your test in that returns a result when there is an error.
Here is an example:
BEGIN TRY
EXEC Api_CreateUserRecords
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
END CATCH
You then goto your test condition and set it to "Not Empty Resultset". Of course you could select more information that what I have shown. The core message here is: Wrap your negative test case in a 'Begin Try/End Try' and 'Begin Catch/End Catch'.
Here is a little 'feature' I hit while building some database unit tests in Visual Studio.
The symptom is you get a message that says something along the lines of:
The item "obj\Debug\DbTestPrj.testName.resources" was specified more than once in the "Resources" parameter. Duplicate items are not supported by the "Resources" parameter.
After digging around and examining all of the project files, I could not find any configuration that was specifying the resource more than once. I did the first thing most people will do and did an Internet search for the problem. I never found a good solution in any of the search results, so I did more digging in the code and project files. Eventually I found the problem by doing a search of my entire disk for a fragment of the testName. This is when I was able to figure out what happened.
While I was building my project, in the solution explorer, I did a copy/paste of one of the tests and renamed the file. Under the covers, the class name was still the same name that was used in the file I copied. This is somewhat hidden from you as a user unless you do a "view code" on the test case.
Once you find this problem, its pretty trivial to fix. Manually change the name of the class and the problem goes away.
In hind site, this is kind of an 'Oh doh' type problem. I don't normally expect the name of the class to change when I change the file name, but since the class is somewhat hidden in the UI, you forget about this little detail when you do a copy/paste operation.
I am posting this because I spent a good hour trying to figure out what the heck was going on and didn't find anything recommending a way to fix it. Hopefully this helps out some and save you some time.
I recently wrote about some issues that we had to diagnose in our production environment. I wanted to take a moment to document an interesting problem that had significant performance impact, had interesting symptoms, and was a rather easy fix once it was identified.
"I've just picked up a fault in the AE35 unit. It's going to go 100% failure in 72 hours." *
Our first clue that we had a problem was that the system was running 'slower' than normal. During normal SQL profiling, we discovered that there were some queries being run about every 100 milliseconds which were requesting the same data each time. In a few minutes, we would see around 7,500 requests for the same data.
"I'm sorry Dave, I'm afraid I can't do that." *
At this point, the proverbial alarm bells were being sounded. A concern was raised that maybe caching wasn't working in our code. Our test team ran testing against the old code and the new code. In both cases, the data indicated that caching was working. We decided to debug the cache code in production and found that the system was caching the results of that query as expected, and immediately dumping the data from cache, giving the appearance of a non-functional cache.
"I am putting myself to the fullest possible use, which is all I think that any conscious entity can ever hope to do." *
Now that we knew why it looked like caching wasn't working, we needed to find the cause. Our first step was to look at the performance metrics that the OS provided. Most specifically, the amount of memory that was available.
The above graph shows four different machines under a production level of load. Notice that the available memory is practically a flat line. There was more memory available on the systems, but it wasn't being used. Now what could cause such a problem? Our suspicion was that there was a configuration limit we were hitting that was artifically keeping the cache from growing. To test this out, we create some code that we could use to force new data to be loaded into the cache. The idea was that if there was memory available and caching was working, then asking for new data to be cached should displace some of the available memory. When we ran the test, the line continued to stay flat. That was an 'aha' moment. We were now about 95% certain that the issue was related to the configuration of the cache.
After a little bit of research, we came across this information: 'cache Element for caching (ASP.NET Settings Schema)'
After digging around and talking with some people, we were able to get some detailed information about how caching is configured:
“If you set <cache privateBytesLimit> and/or a private bytes memory limit for the worker process (in IIS Manager), we use what you set, or the minimum if you set both. If you don’t set either, on an x64 box we use MIN(60%*physical memory, 1 Terabyte). The private byte limit for the cache is independent of percentagePhysicalMemoryUsedLimit. We have two limits, one for private bytes used by the process, and one for % physical memory used on the machine. They’re independent of each other.”
"It can only be attributable to human error." *
After configuring a system with the cache settings, we ran the same test as before. This time, the available memory was affected.
This graph shows the memory fluctuating across multiple machines when our test is run. Exactly what we expected.
Running a SQL trace on our systems with the same level of load on our IIS servers now shows that those pesky repeat queries are practically non-existant. Now we see the query about once every 15 minutes instead of once every 100 milliseconds. Remember that the symptom was our SQL queries were running slower than expected but the problem was not a SQL problem. It was a cache configuration problem which resulted in a huge number of queries being run that shouldn't have.
* Quotes from '2001: A Space Odyssey', 1968
I have not had a chance to post much since I started this blog. My intent was to lay some ground work for building scale-out, high perf systems for medium scale sites. It seems like the best laid plans are always interrupted and my vision for this blog is no different.
These plans were interrupted by production issues that needed to be addressed with the systems we support. We recently implemented several changes that ultimately help us, but caused us some short term grief. I am not going to go into all the issues but I will try to cover a few of the things we learned in the last two weeks.
The database is not always the problem
Of all of the issues we have had to hunt down in the last few weeks, 2/5ths were not database issues, even though it looked that way at first.
The first non-database issue was interesting. At first glance, it appeared that our web services were not responding. This was the exact symptom we say when there was a database issue, so it was natural to look there first. After looking at the database servers, we found that they were working normally. The performance numbers on our VMs looked good too. Ultimately it ended up being a runaway process on the VM host that was starving the VMs of CPU.
The second non-database issue was very interesting (at least to me) to find. The first indication that we had a problem was during a SQL trace of a production machine, we found that the same query with the same parameters was being executed about every 200 milliseconds. This was strange since we were supposed to be caching these kinds of requests in our front end servers. Investigation of this problem actually found two issues: our caching was mis-configured on our front end servers and our VMs did not have enough memory to effectively cache the amount of data we wanted to cache.
Caching was working exactly as it was supposed to. I can't go into all the details of how we configure our machines, but the problem was that one of our applications on the VM was consuming most of the available cache on the machine. The application was tagging data that it was caching as high priority data. All of the other data was being cached and immediately being flushed from the cache, giving the impression that it wasn't cached. To find this problem, we had to do a SQL trace at the same time we were debugging a production server. This was one of those problems where you scratch your head a little bit, identify what could be causing the problem and look for each condition.
Sometimes the Database is the Problem
One of the changes we have recently made was the migration from SQL 2005 to SQL 2008. If you haven't used SQL 2008, it rocks. However, anytime you change engines like this, there are bound to be little problems that pop up. For us, we had two.
Both issues manifested themselves as performance related issues that looked exactly the same. On SQL 2005, we had already learned that for our application, we wanted to be careful with how we used the maximum degrees of parallelism (MDOP). In fact, we had set this to 1 for our production environment. MDOP is a great feature, but is some conditions, it can actually hurt performance and our system is definitely one of them. Unfortunately, during the migration to SQL 2008, the default setting was not modified to what it should have been. As you can imagine, this resulted in a performance problem that was ultimately fixed by just modifying the setting on SQL 2008.
The second problem is more a result of changes in 2008 that affect us in a negative way. Our system has complex queries that include XML (XQuery) components. When these queries pass through the query plan optimizer, enormous query plans were getting generated. The query plan optimizer doesn't keep statistics on all XML so when it was trying to optimize these complex queries, it was doing a poor job on the parts that had XML. In fact, when we compared the query plans over time, we would get different query plans for the same query. In all cases, we found that we were spending way more time in the XQuery parts of the query plan that we had in the past. This problem had been present in 2005, but for a number of reasons, we didn't really experience the issue until we were in SQL 2008. The ultimate fix for this problem was to refactor the SQL code into simpler logic.
One of the first things to do when you have performance issues is to find the low hanging fruit. My definition of low hanging fruit is any change that does not affect business or application logic. For example: Adding a much needed index on a table for coverage.
Although this list is by far not complete, it covers many of the common areas I have found performance issues with:
- Table fragmentation
- Missing indexes
- Poorly constructed stored procedures
- Large keys where smaller keys would suffice
- Joins on 'large' data
- Dynamic queries where not needed
- Cursor (or cursor like) operations over large sets
- Locking issues
- Dynamic DB growth
- Incorrect DB recovery mode for the scenario at hand
- Not enough ram or disks
- Triggers
- 'Always up-to-date' mentality when not needed
- Constraints which effectively never get used
- Poorly configured server
Let me give an example. Below are some numbers from DBCC SHOWCONTIG. I have removed some of the information to allow us to focus on the problem:
DBCC SHOWCONTIG scanning 'someExampleTable' table...
Table: 'someExampleTable' (xxxxxxxxxxxxx); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: xxxxxx
- Extents Scanned..............................: xxxxxx
- Extent Switches..............................: xxxxxx
- Avg. Pages per Extent........................: xxxxxxx
- Scan Density [Best Count:Actual Count].......: xxxx% [xxxxx:xxxxx]
- Logical Scan Fragmentation ..................: 97.72% <--------------------------Important
- Extent Scan Fragmentation ...................: 95.18% <---------------------------Important
- Avg. Bytes Free per Page.....................: 2823.0
- Avg. Page Density (full).....................: 65.12%
These are real numbers from a real system. This is one of the heaviest hit tables in the system. The query plan often generates table scans for queries against this table.
If you didn't catch the issue, see the 'Extent Scan Fragmentation'. This number presents the degree of physical 'fragmentation' for this table. You can think of this as a kind of indicator that represents the order of the data on your disk. A lower number means the data is more sequential on the disk. A higher number means the data is more randomly ordered on the disk. When you have a higher number and your queries generate a table scan, in essence, that means instead of doing sequential I/O, your end up doing random I/O. This can reduce performance as much as 3X.
One way you might 'fix' this problem is to rebuild your clustered index. In essence, physically reordering your data on the disk. This might make your system run faster for a while, but it will not really fix the problem. The problem was caused by having a clustered index on the table where the index is built over a non-monotonically increasing value, like a guid/unique identifier. Fixing the problem would be doing something that reduces the fragmentation. Rebuilding the index is treating the symptom. In some cases this is the best you can do.
Much of the low hanging fruit can be found looking at your perf counters. For example, if you see that your CPU is somewhat low, but your disk queue is high and your seeing a lot of page faults, you probably have an I/O problem caused by not enough throughput from your disks (add more spindles) or not enough memory (add more memory). If you find that your throughput is poor, but your perf counters look normal, make sure you examine your locks and network interface.
Something else that I have found all to frequently is the use of value types for identity which are way too big. For example, if you have a domain with four values in it, using a unique identifier is completely valid and over kill. I saw an attempt at building a data warehouse where a large dimension had a guid for the primary key on the table. A shopping cart analysis over a small sample of the data was taking hours to process. Changing the primary key to an int using the same data took only a few minutes to run.
One of the worst performance issues I have seen in the last few years was created on a system that was trying to keep an inventory on PCs. On each new item added to the inventory, a trigger was used to update reports. With small amounts of data, this seemed to run fine. However, once the database was populated, it was not uncommon for a single write operation to add an item to inventory resulted in tens of thousands of read operations to update the reports. This was a two fold problem: First there was no real business need to keep all of the views 100% up-to-date. Second, the system was effectively not usable after running for about 2 weeks. The fix for this was to remove the triggers and add in a semi-regular process to rebuild the reports.
Divide and conquer
Many times you start scaling out without knowing it and this type of scale out is the opposite of consolidation. You identify those parts of your system that are resource intensive and move them off to be on their own. Its just that simple... right? For many scenarios it is just that simple. When you are faced with buy bigger machines or buy more machines, it is often considered safer to buy more machines. If you can logically break apart functions in your system into discrete applications that just happen to look like one big system, you are in great shape.
.oO(Imagine how much easier it will be to upgrade application 'X' when it lives on a machine of it's own.)
Over the past 25+ years, I have seen consolidation followed by separation followed by consolidation... and so on and so on. I don't think this will change in the near future. ( wow.. this whole scale out thing is kind of boring ... ) The industry has made it easier to manage parts of process by doing what? Virtualization... another chapter from the mainframe days. It is amazing what you can do with vitalization, but it isn't the topic I plan on talking about....
Forms
'But wait, that form of scaling out works well for groups of data and/or applications that can be separated off to their own machine. I have a need to hit my data store with (make up some number) 80 web servers. They are all producing dynamic content based on data in the database, the data cannot easily be broken apart.... what am I supposed to do to scale this out?'
To make sure I scope this correctly, I am not talking about a MSN, Yahoo, or EBay sized site. That type of scaling out is well beyond the scope of these blog postings. I am really focusing on systems that may have a few million users a month which need to get content that requires 'a lot' of database server resources and are currently pushing your database to its breaking point. This is the meat of the scale out problem I am talking about. More specifically, when your having problems scaling out your system, it is usually related to accessing a data store, not web pages with static content, so most of the discussion about scaling out will focus on how to increase the apparent throughput of your data store, not on how to add more web/front end servers.
There are three basic building blocks used for scaling out. They can be applied in different ways to make very complex architectures, but there are still only three. They are:
- distributed
- fanned out
- fanned in
Distributed (Many-to-many)
When you have a data store that is distributed, you can read or write to any logical component. The result of each write is copied across all of the machines participating in the distribution. This is often referred to as peer-to-peer replication. There are also permutations of this that are used for special purposes, like disaster recovery, where the writes only occur against one system until that system is not available, then the writes are sent to another system.
This type of architecture is supported by many off the shelf products, like SQL server.
Fanned Out (One-to-many)
When you have a data store that is fanned out, you write to one logical 'master view' of your data and copy that to all of your 'read' servers. Your application supports a higher throughput by using many read servers. This type of architecture is also supported by many off the shelf products, like SQL server.
Fanned In (Many-to-one)
When you have a large amounts of write traffic and need a single view of that data, you often use a fanned in architecture. This architecture is often used for data collection. There are few, if any, out-of-the-box solutions for this right now. There are plenty of tools that help you build this yourself, like SQL Server Integration Services (SSIS), but you still need to create custom logic to make this work.
These three forms are the basic building blocks for making complex systems (even clouds). Each one has pros and cons associated to them. You can treat any database in these diagrams as a logical database which may be implementing scale-out when physically implemented.
I wanted to present some very general ideas about scaling-up and scaling-out before going on to real issues we have had to address in performance. Now I can get into some more specific details.
This week I attended a day of training on some of the new features for SQL 2008. I mention this because one of the presenters made a 'joke' about problems with databases being related to 'needing more ram'. The reason this is of interested is it touches on the first approach most people are familiar with in regards to addressing performance issues on a database/site: scaling up.
Lets for a moment, put on the hat of a person who is building or supporting a site that has poor performance. The database/site didn't start out that way. In fact, it was working nicely.... for a while. At first it was humming along. You hit a web page and it is super quick. As more users began to use the system, it was a little bit slower (but thats OK, its still working nicely). More users hit the site and performance degrades even more. You start to notice that your database is struggling. CPU is a little higher than you expect, disk I/O begin to increase, cache hit rations start to decline and queued disk requests start to pile up.
You know that you cannot afford to rewrite the application or redesign the schema, and your being told more users are coming. If just reading this causes you a little bit of anxiety, well then you are the right person or people to be reading this. (Have I gotten your attention?)
What is the first thing you think of to address this problem... well if your running a 64 bit OS your answer is almost always 'More ram please'. Congratulations, you have made it to the first stage of recovery, er.... scaling up and providing your server can handle it, it is a very cost effective approach to improving performance for most systems.
Imagine the sounds of a calculator and bell going off... chink chink DING.. 'minus the cost per hour of a developer' chink chink chink DING ... times the number of hours for a developer to write the changes .... chink chink chink DING... plus the cost to test....plus...plus...plus.... EQUALS!!!! 'Buy the ram!!!'
(in my best 'old tome of wisdom' voice) 'Heed ye these warnings, you'll be back, with hat in hand, asking for more soon.'
Like a drug addict looking for a quick fix, you will find that the answer to all your problems is just more More MORE. More CPUs, more memory, more disk, more storage, more RPMs, more more more. At some point you may recognize that you have a problem. There is only so much room for more.
Today, there are practical limits to how many CPUs, ram and how many disks you can use. The advent of multi-core CPUs, cheap ram, and terabyte disks has made it very 'affordable' and easy to hit those limits. For most people, the following are a sort of glass ceiling that becomes too expensive to pass through:
- 16 cores (effective CPUs)
- 64 G ram
- ~64 disk drives
Practically speaking, this becomes an expensive box and a critical resource. Why is that the case? For four reasons:
- This box could run in excess of $60,000. That alone could be a deal breaker for some people.
- Since this is a critical resource, you need to have a backup either cold or warm. (add another $60,000 to your bill)
- Since you went down this route, you clearly know about your performance challenges. Before any changes are permitted on the production system, you will want a full performance acceptance stress test done on equivalent hardware (add another $60,000 to your bill)
- You may want your development organization to be able to reproduce production level issues. Depending on the size of your company, you may need to leverage the 'test' machine, or dedicate another box for development. (yup, add another $60,000)
That $60,000+ investment could easily cost about a quarter of a million dollars ($240,000). Of course you may be able to avoid some of these costs through creative resource sharing, but it will be hard to avoid spending at least twice the price that it will cost for your one 'scaled up' box.
Note to self: How many servers could I buy for $240,000? A lot!!! If only there was a way to use smaller, cheaper servers to do what I need. (if only it were that simple)
Not to let you think that scaling up is a negative approach. It is an excellent approach for most small sites. You do not have to make changes to your software. An entire series of tasks is easier: deployment of upgrades or fixes, testing, disaster recovery, systems management, etc.
If you didn't already think of it, you may start to focus on performance related changes to stave off the costs of more, more, more. However, if your 'lucky' you will reach a point where scaling up no longer works.
It will sneak up on you, but you will scale out. You may already have scaled out and not have known it.
Chaos Fosters Opportunity
For the last 6 or so years, I have been working almost exclusively on technologies related to data storage and retrieval. This has included things like object persistence technology (Object Spaces), data warehousing for billion+ row databases, data analytics, and scalable, high-availability, database driven web sites (there maybe a buzzword bingo on that line alone).
I recently changed teams to help address performance and scalability challenges associated with a successful set of social features used across multiple Microsoft web sites, like MSDN and TechNet. If your not familiar with these features, here are a few links to explore where some of these features can be found:
Forums
Search
Tags, Bookmarks and Profiles
As you explore these social features ask yourself how big the data stores need to be to support something like this. I can tell you it isn't small. I can also tell you it isn't 'big' (like http://www.live.com big). It is kind of in the middle. Its big enough to require special attention to scaling out and performance, but no so big as to warrant vast amounts of specially written data storage and its associated technology.
Over the next few posts, I will explain some of the things that we did to scale our system up and out, challenges we had along the way, ideas on where it will go in the future and some considerations you might make if you take the same trip.
