473,320 Members | 1,810 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Logging And Performance


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********************@comcast.com...
"johnm" <jo***@matrixsg.com> wrote in message
news:11*************@corp.supernews.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


Nov 12 '05 #1
2 1699
"johnm" <jo***@matrixsg.com> wrote in message
news:11*************@corp.supernews.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.
Nov 12 '05 #2
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********************@comcast.com...
"johnm" <jo***@matrixsg.com> wrote in message
news:11*************@corp.supernews.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.

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Luther Baker | last post by:
I'd like to include some very simple logging in my app - removable at compile time. Avoiding macros .. I want to make sure this nonsense would be optimized away by the compiler if I don't need...
6
by: pmatos | last post by:
Hi all, I am trying to create a simple but efficient C++ logging class. I know there are lots of them out there but I want something simple and efficient. The number one requirement is the...
3
by: johnm | last post by:
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...
3
by: Oscar Thornell | last post by:
Hi, I am thinking about doing all my logging asynchronously using a delegate. The main resaon for this would be performance and responsiveness of the application (an ASP.NET app). //Exampel...
6
by: Matt | last post by:
I have a production database that I need to change the logging type from circular to archived so that I can do online backups. The database is not particularly large, but is used by many people. ...
3
by: Udi | last post by:
Hi All, I'm developping an application that has a very strong logging requirement - we should be able to log up to 5000 messages per second, most of them are reletively small -(16 - 30 Words)...
7
by: flupke | last post by:
Hi, i'm getting errors with the log module concerning RotatingFileHandler. I'm using Python 2.4.3 on Windows XP SP2. This used to work in previous python versions but since i upgraded to 2.4.3...
9
by: Rasika WIJAYARATNE | last post by:
Hi guys, Please check this out: http://rkwcoding.blogspot.com/2007/07/error-logging.html
6
by: Vyacheslav Maslov | last post by:
Hi all! I have many many many python unit test, which are used for testing some remote web service. The most important issue here is logging of test execution process and result. I strongly...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.