We recently examined a customer’s system to try to speed up an ETL (Extraction, Transformation and Loading) process for a big data set into a sort of datamart or DW.Â What we typically do is ask customers to run the process in question, and then examine what’s happening.Â In this case, the (very large, powerful) database server was almost completely idle, with virtually no I/O activity or CPU usage.Â So we looked at the server where the ETL process was running.Â It was running at 25% CPU usage and was writing some files to disk, but not waiting on I/O.
What’s going on here?Â Where’s the bottleneck?Â The process is slow, and neither machine is really doing much work.Â Why?
Maybe you guessed the network.Â Nope, not the network either.Â There was plenty of spare network capacity.
If I told you the ETL machine was using exactly 25% of its CPU capacity, would you guess that it had 4 CPU cores and one of them was running at 100% usage?Â This is what was happening.Â The ETL app was single-threaded and CPU-bound.
Of course, we measured the entire process, so we could say authoritatively what was going on.Â But this problem actually took only a few minutes to diagnose.Â The point here is to look beyond the database server for what seems to be a database problem.Â This is why we call ourselves “full-stack performance tuning experts.”Â We try not to have tunnel vision. This reminds me of another problem I helped debug a few weeks ago — a really slow website was due to a curl call that was hidden in the code, and timing out because of DNS issues.
One of the best things you can do to improve your performance is build profiling into your application, or run the application under a profiler (Google for “profiling <language>”) and find the places where it consumes the most time.Â It’s especially valuable to profile “external resource calls” such as calls to the database, web services, and so on. These approaches can make it much easier to find the slow parts.
We devoted part of a chapter to profiling in our book.Â We explain a lot of useful techniques to help you build “light-weight” profiling into the application from the start (a very smart thing to do).
Sometimes our clients believe they already know the source of the problem, but they can’t prove it. They ask us to either prove them right or show them the real problem. The way we do this is to prefer measurements to guesses.
Of course, finding the problem is only part of the battle. Fixing it is another matter. But “how to fix every performance problem” doesn’t fit into a single blog post!