1

In our application we have a page were we list our entities with many options to filter and to search. Over time this page got slower and slower especially for large datasets, so we thought about how to improve the performance. I have two ideas:

  1. Use a service like Elasticsearch which is built for problems like these (though we would not like to introduce an additional dependency)
  2. Implement proper caching

However caching only provides a speedup if the same resource is requested twice or more. On a page where records can be filtered in many different ways however I don't believe that the speedup of caching will come into use often because of the huge variety with which the data will be filtered and presented. And even if a user is requesting the page using identical filters as someone (or himself) before, there is a high probability that until then the data has changed and therefore the cache is invalid again.

Am I wrong in my assumption? How to develop pages like these in a performant way?

Thanks

EDIT:

Here some more information:

We are using Ruby on Rails, a MySQL database and the ActiveRecord ORM. For one request of the mentioned page we fetch data from about 10 different database tables with each containing between 5000 and 5,000,000 entries. There is rarely text-based searching, most of the time we search by filtering foreign keys as in "Give me all employees of company XY". Our database is properly indexed, about this we took great care. I furthermore did a thorough analysis on the queries using the MySQL EXPLAIN functionality. Filtering and preparing the data and meta information (e.g. record counts) happens on the server-side.

The site performs poorly because it displays a lot of information and therefore has to execute several heavy queries. As an example for a heavy query:

Get all rooms for a certain building where at least one person is sitting. In order to get this data we have to first fetch all people of this building and then get the count of people for each room to see whether there is at least one person in it.

  • We need more information to answers. First of : I guess you're currently using a relational database, an idea of the current volume of data (tables with most rows, number of tables queries) would help. Are you using some ORM like tools ? For instance hibernate as a 2nd level cache very usefull for that. Is your query very heavy on text search ? If so yes lucène/elasticsearch/ or any indexer specialized in that stuff will do it. Finally : have you perform some measurement about where your queries get stucked ? In the database ? In the server ? – Walfrat Apr 26 '18 at 13:30
  • Also is your database design good ? Is everything properly setup using FK and join table where needed ? Are the index goods ? Do you have post-filtering on the server side (common case of OOM and very slow application). They're tons of stuff you can do to improve performance, the only thing we can answer at the moment is to do measurement to find the actual bottleneck. – Walfrat Apr 26 '18 at 13:33
  • If it was faster to begin with and has slowed down since, it's a sign that the database needs tweaking. If you have lots of data and you're searching using columns which aren't in an index, your first big hiccup is this. – Neil Apr 26 '18 at 13:34
  • Can you give us a more concrete understanding of the scale of a 'large dataset'? 10K, 100K, 1000K, ... records? – JimmyJames Apr 26 '18 at 13:36
  • 2
    I think you are saying you are doing the filtering in code, your comment didn't really clarify. One common thing that I see very often with performance is trying to store all the results in memory before you filter or return anything to the client. This is very costly, not only in terms of memory usage: it's slow too. If you can't easily restructure the code to filter in the DB, you might want to look into streaming the data, if you aren't already. Caching seems unlikely to help here but you might want to do analysis on the calls made and see how often you get repeats. – JimmyJames Apr 26 '18 at 18:25
2

First of all, caching is hard. You can get some amazing performance improvements when it's implemented correctly and the cache hit ratio is high enough, but you'll gnash a lot of teeth to get there.

Look at this as "just another optimization problem" and proceed accordingly.

This means, don't guess. Find a way to measure the performance of your application and prove conclusively which parts are causing pain. Since (I assume) this is a web app, I'd start by hitting F12 and using the profiler.

Initial questions to ask:

  • Is the web browser taking a long time to process the data once it has it?
  • Is it taking too long to get data to the browser?
  • Is the server taking to long to obtain the data?

Answers to these questions will dictate your next set of questions which will dictate the next (an so on).

  • Caching only partially solves the problem. I think we need more information before we can answer this question properly. – Neil Apr 26 '18 at 13:35
  • Is the datasource taking too long to resolve the request (query)? , For DB connections, is the driver Up to date and has no well-known performance issues?, Have you priorized i/o processes over more trivial precesses of the system?, Is the application sharing CPU, memory and HDD with other applications?,have you sized the application (mem) accordingly to the desirable throughput?, and so on... – Laiv Apr 26 '18 at 18:42
0

Put an index on your database.

Databases are good at searching and filtering, if they have appropriate indices. Then write queries that use these indices. Log and analyse slow queries to find out why they are slow. Without an index, the search has to filter each item, which gets progressively slower as more items are added.

If the load on your DB server is too high, consider whether the DB can be replicated or sharded. This will allow you to distribute your load over multiple servers. Note that sharding doesn't help if all commonly requested items are on one node.

Whether you use a special database like Elastic Search or just use the features that MySQL already has to offer doesn't matter very much here, if you have a suitable data model. E.g. if full-text search is necessary, then a specialized database might be more appropriate.

Caching by itself will not make all your queries fast. It can merely reduce the load on your DB. If you gather statistics for your queries, you might see that some requests are indeed very common (such as items on a landing page). This might actually be a sufficientl solution for your problem, if your measurements indicate this. But caching doesn't actually speed up each individual query.

Note that you could, in theory, write clever code that filters already-cached data. But at that point, you are reinventing a database. There are likely to be bugs. It is much easier to use the real thing, unless creating a new database engine is the product you are offering.

  • 1
    For 5 searched columns, to cover everything you'd need 2^5 or 32 indexes. My first instinct is to assume something should be addressed from the database standpoint, but I don't think there is enough info to determine that yet. – Neil Apr 26 '18 at 13:38
0

The point is here:

Nobody is able to give you proper advice without seeing your system.

Of course, you could get general advice like:

  • use caching
  • use indizes
  • use sharding
  • use denormalization

etc. All boils down to a) »measure« and b) »better organizing« your data.

Which is all not wrong. But from what you wrote, your system is already tuned, so it doesn't help.

Our database is properly indexed, about this we took great care.

Maybe you have a look at Pilosa. Sounds, that your application is a use case for this.

More background:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.