Facebook LinkBench Tests PostgreSQL Social Relation Profile Scenario Performance

Background

LinkBench is an open-source database benchmark developed by Google to evaluate database performance. LinkBench creates a set of test data around a social graph and then perform data operations such as querying, adding or disconnecting relationships.

Test Model of LinkBench

This data can be represented in a social graph, where objects (graph nodes) such as people, posts, comments, and pages are connected by associations (directed edges of the graph) that model different relationships between the nodes.

Introduction to LinkBench for PostgreSQL

The actual benchmark is driven by the LinkBench driver, a Java program that generates the social graph and the operation mix.

mkdir ~/app  
cd ~/app
cd ~  
http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
get
Java SE Development Kit 8u102
Linux x64 173.03 MB jdk-8u102-linux-x64.tar.gz

tar -zxvf jdk-8u102-linux-x64.tar.gz
mv jdk1.8.0_102 /home/digoal/app/
http://maven.apache.org/download.cgi  

wget http://mirrors.cnnic.cn/apache/maven/maven-3/3.3.9/binaries/apache-maven-3.3.9-bin.tar.gz
tar -zxvf apache-maven-3.3.9-bin.tar.gz
mv apache-maven-3.3.9 /home/digoal/app/
export JAVA_HOME=/home/digoal/app/jdk1.8.0_102  
export PATH=/home/digoal/app/apache-maven-3.3.9/bin:/home/digoal/app/jdk1.8.0_102/bin:$PATH
export LD_LIBRARY_PATH=/home/digoal/app/apache-maven-3.3.9/lib:$LD_LIBRARY_PATH
git clone https://github.com/mdcallag/linkbench
$ cd linkbench  

$ mvn clean package -P pgsql -D skipTests

