[Oracle] using open-source tools to implement your own TPC-C

[Oracle] How to use open source tools to implement your own TPC-C

By fenng
Date: Mar 14 2004 (v0.13)
Site: http: // www.dbanotes.net

Introduction This paper introduces how to use open-source tools to realize TPC-C in Oracle database environment, which has some reference value for DBA, developers and Bi implementers.. Description Imagine a situation like this: You are in charge of an OLTP database project of the company and are facing the problem of Database Server Selection: How to Select hardware to meet the business requirements of the product environment? Can this problem be determined by shoot your head? It is undeniable that hardware selection is an art. Choosing the right hardware can save the company a lot of money, and choosing a server product that cannot meet business needs will not only waste money, it will also have an impact on future business. You may first think of reference server TPC-C information. Speaking of TPC-C, many people may feel both familiar and unfamiliar with this, because often listen to people, but the specific ask, can clearly say that not many people, and, the results are not the same. So what does TPC/TPC-C mean? What is TPC? The transaction processing performance Council (TPC) is a non-profit organization with an official site in http://www.tpc.org. Its purpose is to develop standard specifications for the business application benchmark program (benchmark), provide performance and price measurements, and manage the release of test results. It is extremely authoritative in the industry. Standard Specification is open to the public and can be obtained on the official site, but TPC does not provide the code of the benchmark program. Any software and hardware vendor or other testers can build their own systems to be tested according to standard specifications. To ensure the objectivity of the test results, the tester (usually the manufacturer) must submit a complete report (full disclosure report) to the TPC ), including detailed configuration, classification price, and total price of the tested system, including the five-year maintenance fee. The report must be verified by a TPC-authorized Auditor (TPC itself does not perform an audit ). What is tpmc? Let's start with the TPC-C, which is the benchmark program for online transaction processing (on-line transaction processing, OLTP. The TPC-C simulates a wholesaler's cargo management environment. The TPC-C uses three performance and price measurements, where performance is measured by the TPC-C throughput, measured in tpmc. TPM is short for transactions per minute; C refers to the C benchmark program in the TPC. It defines the number of new orders processed by the system per minute. After learning about this, you may think: Our company's PC server is fully self-collected, how to measure the TPC-C? Also refer to the information on the TPC official site? Don't forget that the test results published on tpc.org are all the results that major vendors have optimized based on their systems. For example, the Value published by TPC is like the speed value that the brand new Ferrari sports car ran out of the dedicated runway after careful inspection and maintenance by professional technicians, however, it may be that Ferrari (or even xiali) is driving on the rural roads, which is inferior to each other. Some may think: We can test it on the "Rural Land" with a stopwatch based on our own situation. This is also obvious. Isn't it more valuable to our actual environment? Yes, this is exactly the purpose of this Article: Let's use open source to implement our own TPC-C for Oracle databases. Hammerora-the Open Source Oracle Load Test Tool First, we will introduce hammerora. The author's Title Description of this tool shows that hammerora is an Oracle database load testing tool. This tool can be used in Oracle 8i, 9i, and 10g versions. Currently, it can be used in Linux, UNIX, and windows. It has initially implemented cross-platform. hammerora is completely written in Tcl/TK and published in the form of GPL copyright. The author is Steve Shaw. The project home page is in: http://sourceforge.net/projects/hammerora.   Figure 1: hammerora logo   Hammerora is designed to parse Oracle trace files and convert them into oratcl programs. Then, in the Oracle database, multiple users concurrently play back user transactions ". We know that the Oracle trace file can accurately record user operations on the database. In this way, hammerora can simulate the user environment. Hammerora was initially used to learn: expected to be a certain number of users simulating databases. Such a small family-based Learning Development System can be closer to the product environment. This tool has grown into an Oracle database for stress testing-from small databases to large Rac-based clusters. The TCL language is already used to provide high-performance OCI integration with Oracle databases, without the need to re-compile programs. Each generated program has strict Tcl/oratcl compatibility and can be run in the command line shell of hammerora. The powerful functions and flexibility of TCL mean that potential functions are endless. The TCL thread package is used to implement multiple concurrent connections to the Oracle database. Hammerora contains Oracle stored procedures and Tcl driver scripts used to run TPC-C baselines. However, we need to build a database that matches the TPC-C specifications first. In this way, we need to use orabm. Orabm Orabm is Geoff Ingram (High-performance ORACLE: proven methods Achieving optimum performance and availabilitYA package Built by the author of this book. It contains a set of SQL scripts and several command line programs. Orabm data load builds a set of data that complies with TPC-C standards for testing. Orabm's package and source code can be obtained from its home page http://www.linxcel.co.uk/orabm. Orabm Operating Mechanism The running mechanism of orabm is clearly described in Geoff Ingram. orabm is translated as follows) the number of database transactions executed by the user. the transaction runs through the orabm_serverside_stress stored procedure in orabm mode. For each concurrent session, orabm_serverside_stress runs the specified number of transactions in the orabm command line and returns the TPS value of the session when the sampling interval is completed. make sure that all concurrent sessions process transactions during the sampling interval. The TPS value only includes 80% of the middle of the transaction, that is, the initial 10% and the last 10% will be ignored. For more information, see http://www.linxcel.co.uk/orabm. Installation Steps 1Install Oracle OCI Library For Oracle 9i, if this component is not installed, you may need to run runinstall again. In the Oracle 9i client products tree, select "Oracle call interfaces ". For Oracle 10 Gb, the Oracle 10 OCI library should be installed by default. 2Install TCL CD/usr/local/src/tcl8.4.5/Unix Rm-F config. Cache ./Configure -- enable-threads -- enable-shared Make clean Make Make install 3Install TK CD/usr/local/src/tk8.4.5/Unix Rm-F config. Cache ./Configure -- With-tcl =.../../tcl8.4.5/Unix -- enable-threads -- enable-shared Make clean Make Make install 4Install TCL threads CD/usr/local/src/thread2.5.2/Unix Rm-F config. Cache Sh ../configure -- enable-threads -- enable-shared Make clean Make Make install 5Install oratcl For 10 Gb, to edit the configuration file, add the following content under the line of found_oracle = 0 near row 1728: Echo "checking for Oracle 10.0 style toplevel (libclient10.a)" 1> & 6 Echo "Configure: 1730: checking for Oracle 10.0 style toplevel (libclient10.a)"> & 5 For f in $ oracle_directory $ oratest1 $ oratest2 $ oratest3 $ oracle4/opt/Oracle/u/Oracle/usr/local/Oracle; do Echo "$ ac_t" "looking for lib/libclient10.a in $ F" 1> & 6 If test-R "$ F/lib/libclient10.a"; then Ora = "$ F/lib" ORACLE_HOME = $ F Echo "$ ac_t" "setting Ora to $ ora" 1> & 6 Testver = 10 Ora_major_version = 10 Ora_minor_version = 0 Found_oracle = 1 Break Fi Done Export ORACLE_HOME = your_oracle_home_directory CD/usr/local/src/oratcl-4-1-branch/Unix Rm-F config. Cache Sh .. /configure -- With-tcl = .. /.. /tcl8.4.5/Unix -- With-Oracle-directory = $ ORACLE_HOME -- With-Oracle-version = 9 (or 10 or 8! ) -- Enable-threads -- enable-shared Note: The red part indicates the adjustments required for different database versions. VI makefile-change all-rpath to-wl,-rpath, Make clean Make Make install Sqlplus orabm/orabm @ delivery_sp. SQL Sqlplus orabm/orabm @ new_order_sp. SQL. Sqlplus orabm/orabm @ ostat_sp. SQL Sqlplus orabm/orabm @ payment_sp. SQL Sqlplus orabm/orabm @ slev_sp. SQL   Procedure Operation Command 1 Create an orabm user (use tools as the default tablespace and temp as the temporary tablespace) Sqlplus system/pwd @ orabm_user 2 Create a table Sqlplus system/pwd @ orabm_tab 3 Load data $ Orabmload Warehouses 1 4 Create an index Sqlplus system/pwd @ orabm_ind 5 Analysis table and Index Sqlplus system/pwd @ orabm_analyze 6 Create a stress test PL/SQL Process Sqlplus system/pwd @ orabm_serverside_stress 7 Buffer table and index data to SGA Sqlplus system/pwd @ orabm_cache Load the tpc-c.tcl script into hammerora (remember to change the file filter from *. TRC *. TCL to see TCL files), find the connect string in the tpc-c.tcl and adjust your database user connection information. And adjust the virtual user information. Test Run Run hammerora and create the number of users needed to simulate the TPC-C baseline Program (Be sure to adjust the environment variable for executing the hammerora program, it is best to run it directly with oracle users ). for the data obtained from my laptop [I checked the machine performance]: Figure 2 hammerora test result example   References Home page for http://hammerora.sourceforge.net/hammerora. The current software version is 1.15. Http://www.linxcel.co.uk/orabm/ Geoff Ingram maintained personal sites. This site also has an interesting tool orastress for Oracle Database stress testing. Http://www.tpc.org TPC official site. Http://www.cnoug.org/viewthread.php? Tid = 63 technical expert rudolflu has introduced orabm a long time ago. Http://www.dbanotes.net/Oracle/Install-Oracle10g-RHEL3.htmInstalling Oracle 10g on RHEL as 3 step-by-step Author Fenng, a DBA of a US-funded company, enjoys full use in database-related technical forums in his spare time. Currently, we focus on how to use Oracle databases to effectively build enterprise applications. I have a research on Oracle tuning and troubleshooting. Personal technical site: http://www.dbanotes.net /. You can contact him by email dbanotes@gmail.com. Source Http://www.dbanotes.net/Oracle/Use_OpenSourceTool_implement_YourOwn_TPC-C.htm Home Page All articles (by fenng) are licensed under a Creative Commons license. I wocould welcome any feedback. Please send questions, comments or corrections to dbanotes@gmail.com