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]