[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 5.146 s
[INFO] Finished at: 2016-09-11T13:07:55+08:00
[INFO] Final Memory: 39M/1582M
[INFO] ------------------------------------------------------------------------
# append  
export JAVA_HOME=/home/digoal/app/jdk1.8.0_102
export PATH=/home/digoal/app/linkbench/bin:/home/digoal/app/apache-maven-3.3.9/bin:/home/digoal/app/jdk1.8.0_102/bin:$PATH
export LD_LIBRARY_PATH=/home/digoal/app/apache-maven-3.3.9/lib:$LD_LIBRARY_PATH
export CLASSPATH=.:/home/digoal/app/linkbench/target/FacebookLinkBench.jar
Using java at: /home/digoal/app/jdk1.8.0_102/bin/java  
Did not select benchmark mode
usage: linkbench [-c <file>] [-csvstats <file>] [-csvstream <file>] [-D
<property=value>] [-L <file>] [-l] [-r]
-c <file> Linkbench config file
-csvstats,--csvstats <file> CSV stats output
-csvstream,--csvstream <file> CSV streaming stats output
-D <property=value> Override a config setting
-L <file> Log to this file
-l Execute loading stage of benchmark
-r Execute request stage of benchmark

PostgreSQL Deployment

This article does not include OS parameter optimization.

$ wget https://ftp.postgresql.org/pub/source/v9.6rc1/postgresql-9.6rc1.tar.bz2      
$ tar -jxvf postgresql-9.6rc1.tar.bz2
$ cd postgresql-9.6rc1
$ ./configure --prefix=/home/postgres/pgsql9.6rc1 --enable-debug
$ gmake world -j 32
$ gmake install-world
$ vi ~/env_pg.sh      
# add by digoal
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=1921
export PGDATA=/data01/pgdata/pg_root_96
export LANG=en_US.utf8
export PGHOME=/home/postgres/pgsql9.6rc1
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGDATABASE=postgres
alias rm='rm -i'
alias ll='ls -lh'
unalias vi
$ . ~/env_pg.sh

Create a Test Database

1. Initialize the database

initdb -D $PGDATA -E UTF8 --locale=C -U postgres
$> psql   
DROP DATABASE IF EXISTS linkdb;
CREATE DATABASE linkdb ENCODING='latin1' template template0;

--drop user linkbench to create new one
DROP USER IF EXISTS linkdb;

-- You may want to set up a special database user account for benchmarking:
CREATE USER linkdb password 'password';
-- Grant all privileges on linkdb to this user
GRANT ALL ON database linkdb TO linkdb;
$> \c linkdb linkdb  

--add Schema keep the same query style (dbid.table_name)
DROP SCHEMA IF EXISTS linkdb CASCADE;
CREATE SCHEMA linkdb;

-- FIXME:Need to make it partitioned by key id1 %16
-- Partition tables are recommended, or you can directly use a single table
-- For partition tables, the constraint check(mod(id1,16)=0) is required for the sub-table ID1...
-- src/main/java/com/facebook/LinkBench/LinkStorePgsql.java needs to be modified and re-compiled
-- In addition to id1=... add the same query criterion as the constraint, for example, where id1=val and mod(id1,16) = mod(val,16) ...
-- This allows the optimizer of PostgreSQL to filter id1=val
CREATE TABLE linkdb.linktable (
id1 numeric(20) NOT NULL DEFAULT '0',
id2 numeric(20) NOT NULL DEFAULT '0',
link_type numeric(20) NOT NULL DEFAULT '0',
visibility smallint NOT NULL DEFAULT '0',
data varchar(255) NOT NULL DEFAULT '',
time numeric(20) NOT NULL DEFAULT '0',
version bigint NOT NULL DEFAULT '0',
PRIMARY KEY (link_type, id1,id2)
);

-- this is index for linktable
CREATE INDEX id1_type on linkdb.linktable(
id1,link_type,visibility,time,id2,version,data);

CREATE TABLE linkdb.counttable (
id numeric(20) NOT NULL DEFAULT '0',
link_type numeric(20) NOT NULL DEFAULT '0',
count int NOT NULL DEFAULT '0',
time numeric(20) NOT NULL DEFAULT '0',
version numeric(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id,link_type)
);

CREATE TABLE linkdb.nodetable (
id BIGSERIAL NOT NULL,
type int NOT NULL,
version numeric NOT NULL,
time int NOT NULL,
data text NOT NULL,
PRIMARY KEY(id)
);

Configure the Load Template

Note that each value cannot have a trailing space in the configuration file of LinkBench. Otherwise parsing errors may occur.

# end node id for initial load (exclusive)  
# With default config and MySQL/InnoDB, 1M ids ~= 1GB
maxid1 = 1000000001
# Configure about 1 billion node records (about 1 TB)
workload_file = config/FBWorkload.properties  
linkstore = com.facebook.LinkBench.LinkStorePgsql
nodestore = com.facebook.LinkBench.LinkStorePgsql
# Database connection information
host = xxx.xxx.xxx.xxx
user = linkdb
password = linkdb
port = 1922
dbid = linkdb
# Database tables
linktable = linktable
counttable = counttable
nodetable = nodetable
# INFO output level
debuglevel = INFO
# Print frequency
progressfreq = 300
displayfreq = 1800
# Number of records allowed to be loaded and requested on each thread
load_progress_interval = 500000
req_progress_interval = 500000
maxsamples = 10000
# Number of threads to be loaded
loaders = 64
generate_nodes = true
loader_chunk_size = 2048
# Number of requests enabled (Multiplying by 2 to get the number of connections)
requesters = 192
# Number of requests for each thread
requests = 5000000
requestrate = 0
maxtime = 100000
warmup_time = 0
max_failed_requests = 100

Load Benchmark Data

$ cd ~/app/linkbench  

$ ./bin/linkbench -c config/LinkConfigPgsql.properties -l

Configure Benchmark Template

The steps are the same as those described in the preceding “Configure the load template” section

Stress Testing

./bin/linkbench -c config/LinkConfigPgsql.properties -r

Benchmark Result

Data load Result

INFO 2016-09-12 01:19:07,229 [main]: LOAD_NODE_BULK count = 390625  p25 = [8000,9000]ms  p50 = [8000,9000]ms  p75 = [9000,10000]ms  p95 = [10000,100000]ms  p99 = [10000,100000]ms  max = 1259341.029ms  mean = 9759.494ms
INFO 2016-09-12 01:19:07,229 [main]: LOAD_LINKS_BULK count = 1708831 p25 = [10000,100000]ms p50 = [10000,100000]ms p75 = [10000,100000]ms p95 = [10000,100000]ms p99 = [10000,100000]ms max = 1292335.24ms mean = 33558.09ms
INFO 2016-09-12 01:19:07,229 [main]: LOAD_COUNTS_BULK count = 301615 p25 = [10000,100000]ms p50 = [10000,100000]ms p75 = [10000,100000]ms p95 = [10000,100000]ms p99 = [10000,100000]ms max = 1318474.297ms mean = 66637.2ms
INFO 2016-09-12 01:19:07,229 [main]: LOAD PHASE COMPLETED. Loaded 100000000 nodes (Expected 100000000). Loaded 437452202 links (4.37 links per node). Took 4060.6 seconds. Links/second = 107731

Stress Test Result

Query performance for one-way metrics

INFO 2016-09-11 21:49:47,069 [main]: 
ADD_NODE
count = 2471774
p25 = [0.4,0.5]ms Less than 0.5 milliseconds for 25% requests
p50 = [0.5,0.6]ms Less than 0.6 milliseconds for 50% requests
p75 = [0.6,0.7]ms Less than 0.7 milliseconds for 75% requests
p95 = [1,2]ms Less than 2 milliseconds for 95% requests
p99 = [4,5]ms Less than 5 milliseconds for 99% requests
max = 213.324ms Maximum RT
mean = 0.715ms Average RT
INFO 2016-09-11 21:49:47,070 [main]: 
REQUEST PHASE COMPLETED.
96000000 requests done in 796 seconds.
Requests/second = 120482
INFO 2016-09-11 21:49:47,069 [main]: ADD_NODE count = 2471774  p25 = [0.4,0.5]ms  p50 = [0.5,0.6]ms  p75 = [0.6,0.7]ms  p95 = [1,2]ms  p99 = [4,5]ms  max = 213.324ms  mean = 0.715ms  
INFO 2016-09-11 21:49:47,069 [main]: UPDATE_NODE count = 7073914 p25 = [0.4,0.5]ms p50 = [0.5,0.6]ms p75 = [0.7,0.8]ms p95 = [2,3]ms p99 = [5,6]ms max = 154.589ms mean = 0.813ms
INFO 2016-09-11 21:49:47,069 [main]: DELETE_NODE count = 971421 p25 = [0.3,0.4]ms p50 = [0.4,0.5]ms p75 = [0.6,0.7]ms p95 = [2,3]ms p99 = [4,5]ms max = 80.185ms mean = 0.731ms
INFO 2016-09-11 21:49:47,070 [main]: GET_NODE count = 12414612 p25 = [0.4,0.5]ms p50 = [0.6,0.7]ms p75 = [0.9,1]ms p95 = [2,3]ms p99 = [5,6]ms max = 78.739ms mean = 0.943ms
INFO 2016-09-11 21:49:47,070 [main]: ADD_LINK count = 8631075 p25 = [1,2]ms p50 = [2,3]ms p75 = [3,4]ms p95 = [5,6]ms p99 = [9,10]ms max = 103.442ms mean = 2.657ms
INFO 2016-09-11 21:49:47,070 [main]: DELETE_LINK count = 2870975 p25 = [1,2]ms p50 = [2,3]ms p75 = [3,4]ms p95 = [6,7]ms p99 = [14,15]ms max = 134.991ms mean = 3.197ms
INFO 2016-09-11 21:49:47,070 [main]: UPDATE_LINK count = 7694028 p25 = [1,2]ms p50 = [2,3]ms p75 = [3,4]ms p95 = [5,6]ms p99 = [9,10]ms max = 91.146ms mean = 2.654ms
INFO 2016-09-11 21:49:47,070 [main]: COUNT_LINK count = 4690047 p25 = [0.4,0.5]ms p50 = [0.6,0.7]ms p75 = [1,2]ms p95 = [2,3]ms p99 = [5,6]ms max = 79.349ms mean = 1.026ms
INFO 2016-09-11 21:49:47,070 [main]: MULTIGET_LINK count = 504147 p25 = [0.7,0.8]ms p50 = [0.9,1]ms p75 = [1,2]ms p95 = [3,4]ms p99 = [6,7]ms max = 59.272ms mean = 1.325ms
INFO 2016-09-11 21:49:47,070 [main]: GET_LINKS_LIST count = 48678007 p25 = [0.7,0.8]ms p50 = [0.9,1]ms p75 = [1,2]ms p95 = [3,4]ms p99 = [6,7]ms max = 117.932ms mean = 1.386ms
INFO 2016-09-11 21:49:47,070 [main]: REQUEST PHASE COMPLETED. 96000000 requests done in 796 seconds. Requests/second = 120482

References

https://github.com/mdcallag/linkbench
https://www.facebook.com/notes/facebook-engineering/linkbench-a-database-benchmark-for-the-social-graph/10151391496443920

Original Source

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com