Redshift Serverless Data Sharing Query aborted due to read failure on a perm block

May 1, 2024 adminLeave a comment

Here is an interesting error that we recently encountered with one of our Redshift Serverless and Redshift Provisioned clusters. We have a data sharing setup where the serverless DB is the producer cluster of certain key tables. We share these tables to a provisioned Redshift cluster via data sharing.

When querying this particular table on the provisioned cluster through the data share with python(psycopg2) and airflow we received the following error.

2024-04-29 18:06:25,073 - ERROR -   | Query aborted due to read failure on a perm block.
  | HINT:  Please try again.
2024-04-29 18:06:25,073 - ERROR -   | Stacktrace:
2024-04-29 18:06:25,073 - ERROR -   | Traceback (most recent call last):
  |   File "/usr/local/airflow/.local/lib/python3.10/site-packages/soda/execution/query/query.py", line 122, in fetchone
  |     cursor.execute(self.sql)
  | psycopg2.errors.IoError: Query aborted due to read failure on a perm block.
  | HINT:  Please try again.

2024-04-29 18:06:25,073 - ERROR - | Query aborted due to read failure on a perm block.

| HINT: Please try again.

2024-04-29 18:06:25,073 - ERROR - | Stacktrace:

2024-04-29 18:06:25,073 - ERROR - | Traceback (most recent call last):

| File "/usr/local/airflow/.local/lib/python3.10/site-packages/soda/execution/query/query.py", line 122, in fetchone

| cursor.execute(self.sql)

| psycopg2.errors.IoError: Query aborted due to read failure on a perm block.

| HINT: Please try again.

We opened a support case with AWS and was informed that this is due to a meta data mismatch that can be resolved by running an update against the shared table on the producer side. After running this update we were back in business and things operated as normal.

The following query can be executed on the producer cluster as a mitigation: “UPDATE SET = 1 WHERE false;” where TABLE_NAME is the name of the table on which queries are failing and COLUMN_NAME is the name of any column in this table. This query will not result in any actual change to the producer’s data, but will result in synchronizing the metadata pertaining to TABLE_NAME on the consumer and thus letting subsequent datasharing queries go through successfully.

"UPDATE <TABLE_NAME> SET <COLUMN_NAME> = 1 WHERE false;"

1	"UPDATE <TABLE_NAME> SET <COLUMN_NAME> = 1 WHERE false;"

— Jason Ralph

Redshift Serverless Find Largest Tables

February 17, 2024February 20, 2024 adminLeave a comment

You can use the below SQL on redshift serverless to find the top largest tables. You can return the results in 1MB data blocks or convert to TB. You can obviously change the limit N to whatever you want to change the number of results returned.

1MB data blocks:

(wg-redshift) admin@redshift-sl-prod=# select *, size size_in_1_MB_data_blocks from svv_table_info order by pct_used desc limit 1; 
-[ RECORD 1 ]------------+----------------------------------------
database                 | redshift-sl-prod
schema                   | qa
table_id                 | 1791319
table                    | transactions
encoded                  | Y, AUTO(ENCODE)
diststyle                | AUTO(EVEN)
sortkey1                 | AUTO(SORTKEY)
max_varchar              | 16383
sortkey1_enc             | 
sortkey_num              | 0
size                     | 9102740
pct_used                 | 0.4444
empty                    | 0
unsorted                 | 
stats_off                | 0.00
tbl_rows                 | 91509652251
skew_sortkey1            | 
skew_rows                | 
estimated_visible_rows   | 91509653504
risk_event               | 
vacuum_sort_benefit      | 
create_time              | 2023-11-15 01:53:19.524329
size_in_1_mb_data_blocks | 9102740

Time: 6995.168 ms (00:06.995)

(wg-redshift) admin@redshift-sl-prod=# select *, size size_in_1_MB_data_blocks from svv_table_info order by pct_used desc limit 1;

-[ RECORD 1 ]------------+----------------------------------------

database | redshift-sl-prod

schema | qa

table_id | 1791319

table | transactions

encoded | Y, AUTO(ENCODE)

diststyle | AUTO(EVEN)

