python3.6.2用pyinstaller3.4报错_OceanBase 2.2 版本体验：用 BenchmarkSQL 跑 TPC-C
生活随笔
收集整理的這篇文章主要介紹了
python3.6.2用pyinstaller3.4报错_OceanBase 2.2 版本体验：用 BenchmarkSQL 跑 TPC-C
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
OB君：好消息！「 OceanBase 2.2 版本 」正式上線官網(wǎng)啦！（點擊閱讀原文即可直接下載）OceanBase 2.2版本是成功支撐2019年天貓雙11大促的穩(wěn)定版本，同時也是用于TPC-C測試且榮登TPC-C性能榜首的版本。我們將在接下來的時間里為大家持續(xù)推出 “OceanBase 2.2 手把手系列” ，將手把手帶大家一起體驗OceanBase 2.2的強大功能。歡迎持續(xù)關注！
引言
OceanBase 2.2版本近期已通過官網(wǎng)提供下載（https://oceanbase.alipay.com/download/resource），2.2支持Oracle租戶。OceanBase在2019年10月2日榮膺國際事務委員會(TPC)審計發(fā)布的TPC-C基準測試榜首，用的就是Oracle租戶。TPC-C測試使用了207多臺阿里云高配ECS服務器，是因為TPC-C標準對應用、數(shù)據(jù)庫等規(guī)范非常細致嚴格。一般來說普通企業(yè)或個人很難有那樣的條件去測試。
BenchmarkSQL是開源的TPC-C測試程序，它弱化了TPC-C的關鍵標準(數(shù)據(jù)分布和應用執(zhí)行行為方面)，使得用幾臺服務器就可以跑TPC-C成為可能。當然這個結果不能跟官方TPC-C的結果相比較。不過，使用BenchmarkSQL來比較不同的數(shù)據(jù)庫的事務處理能力還是有一定參考意義的，尤其是相比Sysbench而言。
OceanBase測試租戶準備
1.sys租戶參數(shù)修改
BenchmarkSQL會加載大量數(shù)據(jù)，短時間內對OceanBase內存消耗速度會很快，因此需要針對內存凍結合并和限流參數(shù)做一些調優(yōu)。在sys租戶執(zhí)行：
ALTER SYSTEM SET enable_merge_by_turn=FALSE; ALTER SYSTEM set minor_freeze_times=100; ALTER SYSTEM set freeze_trigger_percentage=70; ALTER SYSTEM set writing_throttling_trigger_percentage=70 tenant='obbmsql'; ALTER SYSTEM set writing_throttling_maximum_duration='10m' tenant='obbmsql'; show parameters where name in ('minor_freeze_times','freeze_trigger_percentage');注意：業(yè)務租戶限流參數(shù)的修改是在sys租戶里，需要指定相應的租戶名。然后查看確認需要到業(yè)務租戶里。
在業(yè)務租戶執(zhí)行：
2. 業(yè)務租戶參數(shù)修改
OceanBase跟Oracle/MySQL相比，會有個默認SQL超時和事務超時機制。這個可能會導致后面查看修改數(shù)據(jù)的SQL報錯。所以先修改一下這些參數(shù)。
set global recyclebin=off; set global ob_query_timeout=1000000000; set global ob_trx_idle_timeout=1200000000; set global ob_trx_timeout=1000000000;此外，需要為bmsql準備一個單獨的schema(即用戶)。
drop user tpcc cascade;create user tpcc identified by 123456; grant all privileges on tpcc.* to tpcc with grant option ; grant create, drop on *.* to tpcc;3. OBProxy配置修改
OBProxy是OceanBase的訪問代理，其內部一些參數(shù)也可能影響性能。如下面的壓縮參數(shù)對CPU有一定消耗，測試時可以關閉。
$ obclient -h127.1 -uroot@sys#obdemo -P2883 -p123456 -c -A oceanbasealter proxyconfig set enable_compression_protocol=False; show proxyconfig like 'enable_compression_protocol';該參數(shù)修改后，需要重啟obproxy進程
[admin@xxx /home/admin] $kill -9 `pidof obproxy`[admin@h07d17167.sqa.eu95 /home/admin] $cd /opt/taobao/install/obproxy[admin@xxx /opt/taobao/install/obproxy] $bin/obproxy bin/obproxyBenchmarkSQL準備
BenchmarkSQL 官方下載地址是： https://sourceforge.net/projects/benchmarksql/ ，下載后請參考 HOW-TO-RUN.txt 中說明先編譯安裝BenchmarkSQL。然后按下面建議修改部分腳本增加對OceanBase支持。也可以直接下載我編譯修改好的BenchmarkSQL，地址是：https://github.com/obpilot/benchmarksql-5.0 。
1. 準備OceanBase驅動文件
BenchmarkSQL是通過jdbc連接各個數(shù)據(jù)庫的。此次OceanBase的測試租戶是Oracle類型，所以需要借用 lib/oracle 目錄，然后把相關jar包一并放入其中。其中 oceanbase-client-*.jar 是OceanBase提供的，其他jar包可以從互聯(lián)網(wǎng)獲取。
[admin@xxx /home/admin/benchmarksql-5.0]$ll lib/oracle/ total 3728 -rwxr-xr-x 1 admin admin 52988 Jul 12 2019 commons-cli-1.3.1.jar -rwxr-xr-x 1 admin admin 245274 Jul 12 2019 commons-lang-2.3.jar -rwxr-xr-x 1 admin admin 2256213 Jul 12 2019 guava-18.0.jar -rwxr-xr-x 1 admin admin 54495 Jul 12 2019 json-20160810.jar -rwxr-xr-x 1 admin admin 1121698 Dec 3 15:04 oceanbase-client-1.0.8.jar -rwxr-xr-x 1 admin admin 174 Jul 12 2019 README.txt -rwxr-xr-x 1 admin admin 76997 Jul 12 2019 toolkit-common-logging-1.10.jar2. 準備OB配置文件
$cat props.ob db=oracle driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver conn=jdbc:oceanbase://127.0.0.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8 user=tpcc@obbmsql#obdemo password=123456warehouses=10 loadWorkers=10 //fileLocation=/home/t4/tmpterminals=10 //To run specified transactions per terminal- runMins must equal zero runTxnsPerTerminal=0 //To run for specified minutes- runTxnsPerTerminal must equal zero runMins=10 //Number of total transactions per minute limitTxnsPerMin=0//Set to true to run in 4.x compatible mode. Set to false to use the //entire configured database evenly. terminalWarehouseFixed=true//The following five values must add up to 100 newOrderWeight=45 paymentWeight=43 orderStatusWeight=4 deliveryWeight=4 stockLevelWeight=4// Directory name to create for collecting detailed result data. // Comment this out to suppress. resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS osCollectorScript=./misc/os_collector_linux.py osCollectorInterval=1 //osCollectorSSHAddr=user@dbhost //osCollectorDevices=net_eth0 blk_sda注意：a. 倉庫數(shù)(warehouses)決定了數(shù)據(jù)量。正式的壓測倉庫數(shù)一般在10000以上。b. loadworkers數(shù)決定了數(shù)據(jù)加載的性能。如果OceanBase租戶資源很小(尤其是內存資源)，那加載速度也不要太快；否則容易把租戶內存打爆。c. 并發(fā)數(shù)(terminals)是后期做TPC-C測試的客戶端并發(fā)數(shù)。這個每次測試都可以調整，以方便觀察不同壓力下的性能。
d. 壓測時間(runMin)是每次測試時間，越長測試結果越好且穩(wěn)定。因為有時候數(shù)據(jù)訪問有個預熱過程，效果會體現(xiàn)在內存命中率上。
3. 創(chuàng)建BenchmarkSQL相關表
1）建表腳本
該SQL腳本不需要直接執(zhí)行。
create table bmsql_config (cfg_name varchar2(30) primary key,cfg_value varchar2(50) );create tablegroup tpcc_group partition by hash partitions 12;create table bmsql_warehouse (w_id integer not null,w_ytd decimal(12,2),w_tax decimal(4,4),w_name varchar2(10),w_street_1 varchar2(20),w_street_2 varchar2(20),w_city varchar2(20),w_state char(2),w_zip char(9),primary key(w_id) )tablegroup='tpcc_group' partition by hash(w_id) partitions 12;create table bmsql_district (d_w_id integer not null,d_id integer not null,d_ytd decimal(12,2),d_tax decimal(4,4),d_next_o_id integer,d_name varchar2(10),d_street_1 varchar2(20),d_street_2 varchar2(20),d_city varchar2(20),d_state char(2),d_zip char(9),PRIMARY KEY (d_w_id, d_id) )tablegroup='tpcc_group' partition by hash(d_w_id) partitions 12;create table bmsql_customer (c_w_id integer not null,c_d_id integer not null,c_id integer not null,c_discount decimal(4,4),c_credit char(2),c_last varchar2(16),c_first varchar2(16),c_credit_lim decimal(12,2),c_balance decimal(12,2),c_ytd_payment decimal(12,2),c_payment_cnt integer,c_delivery_cnt integer,c_street_1 varchar2(20),c_street_2 varchar2(20),c_city varchar2(20),c_state char(2),c_zip char(9),c_phone char(16),c_since timestamp,c_middle char(2),c_data varchar2(500),PRIMARY KEY (c_w_id, c_d_id, c_id) )tablegroup='tpcc_group' use_bloom_filter=true compress partition by hash(c_w_id) partitions 12;create sequence bmsql_hist_id_seq;create table bmsql_history (hist_id integer,h_c_id integer,h_c_d_id integer,h_c_w_id integer,h_d_id integer,h_w_id integer,h_date timestamp,h_amount decimal(6,2),h_data varchar2(24) )tablegroup='tpcc_group' use_bloom_filter=true compress partition by hash(h_w_id) partitions 12;create table bmsql_new_order (no_w_id integer not null ,no_d_id integer not null,no_o_id integer not null,PRIMARY KEY (no_w_id, no_d_id, no_o_id) )tablegroup='tpcc_group' use_bloom_filter=true compress partition by hash(no_w_id) partitions 12;create table bmsql_oorder (o_w_id integer not null,o_d_id integer not null,o_id integer not null,o_c_id integer,o_carrier_id integer,o_ol_cnt integer,o_all_local integer,o_entry_d timestamp,PRIMARY KEY (o_w_id, o_d_id, o_id) )tablegroup='tpcc_group' use_bloom_filter=true compress partition by hash(o_w_id) partitions 12;create table bmsql_order_line (ol_w_id integer not null,ol_d_id integer not null,ol_o_id integer not null,ol_number integer not null,ol_i_id integer not null,ol_delivery_d timestamp,ol_amount decimal(6,2),ol_supply_w_id integer,ol_quantity integer,ol_dist_info char(24),PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number) )tablegroup='tpcc_group' use_bloom_filter=true compress partition by hash(ol_w_id) partitions 12;create table bmsql_item (i_id integer not null,i_name varchar2(24),i_price decimal(5,2),i_data varchar2(50),i_im_id integer,PRIMARY KEY (i_id) )use_bloom_filter=true compress locality='F,R{all_server}@zone1, F,R{all_server}@zone2, F,R{all_server}@zone3' primary_zone='zone1' duplicate_scope='cluster';create table bmsql_stock (s_w_id integer not null,s_i_id integer not null,s_quantity integer,s_ytd integer,s_order_cnt integer,s_remote_cnt integer,s_data varchar2(50),s_dist_01 char(24),s_dist_02 char(24),s_dist_03 char(24),s_dist_04 char(24),s_dist_05 char(24),s_dist_06 char(24),s_dist_07 char(24),s_dist_08 char(24),s_dist_09 char(24),s_dist_10 char(24),PRIMARY KEY (s_w_id, s_i_id) )tablegroup='tpcc_group' use_bloom_filter=true compress partition by hash(s_w_id) partitions 12;注意：
a. 建表語句中的分區(qū)數(shù)目可以根據(jù)實際情況調整，跟集群節(jié)點數(shù)有關。如果集群是3臺(1-1-1)，建議是6個或6的倍數(shù)；如果集群是6臺(2-2-2)，建議是12個或12的倍數(shù)；如果集群是9臺(3-3-3)，建議是36個或36的倍數(shù)。這樣是方便后期彈性伸縮測試的時候能盡可能保證每個節(jié)點上的分區(qū)數(shù)均衡。
b. 上面bmsql_item使用了【復制表】功能，在租戶的所有節(jié)點上都會有一個副本。當然主副本始終只有一個。有關【復制表】功能介紹請參考《OceanBase事務引擎特性和應用實踐分享》。
c. 建表語句不包含非主鍵索引，是為了后面加載數(shù)據(jù)性能更快。
2）建表
./runSQL.sh props.ob ./sql.oceanbase/tableCreates.sql建表后，可以查看主副本分布
SELECT t1.tenant_id,t1.tenant_name,t2.database_name,t3.table_id,t3.table_Name,t3.tablegroup_id,t3.part_num,t4.partition_Id,t4.zone,t4.svr_ip,t4.role, round(t4.data_size/1024/1024) data_size_mb from `gv$tenant` t1join `gv$database` t2 on (t1.tenant_id = t2.tenant_id)join gv$table t3 on (t2.tenant_id = t3.tenant_id and t2.database_id = t3.database_id and t3.index_type = 0)left join `__all_virtual_meta_table` t4 on (t2.tenant_id = t4.tenant_id and ( t3.table_id = t4.table_id or t3.tablegroup_id = t4.table_id ) and t4.role in (1)) where t1.tenant_id = 1001 order by t3.tablegroup_id, t4.partition_Id, t3.table_name ;4. 加載數(shù)據(jù)
1）開始加載數(shù)據(jù)
./runLoader.sh props.ob2）觀察數(shù)據(jù)加載性能
為了對數(shù)據(jù)寫入速度進行觀察，可以在sys租戶下反復執(zhí)行下面SQL，主要是觀察增量內存增速和增量內存總量，以及是否接近總增量內存限制。
SELECT tenant_id, ip, round(active/1024/1024) active_mb, round(total/1024/1024) total_mb, round(freeze_trigger/1024/1024) freeze_trg_mb, round(mem_limit/1024/1024) mem_limit_mb, freeze_cnt , round((active/freeze_trigger),2) freeze_pct, round(total/mem_limit, 2) mem_usage FROM `gv$memstore` WHERE tenant_id IN (1001) ORDER BY tenant_id, ip;當然，觀察數(shù)據(jù)加載另外一個方法就是使用監(jiān)控。OCP的監(jiān)控或者dooba腳本監(jiān)控。
python dooba.py -h 127.1 -uroot@sys#obdemo -P2883 -p123456dooba 進去后，默認是sys租戶。按字母小寫'c'，選擇業(yè)務租戶。按數(shù)字'1'查看幫助，數(shù)字'2'查看租戶總覽，數(shù)字'3'查看租戶的機器性能信息，按TAB切換當前焦點，按字母小寫'd' 刪除當前TAB，按字母大寫R 恢復所有TAB。總覽里的NET TAB沒有意義可以刪除以節(jié)省屏幕空間。
5. 建索引
索引很少，就2條。由于相關表是分區(qū)表，可以建全局索引或者本地索引。我們建本地索引。
$cat ./sql.oceanbase/indexCreates.sql create index bmsql_customer_idx1on bmsql_customer (c_w_id, c_d_id, c_last, c_first) local; create index bmsql_oorder_idx1on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;開始建索引。OceanBase建索引很快就會返回，索引構建是異步的。
./runSQL.sh props.ob ./sql.oceanbase/indexCreates.sql6. 數(shù)據(jù)校驗
檢查一下各個表的數(shù)據(jù)量
obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_CONFIG; *+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_STOCK;+----------+ | COUNT(*) | +----------+ | 4 | +----------+ 1 row in set (0.06 sec)obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_WAREHOUSE; +----------+ | COUNT(*) | +----------+ | 10 | +----------+ 1 row in set (0.06 sec) obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_DISTRICT; +----------+ | COUNT(*) | +----------+ | 100 | +----------+ 1 row in set (0.06 sec)obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_CUSTOMER; +----------+ | COUNT(*) | +----------+ | 300000 | +----------+ 1 row in set (0.34 sec)obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_HISTORY; +----------+ | COUNT(*) | +----------+ | 300000 | +----------+ 1 row in set (0.10 sec)obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_NEW_ORDER; +----------+ | COUNT(*) | +----------+ | 90000 | +----------+ 1 row in set (0.07 sec)obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_OORDER; +----------+ | COUNT(*) | +----------+ | 300000 | +----------+ 1 row in set (0.11 sec)obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_ORDER_LINE; +----------+ | COUNT(*) | +----------+ | 3001782 | +----------+ 1 row in set (0.27 sec)obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_ITEM; +----------+ | COUNT(*) | +----------+ | 100000 | +----------+ 1 row in set (0.08 sec)obclient> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_STOCK; +----------+ | COUNT(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.63 sec)為了避免產生的數(shù)據(jù)不符合規(guī)范(如中間報錯導致有事務失敗)，運行下面校驗腳本
#!/usr/bin/shcc1=" SELECT /*+ no_use_px parallel(8) */ * FROM(SELECT w.w_id, w.w_ytd, d.sum_d_ytdFROM bmsql_warehouse w,(SELECT /*+ no_use_px parallel(8) */ d_w_id, sum(d_ytd) sum_d_ytd FROM bmsql_district GROUP BY d_w_id) dWHERE w.w_id= d.d_w_id ) x WHERE w_ytd != sum_d_ytd; " cc2=" SELECT /*+ no_use_px parallel(8) */ * FROM(SELECT d.d_w_id, d.d_id, d.d_next_o_id, o.max_o_id, no.max_no_o_idFROM bmsql_district d,(SELECT /*+ no_use_px parallel(8) */ o_w_id, o_d_id, MAX(o_id) max_o_id FROM bmsql_oorder GROUP BY o_w_id, o_d_id) o,(SELECT /*+ no_use_px parallel(8) */ no_w_id, no_d_id, MAX(no_o_id) max_no_o_id FROM bmsql_new_order GROUP BY no_w_id, no_d_id) noWHERE d.d_w_id= o.o_w_id AND d.d_w_id= no.no_w_id AND d.d_id= o.o_d_id AND d.d_id= no.no_d_id ) x WHERE d_next_o_id - 1!= max_o_id OR d_next_o_id - 1!= max_no_o_id; "cc3=" SELECT /*+ no_use_px paratLel(8) */ * FROM(SELECT /*+ no_use_px parallel(8) */ no_w_id, no_d_id, MAX(no_o_id) max_no_o_id, MIN(no_o_id) min_no_o_id, COUNT(*) count_noFROM bmsql_new_orderGROUP BY no_w_id, no_d_Id ) x WHERE max_no_o_id - min_no_o_id+ 1!= count_no; "cc4=" SELECT /*+ no_use_px parallel(8) */ * FROM (SELECT o.o_w_id, o.o_d_id, o.sum_o_ol_cnt, ol.count_olFROM (SELECT /*+ no_use_px parallel(8) */ o_w_id, o_d_id, SUM(o_ol_cnt) sum_o_ol_cnt FROM bmsql_oorder GROUP BY o_w_id, o_d_id) o,(SELECT /*+ no_use_px parallel(8) */ ol_w_id, ol_d_id, COUNT(*) count_ol FROM bmsql_order_line GROUP BY ol_w_id, ol_d_id) olWHERE o.o_w_id = ol.ol_w_id AND o.o_d_id = ol.ol_d_id ) x WHERE sum_o_ol_cnt != count_ol; "cc5=" SELECT /*+ no_use_px parallel(8) */ * FROM (SELECT o.o_w_id, o.o_d_id, o.o_id, o.o_carrier_id, no.count_noFROM bmsql_oorder o,(SELECT /*+ no_use_px parallels) */ no_w_id, no_d_id, no_o_id, COUNT(*) count_no FROM bmsql_new_order GROUP BY no_w_id, no_d_id, no_o_id) noWHERE o.o_w_id = no.no_w_id AND o.o_d_id = no.no_d_id AND o.o_id = no.no_o_id ) x WHERE (o_carrier_id IS NULL AND count_no = 0) OR (o_carrier_id IS NOT NULL AND count_no != 0); "cc6=" SELECT /*+ no_use_px parallel(8) */ * FROM (SELECT o.o_w_id, o.o_d_id, o.o_id, o.o_ol_cnt, ol.count_olFROM bmsql_oorder o,(SELECT /*+ no_use_px parallel(8) */ ol_w_id, ol_d_id, ol_o_id, COUNT(*) count_ol FROM bmsql_order_line GROUP BY ol_w_id, ol_d_id, ol_o_id) olWHERE o.o_w_id = ol.ol_w_id AND o.o_d_id = ol.ol_d_id AND o.o_id = ol.ol_o_id ) x WHERE o_ol_cnt != count_ol; " cc7=" SELECT /*+ no_use_px parallel(8) */ * FROM ( SELECT /*+ no_use_px parallel(8) */ * FROM (SELECT o.o_w_id, o.o_d_id, o.o_id, o.o_ol_cnt, ol.count_olFROM bmsql_oorder o,(SELECT /*+ no_use_px parallel(8) */ ol_w_id, ol_d_id, ol_o_id, COUNT(*) count_ol FROM bmsql_order_line GROUP BY ol_w_id, ol_d_id, ol_o_id) olWHERE o.o_w_id = ol.ol_w_id AND o.o_d_id = ol.ol_d_id AND o.o_id = ol.ol_o_id ) x WHERE o_ol_cnt != count_ol; "cc7=" SELECT /*+ no_use_px parallel(8) */ * FROM (SELECT /*+ no_use_px parallel(8) */ ol.ol_w_id, ol.ol_d_id, ol.ol_o_id, ol.ol_delivery_d, o.o_carrier_idFROM bmsql_order_line ol, bmsql_oorder oWHERE ol.ol_w_id = o.o_w_id ANDol.ol_d_id = o.o_d_id ANDol.ol_o_id = o.o_id ) x WHERE (ol_delivery_d IS NULL AND o_carrier_id IS NOT NULL) OR(ol_delivery_d IS NOT NULL AND o_carrier_id IS NULL); "cc8=" SELECT /*+ no_use_px parallel(8) */ * FROM (SELECT w.w_id, w.w_ytd, h.sum_h_amountFROM bmsql_warehouse w,(SELECT /*+ no_use_px parallel(8) */ h_w_id, SUM(h_amount) sum_h_amount FROM bmsql_history GROUP BY h_w_id) hWHERE w.w_id = h.h_w_id) x WHERE w_ytd != sum_h_amount; "cc9=" SELECT /*+ no_use_px parallel(8) */ * FROM (SELECT d.d_w_id, d.d_id, d.d_ytd, h.sum_h_amountFROM bmsql_district d,(SELECT /*+ no_use_px parallel(8) */ h_w_id, h_d_id, SUM(h_amount) sum_h_amount FROM bmsql_history GROUP BY h_w_id, h_d_id) hWHERE d.d_w_id = h.h_w_id AND d.d_id = h.h_d_id ) x WHERE d_ytd != sum_h_amount; "cc_list="$cc1|$cc2|$cc3|$cc4|$cc5|$cc6|$cc7|$cc8|$cc9" oldIFS=$IFS IFS="|"counter=0 for sql in $cc_list dolet counter++echo `date '+%F %X'`" cc$counter start"obclient -Dtpcc -h127.1 -P2883 -utpcc@obbmsql#obdemo -p123456 -A -c -e "$sql"#echo $?if [[ $? -ne 0 ]];thenIFS=$oldIFSecho `date '+%F %X'`" cc$counter failed"exit 1fiecho `date '+%F %X'`" cc$counter finished" done IFS=$oldIFSBenchmarkSQL TPC-C場景分析
1. E-R模型
2. 場景SQL
TPC-C 系統(tǒng)需要處理的交易有以下五種：場景名場景描述交易占比New-Order客戶輸入一筆新的訂貨交易45%Payment更新客戶賬戶余額以反應其支付狀況43%Delivery發(fā)貨（批處理交易）4%Order-Status查詢客戶最近交易的狀態(tài)4%Stock-Level查詢倉庫庫存狀況，以便能夠及時補貨。4%場景的比例是在數(shù)據(jù)庫配置文件中定義的。這里是默認值。對于前四種類型的交易，要求響應時間在 5 秒以內；對于庫存狀況的查詢交易，要求響應時間在 20 秒以內。這五種交易作用在圖 1 所示的九張表上，事務操作類型包括更新，插入，刪除和取消操作。
下面是我事先通過OceanBase的全量SQL審計抓出的TPCC的事務SQL(做過去重,但可能不全)。
1）場景1：New-Order
SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 778 AND d_id = 5 FOR UPDATE; SELECT c_discount, c_last, c_credit, w_tax FROM bmsql_customer JOIN bmsql_warehouse ON (w_id = c_w_id) WHERE c_w_id = 778 AND c_d_id = 5 AND c_id = 2699; UPDATE bmsql_district SET d_next_o_id = d_next_o_id + 1 WHERE d_w_id = 778 AND d_id = 5 ; INSERT INTO bmsql_oorder ( o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (5686, 5, 778, 2699, timestamp '2020-01-04 13:49:34.137', 8, 1); INSERT INTO bmsql_new_order ( no_o_id, no_d_id, no_w_id) VALUES (5686, 5, 778); SELECT i_price, i_name, i_data FROM bmsql_item WHERE i_id = 7752 ; -- 循環(huán)8次 SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = 778 AND s_i_id = 7752 FOR UPDATE; -- 循環(huán)8次 SHOW VARIABLES WHERE Variable_name = 'tx_read_only'; UPDATE bmsql_stock SET s_quantity = 47, s_ytd = s_ytd + 8, s_order_cnt = s_order_cnt + 1, s_remote_cnt = s_remote_cnt + 0 WHERE s_w_id = 778 AND s_i_id = 7752; -- 循環(huán)8次 SHOW VARIABLES WHERE Variable_name = 'tx_read_only'; INSERT INTO bmsql_order_line ( ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (5686, 5, 778, 1, 7752, 778, 8, 589.36, 'lYvcNHkOvt3iNoBb5W29umGO'); -- 循環(huán)8次 COMMIT;2）場景2：New-Order
SELECT c_id FROM bmsql_customer WHERE c_w_id = 778 AND c_d_id = 2 AND c_last = 'PRICALLYPRES' ORDER BY c_first; SELECT c_first, c_middle, c_last, c_balance FROM bmsql_customer WHERE c_w_id = 778 AND c_d_id = 2 AND c_id = 2694; SELECT o_id, o_entry_d, o_carrier_id FROM bmsql_oorder WHERE o_w_id = 778 AND o_d_id = 2 AND o_c_id = 2694 AND o_id = ( SELECT max(o_id) FROM bmsql_oorder WHERE o_w_id = 778 AND o_d_id = 2 AND o_c_id = 2694 ); SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d FROM bmsql_order_line WHERE ol_w_id = 778 AND ol_d_id = 2 AND ol_o_id = 4494 ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number; ROLLBACK;3）場景3：Payment
UPDATE bmsql_district SET d_ytd = d_ytd + 4806.11 WHERE d_w_id = 778 AND d_id = 10; SELECT d_name, d_street_1, d_street_2, d_city, d_state, d_zip FROM bmsql_district WHERE d_w_id = 778 AND d_id = 10; UPDATE bmsql_warehouse SET w_ytd = w_ytd + 4806.11 WHERE w_id = 778; SELECT w_name, w_street_1, w_street_2, w_city, w_state, w_zip FROM bmsql_warehouse WHERE w_id = 778 ; SELECT c_id FROM bmsql_customer WHERE c_w_id = 778 AND c_d_id = 10 AND c_last = 'ESEBAROUGHT' ORDER BY c_first; SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = 778 AND c_d_id = 10 AND c_id = 502 FOR UPDATE; UPDATE bmsql_customer SET c_balance = c_balance - 4806.11, c_ytd_payment = c_ytd_payment + 4806.11, c_payment_cnt = c_payment_cnt + 1 WHERE c_w_id = 778 AND c_d_id = 10 AND c_id = 502; INSERT INTO bmsql_history ( h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data) VALUES (502, 10, 778, 10, 778, timestamp '2020-01-04 13:49:34.148', 4806.11, 'HfYovpM6 b6aJtf2Xk6'); COMMIT;4）場景4：
SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 778 AND s_quantity < 10 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 778 AND d_id = 1 ) ); ROLLBACK;5）場景5：
SELECT no_o_id FROM bmsql_new_order WHERE no_w_id = 778 AND no_d_id = 1 ORDER BY no_o_id ASC; DELETE FROM bmsql_new_order WHERE no_w_id = 778 AND no_d_id = 1 AND no_o_id = 4488; UPDATE bmsql_oorder SET o_carrier_id = 2 WHERE o_w_id = 778 AND o_d_id = 1 AND o_id = 4488; SELECT o_c_id FROM bmsql_oorder WHERE o_w_id = 778 AND o_d_id = 1 AND o_id = 4488; UPDATE bmsql_order_line SET ol_delivery_d = timestamp '2020-01-04 13:49:34.181' WHERE ol_w_id = 778 AND ol_d_id = 1 AND ol_o_id = 4488; SELECT sum(ol_amount) AS sum_ol_amount FROM bmsql_order_line WHERE ol_w_id = 778 AND ol_d_id = 1 AND ol_o_id = 4488; UPDATE bmsql_customer SET c_balance = c_balance + 3733.14, c_delivery_cnt = c_delivery_cnt + 1 WHERE c_w_id = 778 AND c_d_id = 1 AND c_id = 1260; <---循環(huán)8次---> commit注意：可能還有事務SQL沒有找到。
3. TPC-C輸出指標
TPC-C 的測試結果主要有兩個指標：
- 流量指標（tpmC）：描述了系統(tǒng)在執(zhí)行 Payment，Order-Status，Delivery，Stock-level 這四種交易的同時，每分鐘可以處理的 New-Order交易的數(shù)量。流量指標值越大越好。
tpm 是 transactions per minute 的簡稱；C 指 TPC 中的 C 基準程序。它的定義是每分鐘內系統(tǒng)處理的新訂單個數(shù)。要注意的是，在處理新訂單的同時，系統(tǒng)還要按圖 1 的要求處理其 它 4 類事務 請求。從圖 1 可以看出，新訂單請求不可能超出全部事務請求的 45％，因此，當一個系統(tǒng)的性能為 1000tpmC 時，它每分鐘實際處理的請求數(shù)是 2000 多個。 - 性價比（Price/tpmC）：測試系統(tǒng)價格與流量指標的比值。性價比越小越好。
運行BenchmarkSQL TPC-C測試
1. OceanBase內存凍結與合并
前面加載了大量數(shù)據(jù)，OceanBase的增量都在內存中，需要做一次major freeze以釋放增量內存。這個事件分兩步。一是凍結操作，這個很快。二是合并操作，這個跟增量數(shù)據(jù)量有關，通常要幾分鐘或者幾十分鐘。每次重復測試的時候都建議做一次major freeze事件以釋放內存，弊端就是隨后測試中內存數(shù)據(jù)訪問又需要一個預熱過程。
1）觀察內存增量使用情況
select tenant_id, ip, round(active/1024/1024) active_mb, round(total/1024/1024) total_mb, round(freeze_trigger/1024/1024) freeze_trg_mb, round(mem_limit/1024/1024) mem_limit_mb, freeze_cnt, round(total/mem_limit,2) total_pct from `gv$memstore` where tenant_id>1001 order by tenant_id;2）發(fā)起內存major freeze事件
ALTER SYSTEM major freeze;3） 觀察合并進度
觀察合并事件
SELECT DATE_FORMAT(gmt_create, '%b%d %H:%i:%s') gmt_create_ , module, event, name1, value1, name2, value2, rs_svr_ip FROM __all_rootservice_event_history WHERE 1 = 1 AND module IN ('daily_merge') ORDER BY gmt_create DESC LIMIT 100;觀察合并進度
select ZONE,svr_ip,major_version,ss_store_count ss_sc, merged_ss_store_count merged_ss_sc, modified_ss_store_count modified_ss_sc, date_format(merge_start_time, "%h:%i:%s") merge_st, date_format(merge_finish_time,"%h:%i:%s") merge_ft, merge_process from `__all_virtual_partition_sstable_image_info` s order by major_version, zone, svr_ip ;2. 跑TPC-C測試
1）運行測試程序
$./runBenchmark.sh props.ob2）性能監(jiān)控
注意：這個監(jiān)控界面重點關注QPS/TPS、以及相應的RT、增量內存的增量和總量占比等。此外還能看出測試過程中還是有不少物理讀IO。
注意：
這個監(jiān)控界面里的重點看各個節(jié)點的QPS和TPS分布，以及遠程SQL的數(shù)量占總QPS的比例(SRC/SLC)。TPC-C業(yè)務定義會有約1%的遠程倉庫交易事務，在OceanBase里這個交易又有一定概率是分布式事務。
3）TPC-C報告
運行結束后會生成結果。
從圖上看，tpmC結果是48204。這個業(yè)務租戶總資源是20C25G*3。倉庫數(shù)只有10倉太少了，如果機器好一點，并做10000倉，這個結果應該會更高。
運行同時還生成了一個文件夾
$ll my_result_2020-01-13_175531/ total 16 drwxrwxr-x 2 admin admin 4096 Jan 13 17:55 data -rw-rw-r-- 1 admin admin 5130 Jan 13 18:10 report.html -rwxr-xr-x 1 admin admin 1050 Jan 13 17:55 run.properties以上就是通過BenchmarkSQL跑TPC-C測試程序的完整過程，感興趣的同學也可以按照上述步驟體驗。有更多反饋歡迎在文章評論區(qū)留言。
總結
以上是生活随笔為你收集整理的python3.6.2用pyinstaller3.4报错_OceanBase 2.2 版本体验：用 BenchmarkSQL 跑 TPC-C的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 给饺子治病是什么结构？
- 下一篇： 福安哪里有卖水果拼盘的地方？