0

I've an api, notifyCustomers() implemented on my batch server which gets called from my application server. It can send notification via three channels SMS, Push & Email. I've separate helper classes for each of them and they all execute in async mode.

I've got around 30k users out of which I usually send notification to the particular set of users ranging from 3k to 20k. The issue that I face is whenever I call that api, mysql performance just goes for a toss, particularly CPU. CPU utilisation goes around 100% for a very long period of around 30 mins

I've figured out workaround by doing following things and it's helping me in keeping things under control:

  1. Using projection instead of domain object
  2. Getting data in batch of 500 in each call
  3. Implemented indexing based on the criteria that I need
  4. No database calls from async methods of SMS, Email and Push
  5. Thread.sleep(10 mins) between each subsequent fetch operation of data batches <== This is the dirty hack that's bothering me a lot

If I remove Thread.sleep() then everything goes haywire because batch server just calls async methods and then fires up db call to fetch next batch of 500 users in very quick successions till the time db server stops responding.

I need help with what I shall be doing in order to get rid of 5th point while keeping things under control? I'm running mysql on RDS with 300 IOPS and 4 GB RAM (db.t3.medium)

Vishal Pawale
  • 3,416
  • 3
  • 28
  • 32
  • If thread.sleep(10mins) allows your solution to survive use the technique and while you are being successful, provide additional information of. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. A) complete (not edited) my.cnf or my.ini From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report for server workload tuning analysis to provide suggestions for reducing query completion time required. – Wilson Hauck Mar 22 '20 at 19:22
  • Maybe you should try using synchronizers such as a Semaphore or queues to balance the load? – Ilya Lysenko Mar 22 '20 at 19:30

0 Answers0