Configitem Performance Issues

[pizzadood]

Hi, our OTRS system is heavily dependent on the information stored in the CMDB database and our current system has a total of almost 3 million records in the xml_storage table... I just want to ask if this is cause for concern when it comes to system performance since our system is a bit on the slow side? Can we remove records from the xml_storage table with "ITSM::ConfigItem::Archiv" as its xml_type? (I ran a count for this and it returned about 2,100,000 records) Will this affect anything aside from the searching in CMDB?

Also, is there proper documentation for ITSM config items? I have yet to see tutorial or description of how to manage the CMDB.

[pizzadood]

Just a bump. I just want to know if this will have a positive effect on our performance and if it will have repurcussions if we do delete the archived versions in the xml_storage table.

[CSL]

Are you experiencing performance issues just now?

If not, then I wouldn't worry about it. The table in question is only used when working with changes as you say, so it shouldn't affect the rest of the system. Three million rows should be no problem for a well configured MySQL instance running on capable hardware. That table has three BTREE indexes on it, so these will help keep searches fast as long as the system makes use of them.

If you are seeing performance issues, then I'd suggest taking an image of the DB, setting up a test system, and giving your suggested solution a go. This will be the best way to see what the consequences / benefits are.

[pizzadood]

Thank you for the response, CSL! I created a backup of the database just in case and deleted the "ITSM::ConfigItem::Archiv" rows. While the overall performance speed did go noticeably fast, I'm still unsure as to what the consequences in deleting those rows are. At any rate, since you mentioned that three million rows in the xml_storage should be no problem, is there any way we can optimize our OTRS instance at least on the database side? As far as configurations go, we already set the "TicketIndexModule" and "TicketSearchIndexModule" to StaticDB.

[CSL]

I'm not sure about deleting those rows from the xml_storage table; I don't use config management, so don't know what they are for. I'd think they were there for a reason though, and deleting them could cause something to break.

MySQL should be able to handle that number of rows as long as you have capable hardware, eg enough RAM to hold a reasonable chunk of the most frequently accessed sections of the DB in OS cache, enough to have appropriate caches configured for your MySQL instance, and a fast enough CPU.

If you're not sure how to optimize MySQL, you could run something like MySQL tune:
https://github.com/rackerhacker/MySQLTuner-perl

This will check your DB's variables and status, and will recommend config changes based on certain statistics (such as number of prunes of the query cache per day, etc).

There are lots of good articles on page 1 of google when you search for 'mysql optimize' or 'mysql tune' that are well worth exploring as well.