Log in

No account? Create an account
entries friends calendar profile My Website Previous Previous Next Next
Mark Atwood
This is the kind of stuff that MySQL PS Performance Optimization gigs do All The Time...
The Daily WTF: Hastening an Inevitable

Keeping hundreds of millions of sheets of paper on file isn't easy, so the IRS had an application built to computerize their records. It'd scan paper tax returns into a WORM (Write Once, Read Many) drive system and record lookup data in a database. That way they could filter by any fields they recorded in the database and access a scanned image of the tax return for any further information using a simple app, which sure beat the old method of data retrieval - digging through boxes, incurring huge wait times.

The nice thing about the old method, though, was that it generally worked. The new system was full of bugs, in addition to several other irritating issues. On Bobby's first day he was put in front of the application, and right off the bat it looked amateurish. Form elements not lined up properly, buttons not always the same size, inconsistent menus - not broken, but certainly not professional looking. His boss, Boris, explained some of the finer features in a dry, humorless, low monotone.

"Now, as you can see heeere," his boss mumbled, "this is pretty exciiiting." Boris would linger on certain syllables presumably in an effort to sound more boring. His tone made Ben Stein sound like Freddie Mercury. "Allll we have to do is click a feeew buttons annnnd..." Meanwhile he was typing some text into the fields to perform a lookup. "And affffter a few short minnnnnutes..."

Bobby was briefly impressed - Boris had made a joke! Not a particularly funny one, granted, but certainly worth a polite chuckle... except- wait a minute. This is actually taking several minutes, Bobby realized. And in Boris's company, each passing minute felt like ten. Boris whiled away the minutes describing some of his hobbies - toy piano tuning, model taxidermy, palindromatic haiku composition - each somehow more boring than the last. After an eternity (nine minutes) of struggling to keep his eyes open and subtle closed-mouth yawning, an error message popped up.

"Ohhh, fidddddlesticks," mumbled Boris in lifeless anger. As Bobby would eventually learn, the database's average response time for tax form searches was in the realm of 8-10 minutes. That was if you were fortunate, though - it'd often just time out.

Although Bobby wanted to help, he wasn't allowed to lay a finger on the database. He was only authorized to edit the frontend code. So to start off he gathered whatever information he could - the hardware (Pyramid Minicomputer running four 150Mhz processors) and the software (AT&T Unix and Informix DB server). He looked through the code and made a few UI improvements, and it didn't take long for him to locate the biggest bottleneck - the database.

After getting in touch with the DBAs, Bobby was granted read-only ODBC access. Bobby kept making tweaks and little optimizations, constantly running SELECT queries, but couldn't get them to run any faster than six minutes. He dug through ODBC settings, hoping to find something... anything that he could change to improve performance, but judging by the crazy, arcane settings already set up, many had tried the same thing and failed.

Bobby had spent several hours and not gotten far from where he started. Wanting something to show for the time spent, he threw together a quick "Please Wait" dialog box with an animated hourglass to give the bored operators something to watch while they waited for the data to come back.

Several days later, Bobby had a stroke of luck. One of the higher-ups had caught wind of his UI changes and wanted a demonstration. When the topic of speed came up, Bobby mentioned that he'd found the bottleneck and suggested he get full access. It shouldn't be a problem since he worked under constant supervision anyway.

"I don't think that'll be an issue," he said with a smile. "Can you do it right now?"

Bobby hesitated, then gave a weak "...sure." He flushed a little red, concerned that he was wrong and he'd embarrass himself in front of everyone. A DBA was called to the room, who opened up a console window and turned Bobby loose.

Of course, it's highly unorthodox for someone to be given absolute access to one of the IRS's production databases, but no one wanted to question the big cheese. The DBA watched Bobby like a hawk.

Bobby swallowed the lump in his throat, tried to ignore the seven sets of eyes on him, and typed some simple queries in an attempt to isolate the problem. In short:

  • Indexes: none
  • Primary keys: none
  • Rows: ~2M per table, four or more joins per query
  • Processor speed: 150Mhz

Bobby fired off a few commands to create indexes on fields that he knew were frequently queried. The DBA's stare intensified. Bobby felt more nervous, hoping that it would all work ok. And after verifying that queries were still being profiled, he ran a SELECT query.

It took 0.22 seconds.

Crap, he thought. There's no way it could've run that fast. I must've totally destroyed the production database. Wiping sweat off his forehead, he ran the query again, this time to verify the results. And the results were correct.

With each query he ran his smile grew larger, as did the rest of the room's collective disbelief. The "Please Wait" window would appear and hide so quickly that no one could even see it.

