一次Elasticsearch的问题分析

薛晓刚

于 2023-10-07 16:41:40 发布

阅读量224

点赞数

文章标签： elasticsearch 数据库

本文链接：https://blog.csdn.net/xuexiaogang/article/details/133645685

版权

在工作中经历过不少使用es的场景。es作为搜索引擎数据库用作全文搜索是不错的，但是真正用到es的不多，不少场景可能用MySQL、PG甚至Oracle都可以很好的完成，只是缺乏专人的设计和把控。不过我的ES水平也有限，只能处理简单的场景。这里要是有不对的，请指出一下。今天就是分享一个故事。

es数据库又名全文索引，它的索引其实就是传统数据库上的表。至于es的原理今天不说了。就说问题的分析和定位以及重现。

现象是CPU高负荷。其实任何一个数据库的CPU高消耗，去查查SQL都能差不多定位。ES也是一样，它的命令虽然不能叫做SQL，但是也可以这样叫。因为现在的ES似乎是从6开始是支持的SQL。

即可以像下面这样查询：

root@xue-test1-148154:[/root]elasticsearch-sql-cli http://10.60.148.154:9200 （使用支持SQL的工具elasticsearch-sql-cli登录）

.sssssss.` .sssssss.
.:sXXXXXXXXXXo` `ohXXXXXXXXXho.
.yXXXXXXXXXXXXXXo` `oXXXXXXXXXXXXXXX-
.XXXXXXXXXXXXXXXXXXo` `oXXXXXXXXXXXXXXXXXX.
.XXXXXXXXXXXXXXXXXXXXo. .oXXXXXXXXXXXXXXXXXXXXh
.XXXXXXXXXXXXXXXXXXXXXXo``oXXXXXXXXXXXXXXXXXXXXXXy
`yXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.
`oXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
`oXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
`oXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
`oXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
`oXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
.XXXXXXXXXXXXXXXXXXXXXXXXXo`
.oXXXXXXXXXXXXXXXXXXXXXXXXo`
`oXXXXXXXXXXXXXXXXXXXXXXXXo` `odo`
`oXXXXXXXXXXXXXXXXXXXXXXXXo` `oXXXXXo`
`oXXXXXXXXXXXXXXXXXXXXXXXXo` `oXXXXXXXXXo`
`oXXXXXXXXXXXXXXXXXXXXXXXXo` `oXXXXXXXXXXXXXo`
`yXXXXXXXXXXXXXXXXXXXXXXXo` oXXXXXXXXXXXXXXXXX.
.XXXXXXXXXXXXXXXXXXXXXXo` `oXXXXXXXXXXXXXXXXXXXy
.XXXXXXXXXXXXXXXXXXXXo` /XXXXXXXXXXXXXXXXXXXXX
.XXXXXXXXXXXXXXXXXXo` `oXXXXXXXXXXXXXXXXXX-
-XXXXXXXXXXXXXXXo` `oXXXXXXXXXXXXXXXo`
.oXXXXXXXXXXXo` `oXXXXXXXXXXXo.
`.sshXXyso` SQL `.sshXhss.`

进来以后就像MySQL一样的使用。

然后在慢日志中看到

[2023-10-07T15:22:39,449][WARN ][index.search.slowlog.query] [xue-es1] [x2][4] took[80.5ms], took_millis[80], total_hits[0], types[], stats[], search_type[QUERY_THEN_FETCH], total_shards[5], source[{"size":5,"timeout":"45000ms","quey":{"query_string":{"query":"*T30072*","fields":["message^1.0"],"type":"best_fields","default_operator":"or","max_determinized_states":10000,"enable_position_increments":true,"fuzziness":"AUTO","fuzzy_prefix_length":0,"fuzzy_max_expnsions":50,"phrase_slop":0,"escape":false,"auto_generate_synonyms_phrase_query":true,"fuzzy_transpositions":true,"boost":1.0}},"_source":{"includes":["@version","host","message","path","type"],"excludes":[]},"docvalue_fields":["@timstamp"],"sort":[{"_doc":{"order":"asc"}}]}],

这里的size 5是返回5行。limit 5的意思。*T30072* 是前后%的意思。不少开发人员使用es觉得快，其实这样写谁都不会快。只是MySQL一个数据库给了8C 24G。而一套es数据库3个节点起，CPU和内存给的毫不吝啬。甚至是远大于实际数据的。由于都在内存中且比MySQL多几十倍的CPU（N个节点总和）和内存（N个节点总和）感觉就是比关系型数据库快啊。因为条件不对等。

下面的的慢日志可以看到 order 的desc ，因为排序了。所以这个慢日志还是很详细的。
[2023-10-07T15:24:33,285][WARN ][index.search.slowlog.query] [xue-es1] [x2][3] took[62.2ms], took_millis[62], total_hits[2], types[], stats[], search_type[QUERY_THEN_FETCH], total_shards[5], source[{"size":5,"timeout":"45000ms","quey":{"query_string":{"query":"*T30072*","fields":["message^1.0"],"type":"best_fields","default_operator":"or","max_determinized_states":10000,"enable_position_increments":true,"fuzziness":"AUTO","fuzzy_prefix_length":0,"fuzzy_max_expnsions":50,"phrase_slop":0,"escape":false,"auto_generate_synonyms_phrase_query":true,"fuzzy_transpositions":true,"boost":1.0}},"_source":{"includes":["@version","host","message","path","type"],"excludes":[]},"docvalue_fields":["@timstamp"],"sort":[{"message.keyword":{"order":"desc"}}]}],

[2023-10-07T15:24:33,299][WARN ][index.search.slowlog.fetch] [xue-es1] [x2][3] took[12.1ms], took_millis[12], total_hits[2], types[], stats[], search_type[QUERY_THEN_FETCH], total_shards[5], source[{"size":5,"timeout":"45000ms","quey":{"query_string":{"query":"*T30072*","fields":["message^1.0"],"type":"best_fields","default_operator":"or","max_determinized_states":10000,"enable_position_increments":true,"fuzziness":"AUTO","fuzzy_prefix_length":0,"fuzzy_max_expnsions":50,"phrase_slop":0,"escape":false,"auto_generate_synonyms_phrase_query":true,"fuzzy_transpositions":true,"boost":1.0}},"_source":{"includes":["@version","host","message","path","type"],"excludes":[]},"docvalue_fields":["@timstamp"],"sort":[{"message.keyword":{"order":"desc"}}]}],

而打开慢日志是这样的。