10.2B08 (yes I know).
Just wondering what methods are out there for minimising down time on a binary D&L? We have a number of clients who are still running Type I storage on the basis they can't understand why so much downtime is required.
Any thoughts much appreciated.
Quite a bit of your options are going to depend on how your existing Type I areas are set up (tables/indexes per area) , your available hardware/OS and if you really need to do everything at once or can break into multiple maintenance windows (doing selected tables during each window).
The short version is multi threading the process as much as possible and avoiding any dead times. Like waiting for the dump to completely finish before starting the load.
In most cases I have had better luck with the following overall approach:
1) Running multiple proutil dump processes at the same (some with threads and some without) with the server running with a large -B. Large -B and server running can be skipped if you don't have a lot of mixed table areas.
2) Loading the data into the new database with all of new the extents allocated, -i and reasonable startup settings. Don't build indexes as you load.
3) Running a single multithreaded idxbuild per area once the load is completed for that area.
The big variations are going to be tables/indexes that are meaningful percentages of the database and if you are dumping and loading on the same hardware.
It isn't easy to come up with a generic solution that works for every database/server but these have served me pretty well over the years.
I believe Bravepoint has a product intended to migrate the bulk of the data in advance, limiting the amount that needs to be transferred during the down time. I also recall Tom Bascom having given a paper relating to this ... but I don't remember any details since it was not a paper I attended. Which said, a lot of what is possible might depend on the hardware resources, so you might say something about what those usually are. Obviously, there are a lot of possibilities moving to new hardware that don't exist staying on the same machine, especially if that machine is just barely enough to run the application.
Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice http://www.cintegrity.com
Addition to what Keith Sudbury has wrote: carefully choose an index for dump. Dump time of the same table may vary by the tens times depending from the chosen index.
But instead of option 1) I'd suggest to dump db in read-only mode. No resource locks (and no resource conflicts between sessions). And each session will use its own (small) buffer pool.
George is correct about the index you choose for the dump impacting the speed of the dump. Take care that it doesn't cause performance issues when the data is loaded though.
As for using -RO for dump.. I have found some cases where that did indeed work better than with the server running. It seems to be directly related to how heavily (or not) your tables are intermixed with each other (Type I). Both are valid approaches to try on your specific databases.
Bravepoint does have the PRO D&L product www.bravepoint.com/products-pro-dump-and-load.shtml
but there is obviously a cost involved (and most likely some replication triggers added like the PRO2 series). It may or may not make sense if you are just trying to convert a few customers from Type I to Type II.
George is right. However, there is more to the story.
If you choose a dump index with the goal of minimising dump time, this will also establish the order in which the records are loaded. this means that theordering imposed by the index you used for the dump will match the phyiscal storage order. That index will therfore have the highest cluster ratio and an index or table scan using that index will require fewer disk reads and (often more importantly) fewer disk seeks than using another index.
This may not be desireable if the application is doing a high percentage of its queries via another index - performance will be higher (possibly a /lot higher/ ) if you dump each table by an index preferred by the application.
Gus, how true is this in practice. I can see that, if one is doing a table scan regularly and there is a benefit in doing the table scan in the order of a particular index, that the performance would be better if the physical layout matched the logical one. But then, one rarely wants to be doing a table scan and, with a sufficiently modern version of Progress, using the table-scan option *instead* of any index is going to be preferred. Certainly, finding individual records the physical order should be irrelevant. I suppose I can see that, if one is finding a group of records and the bracketing is occurring on the same index which determined the physical ordering, that there might be a slight benefit, but how common or significant is that really. I thought the idea these days was that routine dump and load as a maintenance strategy was not warranted.
BTW, how we can estimate how many records in average are returned per query?
Too detailed information: -logentrytypes QryInfo
For remote clients we can use promon -> Activity: Servers -> Records sent vs. Queries received
I has checked promon data for one customer and the typical ratio is 2-3. For another customer who uses the same application the ratio is 10.
Are there any other methods to estimate the average length of the query result lists?
How much this matters varies greatly by application and table size. If you are lucky enough to be able to cache your entire working set in one of the buffer pools and you don't shut down the DB nightly... you aren't likely to notice the difference.
The benefits come from a potential reduction in the number of blocks needed to buffer the "active" records, how many buffer blocks must be read to satisfy a query and by maximizing the benefit of the read ahead logic on most SAN/Volume managers when the blocks must be read from disk.
Back in the 32 bit days this was much more of an issue for me personally with one specific app and a few specific tables. I could see a 40% improvement in report processing when the core table was loaded in the same order that the application used it. This was a 2TB database with several key tables that were in excess of 20GB in size that had close to a billion reads per day... certainly not your average OE install.
Type II areas and 64 bit buffer pools have certainly reduced the impact but I still see issues from time to time, only on very large and very active tables. I am not quite ready to declare D&Ls dead and buried, but they certainly aren't needed for most tables on a regular basis.
For terrabyte databases the main factor that affects the performance of the reads from disks is a seek time. Slowness is growing much faster than db size. Also the record fragmentation "hurts" performance much stonger in the large databases. In presentation "DBAnalys To The Rescue!" we had an example:
Table size: 25.5G
20% records are fragmented
33.6% blocks are on RM Chain
Reads of the whole table from disk was 35 times slower than when all records are not fragmented.
Fortunately the record fragmentation can be fixed online.
Seek time factor can be eliminated by SSD disks.
Anyone interested in fixing fragmentation online?
> On Mar 4, 2016, at 5:54 PM, Dmitri Levin wrote:
> Anyone interested in fixing fragmentation online?
if you come to the pug challenge at the end of june, you will find out how to do that and also a online table move that will not blow up the before-image log.
> On Mar 2, 2016, at 4:20 PM, Thomas Mercer-Hursh wrote:
> Gus, how true is this in practice. I can see that, if one is doing a table scan regularly and there is a benefit in doing the table scan in the order of a particular index, that the performance would be better if the physical layout matched the logical one.
in practice, here is a real example from a production system i did last Sunday:
two tables of almost the same size.
table 1 has 60,367,550 rows with 4.7 GB of data.
table 2 has 56,560,647 rows with 3.4 GB of data.
binary dump of table 1 takes 13 minutes
binary dump of table 2 takes 86 minutes
the worst case is /much, much, much/ worse than this. fortunately it is not common.
> if you come to the pug challenge at the end of june, you will find out how to do that and also a online table move that will not blow up the before-image log.
Is it something new that comes in future Progress versions?
I had planned to write the variations of AreaDefrag.p program. I'd be happy if I can cancel my plans. ;-)
You'll have to come to PUG Challenge and find out!
But I happen to know that you do not have to wait for a future version.