AFAIK, when we insert higher bulk size we can get more performance compared to lower bulk size. for example 1000 batch better than 500 batch. I haven't done this with MySql but found good references about bulk insert performance improvements. Basically these will explain how transaction,DB buffer size , indexing and etc impact on bulk insertion.
[1] http://dba.stackexchange.com/questions/29913/innodb-insertion-faster [2] http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html *Dhanuka Ranasinghe* Associate TechLead WSO2 Inc. ; http://wso2.com lean . enterprise . middleware phone : +94 715381915 On Thu, Jun 16, 2016 at 4:26 PM, Gihan Anuruddha <[email protected]> wrote: > The same code snippet points out during the ESB-Analytics performance test > as well. Queue not releasing fast enough due to database slowness. So > introducing new queues will not solve the problem, but may introduce OOM > situations. > > On Thu, Jun 16, 2016 at 4:16 PM, Sajith Ravindra <[email protected]> wrote: > >> Gihan, it's true that IO for database operations is also a bottleneck. >> But if you take a look at the thread dump many data receiving threads(to >> persist events of incoming streams) and processing threads (to persist >> events of output streams) are just blocked on this lock trying to insert >> events to the queue. From what I see it's not that threads are blocked >> because queue is full. >> >> Apparently at this point, blocking of these receiving and processing >> threads seems to degrade the performance. >> >> >> On Jun 16, 2016, at 3:48 PM, Gihan Anuruddha <[email protected]> wrote: >> >> Actually bottleneck is coming from the database. Data insertion >> back-pressure shows at this level. What is the exact data insertion rate >> for database(not the server TPS). Btw Innodb give poor performance than >> MyISAM. Also, in MySQL TPS gets degraded with the data growth. >> >> On Thu, Jun 16, 2016 at 2:23 PM, Sajith Ravindra <[email protected]> >> wrote: >> >>> Thread dump for reference >>> >>> >>> https://drive.google.com/a/wso2.com/file/d/0B92QGepCpPD9SFdjQi1OeV9DUUk/view?usp=sharing >>> >>> Thanks >>> *,Sajith Ravindra* >>> Senior Software Engineer >>> WSO2 Inc.; http://wso2.com >>> lean.enterprise.middleware >>> >>> mobile: +94 77 2273550 >>> blog: http://sajithr.blogspot.com/ >>> <http://lk.linkedin.com/pub/shani-ranasinghe/34/111/ab> >>> >>> On Thu, Jun 16, 2016 at 2:18 PM, Sajith Ravindra <[email protected]> >>> wrote: >>> >>>> Hi all, >>>> >>>> We are doing a performance test on IS analytics server. While we keep >>>> pumping data to the server we can see that throughput gradually decreases >>>> to a very low (< 1K) TPS. We used MySQL with InnoDB with tuned parameters. >>>> And set Xms2g and Xmx4g in DAS. >>>> >>>> When looked at the thread dumps we can see that more than 100 threads >>>> BLOCKED/TIMED_WATING at AnalyticsEventQueueManager.put() on a single lock >>>> object(i.e. on the same reference). Many threads are just waiting to get >>>> the lock without processing events which seem to be the reason for degraded >>>> performance. >>>> >>>> Are there any parameters that we can tune to gain better performance to >>>> overcome this bottleneck ? >>>> >>>> I had a look at the code and AFAIU all events of a particular tenant >>>> are persisted through a single synchronized queue. According to my >>>> understanding, this synchronized queue is a performance bottleneck since >>>> all threads pertaining to a persisted event stream will need to block on >>>> this queue. >>>> >>>> IMO if we can have a queue per event-sink(i.e per persisted stream) it >>>> should give a better performance than this. Any implications if we do that >>>> or is there a better option? >>>> >>>> >>>> Thanks >>>> *,Sajith Ravindra* >>>> Senior Software Engineer >>>> WSO2 Inc.; http://wso2.com >>>> lean.enterprise.middleware >>>> >>>> mobile: +94 77 2273550 >>>> blog: http://sajithr.blogspot.com/ >>>> <http://lk.linkedin.com/pub/shani-ranasinghe/34/111/ab> >>>> >>> >>> >> >> >> -- >> W.G. Gihan Anuruddha >> Senior Software Engineer | WSO2, Inc. >> M: +94772272595 >> >> > > > -- > W.G. Gihan Anuruddha > Senior Software Engineer | WSO2, Inc. > M: +94772272595 > > _______________________________________________ > Dev mailing list > [email protected] > http://wso2.org/cgi-bin/mailman/listinfo/dev > >
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
