Converting from Type I to Type II

[KMoody]

Progress: 10.2b SP7
OS: SUSE Linux Enterprise Server 11

This is a followup a previous post of mine.

I'm converting our databases to Type II. Our old database preference files used a block size of 20000 and the following data structure:

db.st
d [DIR]/db.d1 f 500000
d [DIR]/db.d2 f 500000
d [DIR]/db.d3 f 500000
d [DIR]/db.d4 f 500000
d [DIR]/db.d5
b [DIR]/db.b1

I've changed the block size to 1000. Here's our new structure file:

db.st
#
b [DIR]/db.b1 #before-image
a [DIR]/db.a1 #after-image
a [DIR]/db.a2
a [DIR]/db.a3
#
d "Schema Area":6,64 [DIR]/db.d1
d "Data Area":7,64,8 [DIR]/db_7.d1
d "AuditData Area":8,64,8 [DIR]/db_8.d1
d "AuditIndex Area":9,64,8 [DIR]/db_9.d1

Before making these changes, I dumped the data. After making these changes, I loaded the data back in.

• How can I confirm that my database is Type II after loading?
• How do I ensure that the right information goes to the right .d file? I'm not sure where this should be done during dump and load. Do I need to place the Data Area in a separate directory and move to that directory when loading?
• Do you see any problems with this setup?

 

[TomBascom]

What block size are you referring to? Neither 20000 nor 1000 are valid Progress DB Block sizes. DB blocks come in sizes of 1k, 2k, 4k and 8k. You should only use 4k or 8k. The others are for archeological purposes only.

3 after image extents is too few. You should have at least 4, I prefer 8.

You have a "data area" but no corresponding "index area"? It is best practice to keep your data and indexes in discrete areas. You should also make sure that any LOBs (if you uuse them) are in a discrete area.

Confirm that it is type 2 by running prostrct list after creating the db. You will get a new structure file and if the ",8" portions are there the area is type 2.

Dumping data definitions will create a .df. Edit it to remove all AREA lines. (cat dbname.df | grep -v AREA > dbname.df2). Then create a script to do proutil -C tablemove & indexmove on the empty db (first build it with prostrct create, then load dbname.df2, then do the table & index moves, then load the data).

 

[Rob Fitzpatrick]

db.st
#
b [DIR]/db.b1 #before-image
a [DIR]/db.a1 #after-image
a [DIR]/db.a2
a [DIR]/db.a3
#
d "Schema Area":6,64 [DIR]/db.d1
d "Data Area":7,64,8 [DIR]/db_7.d1
d "AuditData Area":8,64,8 [DIR]/db_8.d1
d "AuditIndex Area":9,64,8 [DIR]/db_9.d1

I guess you typed this by hand. You need semi-colons between the RPB and cluster sizes, not commas.

When using OE Auditing it's good practice to also have an audit archive database from which you run audit queries and reports. Then on a regular basis, dump and purge the audit data from your business DB via proutil auditarchive and load it into the archive DB. Then you can deactivate the audit table indexes in your business DB, which minimizes the performance hit of the audit writes.

 

[KMoody]

What block size are you referring to? Neither 20000 nor 1000 are valid Progress DB Block sizes. DB blocks come in sizes of 1k, 2k, 4k and 8k. You should only use 4k or 8k. The others are for archeological purposes only.

Sorry, I meant the -B (buffer) parameter.

3 after image extents is too few. You should have at least 4, I prefer 8.

Okay, I added one more. Why should we have at least four?

Thank you both so much for your help!

 

[Rob Fitzpatrick]

Sorry, I meant the -B (buffer) parameter.

-B 10000 or 20000 is extremely small (80 MB or 160 MB). That will limit database caching and keep your ratio of logical to physical I/O low, which impacts application performance. What is the size of your database and how much RAM do you have available for shared memory? Also, what are your OS shared memory settings? (ipcs -l)

 

[TomBascom]

1 busy (active), 1 full (and presumably in the process of being archived), 1 empty and ready to be switched to and 1 empty for unexpected switches (like when probkup runs right after you switched into your empty extent). If you have OE Replication running you also have to account for the possibility that you may have "locked" extents.

If you want to get really crazy you could put odd and even numbered extents on different disks in order to optimize the IO pattern -- new notes go into the busy extent while the full extent (on another spindle) is being copied to your archive area (also on different disks -- ideally in a different time-zone) That only works with even numbers of extents.

I like 8 because it gives me more time to react if something goes wrong with my archiving process and I have a few more options for manually switching extents. 8 to 16 is generally enough for my taste.

 

[KMoody]

-B 10000 or 20000 is extremely small (80 MB or 160 MB). That will limit database caching and keep your ratio of logical to physical I/O low, which impacts application performance. What is the size of your database and how much RAM do you have available for shared memory? Also, what are your OS shared memory settings? (ipcs -l)

Sorry for the long delay.
Our largest database is 1.5 GiB.
We have 96168 kB of shared memory.

Our OS shared memory settings are below:

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 4194303
max total shared memory (kbytes) = 1073740800
min seg size (bytes) = 1

------ Semaphore Limits --------
max number of arrays = 1024
max semaphores per array = 250
max semaphores system wide = 256000
max ops per semop call = 32
semaphore max value = 32767

------ Messages Limits --------
max queues system wide = 360
max size of message (bytes) = 65536

 

[Rob Fitzpatrick]

Sorry for the long delay.
Our largest database is 1.5 GiB.
We have 96168 kB of shared memory.

Our OS shared memory settings are below:

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 4194303
max total shared memory (kbytes) = 1073740800
min seg size (bytes) = 1

------ Semaphore Limits --------
max number of arrays = 1024
max semaphores per array = 250
max semaphores system wide = 256000
max ops per semop call = 32
semaphore max value = 32767

------ Messages Limits --------
max queues system wide = 360
max size of message (bytes) = 65536

You didn't mention how much RAM you have available to use for the buffer pool. Currently with -B 20000 your buffer pool is about 10% of the size of the DB. If all of your buffers have been used, after some amount of application activity, then you could benefit from a larger value for -B.

 

[KMoody]

Sorry, Rob. I wasn't sure where to find this information on a Linux machine.

Using the "free -m" command, I got the following:

            total      used      free    shared    buffers    cached
Mem:          1887      1768        118          0        100      1480
-/+ buffers/cache:        187      1699
Swap:        2055        29      2025

This means I have 1.7 GB of RAM currently available and about 1.88 GB total RAM in the buffer pool, right?

 

[Rob Fitzpatrick]

So you have 2 GB of RAM in total. By "available" I meant memory that you would be comfortable in allocating to this DB given the other applications already running on the box, rather than a metric you would get from an OS command.

So although you are memory-constrained, you can probably do better in caching. The questions are: whether you need to, based on your application read activity and client application performance; and whether you can, based on demand for RAM by other applications.

From promon you can figure out the ratio of logical to physical reads, and also find the number of empty buffers in the buffer pool. They will provide some of that information. The rest is up to you to figure out. Note that unless you're using private buffers (-Bp) for your database backups, you probably have zero empty buffers. And if you're not, you should; add "-Bp 10" to your probkup command.

If you're feeling adventurous and know some ABL, you can also query the _TableStat and _IndexStat VSTs to get table and index CRUD data. This requires your -tablerangesize and -indexrangesize startup parameters to be set correctly. This data will tell you more about the "hot spots" in your database, which can help with decisions about caching. It can also highlight application design issues you may not have been aware of previously.