dynamobi/dynamobi — Issue #198: [LDB-123] analyze table runtime increases rapidly after delete and re-insert
Author: dynamobi-build
Created: 2011-12-30 02:21:03+00:00
State: closed
Labels: LDB

[reporter="kvu", created="Wed, 9 Aug 2006 18:47:51 -0500 (GMT-05:00)"]
If we delete from a table and repopulate it, time taken to analyze table is shot up. The effect is clearer for larger table such as bench1M (had to kill process after a long time)

Time(seconds) taken to analyze bench tables:

---------------------------------------------------

first round: create flatfileserver

create schema ff_schema

import foreign schema to ff_schema

create schema s & its tables, insert from ff_schema into tables

analyze compute statistics for all columns

(no create index)

bench10kbench100k bench1m

6 9 122

---------------------------------------------------

second round: delete from tables (of schema s)

insert from ff_schema into these tables

analyze compute statistics for all columns

(no create index)

bench10kbench100k bench1m

22 437 killed after 1 hr.

---------------------------------------------------

The same experiment is repeated after running "ant createCatalog":

--------------------------------------------

run "ant createCatalog" and repeat the above

--------------------------------------------

---------------------------------------------------

first round:

bench10kbench100k bench1m

6 9 125

---------------------------------------------------

---------------------------------------------------

second round:

bench10kbench100k bench1m

21 24 did not run analyze

---------------------------------------------------

third round: repeat steps in second round

bench10kbench100k bench1m

23 28 killed after 40min

--------------------------------------------

run "ant createCatalog" and repeat the above

--------------------------------------------

---------------------------------------------------

first round:

bench10kbench100k bench1m

6 9 125

---------------------------------------------------

---------------------------------------------------

second round:

bench10kbench100k bench1m

17 429 did not run analyze

---------------------------------------------------

third round: repeat steps in second round

bench10kbench100k bench1m

23 1092 killed after 2 hrs

The script to reproduce up to second round:

create foreign data wrapper ff_jdbc

library 'class com.lucidera.farrago.namespace.flatfile.FlatFileDataWrapper'

language java;

create server ff_server

foreign data wrapper ff_jdbc

options(

directory '/home/kvu/open/luciddb/test/sql/bench/csv-copy',

file_extension '.csv',

ctrl_file_extension '.bcp',

field_delimiter ',',

line_delimiter '\n',

quote_char '"',

escape_char '',

with_header 'yes',

num_rows_scan '1'

);

create schema ff_schema;

import foreign schema "BCP"

limit to ("bench100", "bench10K", "bench100K", "bench1M")

from server ff_server into ff_schema;

create schema s;

set schema 's';

create table BENCH10K (

"kseq" bigint primary key,

"k2" bigint,

"k4" bigint,

"k5" bigint,

"k10" bigint,

"k25" bigint,

"k100" bigint,

"k1k" bigint,

"k10k" bigint,

"k40k" bigint,

"k100k" bigint,

"k250k" bigint,

"k500k" bigint)

server sys_column_store_data_server;

-- create clustered index BENCH10K_ALL on BENCH10K("kseq","k2","k4","k5","k10","k25","k100","k1k","k10k","k40k","k100k","k250k","k500k");

create table BENCH100K (

"kseq" bigint primary key,

"k2" bigint,

"k4" bigint,

"k5" bigint,

"k10" bigint,

"k25" bigint,

"k100" bigint,

"k1k" bigint,

"k10k" bigint,

"k40k" bigint,

"k100k" bigint,

"k250k" bigint,

"k500k" bigint)

server sys_column_store_data_server;

-- create clustered index BENCH100K_ALL on BENCH100K("kseq","k2","k4","k5","k10","k25","k100","k1k","k10k","k40k","k100k","k250k","k500k");

create table BENCH1M (

"kseq" bigint primary key,

"k2" bigint,

"k4" bigint,

"k5" bigint,

"k10" bigint,

"k25" bigint,

"k100" bigint,

"k1k" bigint,

"k10k" bigint,

"k40k" bigint,

"k100k" bigint,

"k250k" bigint,

"k500k" bigint)

server sys_column_store_data_server;

-- create clustered index BENCH1M_ALL on BENCH1M("kseq","k2","k4","k5","k10","k25","k100","k1k","k10k","k40k","k100k","k250k","k500k");

INSERT INTO s.BENCH10K

SELECT "kseq","k2","k4","k5","k10","k25","k100","k1k","k10k","k40k","k100k","k250k","k500k"

FROM ff_schema."bench10K";

INSERT INTO s.BENCH100K

SELECT "kseq","k2","k4","k5","k10","k25","k100","k1k","k10k","k40k","k100k","k250k","k500k"

FROM ff_schema."bench100K";

INSERT INTO s.BENCH1M

SELECT "kseq","k2","k4","k5","k10","k25","k100","k1k","k10k","k40k","k100k","k250k","k500k"

FROM ff_schema."bench1M";

------------------------------------------------------------

--first round of analyze table

------------------------------------------------------------

analyze table bench10k compute statistics for all columns;

analyze table bench100k compute statistics for all columns;

analyze table bench1m compute statistics for all columns;

------------------------------------------------------------

-- restore data

delete from bench10K;

delete from bench100K;

delete from bench1M;

insert into bench10K

select "kseq","k2","k4","k5","k10","k25","k100","k1k","k10k","k40k","k100k","k250k","k500k"

from ff_schema."bench10K";

insert into bench100K

select "kseq","k2","k4","k5","k10","k25","k100","k1k","k10k","k40k","k100k","k250k","k500k"

from ff_schema."bench100K";

insert into bench1M

select "kseq","k2","k4","k5","k10","k25","k100","k1k","k10k","k40k","k100k","k250k","k500k"

from ff_schema."bench1M";

------------------------------------------------------------

--second round of analyze table

------------------------------------------------------------

analyze table bench10k compute statistics for all columns;

analyze table bench100k compute statistics for all columns;

analyze table bench1m compute statistics for all columns;

------------------------------------------------------------