sortkey1 | AUTO(SORTKEY)

max_varchar | 16383

sortkey1_enc |

sortkey_num | 0

size | 9102740

pct_used | 0.4444

empty | 0

unsorted |

stats_off | 0.00

tbl_rows | 91509652251

skew_sortkey1 |

skew_rows |

estimated_visible_rows | 91509653504

risk_event |

vacuum_sort_benefit |

create_time | 2023-11-15 01:53:19.524329

size_in_1_mb_data_blocks | 9102740

Time: 6995.168 ms (00:06.995)

Size In TB:

(wg-redshift) admin@redshift-sl-prod=# select *, size/(1024*1024) size_in_tb from svv_table_info order by pct_used desc limit 1; 
-[ RECORD 1 ]----------+----------------------------------------
database               | redshift-sl-prod
schema                 | qa
table_id               | 1791319
table                  | transactions
encoded                | Y, AUTO(ENCODE)
diststyle              | AUTO(EVEN)
sortkey1               | AUTO(SORTKEY)
max_varchar            | 16383
sortkey1_enc           | 
sortkey_num            | 0
size                   | 9102740
pct_used               | 0.4444
empty                  | 0
unsorted               | 
stats_off              | 0.00
tbl_rows               | 91509652251
skew_sortkey1          | 
skew_rows              | 
estimated_visible_rows | 91509653504
risk_event             | 
vacuum_sort_benefit    | 
create_time            | 2023-11-15 01:53:19.524329
size_in_tb             | 8

Time: 6763.316 ms (00:06.763)

(wg-redshift) admin@redshift-sl-prod=# select *, size/(1024*1024) size_in_tb from svv_table_info order by pct_used desc limit 1;

-[ RECORD 1 ]----------+----------------------------------------

database | redshift-sl-prod

schema | qa

table_id | 1791319

table | transactions

encoded | Y, AUTO(ENCODE)

diststyle | AUTO(EVEN)

sortkey1 | AUTO(SORTKEY)

max_varchar | 16383

sortkey1_enc |

sortkey_num | 0

size | 9102740

pct_used | 0.4444

empty | 0

unsorted |

stats_off | 0.00

tbl_rows | 91509652251

skew_sortkey1 |

skew_rows |

estimated_visible_rows | 91509653504

risk_event |

vacuum_sort_benefit |

create_time | 2023-11-15 01:53:19.524329

size_in_tb | 8

Time: 6763.316 ms (00:06.763)

Specific Fields:

(wg-redshift) admin@redshift-sl-prod=# select database, schema, "table", size size_in_mb, tbl_rows, size/(1024*1024) size_in_tb from svv_table_info order by pct_used desc limit 1;
-[ RECORD 1 ]---------------------------------------
database   | redshift-sl-prod
schema     | seamqa
table      | transactions
size_in_mb | 9102740
tbl_rows   | 91509652251
size_in_tb | 8

Time: 6202.359 ms (00:06.202)

(wg-redshift) admin@redshift-sl-prod=# select database, schema, "table", size size_in_mb, tbl_rows, size/(1024*1024) size_in_tb from svv_table_info order by pct_used desc limit 1;

-[ RECORD 1 ]---------------------------------------

database | redshift-sl-prod

schema | seamqa

table | transactions

size_in_mb | 9102740

tbl_rows | 91509652251

size_in_tb | 8

Time: 6202.359 ms (00:06.202)

PSQL Connect To AWS Redshift From Windows 10 PowerShell

March 16, 2018December 20, 2019 admin2 Comments

Coming from a completely Linux background, I was tasked with connecting to a aws redshift cluster or a postgres cluster via Windows powershell and PSQL. I knew it was possible and searching the internet came up with CMD prompt solutions, when I attempted via powershell, I was faced with the following error below, you will need to install postgres on windows10 to get access to the psql binary, you can get it here:
https://www.postgresql.org/download/windows/

PS C:\WINDOWS\system32> psql.exe -h afs-rs-dev02.us-east-1.redshift.amazonaws.com  -p 5439 -U awsmaster benchmark01
Password for user awsmaster:
psql: FATAL:  invalid value for parameter "client_encoding": "WIN1252"