They tried to chart the difference, but what's the point? 8-12 minutes versus 0.35 seconds average? Management did all but break out the pom-poms and do cartwheels throughout the office. The operators were so ecstatic that they actually threw him a party. As for us US taxpayers, we benefited as well since we're getting taxed faster!


10 comments or Leave a comment
elfs From: elfs Date: July 8th, 2008 06:29 pm (UTC) (Link)
So... lemme get this straight. A govmn't DBA who's aware of the problem is apparently unaware of some pretty farkin' standard optimization techniques, and bozo the clown walks in off the street with a few hours of MySQL experience under his belt and saves the US taxpayer millions of dollars.

Isn't that illegal? :-)
fallenpegasus From: fallenpegasus Date: July 8th, 2008 08:44 pm (UTC) (Link)
Well, in this case, it wasn't MySQL it was Informix.

And I have no idea if this particular story is actually true.

But it's "truthy", in that things like this are encountered all the time by MySQL Professional Services on Performance Optimization gigs.

You would think that DBAs would know about things like indexes. But you would be wrong.

One gig I myself went on for MySQL PS, the DBA in place did know about indexes and that they are good things to have. So he put an index on Every Single Field of Every Single Table. And then didn't understand why INSERT and UPDATE were so very very slow, and why he was constantly running out of tablespace...
awfief From: awfief Date: July 8th, 2008 10:26 pm (UTC) (Link)
It does indeed happen all the time. However, usually when people say "it's the DB that's the problem" they look to tune server variables, as opposed to schema changes.

My best optimization to date was taking a query that would run for over 4 hours (if it didn't die in the process) and get it to under 10 seconds. The solution? UNIONing 5 queries is way better than using 5 OR's.

But yeah. As Jay Pipes says, most issues are the query or the schema.

In MySQL you only need 1 command -- EXPLAIN.
fallenpegasus From: fallenpegasus Date: July 9th, 2008 02:25 am (UTC) (Link)
Ug. Do you know how UNION is implemented under the hood? Do you WANT to know? It's really horrid...
awfief From: awfief Date: July 9th, 2008 10:09 am (UTC) (Link)
Well, from my experience "better than OR". ;) I'm guessing it just runs both queries and then appends the info together, after a brief check at first that the # of columns is the same and a few other things (ie, no ORDER BY or, I think, LIMIT unless it's the last query, etc).

I would like to know how it works under the hood.
fallenpegasus From: fallenpegasus Date: July 9th, 2008 03:44 pm (UTC) (Link)
The first query is run, and the result set is put in a temp table. Then the next query, and the result set is appended to the temp table. And then the next... and finally the last. And then the temp table is read out into the returning result set, and then the temp table is deleted.

You had better have enough temp table space to hold the entire result set...
From: xaprb Date: July 9th, 2008 05:25 am (UTC) (Link)

Oh, the one-up-ness...

I see your UNION and raise you:

Just Thursday I turned a 6-hour query into 0.08 seconds. Solution: add one index, convert one IN() subquery into a join.

(I am only estimating 6 hours. I think it would run longer; after adding the index, I calculated the IN() subquery would run 8 minutes and it ran 13, so I guess the 6 hours might be 10 or so.)
awfief From: awfief Date: July 9th, 2008 10:06 am (UTC) (Link)

Re: Oh, the one-up-ness...

:) The point for me is "often it's the query or the schema, not the server variables".
From: xaprb Date: July 10th, 2008 02:48 am (UTC) (Link)

Re: Oh, the one-up-ness...

Yeah, I hear the same thing from clients. "My config is not optimal, please tune my.cnf" I rarely change my.cnf much, it's usually indexes, query rewrites, and application changes.
From: gillgunson Date: July 9th, 2008 12:19 am (UTC) (Link)
God, I don't want to know how much that gov't DBA was making, it would depress me.

I think these sorts of WTFs happen enough. I'm reminded of this time at my last job (and one situation that made it easier to leave) was that some developers were doing a search engine optimization project without even consulting me (the only DBA) and when it was nearly completed, one of them asked me if I could have a look in the database to see why their queries were taking 10 hours.

So, they gave me the connection info and I logged in. I looked around and saw that, like the story above, there were no indexes on anything. Maybe auto-increment primary keys, but that's it.

I told the guy "your database has no indexes, and you're doing select *'s joining tables with lots of fields and millions of rows. You need to add some indexes." They also should've had someone who knew what they were doing design the database from the first place, but they were nearing the end of the project, so it wasn't going to happen.

Anyways, when I told the guy to add some indexes, he said:

"Oh, that's okay, I'll just code around it."

I'm so glad I left.
10 comments or Leave a comment