thank you very much for your tips. I'm sure they would reduce the time for our procedure a lot.
But there are a few other questions...
We've got appr. 20 tables which are collecting measurement data from tester maschines. If we are now running the update statistics process this makes an exclusive lock on the specified table there. This measurement result tables are very huge and the process takes a lot of time for each of them.
I don't really understand the reason for this exclusive lock.
Why is this exclusive lock needed, because the data and the indexes are not changed by only updating some system tables with information about the distribution and any physically address informations or whatever.
I don't think that the information for the optimizer has to be so accurate that the tables has to be locked everytime and the values are only a snapshot for the time the procedure started. A few moments later after the lock is gone the data has changed anyway a lot.
In fact these locks are the main problem we have with the whole procedure.
For the verify procedure on the other side I can understand those locks.
Thanks again for help
Georg
Strahl, Torsten wrote:
Hi Timo, there is no universal answer to your questions, but your "backup scenario" looks a little bit oversized. From my point of view you should do the following to reduce i/o:
- use "util_execute check data except index" instead of "check data" resp. "verify"
this will check all database files but no indexes! The most important thing is that your primary data is okay. You could check the entire database very x weeks.
- combine save data (full data backup) with save pages (incremental data backup)
Note that an incremental backup contains only the differences to the last full data backup.
- use "update statistic ... ESTIMATE SAMPLE 10 PERCENT"
As a rule estimation is as good as calculation of statistic values and it takes care to your i/o. First of all you should run update statistic estimate to the tables which are modified more common.
Regards, Torsten
SAP DB, SAP Labs Berlin
-----Original Message----- From: Timo Denis [mailto:[EMAIL PROTECTED] Sent: Montag, 8. M�rz 2004 11:44 To: SAPDB List Subject: backup routines
Hi,
we are running a 70GB database on SAPDB 7.4.03.31. Due to the fact that our hardware for this server is not very powerful, I have a few questions regarding backup and reorganisation.
Currently we are first running database verify, afterwards database backup and update statistics, which runs at least for 50 hours.
Is this order (1st verify, 2nd backup and 3d update statistics) right?
Howto stop the update statistics process correctly?
Verify needs 18 hours, backup needs 4 hours and the update statistics need 30 hours. The weekly data increment is between 0.8GB and 1.0GB. Do we need to run verify and update statistics every week?
Kind regards, Timo
___________________________________________________________ Timo Denis, IT department DBA / Developer / Administrator [LPIC1]
IEE S.A. International Electronics & Engineering Zone Industrielle, L-6468 Echternach, Luxembourg Phone +(352)72 89 89-6801, Fax +(352)72 89 89-6109
This e-mail may contain trade secrets or privileged, undisclosed or otherwise confidential information. If you are not the intended recipient and have received this e-mail in error, you are hereby notified that any review, copying or distribution of it is strictly prohibited. Please inform us immediately and destroy the original transmittal from your system. Thank you for your co-operation.
-- Georg Thom� IEE Luxembourg S.A. IT dept. / software development / DB-administation tel. 00352/7289896810 fax 00352/7289896109 www.iee.lu / [EMAIL PROTECTED]
This e-mail may contain trade secrets or privileged, undisclosed or otherwise confidential information. If you are not the intended recipient and have received this e-mail in error, you are hereby notified that any review, copying or distribution of it is strictly prohibited. Please inform us immediately and destroy the original transmittal from your system. Thank you for your co-operation.
-- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
