Blocks in database buffers values for medium to large databa

The first thing to do is to take the 10% figure ad forget you ever read it. It is misleading! :)

The next thing to do is to realise that for most settings on the DB you will need to benchmark, change and rebenchmark until you hit a sweetspot of performance vs memory.

Whilst you're looking at -B, you'll also be advised to look at -B2. This should be sized so it never has more data in it than it can hold (so it's never flushed) and should contain your schema area, and any small tables that change rarely but have very high reads.

Oh, and if you're sticking a table in -B2 make sure you also stick the table's indexes in there too.

You're using 64-bit OpenEdge on the back end; that's good.  You're on SP05 of 10.2B; is there a reason why you can't install SP08 in the short term?  If you can, I'd recommend that you do.  And make a plan for moving to a recent version of v11 sooner rather than later.

You're on Type I storage; that's bad.  And it's related to your question about caching.  In Type II storage, a storage object (table, index, or LOB column) comprises a chain of clusters of blocks, where a cluster size is 8, 64, or 512 blocks in size depending on the storage area configuration.  And each cluster contains data from only one object.  So each block in the customer table contains only customer records.

By contrast, in Type I storage there is no concept of clusters and there isn't as much physical segregation of objects on disk as in Type II.  In a multi-table storage area, records from different tables may be interleaved within a given record block.  So a block that contains five customer records may also contain three orders, ten order-lines, and twenty sales-reps.  Or fifty records from fifty different tables.  So in the theoretical worst case, your "for each customer: display customer" code may access as many database blocks as it reads records (I'm ignoring index I/O for the sake of simplicity).  

The upshot is such I/O on heavily scattered data will result in a lower ratio of logical to physical reads than if your data were in Type II areas.  In other words, a given amount of buffer pool will generally yield less caching efficiency with a Type I structure than with Type II.

So I strongly recommend that you (a) install 10.2BSP08, at a minimum, (b) read up on the index rebuild performance enhancements that were added in 10.2B between SP04 and SP07, and (c) dump and load into a Type II structure (binary dump/binary load/index rebuild).  You can increase -B in the meantime, but you really need a plan to get to Type II.  Throwing more RAM in the box won't change the fact that you aren't using your RAM as efficiently as you could.

Regarding how much -B *you* need, I can't answer that.  It really depends on your application's behaviour.  Setting aside 10% of your DB (15 GB) for buffer pool would be a waste if your application only ever referenced 2-3% of your data day to day.  And the converse could be true as well: you might benefit from much more than 15 GB of buffer pool.  

Not knowing your application or being able to see run-time stats from your DB I can only provide some very general guidance.  Don't set -B at 10% of your database size *as a rule*.  Maybe 10% is good for you, given your application.  But maybe a good number is 3% or 70%.  You need some empirical data to decide that, and it won't come from taking some percentage of your database size.

Don't increase -B so much that you cause excessive paging in the OS.  Don't make -B larger than your database.  If you set -B to some value x and run your application(s) for a while, running the code you typically run (daily, weekly, monthly workloads, whatever is typical for you) and promon R&D 1 7 shows that you still have empty buffers in the buffer pool, then increasing -B to a value greater than x won't help performance (assuming of course that your application workload remains the same, which it may not).

Start your database broker with -tablerangesize and -indexrangesize values large enough to collect CRUD stats for all objects, and query _TableStat and _IndexStat to see which are your most frequently-read objects and how much logical I/O you're doing versus physical.  Most applications are read-heavy; compare _tablestat-read (in units of records) to _tablestat-osread (in units of blocks).  See how these values change, if at all, as you change -B.  Similarly for _indexstat-read and _indexstat-osread.  This will assist you in planning your Type II structure.

As James indicated you can potentially gain performance benefits from assigning relatively small, very frequently-accessed objects to the Alternate Buffer Pool (sized with -B2) so they are effectively pegged in memory.  However there is a constraint to be aware of: in a Type II storage area you can assign individual objects to the ABP, but in Type I storage you can only assign an entire area.

Note also that changing your database block size to 8 KB necessitates re-evaluating your values for -B, -B2, and area records per block.

And you may find this helpful:

Storage Optimization Strategies, Tom Bascom, DBAppraise.com/White Star Software

video: http://download.psdn.com/media/exchange_online_2010/1004TomBascom.wmv

slides: http://dbappraise.com/ppt/sos.pptx

Hi,

an update on this (-B sizing) with some "weird" data:

We moved a 17 GB database (OE 10.2B 05 64 bit) to type II and redistributed the tables/indexes based on RPB values. The server, that is solely hosting the database, has 128GB of RAM, and is running Windows 2008 R2. The database is networked and is being started from OE Explorer.

We ran a simple procedure that loads (create/import) a ~50 MB table (around 375,000 records) into the database, and the server/database are being restarted prior to each test, anti-virus is off also.

The load took:

