MonetDB heap bug?

接前面的测试 : 

http://blog.163.com/digoal@126/blog/static/1638770402014714104326879/

本文用到的2个测试表, 分别有60个字段, 其中1个字段为INT, 全唯一, 其他varchar(64), 全随机.

其中一个表b 5000万记录, 另一个表c 2500万记录.

数据库总共占用215GB.

数据库服务器内存+SWAP一共97GB.

但是执行某些查询的时候, 数据库目录会暴增到1.1TB, 例如

不带INT类型的分组查询. 

sql>select count(*) from (select c2,c11,c21,c31,c41,c51,c60 from b group by c2,c11,c21,c31,c41,c51,c60) as t;
+----------+
| L1       |
+==========+
| 50000000 |
+----------+
1 tuple (10m 21s)

某些关联则需要更多空间, 甚至导致空间爆满. 报错.

如下 : 

sql>select count(*) from b,c where b.c4=c.c5 and b.c2=c.c3;
+------+
| L1   |
+======+
|    0 |
+------+
1 tuple (31.8s)
sql>select count(*) from b,c where b.c4=c.c5 or b.c2=c.c3;
GDK reported error.
HEAPalloc: Insufficient space for HEAP of 10000000000000000 bytes.
HEAPalloc: Insufficient space for HEAP of 10000000000000000 bytes.
sql>select count(*) from b,c where b.c4=c.c5 or b.c2=c.c3;
could not allocate space for
HEAPextend: failed to extend to 115215433728 for 54/5473.theap: h->storage == STORE_MEM && can_map && fd >= 0 && HEAPload_intern() < 0

存储信息, 注意column size和heap size. 

int类型heapsize=0 

