CODEWITHSUNDEEP
javaajaxamazon-redshiftdatabase-performance
opax
opax

Reputation: 139

Alternative to stopping SQL queries on Redshift when AJAX request is aborted

We have a lot of filters(vehicle year, model, make, driver name etc). Every selected filter will generate 5-10 SQL queries, which are executing between time range 5-15 seconds. Let's say that our users mark filters very fast: selected some driver, next some vehicle, some data range etc. Within 15 seconds a user can send several dozen AJAX request, which reaches to the server and run SQL queries, the database is clogging, gets more and more queries and each execution takes longer.

We thought about the solution which will stop query when AJAX request is aborted, but this is not an easy solution. Is there better and easier solution than stopping queries when AJAX request is aborted?

We use Redshift for analytics data and PostgreSQL for user data etc. We have high-level cache, which caches the results. Redshift has 16 nodes of dc2.large instances, we have also set 15 concurrent queries on WLM configuration(queries don't queue up). We have encoding for all tables, sortkeys and distkeys. We have a several dozen tables, but one big with all transactions which have about 300M records and ~70 columns. In AWS console on Redshift Performance tab, we can see that leader node works for 99-100% and other nodes about 10-30% during heavy load.

Upvotes: 0

Views: 417

Answers (1)

Sar009
Sar009

Reputation: 2276

First of all, redshift is not meant for transactional DB its an analytical DB. Secondly, if there is a huge number of queries you should consider grouping the queries or caching the result. Also, consider using work load management.

Comming back to the original problem you will have to figure out the queries which were started by the timed out AJAX call. For which you will have to rely on svv_transactions and stv_recents these tables will return you the processes and queries that are being run. Next, you will have to PG_CANCEL_BACKEND to cancel the query or in worst case use PG_TERMINATE_BACKEND to terminate the process that is being run. Although there is no easy way to automate this if you happen to automate it do share your experience 😛.

Upvotes: 3

Related Questions