# 3 big indices with very slow query **URL:** \ **Category:** Elasticsearch\ **Created:** [March 15, 2019, 6:37pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594 "2019-03-15T18:37:48Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![ansamHox](https://avatars.discourse-cdn.com/v4/letter/a/54ee81/32.png) [@ansamHox](https://discuss.elastic.co/u/ansamHox)\ **Post date:** [March 15, 2019, 6:37pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/1 "2019-03-15T18:37:48Z")
I have 3 big indices (logs\_2016, logs\_2017 and logs\_2018). They are about 200gb size and 200000000 docs count. I'm doing 99% of my queries by parameter user\_id. Here's the mapping for logs\_2017 e.g.: ``` { "logs_2017": { "mappings": { "log_item": { "_routing": { "required": true }, "properties": { "@timestamp": { "type": "date", "format": "strict_date_optional_time||epoch_millis" }, "app_id": { "type": "string", "index": "not_analyzed" }, "user_id": { "type": "string", "index": "not_analyzed" }, "description": { "type": "string" }, "type_app": { "type": "long" }, "os": { "type": "string", "index": "not_analyzed" }, "session_id": { "type": "string", "index": "not_analyzed" ``` .. . } } } } } } How I query (getting all logs for specific event\_id and device\_type, ordering by timestamp and session\_id): ``` GET logs_2016,logs_2017,logs_2018/_search { "query": { "bool": { "must": [ { "match": { "user_id": "32232699237" } }, { "match": { "device_type": "31" } } ] } }, "size":100, "from":0, "sort":[{"timestamp":{"order":"desc"}}, {"session_id":{"order":"desc"}}] } ``` My results are very slow, 2-4 seconds. How can I improve query results, should I put more indices with the same last event\_id number e.g. or maybe monthly indices (12 per year, but then I would do searching over 36 indices for 3 years)? Running ES on 2 client nodes (4 CPU, 6GB RAM, 2GB HDD), 2 data nodes (8 CPU, 16GB RAM, 400GB HDD). While doing logs template i put 16 number of shards and 1 number of replicas. Ty ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [March 15, 2019, 7:03pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/2 "2019-03-15T19:03:55Z")
What does CPU and disk I/O (and iowait) look like on the data nodes when you are querying? Are you indexing at the same time? ---
**Author:** ![safwan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/safwan/32/31879_2.png) [@safwan](https://discuss.elastic.co/u/safwan)\ **Post date:** [March 15, 2019, 8:36pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/3 "2019-03-15T20:36:20Z")
> [@ansamHox](#): > > I have 3 big indices (logs\_2016, logs\_2017 and logs\_2018). They are about 200gb size and 200000000 docs count. I'm doing 99% of my queries by parameter user\_id. Here's the mapping for logs\_2017 e.g.: Your query does nto reflect your mapping type. Can you provide the correct mapping here where `event_id` and `device_type` is present? ---
**Author:** ![ansamHox](https://avatars.discourse-cdn.com/v4/letter/a/54ee81/32.png) [@ansamHox](https://discuss.elastic.co/u/ansamHox)\ **Post date:** [March 15, 2019, 9:33pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/4 "2019-03-15T21:33:11Z")
it should be user\_id instead of event\_id, i just copied wrong query.. ---
**Author:** ![ansamHox](https://avatars.discourse-cdn.com/v4/letter/a/54ee81/32.png) [@ansamHox](https://discuss.elastic.co/u/ansamHox)\ **Post date:** [March 15, 2019, 9:33pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/5 "2019-03-15T21:33:53Z")
where can i check this, \_cat/nodes?v ? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [March 15, 2019, 9:36pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/6 "2019-03-15T21:36:14Z")
If you are on Linux you can use 'iostat -x'. ---
**Author:** ![ansamHox](https://avatars.discourse-cdn.com/v4/letter/a/54ee81/32.png) [@ansamHox](https://discuss.elastic.co/u/ansamHox)\ **Post date:** [March 15, 2019, 9:47pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/7 "2019-03-15T21:47:58Z")
i can connect only to client node via ssh unfortunately.. ---
**Author:** ![safwan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/safwan/32/31879_2.png) [@safwan](https://discuss.elastic.co/u/safwan)\ **Post date:** [March 15, 2019, 10:03pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/8 "2019-03-15T22:03:22Z")
I think you should use keyword datatype instead of `string` datatype. And moreover, it seems like you should use `filter` with `term` query instead of `must` with `match` query. Can you run below query without changing the mapping and check if any performance get improved? ```auto GET logs_2016,logs_2017,logs_2018/_search { "query": { "bool": { "filter": [ { "match": { "user_id": "32232699237" } }, { "match": { "device_type": "31" } } ] } }, "size":100, "from":0, "sort":[{"timestamp":{"order":"desc"}}, {"session_id":{"order":"desc"}}] } ``` ---
**Author:** ![ansamHox](https://avatars.discourse-cdn.com/v4/letter/a/54ee81/32.png) [@ansamHox](https://discuss.elastic.co/u/ansamHox)\ **Post date:** [March 15, 2019, 10:09pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/9 "2019-03-15T22:09:20Z")
I already tried filter query, but I'm getting pretty much same results (3-4 seconds) 😕 ---
**Author:** ![safwan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/safwan/32/31879_2.png) [@safwan](https://discuss.elastic.co/u/safwan)\ **Post date:** [March 15, 2019, 10:11pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/10 "2019-03-15T22:11:59Z")
What about sorting the indexes according to `timestamp` and `session_id`? [https://www.elastic.co/guide/en/elasticsearch/reference/master/index-modules-index-sorting.html](https://www.elastic.co/guide/en/elasticsearch/reference/master/index-modules-index-sorting.html) ---
**Author:** ![safwan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/safwan/32/31879_2.png) [@safwan](https://discuss.elastic.co/u/safwan)\ **Post date:** [March 15, 2019, 10:16pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/11 "2019-03-15T22:16:58Z")
You may also use the `track_total_hits: false` so that Elasticsearch will not try to count the number of documents. ---
**Author:** ![ansamHox](https://avatars.discourse-cdn.com/v4/letter/a/54ee81/32.png) [@ansamHox](https://discuss.elastic.co/u/ansamHox)\ **Post date:** [March 15, 2019, 10:50pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/12 "2019-03-15T22:50:00Z")
forgot to mention i'm using ES 2.3 version, there is no index sorting or type keyword in this version.. ---
**Author:** ![safwan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/safwan/32/31879_2.png) [@safwan](https://discuss.elastic.co/u/safwan)\ **Post date:** [March 15, 2019, 10:54pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/13 "2019-03-15T22:54:10Z")
In that case the best advice I can give is to upgrade your cluster to atleast 5.x ---
**Author:** ![ansamHox](https://avatars.discourse-cdn.com/v4/letter/a/54ee81/32.png) [@ansamHox](https://discuss.elastic.co/u/ansamHox)\ **Post date:** [March 15, 2019, 10:56pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/14 "2019-03-15T22:56:58Z")
Need to reindex everything, it will take 4 days 🙂 ---
**Author:** ![safwan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/safwan/32/31879_2.png) [@safwan](https://discuss.elastic.co/u/safwan)\ **Post date:** [March 15, 2019, 11:04pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/15 "2019-03-15T23:04:10Z")
If you are not able to upgrade, I think then you should increase the data node of your cluster. Seems like every index have `16` shards and `1` replica. And there are 3 indexes. So total `16x2x3` = `96` shards. 2 nodes are overwhelmed with this `96` shards. Maybe increasing the data node will solve the slow query issue. ---
**Author:** ![ansamHox](https://avatars.discourse-cdn.com/v4/letter/a/54ee81/32.png) [@ansamHox](https://discuss.elastic.co/u/ansamHox)\ **Post date:** [March 15, 2019, 11:22pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/16 "2019-03-15T23:22:57Z")
I don't think that will be possible in the near future.. probably trying those things you mentioned in new ES version. Was testing now without "sort" and it's about 5 times faster (that's acceptable), but I need data to be sorted by `timestamp`. Just realized all users have `user_id` in number format, and I have "string" in my mapping. Does it matter or it would be the same? And thanks a lot.. ---
**Author:** ![safwan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/safwan/32/31879_2.png) [@safwan](https://discuss.elastic.co/u/safwan)\ **Post date:** [March 15, 2019, 11:55pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/17 "2019-03-15T23:55:02Z")
I dont know if saving the `user_id` will help, but I think if you do not need mapping store for the field you needed. Maybe it will give some performance boosting. [https://www.elastic.co/guide/en/elasticsearch/reference/2.3/mapping-store.html](https://www.elastic.co/guide/en/elasticsearch/reference/2.3/mapping-store.html) ---
**Author:** ![ansamHox](https://avatars.discourse-cdn.com/v4/letter/a/54ee81/32.png) [@ansamHox](https://discuss.elastic.co/u/ansamHox)\ **Post date:** [March 19, 2019, 11:30am UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/18 "2019-03-19T11:30:58Z")
Should I put more data nodes with less size maybe? I can put maybe 8 data nodes X 50GB instead of 2 data nodes X 200 GB? ---
**Author:** ![Alex\_Marquardt](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alex_marquardt/32/42925_2.png) [@Alex\_Marquardt](https://discuss.elastic.co/u/Alex_Marquardt)\ **Post date:** [March 27, 2019, 7:09pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/19 "2019-03-27T19:09:23Z")
How many shards do you have in each index? ---
**Author:** ![ansamHox](https://avatars.discourse-cdn.com/v4/letter/a/54ee81/32.png) [@ansamHox](https://discuss.elastic.co/u/ansamHox)\ **Post date:** [March 27, 2019, 8:43pm UTC](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594/20 "2019-03-27T20:43:07Z")
16, and 1 replica [Next page](https://discuss.elastic.co/t/3-big-indices-with-very-slow-query/172594.md?page=2)