Hi Doug, Thanks very much for your response. You have answered my question. We only use ITSM out-of-the-box so I will assume that all ITSM and Atrium workflow has been built to use indexes and therefore there is a low risk that we will see any inefficient queries.
We will also be looking at moving our CMDB services onto a separate server and then we re-balance our server groups as you have suggested. Regards, Mike. Michael Worts Service Management Programme Systems Lead Arqiva Direct 01962 822705 Mobile 07801 755346 Crawley Court, Winchester, Hampshire SO21 2QA www.arqiva.com<http://www.arqiva.com/> ________________________________ From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Mueller, Doug Sent: 21 December 2012 18:18 To: [email protected] Subject: Re: 30+ million rows in the CMDB ** Mike, To answer your questions: 1. Yes 2. No significant additional performance impact vs. 1 million 3. Yes I hope this is clear and I am sure that there are no further questions on this topic...... OK, maybe some additional comments would be useful? I am going to start with the third question - Is anyone already doing this? We have at least 10 customers with CMDBs IN PRODUCTION that are at 30M or more records. We in fact have several customers at the 70 and 80M range currently and two who expect to be in the 150 to 220M range within a year to 18 months. They are managing their configurations, they load updates, they normalize, they reconcile, they have their incident/change/other processes tied to the CMDB. This is being used as the enterprise CMDB and as the master control point of configuration. So, yes, we have customers (and more than one) doing things to larger than the scale you are talking about. Now, back to question 1 - Is it practical? Yes, it is practical. If you have that many things in your environment and you are tracking the configuration of your environment, then that is what you need. It is practical to have many items in the store. Databases are designed to handle multi-millions of records quite well. In fact, 30 million records in a table is not really a big issue for an enterprise database. There are many tables in large scale enterprises of this size or larger in many different applications - think credit card transactions or bank records or sales or inventory records for large organizations. On to the topic of performance impacts. Of course, if you have 30 million records in a table and you do a query that is inefficient and triggers a table scan, it will be slow. However, if you have 1 million records (or 500,000 records) in a table and you do a query that is inefficient and triggers a table scan, it will be slow. Sure, the 1 million record query may be slow and take say 1 minute and the 30 million record query would be slow and take 15 minutes. But, really, if it takes a minute it is over reasonable wait time already. The key to any large data store is to NEVER EVER EVER query such that a table scan is triggered (unless you really mean it and understand what you are doing). The key is good indexing. The way that the CMDB is accessed always uses indexes. The only time there is the risk of a non-indexed query is when some API program is going direct or when you go to the CMDB query screen directly and just "search for a CI or relationship" and specify bad queries. So, sure, you need to control the access to direct querying and you need to pay attention to that, but the normal use of the CMDB is just fine and does not cause an overall impact to ITSM or the CMDB or to the AR System environment. Now, with this scale of data, there are various configuration choices you may want to make because this volume means lots of data loaders and lots of reconciliation and lots of overall CMDB processing. So, you may want a dedicated server in the server group (not behind load balancer and all CMDB loaders go to that server and reconciliation runs on that server and so on) This isolates the load and prep work. Then, all servers can query (using indexed queries) the data. This is a common configuration choice in environments with large CMDBs. Easy to configure, fully supported with standard functionality of the system. Hopefully, this gives a bit of insight into your questions and helps you with your implementation. Doug Mueller From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Mike Worts Sent: Friday, December 21, 2012 4:43 AM To: [email protected] Subject: 30+ million rows in the CMDB ** Hi all, Let's say that we have a requirement to load 30million records into the CMDB and there is a good business justification. 1. Technically, do you think this is good practical? 2. How would it impact ITSM or CMDB performance? 3. Is anyone already doing this? My thought is that since all 30m records would have to go into BaseElement, there must be an impact to overall performance since BaseElement is queried on a regular basis. I assume that most queries will be indexed correctly, however, my concern is that one or two irregular queries on the form would have a serious impact. Thanks and Merry Christmas. Mike. Remedy ITSM 7.6.04 AIX 6.1 Oracle 11g This email, its content and any files transmitted with it are for the personal attention of the addressee only, any other usage or access is unauthorised. It may contain information which could be confidential or privileged. If you are not the intended addressee you may not copy, disclose, circulate or use it. If you have received this email in error, please destroy it and notify the sender by email. Any representations or commitments expressed in this email are subject to contract. Although we use reasonable endeavours to virus scan all sent emails, it is the responsibility of the recipient to ensure that they are virus free and we advise you to carry out your own virus check before opening any attachments. We cannot accept liability for any damage sustained as a result of software viruses. We reserve the right to monitor email communications through our networks. Arqiva Limited. Registered office: Crawley Court, Winchester, Hampshire SO21 2QA United Kingdom Registered in England and Wales number 2487597 _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ This email, its content and any files transmitted with it are for the personal attention of the addressee only, any other usage or access is unauthorised. It may contain information which could be confidential or privileged. If you are not the intended addressee you may not copy, disclose, circulate or use it. If you have received this email in error, please destroy it and notify the sender by email. Any representations or commitments expressed in this email are subject to contract. Although we use reasonable endeavours to virus scan all sent emails, it is the responsibility of the recipient to ensure that they are virus free and we advise you to carry out your own virus check before opening any attachments. We cannot accept liability for any damage sustained as a result of software viruses. We reserve the right to monitor email communications through our networks. Arqiva Limited. Registered office: Crawley Court, Winchester, Hampshire SO21 2QA United Kingdom Registered in England and Wales number 2487597 _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
