Experiment introduction:
YCSB (Yahoo! Cloud Serving Benchmark), this performance testing tool is written in Java (this is also mentioned in the previous MC article, if you haven’t read it, you can take a look of the previous one), mainly a database performance testing tool for cloud or server-side, which internally covers common NoSQL database products, such as Cassandra, MongoDB, HBase, Redis, etc.
MySQL Database Service is a fully managed database service, it can deploy cloud-native applications using the world’s most popular open-source database. It is 100% developed, managed and supported by MySQL.
Experimental Materials:
MySQL/MySQL source code
One SG2042 server (HS-2)
TPCC source code
YCSB source code
JDK (this tutorial uses the JDK 11 that comes with the system)
If you use the Fedora 38 provided by SOPHGO official, The version of OpenJDK should be 20
Experiment Procedure:
Install MySQL:
Method 1: Install directly through apt
sudo apt install mysql-server
Method 2: Compile and install (this way may not work)
Download source code
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.34.tar.gz
If wget is too slow, you can download it to the computer in advance and then transfer it to the server.
tar -xzvf mysql-8.0.34.tar.gz
cd mysql-8.0.34/
mkdir build
cd build/
Unzip and enter
Since MySQL requires boost, downloading boost directly is slow, we choose to download boost manually.
wget https://boostorg.jfrog.io/artifactory/main/release/1.77.0/source/boost_1_77_0.tar.gz
Unzip
tar -xzvf boost_1_77_0.tar.gz
Go back to the mysql build folder:
cd mysql-8.0.34/build/
Run cmake
sudo cmake .. -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci
-DENABLED_LOCAL_INFILE=ON -DWITH_SSL=system
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql/server -DMYSQL_DATADIR=/usr/local/mysql/data
-DMYSQL_TCP_PORT=3306 -DDOWNLOAD_BOOST=0
-DWITH_BOOST=/home/perfxlab01/boost_1_77_0/
Compile and install
sudo make -j64
The result suggested that the code on SHM_Buffer.hpp reported an error, and later found that RISC-V platform compilation was not supported at that time, so it is recommended to install mysql through the package manager.
Note, if you have uninstalled and reinstalled mysql before, if the sock cannot be accessed, please refer to:
If it prompts that there is an error in mysql.cnf, just execute the following command to fix it.
sudo touch /etc/mysql/mysql.cnf（rebuild mysql.cnf file）
sudo dpkg --configure -a（reconfiguration）
Initialization settings:
sudo mysql_secure_installation
Found no permission
enter mysql
sudo mysql
Execute the following command:
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password by ‘[set your password]’;
Then exit the mysql shell
Run the YCSB test
Get YCSB
wget https://hub.yzuu.cf/brianfrankcooper/YCSB/releases/download/0.17.0/ycsb-0.17.0.tar.gz
Or
wget https://github.com/brianfrankcooper/YCSB/releases/download/0.17.0/ycsb-0.17.0.tar.gz
If the wget download is too slow, you can first use the multi-threaded download tool on the computer to download to the computer, and then upload it to the server.
After transferring to the server, unzip:
tar -xzvf ycsb-0.17.0.tar.gz
Enter the YCSB folder
cd ycsb-0.17.0/
Since YCSB is written in Java, we also need JDK. However, the system has already provided JDK11 in advance, so there is no need to install JDK additionally.
Log in to the database and create database and tables:
create database ycsb;
use ycsb;
CREATE TABLE usertable (
YCSB_KEY VARCHAR(255) PRIMARY KEY,
FIELD0 TEXT, FIELD1 TEXT,
FIELD2 TEXT, FIELD3 TEXT,
FIELD4 TEXT, FIELD5 TEXT,
FIELD6 TEXT, FIELD7 TEXT,
FIELD8 TEXT, FIELD9 TEXT
);
Edit db.properties
cd jdbc-binding/conf/
mv db.properties db.properties.bak
vim db.properties
The content of db.properties is as follows
db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/ycsb
db.user=root
db.passwd=[your password]
Download the necessary database driver
wget https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-j-8.0.33.tar.gz
Unzip and install the driver to the specified folder:
tar -xzvf mysql-connector-j-8.0.33.tar.gz
cp mysql-connector-j-8.0.33/mysql-connector-j-8.0.33.jar ~/ycsb-0.17.0/jdbc-binding/lib/
Load data
bin/ycsb load jdbc -P workloads/workloada -P ./jdbc-binding/conf/db.properties
-cp ./jdbc-binding/lib/mysql-connector-j-8.0.33.jar
Then there is the running time to get the scores:
bin/ycsb run jdbc -P workloads/workloada -P ./jdbc-binding/conf/db.properties
-cp ./jdbc-binding/lib/mysql-connector-j-8.0.33.jar
(The above two steps take a long time, you can make a cup of coffee or watch a movie while waiting)
If a Python error is prompted, it is because YCSB uses Python 2 code. Although someone tried to merge Python 3 support with the main repository, it has never been merged (0.17.0 still uses Python 2).
Install python2 first
sudo apt install python2
change link
sudo rm /usr/bin/python
sudo ln -s /usr/bin/python2 /usr/bin/python
test:
python --version
If it prompts Python 2.7.18, it means that the link has been successfully changed to python 2.
Then try to load the data again and run the score
Running score result:
[OVERALL], RunTime(ms), 1180970
[OVERALL], Throughput(ops/sec), 84.67615604122035
[TOTAL_GCS_Copy], Count, 127
[TOTAL_GC_TIME_Copy], Time(ms), 2214
[TOTAL_GC_TIME_%_Copy], Time(%), 0.18747300947526185
[TOTAL_GCS_MarkSweepCompact], Count, 6
[TOTAL_GC_TIME_MarkSweepCompact], Time(ms), 526
[TOTAL_GC_TIME_%_MarkSweepCompact], Time(%), 0.044539658077681905
[TOTAL_GCs], Count, 133
[TOTAL_GC_TIME], Time(ms), 2740
[TOTAL_GC_TIME_%], Time(%), 0.23201266755294375
[READ], Operations, 49947
[READ], AverageLatency(us), 1485119.70881134
[READ], MinLatency(us), 20192
[READ], MaxLatency(us), 66584575
[READ], 95thPercentileLatency(us), 3217407
[READ], 99thPercentileLatency(us), 3725311
[READ], Return=OK, 49947
[CLEANUP], Operations, 100
[CLEANUP], AverageLatency(us), 207285.48
[CLEANUP], MinLatency(us), 4828
[CLEANUP], MaxLatency(us), 6111231
[CLEANUP], 95thPercentileLatency(us), 999935
[CLEANUP], 99thPercentileLatency(us), 1299455
[UPDATE], Operations, 50053
[UPDATE], AverageLatency(us), 609746.7518030887
[UPDATE], MinLatency(us), 22224
[UPDATE], MaxLatency(us), 2666495
[UPDATE], 95thPercentileLatency(us), 1309695
[UPDATE], 99thPercentileLatency(us), 1539071
[UPDATE], Return=OK, 50053
Summarization:
Judging from the running score results, SG2042 is relatively good in database applications. However, since YCSB uses Python 2, and Python 2 has been stopped supporting on January 1, 2020, we hope that YCSM will migrate to Python 3 as soon as possible.
References:
MySQL Chinese official website:
jdbc documentation for YCSB
Port ycsb to Python3 and also make it Python2 compatible #1421 (Github)
MySQL server fails to build on RISC-V 64
Translated From: RISC-V公测平台发布 · 使用YCSB测试SG2042上的MySQL性能