GreatSQL社区

搜索

[已解决] 后端连接greatsql查询,程序卡了一晚上,greatsql自动重启

274 8 2024-9-12 10:01
本帖最后由 驭无殇1998 于 2024-9-12 10:44 编辑

操作系统:centos7.6
数据库版本:greatsql 8.0.32-25
架构:单机
规格:8核16G
greatsql配置文件:

[client]
user = mysql
socket  = /data/greatsql/run/mysql.sock

[mysql]
loose-skip-binary-as-hex
prompt = "(\\D)[\\u@GreatSQL][\\d]>"
no-auto-rehash

[mysqld]
user    = mysql
port    = 3306
#主从复制或MGR集群中,server_id记得要不同
#另外,实例启动时会生成 auto.cnf,里面的 server_uuid 值也要不同
#server_uuid的值还可以自己手动指定,只要符合uuid的格式标准就可以
server_id = 3306
basedir = /usr/local/greatsql
datadir = /data/greatsql/data
socket  = /data/greatsql/run/mysql.sock
pid-file = /data/greatsql/run/mysql.pid
character-set-server = UTF8MB4
skip_name_resolve = 1
#若你的MySQL数据库主要运行在境外,请务必根据实际情况调整本参数
default_time_zone = "+8:00"
bind_address = "0.0.0.0"

#performance setttings
lock_wait_timeout = 3600
open_files_limit    = 65535
back_log = 1024
max_connections = 2000
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
thread_stack = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
max_allowed_packet = 64M
net_buffer_shrink_interval = 180
#GIPK
loose-sql_generate_invisible_primary_key = ON

#开启general日志
general_log = off
general_log_file = /data/greatsql/logs/audit.log
log_timestamps = SYSTEM
log_error = /data/greatsql/logs/error.log
log_error_verbosity = 3
slow_query_log = 1
log_slow_extra = 1
slow_query_log_file = /data/greatsql/logs/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_bin = /data/greatsql/logs/binlog
log-bin-index= /data/greatsql/logs/binlog.index
binlog_format = ROW
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
#控制binlog总大小,避免磁盘空间被撑爆
binlog_space_limit = 50G
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
#MySQL 8.0.22前,想启用MGR的话,需要设置binlog_checksum=NONE才行
binlog_checksum = CRC32
gtid_mode = ON
enforce_gtid_consistency = TRUE

#myisam settings
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

#innodb settings
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_redo_log_capacity = 6G
innodb_max_undo_log_size = 4G
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话,可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话,则可以调整的更高,比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_open_files = 65535
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_online_alter_log_max_size = 4G
innodb_print_ddl_logs = 0
innodb_status_file = 1
#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log_error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 1
innodb_sort_buffer_size = 67108864
innodb_adaptive_hash_index = 0
#开启NUMA支持
#innodb_numa_interleave = ON
#innodb_print_lock_wait_timeout_info = 1
#自动杀掉超过5分钟不活跃事务,避免行锁被长时间持有
kill_idle_transaction = 300
#异步清理大表
#innodb_data_file_async_purge = ON

#innodb monitor settings
innodb_monitor_enable = "module_innodb,module_server,module_dml,module_ddl,module_trx,module_os,module_purge,module_log,module_lock,module_buffer,module_index,module_ibuf_system,module_buffer_page,module_adaptive_hash"

#innodb parallel query
loose-force_parallel_execute = ON
loose-parallel_default_dop = 8
#设置系统中总的并行查询线程数,可以和最大逻辑CPU数量一样
loose-parallel_max_threads = 8
#并行执行时leader线程和worker线程使用的总内存大小上限,可以设置物理内存的5-10%左右
loose-parallel_memory_limit = 512M

#pfs settings
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'
performance_schema_session_connect_attrs_size = 4096


开发今早反馈数据连接报错:

Communications link failure

The last packet successfully received from the server was 344 milliseconds ago. The last packet sent successfully to the server was 344 milliseconds ago.
这个一看是使用了旧连接,遂查看数据库状态,发现数据库几秒前重启:


查看错误日志:

2024-09-12T09:41:02.841337+08:00 0 [System] [MY-010931] [Server] /usr/local/greatsql/bin/mysqld: ready for connections. Version: '8.0.32-25'  socket: '/data/greatsql/run/mysql.sock'  port: 3306  GreatSQL, Release 25, Revision 79f57097e3f.
2024-09-12T09:41:02.841594+08:00 5 [Note] [MY-010051] [Server] Event Scheduler: scheduler thread started with id 5
2024-09-12T09:41:02.841725+08:00 0 [Note] [MY-011240] [Server] Plugin mysqlx reported: 'Using SSL configuration from MySQL Server'
2024-09-12T09:41:02.842655+08:00 0 [Note] [MY-011243] [Server] Plugin mysqlx reported: 'Using OpenSSL for TLS connections'
2024-09-12T09:41:02.843208+08:00 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2024-09-12T09:41:02.843372+08:00 0 [Note] [MY-011946] [InnoDB] Buffer pool(s) load completed at 240912  9:41:02
2024-09-12T09:41:05.669741+08:00 957 [ERROR] [MY-013183] [InnoDB] Assertion failure: row0pread.cc:1350:is_queue_empty() thread 139872770459392
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/ ... nnodb-recovery.html
InnoDB: about forcing recovery.
2024-09-12T01:41:05Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=029072ec4fefcea616d05fa2631b046215e2e6dc

