Redshift is column-store and as such in principle queries are unaffected
by the unused columns in the tables being queried. In practise,
accessing any single column is unaffected by the number of columns, but,
tentatively, it looks like accessing multiple columns shows that the
more columns are present between a column and the final column in the
table, the slower it is to access the column, and so the more columns
are present in a table, the slower access becomes for all columns. The
slowdown, even when there are 1600 columns, is very small for
dc2.large and ra3.xlplus, but is much larger
for ds2.xlarge. However, for normally sized tables, up to
say 100 columns, even on ds2.xlarge the slowdown is very
small.
Introduction
Redshift is a column-store database and as such tables are broken up
into their constituent columns and each column is stored separately on
disk.
A query typically will use only some rather than all of the columns
in a table, and naturally the more columns are used, the slower the
query, since more data has to be read from disk and more work has to be
done to perform materialization, which is the task of joining the
separated columns back up into contiguous rows for return to the
client.
However, there is a question as to whether or not the columns which
are not used by a query have a performance impact on the query. If this
is so, then the more columns a table has, even though they are not used
by a query, the more harmful for performance.
In principle those extra columns, being unused, should have no impact
on query performance. The question is whether or not this is true in
practise.
Test Method
The basic method is that we create a test table, and issue a test
query on the table, and measure the time taken for the query to
execute.
There are four test queries.
Each test query is issued five times, with the slowest and fastest
results being discarded.
The test table is dropped, created and repopulated for every
iteration, and for every test query; which is to say, all the test
tables are identical, but each table only ever has one query issued on
it.
The test table is varied, by the number of columns (2, 800, and 1600)
and the number of blocks per column, per slice (1 and 8), with the five
iterations of the four queries repeated on every variant of the
table.
The table has key distribution. The even
has off-by-one bugs when it comes to distribution rows, so cannot be
used, as you end up with an uneven number of blocks on the different
slices.
The value stored in the distribution key column, and the number of
rows, are chosen to ensure each slice has an equal number of exactly
completely full blocks. Validation checks are present in the test code
to ensure each slice does in fact has the correct number of blocks with
the correct number of rows.
For all columns the data type is bigint and
not null is specified, encoding is raw, and
the table is fully vacuumed and analyzed. Result caching is disabled,
and the analysis threshold set to 0.
The tests described above are performed on three clusters, all two
node, a dc2.large, a ds2.xlarge and an
ra3.xlplus.
How long a query took to execute is taken directly from the system
tables, and so mark the times internal to Redshift as to when the query
execution started and ended (queuing and compile times are
excluded).
The four test queries are;
find the sum() of the first column
find the sum() of the last column
find the sum() of the first column, when the second
column, with one subtracted from its value, is greater than -10
find the sum() of the first column, when the last
column, with one subtracted from its value, is greater than -10
The purpose of the “subtract one” is to defeat use of the Zone Map,
and the comparison, greater than -10, ensures all rows are used in the
sum(). This is because every column in a row has the same
value, and that value is always 0 or greater.
These tests then measure how long it takes to sum() the
first column, and the last column, and to access pairs of columns (first
and second, first and last), as we vary the number of columns, the
amount of data, and the node type.
Results
See Appendix A for the Python
pprint dump of the results dictionary.
The X-axis is the number of exactly full blocks per column, per
slice, in the table.
The Y-axis is the number of columns in the table.
The individual results are “mean/standard deviation” of the duration
of the test queries in seconds.
As ever, five trials occurred, with the slowest and fastest being
discarded.
Test duration was 14,375 seconds.
Performance Test
dc2.large, 2 nodes, first
column
1
8
2
0.007/0.000
0.013/0.000
800
0.008/0.000
0.012/0.000
1600
0.008/0.000
0.013/0.000
dc2.large, 2 nodes, last
column
1
8
2
0.011/0.003
0.012/0.000
800
0.008/0.000
0.013/0.000
1600
0.008/0.001
0.013/0.000
dc2.large, 2 nodes,
first and second column
1
8
2
0.012/0.003
0.018/0.002
800
0.009/0.000
0.025/0.000
1600
0.009/0.001
0.026/0.001
dc2.large, 2 nodes,
first and last column
1
8
2
0.009/0.001
0.019/0.001
800
0.009/0.000
0.017/0.000
1600
0.008/0.000
0.018/0.002
ds2.xlarge, 2 nodes, first
column
1
8
2
0.006/0.000
0.010/0.001
800
0.006/0.000
0.010/0.001
1600
0.007/0.000
0.012/0.001
ds2.xlarge, 2 nodes, last
column
1
8
2
0.006/0.000
0.011/0.002
800
0.007/0.000
0.010/0.000
1600
0.007/0.000
0.011/0.001
ds2.xlarge, 2 nodes,
first and second column
1
8
2
0.006/0.000
0.014/0.001
800
0.007/0.000
0.091/0.002
1600
0.007/0.000
0.145/0.014
ds2.xlarge, 2 nodes,
first and last column
1
8
2
0.007/0.000
0.015/0.001
800
0.008/0.000
0.015/0.000
1600
0.008/0.001
0.014/0.001
ra3.xlplus, 2 nodes, first
column
1
8
2
0.008/0.002
0.009/0.001
800
0.006/0.000
0.008/0.001
1600
0.006/0.000
0.009/0.001
ra3.xlplus, 2 nodes, last
column
1
8
2
0.005/0.000
0.008/0.001
800
0.006/0.000
0.009/0.001
1600
0.006/0.000
0.008/0.001
ra3.xlplus, 2 nodes,
first and second column
1
8
2
0.006/0.000
0.011/0.002
800
0.006/0.001
0.019/0.000
1600
0.007/0.000
0.019/0.000
ra3.xlplus, 2 nodes,
first and last column
1
8
2
0.006/0.001
0.011/0.001
800
0.006/0.000
0.012/0.001
1600
0.007/0.000
0.011/0.001
Discussion
The first point of note is that for all tests with one block per
column per slice, there are no significant differences in performance,
for any of the SQL test queries, no matter how many columns there are,
no matter which node type is used.
However, when we move to the eight block tests, differences do
emerge.
The query times are of course longer, because eight blocks are being
read per column per slice. What matters is not how long queries take as
such, but differences in how long the queries take as the number of
columns (and node types) vary.
Looking first at dc2.large, we see that when accessing
the first column on its own, or the last column on its own, the number
of columns makes no difference.
However, we have next the two test queries which access a pair of
columns. As with increasing the number of blocks, these queries will be
slower - they are reading two columns not one - but as before, we are
only looking at the difference in performance as we vary the number of
columns.
What we find is that when accessing the first and second column,
moving from two columns to 800 or 1600 results in a slowdown, from 0.018
seconds to 0.025 and 0.026 seconds, respectively. This is very slight,
but it seems to be a real change.
However, when looking at accessing the first and last column, we
again see no significant change in performance as the number of columns
change.
Very tentatively then, since we have so little evidence so far, it
looks like the last columns in a table are accessed most quickly, and
the first most slowly.
Moving on to ds2.xlarge, as before, accessing the first
or last column makes no difference, and accessing first and last shows
no significant change.
Now, as before, accessing first and second shows a slowdown, but the
slowdown is far more pronounced with this node type. Query time goes
from 0.014, to 0.091, to 0.145 seconds. That’s a lot.
Finally, moving to ra3.xlplus, we see the same pattern,
but with a change in performance for first and second on much the same
order as for dc2.large.
Conclusions
When a query accesses a single column only, the number of columns in
a table has no effect on performance.
When a query accesses the first and last columns, we also see the
number of columns in a table has no effect on performance.
However, when a query accesses the first and second columns, we see
the number of columns in a table does have an effect on
performance. As the number of columns increases, the query becomes
slower.
This slowdown effect is very small on dc2.large and
ra3.xlplus, with the query duration going from about 0.011
second for 2 columns, to about 0.019 seconds for 1600 columns, but much
larger on ds2.xlarge, which goes from 0.014 seconds to
0.145 seconds.
Tentatively, we can think to generalize this to say that the more
columns there are between an accessed column and the final column in the
table, the slower the column is to access, and so the more columns are
in the table, the slower accessing a column becomes, because there are
increasingly more columns between the column and the final column in the
table.
In other words, the closer a column is to the end of the table, the
faster it is to access, and the closer a column is to the start of the
table, the slower.
The difference in performance is very small on dc2.large
and ra3.xlplus, even with the worst case of 1600 columns,
but looks considerable on ds2.xlarge, although having said
that, it’s a pretty rare table which exceeds even 30 columns, and for
tables of this size, the slowdown even on ds2.xlarge is
going to be very small.
Unexpected Findings
When you investigate Redshift, there are always unexpected
findings.
The distribution style even appears to have
off-by-one errors. When populating four slices with one block each,
where each block has 130,994 rows (the maximum number of
bigint with raw encoding per block), even will
not give you one block per slice, but two slices with one block which is
short one record, and the other two slices both have two blocks, one
completely full and the next with one record.
It won’t matter in normal use, but it seems indicative of a lack of
testing or testing but a lack of care to fix such errors, and it was a
problem for me with this testing work, where I need to exactly control
blocks per slice. It took some hours to implement my own solution using
key based distribution, and this solution has unavoidable
drawbacks compared to using even.
Not quite an unexpected finding, since I knew this already from
earlier work, but I’m listing it here as I had to work around this issue
and I would have discovered it here if I’d not already known; the
slice_num() function appears very rarely to give the wrong
slice number.
I want to use slice_num() because where I’m using
key, I need to know which slice a given distribution key
value will go to, so I can deliberately put rows on each slice.
Where slice_num() is unreliable, what has to be done
instead is write a single row, then check stv_blocklist to
find the column number, and then truncate the table, and then repeat
until a value is found for each slice.
So to work around one bug, I had to work around another bug.
You can’t help but feel at times Redshift is a bit of a clunker.
Redshift in the eyes of the dev
teamThe rest of us
I realised something which actually came into being a little
while ago; when the devs introduced automatic sortkey selection, and
made this the default, it is not longer possible to keep unsorted
tables.
To be more exact, you can create unsorted tables, but because such
tables are created by not specifying a sorting type, and now not
specifying a sorting type has Redshift set the sorting type to
auto, which gives Redshift carte blanche to change the
sorting type, when you make an unsorted table, it can stop being an
unsorted table at any time.
This breaks existing systems, because deliberately using unsorted
tables is a perfectly valid design choice (with small tables and many
slices, they save a lot of disk space), and it also breaks my
test work, because I’ve been using unsorted tables as they’re simpler to
work with by not having an unsorted segment.
I have very strong feelings about this. The devs should
never repeat never break existing
systems - let alone doing so silently - and they should
never get in the way of system developers and admin running
their own cluster.
The problem is I suspect that the devs don’t know when
they’re making breaking changes; this isn’t the first time. I suspect
all their work is based on fleet telemetry, and they’re not actually in
touch with users at all.
Further Questions
What happens with queries accessing three columns, ideally the
first, second and third columns? do we see a further slowdown over the
slowdown for the first and second columns, on the order we’d expect for
the extra column, the third column, which should be the next slowest
column to access.
Credits
This white paper was published on r/aws, leading to a review by one
of the moderators, which identified two flaws in the test method. These
were corrected, the test re-run, the white paper updated with the
results, and then republished.
Revision History
v1
Initial release.
v2
Metadata changes. No content changes.
v3
After posting in r/aws, a subreddit mod reviewed the
white paper and pointed out flaws in the test method. As a result, the
following changes were made;
Test method now for each test specifies the test table size in
blocks per column per slice, rather than total number of rows. The
blocks are exactly full.
The test table is now dropped, created and populated between every
test SQL query, to help defeat caching.
The extra work create tables has made a full test run take many
hours, so the set of numbers of columns to test has been reduced to 2,
800 and 1600.
v4
No content changes; path to image file(s) changed.
v5
Changed to Redshift Research Project (AWS have a copyright on
“Amazon Redshift”).
v6
Added “About the Author”. made site name in title a link, and made
each chapter start a new page.
Appendix A : Raw Data Dump
Note these results are completely unprocessed; they are a raw dump of
the results, so the original, wholly unprocessed data, is available.
I am a C programmer - kernel development, high performance computing,
networking, data structures and so on.
I read the C. J. Date book, the classic text on relational database
theory, and having learned the principles, wrote a relational database
from scratch in C, which purely by chance set me up quite nicely for
what came next, moving into data engineering in late 2011, when I joined
as the back-end engineer two friends in their startup.
In that startup, I began using Redshift the day it came out, in 2012
(we had been trying to get into the beta programme).
We were early, heavy users for a year and a half, and I ending up
having monthly one-to-one meetings with one of the Redshift team
managers, where one or two features which are in Redshift today
originate from suggestions made in those meetings, such as the
distribution style ALL.
Once that was done, after a couple of years of non-Redshift data
engineering work, I returned to Redshift work, and then in about
mid-2018 contracted with a publisher to write a book about Redshift.
The book was largely written but it became apparent I wanted to do a
lot of things which couldn’t be done with a book - republish on every
new Redshift release, for example - and so in the end I stepped back
from the contract and developed the web-site, where I publish
investigation into, and ongoing monitoring of, Redshift.
So for many years now I’ve been investigating Redshift sub-systems
full-time, one by one, and this site and these investigations are as far
as I know the and the only source of this kind of information about
Redshift.
Redshift Cluster Cost
Reduction Service
I provide consultancy services for Redshift - advice, design,
training, getting failing systems back on their feet pronto, the usual
gamut - but in particular offer a Redshift cluster cost reduction
service, where the fee is and only is one month of the savings made.
Broadly speaking, to give guidance, savings are expected fall into
one of two categories; either something like 20%, or something like 80%.
The former is for systems where the business use case is such that
Redshift cannot be operated correctly, and this outcome requires no
fundamental re-engineering work, the latter is for systems where
Redshift can be operated correctly, and usually requires fundamental
re-engineering work (which you may or may not wish to engage in, despite
the cost savings, in which case we’re back to the 20%).
Details and contact information are on the web-site.
