Very slow aggregate query

Hi all I have 200 Gb data in Elasticsearch and when I excecute an aggregate query it takes 8 seconds. My client goes in timeout.
Actually my query is like that:

{
  "aggs": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "term":                 {
                "accountUuid": "12163397"
              }              
            },
            {
              "range": {
                "currentDate": {
                  "from": 1203891371186,
                  "to": 1806483371186                  
                }                
              }              
            }            ]          
        }        
      },
      
          "aggs": {
            "usage_time_histogram" : {
              "date_histogram" : {
                "field" : "currentDate",
                "interval" : "month"                 
              },
              "aggs": {
                "totalUpload": {
                  "sum": {
                    "field": "usageUpload"                            
                  }                          
                },
                "totalDownload": {
                  "sum": {
                    "field": "usageDownload"                            
                  }                          
                }  ,
                "totalTraffic": {
                  "sum": {
                    "field": "totalTraffic"                            
                  }                          
                }                        
              }               
            }            
                
      }      
    }     
  },
  "size": 0  
}

and the index is mappes like that:

{
    "statistic": {
        "mappings": {
            "properties": {
                "_class": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "accountUuid": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "appCategory": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "currentDate": {
                    "type": "long"
                },
                "fastwebCategory": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "profileID": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "serviceName": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "statisticID": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "totalTraffic": {
                    "type": "long"
                },
                "usageDownload": {
                    "type": "long"
                },
                "usageTime": {
                    "type": "long"
                },
                "usageUpload": {
                    "type": "long"
                }
            }
        }
    }
}

Is it only a hardware matter? How to increase speed of my aggregate query? Sorry for my poor english, and thanks in advance

I'd probably run something like this (not tested and json format is probably wrong):

{
    "query": {
        "bool": {
          "must": [
            {
              "term": {
                "accountUuid.keyword": "12163397"
              }              
            },
            {
              "range": {
                "currentDate": {
                  "from": 1203891371186,
                  "to": 1806483371186                  
                }                
              }              
            }            ]          
      },
          "aggs": {
            "usage_time_histogram" : {
              "date_histogram" : {
                "field" : "currentDate",
                "interval" : "month"                 
              },
              "aggs": {
                "totalUpload": {
                  "sum": {
                    "field": "usageUpload"                            
                  }                          
                },
                "totalDownload": {
                  "sum": {
                    "field": "usageDownload"                            
                  }                          
                }  ,
                "totalTraffic": {
                  "sum": {
                    "field": "totalTraffic"                            
                  }                          
                }                        
              }               
      }      
    }     
  },
  "size": 0  
}

Does it take 8 seconds on every run or only on the first run?

It could. What kind of hardware do you have? How much heap did you set for Elasticsearch?

It's well formed :grinning: but it was just to give you the idea of the kind of aggregate I need to do.
If I share the aggregate in 3 different query one for each field aggregate, could be faster?

I don't think it will. What is the total time from the Elasticsearch response?

7 seconds. My mobile client goes in timeout

Could you share the full JSON response? You could share it on gist.github.com if too big for this forum.

