CMDB performance issues

[Wolfgangf]

Hi!

I filled my CMDB with approx 3.500 objects (yes - thats correct, rather large network) and I'm facing HUGE performance issues with opening, browsing searching, ...

MySQL slow query log is full of

Code: Select all

SELECT DISTINCT(xml_key) FROM xml_storage WHERE xml_type = 'ITSM::ConfigItem::35' AND (  (xml_content_key LIKE '[1]{\'Version\'}[1]{\'MAC\'}[1]{\'Content\'}'  AND xml_content_value LIKE '00041335b11c' ) );
SELECT DISTINCT(xml_key) FROM xml_storage WHERE xml_type = 'ITSM::ConfigItem::32';
SELECT DISTINCT(xml_key) FROM xml_storage WHERE xml_type = 'ITSM::ConfigItem::32';
SELECT DISTINCT(xml_key) FROM xml_storage WHERE xml_type = 'ITSM::ConfigItem::33';
SELECT DISTINCT(xml_key) FROM xml_storage WHERE xml_type = 'ITSM::ConfigItem::32';
SELECT DISTINCT(xml_key) FROM xml_storage WHERE xml_type = 'ITSM::ConfigItem::33';
SELECT DISTINCT(xml_key) FROM xml_storage WHERE xml_type = 'ITSM::ConfigItem::33';
SELECT DISTINCT(xml_key) FROM xml_storage WHERE xml_type = 'ITSM::ConfigItem::32';
SELECT DISTINCT(xml_key) FROM xml_storage WHERE xml_type = 'ITSM::ConfigItem::32';
SELECT DISTINCT(xml_key) FROM xml_storage WHERE xml_type = 'ITSM::ConfigItem::33';

Having a look at the database, this could be an issue of indizes; second index (with field xml_type) is limited to 10 chars while the string used in the query is more then double; in my understanding this would lead to full table scan which would take a long time then (44699514 records in this table)

Code: Select all

+-------------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name                    | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| xml_storage |          1 | xml_storage_key_type        |            1 | xml_key         | A         |      798204 |       10 | NULL   |      | BTREE      |         |               |
| xml_storage |          1 | xml_storage_key_type        |            2 | xml_type        | A         |      798204 |       10 | NULL   |      | BTREE      |         |               |
| xml_storage |          1 | xml_storage_xml_content_key |            1 | xml_content_key | A         |         687 |      100 | NULL   |      | BTREE      |         |               |
| xml_storage |          1 | xml_storage_type            |            1 | xml_type        | A         |          14 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

any suggestions, tips, condulations ?
Thanks in advance
Wolfgang

[vilas66]

Me too.
Got the same problem.; It seems thet this issue has arised AFTER a migration to 3.3.x.....

[renezander]

Hello guys.

I have around 2500 CIs in my OTRS CMDB, which runs at version 3.3.5 and I can confirm that there was a heavy performance problem with almost all cmdb classes, similar to what you mentioned right after the migration from version 3.2.2 to 3.3.5.

I fixed that by:
- removing all unnecessary fields from the config item defintions (admin -> config items) - got down from ~20 to 10
- deleting the corresponding classes for the changes to take effect with the onboard perl script otrs.ITSMConfigItemDelete --class ..
- fixing the import templates at admin -> import/export according to the changes see above
- reimporting all CIs

The performance now is even better than is was on version 3.2.2. My server got 2 GB memory, running on centos 6.5 and imports around 700 objects in 2 minutes including liking everything to another class with customer data

P.S. if you are pulling from a mysql backend and didn't have done it so far check out mysqltuner.pl script for setting up the perfect memory usage.


I hope this is helpful for anyone.

Regards from Berlin
Rene Zander

[nedmaj]

I'm facing some performance problems here too. But the frontend operation is ok. The problem is with a synchronization job I use. I have a script used to export some data from GLPI/FusionInventory and import it into OTRS. I also use the OTRS API (LinkObject) to sync the links between the CIs. The API is terribly slow, since the links are done one by one. It takes almost 20 hours to make the links (here we have about 10000 CIs).