PS C:\WINDOWS\system32> psql.exe -h afs-rs-dev02.us-east-1.redshift.amazonaws.com -p 5439 -U awsmaster benchmark01

Password for user awsmaster:

psql: FATAL: invalid value for parameter "client_encoding": "WIN1252"

Turns out a colleague of mine and I figured out you will need to set the variable PGCLIENTENCODING via the powershell command line. This was expected but we could not nail down the syntax, we found it.

PS C:\WINDOWS\system32> $env:PGCLIENTENCODING='utf-8';
PS C:\WINDOWS\system32> psql.exe -h afs-rs-dev02.us-east-1.redshift.amazonaws.com  -p 5439 -U awsmaster benchmark01
Password for user awsmaster:
psql (10.1, server 8.0.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

benchmark01=#

PS C:\WINDOWS\system32> $env:PGCLIENTENCODING='utf-8';

PS C:\WINDOWS\system32> psql.exe -h afs-rs-dev02.us-east-1.redshift.amazonaws.com -p 5439 -U awsmaster benchmark01

Password for user awsmaster:

psql (10.1, server 8.0.2)

WARNING: Console code page (437) differs from Windows code page (1252)

8-bit characters might not work correctly. See psql reference

page "Notes for Windows users" for details.

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Type "help" for help.

benchmark01=#

Once this is set, you can connect to PG as normal.

NAGIOS AWS REDSHIFT TABLE COUNT PLUGIN PYTHON

November 22, 2016November 25, 2016 adminLeave a comment

I needed a quick plugin to warn me if one of our AWS REDSHIFT instances had a table count above 6000 and alert critical if above 7000. I decided to write a python plugin for nagios to do the chore. You can see the source code and the example of executing it below on the nagios host.

WARN = 6000
CRIT = 7000

1 2	WARN = 6000 CRIT = 7000

[root@devnagios ~]# /usr/bin/python /usr/local/nagios/libexec/check_rs_table_count.py -d awsdevdb \
-H aws-server.redshift.amazonaws.com -u nagios -p password -w 6000 -c 7000
OK!: table count: 694

[root@devnagios ~]# /usr/bin/python /usr/local/nagios/libexec/check_rs_table_count.py -d awsdevdb \

-H aws-server.redshift.amazonaws.com -u nagios -p password -w 6000 -c 7000

OK!: table count: 694

[root@devnagios ~]# /usr/bin/python /usr/local/nagios/libexec/check_rs_table_count.py -d awsdevdb \
-H aws-server.redshift.amazonaws.com -u nagios -p password -w 100 -c 200
CRIT!: table count: 694

[root@devnagios ~]# /usr/bin/python /usr/local/nagios/libexec/check_rs_table_count.py -d awsdevdb \

-H aws-server.redshift.amazonaws.com -u nagios -p password -w 100 -c 200

CRIT!: table count: 694

#!/usr/bin/python
""" MODULE IMPORTS """
import psycopg2
import optparse
import sys

""" NAGIOS API RETURN CODES """
nag_ret_dict = {'NagOk': 0,
                'NagWarn': 1,
                'NagCrit': 2,
                'NagUnknown': 3}

""" COMMAND LINE OPTION PARSING """
usage = "USAGE: python %prog -d DATABASE -H REMOTE_HOST -u USER -p DB_PASSWORD -w 6000 -c 7000"
parser = optparse.OptionParser(usage=usage)
parser.add_option('-d', action="store", default="postgres", help="Database selection", type="string")
parser.add_option('-u', action="store", default="postgres", help="DB User", type="string")
parser.add_option('-H', action="store", default="localhost", help="DB Host system", type="string")
parser.add_option('-p', action="store", default="", help="DB Password", type="string")
parser.add_option('-w', action="store", default="-2", help="Warning threshold int", type="int")
parser.add_option('-c', action="store", default="-2", help="critical threshold int", type="int")
options, args = parser.parse_args()

""" ARG COUNT CHECK """
if len(sys.argv[1:]) == 0:
    parser.print_help()
    sys.exit(nag_ret_dict['NagCrit'])

""" TEST DATABASE CONNECTION """
try:
    conn = psycopg2.connect("dbname='%s' user='%s' host='%s' password='%s' port='5439'" \
    % (options.d, options.u, options.H, options.p))
except psycopg2.DatabaseError, e:
    print('CRIT: Error %s' % e)
    sys.exit(nag_ret_dict['NagCrit'])

""" LOCAL SCOPE FUNCTIONS """
def query_rs():
    try:
        cur = conn.cursor()
        cur.execute("""select count(*) from pg_tables;""")
        result = cur.fetchone()
        cast_result = int(result[0])
        warn_thresh = options.w
        crit_thresh = options.c
        if cast_result >= crit_thresh:
            print('CRIT: table count: %d') % result
            sys.exit(nag_ret_dict['NagCrit'])
        elif cast_result >= warn_thresh:
            print ('WARN: table count: %d') % result
            sys.exit(nag_ret_dict['NagWarn'])
        else:
            print('OK!: tables count: %d') % result
    except psycopg2.DatabaseError, e:
        return ('CRIT: Error %s' % e)
        sys.exit(nag_ret_dict['NagCrit'])
    cur.close()
    conn.close()

""" MAIN FUNCTION """
def main():
        query_rs()

if __name__ == "__main__":
    main()

#!/usr/bin/python

""" MODULE IMPORTS """

import psycopg2

import optparse

import sys

""" NAGIOS API RETURN CODES """

nag_ret_dict = {'NagOk': 0,

'NagWarn': 1,

'NagCrit': 2,

'NagUnknown': 3}

""" COMMAND LINE OPTION PARSING """

usage = "USAGE: python %prog -d DATABASE -H REMOTE_HOST -u USER -p DB_PASSWORD -w 6000 -c 7000"

parser = optparse.OptionParser(usage=usage)

parser.add_option('-d', action="store", default="postgres", help="Database selection", type="string")

parser.add_option('-u', action="store", default="postgres", help="DB User", type="string")

parser.add_option('-H', action="store", default="localhost", help="DB Host system", type="string")

parser.add_option('-p', action="store", default="", help="DB Password", type="string")

parser.add_option('-w', action="store", default="-2", help="Warning threshold int", type="int")

parser.add_option('-c', action="store", default="-2", help="critical threshold int", type="int")

options, args = parser.parse_args()

""" ARG COUNT CHECK """

if len(sys.argv[1:]) == 0:

parser.print_help()

sys.exit(nag_ret_dict['NagCrit'])

""" TEST DATABASE CONNECTION """

try:

conn = psycopg2.connect("dbname='%s' user='%s' host='%s' password='%s' port='5439'" \

% (options.d, options.u, options.H, options.p))

except psycopg2.DatabaseError, e:

print('CRIT: Error %s' % e)

sys.exit(nag_ret_dict['NagCrit'])

""" LOCAL SCOPE FUNCTIONS """

def query_rs():

try:

cur = conn.cursor()

cur.execute("""select count(*) from pg_tables;""")

result = cur.fetchone()

cast_result = int(result[0])

warn_thresh = options.w

crit_thresh = options.c

if cast_result >= crit_thresh:

print('CRIT: table count: %d') % result

sys.exit(nag_ret_dict['NagCrit'])

elif cast_result >= warn_thresh:

print ('WARN: table count: %d') % result

sys.exit(nag_ret_dict['NagWarn'])

else:

print('OK!: tables count: %d') % result

except psycopg2.DatabaseError, e:

return ('CRIT: Error %s' % e)

sys.exit(nag_ret_dict['NagCrit'])

cur.close()

conn.close()

""" MAIN FUNCTION """

def main():

query_rs()

if __name__ == "__main__":

main()

Jason R. Ralph

Linux All Day Everyday

Tag: redshift

Redshift Serverless Find Largest Tables

PSQL Connect To AWS Redshift From Windows 10 PowerShell

NAGIOS AWS REDSHIFT TABLE COUNT PLUGIN PYTHON