developers-list
Threads by month
- ----- 2024 -----
- May
- April
- March
- February
- January
- ----- 2023 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2022 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2021 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2020 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2019 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2018 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2017 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2016 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2015 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2014 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2013 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2012 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2011 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2010 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2009 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2008 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2007 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2006 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2005 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
- March
- February
- January
- ----- 2004 -----
- December
- November
- October
- September
- August
- July
- June
- May
- April
June 2013
- 10 participants
- 10 discussions
22 Jun '13
Hi all,
I downloaded a copy of the 11.15.7 (Feb2013 SP2) source and compiled and
installed it. I also have a copy of the same version of MonetDB installed
from elsewhere (I'm not sure where now--sorry).
Now, when I run the "old" version of Monet (the one I didn't just compile)
and create a DB (`monetdb create test`), release it, and start it,
everything works fine. The log looks likes this:
...
MSG merovingian[32695]: starting database 'test', up min/avg/max: 0s/0s/0s,
crash average: 0.00 0.00 0.00 (0-0=0)
MSG control[32695]: (local): started 'test'
MSG test[32705]: arguments: /usr/local/bin/mserver5 ...<snip>...
...
However, when I run with my compiled version (using `configure
--program-suffix=-vanilla`), starting a created DB does not call
mserver5-vanilla, but rather monetdbd-vanilla, which is the incorrect
command, and thus the database cannot start. (If I manually run
mserver5-vanilla, it works fine.) The log looks like this:
MSG merovingian[2616]: starting database 'test', up min/avg/max: 0s/0s/0s,
crash average: 0.00 0.00 0.00 (0-0=0)
MSG test[2625]: arguments: /usr/local/bin/monetdbd-vanilla ...<snip>...
MSG test[2625]: usage: monetdbd [ command [ command-options ] ] <dbfarm>
MSG test[2625]: where command is one of:
MSG test[2625]: create, start, stop, get, set, version or help
MSG test[2625]: use the help command to get help for a particular command
MSG test[2625]: The dbfarm to operate on must always be given to
MSG test[2625]: monetdbd explicitly.
ERR test[2625]: monetdbd: unknown command:
--dbpath=/home/breilly/db/farm-vanilla/test
MSG merovingian[2616]: database 'test' (2625) has exited with exit status 1
ERR control[2616]: (local): failed to fork mserver: database 'test' appears
to shut itself down after starting, check monetdbd's logfile for possible
hints
I tried just now to compile without the suffix, but in a different
directory (i.e. `configure --prefix=$HOME/apps`), and it worked fine, as
with the first version above. I'm not sure why the one with the suffix
fails in the way it does: it's not like it tries to find
"mserver5" (without the suffix) and fails. Instead, it successfully finds a
real command with the suffix, but it's the wrong one.
I have also tried simply symlinking to my suffixed program with
non-suffixed name (i.e. `ln -s $HOME/apps/bin/monetdbd-vanilla
$HOME/apps/bin/monetdb`, for each of monetdbd, monetdb, mclient, and
mserver5), and this also works. However, it does not run the correct
version of mserver5: it runs /usr/local/bin/mserver5, rather than my local
copy at $HOME/apps/bin/mserver5. Is there some environment variable that
I'm not setting correctly? ($HOME/apps/bin is in my PATH).
Please let me know if there is any more information that I can provide for
you. Thanks in advance for your help.
Regards,
Ben Reilly
2
1
Thanks. May I know size of your database?
--- Original Message ---
From: "Brandon Jackson" <usbrandon(a)gmail.com>
Sent: June 20, 2013 12:09 PM
To: "Communication channel for developers of the MonetDB suite." <developers-list(a)monetdb.org>
Subject: Re: Monetdb - production users
We are running MonetDB in our enterprise in a production environment. It
works well. There are nuances.
We do a lot of bulk loading, so we picked a time during the day to shut
MonetDB down and restart it.
The reason, memory fragmentation, does occur and in our experience would
cause the database to fail and lock up. It will stop accepting connections.
Our database health gradually and constantly decreases. Although I can say
we have never received wrong answers from the database or had any obvious
effects from poor database health. We periodically recreate the entire
database to get the health of the database back up to 100%. Essentially we
use MonetDB as a fast query database and not the primary database for
maintaining the integrity of our data. There is no repair utility for the
database. We're still learning how and if we should report issues like
this because it happens gradually and the log has some splat marks " ***
glibc detected *** /usr/bin/mserver5: corrupted doubly-linked list:
0x00007fa12411f4d0 *** and *** glibc detected *** /user/bin/mserver5:
double free or corruption (!prev): 0x00007fa12411f4d0 *** in it.
Like anything else, I think you learn to live with whatever tools you are
using and play into the strengths and avoid the weaknesses, reporting them
and hoping they can get patched up eventually.
Brandon
On Thu, Jun 20, 2013 at 9:29 AM, Baskar Duraikannu <
baskar.duraikannu(a)outlook.com> wrote:
> I would like to know whether any enterprise is running MonetDB outside
> research community. Please help.
>
> _______________________________________________
> developers-list mailing list
> developers-list(a)monetdb.org
> http://mail.monetdb.org/mailman/listinfo/developers-list
>
>
_______________________________________________
developers-list mailing list
developers-list(a)monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list
1
0
I would like to know whether any enterprise is running MonetDB outside research community. Please help.
2
1
Good morning.
I recently installed and tested monetdb with a sample dataset and it performed really well. Then I came across the below posthttp://stackoverflow.com/questions/7435235/is-it-worth-trying-monetdb
Above post talks about non-availability of buffer manager in MonetDB. Has this changed?
2
1
Good afternoon,
I have 2 questions :
- 1st question
I have a csv file with 300000000 million lines.
when I load it in a MonetDB table, it takes more than 16 hours, whereas in Oracle it only takes 15 minutes. Where is the problem?
I use mclient as follows :?
mclient -u user1 -d demo -ims -s " COPY 300000009 RECORDS INTO LINEORDER FROM STDIN USING DELIMITERS '|','\n'" - < D:\SSB\LINEORDER.tbl
?for information I use :
MonetDB5
Windows
Memory : 16 Go
There are two constraints : primary key and foreign key on the table prior to loading data?
2nd question :
how inner joins are made in MongoDB?
cordialy.
3
4
Re: MonetDB: DVframework_bam - Added bam folder under sql/backends/m...
by Stefan Manegold 15 Jun '13
by Stefan Manegold 15 Jun '13
15 Jun '13
Thanks Robin!
Eventually, we should of course add a proper configure check and make conditional for the samtools ... ;-)
Stefan
Robin Cijvat <commits(a)monetdb.org> wrote:
>Changeset: d87b8aafa5b7 for MonetDB
>URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d87b8aafa5b7
>Added Files:
> sql/backends/monet5/bam/85_bam.mal
> sql/backends/monet5/bam/85_bam.sql
> sql/backends/monet5/bam/Makefile.ag
> sql/backends/monet5/bam/bam.mal
> sql/backends/monet5/bam/bam_clear.sql
> sql/backends/monet5/bam/bam_schema.sql
> sql/backends/monet5/bam/bamloader.c
> sql/backends/monet5/bam/bamloader.h
>Modified Files:
> sql/backends/monet5/Makefile.ag
>Branch: DVframework_bam
>Log Message:
>
>Added bam folder under sql/backends/monet5/ containing UDF that
>provides functionality for loading BAM file contents into temporary
>BATs. To run code, samtools has to be installed and referred to in the
>Makefile of the directory.
>
>
>diffs (truncated from 1260 to 300 lines):
>
>diff --git a/sql/backends/monet5/Makefile.ag
>b/sql/backends/monet5/Makefile.ag
>--- a/sql/backends/monet5/Makefile.ag
>+++ b/sql/backends/monet5/Makefile.ag
>@@ -15,7 +15,7 @@
> # Copyright August 2008-2013 MonetDB B.V.
> # All Rights Reserved.
>
>-SUBDIRS = NOT_WIN32?vaults UDF LSST ENABLE_DATACELL?datacell
>HAVE_MSEED?miniseed
>+SUBDIRS = NOT_WIN32?vaults UDF bam LSST ENABLE_DATACELL?datacell
>HAVE_MSEED?miniseed
>
> INCLUDES = ../../include ../../common ../../storage ../../server \
> ../../../monetdb5/modules/atoms \
>diff --git a/sql/backends/monet5/bam/85_bam.mal
>b/sql/backends/monet5/bam/85_bam.mal
>new file mode 100644
>--- /dev/null
>+++ b/sql/backends/monet5/bam/85_bam.mal
>@@ -0,0 +1,2 @@
>+library bam;
>+include bam;
>\ No newline at end of file
>diff --git a/sql/backends/monet5/bam/85_bam.sql
>b/sql/backends/monet5/bam/85_bam.sql
>new file mode 100644
>--- /dev/null
>+++ b/sql/backends/monet5/bam/85_bam.sql
>@@ -0,0 +1,2 @@
>+CREATE PROCEDURE bamloader(repo string, mode int, num_threads int)
>+external name bam.bamloader;
>\ No newline at end of file
>diff --git a/sql/backends/monet5/bam/Makefile.ag
>b/sql/backends/monet5/bam/Makefile.ag
>new file mode 100644
>--- /dev/null
>+++ b/sql/backends/monet5/bam/Makefile.ag
>@@ -0,0 +1,62 @@
>+# The contents of this file are subject to the MonetDB Public License
>+# Version 1.1 (the "License"); you may not use this file except in
>+# compliance with the License. You may obtain a copy of the License at
>+# http://www.monetdb.org/Legal/MonetDBLicense
>+#
>+# Software distributed under the License is distributed on an "AS IS"
>+# basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See
>the
>+# License for the specific language governing rights and limitations
>+# under the License.
>+#
>+# The Original Code is the MonetDB Database System.
>+#
>+# The Initial Developer of the Original Code is CWI.
>+# Portions created by CWI are Copyright (C) 1997-July 2008 CWI.
>+# Copyright August 2008-2013 MonetDB B.V.
>+# All Rights Reserved.
>+
>+INCLUDES = .. \
>+ ../../../include \
>+ ../../../common \
>+ ../../../storage \
>+ ../../../server \
>+ ../../../../monetdb5/modules/atoms \
>+ ../../../../monetdb5/modules/kernel \
>+ ../../../../monetdb5/mal \
>+ ../../../../monetdb5/modules/mal \
>+ ../../../../monetdb5/optimizer \
>+ ../../../../clients/mapilib \
>+ ../../../../common/options \
>+ ../../../../common/stream \
>+ ../../../../gdk \
>+ -I$(HOME)/Documents/BAM/samtools/include
>+
>+lib__bam = {
>+ MODULE
>+ DIR = libdir/monetdb5
>+ SOURCES = bamloader.c bamloader.h
>+ LIBS = ../../../../monetdb5/tools/libmonetdb5 \
>+ ../../../../gdk/libbat \
>+ -L$(HOME)/Documents/BAM/samtools/lib -lbam
>+
>+}
>+
>+headers_mal = {
>+ HEADERS = mal
>+ DIR = libdir/monetdb5
>+ SOURCES = bam.mal
>+}
>+
>+headers_sql = {
>+ HEADERS = sql
>+ DIR = libdir/monetdb5/createdb
>+ SOURCES = 85_bam.sql
>+}
>+
>+headers_autoload = {
>+ HEADERS = mal
>+ DIR = libdir/monetdb5/autoload
>+ SOURCES = 85_bam.mal
>+}
>+
>+EXTRA_DIST_DIR = Tests
>diff --git a/sql/backends/monet5/bam/bam.mal
>b/sql/backends/monet5/bam/bam.mal
>new file mode 100644
>--- /dev/null
>+++ b/sql/backends/monet5/bam/bam.mal
>@@ -0,0 +1,5 @@
>+module bam;
>+
>+pattern bamloader(entry:str, mode:int, num_threads:int):void
>+address bamloader
>+comment "Read the files in the BAM repository, fill and return a
>temp_container accordingly.";
>\ No newline at end of file
>diff --git a/sql/backends/monet5/bam/bam_clear.sql
>b/sql/backends/monet5/bam/bam_clear.sql
>new file mode 100644
>--- /dev/null
>+++ b/sql/backends/monet5/bam/bam_clear.sql
>@@ -0,0 +1,7 @@
>+DROP TABLE bam.alignments_extra;
>+DROP TABLE bam.alignments;
>+DROP TABLE bam.pg;
>+DROP TABLE bam.rg;
>+DROP TABLE bam.sq;
>+DROP TABLE bam.files;
>+DROP SCHEMA bam;
>diff --git a/sql/backends/monet5/bam/bam_schema.sql
>b/sql/backends/monet5/bam/bam_schema.sql
>new file mode 100644
>--- /dev/null
>+++ b/sql/backends/monet5/bam/bam_schema.sql
>@@ -0,0 +1,91 @@
>+CREATE SCHEMA bam;
>+
>+CREATE TABLE "bam"."files" (
>+ "file_location" STRING NOT NULL UNIQUE,
>+ "format_version" REAL,
>+ "sorting_order" VARCHAR(10),
>+ "comments" STRING,
>+ CONSTRAINT "files_pkey_file_location" PRIMARY KEY (file_location)
>+);
>+
>+CREATE TABLE "bam"."sq" (
>+ "sn" STRING NOT NULL,
>+ "file_location" STRING NOT NULL,
>+ "ln" INT NOT NULL,
>+ "as" INT,
>+ "m5" STRING,
>+ "sp" STRING,
>+ "ur" STRING,
>+ CONSTRAINT "sq_pkey_sn_file_location" PRIMARY KEY (sn,
>file_location),
>+ CONSTRAINT "sq_fkey_file_location" FOREIGN KEY (file_location)
>REFERENCES bam.files (file_location)
>+);
>+
>+CREATE TABLE "bam"."rg" (
>+ "id" STRING NOT NULL UNIQUE,
>+ "file_location" STRING NOT NULL,
>+ "cn" STRING,
>+ "ds" STRING,
>+ "dt" TIMESTAMP,
>+ "fo" STRING,
>+ "ks" STRING,
>+ "lb" STRING,
>+ "pg" STRING,
>+ "pi" INT,
>+ "PL" STRING,
>+ "PU" STRING,
>+ "SM" STRING,
>+ CONSTRAINT "rg_pkey_id_file_location" PRIMARY KEY (id,
>file_location),
>+ CONSTRAINT "rg_fkey_file_location" FOREIGN KEY (file_location)
>REFERENCES bam.files (file_location)
>+);
>+
>+CREATE TABLE "bam"."pg" (
>+ "id" STRING NOT NULL UNIQUE,
>+ "file_location" STRING NOT NULL,
>+ "pn" STRING,
>+ "cl" STRING,
>+ "pp" STRING,
>+ "vn" REAL,
>+ CONSTRAINT "pg_pkey_id_file_location" PRIMARY KEY (id,
>file_location),
>+ CONSTRAINT "pg_fkey_file_location" FOREIGN KEY (file_location)
>REFERENCES bam.files (file_location)
>+);
>+
>+CREATE TABLE "bam"."alignments" (
>+ "file_location" STRING NOT NULL,
>+ "virtual_offset" INT NOT NULL,
>+ "qname" STRING NOT NULL,
>+ "flag_temp_mult_segm" BOOLEAN NOT NULL,
>+ "flag_each_segm_prop_alig" BOOLEAN NOT NULL,
>+ "flag_segm_unma" BOOLEAN NOT NULL,
>+ "flag_next_segm_unma" BOOLEAN NOT NULL,
>+ "flag_seq_reve_comp" BOOLEAN NOT NULL,
>+ "flag_seq_next_segm_reve" BOOLEAN NOT NULL,
>+ "flag_first_segm" BOOLEAN NOT NULL,
>+ "flag_last_segm" BOOLEAN NOT NULL,
>+ "flag_seco_alig" BOOLEAN NOT NULL,
>+ "flag_not_pass_qual_cont" BOOLEAN NOT NULL,
>+ "flag_pcr_opti_dupl" BOOLEAN NOT NULL,
>+ "rname" STRING NOT NULL,
>+ "pos" INT NOT NULL,
>+ "mapq" INT NOT NULL,
>+ "cigar" STRING NOT NULL,
>+ "rnext" STRING NOT NULL,
>+ "pnext" INT NOT NULL,
>+ "tlen" INT NOT NULL,
>+ "seq" STRING NOT NULL,
>+ "qual" STRING NOT NULL,
>+ CONSTRAINT "alignments_pkey_file_location_virtual_offset" PRIMARY
>KEY (file_location, virtual_offset),
>+ CONSTRAINT "alignments_fkey_file_location" FOREIGN KEY
>(file_location) REFERENCES bam.files (file_location)
>+);
>+
>+CREATE TABLE "bam"."alignments_extra" (
>+ "tag" CHAR(2) NOT NULL,
>+ "file_location" STRING NOT NULL,
>+ "virtual_offset" INT NOT NULL,
>+ "type" CHAR(1) NOT NULL,
>+ "value" STRING,
>+ CONSTRAINT
>"alignments_extra_pkey_tag_file_location_virtual_offset" PRIMARY KEY
>(tag, file_location, virtual_offset),
>+ CONSTRAINT "alignments_extra_fkey_file_location_virtual_offset"
>FOREIGN KEY (file_location, virtual_offset) REFERENCES bam.alignments
>(file_location, virtual_offset)
>+);
>+
>+
>+
>diff --git a/sql/backends/monet5/bam/bamloader.c
>b/sql/backends/monet5/bam/bamloader.c
>new file mode 100644
>--- /dev/null
>+++ b/sql/backends/monet5/bam/bamloader.c
>@@ -0,0 +1,1013 @@
>+#include <stdio.h>
>+#include "monetdb_config.h"
>+#include "bamloader.h"
>+
>+
>+/*
>+ * NOTE: Copied directly from miniseed/registrar.c
>+ * TODO: Make both miniseed/registrar.c and this file include some
>generic library for these kind of structures
>+ * keeps BAT and other properties of columns of a table.
>+ */
>+typedef struct {
>+ bat *column_bats; /* keeps bats of the columns: lower array */
>+ str *column_names; /* names of columns that are kept in the higher
>array */
>+ str *column_types_strs; /* type strings of columns */
>+} temp_subcontainer;
>+
>+/*
>+ * NOTE: Copied directly from miniseed/registrar.c
>+ * TODO: Make both miniseed/registrar.c and this file include some
>generic library for these kind of structures
>+ * keeps (some) tables of a schema.
>+ */
>+typedef struct {
>+ str schema_name; /* schema or vault name */
>+ temp_subcontainer *tables_columns; /* keeps tables: higher array */
>+ str *table_names; /* names of tables that are kept in the higher
>array */
>+ int *num_columns; /* number of columns in each table in the higher
>array */
>+ int num_tables;
>+} temp_container;
>+
>+/*
>+* File format specific structures
>+*/
>+typedef struct {
>+ str tag;
>+ str value;
>+} bam_header_option;
>+
>+typedef struct {
>+ str header_tag;
>+ bam_header_option *options;
>+ int num_options;
>+} bam_header_line;
>+
>+
>+/* Global vars */
>+FILE *logfile = NULL; //keep logfile file opened in this global var
>while the bam code runs,
>+ //since opening and closing every time
>something has to be written turned out to be very slow
>+
>+
>+
>+/* File format specific functions */
>+str init_temp_container(temp_container *ret_tc);
>+str loadfile(str filepath, temp_container *ret_tc); //load file and
>add contents to tc
>+str process_bam_header(str filepath, str header, temp_container
>*ret_tc);
>+int append_to_bat_cond(temp_container *ret_tc, bam_header_option *opt,
>str cmp, int table, int col, int *appendErr, int *flag);
>+str read_bam_header_line(str *header, bam_header_line *ret_hl, int
>*eof);
>+void free_bam_header_line(bam_header_line *hl);
>+str process_bam_alignment(str filepath, int virtual_offset,
>bam_header_t *header, bam1_t *alignment, temp_container *ret_tc);
>+int parse_alignment_str(str *sam_alig, str *dest);
>+int parse_alignment_long(str *sam_alig, long int *dest);
>+
>+/* Generic functions */
>+str init_temp_subcontainer(temp_subcontainer *ret_tsc,
>+ str *col_names, str *col_types_strs, int *col_types, int
>num_cols);
>+str append_to_bat(bat cb, ptr val);
>+int concatenate_strs(str* words_to_concat, int num_words_to_concat,
>str* ret_concatenated);
>+str prepare_insertion(Client cntxt, temp_container* tc);
>+str insert_into_vault(Client cntxt, temp_container* tc);
>+str register_table(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr
>pci);
>+int read_string_until_delim(str *src, str *ret, char *delims, int
>num_delims);
>+int parse_long(str *src, long int *i);
>+int get_kth_bit(unsigned int i, int k);
>+void append_to_log(str mssg);
>+void free_temp_container(temp_container* tc);
>+
>+/* External functions */
>+str SQLstatementIntern(Client c, str *expr, str nme, int execute, bit
>output);
>+
>+
>+
>+/* File format specific functions */
>_______________________________________________
>checkin-list mailing list
>checkin-list(a)monetdb.org
>http://mail.monetdb.org/mailman/listinfo/checkin-list
--
| Stefan.Manegold(a)CWI.nl | Database Architectures (DA) |
| www.CWI.nl/~manegold | Science Park 123 (L321) |
| +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
1
0
Hai Stefan,
What problems is imprints causing in the sciql branch?
Jennie
On Jun 14, 2013, at 10:29, Stefan Manegold wrote:
> Changeset: 3841d3bd5fd7 for MonetDB
> URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=3841d3bd5fd7
> Modified Files:
> gdk/gdk_select.c
> Branch: sciql
> Log Message:
>
> for now, disbale imprints in sciql branch,
> until we find the time to debug suspicious errors
>
>
> diffs (12 lines):
>
> diff --git a/gdk/gdk_select.c b/gdk/gdk_select.c
> --- a/gdk/gdk_select.c
> +++ b/gdk/gdk_select.c
> @@ -1344,7 +1344,7 @@ BATsubselect(BAT *b, BAT *s, const void
> * ii) it is not an equi-select, and
> * iii) is not var-sized.
> */
> - use_imprints = 1;
> + use_imprints = 0; /* temporarily disabled in sciql branch */
> }
> bn = BAT_scanselect(b, s, bn, tl, th, li, hi, equi, anti,
> lval, hval, maximum, use_imprints);
> _______________________________________________
> checkin-list mailing list
> checkin-list(a)monetdb.org
> http://mail.monetdb.org/mailman/listinfo/checkin-list
2
1
System infomation!//Re: After plenty of querys the database use 40 times more disk space than the original data, How can I avoid this?
by liyuesen 00108910 06 Jun '13
by liyuesen 00108910 06 Jun '13
06 Jun '13
Hi Stefan:
Thanks for your help! Here is the system infomation:
[root@host-28-for-monet monetdb]# mserver5 --version
MonetDB 5 server v11.15.3 "Feb2013-SP1" (64-bit, 64-bit oids)
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 94.4GiB available memory, 24 available cpu cores
Libraries:
libpcre: 7.8 2008-09-05 (compiled with 7.8)
openssl: OpenSSL 1.0.0 29 Mar 2010 (compiled with OpenSSL 1.0.0-fips 29 Mar 2010)
libxml2: 2.7.6 (compiled with 2.7.6)
Compiled by: root@host-28-for-monet (x86_64-unknown-linux-gnu)
Compilation: gcc -g//When testing , debug was disabled. --by Jason
Linking : /usr/bin/ld -m elf_x86_64
OS:Linux host-28-for-monet 2.6.32-220.el6.x86_64 #1 SMP Wed Nov 9 08:03:13 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
CPU: 24 cores INTEL cpu
Memory info, in Million Bytes:
total used free shared buffers cached
Mem: 99030412 3717976 95312436 0 658496 250616
-/+ buffers/cache: 2808864 96221548
Swap: 101269496 23768 101245728
No extra schema was used, we have seven tables in schema SYS, 155 colums of each table. Every miniute, 30 thousands rows were imported into each table, and then 40 threads execute the followwing SQL. The case run more than 90 times, that means the test last longer than one and an half hours!
SQL query:select count(*), bkpi.GGSN_ID, bkpi.SGSN_ID, CASE WHEN CAST(SUM(bkpi.DNS_DELAY_NUM) AS BIGINT) = 0 THEN -1 ELSE FLOOR(CAST(SUM(bkpi.DNS_DELAY_NUM) AS BIGINT))/CAST(SUM(bkpi.DNS_DELAY_NUM) AS BIGINT) * 100 END as KQIDELAY, CASE WHEN CAST(SUM(bkpi.COUNTS) AS BIGINT)=0 THEN -1 ELSE FLOOR(CAST(SUM(bkpi.DNS_DELAY_MSEL) AS BIGINT))/CAST(SUM(bkpi.COUNTS) AS BIGINT) END as DNS_DELAY, CASE WHEN CAST(SUM(bkpi.TCP_DELAY_NUM)
AS BIGINT) = 0 THEN -1 ELSE FLOOR(CAST(SUM(bkpi.TCP_DELAY_MSEL) AS BIGINT))/CAST(SUM(bkpi.TCP_DELAY_NUM) AS BIGINT) * 100 END as TCPDELAY , CASE WHEN CAST(SUM(bkpi.GET_DELAY_NUM) AS BIGINT)=0 THEN -1 ELSE FLOOR(CAST(SUM(bkpi.GET_DELAY_MSEL) AS BIGINT))/CAST(SUM(bkpi.GET_DELAY_NUM) AS BIGINT) END as TCPGETDELAY, CASE WHEN CAST(SUM(bkpi.TCP_DELAY_NUM) AS BIGINT) = 0 THEN -1 ELSE FLOOR(CAST(SUM(bkpi.TCP_DELAY_MSEL) AS BIGINT))/CAST(S
UM(bkpi.TCP_DELAY_NUM) AS BIGINT) * 100 END AS TCP_DELAY_AVGMSEL, CASE WHEN (CAST(SUM(bkpi.POST_DELAY_NUM) AS BIGINT)*1024)=0 THEN -1 ELSE FLOOR(CAST(SUM(bkpi.POST_DELAY_MSEL) AS BIGINT)*1000*8)/(CAST(SUM(bkpi.POST_DELAY_NUM) AS BIGINT)*1024) END as TCPPOSTDELAY, CASE WHEN CAST(SUM(bkpi.GET_DELAY_NUM) AS BIGINT)=0 THEN -1 ELSE FLOOR(CAST(SUM(bkpi.GET_DELAY_MSEL) AS BIGINT))/CAST(SUM(bkpi.GET_DELAY_NUM) AS BIGINT) END AS GET_DELAY_MSE
L_AVG from (select GENTIME, APN, GGSN_ID,SGSN_ID, COUNTS, KQI_DELAY_MSEL,DNS_DELAY_MSEL, DNS_DELAY_NUM, TCP_DELAY_MSEL,TCP_DELAY_NUM, GET_DELAY_MSEL,GET_DELAY_NUM,POST_DELAY_MSEL,POST_DELAY_NUM from bkpi_web_dspdelay_cisai_15min_0 where sgsn_id = 0 and GENTIME >= '2013-06-06 07:51:41' and GENTIME <= '2013-06-06 07:52:27' union all select GENTIME, APN, GGSN_ID,SGSN_ID, COUNTS, KQI_DELAY_MSEL,DNS_DELAY_MSEL, DNS_DELAY_NUM, TCP_
DELAY_MSEL,TCP_DELAY_NUM, GET_DELAY_MSEL,GET_DELAY_NUM,POST_DELAY_MSEL,POST_DELAY_NUM from bkpi_web_dspdelay_cisai_15min_1 where ggsn_id = 0 and GENTIME >= '2013-06-06 07:51:41' and GENTIME <= '2013-06-06 07:52:27' union all select GENTIME, APN, GGSN_ID,SGSN_ID, COUNTS, KQI_DELAY_MSEL,DNS_DELAY_MSEL, DNS_DELAY_NUM, TCP_DELAY_MSEL,TCP_DELAY_NUM, GET_DELAY_MSEL,GET_DELAY_NUM,POST_DELAY_MSEL,POST_DELAY_NUM from bkpi_web_dspdelay_
cisai_15min_0 where sgsn_id = 0 and GENTIME >= '2013-06-06 07:51:41' and GENTIME <= '2013-06-06 07:52:27' union all select GENTIME, APN, GGSN_ID,SGSN_ID, COUNTS, KQI_DELAY_MSEL,DNS_DELAY_MSEL, DNS_DELAY_NUM, TCP_DELAY_MSEL,TCP_DELAY_NUM, GET_DELAY_MSEL,GET_DELAY_NUM,POST_DELAY_MSEL,POST_DELAY_NUM from bkpi_web_dspdelay_cisai_15min_1 where ggsn_id = 0 and GENTIME >= '2013-06-06 07:51:41' and GENTIME <= '2013-06-06 07:52:27' ) b
kpi group by bkpi.GGSN_ID, bkpi.SGSN_ID;
Explain result of the query:
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mal |
+=============================================================================================================================================================================================================+
| function user.s1_4{autoCommit=true}(A0:lng,A1:flt,A2:flt,A3:lng,A4:flt,A5:lng,A6:flt,A7:flt,A8:lng,A9:flt,A10:lng,A11:flt,A12:flt,A13:lng,A14:lng,A15:flt,A16:lng,A17:lng,A18:lng,A19:lng,A20:flt,A21:lng,A |
: 22:str,A23:str,A24:lng,A25:str,A26:str,A27:lng,A28:str,A29:str,A30:lng,A31:str,A32:str):void; :
| X_36:bat[:oid,:wrd] := bat.new(nil:oid,nil:wrd); |
| X_39:bat[:oid,:wrd] := bat.new(nil:oid,nil:wrd); |
| X_40:bat[:oid,:wrd] := bat.new(nil:oid,nil:wrd); |
| X_35 := sql.mvc(); |
| X_44 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","gentime",0); |
| X_55 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","sgsn_id",0); |
| X_41:bat[:oid,:oid] := sql.tid(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0"); |
| X_962 := algebra.subselect(X_55,X_41,A21,A21,true,true,false); |
| (X_57,r1_63) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","sgsn_id",2); |
| X_963 := algebra.subselect(r1_63,A21,A21,true,true,false); |
| X_59 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","sgsn_id",1); |
| X_964 := algebra.subselect(X_59,X_41,A21,A21,true,true,false); |
| X_60 := sql.subdelta(X_962,X_57,X_963,X_964); |
| X_52 := calc.timestamp(A22,7); |
| X_54 := calc.timestamp(A23,7); |
| X_965 := algebra.subselect(X_44,X_60,X_52,X_54,true,true,false); |
| (X_47,r1_47) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","gentime",2); |
| X_966 := algebra.subselect(r1_47,X_52,X_54,true,true,false); |
| X_50 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","gentime",1); |
| X_967 := algebra.subselect(X_50,X_60,X_52,X_54,true,true,false); |
| X_63 := sql.subdelta(X_965,X_47,X_966,X_967); |
| X_64 := sql.projectdelta(X_63,X_55,X_57,r1_63,X_59); |
| X_65 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","ggsn_id",0); |
| (X_70,r1_84) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","ggsn_id",2); |
| X_73 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","ggsn_id",1); |
| X_75 := sql.projectdelta(X_63,X_65,X_70,r1_84,X_73); |
| (X_76,r1_96,r2_96) := group.subgroup(X_75); |
| (X_79,r1_99,r2_99) := group.subgroupdone(X_64,X_76); |
| X_82:bat[:oid,:wrd] := aggr.subcount(X_79,X_79,r1_99,false); |
| X_128 := algebra.leftfetchjoin(r1_99,X_64); |
| X_134 := algebra.leftfetchjoin(r1_99,X_75); |
| X_283 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","dns_delay_num",0); |
| (X_288,r1_503) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","dns_delay_num",2); |
| X_291 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","dns_delay_num",1); |
| X_293 := sql.projectdelta(X_63,X_283,X_288,r1_503,X_291); |
| X_294:bat[:oid,:lng] := aggr.subsum(X_293,X_79,r1_99,true,true); |
| X_363 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","counts",0); |
| (X_368,r1_749) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","counts",2); |
| X_371 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","counts",1); |
| X_373 := sql.projectdelta(X_63,X_363,X_368,r1_749,X_371); |
| X_374:bat[:oid,:lng] := aggr.subsum(X_373,X_79,r1_99,true,true); |
| X_406 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","dns_delay_msel",0); |
| (X_411,r1_875) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","dns_delay_msel",2); |
| X_414 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","dns_delay_msel",1); |
| X_416 := sql.projectdelta(X_63,X_406,X_411,r1_875,X_414); |
| X_417:bat[:oid,:lng] := aggr.subsum(X_416,X_79,r1_99,true,true); |
| X_467 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","tcp_delay_num",0); |
| (X_472,r1_1061) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","tcp_delay_num",2); |
| X_475 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","tcp_delay_num",1); |
| X_477 := sql.projectdelta(X_63,X_467,X_472,r1_1061,X_475); |
| X_478:bat[:oid,:lng] := aggr.subsum(X_477,X_79,r1_99,true,true); |
| X_510 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","tcp_delay_msel",0); |
| (X_515,r1_1187) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","tcp_delay_msel",2); |
| X_518 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","tcp_delay_msel",1); |
| X_520 := sql.projectdelta(X_63,X_510,X_515,r1_1187,X_518); |
| X_521:bat[:oid,:lng] := aggr.subsum(X_520,X_79,r1_99,true,true); |
| X_572 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","get_delay_num",0); |
| (X_577,r1_1376) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","get_delay_num",2); |
| X_580 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","get_delay_num",1); |
| X_582 := sql.projectdelta(X_63,X_572,X_577,r1_1376,X_580); |
| X_583:bat[:oid,:lng] := aggr.subsum(X_582,X_79,r1_99,true,true); |
| X_615 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","get_delay_msel",0); |
| (X_620,r1_1502) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","get_delay_msel",2); |
| X_623 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","get_delay_msel",1); |
| X_625 := sql.projectdelta(X_63,X_615,X_620,r1_1502,X_623); |
| X_626:bat[:oid,:lng] := aggr.subsum(X_625,X_79,r1_99,true,true); |
| X_731 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","post_delay_num",0); |
| (X_736,r1_1865) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","post_delay_num",2); |
| X_739 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","post_delay_num",1); |
| X_741 := sql.projectdelta(X_63,X_731,X_736,r1_1865,X_739); |
| X_742:bat[:oid,:lng] := aggr.subsum(X_741,X_79,r1_99,true,true); |
| X_775 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","post_delay_msel",0); |
| (X_780,r1_1996) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","post_delay_msel",2); |
| X_783 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","post_delay_msel",1); |
| X_785 := sql.projectdelta(X_63,X_775,X_780,r1_1996,X_783); |
| X_786:bat[:oid,:lng] := aggr.subsum(X_785,X_79,r1_99,true,true); |
| X_83 := bat.append(X_40,X_82,true); |
| X_87 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","gentime",0); |
| X_96 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","ggsn_id",0); |
| X_85:bat[:oid,:oid] := sql.tid(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1"); |
| X_968 := algebra.subselect(X_96,X_85,A24,A24,true,true,false); |
| (X_99,r1_128) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","ggsn_id",2); |
| X_969 := algebra.subselect(r1_128,A24,A24,true,true,false); |
| X_102 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","ggsn_id",1); |
| X_970 := algebra.subselect(X_102,X_85,A24,A24,true,true,false); |
| X_104 := sql.subdelta(X_968,X_99,X_969,X_970); |
| X_93 := calc.timestamp(A25,7); |
| X_95 := calc.timestamp(A26,7); |
| X_971 := algebra.subselect(X_87,X_104,X_93,X_95,true,true,false); |
| (X_89,r1_112) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","gentime",2); |
| X_972 := algebra.subselect(r1_112,X_93,X_95,true,true,false); |
| X_92 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","gentime",1); |
| X_973 := algebra.subselect(X_92,X_104,X_93,X_95,true,true,false); |
| X_106 := sql.subdelta(X_971,X_89,X_972,X_973); |
| X_108 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","sgsn_id",0); |
| (X_111,r1_145) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","sgsn_id",2); |
| X_114 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","sgsn_id",1); |
| X_115 := sql.projectdelta(X_106,X_108,X_111,r1_145,X_114); |
| X_116 := sql.projectdelta(X_106,X_96,X_99,r1_128,X_102); |
| (X_117,r1_161,r2_161) := group.subgroup(X_116); |
| (X_120,r1_164,r2_164) := group.subgroupdone(X_115,X_117); |
| X_123:bat[:oid,:wrd] := aggr.subcount(X_120,X_120,r1_164,false); |
| X_131 := algebra.leftfetchjoin(r1_164,X_115); |
| X_136 := algebra.leftfetchjoin(r1_164,X_116); |
| X_297 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","dns_delay_num",0); |
| (X_299,r1_524) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","dns_delay_num",2); |
| X_301 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","dns_delay_num",1); |
| X_302 := sql.projectdelta(X_106,X_297,X_299,r1_524,X_301); |
| X_303:bat[:oid,:lng] := aggr.subsum(X_302,X_120,r1_164,true,true); |
| X_377 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","counts",0); |
| (X_379,r1_770) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","counts",2); |
| X_381 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","counts",1); |
| X_382 := sql.projectdelta(X_106,X_377,X_379,r1_770,X_381); |
| X_383:bat[:oid,:lng] := aggr.subsum(X_382,X_120,r1_164,true,true); |
| X_420 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","dns_delay_msel",0); |
| (X_422,r1_896) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","dns_delay_msel",2); |
| X_424 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","dns_delay_msel",1); |
| X_425 := sql.projectdelta(X_106,X_420,X_422,r1_896,X_424); |
| X_426:bat[:oid,:lng] := aggr.subsum(X_425,X_120,r1_164,true,true); |
| X_481 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","tcp_delay_num",0); |
| (X_483,r1_1082) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","tcp_delay_num",2); |
| X_485 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","tcp_delay_num",1); |
| X_486 := sql.projectdelta(X_106,X_481,X_483,r1_1082,X_485); |
| X_487:bat[:oid,:lng] := aggr.subsum(X_486,X_120,r1_164,true,true); |
| X_524 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","tcp_delay_msel",0); |
| (X_526,r1_1208) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","tcp_delay_msel",2); |
| X_528 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","tcp_delay_msel",1); |
| X_529 := sql.projectdelta(X_106,X_524,X_526,r1_1208,X_528); |
| X_530:bat[:oid,:lng] := aggr.subsum(X_529,X_120,r1_164,true,true); |
| X_586 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","get_delay_num",0); |
| (X_588,r1_1397) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","get_delay_num",2); |
| X_590 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","get_delay_num",1); |
| X_591 := sql.projectdelta(X_106,X_586,X_588,r1_1397,X_590); |
| X_592:bat[:oid,:lng] := aggr.subsum(X_591,X_120,r1_164,true,true); |
| X_629 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","get_delay_msel",0); |
| (X_631,r1_1523) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","get_delay_msel",2); |
| X_633 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","get_delay_msel",1); |
| X_634 := sql.projectdelta(X_106,X_629,X_631,r1_1523,X_633); |
| X_635:bat[:oid,:lng] := aggr.subsum(X_634,X_120,r1_164,true,true); |
| X_745 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","post_delay_num",0); |
| (X_747,r1_1886) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","post_delay_num",2); |
| X_749 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","post_delay_num",1); |
| X_750 := sql.projectdelta(X_106,X_745,X_747,r1_1886,X_749); |
| X_751:bat[:oid,:lng] := aggr.subsum(X_750,X_120,r1_164,true,true); |
| X_789 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","post_delay_msel",0); |
| (X_791,r1_2017) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","post_delay_msel",2); |
| X_793 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","post_delay_msel",1); |
| X_794 := sql.projectdelta(X_106,X_789,X_791,r1_2017,X_793); |
| X_795:bat[:oid,:lng] := aggr.subsum(X_794,X_120,r1_164,true,true); |
| X_124 := bat.append(X_83,X_123,true); |
| X_125:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_129 := bat.append(X_125,X_128,true); |
| X_132 := bat.append(X_129,X_131,true); |
| X_133:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_135 := bat.append(X_133,X_134,true); |
| X_137 := bat.append(X_135,X_136,true); |
| (X_138,r1_216,r2_216) := group.subgroup(X_137); |
| (X_141,r1_219,r2_219) := group.subgroupdone(X_132,X_138); |
| X_144:bat[:oid,:wrd] := aggr.subsum(X_124,X_141,r1_219,true,true); |
| X_194 := algebra.leftfetchjoin(r1_219,X_132); |
| X_199 := algebra.leftfetchjoin(r1_219,X_137); |
| X_282:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_296 := bat.append(X_282,X_294,true); |
| X_304 := bat.append(X_296,X_303,true); |
| X_305:bat[:oid,:lng] := aggr.subsum(X_304,X_141,r1_219,true,true); |
| X_325:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_326 := bat.append(X_325,X_294,true); |
| X_327 := bat.append(X_326,X_303,true); |
| X_328 := algebra.leftfetchjoin(r1_219,X_327); |
| X_342:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_343 := bat.append(X_342,X_294,true); |
| X_345 := bat.append(X_343,X_303,true); |
| X_346 := algebra.leftfetchjoin(r1_219,X_345); |
| X_362:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_376 := bat.append(X_362,X_374,true); |
| X_384 := bat.append(X_376,X_383,true); |
| X_385:bat[:oid,:lng] := aggr.subsum(X_384,X_141,r1_219,true,true); |
| X_405:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_419 := bat.append(X_405,X_417,true); |
| X_427 := bat.append(X_419,X_426,true); |
| X_428:bat[:oid,:lng] := aggr.subsum(X_427,X_141,r1_219,true,true); |
| X_447:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_448 := bat.append(X_447,X_374,true); |
| X_449 := bat.append(X_448,X_383,true); |
| X_451 := algebra.leftfetchjoin(r1_219,X_449); |
| X_466:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_480 := bat.append(X_466,X_478,true); |
| X_488 := bat.append(X_480,X_487,true); |
| X_489:bat[:oid,:lng] := aggr.subsum(X_488,X_141,r1_219,true,true); |
| X_509:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_523 := bat.append(X_509,X_521,true); |
| X_531 := bat.append(X_523,X_530,true); |
| X_532:bat[:oid,:lng] := aggr.subsum(X_531,X_141,r1_219,true,true); |
| X_551:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_552 := bat.append(X_551,X_478,true); |
| X_553 := bat.append(X_552,X_487,true); |
| X_555 := algebra.leftfetchjoin(r1_219,X_553); |
| X_571:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_585 := bat.append(X_571,X_583,true); |
| X_593 := bat.append(X_585,X_592,true); |
| X_594:bat[:oid,:lng] := aggr.subsum(X_593,X_141,r1_219,true,true); |
| X_614:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_628 := bat.append(X_614,X_626,true); |
| X_636 := bat.append(X_628,X_635,true); |
| X_637:bat[:oid,:lng] := aggr.subsum(X_636,X_141,r1_219,true,true); |
| X_656:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_657 := bat.append(X_656,X_583,true); |
| X_658 := bat.append(X_657,X_592,true); |
| X_660 := algebra.leftfetchjoin(r1_219,X_658); |
| X_675:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_676 := bat.append(X_675,X_478,true); |
| X_678 := bat.append(X_676,X_487,true); |
| X_679 := algebra.leftfetchjoin(r1_219,X_678); |
| X_693:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_694 := bat.append(X_693,X_521,true); |
| X_696 := bat.append(X_694,X_530,true); |
| X_697 := algebra.leftfetchjoin(r1_219,X_696); |
| X_710:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_711 := bat.append(X_710,X_478,true); |
| X_713 := bat.append(X_711,X_487,true); |
| X_714 := algebra.leftfetchjoin(r1_219,X_713); |
| X_730:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_744 := bat.append(X_730,X_742,true); |
| X_752 := bat.append(X_744,X_751,true); |
| X_753:bat[:oid,:lng] := aggr.subsum(X_752,X_141,r1_219,true,true); |
| X_774:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_788 := bat.append(X_774,X_786,true); |
| X_796 := bat.append(X_788,X_795,true); |
| X_797:bat[:oid,:lng] := aggr.subsum(X_796,X_141,r1_219,true,true); |
| X_818:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_819 := bat.append(X_818,X_742,true); |
| X_821 := bat.append(X_819,X_751,true); |
| X_822 := algebra.leftfetchjoin(r1_219,X_821); |
| X_838:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_839 := bat.append(X_838,X_583,true); |
| X_841 := bat.append(X_839,X_592,true); |
| X_842 := algebra.leftfetchjoin(r1_219,X_841); |
| X_856:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_857 := bat.append(X_856,X_626,true); |
| X_859 := bat.append(X_857,X_635,true); |
| X_860 := algebra.leftfetchjoin(r1_219,X_859); |
| X_873:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_874 := bat.append(X_873,X_583,true); |
| X_876 := bat.append(X_874,X_592,true); |
| X_877 := algebra.leftfetchjoin(r1_219,X_876); |
| X_146 := bat.append(X_39,X_144,true); |
| X_150 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","gentime",0); |
| X_161 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","sgsn_id",0); |
| X_147:bat[:oid,:oid] := sql.tid(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0"); |
| X_974 := algebra.subselect(X_161,X_147,A27,A27,true,true,false); |
| (X_163,r1_251) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","sgsn_id",2); |
| X_975 := algebra.subselect(r1_251,A27,A27,true,true,false); |
| X_165 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","sgsn_id",1); |
| X_976 := algebra.subselect(X_165,X_147,A27,A27,true,true,false); |
| X_166 := sql.subdelta(X_974,X_163,X_975,X_976); |
| X_158 := calc.timestamp(A28,7); |
| X_160 := calc.timestamp(A29,7); |
| X_977 := algebra.subselect(X_150,X_166,X_158,X_160,true,true,false); |
| (X_153,r1_235) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","gentime",2); |
| X_978 := algebra.subselect(r1_235,X_158,X_160,true,true,false); |
| X_156 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","gentime",1); |
| X_979 := algebra.subselect(X_156,X_166,X_158,X_160,true,true,false); |
| X_169 := sql.subdelta(X_977,X_153,X_978,X_979); |
| X_170 := sql.projectdelta(X_169,X_161,X_163,r1_251,X_165); |
| X_171 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","ggsn_id",0); |
| (X_176,r1_272) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","ggsn_id",2); |
| X_179 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_0","ggsn_id",1); |
| X_181 := sql.projectdelta(X_169,X_171,X_176,r1_272,X_179); |
| (X_182,r1_284,r2_284) := group.subgroup(X_181); |
| (X_185,r1_287,r2_287) := group.subgroupdone(X_170,X_182); |
| X_188:bat[:oid,:wrd] := aggr.subcount(X_185,X_185,r1_287,false); |
| X_196 := algebra.leftfetchjoin(r1_287,X_170); |
| X_202 := algebra.leftfetchjoin(r1_287,X_181); |
| X_307 := sql.projectdelta(X_169,X_283,X_288,r1_503,X_291); |
| X_308:bat[:oid,:lng] := aggr.subsum(X_307,X_185,r1_287,true,true); |
| X_387 := sql.projectdelta(X_169,X_363,X_368,r1_749,X_371); |
| X_388:bat[:oid,:lng] := aggr.subsum(X_387,X_185,r1_287,true,true); |
| X_430 := sql.projectdelta(X_169,X_406,X_411,r1_875,X_414); |
| X_431:bat[:oid,:lng] := aggr.subsum(X_430,X_185,r1_287,true,true); |
| X_491 := sql.projectdelta(X_169,X_467,X_472,r1_1061,X_475); |
| X_492:bat[:oid,:lng] := aggr.subsum(X_491,X_185,r1_287,true,true); |
| X_534 := sql.projectdelta(X_169,X_510,X_515,r1_1187,X_518); |
| X_535:bat[:oid,:lng] := aggr.subsum(X_534,X_185,r1_287,true,true); |
| X_596 := sql.projectdelta(X_169,X_572,X_577,r1_1376,X_580); |
| X_597:bat[:oid,:lng] := aggr.subsum(X_596,X_185,r1_287,true,true); |
| X_639 := sql.projectdelta(X_169,X_615,X_620,r1_1502,X_623); |
| X_640:bat[:oid,:lng] := aggr.subsum(X_639,X_185,r1_287,true,true); |
| X_755 := sql.projectdelta(X_169,X_731,X_736,r1_1865,X_739); |
| X_756:bat[:oid,:lng] := aggr.subsum(X_755,X_185,r1_287,true,true); |
| X_799 := sql.projectdelta(X_169,X_775,X_780,r1_1996,X_783); |
| X_800:bat[:oid,:lng] := aggr.subsum(X_799,X_185,r1_287,true,true); |
| X_189 := bat.append(X_146,X_188,true); |
| X_191:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_195 := bat.append(X_191,X_194,true); |
| X_197 := bat.append(X_195,X_196,true); |
| X_198:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_200 := bat.append(X_198,X_199,true); |
| X_203 := bat.append(X_200,X_202,true); |
| (X_204,r1_341,r2_341) := group.subgroup(X_203); |
| (X_207,r1_344,r2_344) := group.subgroupdone(X_197,X_204); |
| X_210:bat[:oid,:wrd] := aggr.subsum(X_189,X_207,r1_344,true,true); |
| X_259 := algebra.leftfetchjoin(r1_344,X_197); |
| X_264 := algebra.leftfetchjoin(r1_344,X_203); |
| X_281:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_306 := bat.append(X_281,X_305,true); |
| X_310 := bat.append(X_306,X_308,true); |
| X_311:bat[:oid,:lng] := aggr.subsum(X_310,X_207,r1_344,true,true); |
| X_324:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_329 := bat.append(X_324,X_328,true); |
| X_331 := bat.append(X_329,X_308,true); |
| X_332 := algebra.leftfetchjoin(r1_344,X_331); |
| X_341:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_347 := bat.append(X_341,X_346,true); |
| X_348 := bat.append(X_347,X_308,true); |
| X_349 := algebra.leftfetchjoin(r1_344,X_348); |
| X_361:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_386 := bat.append(X_361,X_385,true); |
| X_390 := bat.append(X_386,X_388,true); |
| X_391:bat[:oid,:lng] := aggr.subsum(X_390,X_207,r1_344,true,true); |
| X_404:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_429 := bat.append(X_404,X_428,true); |
| X_433 := bat.append(X_429,X_431,true); |
| X_434:bat[:oid,:lng] := aggr.subsum(X_433,X_207,r1_344,true,true); |
| X_446:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_452 := bat.append(X_446,X_451,true); |
| X_453 := bat.append(X_452,X_388,true); |
| X_454 := algebra.leftfetchjoin(r1_344,X_453); |
| X_465:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_490 := bat.append(X_465,X_489,true); |
| X_494 := bat.append(X_490,X_492,true); |
| X_495:bat[:oid,:lng] := aggr.subsum(X_494,X_207,r1_344,true,true); |
| X_508:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_533 := bat.append(X_508,X_532,true); |
| X_537 := bat.append(X_533,X_535,true); |
| X_538:bat[:oid,:lng] := aggr.subsum(X_537,X_207,r1_344,true,true); |
| X_550:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_556 := bat.append(X_550,X_555,true); |
| X_557 := bat.append(X_556,X_492,true); |
| X_558 := algebra.leftfetchjoin(r1_344,X_557); |
| X_570:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_595 := bat.append(X_570,X_594,true); |
| X_599 := bat.append(X_595,X_597,true); |
| X_600:bat[:oid,:lng] := aggr.subsum(X_599,X_207,r1_344,true,true); |
| X_613:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_638 := bat.append(X_613,X_637,true); |
| X_642 := bat.append(X_638,X_640,true); |
| X_643:bat[:oid,:lng] := aggr.subsum(X_642,X_207,r1_344,true,true); |
| X_655:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_661 := bat.append(X_655,X_660,true); |
| X_662 := bat.append(X_661,X_597,true); |
| X_663 := algebra.leftfetchjoin(r1_344,X_662); |
| X_674:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_680 := bat.append(X_674,X_679,true); |
| X_681 := bat.append(X_680,X_492,true); |
| X_682 := algebra.leftfetchjoin(r1_344,X_681); |
| X_692:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_698 := bat.append(X_692,X_697,true); |
| X_699 := bat.append(X_698,X_535,true); |
| X_700 := algebra.leftfetchjoin(r1_344,X_699); |
| X_709:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_715 := bat.append(X_709,X_714,true); |
| X_716 := bat.append(X_715,X_492,true); |
| X_717 := algebra.leftfetchjoin(r1_344,X_716); |
| X_729:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_754 := bat.append(X_729,X_753,true); |
| X_758 := bat.append(X_754,X_756,true); |
| X_759:bat[:oid,:lng] := aggr.subsum(X_758,X_207,r1_344,true,true); |
| X_773:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_798 := bat.append(X_773,X_797,true); |
| X_802 := bat.append(X_798,X_800,true); |
| X_803:bat[:oid,:lng] := aggr.subsum(X_802,X_207,r1_344,true,true); |
| X_817:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_823 := bat.append(X_817,X_822,true); |
| X_824 := bat.append(X_823,X_756,true); |
| X_825 := algebra.leftfetchjoin(r1_344,X_824); |
| X_837:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_843 := bat.append(X_837,X_842,true); |
| X_844 := bat.append(X_843,X_597,true); |
| X_845 := algebra.leftfetchjoin(r1_344,X_844); |
| X_855:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_861 := bat.append(X_855,X_860,true); |
| X_862 := bat.append(X_861,X_640,true); |
| X_863 := algebra.leftfetchjoin(r1_344,X_862); |
| X_872:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_878 := bat.append(X_872,X_877,true); |
| X_879 := bat.append(X_878,X_597,true); |
| X_880 := algebra.leftfetchjoin(r1_344,X_879); |
| X_211 := bat.append(X_36,X_210,true); |
| X_215 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","gentime",0); |
| X_226 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","ggsn_id",0); |
| X_212:bat[:oid,:oid] := sql.tid(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1"); |
| X_980 := algebra.subselect(X_226,X_212,A30,A30,true,true,false); |
| (X_228,r1_374) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","ggsn_id",2); |
| X_981 := algebra.subselect(r1_374,A30,A30,true,true,false); |
| X_230 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","ggsn_id",1); |
| X_982 := algebra.subselect(X_230,X_212,A30,A30,true,true,false); |
| X_231 := sql.subdelta(X_980,X_228,X_981,X_982); |
| X_223 := calc.timestamp(A31,7); |
| X_225 := calc.timestamp(A32,7); |
| X_983 := algebra.subselect(X_215,X_231,X_223,X_225,true,true,false); |
| (X_218,r1_358) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","gentime",2); |
| X_984 := algebra.subselect(r1_358,X_223,X_225,true,true,false); |
| X_221 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","gentime",1); |
| X_985 := algebra.subselect(X_221,X_231,X_223,X_225,true,true,false); |
| X_234 := sql.subdelta(X_983,X_218,X_984,X_985); |
| X_235 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","sgsn_id",0); |
| (X_240,r1_391) := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","sgsn_id",2); |
| X_243 := sql.bind(X_35,"sys","bkpi_web_dspdelay_cisai_15min_1","sgsn_id",1); |
| X_245 := sql.projectdelta(X_234,X_235,X_240,r1_391,X_243); |
| X_246 := sql.projectdelta(X_234,X_226,X_228,r1_374,X_230); |
| (X_247,r1_408,r2_408) := group.subgroup(X_246); |
| (X_250,r1_411,r2_411) := group.subgroupdone(X_245,X_247); |
| X_253:bat[:oid,:wrd] := aggr.subcount(X_250,X_250,r1_411,false); |
| X_261 := algebra.leftfetchjoin(r1_411,X_245); |
| X_267 := algebra.leftfetchjoin(r1_411,X_246); |
| X_313 := sql.projectdelta(X_234,X_297,X_299,r1_524,X_301); |
| X_314:bat[:oid,:lng] := aggr.subsum(X_313,X_250,r1_411,true,true); |
| X_393 := sql.projectdelta(X_234,X_377,X_379,r1_770,X_381); |
| X_394:bat[:oid,:lng] := aggr.subsum(X_393,X_250,r1_411,true,true); |
| X_436 := sql.projectdelta(X_234,X_420,X_422,r1_896,X_424); |
| X_437:bat[:oid,:lng] := aggr.subsum(X_436,X_250,r1_411,true,true); |
| X_497 := sql.projectdelta(X_234,X_481,X_483,r1_1082,X_485); |
| X_498:bat[:oid,:lng] := aggr.subsum(X_497,X_250,r1_411,true,true); |
| X_540 := sql.projectdelta(X_234,X_524,X_526,r1_1208,X_528); |
| X_541:bat[:oid,:lng] := aggr.subsum(X_540,X_250,r1_411,true,true); |
| X_602 := sql.projectdelta(X_234,X_586,X_588,r1_1397,X_590); |
| X_603:bat[:oid,:lng] := aggr.subsum(X_602,X_250,r1_411,true,true); |
| X_645 := sql.projectdelta(X_234,X_629,X_631,r1_1523,X_633); |
| X_646:bat[:oid,:lng] := aggr.subsum(X_645,X_250,r1_411,true,true); |
| X_761 := sql.projectdelta(X_234,X_745,X_747,r1_1886,X_749); |
| X_762:bat[:oid,:lng] := aggr.subsum(X_761,X_250,r1_411,true,true); |
| X_805 := sql.projectdelta(X_234,X_789,X_791,r1_2017,X_793); |
| X_806:bat[:oid,:lng] := aggr.subsum(X_805,X_250,r1_411,true,true); |
| X_254 := bat.append(X_211,X_253,true); |
| X_256:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_260 := bat.append(X_256,X_259,true); |
| X_262 := bat.append(X_260,X_261,true); |
| X_263:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_265 := bat.append(X_263,X_264,true); |
| X_268 := bat.append(X_265,X_267,true); |
| (X_269,r1_464,r2_464) := group.subgroup(X_268); |
| (X_272,r1_467,r2_467) := group.subgroupdone(X_262,X_269); |
| X_275:bat[:oid,:wrd] := aggr.subsum(X_254,X_272,r1_467,true,true); |
| X_276 := algebra.leftfetchjoin(r1_467,X_268); |
| X_277 := algebra.leftfetchjoin(r1_467,X_262); |
| X_278:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_312 := bat.append(X_278,X_311,true); |
| X_315 := bat.append(X_312,X_314,true); |
| X_316:bat[:oid,:lng] := aggr.subsum(X_315,X_272,r1_467,true,true); |
| X_321:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_333 := bat.append(X_321,X_332,true); |
| X_334 := bat.append(X_333,X_314,true); |
| X_335 := algebra.leftfetchjoin(r1_467,X_334); |
| X_338:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_350 := bat.append(X_338,X_349,true); |
| X_351 := bat.append(X_350,X_314,true); |
| X_352 := algebra.leftfetchjoin(r1_467,X_351); |
| X_358:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_392 := bat.append(X_358,X_391,true); |
| X_395 := bat.append(X_392,X_394,true); |
| X_396:bat[:oid,:lng] := aggr.subsum(X_395,X_272,r1_467,true,true); |
| X_401:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_435 := bat.append(X_401,X_434,true); |
| X_438 := bat.append(X_435,X_437,true); |
| X_439:bat[:oid,:lng] := aggr.subsum(X_438,X_272,r1_467,true,true); |
| X_443:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_455 := bat.append(X_443,X_454,true); |
| X_456 := bat.append(X_455,X_394,true); |
| X_457 := algebra.leftfetchjoin(r1_467,X_456); |
| X_462:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_496 := bat.append(X_462,X_495,true); |
| X_499 := bat.append(X_496,X_498,true); |
| X_500:bat[:oid,:lng] := aggr.subsum(X_499,X_272,r1_467,true,true); |
| X_505:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_539 := bat.append(X_505,X_538,true); |
| X_542 := bat.append(X_539,X_541,true); |
| X_543:bat[:oid,:lng] := aggr.subsum(X_542,X_272,r1_467,true,true); |
| X_547:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_559 := bat.append(X_547,X_558,true); |
| X_560 := bat.append(X_559,X_498,true); |
| X_561 := algebra.leftfetchjoin(r1_467,X_560); |
| X_567:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_601 := bat.append(X_567,X_600,true); |
| X_604 := bat.append(X_601,X_603,true); |
| X_605:bat[:oid,:lng] := aggr.subsum(X_604,X_272,r1_467,true,true); |
| X_610:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_644 := bat.append(X_610,X_643,true); |
| X_647 := bat.append(X_644,X_646,true); |
| X_648:bat[:oid,:lng] := aggr.subsum(X_647,X_272,r1_467,true,true); |
| X_652:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_664 := bat.append(X_652,X_663,true); |
| X_665 := bat.append(X_664,X_603,true); |
| X_666 := algebra.leftfetchjoin(r1_467,X_665); |
| X_671:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_683 := bat.append(X_671,X_682,true); |
| X_684 := bat.append(X_683,X_498,true); |
| X_685 := algebra.leftfetchjoin(r1_467,X_684); |
| X_689:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_701 := bat.append(X_689,X_700,true); |
| X_702 := bat.append(X_701,X_541,true); |
| X_703 := algebra.leftfetchjoin(r1_467,X_702); |
| X_706:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_718 := bat.append(X_706,X_717,true); |
| X_719 := bat.append(X_718,X_498,true); |
| X_720 := algebra.leftfetchjoin(r1_467,X_719); |
| X_726:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_760 := bat.append(X_726,X_759,true); |
| X_763 := bat.append(X_760,X_762,true); |
| X_764:bat[:oid,:lng] := aggr.subsum(X_763,X_272,r1_467,true,true); |
| X_770:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_804 := bat.append(X_770,X_803,true); |
| X_807 := bat.append(X_804,X_806,true); |
| X_808:bat[:oid,:lng] := aggr.subsum(X_807,X_272,r1_467,true,true); |
| X_814:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_826 := bat.append(X_814,X_825,true); |
| X_827 := bat.append(X_826,X_762,true); |
| X_828 := algebra.leftfetchjoin(r1_467,X_827); |
| X_834:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_846 := bat.append(X_834,X_845,true); |
| X_847 := bat.append(X_846,X_603,true); |
| X_848 := algebra.leftfetchjoin(r1_467,X_847); |
| X_852:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_864 := bat.append(X_852,X_863,true); |
| X_865 := bat.append(X_864,X_646,true); |
| X_866 := algebra.leftfetchjoin(r1_467,X_865); |
| X_869:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); |
| X_881 := bat.append(X_869,X_880,true); |
| X_882 := bat.append(X_881,X_603,true); |
| X_883 := algebra.leftfetchjoin(r1_467,X_882); |
| X_849:bat[:oid,:bit] := batcalc.==(X_848,A19); |
| X_850:bat[:oid,:bit] := batcalc.isnil(X_849); |
| X_851:bat[:oid,:bit] := batcalc.ifthenelse(X_850,false:bit,X_849); |
| X_867 := batcalc.flt(X_866); |
| X_868:bat[:oid,:flt] := batmmath.floor(X_867); |
| X_884 := batcalc.flt(X_883); |
| X_885:bat[:oid,:flt] := batcalc.ifthenelse(X_851,1:flt,X_884); |
| X_886:bat[:oid,:flt] := batcalc./(X_868,X_885); |
| X_887:bat[:oid,:flt] := batcalc.ifthenelse(X_851,A20,X_886); |
| X_766:bat[:oid,:lng] := batcalc.*(X_764,A13); |
| X_767:bat[:oid,:bit] := batcalc.==(X_766,A14); |
| X_768:bat[:oid,:bit] := batcalc.isnil(X_767); |
| X_769:bat[:oid,:bit] := batcalc.ifthenelse(X_768,false:bit,X_767); |
| X_810:bat[:oid,:lng] := batcalc.*(X_808,A16); |
| X_811:bat[:oid,:lng] := batcalc.*(X_810,A17); |
| X_812 := batcalc.flt(X_811); |
| X_813:bat[:oid,:flt] := batmmath.floor(X_812); |
| X_829:bat[:oid,:lng] := batcalc.*(X_828,A18); |
| X_830 := batcalc.flt(X_829); |
| X_831:bat[:oid,:flt] := batcalc.ifthenelse(X_769,1:flt,X_830); |
| X_832:bat[:oid,:flt] := batcalc./(X_813,X_831); |
| X_833:bat[:oid,:flt] := batcalc.ifthenelse(X_769,A15,X_832); |
| X_686:bat[:oid,:bit] := batcalc.==(X_685,A10); |
| X_687:bat[:oid,:bit] := batcalc.isnil(X_686); |
| X_688:bat[:oid,:bit] := batcalc.ifthenelse(X_687,false:bit,X_686); |
| X_704 := batcalc.flt(X_703); |
| X_705:bat[:oid,:flt] := batmmath.floor(X_704); |
| X_721 := batcalc.flt(X_720); |
| X_722:bat[:oid,:flt] := batcalc.ifthenelse(X_688,1:flt,X_721); |
| X_723:bat[:oid,:flt] := batcalc./(X_705,X_722); |
| X_724:bat[:oid,:flt] := batcalc.*(X_723,A12); |
| X_725:bat[:oid,:flt] := batcalc.ifthenelse(X_688,A11,X_724); |
| X_607:bat[:oid,:bit] := batcalc.==(X_605,A8); |
| X_608:bat[:oid,:bit] := batcalc.isnil(X_607); |
| X_609:bat[:oid,:bit] := batcalc.ifthenelse(X_608,false:bit,X_607); |
| X_650 := batcalc.flt(X_648); |
| X_651:bat[:oid,:flt] := batmmath.floor(X_650); |
| X_667 := batcalc.flt(X_666); |
| X_668:bat[:oid,:flt] := batcalc.ifthenelse(X_609,1:flt,X_667); |
| X_669:bat[:oid,:flt] := batcalc./(X_651,X_668); |
| X_670:bat[:oid,:flt] := batcalc.ifthenelse(X_609,A9,X_669); |
| X_502:bat[:oid,:bit] := batcalc.==(X_500,A5); |
| X_503:bat[:oid,:bit] := batcalc.isnil(X_502); |
| X_504:bat[:oid,:bit] := batcalc.ifthenelse(X_503,false:bit,X_502); |
| X_545 := batcalc.flt(X_543); |
| X_546:bat[:oid,:flt] := batmmath.floor(X_545); |
| X_562 := batcalc.flt(X_561); |
| X_563:bat[:oid,:flt] := batcalc.ifthenelse(X_504,1:flt,X_562); |
| X_564:bat[:oid,:flt] := batcalc./(X_546,X_563); |
| X_565:bat[:oid,:flt] := batcalc.*(X_564,A7); |
| X_566:bat[:oid,:flt] := batcalc.ifthenelse(X_504,A6,X_565); |
| X_398:bat[:oid,:bit] := batcalc.==(X_396,A3); |
| X_399:bat[:oid,:bit] := batcalc.isnil(X_398); |
| X_400:bat[:oid,:bit] := batcalc.ifthenelse(X_399,false:bit,X_398); |
| X_441 := batcalc.flt(X_439); |
| X_442:bat[:oid,:flt] := batmmath.floor(X_441); |
| X_458 := batcalc.flt(X_457); |
| X_459:bat[:oid,:flt] := batcalc.ifthenelse(X_400,1:flt,X_458); |
| X_460:bat[:oid,:flt] := batcalc./(X_442,X_459); |
| X_461:bat[:oid,:flt] := batcalc.ifthenelse(X_400,A4,X_460); |
| X_318:bat[:oid,:bit] := batcalc.==(X_316,A0); |
| X_319:bat[:oid,:bit] := batcalc.isnil(X_318); |
| X_320:bat[:oid,:bit] := batcalc.ifthenelse(X_319,false:bit,X_318); |
| X_336 := batcalc.flt(X_335); |
| X_337:bat[:oid,:flt] := batmmath.floor(X_336); |
| X_353 := batcalc.flt(X_352); |
| X_354:bat[:oid,:flt] := batcalc.ifthenelse(X_320,1:flt,X_353); |
| X_355:bat[:oid,:flt] := batcalc./(X_337,X_354); |
| X_356:bat[:oid,:flt] := batcalc.*(X_355,A2); |
| X_357:bat[:oid,:flt] := batcalc.ifthenelse(X_320,A1,X_356); |
| X_889 := sql.resultSet(10,1,X_275); |
| sql.rsColumn(X_889,".bkpi","L1","wrd",64,0,X_275); |
| sql.rsColumn(X_889,".bkpi","ggsn_id","bigint",64,0,X_276); |
| sql.rsColumn(X_889,".bkpi","sgsn_id","bigint",64,0,X_277); |
| sql.rsColumn(X_889,".","kqidelay","real",24,0,X_357); |
| sql.rsColumn(X_889,".","dns_delay","real",24,0,X_461); |
| sql.rsColumn(X_889,".","tcpdelay","real",24,0,X_566); |
| sql.rsColumn(X_889,".","tcpgetdelay","real",24,0,X_670); |
| sql.rsColumn(X_889,".","tcp_delay_avgmsel","real",24,0,X_725); |
| sql.rsColumn(X_889,".","tcppostdelay","real",24,0,X_833); |
| sql.rsColumn(X_889,".","get_delay_msel_avg","real",24,0,X_887); |
| X_919 := io.stdout(); |
| sql.exportResult(X_919,X_889); |
| end s1_4; |
| # optimizer.mitosis() |
| # optimizer.dataflow() |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
616 tuples (20.058ms)
Thanks, Jason
Hi Jason,
to be able to analyze the problem, we'd need all the information to be able to reproduce it; this includes at least
- exact version of MonetDB
- operating system
- hardware, in particular #hardware threads and size of RAM
(the output of `mserver5 --version` should contain all these)
- database schema and queries
- data; or at least EXPLAIN's and TRACE's of your queries
Preferably, file a bug report with all this information.
Best,
Stefan
----- Original Message -----
> Hi guys:
> In my case , original data 8G bytes, but after plenty of
> query, the disk space usage is 361G bytes. The query is
> simply, first five UNION ALL, then fetch the result. When the
> query is running, I use vmstat to watch the IO status. When
> the database becomes larger, the BI and BO number increase
> very large too, for example bo 355224, bi 27392. And the
> query cost ten times longer than before!
> After rebooting the database, the database disk space was
> reduced to its original data size, 8G. It seems too much
> runtime data was produced. How can I solve the problem or
> avoid the problem!
>
> Thanks,Jason
******************************************************************************************
This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained here in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this email in error, please notify the sender by phone or email
immediately and delete it!
*****************************************************************************************
1
0
After plenty of querys the database use 40 times more disk space than the original data, How can I avoid this?
by liyuesen 00108910 06 Jun '13
by liyuesen 00108910 06 Jun '13
06 Jun '13
Hi guys:
In my case , original data 8G bytes, but after plenty of query, the disk space usage is 361G bytes. The query is simply, first five UNION ALL, then fetch the result. When the query is running, I use vmstat to watch the IO status. When the database becomes larger, the BI and BO number increase very large too, for example bo 355224, bi 27392. And the query cost ten times longer than before!
After rebooting the database, the database disk space was reduced to its original data size, 8G. It seems too much runtime data was produced. How can I solve the problem or avoid the problem!
Thanks,Jason
******************************************************************************************
This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained here in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this email in error, please notify the sender by phone or email
immediately and delete it!
*****************************************************************************************
2
1
hi all,
I am tracing the select of GDK, it's so complex, and In every type,
there is a nocheck variable, who can help me explain the nocheck variable,
what's the meaning of it? :)
--
Best Wishes!
zhanglei
2
1