A new version of GPLink is available.
– Added support for MixedCase external schema and table names. Use double quotes around these names to preserve the case.
– Added connection information for Teradata.
Michael Goddard, who is a colleague of mine, put together this demo of the Self-Healing feature I created for the Pivotal Greenplum on AWS Marketplace products. If any node in the cluster fails, it will automatically be replaced and correctly added back!
I’ve been using Greenplum since version 3.0 back in 2007. That version was actually the first commercially available version too. The versions were released about once every 6 to 12 months and got as high as 3.3.
Once the version got to 4.3, the number just seemed to get stuck. 4.3.0 was a pretty big release which changed “Append Only” tables to be “Append Optimized” which simply meant you could start updating and deleting tables stored in the append-only format. But more enhancements came to 4.3 but the version number never exceeded 4.3.
Major enhancements came to version 4.3.x and the innovation came at a faster pace but you may not have noticed this if you were just looking at the version number. Pivotal’s engineering team embraced the Pivotal Labs methodologies of pair programming and quick iterations. There was a huge transformation happening in this group.
When 5.0 came out, it was a big deal. Greeplum had always been a fork of PostgreSQL 8.2 but with 5.0, Greenplum was rebased to 8.3. A lot of work went into this change which also requires migrating from 4.3.x to 5.0.
Now this next change, I didn’t expect. During the 4.3.x development, the only time the version number would change to either 4.4 or 5.0, it would be when the database required a migration to upgrade. With 5.x, the version numbers are coming fast and don’t require migrations to upgrade. It just a simple binary swap to upgrade.
The result has been the release of 5.0 on 2017-09-14, 5.1 on 2017-10-20, and 5.2 on 2017-11-18. Do you see the pattern? Monthly point releases! All of these releases so far have been simple binary upgrades but have a ton of improvements each time.
Version 6 is in sight. Seriously. I would expect this to be the next rebase of Greenplum to PostgreSQL 8.4 and will require a database migration to upgrade. It is amazing how the Labs culture and open source software development has enhanced the development of Greenplum.
I haven’t posted much here lately because I’ve been working on deploying GemFire and Greenplum in the AWS Marketplace. It has been interesting work so I thought I would make a post about it here.
Amazon Web Services (AWS)
Amazon has the most popular cloud in the US with many products available which now includes Pivotal Greenplum and Pivotal GemFire. Both products are available as Bring Your Own License (BYOL) or Billed Hourly. BYOL simply means you already own a license and you wish to apply this to a deployment in AWS.
If you are not familiar with AWS, there are many ways to configure resources which can be overwhelming to learn and optimize. I have done extensive testing in these product offerings to ensure stability, security, repeatability, and performance. With just a few clicks, you can have an on-demand cluster built that rivals a full rack of on-premise hardware.
Greenplum is a Massively Parallel Processing database which means the data is distributed across multiple segments on multiple hosts. Each segment process stores a catalog of objects and it is possible to get this out of sync because of hardware failures or killing processes. For example, if you are vacuuming a table and then decide to kill the process on the Master host, the segments may not get the signal properly and rollback the transaction. This can happen when using “kill -9” rather than just “kill” to stop a process.
You may get this error message if a table is corrupt:
gpadmin=# select * from corrupt; ERROR: relation with OID 109802 does not exist (seg0 slice1 gpdbsne:40000 pid=78502)
So you decide to drop the table and start over but that doesn’t work either.
gpadmin=# drop table corrupt; ERROR: table "corrupt" does not exist (seg0 gpdbsne:40000 pid=78502)
The easy fix for this is to use “if exists” in the drop command.
gpadmin=# drop table if exists corrupt; DROP TABLE Time: 10.423 ms
Now the table is gone and you can recreate it. Next time, don’t use kill -9 and don’t kill vacuum commands.
If you are wondering how I managed to corrupt this table, here are the steps.
1. Create the table
gpadmin=# create table corrupt (id int, foo text, bar text) distributed by (id); CREATE TABLE Time: 31.195 ms
2. Insert some data
insert into corrupt select i as id, 'foo_' || i, 'bar_' || i from generate_series(1,100) as i; INSERT 0 100 Time: 46.419 ms gpadmin=# analyze corrupt; ANALYZE Time: 108.193 ms gpadmin=# \q
3. Connect to a segment directly. I’m using a single node for this test.
[gpadmin@gpdbsne ~]$ ps -ef | grep postgres | grep M gpadmin 78213 1 0 09:49 ? 00:00:00 /usr/local/greenplum-db-188.8.131.52/bin/postgres -D /data/primary/gpseg1 -p 40001 -b 3 -z 2 --silent-mode=true -i -M mirrorless -C 1 gpadmin 78214 1 0 09:49 ? 00:00:00 /usr/local/greenplum-db-184.108.40.206/bin/postgres -D /data/primary/gpseg0 -p 40000 -b 2 -z 2 --silent-mode=true -i -M mirrorless -C 0 gpadmin 78239 1 0 09:49 ? 00:00:00 /usr/local/greenplum-db-220.127.116.11/bin/postgres -D /data/master/gpseg-1 -p 5432 -b 1 -z 2 --silent-mode=true -i -M master -C -1 -x 0 -E
I see that the segments are using ports 40,000 and 40,001 while the master is using 5432. I’ll connect to seg0 on port 40,000 and then drop the table. This only drops the object in this one segment. I’ll also do a quick query to make sure I only see half of the data because I have 2 segments.
PGOPTIONS='-c gp_session_role=utility' psql -p 40000 psql (8.2.15) Type "help" for help. gpadmin=# select count(*) from corrupt; count ------- 50 (1 row) Time: 3.485 ms
4. Corrupt the database by dropping the table only on one segment.
drop table corrupt; DROP TABLE Time: 9.410 ms gpadmin=# \q
5. Confirm that the table is corrupt.
[gpadmin@gpdbsne ~]$ psql Timing is on. psql (8.2.15) Type "help" for help. gpadmin=# select * from corrupt; ERROR: relation with OID 109802 does not exist (seg0 slice1 gpdbsne:40000 pid=78502)
And again, the fix:
gpadmin=# drop table if exists corrupt; DROP TABLE Time: 10.423 ms
S3 is Amazon’s Simple Storage Service which is an inexpensive cloud storage solution and has quickly become a solution for cold data and backups. Greenplum now has External Tables that can read and write data to S3 so you can leverage this popular storage service with Greenplum. Here is how you do it!
1. You will need an Amazon account with your Access Key ID and Secret Access Key. If you have the aws CLI installed and configured, just cat ~/.aws/credentials
2. Get your default region. This is in ~/.aws/config
3. You’ll need a bucket in this region and this can be done with the AWS web interface.
4. You’ll need a configuration file. Here is an example (be sure to change the secret and accessid).
[default] secret = <secret> accessid = <accesssid> threadnum = 4 chunksize = 67108864 low_speed_limit = 10240 low_speed_time = 60 encryption = true
5. Copy the configuration to every Segment Host in your cluster.
for i in $(cat segment_hosts.txt); do scp s3_demo.conf $i:/home/gpadmin; done
Writing to S3
1. Create the Writable External Table with the S3 protocol, AWS URL that has the correct region, and the configuration file that is found on every Segment Host.
CREATE WRITABLE EXTERNAL TABLE public.demo_write (id int, fname text, lname text) LOCATION ('s3://s3-us-east-1.amazonaws.com/pivotalguru/demo config=/home/gpadmin/s3_demo.conf') FORMAT 'TEXT' (DELIMITER '|' NULL AS '');
2. Execute an INSERT statement:
INSERT INTO demo_write SELECT i, 'Jon_' || i, 'Roberts_' || i FROM generate_series(1,10000) as i;
Note: Each Segment will create a file in S3 in your bucket with the prefix you specify in the location specified in the Writable External Table. In this demo, each file is prefixed with “demo”. An example filename is “demo9767abbb3.txt”.
Reading from S3
1. Create the External Table with the same location and configuration as before.
CREATE EXTERNAL TABLE public.demo_read (id int, fname text, lname text) LOCATION ('s3://s3-us-east-1.amazonaws.com/pivotalguru/demo config=/home/gpadmin/s3_demo.conf') FORMAT 'TEXT' (DELIMITER '|' NULL AS '');
2. Select the data.
gpadmin=# select * from demo_read limit 10; id | fname | lname -----+---------+------------- 58 | Jon_58 | Roberts_58 90 | Jon_90 | Roberts_90 122 | Jon_122 | Roberts_122 191 | Jon_191 | Roberts_191 207 | Jon_207 | Roberts_207 239 | Jon_239 | Roberts_239 271 | Jon_271 | Roberts_271 319 | Jon_319 | Roberts_319 335 | Jon_335 | Roberts_335 351 | Jon_351 | Roberts_351 (10 rows) Time: 1139.538 ms
1. An S3 External Table that references a single file will only use a single Segment to read the data. Instead, try to have at least 1 file per Segment for an S3 External Table.
2. S3 External Tables supports gzip compression only.
3. Use S3 External Tables for cold storage or to create a backup of a table or query using a Writable External Table.
Greenplum has two major types of table storage techniques. The first is inherited from PostgreSQL so it uses Multi-Version Concurrency Control (MVCC) and is referred to as HEAP storage. The second is more efficient technique called Append Optimized (AO). This blog post discusses the performance benefits of AO over HEAP tables.
This is the default storage technique when you create a table in Greenplum. It handles UPDATE and DELETE commands just like PostgreSQL in terms of marking the old row invalid and inserting a new record into the table. This hides the deleted or stale row for new queries but provides read consistency to sessions that may have started querying the table prior to the UPDATE or DELETE.
Here is an example of a HEAP table.
CREATE TABLE heap_table (id int, fname text, lname text, address1 text, address2 text, city text, state text, zip text) DISTRIBUTED BY (id);
But how does this HEAP table store data? How much space does it use?
INSERT INTO heap_table (id, fname, lname, address1, address2, city, state, zip) SELECT i, 'Jon_' || i, 'Roberts_' || i, i || ' Main Street', 'Apartment ' || i, 'New York', 'NY', i::text FROM generate_series(1, 10000) AS i; ANALYZE heap_table; SELECT sotdsize FROM gp_toolkit.gp_size_of_table_disk WHERE sotdschemaname = 'public' AND sotdtablename = 'heap_table'; 1114112
So it uses 1,114,112 bytes in my small single node test for this generated data of 10,000 records.
AO storage was originally designed to be Append Only but starting with Greenplum version 4.3, these tables became Append Optimized because the tables now allow UPDATE and INSERT like a HEAP table but retain the efficient storage.
Create the same table and data but as an AO table.
CREATE TABLE ao_table (id int, fname text, lname text, address1 text, address2 text, city text, state text, zip text) WITH (appendonly=true) DISTRIBUTED BY (id); INSERT INTO ao_table (id, fname, lname, address1, address2, city, state, zip) SELECT i, 'Jon_' || i, 'Roberts_' || i, i || ' Main Street', 'Apartment ' || i, 'New York', 'NY', i::text FROM generate_series(1, 10000) AS i; ANALYZE ao_table; SELECT sotdsize FROM gp_toolkit.gp_size_of_table_disk WHERE sotdschemaname = 'public' AND sotdtablename = 'ao_table'; 973016
This is now only using 973,016 bytes or a 12.6% reduction in disk space being used. For large tables, the performance difference will be more noticeable because it will take less time to scan your disks.
AO tables also let you compress it where a HEAP table does not. What does that look like?
CREATE TABLE ao_compressed_table (id int, fname text, lname text, address1 text, address2 text, city text, state text, zip text) WITH (appendonly=true, compresstype=quicklz) DISTRIBUTED BY (id); INSERT INTO ao_compressed_table (id, fname, lname, address1, address2, city, state, zip) SELECT i, 'Jon_' || i, 'Roberts_' || i, i || ' Main Street', 'Apartment ' || i, 'New York', 'NY', i::text FROM generate_series(1, 10000) AS i; ANALYZE ao_compressed_table; SELECT sotdsize FROM gp_toolkit.gp_size_of_table_disk WHERE sotdschemaname = 'public' AND sotdtablename = 'ao_compressed_table'; 234344
Dropped more to 234,344 bytes. This is a huge decrease in disk space being used and measures at another 75.9% reduction! This is trading CPU cycles for IO scans which in most cases, will provide better query times.
In the last test, make the table also column oriented. This is also a feature only available for AO tables.
CREATE TABLE ao_co_compressed_table (id int, fname text, lname text, address1 text, address2 text, city text, state text, zip text) WITH (appendonly=true, compresstype=quicklz, orientation=column) DISTRIBUTED BY (id); INSERT INTO ao_co_compressed_table (id, fname, lname, address1, address2, city, state, zip) SELECT i, 'Jon_' || i, 'Roberts_' || i, i || ' Main Street', 'Apartment ' || i, 'New York', 'NY', i::text FROM generate_series(1, 10000) AS i; ANALYZE ao_co_compressed_table; SELECT sotdsize FROM gp_toolkit.gp_size_of_table_disk WHERE sotdschemaname = 'public' AND sotdtablename = 'ao_co_compressed_table'; 196840
It dropped again! This time to 196,840 bytes or another 16%.
The total percentage difference between a HEAP table and the AO table that is column oriented and compressed is 82.33%!
Use AO tables. These are faster than HEAP tables and also let you compress and column orient your larger tables for even better performance.
Here are some tips for configuring Pivotal HDB (based on Apache HAWQ) with Ambari.
A Hadoop cluster typically is configured with JBOD so utilize all data disks for temp space.
Here is an example of the “HAWQ Master Temp Directories” entry when the Master and Standby nodes each have 8 disks:
Here is an example of the “HAWQ Segment Temp Directories” entry when each Data Node has 8 disks:
VM Overcommit set to 2
VM Overcommit Ratio
2GB – 64GB: set the Overcommit Ratio to 50
>= 64GB of RAM: set the Overcommit Ratio to 100
2GB – 8GB: set swap space equal to RAM
8GB – 64GB: set swap space to 0.5 * RAM
>= 64GB: set swap space to 4GB
Segment Memory Usage Limit
Step 1: Calculate total memory (RAM * overcommit_ratio_percentage + SWAP)
Step 2: Calculate total memory used by other activities (2GB for OS, 2GB for Data Node, 2GB for Node Manager, 1GB for PXF)
Step 3: Subtract other memory from total memory to get the value for the Segment Memory Usage Limit
Overcommit Ratio: 100
Using Yarn: ((256 * 1) + 4) – 7 = 253
Using Default Resource Manager: (256 * 1) – 7 = 249
Overcommit Ratio: 50
Using Yarn: ((64 * 0.5) + 32) – 7 = 57
Using Default Resource Manager: (64 * 0.5) – 7 = 57
ipc.client.connect.timeout = 300000
ipc.client.connection.maxidletime = 3600000
Optional HAWQ hawq-site.xml
hawq_rm_stmt_vseg_memory = 1gb
By default, this is set to 128mb which is great for a high level of concurrency. If you need to utilize more memory in the cluster for each query, you can increase this value considerably. Here are the acceptable values:
128mb, 256mb, 512mb, 1gb, 2gb, 4gb, 8gb, 16gb
Alternatively, you can set this at the session level instead of the entire database.
Operating System gpadmin account
Log into the Master and Standby nodes and execute the following:
echo "source /usr/local/hawq/greenplum_path.sh" >> ~/.bashrc
Now set the database password. Below, I am using ‘password’ as the password so set this based on your organization’s password policy. By default, gpadmin doesn’t have a password set at all.
psql -c "alter user gpadmin password 'password'"
Enable encrypted password authentication. This assumes you are using the default /data/hawq/master path. Adjust if needed. This allows you to connect to the database remotely with an encrypted password.
echo "host all all 0.0.0.0/0 md5" >> /data/hawq/master/pg_hba.conf hawq stop cluster -u -a
Pivotal HDB 2.0.1 (based on Apache HAWQ)
The newest release of Pivotal HDB is fantastic! It adds new features and resolves some issues as well. Here are the release notes.
One resolved issue affected Outsourcer which is a JDBC problem and is documented here: HAWQ-738. Pivotal HDB 2.0.0 was released in May and I found that Outsourcer suffered from stability issues under load and was generally slower navigating in the UI than HDB 1.3 or Greenplum Database. The issue was quickly resolved in that same month but the fix wasn’t publicly available until October of 2016 with this new release.
Quicklz is a compression library that uses GPL licensing. It is bundled with Greenplum Database and Pivotal HDB 1.3 and 2.0.0. Starting with Pivotal HDB 2.0.1, Quicklz has been removed. Why? Because of that GPL license and HAWQ is an Apache project.
In HDB 1.3 and 2.0.0, the guidance was to use Quicklz compression for row oriented tables but starting with 2.0.1, you should use Snappy compression. It is an easy change too:
CREATE TABLE mytable (id INT, fname TEXT) WITH (APPENDONLY=TRUE, COMPRESSTYPE=SNAPPY) DISTRIBUTED RANDOMLY;
Note: When upgrading to 2.0.1, be sure to first change your Quicklz compressed tables to either Zlib, Parquet with Snappy, or no compression at all. Then upgrade and then you can change back to row orientation and use Snappy compression. More details are in this section of the release notes.
Future releases of Pivotal HDB should come quicker because the licensing hurdle is now complete. This means developers can focus on enhancements and fixes rather than licensing.
This new release officially supports Pivotal HDB 2.0.1. It makes the compression for row oriented tables to now use Snappy instead of Quicklz. If you are using Pivotal HDB 2.0.0 or Greenplum Database, Outsourcer will still use Quicklz compression.
Please consider upgrading to Pivotal HDB 2.0.0 to 2.0.1 especially if you are using Outsourcer. When I test Outsourcer with Pivotal HDB 2.0, I use build 22425 rather than build 22126 which is what you can download from Pivotal’s site. 22126 has the JDBC bug while 22425 and new builds do not. And when you upgrade to 2.0.1, also upgrade to Outsourcer 5.2.0.