Logging And Performance

[johnm]

As a refresher, a description of my problem is in the message body below.

I could not get answers to all of the questions that were asked below in a
previous thread, but here is what I found out:

1. Number of LOB's inserted per hour and average size
2. Number of LOB's updated per hour and average size
3. Number of LOB's deleted per hour and average size
4. Rough estimate of the amount of non-LOB data inserted, updated, and
deleted per hour

Answer: My DBA tells me that he can not get this information. He claims that
it can be gleaned from the CRM application with some code changes. I don't
know whether this is true or not. But, I don't have this information for
you. Is my DBA mistaken here? Can he get this information from the DB?
5. Type of disk subsystem (including controller) used for log files

Answer: Here is how the disks are set up: The OS is on a mirrored set.
Everything else, including the log files and the DB are on a SCSI External
Raid 5 array.
6. Whether the disk and controller used for logging is used for anything
else (DB2 or non-DB2)

Answer: Yes it is: the log files, the DB, the backup program and backup
files and a monitoring program and files.
7. Size of log buffer

Answer: 1024 pgs of 4k
8. Size and number of log files

Answer: 15000 pgs of 4k: 7 primary & 7 Secondary
9. Number of processors

Answer: 2 processors
I hope this supplies you with enough information to be of some help.

If it is possible to log the LOB data, I have a strong notion that quite a
few of my other issues would be resolved. I just would like to know if
logging the LOB data is going to over tax the system.

My best guess is that over the course of any work day, there will be LOBs
inserted with a mazimum size range of 1 GB, at a rate of about 20 times per
hour.

I am getting conflicting information from my DBA and would like to find out
the "real" story.

Any and all help and/or suggestions is greatly appreciated.

Thanks,

John

"Mark A" <no****@nowhere .com> wrote in message
news:PL******** ************@co mcast.com...

"johnm" <jo***@matrixsg .com> wrote in message
news:11******** *****@corp.supe rnews.com...

We have an application through which users store various types of files

and

attachments in a LOB in a DB2 7.2 database. Some of these files and
attachments can reach a size of 1 GB, although most are in the 10 MB

range.

Logging is not enabled for the LOB columns in the database tables. We do a full offline backup of the DB every night. So, it appears as though, if we suffer a system problem during the day sometime, we could loose up to 24
hours of the LOB data.

We would like to recreate the tables and columns holding the LOB data so
that logging is enabled. That way, should a system failure occur, we could recover all of the LOB data by restoring the back up and then applying the log files with a roll forward. This is what we would like to do if at all possible. We currently generate log files every 15 minutes, so by enabling logging, we would suffer a maximum LOB data loss of 15 minutes.

Our primary goal, which we must accomplish due to regulatory requirements, is to limit any data loss to one hour or less.

However, we have been told that enabling logging for the LOB tables and
columns will significantly impact the performance of the system in a very negative way.
Is this correct?

Any other ideas about how we many obtain a max LOB data loss of 1 hour or less?

Thanks for any and all help.

I can assure you that it is greatly appreciated!

John

Can you provide the following information?:

1. Number of LOB's inserted per hour and average size
2. Number of LOB's updated per hour and average size
3. Number of LOB's deleted per hour and average size
4. Rough estimate of the amount of non-LOB data inserted, updated, and
deleted per hour
5. Type of disk subsystem (including controller) used for log files
6. Whether the disk and controller used for logging is used for anything
else (DB2 or non-DB2)
7. Size of log buffer
8. Size and number of log files
9. Number of processors

[Mark A]

"johnm" <jo***@matrixsg .com> wrote in message
news:11******** *****@corp.supe rnews.com...

5. Type of disk subsystem (including controller) used for log files

Answer: Here is how the disks are set up: The OS is on a mirrored set.
Everything else, including the log files and the DB are on a SCSI External
Raid 5 array.
Putting the logs on RAID-5 arrays, and on the same disks as the data is not
the best for optimum performance, but whether the difference is noticeable
would depend on your application activity.

Also, in the event that you lost the entire RAID-5 array (maybe lost 2 disks
in the array), then you would not be able to recover. The logs and the data
should be on separate devices.

So the best strategy for logs is either dual logging to 2 single disks on
their own controller, or single logging to the mirrored OS disks.
I hope this supplies you with enough information to be of some help.

If it is possible to log the LOB data, I have a strong notion that quite a
few of my other issues would be resolved. I just would like to know if
logging the LOB data is going to over tax the system.

My best guess is that over the course of any work day, there will be LOBs
inserted with a mazimum size range of 1 GB, at a rate of about 20 times
per
hour.

I am getting conflicting information from my DBA and would like to find
out
the "real" story.

Any and all help and/or suggestions is greatly appreciated.

Thanks,

John

With an average insert rate of one LOB every 3 minutes, I don't see any
reason why you cannot log the LOBs. If the performance suffers too much,
then you can always stop the logging. Some additional fine tuning of the
logs may be in order to accommodate the size and to increase
performance/reliability.

[johnm]

Mark,

Thanks so much for the information. It is exactly what I was looking for.

I'll let you know how all of this turns out.

Thanks again,

John
"Mark A" <no****@nowhere .com> wrote in message
news:xv******** ************@co mcast.com...

"johnm" <jo***@matrixsg .com> wrote in message
news:11******** *****@corp.supe rnews.com...

5. Type of disk subsystem (including controller) used for log files

Answer: Here is how the disks are set up: The OS is on a mirrored set.
Everything else, including the log files and the DB are on a SCSI External Raid 5 array.
Putting the logs on RAID-5 arrays, and on the same disks as the data is

not the best for optimum performance, but whether the difference is noticeable
would depend on your application activity.

Also, in the event that you lost the entire RAID-5 array (maybe lost 2 disks in the array), then you would not be able to recover. The logs and the data should be on separate devices.

So the best strategy for logs is either dual logging to 2 single disks on
their own controller, or single logging to the mirrored OS disks.

I hope this supplies you with enough information to be of some help.

If it is possible to log the LOB data, I have a strong notion that quite a few of my other issues would be resolved. I just would like to know if
logging the LOB data is going to over tax the system.

My best guess is that over the course of any work day, there will be LOBs inserted with a mazimum size range of 1 GB, at a rate of about 20 times
per
hour.

I am getting conflicting information from my DBA and would like to find
out
the "real" story.

Any and all help and/or suggestions is greatly appreciated.

Thanks,

John

With an average insert rate of one LOB every 3 minutes, I don't see any
reason why you cannot log the LOBs. If the performance suffers too much,
then you can always stop the logging. Some additional fine tuning of the
logs may be in order to accommodate the size and to increase
performance/reliability.