{"id":1838,"date":"2020-07-01T06:13:13","date_gmt":"2020-06-30T22:13:13","guid":{"rendered":"http:\/\/39.97.231.10\/?p=1838"},"modified":"2020-07-01T06:13:13","modified_gmt":"2020-06-30T22:13:13","slug":"benchmarksql-5-0-%e6%94%af%e6%8c%81%e5%af%b9mysql%e7%9a%84tpc-c%e6%b5%8b%e8%af%95","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=1838","title":{"rendered":"benchmarksql 5.0 \u652f\u6301\u5bf9MySQL\u7684TPC-C\u6d4b\u8bd5"},"content":{"rendered":"<div>\n<div>benchmarksql \u57285.0\u7248\u672c\u53bb\u9664\u4e86\u5bf9MySQL\u7684TPC-C\u6d4b\u8bd5\u652f\u6301\uff0c\u672c\u6587\u901a\u8fc7\u4fee\u6539\u90e8\u5206\u6e90\u7801\uff0c\u8ba9benchmarksql 5.0\u652f\u6301MySQL\u3002<\/div>\n<\/div>\n<article>\n<h4>1. \u73af\u5883:<\/h4>\n<ul>\n<li>CentOS 7.2<\/li>\n<li>benchmarksql 5.0<\/li>\n<li>Percona MySQL Server 5.7.19<\/li>\n<\/ul>\n<h4>2. \u4e0b\u8f7d\u7f16\u8bd1benchmarksql\u6e90\u7801<\/h4>\n<p>2.1 \u9996\u5148\u5b89\u88c5java\u5f00\u53d1\u73af\u5883\uff0c\u5177\u4f53\u6b65\u9aa4\u7565\u8fc7\uff0c\u672c\u6587\u6d89\u53ca\u5230\u7684\u64cd\u4f5c\u5728 java 1.8.0 \u73af\u5883\u4e0b\u6d4b\u8bd5\u901a\u8fc7\u3002<br \/>\n2.2 \u5b89\u88c5ant\u5de5\u5177\u3002<br \/>\nyum install ant<br \/>\n2.3 \u4e0b\u8f7dbenchmarksql 5.0 \u6e90\u7801\uff0c\u89e3\u538b\u3002<br \/>\n<a href=\\\"https:\/\/sourceforge.net\/projects\/benchmarksql\/\\\" target=\\\"_blank\\\" rel=\\\"nofollow noopener noreferrer\\\">https:\/\/sourceforge.net\/projects\/benchmarksql\/<\/a><br \/>\ncd benchmarksql-5.0<br \/>\n2.4 \u7f16\u8bd1<br \/>\nant<br \/>\n\u6b64\u65f6\u4f1a\u7f16\u8bd1\u51fa\u4e00\u4e2a\u7248\u672c\u00a0benchmarksql-5.0\/dist\/BenchmarkSQL-5.0.jar\uff0c\u4f46\u662f\u8be5\u7248\u672c\u5e76\u4e0d\u652f\u6301MySQL\u7684TPC-C\u6d4b\u8bd5\uff0c\u9700\u8981\u505a\u5982\u4e0b\u7684\u4fee\u6539\u3002<\/p>\n<h4>3 \u4fee\u6539benchmarksql\u6e90\u7801<\/h4>\n<p>3.1 \u4fee\u6539benchmarksql-5.0\/src\/client\/jTPCC.java\uff0c\u589e\u52a0mysql\u76f8\u5173\u90e8\u5206\uff0c\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n<div>\n<pre>if (iDB.equals(\\\"firebird\\\"))\n        dbType = DB_FIREBIRD;\n    else if (iDB.equals(\\\"oracle\\\"))\n        dbType = DB_ORACLE;\n    else if (iDB.equals(\\\"postgres\\\"))\n        dbType = DB_POSTGRES;\n    else if (iDB.equals(\\\"mysql\\\"))\n        dbType = DB_UNKNOWN;\n    else\n    {\n        log.error(\\\"unknown database type \\'\\\" + iDB + \\\"\\'\\\");\n        return;\n    }\n<\/pre>\n<\/div>\n<p>3.2 \u4fee\u6539benchmarksql-5.0\/src\/client\/jTPCCConnection.java\uff0c SQL\u5b50\u67e5\u8be2\u589e\u52a0\\&#8221;AS L\\&#8221;\u522b\u540d\uff0c\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n<div>\n<pre>default:\n        stmtStockLevelSelectLow = dbConn.prepareStatement(\n            \\\"SELECT count(*) AS low_stock FROM (\\\" +\n            \\\"    SELECT s_w_id, s_i_id, s_quantity \\\" +\n            \\\"        FROM bmsql_stock \\\" +\n            \\\"        WHERE s_w_id = ? AND s_quantity &lt; ? AND s_i_id IN (\\\" +\n            \\\"            SELECT ol_i_id \\\" +\n            \\\"                FROM bmsql_district \\\" +\n            \\\"                JOIN bmsql_order_line ON ol_w_id = d_w_id \\\" +\n            \\\"                 AND ol_d_id = d_id \\\" +\n            \\\"                 AND ol_o_id &gt;= d_next_o_id - 20 \\\" +\n            \\\"                 AND ol_o_id &lt; d_next_o_id \\\" +\n            \\\"                WHERE d_w_id = ? AND d_id = ? \\\" +\n            \\\"        ) \\\" +\n            \\\"    )AS L\\\");\n        break;\n<\/pre>\n<\/div>\n<p>3.3 \u7f16\u8bd1\u4fee\u6539\u540e\u7684\u6e90\u7801\uff0c\u6b64\u65f6\u5f97\u5230\u7684benchmarksql\u7248\u672c\u00a0benchmarksql-5.0\/dist\/BenchmarkSQL-5.0.jar\u00a0\u5df2\u7ecf\u652f\u6301MySQL\u7684TPC-C\u6d4b\u8bd5\u3002<br \/>\ncd benchmarksql-5.0<br \/>\nant<\/p>\n<h4>4 \u4fee\u6539\u76f8\u5173\u811a\u672c\uff0c\u652f\u6301MySQL<\/h4>\n<p>4.1. \u5728benchmarksql-5.0\/run\u76ee\u5f55\u4e0b\uff0c\u521b\u5efa\u6587\u4ef6prop.mysql\uff0c\u5185\u5bb9\u5982\u4e0b\uff1a<\/p>\n<div>\n<pre>db=mysql\ndriver=com.mysql.jdbc.Driver\nconn=jdbc:mysql:\/\/127.0.0.1:3306\/benchmarksql\nuser=benchmarksql\npassword=123456\nwarehouses=1\nloadWorkers=4\nterminals=1\n\/\/To run specified transactions per terminal- runMins must equal zero\nrunTxnsPerTerminal=10\n\/\/To run for specified minutes- runTxnsPerTerminal must equal zero\nrunMins=0\n\/\/Number of total transactions per minute\nlimitTxnsPerMin=300\n\/\/Set to true to run in 4.x compatible mode. Set to false to use the\n\/\/entire configured database evenly.\nterminalWarehouseFixed=true\n\/\/The following five values must add up to 100\n\/\/The default percentages of 45, 43, 4, 4 &amp; 4 match the TPC-C spec\nnewOrderWeight=45\npaymentWeight=43\norderStatusWeight=4\ndeliveryWeight=4\nstockLevelWeight=4\n\/\/ Directory name to create for collecting detailed result data.\n\/\/ Comment this out to suppress.\nresultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS\nosCollectorScript=.\/misc\/os_collector_linux.py\nosCollectorInterval=1\n\/\/osCollectorSSHAddr=user@dbhost\nosCollectorDevices=net_eth0 blk_sda\n<\/pre>\n<\/div>\n<p>4.2. \u4fee\u6539 \u6587\u4ef6\uff1abenchmarksql-5.0\/run\/funcs.sh\uff0c\u6dfb\u52a0mysql \u6570\u636e\u5e93\u7c7b\u578b\u3002<\/p>\n<div>\n<pre>function setCP()\n{\n    case \\\"$(getProp db)\\\" in\n    firebird)\n        cp=\\\"..\/lib\/firebird\/*:..\/lib\/*\\\"\n        ;;\n    oracle)\n        cp=\\\"..\/lib\/oracle\/*\\\"\n        if [ ! -z \\\"${ORACLE_HOME}\\\" -a -d ${ORACLE_HOME}\/lib ] ; then\n        cp=\\\"${cp}:${ORACLE_HOME}\/lib\/*\\\"\n        fi\n        cp=\\\"${cp}:..\/lib\/*\\\"\n        ;;\n    postgres)\n        cp=\\\"..\/lib\/postgres\/*:..\/lib\/*\\\"\n        ;;\n    mysql)\n        cp=\\\"..\/lib\/mysql\/*:..\/lib\/*\\\"\n        ;;\n    esac\n    myCP=\\\".:${cp}:..\/dist\/*\\\"\n    export myCP\n}\n...\u7701\u7565\ncase \\\"$(getProp db)\\\" in\n    firebird|oracle|postgres|mysql)\n    ;;\n    \\\"\\\") echo \\\"ERROR: missing db= config option in ${PROPS}\\\" &gt;&amp;2\n    exit 1\n    ;;\n    *)  echo \\\"ERROR: unsupported database type \\'db=$(getProp db)\\' in ${PROPS}\\\" &gt;&amp;2\n    exit 1\n    ;;\nesac\n<\/pre>\n<\/div>\n<p>4.3 \u6dfb\u52a0mysql java connector\u9a71\u52a8\uff0cmysql-connector-java-5.1.45.jar \u9700\u81ea\u884c\u4e0b\u8f7d\u3002<br \/>\nmkdir -p benchmarksql-5.0\/lib\/mysql<br \/>\ncp mysql-connector-java-5.1.45.jar benchmarksql-5.0\/lib\/mysql\/<br \/>\n4.4 \u4fee\u6539benchmarksql-5.0\/run\/runDatabaseBuild.sh\uff0c\u53bb\u6389extraHistID<\/p>\n<div>\n<pre>AFTER_LOAD=\\\"indexCreates foreignKeys extraHistID buildFinish\\\"\n\u4fee\u6539\u4e3a\uff1a\nAFTER_LOAD=\\\"indexCreates foreignKeys buildFinish\\\"\n<\/pre>\n<\/div>\n<h4>5 \u6d4b\u8bd5MySQL TPC-C<\/h4>\n<p>cd benchmarksql-5.0\/run<br \/>\n.\/runDatabaseBuild.sh props.mysql<br \/>\n.\/runBenchmark.sh props.mysql<br \/>\n\u4fee\u6539\u540e\u7684benchmarksql\u5bf9MySQL\u8fdb\u884cTPC-C\u6d4b\u8bd5\uff0c\u7ed3\u679c\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n<div>\n<pre>02:58:47,071 [Thread-1] INFO   jTPCC : Term-00,\n02:58:47,071 [Thread-1] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 136.71\n02:58:47,071 [Thread-1] INFO   jTPCC : Term-00, Measured tpmTOTAL = 298.81\n02:58:47,071 [Thread-1] INFO   jTPCC : Term-00, Session Start     = 2018-12-27 02:53:46\n02:58:47,071 [Thread-1] INFO   jTPCC : Term-00, Session End       = 2018-12-27 02:58:47\n02:58:47,071 [Thread-1] INFO   jTPCC : Term-00, Transaction Count = 1494<\/pre>\n<\/div>\n<\/article>\n","protected":false},"excerpt":{"rendered":"<p>benchmarksql \u57285.0\u7248\u672c\u53bb\u9664\u4e86\u5bf9MySQL\u7684TPC-C\u6d4b\u8bd5\u652f\u6301\uff0c\u672c\u6587\u901a\u8fc7\u4fee\u6539\u90e8\u5206\u6e90\u7801\uff0c\u8ba9benc &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"http:\/\/www.weijing.co\/?p=1838\"> <span class=\"screen-reader-text\">benchmarksql 5.0 \u652f\u6301\u5bf9MySQL\u7684TPC-C\u6d4b\u8bd5<\/span> \u67e5\u770b\u5168\u6587 &raquo;<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[90,88],"tags":[],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"mailpoet_newsletter_max":false,"woocommerce_thumbnail":false,"woocommerce_single":false,"woocommerce_gallery_thumbnail":false,"shop_catalog":false,"shop_single":false,"shop_thumbnail":false},"uagb_author_info":{"display_name":"weijing","author_link":"http:\/\/www.weijing.co\/?author=4"},"uagb_comment_info":0,"uagb_excerpt":"benchmarksql \u57285.0\u7248\u672c\u53bb\u9664\u4e86\u5bf9MySQL\u7684TPC-C\u6d4b\u8bd5\u652f\u6301\uff0c\u672c\u6587\u901a\u8fc7\u4fee\u6539\u90e8\u5206\u6e90\u7801\uff0c\u8ba9benc&hellip;","_links":{"self":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1838"}],"collection":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1838"}],"version-history":[{"count":0,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1838\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1838"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1838"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1838"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}