projection stats not getting updated

Moderator: NorbertKrupa

Victorgm
Beginner
Beginner
Posts: 25
Joined: Fri Jul 17, 2015 2:22 pm

Re: projection stats not getting updated

Post by Victorgm » Mon Aug 24, 2015 3:41 pm

Thanks very much for your help, Jim & Norbert.

Here is the setup, step-by-step.
At the bottom of the post, I included what I thought are relevant columns from the projections table - let me know if you need anything else.

One thing I noticed - table (superprojection) segmentation and custom projection segmentation do not match. Is this an issue?


-- set up the test scenario ----
create schema test ;

create table test.test_stats as select * from <source_schema>.<table> limit 1000000 ;

-- confirmed 1 M rows in anchor table:
select count(*) from test.test_stats ;
count
----------
1000000


--I queried projection_storage to confirm data is populated across all nodes
-- in the superprojection.


-- Create a custom projection:
CREATE PROJECTION test.test_stats_p1
(
col1 encoding rle,
col2 enchoding rle,
col3,
col4,
etc...
)
AS SELECT test_stats.col1,
test_stats.col2,
etc...
FROM test.test_stats
ORDER BY test_stats.col1
SEGMENTED BY hash(col1)
ALL NODES KSAFE 1;

-- I queried projection_storage to confirm creation of new empty projections on all 3 nodes.


-- populate the new query
select start_refresh() ;

-- I queried projection_storage to confirm new projections are populated across all 3 nodes.


-- So here it is - I analyze stats and but afterward the projections table
-- shows the new projection has_statistics = "false"
select analyze_statistics('test.test_stats') ;


projection_schema projection_name projection_basename anchor_table_name create_type is_up_to_date has_statistics is_segmented segment_expression

test test_stats_b0 test_stats test_stats CREATE TABLE true true true hash(col1, col2, col3, col4...)
test test_stats_b1 test_stats test_stats CREATE TABLE true true true hash(col1, col2, col3, col4...)
test test_stats_p1_b0 test_stats_p1 test_stats CREATE PROJECTION true false true hash(col1)
test test_stats_p1_b1 test_stats_p1 test_stats CREATE PROJECTION true false true hash(col1)



Thanks again!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: projection stats not getting updated

Post by JimKnicely » Mon Aug 24, 2015 5:20 pm

Hi,

I tried to mimic your scenario (see below). I created a new DB and didn't have a table with 1,000,000 records so I created one from a file that had 1,000,000 random integers.

I created the data file using this command in the Linux prompt:

Code: Select all

for i in {1..1000000}; do echo $[($RANDOM % 1000)]; done > test.in
Here is my test of your scenario:

Code: Select all

dbadmin=> create schema test ;
CREATE SCHEMA

dbadmin=> create table test.test_stats (col1 int) order by col1 segmented by hash(col1) all nodes ksafe 1;
CREATE TABLE
dbadmin=> copy test.test_stats from '/home/dbadmin/test.in' direct;
 Rows Loaded 
-------------
     1000000
(1 row)

dbadmin=> create projection test.test_stats_p1 (col1 encoding rle) as select col1 from test.test_stats order by col1 segmented by hash(col1) all nodes ksafe 1;
WARNING 4468:  Projection <test.test_stats_p1_b0> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
WARNING 4468:  Projection <test.test_stats_p1_b1> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

dbadmin=> select start_refresh();
             start_refresh              
----------------------------------------
 Starting refresh background process. 

(1 row)

dbadmin=> select count(*) from projection_refreshes where is_executing;
 count 
-------
     0
(1 row)

dbadmin=> select projection_schema, projection_name, projection_basename, create_type, is_up_to_date,has_statistics,is_segmented, segment_expression from projections where anchor_table_name = 'test_stats';
 projection_schema | projection_name  | projection_basename |          create_type          | is_up_to_date | has_statistics | is_segmented |  segment_expression   
-------------------+------------------+---------------------+-------------------------------+---------------+----------------+--------------+-----------------------
 test              | test_stats_b0    | test_stats          | CREATE TABLE WITH PROJ CLAUSE | t             | f              | t            | hash(test_stats.col1)
 test              | test_stats_b1    | test_stats          | CREATE TABLE WITH PROJ CLAUSE | t             | f              | t            | hash(test_stats.col1)
 test              | test_stats_p1_b0 | test_stats_p1       | CREATE PROJECTION             | t             | f              | t            | hash(test_stats.col1)
 test              | test_stats_p1_b1 | test_stats_p1       | CREATE PROJECTION             | t             | f              | t            | hash(test_stats.col1)
(4 rows)

dbadmin=> select analyze_statistics('test.test_stats');
 analyze_statistics 
--------------------
                  0
(1 row)

dbadmin=> select projection_schema, projection_name, projection_basename, create_type, is_up_to_date,has_statistics,is_segmented, segment_expression from projections where anchor_table_name = 'test_stats';
 projection_schema | projection_name  | projection_basename |          create_type          | is_up_to_date | has_statistics | is_segmented |  segment_expression   
-------------------+------------------+---------------------+-------------------------------+---------------+----------------+--------------+-----------------------
 test              | test_stats_b0    | test_stats          | CREATE TABLE WITH PROJ CLAUSE | t             | t              | t            | hash(test_stats.col1)
 test              | test_stats_b1    | test_stats          | CREATE TABLE WITH PROJ CLAUSE | t             | t              | t            | hash(test_stats.col1)
 test              | test_stats_p1_b0 | test_stats_p1       | CREATE PROJECTION             | t             | t              | t            | hash(test_stats.col1)
 test              | test_stats_p1_b1 | test_stats_p1       | CREATE PROJECTION             | t             | t              | t            | hash(test_stats.col1)
(4 rows)
My "test_stats_p1" projection has stats...

So, what version of Vertica are you using? I have 7.1.1-12 installed.

Code: Select all

dbadmin=> select version();
               version               
-------------------------------------
 Vertica Analytic Database v7.1.1-12
(1 row)
Can you do what I did in your system to see if you get the same results?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Victorgm
Beginner
Beginner
Posts: 25
Joined: Fri Jul 17, 2015 2:22 pm

Re: projection stats not getting updated

Post by Victorgm » Mon Aug 24, 2015 7:32 pm

select version();
Vertica Analytic Database v7.1.2-1

I repeated your test and got the same results you did.
I am looking into this further and will let you know the results.

Post Reply

Return to “Vertica Performance Tuning”