1- A little less than 5 minutes with -B set to 800,000 (4k blocksize database, so that's 3.2GB)

2- 13 minutes with -B set to 8,000,000 (8 Millions, so that's 32 GB, out of the 128GB available)

Note: The same result came in whether dbanalys was run prior to the tests or not.

So to simply translate this, without thinking, a high -B will have a significant bad impact on performance irrespective if the server has more than enough free RAM or not.

Unless we're missing something.

Regards,

Jimmer

you cannot draw such a conclusion from a single data point.

when you are loading data, each data block is accessed several times until it becomes full, after which it is (probably) not accessed again. index blocks will be accessed more times since the btree has to be in order of the index keys. still, the the number of blocks that need to be in memory at the same time will not be terribly large since you are loading a small data set.

furthermore, there are other configuration parameters that will affect loading time.

regardless of how much time the load takes, it is not a predictor of application performance.

-gus

> On Oct 16, 2015, at 4:45 AM, Jimmer wrote:

>

> Update from Progress Community [https://community.progress.com/]

>

> Jimmer [https://community.progress.com/members/jimmer]

>

> Hi,

>

> an update on this (-B sizing) with some "weird" data:

>

> We moved a 17 GB database (OE 10.2B 05 64 bit) to type II and redistributed the tables/indexes based on RPB values. The server, that is solely hosting the database, has 128GB of RAM, and is running Windows 2008 R2. The database is networked and is being started from OE Explorer.

>

> We ran a simple procedure that loads (create/import) a ~50 MB table (around 375,000 records) into the database, and the server/database are being restarted prior to each test, anti-virus is off also.

>

> The load took:

>

> 1- A little less than 5 minutes with -B set to 800,000 (4k blocksize database, so that's 3.2GB)

>

> 2- 13 minutes with -B set to 8,000,000 (8 Millions, so that's 32 GB, out of the 128GB available)

>

> Note: The same result came in whether dbanalys was run prior to the tests or not.

>

> So to simply translate this, without thinking, a high -B will have a significant bad impact on performance irrespective if the server has more than enough free RAM or not.

>

> Unless we're missing something.

>

> Regards,

>

> Jimmer

>

> View online [https://community.progress.com/community_groups/openedge_rdbms/f/18/p/17493/73407#73407]

>

> You received this notification because you subscribed to the forum. To unsubscribe from only this thread, go here [https://community.progress.com/community_groups/openedge_rdbms/f/18/t/17493/mute].

>

> Flag [https://community.progress.com/community_groups/openedge_rdbms/f/18/p/17493/73407?AbuseContentId=d6116dcd-cc2a-4ca1-847d-6d31e1f066e1&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true] this post as spam/abuse.

There was a strange issue with Windows and large amounts of shared memory.  I don't recall the details but I vaguely recall that it was addressed in the late 10.2B service packs.  So you might want to at least get to 10.2B08.

OTOH... if you are saying that you did "proutil dbname -C load" with those -B values then your results are expected and normal.  proutil load should be run with very small -B values.  Bigger is not better in that case.

We tried the 08 as suggested, small improvement (11+ min instead of 13) with 8 Million -B.

800,000 in -B is still around 5 min. We are loading programmatically (Create/import), not using proutil load.

The thing is that we had an issue with specific modules being slower than expected, so we shrunk down the code to something really simple (like loading a table) and started revisiting the parameters (the first that we modified being -B) so we noticed the 5 min vs 13 min difference.

I had opened a case about it and it seems the definitive culprit is Windows (the 64 bit version with its cache management), but I was wondering if anyone had encountered similar situation and if there were any hints on how to tackle/alleviate it.

thanks

JM

Before you spend too much time on -B... take a look at the overall disk performance and BI/AI related settings. Unless you are over comitting memory you will see little if any difference for loads once you get past a reasonable value.

You reallly need to make sure the BI cluster/block sizes are appropriate, as well as -spin , -bibufs and a host of other parameters. Find out where the bottlenecks are and tune those.

Is this load process a single-user session?  Or is there a server running with APW, BIW, AIW and so forth?

@Keith: Yes, I am aware that -B is not the sole parameter to consider in the journey to performance tuning, but I was under the impression that bigger is better (up to a certain point), specifically when moving to a 64 bit database. But the results, viewed in a simple manner,  are like bigger is bad :) that's why my "relation" with the -B is somewhat "shaken" :)

@Tom: The database is being started as multi-user, with BI and APW (we had tried with 3 and 9 APW, but got the same results). There is only one user though performing the tests.

Thanks

JM

But, -B is about optimum performance with a full load of users, not about optimum performance on a single threaded load session.    As Tom has said, for single threaded load, a small -B is appropriate.

FWIW loading data is not typically something that -B is going to help with.  In a load focused scenario there is much less  benefit to caching -- your "hit ratio" is quite low.  That doesn't directly explain why things get slower but I would not have expected much benefit in that scenario to start with.  I *suspect* that you are seeing behavior related to the allocation of memory for -B.  My guess is that there is some sort of lazy memory allocation going on -- the OS (or the VM -- is this a virtual environment?) isn't allocating all of the RAM when it is asked for but is instead trickling it out on demand and maybe having an increasingly difficult time arranging for those additions or rearranging things to fit within the address space or something like that.

The big benefit from "bigger is better" -B thinking is tied to *reads*.  Most applications are much, much heavier readers than they are writers.  And "locality of reference" is very strong in most application's read activity (the same stuff gets read over and over and over... again and again and again...)

I think you should dig into that windows problem that was referenced above.  Or, if this is a VM, look at the memory allocation settings.

It would be hard to say why it slows down without looking into the gory details of promon and perfmon. It could be one of the various memory issues with OE and Windows 2008.

Or you chould be setting other parameters that cause the issue.

This is not the test to run to find the issues though.

Yes, I got your points about the "good" use of -B, and in which conditions it is really beneficial.

Though, the way I see it (I may be wrong, but it is based on the fact that we repeated the exact steps while only removing/adding a "0" from the -B value) , there should have been a big sign somewhere that says something like:

"Watch out, if you are running Windows 64 bit (at least 2008 R2), and if you increase the -B to a certain point, it will cause a degradation of performance (not only it won't be beneficial), irrespective if you have enough free RAM on the server or not."

By the way, it is not a virtual environment.

Thanks for all the valuable replies and for your time.

Regards,

JM