Pangu Cloud Classroom , transaction guarantee , buy with confidence , focus on PG training and learning
applet
PostgreSQL usually performs stress testing through pgbench. Before, we also performed stress testing on postgresql through pgbench. There are many changes in the test indicators, but the results are relatively simple. Therefore, I have not been satisfied with the stress test results of pgbench.
Based on the pressure test of more performance indicators of PostgreSQL and to obtain more data to do related guidance. So this time, we need to pass the sysbench to carry out the stress test. The default installation of sysbench is the first choice for stress testing of MYSQL, and if you use it to perform stress testing on pg, you still need to study it.
The installation is very simple, download the corresponding installation script directly from the official website
wget https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh
sbench/script.rpm.sh
Then in the case of PostgreSQL variable environment, execute the SHELL script to install the sysbnech software. The location of the overall callable script is in the following directory
/usr/share/sysbench/tests/include/oltp_legacy
The following is the lua script contained in this directory
-rw-r--r-- 1 root root 1.2K Apr 24 2020 bulk_insert.lua
-rw-r--r-- 1 root root 4.6K Apr 24 2020 common.lua
-rw-r--r-- 1 root root 366 Apr 24 2020 delete.lua
-rw-r--r-- 1 root root 1.2K Apr 24 2020 insert.lua
-rw-r--r-- 1 root root 3.0K Apr 24 2020 oltp.lua
- rw-r--r-- 1 root root 368 Apr 24 2020 oltp_simple.lua
-rw-r--r-- 1 root root 527 Apr 24 2020 parallel_prepare.lua
-rw-r--r-- 1 root root 369 Apr 24 2020 select.lua
-rw-r--r-- 1 root root 1.5K Apr 24 2020 select_random_points.lua
-rw-r--r-- 1 root root 1.6K Apr 24 2020 select_random_ranges.lua
-rw-r --r-- 1 root root 369 Apr 24 2020 update_index.lua
-rw-r--r-- 1 root root 578 Apr 24 2020 update_non_index.lua
Before stress testing, we need to pay attention to the following configuration issues
1 Confirm the isolation level of the system
2 Confirm the way the wal log is written
3 Confirm and adjust the parameters of checkpoint
4 Adjust the parameters of AUTOVACUUM.
After adjusting some of the database parameters above, adjust the database to a better state. Later, we need to inject test data into our PG database.
Before we fill in the data, we first create a test database, and the test account, database, user, and password are all sbtest
sysbench --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=sbtest --pgsql-password=sbtest --pgsql-db=sbtest --oltp-table -size=200000 --oltp-tables-count=10 --rand-init=on --threads=10 --time=30 --events=0 --report-interval=10 --percentile=99 /usr/ share/sysbench/tests/include/oltp_legacy/oltp.lua prepare
Let's talk a little bit about the parameters here
sysbench \
--db-driver=pgsql \ # Test against POSTGRESQL database
--pgsql-host=127.0.0.1 \ #Connect database address
--pgsql-port=5432 \ #Connect database port number
--pgsql-user=sbtest \ #Database user, preferably with superuser
--pgsql-password=sbtest \
#Password --pgsql-db=sbtest \ #Database name
--oltp-table-size=200000 \ #Number of data rows per table
--oltp -tables-count=10 \ #How many tables are there in a database
--rand-init=on \ #Whether the randomness of the data is turned on
--threads=10 \ #The number of threads that work is parallel
--time=30 \ # How long to test in seconds
--events=0 \ #Whether to limit the amount of data executed by the transaction 0 is
unlimited --report-interval=10 \ #Generate a report every 10 seconds
--percentile=99 \ #For test data Summarize, the percentage of total data occupied by the aggregated data
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \ #Which lua script to use
prepare
It should be noted that the selected script should be consistent from start to test and cannot be modified. The test process is mainly divided into prepare, run, cleanup
The results of the sysbench test are as follows. In fact, compared with pgbench, sysbench has the following advantages
1 Within a limited time, the overall, read operands, write operands, and total operands can be analyzed by testing.
2 It can analyze the number of transactions processed per second, the number of queries processed per second, and the number of operational errors per second based on performance
3 The overall operation delay data can be obtained, including the minimum, maximum, average, and 99TH delay numbers
The above data can have more comparisons with the data of PGBENCH.
In addition, there is also a good choice for the type of data processed by the database in the test. We can provide the following test for the database itself under test
1 The select test contains 3 kinds of tests, ordinary select, random query, range query
2 The update test mainly includes index update and non-index update, which is an important test indicator for PG.
3 oltp common test
4 bulk_insert data insertion test
Compared with the single test method of pgbench, it can also test the performance risks of different types of business types to the database, and obtain some indicators and meaningful guidance in advance.
Then we simply use the following test example to illustrate the advantages of sysbench's stress test for PG. For example, in our test below, we mainly focus on the PG database.
1 PG performance metrics in random queries
2 PG performance metrics for range queries
Actually we also tested the performance gap between non index and index update.
You can see the results below. Obviously, under the same configuration, the performance feedback of PG for different query methods in the query is different. Through such query methods, we can understand that different query methods have different effects on machine performance. loss, and the direction of our post-optimization
sysbench \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=sbtest \
--pgsql-password=sbtest \
--pgsql-db=sbtest \
--oltp-table-size=200000 \
--oltp-tables-count=10 \
--rand-init=on \
--threads=10 --time=30 \
--events=0 \
--report-interval=10 \
--percentile=99 /usr/share/sysbench/tests/include/oltp_legacy/select_random_points.lua \
prepare
sysbench \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=sbtest \
--pgsql-password=sbtest \
--pgsql-db=sbtest \
--oltp-table-size=200000 \
--oltp-tables-count=10 \
--rand-init=on \
--threads=10 --time=30 \
--events=0 \
--report-interval=10 \
--percentile=99 /usr/share/sysbench/tests/include/oltp_legacy/select_random_points.lua \
run
sysbench \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=sbtest \
--pgsql-password=sbtest \
--pgsql-db=sbtest \
--oltp-table-size=200000 \
--oltp-tables-count=10 \
--rand-init=on \
--threads=10 --time=30 \
--events=0 \
--report-interval=10 \
--percentile=99 /usr/share/sysbench/tests/include/oltp_legacy/select_random_points.lua \
cleanup
[ 10s ] thds: 10 tps: 15955.93 qps: 15955.93 (r/w/o: 15955.93/0.00/0.00) lat (ms, 99%): 2.71 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 10 tps: 16033.47 qps: 16033.47 (r/w/o: 16033.47/0.00/0.00) lat (ms, 99%): 2.66 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 16145.02 qps: 16145.02 (r/w/o: 16145.02/0.00/0.00) lat (ms, 99%): 2.66 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 481380
write: 0
other: 0
total: 481380
transactions: 481380 (16042.84 per sec.)
queries: 481380 (16042.84 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 30.0046s
total number of events: 481380
Latency (ms):
min: 0.10
avg: 0.62
max: 28.89
99th percentile: 2.66
sum: 299448.61
Threads fairness:
events (avg/stddev): 48138.0000/14694.89
execution time (avg/stddev): 29.9449/0.02
sysbench \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=sbtest \
--pgsql-password=sbtest \
--pgsql-db=sbtest \
--oltp-table-size=200000 \
--oltp-tables-count=10 \
--rand-init=on \
--threads=10 --time=30 \
--events=0 \
--report-interval=10 \
--percentile=99 /usr/share/sysbench/tests/include/oltp_legacy/select_random_ranges.lua \
prepare
sysbench \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=sbtest \
--pgsql-password=sbtest \
--pgsql-db=sbtest \
--oltp-table-size=200000 \
--oltp-tables-count=10 \
--rand-init=on \
--threads=10 --time=30 \
--events=0 \
--report-interval=10 \
--percentile=99 /usr/share/sysbench/tests/include/oltp_legacy/select_random_ranges.lua \
run
sysbench \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=sbtest \
--pgsql-password=sbtest \
--pgsql-db=sbtest \
--oltp-table-size=200000 \
--oltp-tables-count=10 \
--rand-init=on \
--threads=10 --time=30 \
--events=0 \
--report-interval=10 \
--percentile=99 /usr/share/sysbench/tests/include/oltp_legacy/select_random_ranges.lua \
cleanup
[ 10s ] thds: 10 tps: 4371.54 qps: 4371.54 (r/w/o: 4371.54/0.00/0.00) lat (ms, 99%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 10 tps: 4463.95 qps: 4463.95 (r/w/o: 4463.95/0.00/0.00) lat (ms, 99%): 16.71 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 4514.28 qps: 4514.28 (r/w/o: 4514.28/0.00/0.00) lat (ms, 99%): 16.41 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 133519
write: 0
other: 0
total: 133519
transactions: 133519 (4448.60 per sec.)
queries: 133519 (4448.60 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 30.0122s
total number of events: 133519
Latency (ms):
min: 0.36
avg: 2.25
max: 67.31
99th percentile: 16.71
sum: 299801.49
Threads fairness:
events (avg/stddev): 13351.9000/3937.09
execution time (avg/stddev): 29.9801/0.01
——— ——————————————————————————————
Job Offers:
One senior DBA
1 Possess POSTGRESQL certificate PGCM PGCE, have POSTGRESQL work experience, other database related experience 8-10 years
2 Awareness of the pain points based on POSTGRESQL in application development, which can help develop the characteristics of more effective use of databases in the use of POSTGRESQL
3 Have project experience, can lead relevant personnel to gradually improve the defects and difficulties in the project, and bring the project into the right track.
4 Be able to understand the architectural design thinking in software design, and be able to make rational use of database products in the overall software project.
5 Able to understand business, and the ability to design or optimize the table.
6 Have basic MYSQL knowledge, redis knowledge, ORACLE knowledge (plus points)
We will provide competitive salary
One junior and intermediate DBA
1. Dedicated to work, serious and responsible, have a certain understanding of the database, and have a certain operation and maintenance experience
2. Quick learning ability, master and learn the operation of POLARDB and MYSQL.
3 Can draw inferences from one instance, and generate more insights into the problems that arise
4 Possess REDIS, MONGODB knowledge (plus points)
Working place: Building W11, West Business District, Tianjin Airport
Please send your resume to: