What block size should tables have?

[Cringer]

We use a piece of code called stcreate.p to work out which storage area to put tables into based on a calculation of the block size needed from dbanalys output. We're not sure we trust this code anymore as it seems to make assumptions about things, in particular smaller tables. In particular we have 2 tables that are almost carbon copies of each other, but one holds a lot of records the other doesn't and one is pushed into a size 32 blocked area, the other is put in a 64. This makes little sense. We have noticed some storage areas growing very quickly and suspect some tables are in the wrong block sized area. We are unsure of which though. Are there any other tools out there that we might use to analyse where to put things?

Or is there an easy way of manually deciding where to put things?

Thanks in advance.

 

[TomBascom]

I think you mean "rows per block" rather than "block size" -- any given database can only have a single block size.

Most automated programs that allocate these sorts of things do it based on the average row size as reported by dbanalys -- if the two tables have different average row sizes they would end up in different areas with different rows per block. This is quite possible even if the schemas are identical.

Automated programs from PSC that do this sort of thing tend to specify a rows per block which is far too small resulting in very "fluffy" databases that grow quickly and perform poorly. IMHO.

 

[Cringer]

Thanks Tom. Rows per block is probably what I mean.

So what's the best way of deciding how many rows per block a table needs? Is it a case of going through the dbanalys for each table and working it out manually? Or is there an easier way? We have over 500 tables so it's a bit of a mammoth task, hence the use of auto-generation. But as you say, it's resulted in a database that is very fluffy and is growing too quickly for my liking.

 

[TomBascom]

You *can* automate it. But it sounds like you have one of those tools that rounds down to the nearest power of 2 rather than rounding up to the next power of two.

I suggest: Storage Optimization Strategies

I know a consultant who would be happy to help you out with such things

 

[Cringer]

Thanks Tom. ALthough I'm disappointed. I told my colleague you'd make a comment about consultancy within the hour when I posted my last one and you were a minute late!!

I'll have a look at the presentation.

Ever fancied a trip to North West England?! Sadly I'm not in a position to authorise a trip

 

[TomBascom]

Too many balls in the air this morning...

Sure, NW England sounds lovely