GreatSQL社区

搜索

北部卡车

greatsql-8.0.32-25 测试环境运行过程中自动停服

北部卡车 已有 232 次阅读2024-3-28 16:37 |系统分类:运维实战

数据库实例启动后一段时间会自行关闭,风险极高,相关日志如下:

安装环境: Linux VM-4-10-kylin 4.19.90-23.20.v2101.ky10.aarch64 #1 SMP Fri Feb 18 14:29:46 CST 2022 aarch64 aarch64 aarch64 GNU/Linux

2024-03-28T14:38:41 完成启动,运行一段时间后 2024-03-28T15:41:57实例关闭

2024-03-28T15:41:58.737265+08:00 1 [Note] [MY-012560] [InnoDB] The log sequence number 27614089 in the system tablespace does not match the log sequence number 27741505 in the redo log files!

2024-03-28T15:41:58.737278+08:00 1 [Note] [MY-012551] [InnoDB] Database was not shutdown normally!
2024-03-28T15:41:58.737286+08:00 1 [Note] [MY-012552] [InnoDB] Starting crash recovery.

相关报错日志和配置文件内容如下:不知道是哪里的配置不合适导致了系统表空间的lsn和redolog里面的lsn不一致,实例宕机了。

2024-03-28T14:38:41.738235+08:00 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.
2024-03-28T14:38:41.739967+08:00 0 [Note] [MY-010252] [Server] Server hostname (bind-address): '0.0.0.0'; port: 3308
2024-03-28T14:38:41.739986+08:00 0 [Note] [MY-010264] [Server]   - '0.0.0.0' resolves to '0.0.0.0';
2024-03-28T14:38:41.740005+08:00 0 [Note] [MY-010251] [Server] Server socket created on IP: '0.0.0.0'.
2024-03-28T14:38:41.791372+08:00 0 [Note] [MY-011025] [Repl] Failed to start slave threads for channel ''.
2024-03-28T14:38:41.796641+08:00 5 [Note] [MY-010051] [Server] Event Scheduler: scheduler thread started with id 5
2024-03-28T14:38:41.796763+08:00 0 [System] [MY-010931] [Server] /data/app/greatsql-8.0.32-25/bin/mysqld: ready for connections. Version: '8.0.32-25'  socket: '/data/greatsql_data/greatsql-3308/data/greatsql.sock'  port: 3308  GreatSQL, Release 25, Revision 79f57097e3f-gmssl.
2024-03-28T14:38:41.968251+08:00 0 [Note] [MY-011946] [InnoDB] Buffer pool(s) load completed at 240328 14:38:41


