[CentOS] Way OT ... originally "Good value for /proc/sys/vm/min_free_kbytes

Fri Dec 8 19:52:41 UTC 2006
MrKiwi <mrkiwi at gmail.com>

Morten Torstensen wrote:
> John R Pierce wrote:
>> in our case, our production systems are a very large very complex 
>> realtime oracle database running on large scale Sun enterprise 
>> hardware on bigiron EMC storage, using dozens and dozens of raid10 
>> logical volumes as you do NOT want to have a single 10TB volume, 
>> sorry.   by hand optimizing the tablespace layouts of the applications 
>> tables and indicies, which have very specific access patterns, we can 
>> get double the throughput of the blind 'just stripe the universe' 
>> approach.   Since 
> 
> Oh you would not have a single 10TB volume of course: and it is still a 
> little quaint to hand optimize tablespaces. That is something we did in 
> the 90s.  Our own tests on multi-TB databases and modern SAN systems, 
> the shotgun approach beat hand optimization every time. And we are 
> talking about people with dozens of years with SQL optimization behind 
> them. Intelligent I/O prefetch adaption, intelligent and dynamic access 
> plans... the world of performance in the RDBM world is changing and old 
> rules for performance are changing with it.
> 

Im not sure im following the language correctly ...
Morten and John - you seem to be at opposite ends of the 
opinion scale about hand optimising tablespaces?

Maybe you have different definitions of "hand optimising 
tablespaces"?

Morten - Are your systems similar enough to Johns to make a 
valid comparison about the merits of the shotgun approach vs 
"hand optimising tablespaces"?

For the benefit of the rest-of-us DBAs, i understand "hand 
optimising tablespaces" to mean ...
"choosing where a table (or a partition of a table) will 
physically live, and where its indexes will live so that 
roughly;
sum(reads/s + writes/s on spindle [1..n])^2 ) is as low as 
possible. (ie balanced across all spindles). I have never 
had a db span anything bigger than a redundant fibre channel 
array controller (yep - a bit outdated now), but if it did, 
then balance across n controllers as well.
I imagine you also decide if/which tables or indexes are 
pinned in memory?

Thanks to both of you for the info so far :)

Regards,

MrKiwi