{
  "took" : 8647,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "filtered" : {
      "doc_count" : 3742,
      "category" : {
        "doc_count_error_upper_bound" : 0,
        "sum_other_doc_count" : 0,
        "buckets" : [
          {
            "key" : "Web Browsing",
            "doc_count" : 1126,
            "totalTrafficBucket" : {
              "value" : 1.795599E7
            },
            "services" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [
                {
                  "key" : "SSL",
                  "doc_count" : 417,
                  "totalTrafficPerService" : {
                    "value" : 1.0634734E7
                  }
                },
                {
                  "key" : "HTTP",
                  "doc_count" : 406,
                  "totalTrafficPerService" : {
                    "value" : 1084601.0
                  }
                },
                {
                  "key" : "WebBrowsing",
                  "doc_count" : 303,
                  "totalTrafficPerService" : {
                    "value" : 6236655.0
                  }
                }
              ]
            }
          },
          {
            "key" : "Miscellaneous",
            "doc_count" : 460,
            "totalTrafficBucket" : {
              "value" : 3717128.0
            },
            "services" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [
                {
                  "key" : "Miscellaneous",
                  "doc_count" : 460,
                  "totalTrafficPerService" : {
                    "value" : 3717128.0
                  }
                }
              ]
            }
          },
          {
            "key" : "Instant Messaging",
            "doc_count" : 453,
            "totalTrafficBucket" : {
              "value" : 1687002.0
            },
            "services" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [
                {
                  "key" : "RealTimeCommunication",
                  "doc_count" : 142,
                  "totalTrafficPerService" : {
                    "value" : 173620.0
                  }
                },
                {
                  "key" : "Whatsapp",
                  "doc_count" : 137,
                  "totalTrafficPerService" : {
                    "value" : 47667.0
                  }
                },
                {
                  "key" : "Skype",
                  "doc_count" : 93,
                  "totalTrafficPerService" : {
                    "value" : 504829.0
                  }
                },
                {
                  "key" : "Telegram",
                  "doc_count" : 49,
                  "totalTrafficPerService" : {
                    "value" : 4884.0
                  }
                },
                {
                  "key" : "Facebook",
                  "doc_count" : 31,
                  "totalTrafficPerService" : {
                    "value" : 955997.0
                  }
                },
                {
                  "key" : "Snapchat",
                  "doc_count" : 1,
                  "totalTrafficPerService" : {
                    "value" : 5.0
                  }
                }
              ]
            }
          },
          {
            "key" : "Social",
            "doc_count" : 446,
            "totalTrafficBucket" : {
              "value" : 5979175.0
            },
            "services" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [
                {
                  "key" : "SocialNetworking",
                  "doc_count" : 128,
                  "totalTrafficPerService" : {
                    "value" : 527260.0
                  }
                },
                {
                  "key" : "Facebook",
                  "doc_count" : 102,
                  "totalTrafficPerService" : {
                    "value" : 2358776.0
                  }
                },
                {
                  "key" : "Instagram",
                  "doc_count" : 88,
                  "totalTrafficPerService" : {
                    "value" : 101615.0
                  }
                },
                {
                  "key" : "Instagram_Video",
                  "doc_count" : 71,
                  "totalTrafficPerService" : {
                    "value" : 2980255.0
                  }
                },
                {
                  "key" : "Twitter",
                  "doc_count" : 57,
                  "totalTrafficPerService" : {
                    "value" : 11269.0
                  }
                }
              ]
            }
          },
          {
            "key" : "Video",
            "doc_count" : 236,
            "totalTrafficBucket" : {
              "value" : 4217337.0
            },
            "services" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [
                {
                  "key" : "Youtube",
                  "doc_count" : 155,
                  "totalTrafficPerService" : {
                    "value" : 440612.0
                  }
                },
                {
                  "key" : "RealTimeEntertainment",
                  "doc_count" : 68,
                  "totalTrafficPerService" : {
                    "value" : 3762405.0
                  }
                },
                {
                  "key" : "TikTok",
                  "doc_count" : 11,
                  "totalTrafficPerService" : {
                    "value" : 4603.0
                  }
                },
                {
                  "key" : "Twitch",
                  "doc_count" : 2,
                  "totalTrafficPerService" : {
                    "value" : 9717.0
                  }
                }
              ]
            }
          },
          {
            "key" : "Bulk Transfer",
            "doc_count" : 205,
            "totalTrafficBucket" : {
              "value" : 713584.0
            },
            "services" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [
                {
                  "key" : "Google_Play",
                  "doc_count" : 147,
                  "totalTrafficPerService" : {
                    "value" : 288810.0
                  }
                },
                {
                  "key" : "BulkTransfer",
                  "doc_count" : 41,
                  "totalTrafficPerService" : {
                    "value" : 36961.0
                  }
                },
                {
                  "key" : "Windows_Update",
                  "doc_count" : 17,
                  "totalTrafficPerService" : {
                    "value" : 387813.0
                  }
                }
              ]
            }
          },
          {
            "key" : "Network Storage",
            "doc_count" : 203,
            "totalTrafficBucket" : {
              "value" : 78447.0
            },
            "services" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [
                {
                  "key" : "NetworkStorage",
                  "doc_count" : 138,
                  "totalTrafficPerService" : {
                    "value" : 69595.0
                  }
                },
                {
                  "key" : "Google_Cloud_Storage",
                  "doc_count" : 35,
                  "totalTrafficPerService" : {
                    "value" : 1893.0
                  }
                },
                {
                  "key" : "iCloud",
                  "doc_count" : 30,
                  "totalTrafficPerService" : {
                    "value" : 6959.0
                  }
                }
              ]
            }
          },
          {
            "key" : "Tunneling",
            "doc_count" : 199,
            "totalTrafficBucket" : {
              "value" : 32540.0
            },
            "services" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [
                {
                  "key" : "Tunneling",
                  "doc_count" : 199,
                  "totalTrafficPerService" : {
                    "value" : 32540.0
                  }
                }
              ]
            }
          },
          {
            "key" : "Musica",
            "doc_count" : 167,
            "totalTrafficBucket" : {
              "value" : 15448.0
            },
            "services" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [
                {
                  "key" : "Spotify",
                  "doc_count" : 156,
                  "totalTrafficPerService" : {
                    "value" : 15310.0
                  }
                },
                {
                  "key" : "Amazon_Music",
                  "doc_count" : 11,
                  "totalTrafficPerService" : {
                    "value" : 138.0
                  }
                }
              ]
            }
          },
          {
            "key" : "Email",
            "doc_count" : 118,
            "totalTrafficBucket" : {
              "value" : 599771.0
            },
            "services" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [
                {
                  "key" : "Email",
                  "doc_count" : 118,
                  "totalTrafficPerService" : {
                    "value" : 599771.0
                  }
                }
              ]
            }
          },
          {
            "key" : "Video-Pagamento",
            "doc_count" : 101,
            "totalTrafficBucket" : {
              "value" : 5434375.0
            },
            "services" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [
                {
                  "key" : "Netflix",
                  "doc_count" : 54,
                  "totalTrafficPerService" : {
                    "value" : 26231.0
                  }
                },
                {
                  "key" : "Amazon_Video",
                  "doc_count" : 19,
                  "totalTrafficPerService" : {
                    "value" : 4093533.0
                  }
                },
                {
                  "key" : "Other_Video-Pagamento",
                  "doc_count" : 19,
                  "totalTrafficPerService" : {
                    "value" : 793.0
                  }
                },
                {
                  "key" : "DAZN",
                  "doc_count" : 6,
                  "totalTrafficPerService" : {
                    "value" : 1221723.0
                  }
                },
                {
                  "key" : "Sky",
                  "doc_count" : 3,
                  "totalTrafficPerService" : {
                    "value" : 92095.0
                  }
                }
              ]
            }
          },
          {
            "key" : "Gaming + Games Download",
            "doc_count" : 28,
            "totalTrafficBucket" : {
              "value" : 6.1502021E7
            },
            "services" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [
                {
                  "key" : "Gaming",
                  "doc_count" : 22,
                  "totalTrafficPerService" : {
                    "value" : 640861.0
                  }
                },
                {
                  "key" : "PS_Games_Download",
                  "doc_count" : 5,
                  "totalTrafficPerService" : {
                    "value" : 6.0861083E7
                  }
                },
                {
                  "key" : "Other_GamesDownload",
                  "doc_count" : 1,
                  "totalTrafficPerService" : {
                    "value" : 77.0
                  }
                }
              ]
            }
          }
        ]
      }
    }
  }
}

