Performance is one of the main concerns for DBAs (database administrators). There are several standard database benchmarks (eg. TPC-C, TPC-H, ...). EMP7 is not a standard benchmark... but it is a very simple to use and very useful for database performance prediction. It performs a CPU intensive SQL query. EMP7 can be used to produce an intense database load or to measure database/processor/system performances. Set up and test time is generally less than one minute and there are scripts ready to run for Oracle, MySQL, PostgreSQL, SQLite, DB2, Informix, ... and more.
EMP7 is very easy to use. With the Oracle demo database, You have to connect with SCOTT/TIGER, set timing on, and execute a select count(*) from emp, emp, ... 7 times! The EMP table has 14 rows, using a cartesian product for seven times gives as result 147= 105.413.504 about one hundred million records: perfect for a test!
The standard EMP7 query is:
SELECT COUNT(*) FROM emp emp1, emp emp2, emp emp3, emp emp4, emp emp5, emp emp6, emp emp7 WHERE emp7.deptno=10;
There are several scripts already available for most used RDBMS: Oracle, MySQL, PostgreSQL, SQLite, DB2, Informix, JDBC (Grider/Jython script), Sysbench (Lua script), Hadoop Pig (uses this data file), ClickHouse, ...
This PDF REPORT contains the results of the EMP7 test over a large set of systems and databases. If You want to publish the results of Your test send me an email.
Using EMP7 is straightforward. Download it and run the script for Your database! It generally takes less than one minute (eg. 5 second for MySQL on a recent x86_64 processor).
A bit more complex can be calculating the throughput of a whole system. This require to start several sessions of the EMP7. Notice that each session should be sampled indipendently: on many recent processors different threads have different speeds. The variance of test results must be checked too. In the following picture You see EMP7 results on several Virtual Machines hosted on the same Hypervisor. Notice the high variance when the concurrent sessions count is not a multiple of CPU.
The results obtained with EMP7 close predict performances for CPU intensive database applications.
This is true for complex applications and architectures too.
EMP7 tests are very useful in Rightsizing, System Consolidation, Hardware or Software Upgrade,
Database Migration, Virtualization, ...
With EMP7 You do not need a crystal ball to predict batch performace gains after an hardware upgrade: a couple of test, a bit of experience, a multiplication and You are done!
EMP7 CAN NOT be used to compare different database or different database versions. The execution time greatly depends on the join algoritm and on the strange query used by the benchmark. Generally more recent versions are faster (eg. with Oracle by an order of magnitude between an old Oracle 8i and a new Oracle 11R2). Generally the results are not so far between two different RDBMS. EMP7 is a CPU bonud test and must be executed on a idle system.
EMP7 benchmarks are easy to perform and are good indicators of CPU power as seen by the RDBMS. The benchmark can be reduced to have less impact on slower devices or databases (eg Smartphone: EMP7 on SQLite for Android).
A smaller, faster version of EMP7 is EMP5. EMP5 handles about 500.000 records. It's ideal for slower processors like the ones used in Smartphone (eg ARM low power comsumption processors). EMP5 uses 5 cartesian products of the EMP table. Result can be easly converted and compared like in this PDF REPORT.
EMP7 scripts can be downloaded from Sourceforge. EMP7 license is GNU based.
Date: 1 April 2005
Version: 1.1.5 - 14 February 2017