Which data store is best for my scenario?

Question

I'm working on an application that involves very high execution of update / select queries in the database.

I have a base table (A) which will have about 500 records for an entity for a day. And for every user in the system, a variation of this entity is created based on some of the preferences of the user and they are stored in another table (B). This is done by a cron job that runs at midnight everyday.

So if there are 10,000 users and 500 records in table A, there will be 5M records in table B for that day. I always keep data for one day in these tables and at midnight I archive historical data to HBase. This setup is working fine and I'm having no performance issues so far.

There has been some change in the business requirements lately and now some attributes in base table A ( for 15 - 20 records) will change every 20 seconds and based on that I have to recalculate some values for all of those variation records in table B for all users. Even though only 20 master records change, I need to do recalculation and update 200,000 user records which takes more than 20 seconds and by then the next update occurs eventually resulting in all Select queries getting queued up. I'm getting about 3 get request / 5 seconds from online users which results in 6-9 Select queries. To respond to an api request, I always use the fields in table B.

I can buy more processing power and solve this situation but I'm interested in having a properly scaled system which can handle even a million users.

Can anybody here suggest a better alternative? Does nosql + relational database help me here? Are there any platforms / datastores which will let me update data frequently without locking and at the same time give me the flexibility of running select queries on various fields in an entity?

Answer 1

Looks like the table B is some kind of cache. But that kind of cache which lowers productivity..

Even if you have 25 queries per second you could refuse the usage of the table B, and calculate answer for each request.

Anyway, if you have 30 sec delay on updating 20 records - it is a fail in a software architecture (I am wrong, if your DB calculates first 10^100 signs of PI for every record).

As I know, relational DB without ugly SQL-queries, with indexes, and with less than 1 000 000 records will work perfectly for almost all queries.

Try to refuse of usage of table B and add appropriate indexes to your table A (most modern databases have a helper tool). Next: try to optimize the structure of data (table A) and a query (using query analyzer, or with SQL-experts) to speed up the calculation. If you will update just 20 records - the existence of indexes will not harm productivity of a update process, but significantly improves select speed.

Answer 2

The question really is what system calculates the record to insert into B and the size of the B data.

Any database (eg MSSQL) should be able to handle the volume of inserts you are talking about no problem assuming the object isn't huge.

Updates may by a more difficult problem, but with the right indexing and locking, again shouldn't be a big problem.

99% of the time when I see a problem like this its due to the B record being calculated by a stored proc. This puts all the load on the db server

If this is the case the solution is to move this code to a offline service which can be called via a queuing system.

So your update A message would trigger a worker process which would loop through the users and create an update B message for each user

A second worker process B would pickup the update User X with data A event create the B record and update the DB

This can be scaled by adding more boxes with queue workers on them, so you have more and more processing power behind the calculation, leaving your db free to concentrate on updates and selects.

you can further optimize by separating the selects from the update/inserts. have a new DB which gets all the select requests as a replication slave the the old DB which gets all the updates.

Answer 3

If you are running in Amazon I would consider DynamoDB. It's flash memory based. Here is a link to it: https://aws.amazon.com/dynamodb/.

What kinds of RDBMS are you using? You may be able to increase performance by using a UDF, or a calculated field in a view. Are you running the calculation in the database via a single update query, or do you select the data out of the database, run the calculations in another process and then load them back in?

Oracle is configured by default to use snapshot mode execution, meaning the rows are not locked during update and concurrent selects get the original value. SQL Server is configured by default with pessimistic concurrency, so concurrent selects will block until the update is complete. Some versions of SQL Server can be put into snapshot mode, however it greatly increases the stress on the temp table.

What kind of environment are you running in? If it's an RDBMS on an EC2 instance in Amazon then try putting the DB datafiles on the the local flash disk. I've seen an order of magnitude difference in moving the files from EBS to the local disk.