Hello, hackers.
It might be a good idea to give users an opportunity to test their
applications with pgbench under different real-life-like load. So that
they will be able to see what's going to happen on production.
YCSB (Yahoo! Cloud Serving Benchmark) was taken as a concept. YCSB tests
were originally designed to facilitate performance comparisons of
different cloud data serving systems and it takes into account different
application workloads like:
workload A - assumes that application do a lot of reads(50%) and
updates(50%).
workload B - case when application do 95% of cases reads
and 5% updates
workload C - models behavior of read-only application.
workload E - the workload of the applications which in 95% of cases
requests for several neighboring tuples and in 5% of cases - does
updates.
In the patch those workloads were implemented to be executed by pgbench:
pgbench -b ycsb-A
--
Anthony Bykov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 41b756c089..cd884af937 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -506,6 +506,153 @@ static const BuiltinScript builtin_script[] =
"<builtin: select only>",
"\\set aid random(1, " CppAsString2(naccounts) " * :scale)\n"
"SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
+ },
+ {
+ "ycsb-A",
+ "<builtin: ycsb like workload A>",
+ "\\set write_weight 0\n"
+ "\\set read_weight 50\n"
+ "\\set scan_weight 0\n"
+ "\\set update_weight 50\n"
+ "\\set total_weight 100\n"
+ "\\set operation random(1,:total_weight)\n"
+ "\\set parameter 2\n"
+ "\\if (:operation < :write_weight)\n"
+ "\\set aid abs(hash(random_zipfian(1, " CppAsString2(naccounts)", :parameter)))%"CppAsString2(naccounts)"\n"
+ "\\set bid random(1,"CppAsString2(nbranches)")\n"
+ "\\set tid random(1,"CppAsString2(ntellers)")\n"
+ "\\set delta random(-5000, 5000)\n"
+ "INSERT into pgbench_accounts (bid, aid) VALUES (:bid, :aid);\n"
+ "\\elif (:operation < :read_weight+:write_weight)\n"
+ "\\set read abs(hash(random_zipfian(1, 10,:parameter)))%" CppAsString2(naccounts)"\n"
+ "SELECT * from pgbench_accounts where aid = :read;\n"
+ "\\elif (:operation < :total_weight-:update_weight)\n"
+ "\\set scan abs(hash(random_zipfian(1, 10, :parameter)))%" CppAsString2(naccounts)"\n"
+ "\\set scanlimit random(2, 10)\n"
+ "SELECT * from pgbench_accounts where abalance>:scan limit :scanlimit\n"
+ "\\elif (:operation < :total_weight)\n"
+ "\\set aid abs(hash(random_zipfian(1, " CppAsString2(naccounts)", :parameter)))%" CppAsString2(naccounts)"\n"
+ "\\set delta random(-5000, 5000)\n"
+ "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid\n"
+ "\\endif"
+ },
+ {
+ "ycsb-B",
+ "<builtin: ycsb like workload B>",
+ "\\set write_weight 0\n"
+ "\\set read_weight 95\n"
+ "\\set scan_weight 0\n"
+ "\\set update_weight 5\n"
+ "\\set total_weight 100\n"
+ "\\set operation random(1,:total_weight)\n"
+ "\\set parameter 2\n"
+ "\\if (:operation < :write_weight)\n"
+ "\\set aid abs(hash(random_zipfian(1, " CppAsString2(naccounts)", :parameter)))%"CppAsString2(naccounts)"\n"
+ "\\set bid random(1,"CppAsString2(nbranches)")\n"
+ "\\set tid random(1,"CppAsString2(ntellers)")\n"
+ "\\set delta random(-5000, 5000)\n"
+ "INSERT into pgbench_accounts (bid, aid) VALUES (:bid, :aid);\n"
+ "\\elif (:operation < :read_weight+:write_weight)\n"
+ "\\set read abs(hash(random_zipfian(1, 10,:parameter)))%" CppAsString2(naccounts)"\n"
+ "SELECT * from pgbench_accounts where aid = :read;\n"
+ "\\elif (:operation < :total_weight-:update_weight)\n"
+ "\\set scan abs(hash(random_zipfian(1, 10, :parameter)))%" CppAsString2(naccounts)"\n"
+ "\\set scanlimit random(2, 10)\n"
+ "SELECT * from pgbench_accounts where abalance>:scan limit :scanlimit\n"
+ "\\elif (:operation < :total_weight)\n"
+ "\\set aid abs(hash(random_zipfian(1, " CppAsString2(naccounts)", :parameter)))%" CppAsString2(naccounts)"\n"
+ "\\set delta random(-5000, 5000)\n"
+ "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid\n"
+ "\\endif"
+ },
+ {
+ "ycsb-C",
+ "<builtin: ycsb like workload C>",
+ "\\set write_weight 0\n"
+ "\\set read_weight 100\n"
+ "\\set scan_weight 0\n"
+ "\\set update_weight 0\n"
+ "\\set total_weight 100\n"
+ "\\set operation random(1,:total_weight)\n"
+ "\\set parameter 2\n"
+ "\\if (:operation < :write_weight)\n"
+ "\\set aid abs(hash(random_zipfian(1, " CppAsString2(naccounts)", :parameter)))%"CppAsString2(naccounts)"\n"
+ "\\set bid random(1,"CppAsString2(nbranches)")\n"
+ "\\set tid random(1,"CppAsString2(ntellers)")\n"
+ "\\set delta random(-5000, 5000)\n"
+ "INSERT into pgbench_accounts (bid, aid) VALUES (:bid, :aid);\n"
+ "\\elif (:operation < :read_weight+:write_weight)\n"
+ "\\set read abs(hash(random_zipfian(1, 10,:parameter)))%" CppAsString2(naccounts)"\n"
+ "SELECT * from pgbench_accounts where aid = :read;\n"
+ "\\elif (:operation < :total_weight-:update_weight)\n"
+ "\\set scan abs(hash(random_zipfian(1, 10, :parameter)))%" CppAsString2(naccounts)"\n"
+ "\\set scanlimit random(2, 10)\n"
+ "SELECT * from pgbench_accounts where abalance>:scan limit :scanlimit\n"
+ "\\elif (:operation < :total_weight)\n"
+ "\\set aid abs(hash(random_zipfian(1, " CppAsString2(naccounts)", :parameter)))%" CppAsString2(naccounts)"\n"
+ "\\set delta random(-5000, 5000)\n"
+ "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid\n"
+ "\\endif"
+ },
+ {
+ "ycsb-E",
+ "<builtin: ycsb like workload E>",
+ "\\set write_weight 5\n"
+ "\\set read_weight 0\n"
+ "\\set scan_weight 95\n"
+ "\\set update_weight 0\n"
+ "\\set total_weight 100\n"
+ "\\set operation random(1,:total_weight)\n"
+ "\\set parameter 2\n"
+ "\\set maxscanlength 100\n"
+ "\\if (:operation < :write_weight)\n"
+ "\\set aid abs(hash(random_zipfian(1, " CppAsString2(naccounts)", :parameter)))%"CppAsString2(naccounts)"\n"
+ "\\set bid random(1,"CppAsString2(nbranches)")\n"
+ "\\set tid random(1,"CppAsString2(ntellers)")\n"
+ "\\set delta random(-5000, 5000)\n"
+ "INSERT into pgbench_accounts (bid, aid) VALUES (:bid, :aid);\n"
+ "\\elif (:operation < :read_weight+:write_weight)\n"
+ "\\set read abs(hash(random_zipfian(1, 10,:parameter)))%" CppAsString2(naccounts)"\n"
+ "\\set scanlength random(1, :maxscanlength)%(" CppAsString2(naccounts)"-:read)\n"
+ "SELECT * from pgbench_accounts where aid > :read limit scanlength;\n"
+ "\\elif (:operation < :total_weight-:update_weight)\n"
+ "\\set scan abs(hash(random_zipfian(1, 10, :parameter)))%" CppAsString2(naccounts)"\n"
+ "\\set scanlimit random(2, 10)\n"
+ "SELECT * from pgbench_accounts where abalance>:scan limit :scanlimit\n"
+ "\\elif (:operation < :total_weight)\n"
+ "\\set aid abs(hash(random_zipfian(1, " CppAsString2(naccounts)", :parameter)))%" CppAsString2(naccounts)"\n"
+ "\\set delta random(-5000, 5000)\n"
+ "UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid\n"
+ "\\endif"
}
};
