{"id":13607,"date":"2020-03-08T14:48:13","date_gmt":"2020-03-08T13:48:13","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/yugabytedb-2-1\/"},"modified":"2020-03-08T14:48:13","modified_gmt":"2020-03-08T13:48:13","slug":"yugabytedb-2-1","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/yugabytedb-2-1\/","title":{"rendered":"YugaByteDB 2.1: the Open Source multi-region distributed database with PostgreSQL API is in GA with huge performance improvement"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\n9 months ago I was looking at YugaByteDB which was still in beta version for its ‘YSQL’ API. I published my first test on Medium: https:\/\/medium.com\/@FranckPachot\/running-pgbench-on-yugabytedb-1-3-3a15450dfa42<\/a>. I have been very enthusiastic about the idea, the architecture, the way they open-sourced it and how all was documented in their blog. I’ve even met them in Sunnyvale when I traveled to California for Oracle Open World. Great people with a great vision on the future of databases. From this first test, I was not impressed by the performance but it was an early beta and a multi-master\/multi-index database has many challenges to solve before tuning the details of implementation. This tuning task has been done for the General Availability version 2.1 released in February. I was eager to test it, but this first month back to dbi-services consulting was very busy. So finally here it is.<\/p>\n

This post takes the same test I did last July and the result is impressive: the pgbench initialization time is fully fixed and the pgbench run shows 9x higher throughput. <\/p>\n

I’m on the same VM which, as funny as it might sound, is an Oracle Linux 7 running on the Oracle Cloud.<\/p>\n

Install and start:<\/h3>\n

I install this version 2.1 in the same way I installed the 1.3 in the past. All is documented: https:\/\/docs.yugabyte.com\/latest\/quick-start\/install\/linux\/<\/a><\/p>\n

\nwget -O yugabyte-2.1.0.0-linux.tar.gz  https:\/\/downloads.yugabyte.com\/yugabyte-2.1.0.0-linux.tar.gz\ntar -xzvf yugabyte-2.1.0.0-linux.tar.gz\nyugabyte-2.1.0.0\/bin\/post_install.sh\nexport PATH=\"~\/yugabyte-2.1.0.0\/bin:~\/yugabyte-2.1.0.0\/postgresql\/bin:$PATH\"\n<\/code><\/pre>\n
I create a 3 nodes cluster with replication factor 3, with all nodes in the same host for this test:<\/p>\n
\nyb-ctl --rf 3 create\nyb-ctl status\n<\/code><\/pre>\n
\"\"<\/a><\/p>\n
I create database for this:<\/p>\n
\nysqlsh\n \\timing on\n drop database if exists franck;\n create database franck;\n \\q\n<\/code><\/pre>\n
\"\"<\/a><\/p>\n
pgbench initialization<\/h3>\n
I use pgbench that I have from a “normal” PostgreSQL installation on the same server.<\/p>\n
\n[opc@db192 ~]$ type pgbench\npgbench is hashed (\/usr\/pgsql-11\/bin\/pgbench)\ntime pgbench --initialize --host localhost -p 5433 -U postgres franck\n<\/code><\/pre>\n
\"\"<\/a> <\/p>\n
If you compare with the previous post on version 1.3 you will see many differences.<\/p>\n
    \n
  • No “ERROR: DROP multiple objects not supported yet” here. This issue has been fixed.<\/li>\n
  • No “ERROR: VACUUM not supported yet” but just a warning because there’s no vaccum here in the YugaByteDB storage layer<\/li>\n
  • And the best: 8 seconds instead of the 2 minutes we had before<\/li>\n<\/ul>\n
    However the “ALTER TABLE” to add the constraints afterward is still not supported so I run the same manually with the FOREIGN KEY declaration in the CREATE TABLE:<\/p>\n
    \nysqlsh franck\n drop table if exists pgbench_history;\n drop table if exists pgbench_tellers;\n drop table if exists pgbench_accounts;\n drop table if exists pgbench_branches;\nCREATE TABLE pgbench_branches (\n    bid integer NOT NULL\n   ,bbalance integer\n   ,filler character(88)\n   ,CONSTRAINT pgbench_branches_pkey PRIMARY KEY (bid)\n );\nCREATE TABLE pgbench_accounts (\n    aid integer NOT NULL\n   ,bid integer references pgbench_branches\n   ,abalance integer\n   ,filler character(84)\n   ,CONSTRAINT pgbench_accounts_pkey PRIMARY KEY (aid)\n );\nCREATE TABLE pgbench_tellers (\n    tid integer NOT NULL\n   ,bid integer references pgbench_branches\n   ,tbalance integer\n   ,filler character(84)\n   ,CONSTRAINT pgbench_tellers_pkey PRIMARY KEY (tid)\n );\nCREATE TABLE pgbench_history (\n    tid integer references pgbench_tellers\n   ,bid integer references pgbench_branches\n   ,aid integer references pgbench_accounts\n   ,delta integer\n   ,mtime timestamp without time zone\n   ,filler character(22)\n );\n\\q\n<\/code><\/pre>\n
    Now remains to insert the rows there. This was very long (about an hour) in 1.3:<\/p>\n
    \ntime pgbench --initialize --init-steps=g -h localhost -p 5433 -U postgres franck\n<\/code><\/pre>\n
    \"\"<\/a><\/p>\n
    No foreign key error and 6 seconds only!<\/p>\n
    Do you remember that I had to switch to SERIALIZABLE isolation level? I don’t have to here:<\/p>\n
    \"\"<\/a><\/p>\n
    This has been fixed with the support of SELECT locks, so no need to go to optimistic locking with SERIALIZABLE (which requires that the application implements a ‘retry’ logic).<\/p>\n
    Simple Update in 1 thread<\/h3>\n
    Then, as I did before, I run a Simple Update workload from one session during 30 seconds:<\/p>\n
    \npgbench --no-vacuum --builtin=simple-update --protocol=prepared --time 30 -h localhost -p 5433 -U postgres franck\n<\/code><\/pre>\n
    \"\"<\/a><\/p>\n
    When compared with the previous run<\/a> on 1.3 I’ve updated 9x more transactions. Yes, that’s exactly what has been announced for this version: huge performance imprevements:<\/p>\n
    \n
    What we've been up to over the last few month – YugabyteDB 2.1 has a ton of perf improvements and a lot of other goodies! https:\/\/t.co\/wwwhVG7bPd<\/a><\/p>\n
    PS: Please do give it a spin, would love your feedback.<\/p>\n
    — Karthik Ranganathan (@karthikr) February 25, 2020<\/a><\/p><\/blockquote>\n