What is the exact query for this?

What is the output of:

GET /
GET /_cat/nodes?v
GET /_cat/health?v
GET /_cat/indices?v

If some outputs are too big, please share them on gist.github.com and link them here.

Also, have a look at this great blog post about Slow Queries. That might help.

{
  "name" : "mdn-dn302ela",
  "cluster_name" : "mycluster",
  "cluster_uuid" : "BtmehvuxQXuhs0WsFA-NBA",
  "version" : {
    "number" : "7.9.3",
    "build_flavor" : "default",
    "build_type" : "tar",
    "build_hash" : "c4138e51121ef06a6404866cddc601906fe5c868",
    "build_date" : "2020-10-16T10:36:16.141335Z",
    "build_snapshot" : false,
    "lucene_version" : "8.6.2",
    "minimum_wire_compatibility_version" : "6.8.0",
    "minimum_index_compatibility_version" : "6.0.0-beta1"
  },
  "tagline" : "You Know, for Search"
}

ip             heap.percent ram.percent cpu load_1m load_5m load_15m node.role master name
x.x.x.x           53          94   0    0.07    0.04     0.05 dimrt     *      mdn-dn302ela

epoch      timestamp cluster            status node.total node.data shards pri relo init unassign pending_tasks max_task_wait_time active_shards_percent
1634223408 14:56:48  mycluster          yellow          1         1     71  71    0    0       20             0                  -                 78.0%
health status index                           uuid                   pri rep docs.count docs.deleted store.size pri.store.size
green  open   .triggered_watches              VC0TH13LQ66CNZtd91V9CA   1   0          0       749832     52.5mb         52.5mb
green  open   copy.job                        gKlC06K3TR6JHTlUhwKUzA   1   0      71765            0     30.3mb         30.3mb
green  open   .temp.account                   S1DCxwr2QQqsXoUyxFjPoQ   1   0     109826        20576     29.9mb         29.9mb
yellow open   profile-history                 Ug-nW--ZTvqEUMdu_hRfwQ   1   1     418723         1830     84.5mb         84.5mb
green  open   .kibana-event-log-7.9.3-000009  7I98ddoDSmaGIvf5Oj2Xcg   1   0          0            0       208b           208b
green  open   .temp.profiles                  G7VqFn45TMewVxX5KJsiIg   1   0    4628133      1169577    256.9mb        256.9mb
yellow open   degradation                     gy3STaonS-Oa6T9oqAfpYA   1   1          0            0       208b           208b
yellow open   dsp_retention_statistic         7-9CUnUUSwiZOPYXCssrjQ   1   1          1            0     12.5kb         12.5kb
green  open   copy.account                    kQna0lnCQbiDxdpzajO1FA   1   0     109829        31640     29.4mb         29.4mb
green  open   .apm-custom-link                6so8Oe9IT1uOy81XxjTeZA   1   0          0            0       208b           208b
green  open   copy.invitation                 _o1aZ9gwT-murfXStz_oDw   1   0        250            0    131.4kb        131.4kb
green  open   .kibana_task_manager_1          JuvrJWfWRE2jWFiOPST4Ig   1   0          6        25666     46.7mb         46.7mb
green  open   copy.statistic                  zuxmCxj0Q7St05DK3PSKUA   1   0   41116057     13545314      7.7gb          7.7gb
yellow open   dsp_retention_parental_control  gCxDIoVJQ-mwW1KfFGaOtw   1   1          1            0     12.7kb         12.7kb
green  open   statistic                       CPVk7mooTJC76eCQgj9lng   1   0  213544561      2771301     50.4gb         50.4gb
green  open   .monitoring-kibana-7-2021.10.09 puwF3aeCTc-f1l-rgxPlMg   1   0      17280            0      2.7mb          2.7mb
green  open   .monitoring-kibana-7-2021.10.08 j7fv9n1uSPaUdlCO_AmRTw   1   0      15602            0      2.5mb          2.5mb
yellow open   invitation                      CPOAUB8nQa6J4LdTp-a7uw   1   1        301           41    224.9kb        224.9kb
green  open   .apm-agent-configuration        afNe41z8TWiSXcLqmKLo3g   1   0          0            0       208b           208b
yellow open   profiles                        rTWVl_xMSOOMd_jLtm5gsQ   1   1   11758986      1250188    693.6mb        693.6mb
yellow open   account_temp                    HmP4m3owRRyr6kvr5YWfSw   1   1         24            0     21.1kb         21.1kb
green  open   .tasks                          A7qHZllgTxiSpi4-AGBLNA   1   0         38            0     72.2kb         72.2kb
green  open   copy.cpes                       F3BRafcdRUSN6mDBf9VnlA   1   0      55027            0      101mb          101mb
yellow open   profile-parental-control        n9GhZf8lRCmoafiAC6-5Iw   1   1      21425         7412      3.8mb          3.8mb
yellow open   digital-wellness                H4Y52mJVQYGTQVhY78sWDg   1   1    2160186          181    396.8mb        396.8mb
green  open   copy.profile-history            RIdDM0a-SJWU-YQhWJ__nQ   1   0      76100         7274     18.3mb         18.3mb
green  open   .monitoring-kibana-7-2021.10.14 FEkfDiMlSfergxn4HYFrsw   1   0      10776            0      1.7mb          1.7mb
green  open   .monitoring-kibana-7-2021.10.13 fE_7TnSlTmiZCFJgBClBRA   1   0      17278            0      2.9mb          2.9mb
green  open   .monitoring-kibana-7-2021.10.12 9DMX1cX4SKKmMCG9wfU5HA   1   0      17280            0      2.8mb          2.8mb
yellow open   job                             pWrpFjbwR7214kXQEv4uDQ   1   1     372921            9    151.4mb        151.4mb
yellow open   account_back                    N4yQ5o3PTnqEEHW-r2odcg   1   1          4            0       19kb           19kb
green  open   .monitoring-kibana-7-2021.10.11 fGn0dAppTFSxBKqZBFbCow   1   0      17278            0      2.8mb          2.8mb
green  open   .monitoring-kibana-7-2021.10.10 zv-FNzUNRz6DRtB-D67o0g   1   0      17278            0      2.8mb          2.8mb
yellow open   account_temp_30_08              0eh6BDaHT62tyGGHF7kBBw   1   1     109822            0     22.8mb         22.8mb
green  open   copy.profile-parental-control   yERpQ1YLTamcUMgtoCd7gQ   1   0       3534            0    418.4kb        418.4kb
yellow open   dsp_retention_degradation       UivRA3WLRqit3b-rs9pNSg   1   1          1            0     12.6kb         12.6kb
yellow open   statistic_temp                  ul3S7RlLRxqx52BVjyYy8Q   1   1    3125058            0    485.3mb        485.3mb
green  open   copy.profiles                   02PEydEgSGaaNzyEimVUng   1   0    4687531      1310425    233.7mb        233.7mb
yellow open   profile-history-temp            I7mLwtz-TqOG2MHAGuvJZw   1   1      92514            0     19.4mb         19.4mb
yellow open   csp_contact                     SY57_k_WTBWcFHvHK_waZg   1   1        510            8    139.5kb        139.5kb
yellow open   cpes_backup                     Gue_ZZuXTdaxjI_eo9WsRw   1   1         34            0    108.9kb        108.9kb
yellow open   account_backup                  dZdxjv30SK2IDmTeM_W5yg   1   1         24            0     40.5kb         40.5kb
green  open   .monitoring-alerts-7            43_OPacHRM6Tmcq7v4b7-Q   1   0          2         8952      6.8mb          6.8mb
green  open   .watches                        UfVZ39fXSZ-gHHlHKszkUA   1   0          6        53718      168mb          168mb
green  open   .kibana-event-log-7.9.3-000012  3b5o4ffFRradkxljUZtXPA   1   0          0            0       208b           208b
green  open   .kibana-event-log-7.9.3-000011  fR4cSlNuTj2puxyoK7uXEw   1   0          0            0       208b           208b
green  open   .kibana-event-log-7.9.3-000010  UQpCXAQJQyaipS3RWct2Rg   1   0          0            0       208b           208b
green  open   .monitoring-es-7-2021.10.10     DnWvz81UQWGgeJ4fS0rAGA   1   0     562905       165792    270.4mb        270.4mb
green  open   .monitoring-es-7-2021.10.12     Sl5kmVTPTS6qC0UiZdArmA   1   0     563179       154154    277.8mb        277.8mb
green  open   .monitoring-es-7-2021.10.11     5jUDmTIPSGW0l4_AnW3EVg   1   0     562904       254512    280.3mb        280.3mb
green  open   .kibana_1                       2V7NJyrQRC24XKnFMI2i2g   1   0        234           28     10.4mb         10.4mb
green  open   .monitoring-es-7-2021.10.14     0Aq1fa2NSnC15msd-wUbfA   1   0     351496       169351    312.8mb        312.8mb
green  open   .monitoring-es-7-2021.10.13     f637CaRATfuGaQgRzzSMBA   1   0     563267       143507    281.5mb        281.5mb
green  open   .monitoring-es-7-2021.10.09     ulXmpLewSj-ss9ubiLPQjg   1   0     562842       165408    270.7mb        270.7mb
yellow open   cpes                            FKi4AVifTem32uOrpJUchA   1   1     129424           28    247.6mb        247.6mb
green  open   .monitoring-es-7-2021.10.08     v-KX4y9dT1Wnr7c-owID5Q   1   0     508366       159744    249.7mb        249.7mb
green  open   copy.digital-wellness           fzItPq8_SNC4dZ0uVOKBrQ   1   0     902050        12308    140.8mb        140.8mb
yellow open   account                         a0bB7eOwRJmiiK9A26qV3A   1   1     257782           24     52.4mb         52.4mb

Some comments:

Please upgrade. So many things changed since that version. 7.15 is the last one.

What's the HEAP size of your node?

I guess that the index you are trying to query is statistic, right?
It's one single shard with around 50gb of data. May be you should try to split it in 3 or 4 shards?

We try to increase shards but performance are worst

Could you answer the other questions?

HEAP
heap.current heap.percent heap.max
7.9gb 49 16gb

yes it is statistic and actually we have 8 shards

But

This says one shard. What is the exact status?

green  open   statistic                       CPVk7mooTJC76eCQgj9lng   1   0  213544561      2771301     50.4gb         50.4gb

Also, could you answer to this question?

What kind of hardware do you have?

SORRY 1 SHARDS.
I do not know anything about hw. Sorry

May be you are using slow spinning disks?

May be check with the Profile API what is happening? See Profile API | Elasticsearch Guide [7.15] | Elastic