Build tested: 5.6.1 ( Drone #2273 )
VM memory=16gb
Dataset: number 0 to 32M, loaded 10 times, a total of 320000010
1. Reproduced issue
5.5.2-1
|
|
|
MariaDB [mytest]> select a, sum(a), count(*), avg(a) from test group by a order by 1 desc limit 30;
|
ERROR 1815 (HY000): Internal error: IDB-2003: Aggregation/Distinct memory limit is exceeded.
|
|
|
5.6.1, with AllowDiskBasedAggregation disabled:
|
|
|
MariaDB [mytest]> select a, sum(a), count(*), avg(a) from test group by a order by 1 desc limit 30;
|
ERROR 1815 (HY000): Internal error: TupleAggregateStep::threadedAggregateRowGroups()[2] IDB-2003: Aggregation/Distinct memory limit is exceeded.
|
2. Functional test
After setting AllowDiskBasedAggregation to 'Y' and restarted mariadb and mariadb-columnstore, the /var/lib/columnstore/disk-based-aggr-tmpdir directory is automatically created.
|
|
|
/var/lib/columnstore/disk-based-aggr-tmpdir contains files like the following:
|
|
|
Agg-p10099-t0x7f44656160a0-rg54-g8 Agg-p10099-t0x7f446561a100-rg147-g5 Agg-PosHash-p10099-t0x7f44656368e0-g5
|
Agg-p10099-t0x7f44656160a0-rg54-g9 Agg-p10099-t0x7f446561a100-rg147-g9 Agg-PosHash-p10099-t0x7f44656368e0-g6
|
Agg-p10099-t0x7f44656160a0-rg55-g0 Agg-p10099-t0x7f446561a100-rg148-g0 Agg-PosHash-p10099-t0x7f44656368e0-g7
|
Agg-p10099-t0x7f44656160a0-rg55-g1 Agg-p10099-t0x7f446561a100-rg148-g20 Agg-PosHash-p10099-t0x7f44656368e0-g8
|
Agg-p10099-t0x7f44656160a0-rg55-g10 Agg-p10099-t0x7f446561a100-rg148-g25 Agg-PosHash-p10099-t0x7f44656368e0-g9
|
|
|
[centos8:root~]# du -sh disk-based-aggr-tmpdir
|
31G disk-based-aggr-tmpdir
|
|
|
These temp files were removed after query execution
|
|
|
[centos8:root~]# ls -sh /data/qa/shares/mcol563/mcol563.dat
|
2.9G /data/qa/shares/mcol563/mcol563.dat
|
|
|
The same query executed successfully.
|
3. Performance comparison
5.6.1-1
|
TotalUmMemory=25%
|
AllowDiskBasedAggregation=N
|
|
|
MariaDB [mytest]> select a, sum(a), count(*), avg(a) from test group by a order by 1 desc limit 30;
|
|
|
+----------+-----------+----------+---------------+
|
| a | sum(a) | count(*) | avg(a) |
|
+----------+-----------+----------+---------------+
|
| 32000000 | 320000000 | 10 | 32000000.0000 |
|
| 31999999 | 319999990 | 10 | 31999999.0000 |
|
| 31999998 | 319999980 | 10 | 31999998.0000 |
|
| 31999997 | 319999970 | 10 | 31999997.0000 |
|
| 31999996 | 319999960 | 10 | 31999996.0000 |
|
| 31999995 | 319999950 | 10 | 31999995.0000 |
|
| 31999994 | 319999940 | 10 | 31999994.0000 |
|
| 31999993 | 319999930 | 10 | 31999993.0000 |
|
| 31999992 | 319999920 | 10 | 31999992.0000 |
|
| 31999991 | 319999910 | 10 | 31999991.0000 |
|
| 31999990 | 319999900 | 10 | 31999990.0000 |
|
| 31999989 | 319999890 | 10 | 31999989.0000 |
|
| 31999988 | 319999880 | 10 | 31999988.0000 |
|
| 31999987 | 319999870 | 10 | 31999987.0000 |
|
| 31999986 | 319999860 | 10 | 31999986.0000 |
|
| 31999985 | 319999850 | 10 | 31999985.0000 |
|
| 31999984 | 319999840 | 10 | 31999984.0000 |
|
| 31999983 | 319999830 | 10 | 31999983.0000 |
|
| 31999982 | 319999820 | 10 | 31999982.0000 |
|
| 31999981 | 319999810 | 10 | 31999981.0000 |
|
| 31999980 | 319999800 | 10 | 31999980.0000 |
|
| 31999979 | 319999790 | 10 | 31999979.0000 |
|
| 31999978 | 319999780 | 10 | 31999978.0000 |
|
| 31999977 | 319999770 | 10 | 31999977.0000 |
|
| 31999976 | 319999760 | 10 | 31999976.0000 |
|
| 31999975 | 319999750 | 10 | 31999975.0000 |
|
| 31999974 | 319999740 | 10 | 31999974.0000 |
|
| 31999973 | 319999730 | 10 | 31999973.0000 |
|
| 31999972 | 319999720 | 10 | 31999972.0000 |
|
| 31999971 | 319999710 | 10 | 31999971.0000 |
|
+----------+-----------+----------+---------------+
|
30 rows in set (1 min 43.414 sec)
|
|
|
TotalUmMemory=25%
|
AllowDiskBasedAggregation=Y
|
|
|
MariaDB [mytest]> select a, sum(a), count(*), avg(a) from test group by a order by 1 desc limit 30;
|
+----------+-----------+----------+---------------+
|
| a | sum(a) | count(*) | avg(a) |
|
+----------+-----------+----------+---------------+
|
| 32000000 | 320000000 | 10 | 32000000.0000 |
|
| 31999999 | 319999990 | 10 | 31999999.0000 |
|
| 31999998 | 319999980 | 10 | 31999998.0000 |
|
| 31999997 | 319999970 | 10 | 31999997.0000 |
|
| 31999996 | 319999960 | 10 | 31999996.0000 |
|
| 31999995 | 319999950 | 10 | 31999995.0000 |
|
| 31999994 | 319999940 | 10 | 31999994.0000 |
|
| 31999993 | 319999930 | 10 | 31999993.0000 |
|
| 31999992 | 319999920 | 10 | 31999992.0000 |
|
| 31999991 | 319999910 | 10 | 31999991.0000 |
|
| 31999990 | 319999900 | 10 | 31999990.0000 |
|
| 31999989 | 319999890 | 10 | 31999989.0000 |
|
| 31999988 | 319999880 | 10 | 31999988.0000 |
|
| 31999987 | 319999870 | 10 | 31999987.0000 |
|
| 31999986 | 319999860 | 10 | 31999986.0000 |
|
| 31999985 | 319999850 | 10 | 31999985.0000 |
|
| 31999984 | 319999840 | 10 | 31999984.0000 |
|
| 31999983 | 319999830 | 10 | 31999983.0000 |
|
| 31999982 | 319999820 | 10 | 31999982.0000 |
|
| 31999981 | 319999810 | 10 | 31999981.0000 |
|
| 31999980 | 319999800 | 10 | 31999980.0000 |
|
| 31999979 | 319999790 | 10 | 31999979.0000 |
|
| 31999978 | 319999780 | 10 | 31999978.0000 |
|
| 31999977 | 319999770 | 10 | 31999977.0000 |
|
| 31999976 | 319999760 | 10 | 31999976.0000 |
|
| 31999975 | 319999750 | 10 | 31999975.0000 |
|
| 31999974 | 319999740 | 10 | 31999974.0000 |
|
| 31999973 | 319999730 | 10 | 31999973.0000 |
|
| 31999972 | 319999720 | 10 | 31999972.0000 |
|
| 31999971 | 319999710 | 10 | 31999971.0000 |
|
+----------+-----------+----------+---------------+
|
30 rows in set (13 min 6.513 sec)
|
|
|
It took 7.6x long to execute the query using disk.
|
|
|
I am using virtual machine and virtual disk. This could be contributing to the slowness.
|
|
Another test run
5.6.1-1
TotalUmMemory=25%
AllowDiskBasedAggregation=Y
10g DBT3 lineitem dataset, loaded 5 times (299930260 rows)
disk run (right after cpimport)
MariaDB [mytest]> select l_orderkey, count(*), sum(l_extendedprice), avg(l_discount) from lineitem group by l_orderkey order by 1 desc limit 30;
|
+------------+----------+----------------------+-----------------+
|
| l_orderkey | count(*) | sum(l_extendedprice) | avg(l_discount) |
|
+------------+----------+----------------------+-----------------+
|
| 60000000 | 35 | 1812257.45 | 0.032857 |
|
| 59999975 | 5 | 211748.85 | 0.100000 |
|
| 59999974 | 5 | 430638.55 | 0.070000 |
|
| 59999973 | 5 | 122330.00 | 0.050000 |
|
| 59999972 | 35 | 1051620.90 | 0.060000 |
|
| 59999971 | 15 | 352397.85 | 0.056667 |
|
| 59999970 | 20 | 1055263.00 | 0.050000 |
|
| 59999969 | 30 | 743641.60 | 0.036667 |
|
| 59999968 | 30 | 1448658.35 | 0.063333 |
|
| 59999943 | 25 | 903624.00 | 0.052000 |
|
| 59999942 | 20 | 802560.90 | 0.040000 |
|
| 59999941 | 5 | 23182.05 | 0.020000 |
|
| 59999940 | 10 | 543875.35 | 0.045000 |
|
| 59999939 | 5 | 95581.80 | 0.070000 |
|
| 59999938 | 10 | 248193.30 | 0.085000 |
|
| 59999937 | 25 | 979903.15 | 0.076000 |
|
| 59999936 | 35 | 1121067.90 | 0.064286 |
|
| 59999911 | 15 | 492170.45 | 0.036667 |
|
| 59999910 | 5 | 286220.25 | 0.050000 |
|
| 59999909 | 30 | 877935.40 | 0.036667 |
|
| 59999908 | 5 | 297051.75 | 0.090000 |
|
| 59999907 | 15 | 697294.25 | 0.063333 |
|
| 59999906 | 25 | 956472.50 | 0.054000 |
|
| 59999905 | 35 | 1694750.65 | 0.045714 |
|
| 59999904 | 30 | 1352077.20 | 0.035000 |
|
| 59999879 | 25 | 1333028.20 | 0.040000 |
|
| 59999878 | 25 | 854084.70 | 0.036000 |
|
| 59999877 | 30 | 1193381.25 | 0.051667 |
|
| 59999876 | 5 | 177069.00 | 0.020000 |
|
| 59999875 | 30 | 1203320.70 | 0.055000 |
|
+------------+----------+----------------------+-----------------+
|
30 rows in set (2 min 7.935 sec)
|
cached run (2nd run)
MariaDB [mytest]> select l_orderkey, count(*), sum(l_extendedprice), avg(l_discount) from lineitem group by l_orderkey order by 1 desc limit 30;
|
+------------+----------+----------------------+-----------------+
|
| l_orderkey | count(*) | sum(l_extendedprice) | avg(l_discount) |
|
+------------+----------+----------------------+-----------------+
|
| 60000000 | 35 | 1812257.45 | 0.032857 |
|
| 59999975 | 5 | 211748.85 | 0.100000 |
|
| 59999974 | 5 | 430638.55 | 0.070000 |
|
| 59999973 | 5 | 122330.00 | 0.050000 |
|
| 59999972 | 35 | 1051620.90 | 0.060000 |
|
| 59999971 | 15 | 352397.85 | 0.056667 |
|
| 59999970 | 20 | 1055263.00 | 0.050000 |
|
| 59999969 | 30 | 743641.60 | 0.036667 |
|
| 59999968 | 30 | 1448658.35 | 0.063333 |
|
| 59999943 | 25 | 903624.00 | 0.052000 |
|
| 59999942 | 20 | 802560.90 | 0.040000 |
|
| 59999941 | 5 | 23182.05 | 0.020000 |
|
| 59999940 | 10 | 543875.35 | 0.045000 |
|
| 59999939 | 5 | 95581.80 | 0.070000 |
|
| 59999938 | 10 | 248193.30 | 0.085000 |
|
| 59999937 | 25 | 979903.15 | 0.076000 |
|
| 59999936 | 35 | 1121067.90 | 0.064286 |
|
| 59999911 | 15 | 492170.45 | 0.036667 |
|
| 59999910 | 5 | 286220.25 | 0.050000 |
|
| 59999909 | 30 | 877935.40 | 0.036667 |
|
| 59999908 | 5 | 297051.75 | 0.090000 |
|
| 59999907 | 15 | 697294.25 | 0.063333 |
|
| 59999906 | 25 | 956472.50 | 0.054000 |
|
| 59999905 | 35 | 1694750.65 | 0.045714 |
|
| 59999904 | 30 | 1352077.20 | 0.035000 |
|
| 59999879 | 25 | 1333028.20 | 0.040000 |
|
| 59999878 | 25 | 854084.70 | 0.036000 |
|
| 59999877 | 30 | 1193381.25 | 0.051667 |
|
| 59999876 | 5 | 177069.00 | 0.020000 |
|
| 59999875 | 30 | 1203320.70 | 0.055000 |
|
+------------+----------+----------------------+-----------------+
|
30 rows in set (1 min 59.342 sec)
|
Fixed typos and added additional info for clarification.
With disk-base aggregation disabled and TotalUmMemory set to 50M, The test in my last comment failed with the aggregation error.
With disk-base aggregation disabled and TotalUmMemory set to back to the default value of 25%, I got the follow timing:
disk run (hot) = 57.488 seconds
cached run (code) = 53.324 seconds
Concurrentcy test:
disk-base aggregation enabled.
This test is to verify temp files are not overwriting each other and affecting final test results.
Execute the same test queries in three concurrency sessions and got identical results. Repeated the same concurrency test many times and got matching results.
Performance comparison when there is enough memory to run the query in-memory.
My understanding is that when there is enough to run the query in-memory,
even if disk-base aggr is enabled, in-memory aggr will be used
Therefore, performance timing should be similar.
25% toalummemory
Disk-based aggr enabled
disk run (cold): 30 rows in set (56.534 sec)
cached run (hot): 30 rows in set (55.388 sec)
Disk-based aggr disabled
disk run (cold): 30 rows in set (55.777 sec)
cached run (hot): 30 rows in set (52.182 sec)
I ran the same performance test that Daniel did against the same table:
select count(*) from test ;
|
+-----------+
|
| count(*) |
|
+-----------+
|
| 320000010 | |
+-----------+
|
The following tests were run with a debug build with 25% totalummemory and cached data. The aggregation is expected to fit into memory.
With disk aggregation off
select a, sum(a), count(*), avg(a) from test group by a order by 1 desc limit 30; |
+----------+-----------+----------+---------------+
|
| a | sum(a) | count(*) | avg(a) |
|
+----------+-----------+----------+---------------+
|
| 32000000 | 320000000 | 10 | 32000000.0000 | |
| 31999999 | 319999990 | 10 | 31999999.0000 | |
| 31999998 | 319999980 | 10 | 31999998.0000 | |
| 31999997 | 319999970 | 10 | 31999997.0000 | |
| 31999996 | 319999960 | 10 | 31999996.0000 | |
| 31999995 | 319999950 | 10 | 31999995.0000 | |
| 31999994 | 319999940 | 10 | 31999994.0000 | |
| 31999993 | 319999930 | 10 | 31999993.0000 | |
| 31999992 | 319999920 | 10 | 31999992.0000 | |
| 31999991 | 319999910 | 10 | 31999991.0000 | |
| 31999990 | 319999900 | 10 | 31999990.0000 | |
| 31999989 | 319999890 | 10 | 31999989.0000 | |
| 31999988 | 319999880 | 10 | 31999988.0000 | |
| 31999987 | 319999870 | 10 | 31999987.0000 | |
| 31999986 | 319999860 | 10 | 31999986.0000 | |
| 31999985 | 319999850 | 10 | 31999985.0000 | |
| 31999984 | 319999840 | 10 | 31999984.0000 | |
| 31999983 | 319999830 | 10 | 31999983.0000 | |
| 31999982 | 319999820 | 10 | 31999982.0000 | |
| 31999981 | 319999810 | 10 | 31999981.0000 | |
| 31999980 | 319999800 | 10 | 31999980.0000 | |
| 31999979 | 319999790 | 10 | 31999979.0000 | |
| 31999978 | 319999780 | 10 | 31999978.0000 | |
| 31999977 | 319999770 | 10 | 31999977.0000 | |
| 31999976 | 319999760 | 10 | 31999976.0000 | |
| 31999975 | 319999750 | 10 | 31999975.0000 | |
| 31999974 | 319999740 | 10 | 31999974.0000 | |
| 31999973 | 319999730 | 10 | 31999973.0000 | |
| 31999972 | 319999720 | 10 | 31999972.0000 | |
| 31999971 | 319999710 | 10 | 31999971.0000 | |
+----------+-----------+----------+---------------+
|
30 rows in set (3 min 1.853 sec) |
With disk aggregation on
select a, sum(a), count(*), avg(a) from test group by a order by 1 desc limit 30; |
+----------+-----------+----------+---------------+
|
| a | sum(a) | count(*) | avg(a) |
|
+----------+-----------+----------+---------------+
|
| 32000000 | 320000000 | 10 | 32000000.0000 | |
| 31999999 | 319999990 | 10 | 31999999.0000 | |
| 31999998 | 319999980 | 10 | 31999998.0000 | |
| 31999997 | 319999970 | 10 | 31999997.0000 | |
| 31999996 | 319999960 | 10 | 31999996.0000 | |
| 31999995 | 319999950 | 10 | 31999995.0000 | |
| 31999994 | 319999940 | 10 | 31999994.0000 | |
| 31999993 | 319999930 | 10 | 31999993.0000 | |
| 31999992 | 319999920 | 10 | 31999992.0000 | |
| 31999991 | 319999910 | 10 | 31999991.0000 | |
| 31999990 | 319999900 | 10 | 31999990.0000 | |
| 31999989 | 319999890 | 10 | 31999989.0000 | |
| 31999988 | 319999880 | 10 | 31999988.0000 | |
| 31999987 | 319999870 | 10 | 31999987.0000 | |
| 31999986 | 319999860 | 10 | 31999986.0000 | |
| 31999985 | 319999850 | 10 | 31999985.0000 | |
| 31999984 | 319999840 | 10 | 31999984.0000 | |
| 31999983 | 319999830 | 10 | 31999983.0000 | |
| 31999982 | 319999820 | 10 | 31999982.0000 | |
| 31999981 | 319999810 | 10 | 31999981.0000 | |
| 31999980 | 319999800 | 10 | 31999980.0000 | |
| 31999979 | 319999790 | 10 | 31999979.0000 | |
| 31999978 | 319999780 | 10 | 31999978.0000 | |
| 31999977 | 319999770 | 10 | 31999977.0000 | |
| 31999976 | 319999760 | 10 | 31999976.0000 | |
| 31999975 | 319999750 | 10 | 31999975.0000 | |
| 31999974 | 319999740 | 10 | 31999974.0000 | |
| 31999973 | 319999730 | 10 | 31999973.0000 | |
| 31999972 | 319999720 | 10 | 31999972.0000 | |
| 31999971 | 319999710 | 10 | 31999971.0000 | |
+----------+-----------+----------+---------------+
|
30 rows in set (3 min 5.229 sec) |
Build test: 5.6.1 ( Drone #2319 )
I executed the same lineitem test for the following:
TotalUMMemory: 50M
Disk-base aggregation: enabled
The query returned the following error:
ERROR 1815 (HY000) at line 1: Internal error: TupleAggregateStep::doThreadedAggregate() IDB-2056: There was an IO error during a disk-based aggregation.
I repeated the test few times, and got the same error.
alexey.antipovskyPlease run a test on you system using the same build to see if this also hapens to you. Thanks
Build verified: 5.6.1-1 ( Drone #2338 )
There are additional improvements waiting for unit test. When this PR is ready we'll get it back to testing.
Build tested: 5.6.1 (Drone #2434)
VM memory: 4GB
TotalUmMemory=25%
AllowDiskBasedAggregation=Y
10g DBT3 lineitem dataset, loaded 5 times (299930260 rows)
Test query: select l_orderkey, count
, sum(l_extendedprice), avg(l_discount) from lineitem group by l_orderkey order by 1 desc limit 30;
1. Execute time is about 8.5% faster than previous build for disk run
2. Execute time is virtually the same for cached run
Drone #2273
|
disk-run: 30 rows in set (2 min 7.935 sec)
|
cached-run: 30 rows in set (1 min 59.342 sec)
|
|
|
Drone #2434
|
disk-run: 30 rows in set (1 min 57.961 sec)
|
cached-run: 30 rows in set (1 min 59.468 sec)
|
3. Max tmp file utilization is way down, 1.3gb compared to 31gb used by previous build
4. tmp files are 4 to 20kb each
[centos8:root~]# ls -al disk-based-aggr-tmpdir/
|
total 76
|
[centos8:root~]# ls disk-based-aggr-tmpdir -al
|
total 220
|
drwxr-xr-x 19 mysql mysql 4096 May 25 17:42 .
|
drwxr-xr-x 6 mysql mysql 4096 May 25 17:42 ..
|
drwxr-xr-x 2 mysql mysql 12288 May 25 17:43 p6793-t0x7fa132657000
|
drwxr-xr-x 2 mysql mysql 12288 May 25 17:43 p6793-t0x7fa132657140
|
drwxr-xr-x 2 mysql mysql 12288 May 25 17:43 p6793-t0x7fa132657280
|
drwxr-xr-x 2 mysql mysql 12288 May 25 17:43 p6793-t0x7fa1326573c0
|
drwxr-xr-x 2 mysql mysql 12288 May 25 17:43 p6793-t0x7fa132657500
|
drwxr-xr-x 2 mysql mysql 12288 May 25 17:43 p6793-t0x7fa132657640
|
drwxr-xr-x 2 mysql mysql 12288 May 25 17:43 p6793-t0x7fa132657780
|
drwxr-xr-x 2 mysql mysql 16384 May 25 17:43 p6793-t0x7fa1326578c0
|
drwxr-xr-x 2 mysql mysql 16384 May 25 17:43 p6793-t0x7fa132657a00
|
drwxr-xr-x 2 mysql mysql 12288 May 25 17:43 p6793-t0x7fa132657b40
|
drwxr-xr-x 2 mysql mysql 20480 May 25 17:43 p6793-t0x7fa132657c80
|
drwxr-xr-x 2 mysql mysql 12288 May 25 17:43 p6793-t0x7fa132657dc0
|
drwxr-xr-x 2 mysql mysql 12288 May 25 17:43 p6793-t0x7fa132657f00
|
drwxr-xr-x 2 mysql mysql 12288 May 25 17:43 p6793-t0x7fa132658040
|
drwxr-xr-x 2 mysql mysql 12288 May 25 17:43 p6793-t0x7fa132658180
|
drwxr-xr-x 2 mysql mysql 12288 May 25 17:43 p6793-t0x7fa1326582c0
|
drwxr-xr-x 2 mysql mysql 4096 May 25 17:42 p6793-t0x7fa137e82440
|
|
5. What is the actual location of the aggr temp files?
Columnstore.xml indicates "<!-- <TempDir>/tmp/cs-agg</TempDir> -->"
But I found files in /var/lib/columnstore/disk-based-aggr-tmpdir
Default temp space is /var/lib/columnstore/disk-based-aggr-tmpdir. The line in the XML is commented as a placeholder if you wish to change the tempdir. I have to do that because my /var/lib disk is tiny and I need to move it to a larger disk.
Build tested: 5.6.1 (Drone #2452)
The consistency issue for aggregation tmp directory in Columnstore.xml has been corrected.
<!-- <TempDir>/var/lib/columnstore/disk-based-aggr-tmpdir</TempDir> -->
Let's simplify the tmpdir path.
Just for fun, let me note that the entry
<TempFileDir>/columnstore_tmp_files</TempFileDir>
doesn't appear to be used anymore.
On Thu, May 27, 2021 at 11:13 AM Gregory Dorman (Jira) <jira@mariadb.org>
Build tested: 5.6.1 ( Drone #2466)
/tmp/columnstore_tmp_files/aggregates is not used as the tmp directory for aggregation. But this directory is no longer in the Columnstore.xml file as the default tmp directory. Can user still modify the Columnstore.xml file to relocate the tmp directory?
Build tested: 5.6.1 Engine: Drone #2503, CMAPI: Drone #490
VM memory: 16gb
Dataset: 10G DBT3 lineitem, loaded 5 times.
Test #1
TotalUmMemory: 25%
AllowDiskBasedAggregation: N
Tests performed as expected
Test #2
TotalUmMemory: 25%
AllowDiskBasedAggregation: Y
Tests performed as expected. Disk-based aggregation was not used since there is enough memory to perform in-memory aggregation
Test #3
TotalUmMemory: 1GB
AllowDiskBasedAggregation: Y
Disk-based aggregation was used as expected.
After installation and data loading, my first test hit the following error. I repeated the test 9 more times and all worked fine. I restarted ColumnStore and tried it again. That also worked. I also truncated and loaded the lineitem table. The test also worked 5 times in a row. I don't know what triggered the first failure.
select l_orderkey, count(*), sum(l_extendedprice), avg(l_discount) from lineitem group by l_orderkey order by 1 desc limit 30;
|
|
|
ERROR 1815 (HY000): Internal error: TupleAggregateStep::doThreadedAggregate() IDB-2056: There was an IO error during a disk-based aggregation: No such file or directory
|
err.log
Jun 3 17:10:23 centos-8 threadpool[8235]: 23.816630 |0|0|0| E 22 CAL0005: ThreadPool: Caught exception during execution: IDB-2056: There was an IO error during a disk-based aggregation: No such file or directory
Jun 3 17:10:24 centos-8 threadpool[8235]: 24.012870 |0|0|0| E 22 CAL0005: ThreadPool: Caught exception during execution: IDB-2056: There was an IO error during a disk-based aggregation: No such file or directory
Jun 3 17:10:24 centos-8 threadpool[8235]: 24.106256 |0|0|0| E 22 CAL0005: ThreadPool: Caught exception during execution: IDB-2056: There was an IO error during a disk-based aggregation: No such file or directory
Jun 3 17:10:24 centos-8 threadpool[8235]: 24.352455 |0|0|0| E 22 CAL0005: ThreadPool: Caught exception during execution: IDB-2056: There was an IO error during a disk-based aggregation: No such file or directory
Jun 3 17:10:24 centos-8 joblist[8235]: 24.913216 |3|0|0| C 05 CAL0000: TupleAggregateStep::doThreadedAggregate() IDB-2056: There was an IO error during a disk-based aggregation: No such file or directory
Build verified: 5.6.1 ( Drone #2524)
Verified that the first disk-based aggregate queries no longer reporting the missing file error. The query works now.
Build verified: 5.6.1 ( Drone #2537)
Verified with this latest build
Build verified: 5.6.1 ( Drone #2551)
Reproduced the issue with build 2537 using info from the Google Drive link above and verified the fix in build #2551.
Both queries did use the tmp directory for aggregation temp files. I used the temp directory for a very short time and the customer may not have caught it in time.