RE: Database config for large table

Also, to anyone following this thread:

If you are having problems with the performance of a particular query
you should really take the time to do a little trial-and-error
optimization testing of different forms of the query.

From what we have observed, the database engine can't always make the
correct assumptions about the contents of a database, and there have
been several instances where just rearranging the conditions in a where
clause have made a query run 100 times faster.

Specifically, we have found that moving our most restrictive conditions
up to the front of the WHERE clause often helps.

Also, as I mentioned in this email thread, moving some conditions into
sub-selects in the FROM clause can also have a big effect.

Have fun.

-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Joel
Garringer
Sent: Thursday, April 06, 2006 12:49 PM
To: [email protected]
Subject: RE: Database config for large table

I will try upping the buffered_io_max_pages to 2048 and we will run with
your suggestion of 32 meg for the cache. That really seemed to help and
we can always increase it latter.

Forcing the query to work a certain way based on what we know about the
contents of the table does make sense, I just wanted to make sure that
the apparent performance increase wasn't just in our imagination or that
restructuring queries in that way wasn't something we would regret
later.

Thanks for your help, and thanks for developing Mckoi. We have been
using it for about two years now and it has helped us on _many_
projects.

Joel


-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Tobias
Downer
Sent: Thursday, April 06, 2006 3:15 AM
To: [email protected]
Subject: Re: Database config for large table

Joel,

There isn't really a formula for finding the optimal cache size for an 
application because queries result in such a variety of access patterns 
that it's difficult to predict what size cache is best.  Mckoi does 
default to rather small cache sizes to ensure the heap usage is in line 
with the typical default JVM max heap size.  The other property I 
suggest you add or change in your configuration is 
'buffered_io_max_pages'.  It defaults to 256 which is only 2 MB of 
memory.  Try setting this property to 2048 or greater.

Nesting queries to force the query planner to take a certain path to 
resolve a query is a very sensible technique.

Toby.


Joel Garringer wrote:
> Based on previous messages in the mckoidb mailing list, we were making
> sure to use one connection for the life of the application.
> 
> I know it is pretty sad that we hadn't tried it already, but the
> increase in the data_cache_size does seem to have helped for many of
the
> queries. Is there any way to know the point of diminishing return for
> giving it more memory? Are there any other settings we should also try
> changing?
> 
> Some queries are still performing strangly. All of the test below were
> performed using the Mckoi JDBC Query Tool.
> 
> In it's original form:
> 
> SELECT MIN(ed.SampleIndex) 
> FROM EventDetail ed, EventHeader eh 
> WHERE EventType=2 
>   AND ed.SampleIndex>200000 
>   AND eh.LocatesPriority 
>   AND ed.SampleIndex=eh.SampleIndex
> 
> This query took around 2 second to execute against a particular
> database.
> 
> We modified it to:
> 
> SELECT MIN(ed.SampleIndex) 
> FROM 
>   (SELECT * FROM EventDetail 
>   WHERE EventType=2) ed 
>   JOIN EventHeader eh 
>   ON ed.SampleIndex=eh.SampleIndex 
> WHERE ed.SampleIndex>200000 
>   AND eh.LocatesPriority
> 
> In this form the query took ~0.02 seconds when it is run using version
> 1.0.2 of Mckoi. Against version 1.0.3 this form still takes ~2
seconds.
> Removing the "AND eh.LocatesPriority" under 1.0.3 drops the time down
to
> ~0.02 seconds.
> 
> We have discovered in situations where a filtering where condition
(such
> as the "EventType=2" in the example above) is going to cut down the
> number of records significantly, applying it before the join
conditions
> can significantly up the performance. Does this sound like a sensible
> technique?
> 
> Thanks for your help.
> 
> Joel


---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]



---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]



---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]