Hi Mickey, MySQL needs to be running to optimize any mysql database.
We're running 9.1.3 H14. There are no db_maintenance.pl or db_optimize.pl scripts for SRM now. In 9.1.3 H14 they only exist for the DDMdb. The scripts require that mysql is running. I haven't found any issue when running the db_optimize.pl and db_maintenance.pl scripts on the DDMdb. When the scripts are running on the DDMdb I haven't seen any performance issues on the SpectroServer. Optimizing the SRMdb can take some time if it's a large DB and a lot has been purged. Now that I optimize the DB regularly, it doesn't take long. The first time I did it, the optimization ran nearly 20 hours. During that time our Event table went from more then 40 GB to 22GB. It was worth the wait! Hope that helps! Karen Kedda Enterprise Monitoring Solutions Berkley Technology Services LLC 405 Silverside Rd., Suite 205 Wilmington, DE 19809 302.746.2113 [email protected] From: Mickey Alderson <[email protected]> To: Karen Kedda <[email protected]> Date: 07/08/2011 03:39 PM Subject: Re: [spectrum] report manager - mysql Thanks for the great info - it should definitely help me out. One other question about the mysql optimization commands (and forgive me, not an expert in mysql by any means) - will I need to stop mysql from running when executing those scripts, or can it be running during? And is there any adverse effect on Spectrum while running those scripts? Thank you so much! -Mickey Alderson On Thu, Jul 7, 2011 at 10:06 PM, Karen Kedda <[email protected]> wrote: We're running 9.1.3 H14 on Windows 2k3. After upgrading to 9.1.3 H14, we had the Data Retention Periodset to 185 days and Data Retention Policy set to archive. Our database continued to grow and the archive directory grew. After never seeing the size of our database decrease, CA Support recommended that we turn off archiving and set the Data Retention Policyto purge. They also mentioned that the automatic optimize was removed because it was causing issues (I don't know when automatic optimization was removed or what other versions are affected). The CA SRM expert reminded me that archived data was essentially unusable, so why keep it. Have you and your customer determined how far back in time they would want to go to run a report. Six months? Three months? Nine months? Choose carefully! Also be sure to check the Archive Expert in SRM Admin Tools. It's a good place to watch the daily growth of each table. If there is huge growth in the Event table, it would be advisable to look at some Top-N Event and Top-N Alarm reports! We changed our Data Retention Policy to purge and after a few days it finished purging. The size of the database tables didn't decrease, but the number of days retained in our Event table shrunk to the number of days in our Data Retention Period (185 days). The final step was to manually optimize the tables of the database. The largest tables were the Event, AlarmActivity, and AlarmInfo. >From a bash shell: $ cd $SPECROOT/mysql/bin $ ./mysql -uroot -proot reporting mysql> optimize table alarminfo; mysql> optimize table alarmactivity; mysql> optimize table event; You might want to optimize the third largest table before tackling the two largest tables. It will take some time for optimize to run, but you should see the size of the table files decrease ($SPECROOT/mysql/data/reporting) once the optimization is done. You will need free disk space of about 2 times the size of the table being optimized. The Event table in our database shrunk about 40% due to the optimization. Our largest tables are now manually optimized monthly. Running the optimize regularly means that it doesn't take as long. Hope that helps. Karen Kedda Enterprise Monitoring Solutions Berkley Technology Services LLC 405 Silverside Rd., Suite 205 Wilmington, DE 19809 302.746.2113 [email protected] From: Mickey Alderson <[email protected]> To: "spectrum" <[email protected]> Date: 07/07/2011 10:20 AM Subject: [spectrum] report manager - mysql Disclaimer: My apologies in advance if this is an easy issue that I should know... Disclaimer 2: We are currently working without a maintenance agreement due to some financial difficulties that our customer is having, although we hope that will change fairly soon... Spectrum environment: Win2k3, version 9.1.1.7.13 I'm running into an issue where I have over 100Gb of data in the SPECROOT\mysql folder, 64.4Gb of which is in the SPECROOT\mysql\data\archive folder. I ran the db_optimize and db_maintenance scripts last night, which got 5Gb of HD space back, but I need to try to do something with the archive folder, and possibly the file sizes of some of the files in the SPECROOT\mysql\data\reporting folder (for example, the event.MYD file is currently just under 25Gb by itself.) Reading the Report Manager manual didn't exactly help point me in the right direction as to what I need to do, so I'm appealing to the list here for assistance. -- Mickey Alderson Sr. Network Engineer ACS, A Xerox Company mobile. 901.833.4908 email. [email protected] --To unsubscribe from spectrum, send email to [email protected] the body: unsubscribe spectrum [email protected] ************* CONFIDENTIALITY NOTICE: This e-mail and the transmitted documents contain private, privileged and confidential information belonging to the sender. The information therein is solely for the use of the addressee. If your receipt of this transmission has occurred as the result of an error, please immediately notify us so we can arrange for the return of the documents. In such circumstances, you are advised that you may not disclose, copy, distribute or take any other action in reliance on the information transmitted. --To unsubscribe from spectrum, send email to [email protected] with the body: unsubscribe spectrum [email protected] ************* CONFIDENTIALITY NOTICE: This e-mail and the transmitted documents contain private, privileged and confidential information belonging to the sender. The information therein is solely for the use of the addressee. If your receipt of this transmission has occurred as the result of an error, please immediately notify us so we can arrange for the return of the documents. In such circumstances, you are advised that you may not disclose, copy, distribute or take any other action in reliance on the information transmitted. --- To unsubscribe from spectrum, send email to [email protected] with the body: unsubscribe spectrum [email protected]