Build ID: 029072ec4fefcea616d02024-09-12T09:41:05.669742+08:00 956 [ERROR] [MY-013183] [InnoDB] Assertion failure: row0pread.cc:1350:is_queue_empty() thread 139872779384576
5fa2631b0462InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/ ... nnodb-recovery.html
InnoDB: about forcing recovery.
15e2e6dc
Server Version: 8.0.32-25 GreatSQL, Release 25, Revision 79f57097e3f

Thread pointer: 0x7f36c6f8ed58
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f36aacbeff0 thread_stack 0x80000
/usr/local/greatsql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x2322cdd]
/usr/local/greatsql/bin/mysqld(print_fatal_signal(int)+0x3cf) [0x135b0cf]
/usr/local/greatsql/bin/mysqld(my_server_abort()+0x7e) [0x135b27e]
/usr/local/greatsql/bin/mysqld(my_abort()+0xa) [0x231ca0a]
/usr/local/greatsql/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x31f) [0x25ea7ef]
/usr/local/greatsql/bin/mysqld(Parallel_reader::dispatch_ctx(row_prebuilt_t*)+0x405) [0x25603f5]
/usr/local/greatsql/bin/mysqld(ha_innobase::pq_worker_scan_next(void*, unsigned char*)+0x12b) [0x24488cb]
/usr/local/greatsql/bin/mysqld(handler::ha_pq_next(unsigned char*, void*)+0x270) [0xdcdfe0]
/usr/local/greatsql/bin/mysqld(PQblockScanIterator::Read()+0x25) [0x102c645]
/usr/local/greatsql/bin/mysqld(FilterIterator::Read()+0x14) [0x14d5124]
/usr/local/greatsql/bin/mysqld(LimitOffsetIterator::Read()+0x12d) [0x14d54bd]
/usr/local/greatsql/bin/mysqld(Query_expression::ExecuteIteratorQuery(THD*)+0xa72) [0x12a45b2]
/usr/local/greatsql/bin/mysqld(pq_worker_exec(void*)+0x90) [0x1170990]
/usr/local/greatsql/bin/mysqld() [0x27f8905]
/usr/lib64/libpthread.so.0(+0x7ea5) [0x7f3968641ea5]
/usr/lib64/libc.so.6(clone+0x6d) [0x7f3966c2296d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f36f843c030): SELECT  id,user_code,user_type,user_name,user_name1,user_name2,status,login_name,password,super_admin,login_chk,other_flags,ipaddr_lim,login_time_lim,tested_pwd_times,mmids,cbm_id,limb_code,be_date,le_date,country_code,mobile,email,cbm_admin,user_category,muteb_date,mutee_date,real_flags,high_flags,reg_sys_code,reg_channel,id_number,id_type,country,gender,birth_date,image_path,full_name,password_update_time,cbm_code,creator,create_time,updater,update_time  FROM ua_users      WHERE  (user_code = 'UCUS23032200001') LIMIT 1
Connection ID (thread ID): 957
Status: NOT_KILLED

Please help us make Percona Server better by reporting any
bugs at https://bugs.percona.com/

You may download the Percona Server operations manual by visiting
http://www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.
2024-09-12T01:41:05Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=029072ec4fefcea616d05fa2631b046215e2e6dc

Build ID: 029072ec4fefcea616d05fa2631b046215e2e6dc
Server Version: 8.0.32-25 GreatSQL, Release 25, Revision 79f57097e3f

