0

I am developing a dictionary application and using many external sources to collect the data. This data is collected from those sources only for the first time, after that i persist it to my db and fetch it from their.

The problem i am facing is, some words like set, cut, put etc have 100's of meanings and many examples as well. It takes around 10 seconds to Persist all this data to mysql. I am using mybatis to persist data. And because of this, the response time is getting screwed up. Without this database persist, i get response in 400-500ms, if i show data directly after fetching from sources.

I am trying to find a way to persist the data in background. I am using MVC pattern so dao layer is separate.

Is it a good idea to use threading in the dao layer as a solution? Or should I use some messaging tool like Kafka to send a message to persist the given word in background? What else can I do?

Note: I prefer MySQL as the db right now, will probably use redis for caching later on.

Ayush Baheti
  • 187
  • 13
  • 2
    10 seconds to save data to MySQL means you're not using I/O of your hdd efficiently. Wrap your insert queries with a transaction block. You don't need redis for caching, MySQL is extremely fast when used properly (people just have no clue how to do that sadly, nor do they research). Your inserts should be more than quick enough. Pay attention to using `InnoDB` engine and that your `innodb_buffer_pool_size` variable is high enough so MySQL doesn't have to use your HDD for everything. – Mjh Dec 08 '16 at 16:39
  • Its actually firing those multiple requests one after the other. So it's like 100 insert queries. Also, logging is taking time, but even if i disable logging i don't think it will persist quickly. Thanks. I will increase innodb buffer size and will use a transaction block, and let you know the results – Ayush Baheti Dec 08 '16 at 16:43
  • So wrap those 100 queries in 1 transaction. Instead of 100 I/O's you'll spend 1. That makes it quick. – Mjh Dec 08 '16 at 16:55
  • 1
    Take a look at spring-batch, if you are not interested to use spring-batch, you could very well go through MyBatisItemWriter implementation, which batches and executes your insert query in a single transaction/ or if required you can change to multiple transactions using writer property. http://www.mybatis.org/spring/apidocs/reference/org/mybatis/spring/batch/MyBatisBatchItemWriter.html – Karthik Prasad Dec 09 '16 at 07:27
  • How do I use transactions in mybatis.. I am loading all the configurations from a config xml file. Using sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); to get sqlSessionFactory.. and finally sqlSessionFactory.openSession(true); to start a session. I am not able to understand, how do i start a transaction? Also, I am writing queries in xml and not using annotations. Can you help with how to start a transaction ?? @KarthikPrasad – Ayush Baheti Dec 09 '16 at 07:54
  • Are you using spring? – Karthik Prasad Dec 09 '16 at 08:19
  • No. @KarthikPrasad – Ayush Baheti Dec 09 '16 at 09:47
  • I searched online, but can't figure out how to use transactions! I guess will move to hibernate later sometime in future, meanwhile will be persisting the data in background by kafka. @Mjh Also, stackoverflow is not allowing me to post more questions, so that I can ask on how to use transactions :P – Ayush Baheti Dec 09 '16 at 10:49
  • Well, I don't know how to do it with mybatis, I just know how to start it manually. You issue `BEGIN TRANSACTION;` query, then you perform your insert queries and you issue `COMMIT;` query at the end. Those are MySQL statements (`begin transaction;commit;`), and since I've no clue what mybatis even is, I can't help you our in a better way sadly. – Mjh Dec 09 '16 at 11:41
  • Have you checked with implementation http://stackoverflow.com/questions/23486547/mybatis-batch-insert-update-for-oracle – Karthik Prasad Dec 09 '16 at 13:56
  • @KarthikPrasad I actually tried using loop inside the xml.. but the problem is, i need auto incremented id generated in mysql to be set in my java object. And it is not possible when done using for each inside xml, please tell me if it is? But the second answer in the link you posted will probably work. I'll try and let you know. Thanks :) – Ayush Baheti Dec 14 '16 at 03:52
  • Or if nothing works, I will generate a random Id in java and do batch insert. – Ayush Baheti Dec 14 '16 at 03:54

1 Answers1

0

My global answer on question + further comments:

Do not bulk insert with Mybatis foreach. Instead you shall execute the statement in a java iteration over the list of object to store, using ExecutorType Reuse or Batch(Read the documentation).

For transactions, in main mybatis-config xml, configure the environment:

  • transactionManager type JDBC to manage the transaction in the code session = sessionFactory.openSession(); session.commit(); session.rollback();
  • transactionManager type MANAGED to let the container manage.

Furthermore, you can let the web app send the response, while a new thread takes its time to store the data.

blackwizard
  • 2,034
  • 1
  • 9
  • 21