2024-03-28T15:41:57.449591+08:00 0 [Warning] [MY-011068] [Server] The syntax 'log_slow_slave_statements' is deprecated and will be removed in a future release. Please use log_slow_replica_statements instead.
2024-03-28T15:41:57.449631+08:00 0 [Warning] [MY-011068] [Server] The syntax 'slave_parallel_type' is deprecated and will be removed in a future release. Please use replica_parallel_type instead.
2024-03-28T15:41:57.449637+08:00 0 [Warning] [MY-011069] [Server] The syntax '--replica-parallel-type' is deprecated and will be removed in a future release.
2024-03-28T15:41:57.449646+08:00 0 [Warning] [MY-011068] [Server] The syntax 'slave_parallel_workers' is deprecated and will be removed in a future release. Please use replica_parallel_workers instead.
2024-03-28T15:41:57.449655+08:00 0 [Warning] [MY-011068] [Server] The syntax 'slave_preserve_commit_order' is deprecated and will be removed in a future release. Please use replica_preserve_commit_order instead.
2024-03-28T15:41:57.449663+08:00 0 [Warning] [MY-011068] [Server] The syntax 'slave_checkpoint_period' is deprecated and will be removed in a future release. Please use replica_checkpoint_period instead.
2024-03-28T15:41:57.449738+08:00 0 [Warning] [MY-010086] [Server] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-03-28T15:41:57.449802+08:00 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2024-03-28T15:41:57.449825+08:00 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-03-28T15:41:57.449829+08:00 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-log-path: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-03-28T15:41:57.449923+08:00 0 [Note] [MY-013932] [Server] BuildID[sha1]=3262bed7891916c33fb609b8b5b4466899d0b286
2024-03-28T15:41:57.449929+08:00 0 [Note] [MY-010949] [Server] Basedir set to /data/app/greatsql-8.0.32-25/.
2024-03-28T15:41:57.449937+08:00 0 [System] [MY-010116] [Server] /data/app/greatsql-8.0.32-25/bin/mysqld (mysqld 8.0.32-25) starting as process 1915661
2024-03-28T15:41:57.484400+08:00 0 [Note] [MY-012366] [InnoDB] Using Linux native AIO
2024-03-28T15:41:57.484503+08:00 0 [Warning] [MY-013869] [InnoDB] Ignored deprecated configuration parameter innodb_log_file_size. Used innodb_redo_log_capacity instead.
2024-03-28T15:41:57.484512+08:00 0 [Warning] [MY-013870] [InnoDB] Ignored deprecated configuration parameter innodb_log_files_in_group. Used innodb_redo_log_capacity instead.
2024-03-28T15:41:57.484900+08:00 0 [Note] [MY-010747] [Server] Plugin 'FEDERATED' is disabled.
2024-03-28T15:41:57.484939+08:00 0 [Note] [MY-010747] [Server] Plugin 'ndbcluster' is disabled.
2024-03-28T15:41:57.484950+08:00 0 [Note] [MY-010747] [Server] Plugin 'ndbinfo' is disabled.
2024-03-28T15:41:57.484956+08:00 0 [Note] [MY-010747] [Server] Plugin 'ndb_transid_mysql_connection_map' is disabled.
2024-03-28T15:41:57.485032+08:00 0 [Note] [MY-010747] [Server] Plugin 'mysqlx' is disabled.
2024-03-28T15:41:57.489917+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-28T15:41:57.489945+08:00 1 [Note] [MY-013546] [InnoDB] Atomic write enabled
2024-03-28T15:41:57.494294+08:00 1 [Note] [MY-012932] [InnoDB] PUNCH HOLE support available
2024-03-28T15:41:57.494329+08:00 1 [Note] [MY-012944] [InnoDB] Uses event mutexes
2024-03-28T15:41:57.494343+08:00 1 [Note] [MY-012945] [InnoDB] GCC builtin __atomic_thread_fence() is used for memory barrier
2024-03-28T15:41:57.494356+08:00 1 [Note] [MY-012948] [InnoDB] Compressed tables use zlib 1.2.13
2024-03-28T15:41:57.495659+08:00 1 [Note] [MY-011066] [InnoDB] File purge : set file purge path : /data/greatsql_data/greatsql-3308/data/#file_purge
2024-03-28T15:41:57.495919+08:00 1 [Note] [MY-012951] [InnoDB] Using hardware accelerated crc32 and polynomial multiplication.
2024-03-28T15:41:57.496128+08:00 1 [Note] [MY-012203] [InnoDB] Directories to scan './'
2024-03-28T15:41:57.496215+08:00 1 [Note] [MY-012204] [InnoDB] Scanning './'
2024-03-28T15:41:57.769284+08:00 1 [Note] [MY-012208] [InnoDB] Completed space ID check of 178 files.
2024-03-28T15:41:57.770466+08:00 1 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 4.000000G, instances = 8, chunk size =128.000000M
2024-03-28T15:41:57.770490+08:00 1 [Note] [MY-011872] [InnoDB] Setting NUMA memory policy to MPOL_INTERLEAVE
2024-03-28T15:41:58.050717+08:00 1 [Note] [MY-011874] [InnoDB] Setting NUMA memory policy to MPOL_DEFAULT
2024-03-28T15:41:58.051132+08:00 1 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool
2024-03-28T15:41:58.668475+08:00 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
2024-03-28T15:41:58.678942+08:00 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite
2024-03-28T15:41:58.679187+08:00 0 [Note] [MY-011952] [InnoDB] If the mysqld execution user is authorized, page cleaner and LRU manager thread priority can be changed. See the man page of setpriority().
2024-03-28T15:41:58.688788+08:00 1 [Note] [MY-013566] [InnoDB] Double write buffer files: 2
2024-03-28T15:41:58.688816+08:00 1 [Note] [MY-013565] [InnoDB] Double write buffer pages per instance: 4
2024-03-28T15:41:58.688834+08:00 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
2024-03-28T15:41:58.688849+08:00 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite
2024-03-28T15:41:58.733552+08:00 1 [Note] [MY-013883] [InnoDB] The latest found checkpoint is at lsn = 27741505 in redo log file ./#innodb_redo/#ib_redo0.
2024-03-28T15:41:58.737265+08:00 1 [Note] [MY-012560] [InnoDB] The log sequence number 27614089 in the system tablespace does not match the log sequence number 27741505 in the redo log files!
2024-03-28T15:41:58.737278+08:00 1 [Note] [MY-012551] [InnoDB] Database was not shutdown normally!
2024-03-28T15:41:58.737286+08:00 1 [Note] [MY-012552] [InnoDB] Starting crash recovery.
2024-03-28T15:41:59.465439+08:00 1 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn = 27741214, whereas checkpoint_lsn = 27741505 and start_lsn = 27741184
2024-03-28T15:41:59.485538+08:00 1 [Note] [MY-012550] [InnoDB] Doing recovery: scanned up to log sequence number 27741505
2024-03-28T15:42:00.497354+08:00 1 [Note] [MY-013083] [InnoDB] Log background threads are being started...
2024-03-28T15:42:05.759384+08:00 1 [Note] [MY-012532] [InnoDB] Applying a batch of 0 redo log records ...
2024-03-28T15:42:07.170688+08:00 1 [Note] [MY-012535] [InnoDB] Apply batch completed!
2024-03-28T15:42:09.242725+08:00 1 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'.
2024-03-28T15:42:12.975964+08:00 1 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'.
2024-03-28T15:42:15.156099+08:00 1 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces.
2024-03-28T15:42:26.012649+08:00 1 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 10513
2024-03-28T15:42:39.650844+08:00 1 [Note] [MY-013776] [InnoDB] Parallel initialization of rseg complete
2024-03-28T15:42:45.357572+08:00 1 [Note] [MY-013777] [InnoDB] Time taken to initialize rseg using 4 thread: 20953742 ms.
2024-03-28T15:42:46.652169+08:00 1 [Note] [MY-012255] [InnoDB] Removed temporary tablespace data file: "ibtmp1"
2024-03-28T15:42:50.252689+08:00 1 [Note] [MY-012923] [InnoDB] Creating shared tablespace for temporary tables
2024-03-28T15:42:52.214067+08:00 1 [Note] [MY-012265] [InnoDB] Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-03-28T15:42:53.952756+08:00 1 [Note] [MY-012266] [InnoDB] File './ibtmp1' size is now 12 MB.
2024-03-28T15:43:00.467077+08:00 1 [Note] [MY-013627] [InnoDB] Scanning temp tablespace dir:'./#innodb_temp/'


实例配置文件:

[root@VM-4-10-kylin data]# cat   /data/greatsql_data/cnf/great-3308.cnf

[client]

socket = /data/greatsql_data/greatsql-3308/data/greatsql.sock


[mysql]

loose-skip-binary-as-hex

prompt = "(\\D)[\\u@GreatSQL][\\d]>"

no-auto-rehash


[mysqld]

user = mysql

port = 3308

server_id = 1046410

basedir   = /data/app/greatsql-8.0.32-25

datadir   = /data/greatsql_data/greatsql-3308/data

socket    = /data/greatsql_data/greatsql-3308/data/greatsql.sock

pid-file  = /data/greatsql_data/greatsql-3308/data/greatsql.pid

character-set-server = UTF8MB4

skip_name_resolve = 1

#若你的MySQL数据库主要运行在境外,请务必根据实际情况调整本参数

default_time_zone = "+8:00"

bind_address = "0.0.0.0"


mysqlx=0

explicit_defaults_for_timestamp=OFF


#enable openssl & SM

#require_secure_transport = ON

#tls_ciphersuites = "TLS_SM4_GCM_SM3:TLS_SM4_CCM_SM3"

#tls_version = 'TLSv1.3'


loose-plugin-load=keyring_file.so

keyring_file_data=/data/greatsql_data/keyring/master_keyring


transaction_isolation = read-committed


#performance setttings

lock_wait_timeout = 3600

open_files_limit  = 65535

back_log = 1024

max_connections = 1024

max_connect_errors = 1000000

table_open_cache = 2048

table_definition_cache = 2048

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 = 96M

max_heap_table_size = 96M

max_allowed_packet = 64M

net_buffer_shrink_interval = 180


#GIPK

loose-sql_generate_invisible_primary_key = ON


#log settings

log_timestamps = SYSTEM

log_error = /data/greatsql_data/greatsql-3308/log/alert.log

log_error_verbosity = 3

slow_query_log = 1

log_slow_extra = 1

slow_query_log_file = /data/greatsql_data/greatsql-3308/log/slow.log

#设置slow log文件大小1G及总文件数10

max_slowlog_size = 1073741824

max_slowlog_files = 10

long_query_time = 0.01

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_slow_verbosity = FULL

log_bin = /data/greatsql_data/binlog/greatsql_bin

binlog_format = ROW

sync_binlog = 1

binlog_cache_size = 4M

max_binlog_cache_size = 6G

max_binlog_size = 1G

#控制binlog总大小,避免磁盘空间被撑爆

binlog_space_limit = 500G

binlog_rows_query_log_events = 1

binlog_expire_logs_seconds = 604800

binlog_checksum = CRC32

gtid_mode = ON

enforce_gtid_consistency = TRUE


#myisam settings

key_buffer_size = 32M

myisam_sort_buffer_size = 128M


#replication settings

relay_log_recovery = 1

slave_parallel_type = LOGICAL_CLOCK

#并行复制线程数可以设置为逻辑CPU数量的2倍

slave_parallel_workers = 64

binlog_transaction_dependency_tracking = WRITESET

slave_preserve_commit_order = 1

slave_checkpoint_period = 2


#启用InnoDB并行查询优化功能

loose_force_parallel_execute = OFF

#设置每个SQL语句的并行查询最大并发度

loose_parallel_default_dop = 8

#设置系统中总的并行查询线程数,可以和最大逻辑CPU数量一样

loose_parallel_max_threads = 64

#并行执行时leader线程和worker线程使用的总内存大小上限,可以设置物理内存的5-10%左右

loose_parallel_memory_limit = 2G


#parallel load data

#loose_gdb_parallel_load_chunk_size = 4M


#rapid engine

#loose_rapid_memory_limit = 12G

#loose_rapid_worker_threads = 32

#loose_rapid_hash_table_memory_limit = 30

loose_secondary_engine_parallel_load_workers = 16


#innodb settings

innodb_buffer_pool_size = 4G

innodb_buffer_pool_instances = 8

innodb_data_file_path = ibdata1:6G;ibdata2:512M: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_doublewrite_files = 2

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 = 65534

#提醒:当需要用CLONE加密特性时,不要选用O_DIRECT模式,否则会比较慢

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 = 1

innodb_status_file = 1

innodb_status_output = 0

innodb_status_output_locks = 1

innodb_sort_buffer_size = 64M

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


sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'


#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"

#pfs settings

performance_schema = 1

#performance_schema_instrument = '%memory%=on'

performance_schema_instrument = '%lock%=on'

发表评论 评论 (1 个评论)

回复 北部卡车 2024-4-1 12:32
可能是其他组件压测期间到账greatsql实例异常宕机,数据文件损坏,并且使用默认的恢复级别不能完成恢复,导致表空间的lsn和redolog的lsn不一致,最终数据库实例重启后运行一段时间后自行宕机或是无法重新启动,清理数据目录后进行重新初始化做专用服务器进行测试

facelist

您需要登录后才可以评论 登录 | 立即注册

合作电话:010-64087828

社区邮箱:greatsql@greatdb.com

社区公众号
社区小助手
QQ群
GMT+8, 2024-4-22 14:17 , Processed in 0.013689 second(s), 8 queries , Redis On.
返回顶部