DX Infrastructure Management

Expand all | Collapse all

Data engine slow data insert

  • 1.  Data engine slow data insert

    Posted 09-04-2018 06:50 AM

    Hi,

     

    We have some issue in data_engine.

    Almost everyday we got data_engine slow data insert then showing high queue and the impact is delay data in UMP.

    It's like periodically, this issue usually was happened at evening and solve at midnight.

     

    Any clue about this?

     

    Thanks.



  • 2.  Re: Data engine slow data insert

    Posted 09-04-2018 06:56 AM


  • 3.  Re: Data engine slow data insert

    Posted 09-04-2018 07:06 AM

    There could be a few things going wrong here so let's try something to narrow down the problem.

    Put the data_engine into log level 1 and increase the size of the log so that you can capture a days worth.

    The log level 1 will give you insert times in your log for your bulk inserts which will look like this…

    Sep  4 16:30:46:578 [4800] de: Commit - inserted   11 rows to RN_QOS_DATA_0172 in   7 ms (ms/r:0)

    In the above example we can see the insert time for 11 rows was 7 ms which is good.

    If you have insert times higher than 100-150ms (depending on number of rows) then you likely to have a problem in your database performance and ask your DBA to look at this.

    If it is a sporadic problem and if your database has some shared resources it is likely the other application is impacting UIM.

    We recommend that the database is dedicated to UIM at all levels.

    Hope this helps



  • 4.  Re: Data engine slow data insert

    Posted 09-04-2018 07:20 AM

    Insert time in my environment is higher than 100-150ms, even it can be higher than 1000ms.

    So this is database performance problem?



  • 5.  Re: Data engine slow data insert

    Posted 09-04-2018 07:26 AM

    That is where I would continue your investigation…



  • 6.  Re: Data engine slow data insert

    Posted 09-04-2018 10:32 PM

    In previous we saw sessions from sqlplus has a a large amount of bulk insert into tables in same tablespace, from awr report, we saw a lot of disk hits from these sessions, I am not sure if this is still the case. consider that you can move these (including the custom probe) to other database (not to share disk) , if you need to access them in UIM, use database link or create a new db connection in dashboard if they are to be present in HTML5 dashboard.



  • 7.  Re: Data engine slow data insert

    Posted 09-07-2018 04:09 AM

    I would also suggest you check you db auto grow settings. With the volume of data you are collecting you could be forcing the db to continuously auto grow and this will effect performance



  • 8.  Re: Data engine slow data insert

    Posted 09-04-2018 11:59 AM

    Auto index is enabled and it is running in particular time ? Keep it disabled for a day and check.



  • 9.  Re: Data engine slow data insert

    Posted 09-06-2018 11:30 PM

    How to check it?



  • 10.  Re: Data engine slow data insert

    Posted 09-06-2018 11:41 PM

    Please check the below snap



  • 11.  Re: Data engine slow data insert

    Posted 09-07-2018 12:00 AM

    I couldn't find the option in my IM

    Any other option to check auto index?



  • 12.  Re: Data engine slow data insert

    Posted 09-07-2018 12:15 AM

    Your data statistics are huge. I've got 7,000 robots and I'm only hitting 60k messages a minute and I think that I still collect too much data. You're at twice that. Unless you have 14,000 robots generating data, I think that I'd spend some time looking into what is generating your data volume and reducing it.

     

    -Garin



  • 13.  Re: Data engine slow data insert

    Posted 09-07-2018 04:15 AM

    Index maintenance is that Option here . Uncheck that and see for a day 



  • 14.  Re: Data engine slow data insert

    Posted 09-07-2018 07:01 AM

    Ok. What's the impact if I uncheck that? Does it impact to Data performance? or something else?



  • 15.  Re: Data engine slow data insert

    Posted 09-07-2018 05:03 AM

    Hi Zahir,

    We have a similar problem, can you let me know what DB you are running? We are running Oracle RAC and I was wondering if this might be causing an issue. If you're using SQL then that might save us time going down that route.



  • 16.  Re: Data engine slow data insert

    Posted 09-07-2018 05:25 AM

    Oracle RAC is a supported database and so you should get very good performance from this if it is setup properly.

    Can you tell me your message throughput (m/s) and the specs of your database server?



  • 17.  Re: Data engine slow data insert

    Posted 09-07-2018 07:02 AM

    I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.4.0



  • 18.  Re: Data engine slow data insert

    Posted 09-12-2018 07:05 AM

    Hi, I'm Zhahir colleague, and yes, we are using Oracle RAC DB too with Exadata machine. Are you using exadata machine too?



  • 19.  Re: Data engine slow data insert

    Posted 09-07-2018 10:53 AM

    What hardware is your SQL server running on? SSDs? We used to have QoS build up but once we moved to a faster storage medium (XtremIO) no more DB insert issues. 

     

    Also how is your data_engine schedule setup? We used to run the QoS maintenance only 1x a day at 3:00am but I've since changed it to run on an hourly basis so that the trimming of the QoS for the raw, hourly, daily rolls ups complete. 



  • 20.  Re: Data engine slow data insert

    Posted 09-10-2018 04:29 AM

    We using Oracle DB. This setup for data_engine.

    This for data_engine schedule setup.



  • 21.  Re: Data engine slow data insert

    Posted 09-10-2018 10:45 AM

    Suggestion: Do you really need 360 days worth of hourly data? I'd use the Admin Console and configure the data_engine probe to roll up the hourly data to daily after say X # of days. Your holding onto too much hourly. Just FYI I have mine set to:

    14 days of RAW

    up to 75 days of hourly then 

    up to 490 days of daily

     

    0 - 14 days is RAW, 15 to 75 days is hourly data, 76 to 490 is daily.

     

    This shrunk our DB size from 1TB to ~250-300GB now. 

     

    Also what medium is your DB running on? Hopefully SSD's otherwise your I/O from the DB inserts is not doing to handle that much data traffic.



  • 22.  Re: Data engine slow data insert

    Posted 09-12-2018 07:30 AM

    Hi Daniel,

     

    We are using Oracle Exadata Machine for the UIM DB, so I think the I/O is not the problem here.

    Btw for reducing the hourly and daily data, can we reduce it directly let say from 360 days to 90 days? Or we should reduce it gradually for example reduce it 60 days daily?



  • 23.  Re: Data engine slow data insert

    Posted 09-12-2018 12:17 PM

    You would have to do it gracefully. You'd have to wait for the QoS trimming to kick in and wait for it to finish before you can then reduce the #'s more. So the way you have it configured you have it running once a day starting at 2:40AM. 

     

    You could change that so it runs the QoS clean up every 2 hours, adjust the #'s then check how much QoS is not getting trimmed with the #'s you then specified. There is a DB query you can run that allows you to check what QoS is not trimmed off according to the #'s you specified. Not sure if it works with your Oracle DB but the link to the page is:

    Verifying that data_engine is deleting old raw and - CA Knowledge 



  • 24.  Re: Data engine slow data insert

    Posted 09-10-2018 03:28 PM

    To rewind, do you have a DBA you can talk to about Oracle tuning? Your screen shot indicates 1600 transactions a second. That's an extremely large number for a UIM install and if that's real, you need a dedicated resource for that system to maintain it.

     

    If you have 100 QoS collected per robot every 60 seconds, your 96,000 msg/min would imply that you have almost a thousand robots. Do you have that many? Now ask yourself if you really need that level of detail in your QoS - often the default number, if you haven't considered it, is an order of magnitude more than you need - how different is disk QoS every 15 minutes compared to every 60 seconds. Or CPU usage?

     

    -Garin