|
本帖最后由 驭无殇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再说 |
|
|
yejr
2024-9-12 10:12:15
| |
|
|
驭无殇1998
2024-9-12 10:45:29
| |
|
|
reddey
2024-9-12 13:09:56
| |
|
一个学艺不精的国产数据库爱好者
|
||
|
|
yejr
2024-9-12 13:22:03
| |
|
|
reddey
2024-9-12 14:49:18
| |
|
一个学艺不精的国产数据库爱好者
|
||
|
|
驭无殇1998
2024-9-13 09:36:51
| |
|
|
czxin788
2024-9-14 14:05:13
| |
|
|
yejr
2024-9-14 14:18:02
| |
合作电话：010-64087828
社区邮箱：greatsql@greatdb.com
