# ES-Hadoop 5.1.1 performance
**URL:** https://discuss.elastic.co/t/es-hadoop-5-1-1-performance/71492
**Category:** Elasticsearch
**Tags:** es-hadoop
**Created:** [January 13, 2017, 9:04am UTC](https://discuss.elastic.co/t/es-hadoop-5-1-1-performance/71492 "2017-01-13T09:04:03Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@Tommaso\_Anzidei](https://discuss.elastic.co/u/Tommaso_Anzidei)
#### Post date: [January 13, 2017, 9:04am UTC](https://discuss.elastic.co/t/es-hadoop-5-1-1-performance/71492/1 "2017-01-13T09:04:03Z")
Hello,
we upgraded Elasticsearch to 5.1.1 from 2.1. After that upgrade we noticed a loss of performance in running hive queries against an external table linked to an Elasticsearch index. The same query used to take 30 minutes before the upgrade and now runs in more than 2 hours. Nothing was changed apart of upgrading. Is there any suggestion?
Thanks a lot
Tommaso
---
### Author:  [@james.baiera](https://discuss.elastic.co/u/james.baiera)
#### Post date: [January 16, 2017, 10:33pm UTC](https://discuss.elastic.co/t/es-hadoop-5-1-1-performance/71492/2 "2017-01-16T22:33:31Z")
Could you please include information on the query that you are executing, as well as the mappings of the data in elasticsearch, and any other profiling information that you have collected?
---
### Author:  [@Tommaso\_Anzidei](https://discuss.elastic.co/u/Tommaso_Anzidei)
#### Post date: [January 17, 2017, 8:47am UTC](https://discuss.elastic.co/t/es-hadoop-5-1-1-performance/71492/3 "2017-01-17T08:47:39Z")
Hi James,
thanks a lot for your suggestions. Our mappings are:
{
"syslogfe" : {
"mappings" : {
"syslog" : {
"properties" : {
"@timestamp" : {
"type" : "date",
"format" : "strict\_date\_optional\_time||epoch\_millis"
},
"@version" : {
"type" : "string"
},
"build" : {
"type" : "string"
},
"connection-status" : {
"type" : "string"
},
"device" : {
"type" : "string"
},
"geoip" : {
"properties" : {
"area\_code" : {
"type" : "long"
},
"city\_name" : {
"type" : "string"
},
"continent\_code" : {
"type" : "string"
},
"coordinates" : {
"type" : "geo\_point",
"geohash" : true,
"geohash\_prefix" : true
},
"country\_code2" : {
"type" : "string"
},
"country\_code3" : {
"type" : "string"
},
"country\_name" : {
"type" : "string"
},
"dma\_code" : {
"type" : "long"
},
"ip" : {
"type" : "string"
},
"latitude" : {
"type" : "double"
},
"location" : {
"type" : "double"
},
"longitude" : {
"type" : "double"
},
"postal\_code" : {
"type" : "string"
},
"real\_region\_name" : {
"type" : "string"
},
"region\_code" : {
"type" : "string",
"fields" : {
"keyword" : {
"type" : "string",
"index" : "not\_analyzed",
"ignore\_above" : 256,
"fielddata" : false
}
},
"fielddata" : false
},
"region\_name" : {
"type" : "string"
},
"timezone" : {
"type" : "string"
}
}
},
"host" : {
"type" : "string"
},
"http-action" : {
"type" : "string"
},
"http-status" : {
"type" : "string"
},
"language" : {
"type" : "string"
},
"local-ip" : {
"type" : "string"
},
"major" : {
"type" : "string"
},
"message" : {
"type" : "string"
},
"minor" : {
"type" : "string"
},
"name" : {
"type" : "string"
},
"os" : {
"type" : "string"
},
"os\_major" : {
"type" : "string"
},
"os\_minor" : {
"type" : "string"
},
"os\_name" : {
"type" : "string"
},
"patch" : {
"type" : "string"
},
"port" : {
"type" : "long"
},
"query-string" : {
"type" : "string"
},
"referer" : {
"type" : "string"
},
"remote-ip" : {
"type" : "string"
},
"request-bytes" : {
"type" : "string"
},
"response-bytes" : {
"type" : "string"
},
"secure-cipher" : {
"type" : "string"
},
"secure-protocol" : {
"type" : "string"
},
"secure-session" : {
"type" : "string"
},
"secure-user" : {
"type" : "string"
},
"tags" : {
"type" : "string"
},
"time" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss"
},
"time-to-serve-us" : {
"type" : "string"
},
"timestamp" : {
"type" : "string"
},
"type" : {
"type" : "string"
},
"url" : {
"type" : "string",
"index" : "not\_analyzed",
"fielddata" : false
},
"user-agent" : {
"type" : "string"
}
}
}
}
}
}
which we map to an external hive table with:
CREATE EXTERNAL TABLE EXT\_LOGSIS\_PRD
(
`event_time` string,
`secure-user` string,
`url` string,
`query-string` string,
`http-status` string,
`city_name` string,
`postal_code` string,
`real_region_name` string
)
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES
(
'es.resource' = 'syslogfe/syslog',
'es.mapping.date.rich' = 'false',
'es.nodes' = 'http://:9200',
'es.nodes.wan.only' = 'true',
'es.mapping.names' ='
event\_time:time,
city\_name:geoip.city\_name,
postal\_code:geoip.postal\_code,
real\_region\_name:geoip.real\_region\_name
'
);
and then we query that external table with:
CREATE TABLE LST\_LOGSIS\_PRD AS
SELECT
\*
FROM EXT\_LOGSIS\_PRD
WHERE
YEAR(TO\_DATE(`event_time`))= 2016
AND MONTH(TO\_DATE(`event_time`))= 12
AND `http-status` IN ('200','304')
This last query used to run in around 30 minutes and now takes 4 hours to complete.
Anyway we now suppose that the update to ELK5 changed something in the config files of Elasticsearch (namely the use of JVM resources) and we are investigating it. So we are confident that the new ES-Hadoop jar is not responsible for that problem. Again thanks, the Elasticsearch community is really a supportive one!
Tommaso
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [February 14, 2017, 8:48am UTC](https://discuss.elastic.co/t/es-hadoop-5-1-1-performance/71492/4 "2017-02-14T08:48:09Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.