Recently we started to work on an improved method to search through our fitting database. As you might have noticed our site was quite slow late December 2020 and early January of this year.
The background of the issue!
This was caused due to the fact that we established an import for zKillboard which made the database grow by nearly 300.000 temporarily fits. Due to this we had to create indexes which cause an other issue. As we are using the free edition of Microsoft SQL Server which has a limit of 10 GB per database.
Guess what, when we enabled this the database grew to a small 9.6 GB leaving 400 MB. So we had multiple choices available…
- Buy a Microsoft SQL Server license (Standard or Web edition)
- Automatically remove all zKillboard imported fits after 3 days*
For now we chose to remove all the zKillboard imported fits automatically after 3 days, but we will add an option where the user can claim its fit and add it to his/hers profile.
In the meanwhile the database shrunk back to around 4.4 GB which gives is a small 5.6 GB of space left… Meaning that eventually will need a MSSQL license…
The solution we think should benefit perfomance.
While the background seems to be non related to the search engine it also gave us a look on the future performance of our platform. While Microsoft SQL Server is one of the better database engines out there it still is a SQL environment which can be slow on big search queries like we do on EVE Workbench.
When we started you could only search for parts in the title and ship name, but that changed last year when we introduces some new features such as search on hull, class, author and tags. To combine all this useful information we decided to change the search engine in our code and make if future proof and, maybe more important, fast and reliable.
In order to accomplish this there a few options available you can use.
We could use an internal caching system for .NET Standard framework but this would mean we have to change our configuration of the servers as this would cost a lot more RAM.
Next to this we could chose to use Redis as an external caching system but this would also involve a new configuration and it mainly uses the system memory to cache everything. Meaning after a restart this would be cleared and the search would just malfunction.
The third option that came across our mind is using some sort of elastic search software such as Apache Lucene. This would give us the flexibility of a special piece of software build for these kind of tasks and also using persistent storage, meaning that after a restart all data still is available.
So we chose to start working on a new search engine for our website using Apache Solr which is build on top of the Lucene engine. Using this setup we think we can handle the growth for the next few years and be more flexible on the data we want to add in the search areas of our website.
For now we will be changing the Fit search to use Solr but maybe we will change more features of our website to use this fantastic and quick search engine.
As we just started the development for this we can’t post any data (yet) but as soon as we have more details, schematics or statistics available we will share them with you on this page!