$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
— 设置内存限制
ALTER SYSTEM SET memory_limit=’200G’;

— 设置系统内存
ALTER SYSTEM SET system_memory=’50G’;

— 设置MemStore内存比例
ALTER SYSTEM SET memstore_limit_percentage=50;

— 设置冻结触发比例
ALTER SYSTEM SET freeze_trigger_percentage=70;

— 设置转储次数
ALTER SYSTEM SET minor_freeze_times=10;

— 设置合并时间
ALTER SYSTEM SET major_freeze_duty_time=’02:00′;
”

Query OK, 0 rows affected

# 验证参数
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
SHOW PARAMETERS LIKE ‘memory_limit’;学习交流加群风哥QQ113257174。
SHOW PARAMETERS LIKE ‘memstore_limit_percentage’;
SHOW PARAMETERS LIKE ‘freeze_trigger_percentage’;
”

$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
— 设置Clog同步超时警告阈值
ALTER SYSTEM SET clog_sync_time_warn_threshold=’100ms’;

— 设置最大日志文件数
ALTER SYSTEM SET max_syslog_file_count=100;

— 启用日志回收
ALTER SYSTEM SET enable_syslog_recycle=true;

— 设置日志级别
ALTER SYSTEM SET syslog_level=’WARN’;
”

Query OK, 0 rows affected

# 查看日志统计
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
SELECT
svr_ip,
svr_port,
clog_disk_in_use/1024/1024/1024 as clog_used_gb,
ilog_disk_in_use/1024/1024/1024 as ilog_used_gb
FROM oceanbase.__all_virtual_disk_stat;
”

1. 批量INSERT优化
$ obclient -h192.168.1.101 -P2881 -uroot@fgedu_tenant -p -e ”
— 单条INSERT（不推荐）
— INSERT INTO fgedudb.fgedu_order VALUES (1, 10001, 199.99, NOW());

— 批量INSERT（推荐）
INSERT INTO fgedudb.fgedu_order (order_id, user_id, amount, create_time) VALUES
(1, 10001, 199.99, NOW()),
(2, 10002, 299.99, NOW()),
(3, 10003, 399.99, NOW()),
…
(1000, 10100, 99.99, NOW());
”

Query OK, 1000 rows affected
Records: 1000 Duplicates: 0 Warnings: 0

2. 使用LOAD DATA
$ obclient -h192.168.1.101 -P2881 -uroot@fgedu_tenant -p -e ”
LOAD DATA INFILE ‘/data/orders.csv’
INTO TABLE fgedudb.fgedu_order
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
(order_id, user_id, amount, create_time);
”

Query OK, 100000 rows affected
Records: 100000 Deleted: 0 Skipped: 0 Warnings: 0

3. JDBC批量写入示例
// Java代码示例
String sql = “INSERT INTO fgedu_order (order_id, user_id, amount) VALUES (?, ?, ?)”;
PreparedStatement pstmt = conn.prepareStatement(sql);

for (int i = 0; i < orders.size(); i++) { Order order = orders.get(i); pstmt.setLong(1, order.getOrderId()); pstmt.setLong(2, order.getUserId()); pstmt.setBigDecimal(3, order.getAmount());更多学习教程公众号风哥教程itpux_com。 pstmt.addBatch(); if (i % 1000 == 0) { pstmt.executeBatch(); conn.commit(); } } pstmt.executeBatch(); conn.commit();

1. 创建分区表
$ obclient -h192.168.1.101 -P2881 -uroot@fgedu_tenant -p -e ”
— 按时间范围分区
CREATE TABLE fgedudb.fgedu_order (
order_id BIGINT PRIMARY KEY,
user_id BIGINT,
amount DECIMAL(18,2),
create_time TIMESTAMP
) PARTITION BY RANGE COLUMNS(create_time) (
PARTITION p202401 VALUES LESS THAN (‘2024-02-01’),
PARTITION p202402 VALUES LESS THAN (‘2024-03-01’),
PARTITION p202403 VALUES LESS THAN (‘2024-04-01’),
PARTITION pmax VALUES LESS THAN MAXVALUE
);from DB视频:www.itpux.com。

— 按ID哈希分区
CREATE TABLE fgedudb.fgedu_log (
log_id BIGINT PRIMARY KEY,
log_type VARCHAR(50),
log_content TEXT,
create_time TIMESTAMP
) PARTITION BY HASH(log_id) PARTITIONS 16;
”

Query OK, 0 rows affected

2. 分区维护
$ obclient -h192.168.1.101 -P2881 -uroot@fgedu_tenant -p -e ”
— 添加新分区
ALTER TABLE fgedudb.fgedu_order ADD PARTITION (
PARTITION p202404 VALUES LESS THAN (‘2024-05-01’)
);

— 删除旧分区
ALTER TABLE fgedudb.fgedu_order DROP PARTITION p202401;

— 查看分区信息
SELECT
partition_name,
high_value,
table_rows
FROM information_schema.partitions
WHERE table_schema = ‘fgedudb’ AND table_name = ‘fgedu_order’;
”

+—————-+———————+————+
| partition_name | high_value | table_rows |
+—————-+———————+————+
| p202402 | ‘2024-03-01’ | 1000000 |
| p202403 | ‘2024-04-01’ | 1500000 |
| pmax | MAXVALUE | 0 |
+—————-+———————+————+