<\/span><\/h3>\n\n\n\nIntervene in the execution result width evaluation of group_concat, for example, by wrapping it with substr.<\/p>\n\n\n\n
substr(group_concat(xxx),0,1000)<\/code><\/pre>\n\n\n\nThis way, the resulting width will not exceed 1000 characters, and the column type for the intermediate table will be varchar(1000).<\/p>\n\n\n\n
The proposed solution was implemented on-site and completed in under 30 seconds.<\/strong><\/p>\n\n\n\n<\/span>Use hint<\/span><\/h3>\n\n\n\ngbase 8a can dynamically adjust session-level parameter values through hints, for example<\/p>\n\n\n\n
select \/*+group_concat_max_len(3000)*\/ ...<\/code><\/pre>\n\n\n\nThis hint specifies that the maximum `group_concat` width for this SQL will not exceed 3000 bytes, which is equivalent to 1000 UTF-8 characters.<\/p>\n\n\n\n
<\/span>Upgrade to version 95<\/span><\/h3>\n\n\n\nIn version 95, memory allocation is not based on the maximum width of fields. Instead, a block of memory is allocated based on the current data, and if it's insufficient, more memory is continuously allocated. This reduces the actual memory footprint, and the corresponding intermediate result sets more closely resemble actual disk usage.<\/p>\n\n\n\n
<\/span>Summary<\/span><\/h2>\n\n\n\nDue to parameter adjustments, the database memory evaluation was large, writing a large number of intermediate disk files for sorting. This caused the node's IO resources to be continuously busy, leading to a performance degradation of other normal SQL operations.<\/p>\n\n\n\n
At the same time, one parameter cannot adapt to all scenarios, and some important business SQLs can use hints to set their own suitable parameters.<\/p>","protected":false},"excerpt":{"rendered":"
This article introduces a case where parameter adjustments made for business needs led to performance anomalies in other business areas. It includes the investigation and analysis processes.<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[28],"class_list":["post-13366","post","type-post","status-publish","format-standard","hentry","category-gbase8a","tag-28"],"_links":{"self":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/13366","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/comments?post=13366"}],"version-history":[{"count":19,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/13366\/revisions"}],"predecessor-version":[{"id":13402,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/13366\/revisions\/13402"}],"wp:attachment":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/media?parent=13366"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/categories?post=13366"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/tags?post=13366"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}