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: CDH 5.13.0 / 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.

If you are upgrading Impala to CDH 5.13 / Impala 2.10 or higher from CDH 5.12 / Impala 2.9 or lower, follow the instructions in CDH 5.13 / Impala 2.10 to check if your queries are affected by these changes and to modify your configuration settings if so. This advice is especially important for any users who have increased the --read_size configuration setting from its default value of 8 MB.

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, CDH 5.13