This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Feb 20, 2008 8:12 AM by Flake Go to original post RSS
  • 15. Re: Multiple block size advantages.
    51034 Newbie
    Currently Being Moderated
    The TPC is designed to produce a one-off, bragging rights result. It's a stress test, no doubt. But once the figures have been collected, that's it: the thing is packed away and never returned to again. It is not designed to produce a stable platform for the long term. No TPC benchmarker is going to be sat there in six months bemoaning the fact that he has to constantly tune his db_32K_cache_size himself because the automatic tuning mechanism doesn't do it for him, for example. That sort of long-term care issue doesn't arise in TPC benchmarks... and long-term code stability isn't something they have to worry about, either. I think that has a relevance to the issue of running a production database on the basis of what was done to achieve a great TPC benchmark.

    And never mind that the hardware required to achieve those benchmarks bears only a tenuous relationship to the sort of hardware that 95% of Oracle databases are going to be running on.

    According to Kevin Closson (here: http://tinyurl.com/2nq9qr), no audited Oracle TPC benchmark has ever been published that has the database in archivelog mode. Are you recommending people run their production databases without archives? Of course not. But nevertheless, we see a TPC benchmark doing things that you would never do in a production setting because it achieves a great result for the purposes of one-off bragging rights.

    I seem to recall (but I am hazy on the details) that some TPC benchmarks have been obtained with disablelogging set to true... yet I presume you're not recommending that in a production environment, either!

    Just because a TPC benchmark does X, Y or Z, therefore, and produces an excellent result in the process does not mean it's a good idea or something to be recommended for long-term production-quality use.

    NASA sends people into space using a liquid hydrogen/liquid oxygen mix as the main fuel tank propellant. I tend to fill up my car with plain ol' gasoline, thanks all the same.

    And that's all there is to say on the subject of TPC benchmarks using multiple block sizes as far as I am concerned.

    Your experience on mainframes in the 1980s is of no relevance to the discussion about whether multiple block sizes in Oracle 9i and 10g in the 2000s is a sensible thing to do. It would be as foolish to claim it was as it would be to claim that you were using email in 1973 and that that "fact" somehow qualifies you to make pronouncements on the way the world wide web works today. Different technology, implemented differently, in different products at widely diverse times: relevance zero.

    Meanwhile, today, the official Oracle documentation says multiple block sizes are 'of particular use when transporting tablespaces'. Period. Tom Kyte says likewise. So do I. And Richard Foote has done quite a nice demolition job on the claim that all indexes -which, as you point out, would be frequently range or fast full scanned- should be stored in 32K blocks (which you can't get in Linux and Windows anyway).
  • 16. Re: Multiple block size advantages.
    108476 Journeyer
    Currently Being Moderated
    Hi Santosh,
    Does it really happens?
    I couldn't have said it better myself. That's a GREAT question.

    Can you please paste-in the whole script that you used, please?

    It would be interesting to explore why you noted this huge improvement in response time with larger blocksizes . . . .

    TIA!

    Don Burleson
    Oracle Press author
  • 17. Re: Multiple block size advantages.
    311441 Employee ACE
    Currently Being Moderated
    Hi Don

    As I mentioned in the other thread, Santosh didn't note the huge improvement, he simply copied the example from the Ask Tom site.

    Santosh simply asked the question, is the "Reader From Russia" legit or did he have one too many vodkas.

    Answer is we simply don't know, there's insufficient information yet interestingly, our Russian comrade hasn't been able to supply the additional info required so I'm afraid the whole example is a bit suspect.

    Never mind, you now have the demo to prove it one way or the other yourself.

    Cheers

    Richard
  • 18. Re: Multiple block size advantages.
    108476 Journeyer
    Currently Being Moderated
    so I'm afraid the whole example is a bit suspect.
    Yeah, I redacted that one. Do you find the other evidence credible?

    http://www.dba-oracle.com/t_multiple_blocksizes_summary.htm
    demo to prove it one way or the other yourself.
    Sorry, Richard, I'm not biting. At best, your demo is a rule-of-thumb, but not a proof, by any definition.

    Your test case shows only one of zillions of possible conditions.

    Don't you agree that it's reckless to generalize from a single test case, especially when exceptions are known to exist?

    Message was edited by:
    burleson
  • 19. Re: Multiple block size advantages.
    153119 Pro
    Currently Being Moderated
    Don't you agree that it's reckless to generalize from a single test case, especially when exceptions are known to exist?


    If you do agree with that, this basically means all of your advice is reckless.
    You always have been generalizing from a single test case.

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 20. Re: Multiple block size advantages.
    chris_c Journeyer
    Currently Being Moderated
    I habve found one real world application of TPC-C benchmarks, It is a usefull tool when evaluating hardware when you do not know exactly what your workload is going to be so cannot produce more realistic benchmarks.

    Las time I did this it was to compare the performance of a number of iSCSI SAN solutions, using an open source tool to build the TPC-C database and run a load test. Along with some bulk load tests and bulk deletes it gave us the ability to say SAN 1 is faster/slower than SAN 2 but nothing else.

    As for using multiple block sizes in a single database as a performance tool, I suspect that in the mundane world of the databases I deal with that range from 50GB to a couple of terabytes where I don't have the time or equipment to run complete tests or for the kind of micromangment required to keep on top of this.

    I think if there is any performance advantage to this it will be similar to the advantages of moving tables and indexes onto different disks ( i.e. tables seperated from tables, indexes from indexes and tables from indexes) back before we all used massivly stripped disk arrays reduce IO contention, the actual analysis required is so high we end up with a generic rule of 'stick all indexes into a large blocksize' rather than a rule of 'possibly, maybe, in some cases when you have performed a couple of months of analysis you may get a performance boost from using non default blocksizes for some objects/segments but are going to increase the amount of database managment overhead and may have got a better result by analysing the application code instead '
  • 21. Re: Multiple block size advantages.
    588568 Newbie
    Currently Being Moderated
    Howard: If I am understanding you correctly, then these mega tests are a bit useless! A bit like building a road that a souped-up, billion-dollar, supercar managed to reach near lightspeed on, only to find that for the "normal" motorist, the road is is too narrow to pass other roadusers, has no lighting, doesn't go anywhere you'd ever want to go, and wears away after the first 100 cars have driven over it, etc. So, what DO these tests prove that's of any meaningful use?
  • 22. Re: Multiple block size advantages.
    311441 Employee ACE
    Currently Being Moderated
    Sorry, Richard, I'm not biting. At best, your demo
    is a rule-of-thumb, but not a proof, by any
    definition.

    Your test case shows only one of zillions of possible
    conditions.

    Don't you agree that it's reckless to generalize from
    a single test case, especially when exceptions are
    known to exist?
    Hi Don

    You totally and fundamentally miss the point (again).

    It's not a single test case or demo, it's an approach that can generate as many test cases as you like, in whatever environments or like, be it "real world" or otherwise (whatever that means), that can be modified in any manner you like.

    You have proven to be "reckless" in relying on Robin's simplistic and erroneous test case for years to justify multiblock databases for indexes. Seriously, how many times has this subject come up and how many times have you used Robin's "test" case as some kinda proof, when really it tells you nothing, absolutely nothing except the obvious, the consistent gets get halved.

    Here's a method or approach to prove (or otherwise) a piece of Oracle behavior and yet you simply refuse to even try.

    Sorry Don, I'm afraid you're the one who relies on single, simplistic test cases, without even understanding what these simple results really mean ..

    This is why we fundamentally differ you and I.

    You can only lead a horse to water ...

    Cheers

    Richard
  • 23. Re: Multiple block size advantages.
    51034 Newbie
    Currently Being Moderated
    The direct answer to your question is: nothing.

    Formula 1 motor cars don't tell me anything 'of meaningful use' about how to drive or look after my 4-door sedan.

    They perhaps tell me my car is made by a company with great engineering excellence in depth, but that's about it. Maybe they tell me my car company has better engineering excellence in depth than some other car company, too.

    Same with TPC: HP (or whoever) can make their hardware run faster than Company X. HP must be good, next time I'm buying a $10,000 server, I'll keep them in mind. Oracle is run faster than (say) SQL Server: next time I'm thinking of deploying an RDBMS, I'll keep them in mind: you can obviously push the one further than the other. But as a set of practical, meaningful guidelines on how to configure a database... nope.

    There would be a handful -really, just a handful- of businesses on the planet that genuinely (a) have that much money to spend and (b) need performance above all else, and for them, maybe the TPC represents something a little more tangible. But I've never worked for such a company and I doubt 99%+ of DBAs ever will either.
  • 24. Re: Multiple block size advantages.
    108476 Journeyer
    Currently Being Moderated
    Hi Richard,
    It's not a single test case or demo, it's an approach that can generate as many test cases as you like
    OK, I'll buy that, in theory, but what about the TCP standard? The TPC benchmarks are 100% reproduceable, and you are free to re-run their TCP-C and TPC-H benchmarks and prove them wrong.
    Here's a method or approach to prove (or otherwise) a piece of Oracle behavior
    No, it cannot "prove" anything, IMHO. There are too many interviening factors (init.ora parms, I/O sub-system, etc). In my world, we conduct a full test using a real-life workload, the only truly representative measure, IMHO.
    You have proven to be "reckless" in relying on Robin's simplistic and erroneous test case for years.
    No, I respectfully disagree. Do you really think that shops just rock-and-roll in production without testing? Now, that would be reckless! I ALWAYS insist on complete load testing whenever a client considers implementing multiple blocksizes.

    After all, WHY GUESS?
    I'm afraid you're the one who relies on single, simplistic test cases
    That's a bit presumptious. How would you know what I do at a client site?

    I spoke with a client just today who use using multiple blocksizes ( a multiu-terabyte OLTP), and they reminded me of another reason that some shops use multiple blocksizes to save RAM resources. They use a separate 2k data buffer for tablespaces that randomly fetch small rows, thereby maximizing RAM by not reading-in more data than required.

    Why fetch 16k to get 80 bytes?

    Oh, and Chris Foot (Oracle ACE and senior database architect) notes that employing multiple block sizes will help maximize I/O performance:

    "Multiple blocksize specifications allow administrators to tailor physical storage specifications to a data object’s size and usage to maximize I/O performance."

    http://www.dbazine.com/oracle/or-articles/foot5

    ***********************************************

    Also, see this, the official word on Metalink:

    Metalink Note:46757.1 titled "Notes on Choosing an Optimal DB BLOCK SIZE":

    - Using bigger blocks means more data transfer per I/O call; this is an advantage since the cost of I/O setup dominates the cost of an I/O. . .

    - Using bigger blocks means more space for key storage in the branch nodes of B*-tree indexes, which reduces index height, which improves the performance of indexed queries.

    - Using a block size that is k times bigger than your current one will save you (k-1)f/(kb-f) bytes of space for large segments, where f is the size of a block's fixed block header (61 bytes for tables, 57+4n for n-table clusters, 113 for indexes). For example, you will conserve about 4% of data storage (4GB on every 100GB) for every large index in your database by moving from a 2KB database block size to an 8KB database block size.

    - When using large block there are less probability of chained and migrated rows, which in turn reduced the number of reads required to get the information.
    Metalink goes on to say that multiple blocksizes may benefit shops that have "mixed" block size requirements:

    What can you do if you have mixed requirements of the above block sizes? Oracle9i "Multiple Block Sizes" new feature comes into the rescue here, it allows the same database to have multiple block sizes at the same time . . .
    You can specify up to additional four block sizes , you can configure subcaches within the buffer cache for each of these block sizes in init.ora file or dynamically . You can create tablespaces having any of these block sizes.
    This is why we fundamentally differ you and I.
    And that's OK! We come from very different worlds, and we each have a fresh (and largely valid) perspective on this issue . . . .
  • 25. Re: Multiple block size advantages.
    108476 Journeyer
    Currently Being Moderated
    There would be a handful -really, just a handful- of businesses on the planet that genuinely (a) have that much money to spend
    Paying a million for a server is not that rare. Most large shops spend millions on their hardware, it's not that unusual, especially for shops that support thousands of concurrent users.

    I'll see if I can find a survey on hardware expenses for Oracle shops. This survey from the Gartner group says the average IT budget is $71 million per year in 2006:

    http://www.gartner.com/press_releases/asset_143678_11.html

    "CIOs surveyed head centralised IT organisations, employing an average of 300 IT professionals with an average IT budget of US $71 million."
    But I've never worked for such a company and I doubt 99%+ of DBAs ever will either.
    If you say so . . . .

    Have fun in SF! BTW, if you like aChinsese food, try "House of Nanking", at Columbus and Kearney. They don't take reservations, and have been rated #1 in SF for many years running. The lines form about 5:00 PM, so go early, if this is of interest!
  • 26. Re: Multiple block size advantages.
    311441 Employee ACE
    Currently Being Moderated
    Hi Don

    I guess that's a valid point. Sometimes one simply needs to agree to disagree.

    Cheers

    Richard
  • 27. Re: Multiple block size advantages.
    51034 Newbie
    Currently Being Moderated
    Paying a million for a server is not that rare.

    I didn't say it was. I spent 0.25 million (admittedly only of AU dollars... though there's less and less difference these days!) on some hard disks back in January, after all. But some of these TPC systems are a lot more expensive than $1m

    Besides, you cut out the bit where I made point (b): these companies with spare millions also need to not care about anything other than the fastest possible performance. It's the combination of the two that is extremely rare.

    This survey from the Gartner group says the average IT budget is $71 million per year

    Gartner survey the big guys. It's no wonder that you end up with big averages when you do so!

    Have fun in SF

    Strange. Whatever gave you the idea that I'd be in San Francisco?
  • 28. Re: Multiple block size advantages.
    51034 Newbie
    Currently Being Moderated
    Why fetch 16k to get 80 bytes?

    Because the other 15.9K will be useful for other people. That is the whole point of a cache, after all!

    Oh, and Chris Foot (Oracle ACE and senior database architect) notes that employing multiple block sizes will help maximize I/O performance:

    Well, "notes" is the right word there! He mentions it in passing and as a mere assertion, without a word of explanation of what he means or why he means it. And without one piece of evidence to back the claim up!

    Interesting that he swiftly gets off that and reminds us what multiple block sizes are really for:

    Multiple block sizes - Multiple blocksize specifications allow administrators to tailor physical storage specifications to a data object's size and usage to maximize I/O performance. In addition, it also allows administrators to easily use the transportable tablespace feature to transfer tablespaces between databases having different default blocksizes (i.e. moving data from an OLTP application to a data warehouse).

    As for your "official word from Metalink": it consists of four bullet points making the completely valid point that you need to pick the DB_BLOCK_SIZE carefully, so that it's optimum for a particular workload. It then has ONE sentence which happens to mention in passing that it is POSSIBLE to have multiple block sizes, without once recommending it or demonstrating its efficacy:

    What can you do if you have mixed requirements of the above block sizes? Oracle9i "Multiple Block Sizes" new feature comes into the rescue here, it allows the same database to have multiple block sizes at the same time. It is also very useful when transporting a tablespace from an OLTP database to an enterprise data warehouse.

    Oh, and look: one short sentence later, and they're on to the real reason for using multiple block sizes!

    In all the material you've cited, therefore, you merely have unsupported-assertion-in-passing. Not exactly a ringing endorsement of the technique, I think.
  • 29. Re: Multiple block size advantages.
    51034 Newbie
    Currently Being Moderated
    OK, I'll buy that, in theory, but what about the TCP standard? The TPC benchmarks are 100% reproduceable, and you are free to re-run their TCP-C and TPC-H benchmarks and prove them wrong.

    No-one, I think, is claiming that the TPC benchmarks are "wrong". The question is whether they are relevant. They are a highly specific database and hardware configuration that is aiming to get the maximum performance possible, even if (when) in so doing, they compromise database recoverability or supportability. (Not multiplexing your redo logs makes things go fast, but is risky. Using half a dozen hidden parameters makes things go fast, but is likely to mean no support from Oracle.)

    The TPC benchmarks are therefore very right... but completely irrelevant for anyone not running a Formula 1 database for kicks.

    On the other hand, you have been invited to adopt a testing approach. An approach is generic. It's a testing methodology that can be employed for databases configured in high availability, high recoverability, high performance or indeed any mode at all. You can even adopt it on a database configured to run TPC benchmarks. The approach can be made highly relevant, therefore, by adopting it on databases configured as you and your love of real-world, non-simplistic cases would want them configured.

    The two issues are thus quite different. Why Richard has said you have a point, therefore, I can't imagine. Maybe he's recognised the sounds of head meeting brick wall...