sql>select * from storage where "table" in('b','c');
+--------+-------+-----------+---------+----------+----------+-----------+------------+------------+---------+--------+
| schema | table | column    | type    | location | count    | typewidth | columnsize | heapsize   | indices | sorted |
+========+=======+===========+=========+==========+==========+===========+============+============+=========+========+
| sys    | b     | c1        | int     | 10/1070  | 50000000 |         4 |  200000000 |          0 |       0 | false  |
| sys    | b     | c2        | varchar | 10/1072  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c3        | varchar | 11/1100  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c4        | varchar | 10/1073  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c5        | varchar | 10/1071  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c6        | varchar | 10/1076  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c7        | varchar | 10/1074  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c8        | varchar | 14/1443  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c9        | varchar | 14/1444  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c10       | varchar | 14/1445  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c11       | varchar | 14/1446  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c12       | varchar | 14/1447  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c13       | varchar | 14/1450  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c14       | varchar | 14/1451  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c15       | varchar | 14/1452  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c16       | varchar | 14/1453  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c17       | varchar | 14/1454  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c18       | varchar | 14/1455  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c19       | varchar | 14/1456  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c20       | varchar | 14/1457  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c21       | varchar | 14/1460  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c22       | varchar | 14/1461  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c23       | varchar | 14/1462  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c24       | varchar | 14/1463  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c25       | varchar | 14/1464  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c26       | varchar | 14/1465  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c27       | varchar | 14/1466  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c28       | varchar | 14/1467  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c29       | varchar | 14/1470  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c30       | varchar | 14/1471  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c31       | varchar | 14/1472  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c32       | varchar | 14/1473  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c33       | varchar | 14/1474  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c34       | varchar | 14/1475  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c35       | varchar | 14/1476  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c36       | varchar | 14/1477  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c37       | varchar | 15/1500  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c38       | varchar | 15/1501  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c39       | varchar | 15/1502  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c40       | varchar | 15/1503  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c41       | varchar | 15/1504  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c42       | varchar | 15/1505  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c43       | varchar | 15/1506  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c44       | varchar | 15/1507  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c45       | varchar | 15/1510  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c46       | varchar | 15/1511  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c47       | varchar | 15/1512  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c48       | varchar | 15/1513  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c49       | varchar | 15/1514  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c50       | varchar | 15/1515  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c51       | varchar | 15/1516  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c52       | varchar | 15/1517  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c53       | varchar | 15/1520  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c54       | varchar | 15/1521  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c55       | varchar | 15/1522  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c56       | varchar | 15/1523  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c57       | varchar | 15/1524  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c58       | varchar | 15/1525  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c59       | varchar | 15/1526  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | c60       | varchar | 15/1527  | 50000000 |        34 |  200000000 | 2400321536 |       0 | false  |
| sys    | b     | b_c1_pkey | oid     | 13/1316  |        0 |         8 |          0 |          0 |       0 | true   |
| sys    | c     | c1        | int     | 10/1035  | 25000000 |         4 |  100000000 |          0 |       0 | true   |
| sys    | c     | c2        | varchar | 06/675   | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c3        | varchar | 03/370   | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c4        | varchar | 27/2774  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c5        | varchar | 27/2775  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c6        | varchar | 27/2776  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c7        | varchar | 27/2777  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c8        | varchar | 30/3000  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c9        | varchar | 30/3001  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c10       | varchar | 30/3002  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c11       | varchar | 30/3003  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c12       | varchar | 30/3004  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c13       | varchar | 30/3005  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c14       | varchar | 30/3006  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c15       | varchar | 30/3007  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c16       | varchar | 30/3010  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c17       | varchar | 30/3011  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c18       | varchar | 30/3012  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c19       | varchar | 30/3013  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c20       | varchar | 30/3014  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c21       | varchar | 30/3015  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c22       | varchar | 30/3016  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c23       | varchar | 30/3017  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c24       | varchar | 30/3020  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c25       | varchar | 30/3021  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c26       | varchar | 30/3022  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c27       | varchar | 30/3023  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c28       | varchar | 30/3024  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c29       | varchar | 30/3025  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c30       | varchar | 30/3026  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c31       | varchar | 30/3027  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c32       | varchar | 30/3030  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c33       | varchar | 30/3031  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c34       | varchar | 30/3032  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c35       | varchar | 30/3033  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c36       | varchar | 30/3034  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c37       | varchar | 30/3035  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c38       | varchar | 30/3036  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c39       | varchar | 30/3037  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c40       | varchar | 30/3040  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c41       | varchar | 30/3041  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c42       | varchar | 30/3042  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c43       | varchar | 30/3043  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c44       | varchar | 30/3044  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c45       | varchar | 30/3045  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c46       | varchar | 30/3046  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c47       | varchar | 30/3047  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c48       | varchar | 30/3050  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c49       | varchar | 30/3051  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c50       | varchar | 30/3052  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c51       | varchar | 30/3053  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c52       | varchar | 30/3054  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c53       | varchar | 30/3055  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c54       | varchar | 30/3056  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c55       | varchar | 30/3057  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c56       | varchar | 30/3060  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c57       | varchar | 30/3061  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c58       | varchar | 30/3062  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c59       | varchar | 30/3063  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
| sys    | c     | c60       | varchar | 30/3064  | 25000000 |        34 |  100000000 | 1200160768 |       0 | false  |
+--------+-------+-----------+---------+----------+----------+-----------+------------+------------+---------+--------+
121 tuples (7.312ms)

目前尚不清楚是不是MonetDB的BUG, 看起来不适合字符串这样的变长类型. 

需要了解详情需对MonetDB代码有一定了解.

如果要使用的话, 建议将字符串类型字典话, 使用固定长度的类型, 例如使用INT类型或者INT8类型替代.

后面会测试一下int类型的性能.

时间： 2025-01-26 17:35:16

MonetDB heap bug?的相关文章

MonetDB bulk load performance &amp; fixed length type performance &amp; JOIN performance

之前写过一篇关于PostgreSQL使用UUID作为PK, 造成写入性能很差的分析文章, 有兴趣的童鞋可参考如下 :  http://blog.163.com/digoal@126/blog/static/16387704020129249646421/ 本文主要测试一下几个因素对导入速度的影响. 1. 索引列无序导入 2. 索引列顺序导入 3. 无索引无序导入(指某列无序) 4. 无索引顺序导入(指某列顺序) 测试数据使用的是前面一篇BLOG的测试数据, 60个字段, 第一个字段是INT, 主

[MySQL 源码] 关于bug#65389的碎碎念

[MySQL Bug] bug#65389  MVCC IS BROKEN WITH IMPLICIT LOCK 该bug在5.5.26中被修复,changelog的描述如下: If a row was deleted from an InnoDB table, then another row was re-inserted with the same primary key value, an attempt by a concurrent transaction to lock the r

