MAX_ROW_SIZE Query Option
Ensures that Impala can process rows of at least the specified size. (Larger rows might be successfully processed, but that is not guaranteed.) Applies when constructing intermediate or final rows in the result set. This setting prevents out-of-control memory use when accessing columns containing huge strings.
Type: integer
Default:
524288 (512 KB)
Units: A numeric argument represents a size in bytes; you can also use a suffix of m
or mb for megabytes, or g or gb for gigabytes. If you
specify a value with unrecognized formats, subsequent queries fail with an error.
Added in: Impala 2.10.0
Usage notes:
If a query fails because it involves rows with long strings and/or
many columns, causing the total row size to exceed MAX_ROW_SIZE
bytes, increase the MAX_ROW_SIZE setting to accommodate
the total bytes stored in the largest row. Examine the error messages for any
failed queries to see the size of the row that caused the problem.
Impala attempts to handle rows that exceed the MAX_ROW_SIZE
value where practical, so in many cases, queries succeed despite having rows
that are larger than this setting.
Specifying a value that is substantially higher than actually needed can cause Impala to reserve more memory than is necessary to execute the query.
In a Hadoop cluster with highly concurrent workloads and queries that process
high volumes of data, traditional SQL tuning advice about minimizing wasted memory
is worth remembering. For example, if a table has STRING columns
where a single value might be multiple megabytes, make sure that the
SELECT lists in queries only refer to columns that are actually
needed in the result set, instead of using the SELECT * shorthand.
Examples:
The following examples show the kinds of situations where it is necessary to
adjust the MAX_ROW_SIZE setting. First, we create a table
containing some very long values in STRING columns:
create table big_strings (s1 string, s2 string, s3 string) stored as parquet;
-- Turn off compression to more easily reason about data volume by doing SHOW TABLE STATS.
-- Does not actually affect query success or failure, because MAX_ROW_SIZE applies when
-- column values are materialized in memory.
set compression_codec=none;
set;
...
MAX_ROW_SIZE: [524288]
...
-- A very small row.
insert into big_strings values ('one', 'two', 'three');
-- A row right around the default MAX_ROW_SIZE limit: a 500 KiB string and a 30 KiB string.
insert into big_strings values (repeat('12345',100000), 'short', repeat('123',10000));
-- A row that is too big if the query has to materialize both S1 and S3.
insert into big_strings values (repeat('12345',100000), 'short', repeat('12345',100000));
With the default MAX_ROW_SIZE setting, different queries succeed
or fail based on which column values have to be materialized during query processing:
-- All the S1 values can be materialized within the 512 KB MAX_ROW_SIZE buffer.
select count(distinct s1) from big_strings;
+--------------------+
| count(distinct s1) |
+--------------------+
| 2 |
+--------------------+
-- A row where even the S1 value is too large to materialize within MAX_ROW_SIZE.
insert into big_strings values (repeat('12345',1000000), 'short', repeat('12345',1000000));
-- The 5 MiB string is too large to materialize. The message explains the size of the result
-- set row the query is attempting to materialize.
select count(distinct(s1)) from big_strings;
WARNINGS: Row of size 4.77 MB could not be materialized in plan node with id 1.
Increase the max_row_size query option (currently 512.00 KB) to process larger rows.
-- If more columns are involved, the result set row being materialized is bigger.
select count(distinct s1, s2, s3) from big_strings;
WARNINGS: Row of size 9.54 MB could not be materialized in plan node with id 1.
Increase the max_row_size query option (currently 512.00 KB) to process larger rows.
-- Column S2, containing only short strings, can still be examined.
select count(distinct(s2)) from big_strings;
+----------------------+
| count(distinct (s2)) |
+----------------------+
| 2 |
+----------------------+
-- Queries that do not materialize the big column values are OK.
select count(*) from big_strings;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
The following examples show how adjusting MAX_ROW_SIZE upward
allows queries involving the long string columns to succeed:
-- Boosting MAX_ROW_SIZE moderately allows all S1 values to be materialized.
set max_row_size=7mb;
select count(distinct s1) from big_strings;
+--------------------+
| count(distinct s1) |
+--------------------+
| 3 |
+--------------------+
-- But the combination of S1 + S3 strings is still too large.
select count(distinct s1, s2, s3) from big_strings;
WARNINGS: Row of size 9.54 MB could not be materialized in plan node with id 1. Increase the max_row_size query option (currently 7.00 MB) to process larger rows.
-- Boosting MAX_ROW_SIZE to larger than the largest row in the table allows
-- all queries to complete successfully.
set max_row_size=12mb;
select count(distinct s1, s2, s3) from big_strings;
+----------------------------+
| count(distinct s1, s2, s3) |
+----------------------------+
| 4 |
+----------------------------+
The following examples show how to reason about appropriate values for
MAX_ROW_SIZE, based on the characteristics of the
columns containing the long values:
-- With a large MAX_ROW_SIZE in place, we can examine the columns to
-- understand the practical lower limit for MAX_ROW_SIZE based on the
-- table structure and column values.
select max(length(s1) + length(s2) + length(s3)) / 1e6 as megabytes from big_strings;
+-----------+
| megabytes |
+-----------+
| 10.000005 |
+-----------+
-- We can also examine the 'Max Size' for each column after computing stats.
compute stats big_strings;
show column stats big_strings;
+--------+--------+------------------+--------+----------+-----------+
| Column | Type | #Distinct Values | #Nulls | Max Size | Avg Size |
+--------+--------+------------------+--------+----------+-----------+
| s1 | STRING | 2 | -1 | 5000000 | 2500002.5 |
| s2 | STRING | 2 | -1 | 10 | 7.5 |
| s3 | STRING | 2 | -1 | 5000000 | 2500005 |
+--------+--------+------------------+--------+----------+-----------+
Related information:
BUFFER_POOL_LIMIT Query Option, DEFAULT_SPILLABLE_BUFFER_SIZE Query Option, MIN_SPILLABLE_BUFFER_SIZE Query Option, Scalability Considerations for Impala