The core application business of our industry is currently still a traditional OLTP business. The application system is developed using Java, and it is not recommended to use stored procedures. It is the most fair to use benchmarksql to pressure test the database. It can not only test the database performance, but also test the JDBC driver.
1. Key points of tpmC performance testing
1. Traffic indicators (Throughput, referred to as tpmC)
According to the definition of the TPC organization, the traffic indicator describes how many new order transactions can be processed per minute while the system performs four transactions: payment operations, order status query, shipment and inventory status query. The response time of all transactions must meet the requirements of the TPC-C test specification, and the proportion of various transactions should also meet the requirements of the TPC-C test specification. In this case, the larger the traffic metric value indicates the higher the online transaction processing capability of the system.
2. Test duration
The longer benchmarksql runs, the larger the database will be. Generally speaking, when the database size exceeds 3 times the database shared cache, the performance will begin to decline significantly.
3. CPU load
You can use htop to monitor the CPU utilization of the database server and tpcc client. Under the best performance test, the occupancy rate of each service CPU should be as high as possible. If the CPU occupancy rate does not meet the standard, it may be because the kernel binding method is incorrect or other problems, and CPU binding technology can be used.
4、warehouses
Specify the number of warehouses, and the number of warehouses determines the performance test results. If higher test results are expected, the number of warehouses cannot be too low. For production environment machine testing, it is recommended to start with a 5000 warehouse (5000 warehouse is not very consistent with our business scenario, it is recommended to start with 100 warehouses).
5、terminals
Specify the number of concurrency during performance pressure measurement. It is recommended that the number of concurrency should not be higher than the number of warehouses * 10. Otherwise, there will be unnecessary locks waiting. In a production environment, setting the concurrency to 1000 is very high. General environmental testing is recommended to start at 100.
6、runMins
Specify the duration of performance testing. The longer the time, the more it can test the performance and stability of the database. It is recommended not to be less than 10 minutes, and it is recommended to be no less than 1 hour for machines in production environments.
2. Test steps
1. benchmarksql download
wget /MapLover/benchmarksql5.1/repository/archive/
2、Requirements
■ Configure ant java
yum install -y ant java
If ant cannot be installed, download ant after installing java
/Confirm the latest version of ant
/apache/ant/binaries/apache-ant-1.10.
Only the bin and lib directories are required to run Ant, install Ant, select a directory and copy the distribution file to that directory, this directory will be called ANT_HOME
Before running Ant, some additional settings are required unless the RPM version is installed
1. Add the bin directory to the path
2. Set the ANT_HOME environment variable to the directory where Ant is installed
3. Optionally, set the JAVA_HOME environment variable
ln -s /u01/nfs/soft/apache-ant-1.10.14 /opt/ant
vim /etc/profile
export JAVA_HOME=/u01/app/oracle/product/19.3.0/db_1/jdk
export ANT_HOME=/opt/ant
export PATH=$JAVA_HOME/bin:$ANT_HOME/bin:$PATH
■ Configure python
When collecting performance information such as CPU, disk, network, etc., you need to call the python program. The supported python version is 2. If you use 3 version, an error will be reported:
2024-07-31 15:15:52: osCollectorScript=./misc/os_collector_linux.py
2024-07-31 15:15:52: osCollectorInterval=1
2024-07-31 15:15:52: osCollectorSSHAddr=null
2024-07-31 15:15:52: osCollectorDevices=net_ens39 blk_sda
2024-07-31 15:15:52: Term-00,
File "<stdin>", line 63
print ",".join([str(x) for x in sysInfo])
^
SyntaxError: invalid syntax
2024-07-31 15:15:52: OSCollector, unexpected EOF while reading from external helper process
Because information collection is performed on the database side, download
/python/2.7.18/Python-2.7.
Decompression and configuration:
./configure --enable-optimizations --prefix=/usr/local/python2.7/
Modify the configuration file:
vim Modules/Setup
_ssl _ssl.c
-DUSE_SSL -I$(SSL)/include -I$(SSL)/include/openssl
-L$(SSL)/lib -lssl -lcrypto
By default, this is annotated, and you can open it if you let the comments go. This function is to enable the SSL module, otherwise there will be an error that the ssl module cannot be found after installation.
Compile and install:
make -j8 && make install
Create a new soft chain:
mv /usr/bin/python /usr/bin/
ln -s /usr/local/python2.7/bin/python2.7 /usr/bin/python
python -V
Compile the BenchmarkSQL source code
Finally, BenchmarkSQL can be compiled:
[root@BC86 benchmarksql5.1-master]# ant
Buildfile: /u01/nfs/soft/benchmark/benchmarksql5.1-master/
init:
[mkdir] Created dir: /u01/nfs/soft/benchmark/benchmarksql5.1-master/build
compile:
[javac] Compiling 11 source files to /u01/nfs/soft/benchmark/benchmarksql5.1-master/build
dist:
[jar] Building jar: /u01/nfs/soft/benchmark/benchmarksql5.1-master/dist/BenchmarkSQL-5.
BUILD SUCCESSFUL
---If you encounter the following error, you need to configure JAVA_HOME:
BUILD FAILED
/u01/nfs/soft/benchmarksql5.1-master/:24: Unable to find a javac compiler;
is not on the classpath.
Perhaps JAVA_HOME does not point to the JDK.
It is currently set to "/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.312.b07-11.oe2203.aarch64/jre"
Create the benchmark configuration file
Create a configuration file for the test:
cd run
cp my_postgres.properties
vim my_postgres.properties
---
db=postgres
driver=
conn=jdbc:postgresql://192.168.10.108:5432/postgres
user=postgres
password=passwdsefsdfsdfsdf
// Each W is about 100MB, then 100 are about 10G
warehouses=5
loadWorkers=5
//The number of terminals, that is, the number of concurrent clients, is usually set to 2 to 6 times the total number of CPU threads
terminals=5
//To run specified transactions per terminal- runMins must equal zero
//The fixed number of transactions run by each terminal. If the value is 10, each terminal will run 10 transactions. If there are 32 terminals, the test will be completed after running 320 transactions as a whole. When this parameter is configured as a non-0 value, the following runMins parameter must be set to 0.
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
//runMins indicates the length of time to be measured in minutes. When this value is a non-0 value, the runTxnsPerTerminal parameter must be set to 0. These two parameters cannot be set to positive integers at the same time. If one of them is set, the other must be 0. The main difference is that runMins defines the time length to control the test time; runTxnsPerTerminal defines the total number of transactions to control the time.
runMins=5
//Number of total transactions per minute
limitTxnsPerMin=10000000
//Set to true to run in compatible mode. Set to false to use the
//entire configured database evenly.
terminalWarehouseFixed=true
//The following five values must add up to 100
//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
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=pg_result_%tY-%tm-%td_%tH%tM%tS
osCollectorScript=./misc/os_collector_linux.py
osCollectorInterval=1
//[email protected]
osCollectorDevices=net_ens39 blk_sda---The following official documents are for reference:
Note that the provided example configuration is meant to test the functionality of your setupr. That benchmarksql can connect to the database and execute transactions. [■■■That configuration is NOT a benchmark run.■■■] To make it into one you need to have a configuration that matches your database server size and workload. Leave the sizing for now and perform a first functional test.
The BenchmarkSQL database has an initial size of approximately 100-100MB per configured warehouse. A typical setup would be a database of 2-5 times the physical RAM of the server.
Likewise the number of concurrent database connections (config parameter terminals) should be something about 2-6 times the number of CPU threads.
Last but not least benchmark runs are normally done for hours, if not days. This is because on the database sizes above it will take that long to reach a steady state and make sure that all performance relevant functionality of the database, like checkpointing and vacuuming, is included in the measurement.
So you can see that with a modern server, that has 32-256 CPU threads and 64-512GBi, of RAM we are talking about thousands of warehouses and hundreds of concurrent database connections.
Build the schema and initial database load
Create schema and initialize database:
./ my_postgres.properties > load_`date '+%Y-%m-%d-%H%M'`.log 2>&1 &
tail -100f load_*.log
If the oracle database is used, you can create an awr snapshot before performing the test.
exec dbms_workload_repository.create_snapshot();
Run the configured benchmark
Perform a test:
./ my_postgres.properties > run_`date '+%Y-%m-%d-%H%M'`.log 2>&1 &
tail -100f run_*.log
The benchmark should run for the number of configured concurrent connections (terminals) and the duration or number of transactions.
If oracle executes the test, generate another awr snapshot and then obtain the awr report during the test
exec dbms_workload_repository.create_snapshot();
Scale the benchmark configuration.
Change the my_postgres.properties file to the correct scaling (number of warehouses and concurrent connections/terminals). Switch from using a transaction count to time based:
runTxnsPerTerminal=0
runMins=5
Rebuild the database
Recreate the test library:
./ my_postgres.properties
./ my_postgres.properties
In order to facilitate multiple tests and reduce the time for importing data, you can stop the database and perform a copy of the entire data directory to backup the database.
Result report
Generate a database test report:
./ pg_result_2024-07-31_195621
When generating a report, if the prompt "Cannot open the link to X11 display", you need to enable the vncserver service
Configure R
wget /CRAN/src/base/R-4/R-4.4.
./configure
make -j18
make install
---configure If there are many errors, install whatever is missing:
yum install -y gcc gcc-c++ gcc-gfortran readline-devel
yum install -y gcc-gfortran
If an error is reported: configure: error: --with-x=yes (default) and X11 headers/libs are not available
yum install -y libX11-devel libXt-devel
yum install -y zlib-devel bzip2-devel
If an error is reported: liblzma library and headers are required
yum install -y xz-devel
yum install -y pcre2-devel
If an error is reported: configure: error: libcurl >= 7.28.0 library and headers are required with support for https
yum install -y libcurl-devel
■ Solutions that do not support png
yum install -y libpng-devel libtiff-devel libjpeg-turbo-devel pango-devel
Just configure again!
The correct result should be png TRUE, bitmapType is cairo, as follows:
R
capabilities()
> capabilities()
jpeg png tiff tcltk X11 aqua
TRUE TRUE TRUE FALSE TRUE FALSE
http/ftp sockets libxml fifo cledit iconv
TRUE TRUE FALSE TRUE TRUE TRUE
NLS Rprof profmem cairo ICU
TRUE TRUE FALSE FALSE FALSE TRUE
libcurl
TRUE
getOption("bitmapType")
[1] "cairo"
■ The following is the configuration to support png using Cairo [optional]
wget /CRAN/src/contrib/png_0.
wget /CRAN/src/contrib/Cairo_1.
("Cairo_1.",repos=NULL)
Error: configure: error: Cannot find !
yum install -y cairo* libxt*
("png_0.",repos=NULL)
library(Cairo)
library(png)
()
---
png jpeg tiff pdf svg ps x11 win
TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE
raster freetype harfbuzz
TRUE TRUE FALSE