shared pool系列一：heap /extent /chunk/x$ksmsp

介绍shared pool物理结构的heap 堆,extent 区,chunk 内存中:共享池.大池.PGA是heap管理 chunk shared pool物理层面上由许多内存块组成,这些内在块称为chunk,chunk是shared pool中内存分配最小单位-类似extent,但是chunk是大小不一的,在内存中一个chunk是连续的. chunk属于可用类型的时候,既不属于library cache,也不属于dictionary cache, 如果chunk被用于存放SQL相关的数据时,

wingdb开发过程中遇到一个比较“头疼”的bug

    我自持也写了XX行的代码,调试经验也不可谓不强.但是最近遇到的这个bug,却 着实让我头疼了一会...错!不是一会,是3天啊!     具体是这样的,在开发windows下的gdb GUI调试环境时,使用thread PIPE与gdb mi 接口交互,在winodows 7下编写代码.上周五突然遇到一个问题:就是程序运行着就会突 然崩溃.马上用ollydbg作为系统默认活动调试器,在崩溃后立即调试进程,发现错误点 随机出现在主线程和PIPE READ线程中,而且指令在ntdll中出错.并

PostgreSQL pending patch : fail-back without fresh backup (have bug?)

[补充说明] 本文测试fail-back未成功的bug是-m immediate的一个bug, 已经修复 :  http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=91c3613d3748d881706c3e60d8221ea92833ac1a http://blog.163.com/digoal@126/blog/static/16387704020137894542352/ [正文] PostgreSQL的流复制应用非常

服务器-关于tomcat报OutOfMemoryError: Java heap space

问题描述 关于tomcat报OutOfMemoryError: Java heap space 我有台16G内存的服务器,JVM用的默认值(听说默认最小是1/64,最大是1/4即256M和4096M), 但是运行一段时间后报了java.lang.OutOfMemoryError: Java heap space异常,tomcat也挂了,我远程服务器上去看,发现tomcat只占了40万K的内存, 总内存也只占用了20%,倒是CPU占在70%(正常情况下应该是10%以下). 求支招啊,怎么解决. 我

Stack and heap（from others）

Stack and heap(from others) Java栈与堆  ----对这两个概念的不明好久,终于找到一篇好文,拿来共享  1. 栈(stack)与堆(heap)都是Java用来在Ram中存放数据的地方.与C++不同,Java自动管理栈和堆,程序员不能直接地设置栈或堆.  2. 栈的优势是,存取速度比堆要快,仅次于直接位于CPU中的寄存器.但缺点是,存在栈中的数据大小与生存期必须是确定的,缺乏灵活性.另外,栈数据可以共享,详见第3点.堆的优势是可以动态地分配内存大小,生存期也不必事先

谷歌修复了 FFmpeg 中上千个 bug

谷歌在科技业界中几乎每天都会创造出新闻素材,它的触手涉及到了生活中的多个领域.最近谷歌将其Google +社交网络与邮件服务Gmail相结合.然而今天谷歌宣布他们修复了FFmpeg的上千个bug. FFmpeg是一个跨平台多媒体库解决方案.谷歌表示该平台的安全性能是非常重要的,他们也利用了自己无可比拟的计算以及数据优势进行了大规模的安全演习.谷歌对FFmpeg的优化持续了两年之久,他们修复了其中的上千个bug. FFmpeg在Chrome, MPlayer, VLC, xine中都有应用,因此谷

MQ V6 6.0.2.5/V6 6.0.2.4/V5.3 csd14 调用 MQCONNX() 的bug?

问题描述 我发现一个奇怪的现象我以前的程序,在MQV6.0.2.2andV5.3csd07/csd08/csd12都跑的好好的,但是用了MQV66.0.2.5/V66.0.2.4/V5.3csd14之后,执行MQCONNX,而且我的机器不接网线的时候,就会出现错误,在VS2005下HEAP:FreeHeapblock5079c70modifiedat507a318afteritwasfreed我觉得这个一个mq补丁的bug,为了证实这一点,我把代码进行了简化,挑出了最关键的几句,直接放在一个全新