Thread pointer: 0x7f36c6f8b070
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f36ab541ff0 thread_stack 0x80000
/usr/local/greatsql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x2322cdd]
/usr/local/greatsql/bin/mysqld(print_fatal_signal(int)+0x3cf) [0x135b0cf]
/usr/local/greatsql/bin/mysqld(my_server_abort()+0x7e) [0x135b27e]
/usr/local/greatsql/bin/mysqld(my_abort()+0xa) [0x231ca0a]
/usr/local/greatsql/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x31f) [0x25ea7ef]
/usr/local/greatsql/bin/mysqld(Parallel_reader::dispatch_ctx(row_prebuilt_t*)+0x405) [0x25603f5]
/usr/local/greatsql/bin/mysqld(ha_innobase::pq_worker_scan_next(void*, unsigned char*)+0x12b) [0x24488cb]
/usr/local/greatsql/bin/mysqld(handler::ha_pq_next(unsigned char*, void*)+0x270) [0xdcdfe0]
/usr/local/greatsql/bin/mysqld(PQblockScanIterator::Read()+0x25) [0x102c645]
/usr/local/greatsql/bin/mysqld(FilterIterator::Read()+0x14) [0x14d5124]
/usr/local/greatsql/bin/mysqld(LimitOffsetIterator::Read()+0x12d) [0x14d54bd]
/usr/local/greatsql/bin/mysqld(Query_expression::ExecuteIteratorQuery(THD*)+0xa72) [0x12a45b2]
/usr/local/greatsql/bin/mysqld(pq_worker_exec(void*)+0x90) [0x1170990]
/usr/local/greatsql/bin/mysqld() [0x27f8905]
/usr/lib64/libpthread.so.0(+0x7ea5) [0x7f3968641ea5]
/usr/lib64/libc.so.6(clone+0x6d) [0x7f3966c2296d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f36f843c030): SELECT  id,user_code,user_type,user_name,user_name1,user_name2,status,login_name,password,super_admin,login_chk,other_flags,ipaddr_lim,login_time_lim,tested_pwd_times,mmids,cbm_id,limb_code,be_date,le_date,country_code,mobile,email,cbm_admin,user_category,muteb_date,mutee_date,real_flags,high_flags,reg_sys_code,reg_channel,id_number,id_type,country,gender,birth_date,image_path,full_name,password_update_time,cbm_code,creator,create_time,updater,update_time  FROM ua_users      WHERE  (user_code = 'UCUS23032200001') LIMIT 1
Connection ID (thread ID): 956
Status: NOT_KILLED

Please help us make Percona Server better by reporting any
bugs at https://bugs.percona.com/

You may download the Percona Server operations manual by visiting
http://www.percona.com/software/percona-server/. You may find information


开发说他写的后端程序在执行任务,在执行的时候会卡住,然后就报刚才那个使用旧连接错误
现在每执行一次,greatsql就报一次上面的错误,然后自动重启。。。。。。

现在不知道啥情况了,准备先升级到8.0.32-26再说
全部回复(8)
yejr 2024-9-12 10:12:15
这是触发了InnoDB并行查询bug导致的,请设置 force_parallel_execute = OFF,不要启用该特性。

可以参考这份FAQ: 故障排查 | GreatSQL用户手册
驭无殇1998 2024-9-12 10:45:29
yejr 发表于 2024-9-12 10:12
这是触发了InnoDB并行查询bug导致的,请设置 force_parallel_execute = OFF,不要启用该特性。

可以参考这 ...

是这个问题
reddey 2024-9-12 13:09:56

很多网友好像都遇到这个问题
一个学艺不精的国产数据库爱好者
yejr 2024-9-12 13:22:03
reddey 发表于 2024-9-12 13:09
很多网友好像都遇到这个问题

可以记住一个窍门:数据库实例进程发生core时,如果错误日志中有PQblock关键字,那肯定是InnoDB并行查询特性导致的了。

推荐采用新的Rapid引擎来代替InnoDB PQ:https://greatsql.cn/docs/8.0.32- ... f-rapid-engine.html
reddey 2024-9-12 14:49:18
yejr 发表于 2024-9-12 13:22
可以记住一个窍门:数据库实例进程发生core时,如果错误日志中有PQblock关键字,那肯定是InnoDB并行查询 ...

这个报错确实有
一个学艺不精的国产数据库爱好者
驭无殇1998 2024-9-13 09:36:51
yejr 发表于 2024-9-12 13:22
可以记住一个窍门:数据库实例进程发生core时,如果错误日志中有PQblock关键字,那肯定是InnoDB并行查询 ...

这个后面可以尝试一下,哈哈哈哈
czxin788 2024-9-14 14:05:13
yejr 发表于 2024-9-12 13:22
可以记住一个窍门:数据库实例进程发生core时,如果错误日志中有PQblock关键字,那肯定是InnoDB并行查询 ...

Rapid什么时候出新版本,期待半年了
yejr 2024-9-14 14:18:02
czxin788 发表于 2024-9-14 14:05
Rapid什么时候出新版本,期待半年了

最快这个月,最慢今年底前会发Rapid新版,我最近正在内测中
驭无殇1998

22

主题

0

博客

181

贡献

中级会员

Rank: 3Rank: 3

积分
297

勤学好问(铜)助人为乐(银)

合作电话:010-64087828

社区邮箱:greatsql@greatdb.com

社区公众号
社区小助手
QQ群
GMT+8, 2024-12-4 21:19 , Processed in 0.028344 second(s), 21 queries , Redis On.
快速回复 返回顶部 返回列表