-1

We run an application more like competition organizing websites like HackerEarth.com, HackerRank.com. When the tasks start, we see huge load on our server. The problem is, we need to interact with Postgres heavily in order to give random questions and then submitting their answers. There are JOINS between multiple tables and searching and other things, which results in a lot of data traffic and it makes our system slow and unresponsive many times, which is embarrassing.

What can we do in order to make it better? How to handle things which interacts a lot with RDBMS, any caching or in-memory database usage. I searched a lot over various sources, but couldn't find concrete solutions for our needs. How companies organise database and user interactions? What are some good architectures for models like these? Any insight would be helpful.

theGamblerRises
  • 101
  • 1
  • Those answers are more about Hardware planning. I am asking more in terms of architecture. Should I move my question to StackOverFlow? – theGamblerRises Jul 01 '16 at 06:20

2 Answers2

2

You need to put monitoring in place to gather metrics and use Scientific Method to identify bottlenecks.

You can then plan, implement and test solutions.

Iterate as required.

user9517
  • 114,104
  • 20
  • 206
  • 289
0

In general there are a couple of points you need to take care of

  1. Make sure that the database tables are correctly designed and indexed is essential.

So, you should have proper column data types according to data stored, and indices on columns that are used for SELECTs / JOINs.

  1. Use caching for data that does not need to be fresh on every page load.

  2. Allocate proper resources for your database engine. Make sure it gets proper amount of real memory to use and it doesn't use swap.

There cannot be concrete sources like how to do it exactly, since it all depends on your particular application and data you store.

Tero Kilkanen
  • 34,499
  • 3
  • 38
  • 58