VistaDB 4.3 Performance Optimization
By · Comments
Download VistaDB 4.3
We are happy to announce the biggest update to the VistaDB engine since Gibraltar Software took over the product last year. It’s actually our fifth update, though our earlier releases were more limited in scope consisting of a new streamlined licensing system and a number of bug fixes—often providing closer compatibility with SQL Server scripts.
The main focus of VistaDB 4.3 is query performance, particularly with multiple JOINs. We’re pleased to report 2.5x improvements in many cases and discuss below what’s happening inside the VistaDB engine to achieve these results. Let’s start with an overview of how VistaDB produces query results.
How Does My Query Work?
VistaDB queries are executed in three general phases:
- Parse: The SQL query text is parsed into a tree of objects representing each language element.
- Prepare: The statement/expression tree is recursively processed to identify table and column references and to determine the data types of results.
- Execute: The statement/expression tree is recursively processed to execute statements and provide results in order (returning to the calling application as each row is ready).
If there were no optimizations at all, the engine would walk every row of the parent table. And for each of those rows, then walk every row of the first joined table looking for matches to the ON clause. For each of those rows it would then repeat the process for every row of every additional table referenced in the query. Obviously, this would be ridiculously slow when joining multiple tables of any real size, or even when querying a single table with a large number of rows when you don’t actually want most of them.
To be more efficient, VistaDB performs an additional optimization step at the start of the Execute phase. This step recursively walks the WHERE clause and ON clauses and converts the comparison expressions (and special functions such as BETWEEN) into a more efficient representation as constrains in which each constraint specifies a range of values for a particular column based on constants, parameter values, or the value of a column from an earlier table in the parse tree.
In optimizing the parse tree, VistaDB simplifies the execution plan into a series of constrained tables you could imagine as being evaluated left-to-right. Constraints that can’t be resolved are declared non-optimizable and must be handled by testing the WHERE clause. Similarly, target columns for which there isn’t an available index are also non-optimizable and must be tested row-by-row. These optimized conditions are then processed for logical ANDs and ORs to calculate an overall optimized filter for each table.
Building On What Already Works Well
VistaDB has always done well with queries in which a range of rows can be retrieved on a single-column index with an identifiable starting and ending value based on the current rows of tables “to the left” of it. For example, if the parent (FROM) table is restricted by a single comparison in the WHERE clause such as: WHERE ParentTable.ColA = @ChosenValue; (with an index on ColA), then the engine doesn’t need to walk every row of ParentTable, it can start with the first row in the index with a value of @ChosenValue for ColA, and walk each row in the index until it passes the last row with a value of @ChosenValue for ColA. If another table is then joined in it doesn’t need to consider any combinations outside of that range on ParentTable; they’re already certain to be excluded by that condition in the WHERE clause.
Improvements in VistaDB 4.3
We use VistaDB extensively in our Gibraltar application monitoring system and noticed that VistaDB performance left something to be desired for some of our more complex queries. For example, it is common to use a placeholder ID (perhaps a UNIQUEIDENTIFIER) as a foreign key into a small lookup table which can contain additional information fields universal to that value. In Gibraltar we have tables such as Application_Type and Boot_Mode which provide caption and description labels for display purposes. They can be joined directly into a query about one or more sessions, like so:
SELECT * FROM Session_Details SD JOIN Processor_Architecture OSA ON OSA.PK_Architecture_Id = SD.FK_OS_Architecture_Id JOIN Boot_Mode BM ON BM.PK_Boot_Mode_Id = SD.FK_OS_Boot_Mode_Id JOIN Processor_Architecture RA ON RA.PK_Architecture_Id = SD.FK_Runtime_Architecture_Id
The joined tables are tiny, only 5 or so rows each, so (in theory) this should be very efficient. Each joined table can have its unique matching row directly looked up based on the corresponding column value in the parent table. Perfect, right? But this query was taking several seconds. The base query (SELECT * FROM Session_Details) took less than half a second, and that’s querying the entire table! What we found in VistaDB 4.2 was that as each JOIN was added to this query, the overall query time nearly doubled! Something was clearly less efficient than it should be.
As we analyzed the engine internals, we found a lot of opportunities to improve performance which we’ll be implementing over the coming year. As a first step, we decided to focus in VistaDB 4.3 on reducing the overhead for multiple joins and optimize for the most common cases.
We expect that the majority of joins will be on a single equality between a single column from each table with a foreign key relationship between them. Since this should by definition identify a single value (and often a single row), it should be the most efficient filter to narrow down the joined table based on those “to the left”. So the optimization logic will now catch these top-priority conditions early on and bypass the rest of the expensive reduction pass.
And in queries like our example, we integrated column value caching to eliminate the need to search for the same rows over and over again. When a table is joined on a UNIQUE single column index, the table can cache the row in a Dictionary keyed by the column value from the other table, and each time it comes back to that value, it grabs the row from cache instead of searching the index and reading it from disk again.
We also coded our caching to ensure that it doesn’t consume excessive memory when processing large tables. The cache only holds hard references to the most recently accessed rows. By using weak references to less recently used rows they stick around when memory is plentiful but can be garbage-collected if necessary. For more info on on weak references, check out Kendall’s Code Project article and sample code on creating a single instance string store.
As shown in the graph above, VistaDB 4.3 is several times faster for many common queries. More importantly, in queries such as above, performance degrades linearly as more tables are joined, rather than exponentially as before.
Stay Tuned for More to Come
The query optimizations we’ve introduced in VistaDB 4.3 are just a start. Subsequent releases will have additional query optimizations as well as other performance improvements such as support for bulk insert and enhanced multi-user scalability. We also will be adding new features such as enhanced support for Entity Framework, enhanced compatibility with SQL Server and improvements to our development tools (Data Builder, Data Migration Wizard, etc).
We’ll be writing additional blog posts about our adventures taking VistaDB to the next level, so check back often or leave a question/comment below or in our support forums –we’d love to hear from you!
Gibraltar 3.0 New Feature Dive – Super sized Sessions
By · CommentsA major challenge customers have had with Gibraltar 2.0 is working with long running and large sessions. This stems from the early on design decision to target smart client applications for the first releases of Gibraltar. From this decision we made a series of assumptions – how large sessions would be, how people would want to count and interpret them, when they would get sent in, etc. For example, early beta versions we assumed by default that you only wanted to keep 50MB of total log data and each session would be around 5MB. Looking back, this just isn’t how things turned out at all.
Once you start using Gibraltar with server applications – like web sites or even windows services then a few things happen:
- Long Life: Gibraltar equates a session lifetime to the application domain it runs in. For windows services this could be months, possibly even years. For web sites it’s typically a day or less.
- Session Size: It’s hard to imagine in a single user smart client recording more than 50MB of information. Our analysis of log files we receive and our customers use indicates this is a pretty fair assumption. For a busy corporate line of business web application you can easily generate 500MB of log data each day.
- Submitting Sessions: By default, Gibraltar waits for a session to end before sending in the data. This works well with end-user applications but doesn’t match up with operating your own web sites or services. What use is it to find out a month later your Windows Service was having a problem? You could trigger sending data manually and even when an error happened, but if your session got to large this could be dangerous because we merged together all of the data first to send it.
The biggest problems people have with using Gibraltar 2.x in these situations is:
- If you log a lot of data, you need a lot of memory and processor to submit the data to be able to view it – about five times as much as the log data. To add insult to injury, every time it submitted the data it pushed it all to the server, and that got completely re-analyzed for issues then copied down to each Analyst. Very inefficient.
- You had to add code to your application to explicitly push the session to the server to see any of the data. And when you did you could run into the first problem.
No question about it, this sucks and it had to stop.
Editor’s Note: Not all of the optimizations discussed below have shipped in Gibraltar 3.0 Beta 2.
Smaller Data Files
Our original compression approach was based on a sampling of files between 5 and 50MB. It turns out that the files we really should worry about were in the 50 to 500MB range. Saving 25% on a 5MB file isn’t likely to impress anyone. Cutting 100MB off a log file makes a real difference. We’ve substantially revised how we compress files and will get an average of 65% reduction in size for typical files. That’s right – they’re now 1/3rd as big. This is all done with no impact on the throughput of the log system because of the asynchronous, queued approach Gibraltar uses to record data.
We also reduced the memory it takes in the Agent to perform this compression. Previously it was possible to use up to your maximum log file size in RAM (in extreme cases) while logging. To avoid the problem of merging fragments (which uses even more memory) some customers ran into this when they dramatically upped their log file sizes. Now the compression uses a fixed buffer size measured in KB to ensure it’s both efficient and predictable.
These smaller files have another advantage: We previously zipped files before they were sent to the hub to save network time. This would get us about a 40% reduction in file size but took time, processor, and disk space. With the new scheme there’s no advantage to doing this – the files would actually get slightly larger. So we can skip this step which saves time on both the client and server.
We’ve also added a feature to let you understand why your data files are as big as they are. In Analyst, you can open a session and then click File Stats on the Session Details tab.
Here’s the analysis we get for one of our Gibraltar Hubs we’re using for testing. It logged about 700,000 messages in a 24 hour period.
You can see that the average size of each log message is 57 bytes. Now that’s good stuff from a compression standpoint. The process also recorded a fair number of metrics and performance counters. There are metrics for each data operation, web service call, etc. and these add up to a total of about 350,000 individual samples at an average size of 31 bytes. All of the performance counter samples – 35,000 of them – came in at an average of 10 bytes. All in, the file was just over 50MB.
From this information you can get a good feel for what’s driving your log file size. Now, you may never care – but when you get into logging millions of messages and millions of metrics then it’s good to have around.
Incremental Data Files
For Gibraltar 3.0 we’ve changed it so that the individual file fragments Gibraltar stores data into (each representing a period of time) are never merged together – all the way down to Analyst. When data needs to be made available Gibraltar has always rolled over the current log file (called a session fragment) and started a new one. Previously these fragments all had to be merged together before the data could be sent anywhere off the computer. Now they’re each managed individually. Thanks to this change, each time your application wants to send data to the Hub only the fragments that haven’t already been sent are processed. Likewise when this data needs to be analyzed for issues or sent down to clients that want to view the details it’s just the new information.
We’ve also gone through the processes that send data – whether it’s to the Hub server, email, or files – and worked to eliminate unnecessary file copies that were being done previously in an overabundance of copies (these get expensive if you have a 300MB file) and minimize the memory required. The bottom line is that you can safely send session data at any time – regardless of how much data you’ve logged or how long your application’s been running.
Let Your Application Run Forever
Making the session files smaller and keeping them in fragments helps with large sessions, but when you have a really long application there are a few other things that kick into play. First, you want to make sure you can discard older log data off the computer (to keep down local disk space) even while the application was still running. In many cases, Gibraltar 2.x would end up throwing away the start of the session and all you’d have is the last 150MB of data (if you used the default limits). For 3.0, session fragments can be sent automatically as soon as the log file rolls over for any reason. The default limits of 50MB and one day mean that for each Windows Service you’d at least get a fragment every day – which would be sent up to the server and then pruned locally. You can still trigger sending data more often from within the application like you used to and it’ll work great.
The Hub server works better with this as well – it will analyze each fragment without having to load up information it’s previously worked through. This means you get steady updates and the memory required on the server is dramatically reduced. We’ve had customers with Gibraltar 2.x that had servers perpetually behind analyzing sessions because by the time they analyzed a large file they received another copy of it with just a modest amount of new information. Now this is a non-issue because the server can load just the new data and check it.
Of Course, You’d Like to see All of That
The final problem area for Gibraltar with very large sessions was viewing them in Analyst. Historically you could load up a session with between 1.2 and 2 million messages. More than that and it’d fail with an out of memory exception. This was because Gibraltar Analyst was a 32-bit only application. We’ve rewritten our graphing system to use the DevExpress XtraCharts like we use for Charting and that means we’re good to go for 64-bit in Analyst. If you have 8GB of RAM, you can use all of it for session data if you want. In practice this means you’re still limited to a session with perhaps 10-15 million messages. We’re going to work on that in a future release to really achieve our goal of infinite logging.
This is all fine and good, but it’s still missing something… The ability to handle the server-on-fire-right-now scenario we’ve all experienced at some time in our career. For the story on that, stay tuned for our next deep dive!
Getting to Know Gibraltar – Making a Start
By · CommentsHello there. My name is Gary Short and I’ve recently joined Gibraltar Software. Amongst the other things I’ll be doing (more details on those later) I’ll be evangelising the Gibraltar product.
Now I’ve never used the product and I don’t know anything about it, so I guess I’m on the same sort of learning curve as a new customer when they first download a trial version.
We all learn in different ways and at different speeds, but the way I learn best is through action centred learning, in other words, I like to “do stuff” to learn how “stuff works”. So, the best way for me to learn how to do something with Gibraltar, is to do something with Gibraltar. 
So what’s the simplest thing I can do to get something working? Well after having a quick look at the documentation, it seems to me that the simplest thing to do is:
- Configure a minimalist application for tracing
- Hook up Gibraltar
- Examine the data in Gibraltar.
Okay, so let’s do that!
Step 1. Configure a Minimalist Application For Tracing
Let’s work with the simplest thing, namely a console application with one method and a couple of calls to the Tracing API. Something like this should fit the bill:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Diagnostics;
using Gibraltar.Agent;
namespace GibraltarGettingStartedApp
{
class Program
{
static void Main(string[] args)
{
Trace.WriteLine("Entered method!");
Console.WriteLine("In the body of the method!");
Trace.WriteLine("Leaving method!");
}
}
}
And when we run this, it does pretty much as we’d expect it to do:
Step 2. Hook up Gibraltar
Firstly, start Gibraltar and from the and click the “Add Gibraltar Now” button:
Then, simply work your way through the wizard. Starting with selecting the project file for your application:
Next, since we’re doing the simplest thing possible, accept the default configuration:
Then deselect all the checkboxes.With the exception of “Allow Sessions to be Emailed to you”, which I can guess at, I have no idea what these do, but we’ll find out later. For now, we just want to do the simplest thing that’ll work.
And we’re done:
Okay so let’s run our application again.
Hmm, notice that the console window stays open this time, and we have to physically close it to make our application end – what’s happening here I wonder? We’ll come back and investigate that in a moment, but for now, let’s see what information is available in Gibraltar.
Step 3. Examine the Data in Gibraltar
Open Gibraltar and select “New Sessions”
and in the right hand pane, we see that we do indeed have information from our application!
Now it’s time to investigate that “application is still running” oddity. Looking at the information we can see that the “status” is “crashed”, even though we closed it down properly.
My guess as to what is going on here is that the Gibraltar Agent is holding open the application after the execution path has reached the end of the Main() method. We then closed the window manually, and because our application didn’t end in the way Gibraltar expected, it’s marked this event as a “crash”. It’s time to look up the documentation and find out what’s going on.
A quick read of the documentation at:
Shows us that indeed, when we are using a console application, we are responsible for ending the session. We can also see that there are a number of ways to do this, but since we are trying the simplest possible thing that will work, we’ll just add the Trace.Close() call to the end off our application. So now our code looks like this:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Diagnostics;
using Gibraltar.Agent;
namespace GibraltarGettingStartedApp
{
class Program
{
static void Main(string[] args)
{
Trace.WriteLine("Entered method!");
Console.WriteLine("In the body of the method!");
Trace.WriteLine("Leaving method!");
Trace.Close();
}
}
}
If we run our application now, we can see that our application closes when we get to the end of the Main() method. If we look in Gibraltar we can also see that our application has closed normally:
Okay, I think that is enough for this post. To help us learn how Gibraltar works, we’ve build a minimalist application and enabled Gibraltar to record our trace information. In the next post, we’ll build on these beginnings by looking at what happens when our application throws and error. We’ll also find out if Gibraltar deals with handled and unhandled exceptions differently.
Until next time, happy coding!
After a long time baking we’re happy to announce that Gibraltar 3.0 Beta 2 is available for immediate download. While this is still a beta release, we’ve run it through more testing than normal so it’d be ready for you to try out. This is just a quick overview of what’s new in Beta 2; stay tuned for drill in articles on the high points.
What’s it Mean?
Our focus with Gibraltar 3.0 is:
- Scalability: Big sessions, long running sessions, large data sets, lots of sessions… We want to up the limits in every direction.
- Easy Adoption: We want every team member from the project managers down to the junior developers to be able to get into Gibraltar and be comfortable.
- Servers and Data Centers: Where Gibraltar 2.0 was focused on applications shipped out to hundreds or thousands of computers, Gibraltar 3.0 adds features specifically for monitoring just a few servers in your own data center.
Scalability
When we originally designed Gibraltar we had to pick some target limits for the number of log messages in a session and the number of sessions in a repository. Since our initial target was smart clients deployed to hundreds of systems (this was the customer base that we’d been working with for years that motivated creating the product in the first place) we went back and looked at the size and scale of what had been done in the past. We applied some scale up factors and came up with a target of 500,000 log messages per session and 2000 sessions in a repository.
This turned out to be laughably low.
We now know that people will cheerfully log millions of messages (biggest we’ve seen: 55 million messages in one session that was just 12 hours of data) and will gather tens of thousands of sessions per day. We’ve nibbled around the edges of this problem before – we added paging to the log viewer which let us get from around 1 million to about 2 million messages that still could be displayed.
For 3.0 we’ve made fundamental changes to how data is moved and tracked to make sure we’re ready for large, long-running sessions and lots of them. Still, this is more of a journey than a destination – if there’s one thing we’ve learned it’s that people will push scale until the infrastructure pushes back. We’re working hard to move those limits well out so you can get the most from Gibraltar.
Now with a splash screen - doesn't every app need one?
Making it Easy
We’ve done some usability testing with Gibraltar and the results weren’t what we wanted to hear. The feedback we got was that we’d designed a tool for experts. Not expert developers, but experts in logging and performance monitoring. In one usability session we saw a junior team member be visibly startled by the information overload presented by Analyst. The developer closed it and didn’t want to go in again – too much information to find the detail they needed to work on. Now, we haven’t had a chance to incorporate this research into 3.0 Beta 2 but we have work already underway for the 3.0 release that is squarely aimed at making sure everyone on the team can get value out of Gibraltar. Stay tuned and wait for Beta 3 for a preview of these changes.
Servers and Data Centers
To help out with monitoring your own server applications we’ve added new session categorization features for tracking the environment and promotion level of each session so you can view information by where it was gathered (environment) or the stage in your development process (promotion level). You don’t have to use these at all, but when you want them they’re ready. We’ve enabled long running sessions like windows services to work much better with Gibraltar and added a real time log viewing capability specifically for internal server scenarios so you can see the contents of your web server logs as things are happening, all without affecting your application’s performance.
What’s Changed?
Internally we’ve made a few big changes:
- The Agent works just off files and directories without an index: We’ve dropped the index database approach Gibraltar 2.0 used to instead rely on scanning directories for their contents. This is much more resilient in the face of manipulating files and directories and eliminates the problem of upgrading the index database schema.
- The file format is now much smaller: We’ve extensively optimized our serialization format based on sampling hundreds of thousands of real-world data files. In many cases, log files will be around 30% the size they would be under Gibraltar 2.0. The benefits are particularly visible on large log files.
- Sessions are no longer merged in the Agent: Previously if a session was recorded into multiple files, these files were merged together in the agent before the data could be sent anywhere. This could take 5x as much memory as the file fragments in question, a real problem for long running applications. Now these individual fragments are handled individually all the way down to Analyst.
- There is a new live streaming protocol: In addition to writing data to a file it can now be sent across the wire via TCP/IP to be viewed in real time.
- Gibraltar Hub can now use SQL Server or VistaDB for its database: For larger customers, or situations where you want to do your own reporting and analysis, you can elect to have the Hub use SQL Server for tracking session data instead of VistaDB, which is included by default.
- Analyst is now 64-bit ready: We reworked the graphing in Analyst so we could make the whole application run in 64-bit, helping with large data sets.
Try it Out Today
You can download 3.0 Beta 2 right from our site. To install it you’ll need to have a valid Gibraltar license that’s up to date with upgrade assurance. If you’ve let your upgrade assurance lapse and want to give 3.0 a try, contact us to discuss your options.
If you aren’t a Gibraltar customer, you can get a trial key and then use that to try out 3.0 as well.
We’re planning on one more round of beta for 3.0 after this so there is still time for your feedback to make a difference. Don’t be shy, tell us where we’ve missed the mark by emailing support or post your comments on the forums.
