Tuesday, September 13, 2016

The Tao of DB2 - Part 7: Dealing with Performance Issues

The last time we checked in on our DBAs (the soon-to-retire mentor and his intern) the mentor was schooling his young intern on storage and data retention issues. But there is still much to learn, as our intern will soon find out!

"Soon you will get the chance to learn about performance tuning," said the mentor, as he nodded solemnly in his chair. As if on cue, one of the programmers came stumbling into the DBA's cubicle muttering "There's a problem with DB2!"

"Guilty until proven innocent again," mumbled the senior DBA as he rocked back in his chair and smiled. His apprentice though was running back and forth from his terminal to the developer’s area back to his phone and over to his monitor. Frustrated, the intern yelled over at his mentor “How can you be so calm? Things are falling apart!”

“Things are always falling apart and yet life goes on,” muttered the senior DBA under his breath. Not only did he have years of experience as a DBA but he was also a practitioner of Tao. As the apprentice continued running around like a chicken with its head cut off the Taoist DBA calmly glided his mouse across the top of his desk, clicked, and typed something on his keyboard. In a minute or two the apprentice walked into the senior DBA’s office and said, “Boy am I glad that is over. I don’t know what exactly I did to solve that problem, but I am exhausted.”

The senior DBA shook his head and pointed to a printout he had thumb tacked to his wall that read “Don’t Panic!” He showed the apprentice the script he had run and told him why it solved the problem. A good DBA is prepared to resolve issues he has seen before because he has must be a packrat with a good memory. “That means you must learn from every action you take,” said the senior DBA. “Running around like a crazy man when things are going wrong will never help. Remaining calm and taking things one step at a time will help. And always remember the situation and the steps you took to remedy it. Things have a way of repeating themselves.”

The senior DBA continued, “Skill and knowledge are required to be a successful DBA, but sometimes you need to master the art of getting out of your own way. You must learn how to act without forcing conclusions.”

Although learning from past experience is important, most performance problems are not solved on the first try; many performance problems require multiple things to be changed for results to be achieved. It is important, too, to remember the DB2 performance tuning hierarchy. Start with the SQL and application code, because that is where most of the problems reside. Move to the database structures and organization after that. And finally, if the problem has not been isolated yet, move on to the system and DB2 subsystem. Furthermore, tune one thing at a time and test between every change you make!

Just then, another visitor comes by stating that things are looking grim in production for the new system. "One of the programs is just taking forever to run! It was very efficient in the test system..." 

"Oh, I am sure it was," grinned the experienced DBA as he sent his intern off to check the production access paths. 

"Hey look, here is a table space scan!" shouted the intern. "Oh, that was there in test, so it can't be the problem, right" questioned the developer. The experienced DBA directed the intern to build a new index and rebind the program and everybody was happy again. 

The lesson here is that the DB2 optimizer formulates access paths at bind time based on the statistics that measure the amount of data and its make-up. The test system obviously had less data so a scan was not a problem... but more data in production caused performance issues! It is important to build effective SQL that encompasses as many of the “needs” of the application as possible; this eases performance tuning. And as data patterns change and DB2 evolves new features, rebinding the SQL can quickly adapt the program to the data and updates. 

The intern requested of his teacher, “Teach me all the mysteries of the DB2 Optimizer.” The Taoist DBA replied “A mystery is named such because it cannot be fully understood. Sometimes that which we cannot understand 100%  is still well-designed and required. As Heraclitus teaches us 'The unseen design of things is more harmonious than the seen.'"

The lesson here is that we can (and should) take the time to understand how the Optimizer works, but we must also understand that nobody (outside of IBM) fully understands every nuance of the optimizer. We can learn general rules, one of which is to entrust the optimizer to create more efficient access than we can write ourselves. Additionally, perform as much work as possible within the SQL and always remember: the DB2 code is more efficient than my/your code!

"There is sure a lot to learn here. Even though I've tried to take your lessons to the programmers they continue to blame DB2 for their errors,” lamented the intern. 

“A monkey chatters until you give it a banana. Then the monkey chatters again tomorrow,” said the experienced DBA. "But that is a bit unfair. Those programmers have a lot on their plate and cannot become as experienced as you will become with DB2. You must exercise patience and wisdom."

"So is there more that I need to know about DB2 performance management?" questioned the intern.

"Patience," replied his mentor.

Until next time...