Distributed SQL Tips and Tricks – July 9th, 2021
Welcome back to our bi-weekly tips and tricks blog where I have the pleasure of recapping some distributed SQL questions from around the Internet. This blog series would not be possible without all of the behind the scenes and community work done by YugabyteDB members such as Dorian Hoxha and Frits Hoogland. All of us here at Yugabyte also have to thank our incredible user community for their work with the technology, and not being afraid to ask questions. Do you have questions? Make sure to ask them on our YugabyteDB Slack channel, Forum, GitHub, or Stack Overflow. Let’s dive in:
Using OpenSSL with YugabyteDB’s YSQL API
You have YugabyteDB installed and now you are looking at the best ways to make sure your connection to that database is secure. The first step you take is to enable TLS. You try connecting through ysqlsh and it works without any issue:
$ ysqlsh -h xxx.xxx.xx.xxx ysqlsh (11.2-YB-126.96.36.199-b0) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. yugabyte=#
However when you try connecting with OpenSSL you get an error that says the peer has no certificate.
$ /usr/local/bin/openssl s_client -connect xxx.xxx.xx.xxx:5433 CONNECTED(00000003) write:errno=0 --- no peer certificate available --- No client certificate CA names sent --- SSL handshake has read 0 bytes and written 293 bytes Verification: OK --- New, (NONE), Cipher is (NONE) Secure Renegotiation IS NOT supported Compression: NONE Expansion: NONE No ALPN negotiated Early data was not sent Verify return code: 0 (ok)
In order to get around this, you need to elevate the insecure connection to a secure one using
STARTTLS. This is referred to as “opportunistic TLS”. This is why the openssl connection initially acts like TLS is not enabled when you attempt to connect. In order to make this work, you need a version of OpenSSL >= 1.1.1 and an additional command line option
-starttls postgres. For this specific example you would use the following command:
$ openssl s_client -starttls postgres -connect xxx.xxx.xx.xxx:5433
If you are using Yugabyte Platform or our fully managed Yugabyte Cloud, you do not need to worry about this level of detail. Rather you can set this up in a couple clicks.
Specifying the Number of Tablets with YugabyteDB
When beginning to work with YugabyteDB, it can be difficult to predict the number of tablets (shards) needed. This concept is an important part of the YugabyteDB sharding layer. Recall that the default tablet number for the cluster is 8 for most production workloads, if not explicitly set. This can be changed at the
tserver or table/index level.
An example of this change for YSQL on the
tserver level would be:
bin/yb-ctl --rf=3 create --ysql_num_shards_per_tserver=1
For YCQL it would be:
bin/yb-ctl --rf=3 create --num_shards_per_tserver=1
On the table level setting, the number of tablets is done during creation. For example the
CREATE TABLE...SPLIT INTO command is used for hash sharded tables using the YSQL API. Additional examples can be found in our documentation. The most exciting development on this front is automatic tablet splitting, where you can set tablets to automatically split upon reaching a specified size threshold. The YugabyteDB development team is working hard to enable this as the default option in an upcoming release, so stay tuned. To keep up with the progress being made in regards to tablet splitting you can follow this GitHub issue. Overall, the number of tablets will be controlled at the
tserver or table/index level. The only exception to this is for colocated tables which are done on the database level.
For more information about data sharding, and how it works with YugabyteDB, you can read our blog on the topic here. For examples of the
CREATE TABLE commands used in order to pre split tables, you can read them here: YSQL and YCQL.
Column Type Conversion with YugabyteDB
If you are looking to convert column types in YugabyteDB today there are a couple avenues you can take. The most straightforward way would be to use the following command, however you will notice that this currently returns an error.
ALTER TABLE "my_table" ALTER COLUMN "my_column" TYPE varchar; ERROR: This ALTER TABLE command is not yet supported.
Today we do support
ALTER COLUMN type that does not require on-disk changes. This however is limited to increasing the size of
varbit types. Meaning that you can change a column from
varchar(255) for example. However, if you attempt to change from the
int type to the
varchar type, you will get an error. As called out in this issue, the reason for that is because altering data type may require changing on-disk data for existing rows. Performing this in a distributed cluster, as well as with replication and indexes, will result in overhead and data-transfer. Without such support today we can still accomplish this task in one of two ways. One way would be to create a new column with the correct type and move the data over. Another way would be to create a new table with the correct column types and move the data over. As part of our ongoing effort to continuously provide additional features to YugabyteDB we are aiming to eventually support all of the main
ALTER TABLE commands and variants. This master list can be found here.
New Documentation, Blogs, Tutorials, and Videos
- The YugabyteDB Vagrant Box
- Migrating MySQL to YugabyteDB Using pgloader
- Reads in YugabyteDB – Tuning Consistency, Latency, and Fault Tolerance
- Yugabyte + Hasura Make it Easier to Build Complex, Global Applications
- Deploy a Real-Time Polling App with Hasura Cloud and YugabyteDB Cloud
- Auto-Balancing Data in YugabyteDB, a Distributed SQL Database
- Multi-Region YugabyteDB Deployments on Kubernetes with Istio
- Why I Joined Yugabyte series – Gavin Johnson and Akshat Jain
- Distributed SQL Fundamentals: Horizontal Scaling
- High Performance Design and Architecture in a Distributed SQL Database
- YugabyteDB YCQL Fundamentals Community Training and Certification
- July 13, 2021 – Building Always-On, Scalable Applications with Yugabyte Cloud and Hasura Cloud
- July 20, 2021 – Performance Tuning and Debugging GraphQL Applications with Yugabyte Cloud and Hasura Cloud
- July 27, 2021 – YugabyteDB: PostgreSQL with Cloud Native Resilience and Scale
- July 29, 2021 – YugabyteDB YSQL Development Advanced Certification and Training
- August 3, 2021 – Building Cloud Native GraphQL Applications on Kubernetes with YugabyteDB and Hasura on Open Source Projects
Ready to start exploring YugabyteDB features? Getting up and running locally on your laptop is fast. Visit our quickstart page to get started.