Re: How to tune database with lots of very light queries

  • From: "Mark Brinsmead" <pythianbrinsmead@xxxxxxxxx>
  • To: zhuchao@xxxxxxxxx
  • Date: Tue, 29 Apr 2008 19:54:40 -0600

3 to 6  gets/exec?  No complex joins?  10,000 executions per second?

It's pretty tough to beat 3 to 6 gets/exec.  At least be working at the *
statement* level.  I would be quite surprised, though, if there were not
some huge opportunities for performance improvement in the application code.

This sounds (almost exactly) like that I would expect with an application
written by developers who do not understand *joins*, and code "around" their
lack of understanding by using loops that issue tens of thousands of small
queries where a single SQL statement could (should) have been used.

On the other hand, perhaps you are working with a lean, mean, high
performance OLTP system where everything has been designed, tested, and
tuned with immaculate care and uncommon skill.  If this is the case, you can
probably just put your feet on the desk and relax until somebody needs
something recovered from backup.  :-)

Okay.  Probably not.

You have not said much about actual performance problems.  Is the database
meeting performance expectations?  Which business functions, if any, are
slower than they *should* be?  Do you have a statspack report?  Can you
reduce PIOs by increasing the buffer cache or implementing a keep pool?  Is
your IO subsystem slower than it might be?  Do you need more CPUs?  Or is
everything just fine?  It *could* be...

On Tue, Apr 29, 2008 at 8:58 AM, Zhu,Chao <zhuchao@xxxxxxxxx> wrote:

> We have  some databases with load profile like lots of light query (mostly
> pk based),  each execution cost only 3-6 buffer_gets and no complex join
> etc.
> But due to excessive executions (10K+ query per second), load is also
> driving pretty high.
> Anyone has experience tune such kind of application/database?  From single
> SQL point of view, they are already perfect.
> --
> Regards
> Zhu Chao

-- Mark Brinsmead
Senior DBA,
The Pythian Group

Other related posts: