oceanbase 问题汇总

1.权限问题：

   load from csv报 access denied。

 需设置环境变量：set global secure_file_priv=""

2.query超时：

OS参数

sudo sysctl kernel.sched_migration_cost_ns=0;sudo sysctl -p

kernel.sched_migration_cost_ns

JDBC参数

conn=jdbc:oceanbase://172.18.4.191:2828/test?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=3000000

allowMultiQueries

rewriteBatchedStatements

useLocalTransactionState

OBServer内核

• root@sys

alter system set cpu_quota_concurrency=4;

alter system set enable_pg=true;

alter system set enable_early_lock_release=false tenant=all;

alter system set _max_trx_size='600M'

_max_trx_size

enable_early_lock_release

enable_pg

cpu_quota_concurrency

• 测试租户

set global ob_query_timeout=3600000000;

set global ob_trx_timeout=3600000000;

set global max_allowed_packet=67108864;

set global ob_sql_work_area_percentage=100;

set global parallel_max_servers=600;

set global parallel_servers_target=600;

set global ob_create_table_strict_mode=off;

ob_query_timeout

ob_trx_timeout

max_allowed_packet

ob_sql_work_area_percentage

parallel_max_servers

parallel_servers_target

ob_create_table_strict_mode

构建仓库倒数参数

alter system set memory_chunk_cache_size ='16G';

alter system set clog_transport_compress_all=false;

alter system set trx_try_wait_lock_timeout='0ms';

alter system set large_query_threshold='1s';

alter system set trace_log_slow_query_watermark='500ms';

alter system set syslog_io_bandwidth_limit='30m';

alter system set enable_async_syslog=true;

alter system set merger_warm_up_duration_time='0';

alter system set merger_switch_leader_duration_time='0';

alter system set large_query_worker_percentage=10;

alter system set builtin_db_data_verify_cycle = 0;

alter system set enable_merge_by_turn = False;

alter system set minor_merge_concurrency=30;

alter system set system_memory ='30G';

alter system set enable_pg=true;

alter system set memory_limit_percentage = 85;

alter system set memstore_limit_percentage = 80;

alter system set freeze_trigger_percentage = 30;

alter system set minor_freeze_times=10;

alter system set minor_compact_trigger=5;

alter system set micro_block_merge_verify_level = 0;

alter system set max_kept_major_version_number=1;

alter system set sys_bkgd_io_high_percentage = 90;

alter system set sys_bkgd_io_low_percentage = 70;

alter system set merge_thread_count  = 45;

alter system set merge_stat_sampling_ratio = 1;

alter system set _ob_btree_retire_limit=10240;

压测测试

alter system major freeze;

alter system set memstore_limit_percentage = 80;

alter system set freeze_trigger_percentage = 30;

alter system set large_query_threshold = '200s';

alter system set trx_try_wait_lock_timeout = '0ms';

alter system set cpu_quota_concurrency = 4;

alter system set minor_warm_up_duration_time = 0;

alter system set minor_freeze_times=1;       ####这块建议设置成200或者，更大让他不进行合并

alter system set minor_compact_trigger=0;

alter system set merge_stat_sampling_ratio = 1;

alter system set sys_bkgd_io_high_percentage = 90;

alter system set sys_bkgd_io_low_percentage = 70;

alter system set minor_merge_concurrency =20;

alter system set builtin_db_data_verify_cycle = 0;

alter system set micro_block_merge_verify_level = 0;

alter system set trace_log_slow_query_watermark = '10s';

alter system set gts_refresh_interval='500us';

alter system set server_permanent_offline_time='36000s';

alter system set weak_read_version_refresh_interval=0;

alter system set _ob_get_gts_ahead_interval = '5ms';

alter system set bf_cache_priority = 10;

alter system set user_block_cache_priority=5;

alter system set merge_stat_sampling_ratio = 0; ####这块可以忽略，不然影响ndv统计，只要压测中不进行major freeze

alter system set enable_sql_audit=false;

alter system set syslog_level='PERF';

Proxy参数

./obproxy -p8888 -c obtest -n obtest -r'192.168.4.159:2881;192.168.4.160:2881;192.168.4.161:2881' -o enable_metadb_used=false,enable_strict_kernel_release=false,work_thread_num=64,automatic_match_work_thread=false,enable_compression_protocol=false,syslog_level=info,enable_async_log=true,proxy_mem_limited='8G',slow_proxy_process_time_threshold='5s'

alter proxyconfig  set slow_proxy_process_time_threshold='5s';

alter proxyconfig  set proxyconfig proxy_mem_limited='8G';

enable_compression_protocol

enable_async_log

automatic_match_work_thread

work_thread_num

enable_strict_kernel_release

enable_metadb_used

enable_async_log

slow_proxy_process_time_threshold