Q:

Solr how to improve query speed time after too many atomic update

I'm using solrCloud 7.4 with 3 instance (16GB RAM each instance) and have 1 collection with 10m data. For starter it really fast, almost no query more than 2 seconds.
Then i have updated with transaction (i.e popularity) data in other oracle database to make my collection more relevant. I just simply loop transaction then using solr atomic update like set and inc about 1~10 fields (almost all field type float n long). But transaction has more than 300m data. So the process i set and inc every 10k transaction data to collection in solr.
The update part of 300m data only process once, After that maybe take 50k/day and processing at 0am. 
In the end. the collection still have 10m data, but looks like my query has slow down almost up to 10 seconds.
I look in shard overview, each shard have 20+ segment and half of them are deleted document:

Have i do something miss here, why my query time drop? 
How do i speed up again like before?
Should i copy and creating new collection n reindex my 10m collection after atomic update (from 300m transc) to the new collection?

A:

The issue is caused by a large number of segments being created, mostly consisting of deleted documents. When you're doing an atomic update, the previous document is fetched, the value is changed, and the new document (with the new value) is indexed. This leaves the old document as deleted, while the new document is written to a new file. 
These segments are merged when the mergeFactor value is hit; i.e. when the number of segments gets high enough, they're merged into a new segment file instead of having multiple files around. When this merges happens, deleted documents are expunged (no need to write documents that no longer exists to a new file).
You can force this process to happen by issuing an optimize, and while you usually can rely on mergeFactor to do the job for you (depending on the value of mergeFactor and your indexing strategy), datasets where everything is updated in one go, such as once at night, issuing an optimize afterwards works fine.
The down side is that it'll require extra processing (but that would happen anyway if you just relied on mergeFactor, but not everything at the same time), and up to 2x the current size of the index as temporary space.
You can perform an optimize by calling the update endpoint for your collection: http://localhost:8983/solr/collection/update?optimize=true&maxSegments=1&waitFlush=false
The maxSegments value tells Solr how many segments its acceptable to end up with. The default value is 1. For most use cases that'll be fine.
While calling optimize has gotten a bad rep (since mergeFactor usually should do the work for you, and people tend to call optimize far too often), this is a perfectly fine use case for optimize. There are also optimization enhancements for the optimize command in 7.5, which will help avoid the previous worst case scenarios.

