CODEWITHSUNDEEP
vertica
Maxim
Maxim

Reputation: 9961

ERROR: Too many data partitions

I see the following error in vertica.log:

2016-09-01 15:30:54.007 TM Moveout:0x7f9438012440-a00000001212c3 [Txn] <INFO> Begin Txn: a00000001212c3 'Moveout: Tuple Mover'
2016-09-01 15:30:54.007 TM Moveout:0x7f9438012440-a00000001212c3 [TM] <INFO> Tuple Mover: moving out projection rosing_epg_program_events_super
2016-09-01 15:30:54.017 TM Moveout:0x7f9438012440-a00000001212c3 [EE] <INFO> (a00000001212c3) Moveout projection staging.rosing_epg_program_events_super
2016-09-01 15:30:54.017 TM Moveout:0x7f9438012440-a00000001212c3 [EE] <INFO> (a00000001212c3) TM Moveout: moving out data in WOS for proj "staging.rosing_epg_program_events_super" to epoch 3061
2016-09-01 15:30:54.017 TM Moveout:0x7f9438012440-a00000001212c3 [EE] <INFO> (a00000001212c3) Executing the moveout plan
2016-09-01 15:30:54.040 TM Moveout:0x7f9438012440-a00000001212c3 [EE] <INFO> SortManager found maxMerges 7 too small(64 MB Assigned).
2016-09-01 15:30:54.040 TM Moveout:0x7f9438012440-a00000001212c3 [EE] <INFO> After disabling optimization, maxMerges becomes 15.
2016-09-01 15:30:54.069 TM Moveout:0x7f9438012440-a00000001212c3 [Txn] <INFO> Rollback Txn: a00000001212c3 'Moveout: (Table: staging.rosing_epg_program_events) (Projection: staging.rosing_epg_program_events_super)'
2016-09-01 15:30:54.070 TM Moveout:0x7f9438012440 <LOG> @v_statistic_node0001: 00000/3298: Event Posted: Event Code:14 Event Id:261 Event Severity: Warning [4] PostedTimestamp: 2016-09-01 16:30:54.069887 ExpirationTimestamp: 2016-09-01 16:31:09.069887 EventCodeDescription: Timer Service Task Error ProblemDescription: threadShim: Too many data partitions DatabaseName: statistic Hostname: rosing-vertica.elt.stag.local
2016-09-01 15:30:54.070 TM Moveout:0x7f9438012440 <ERROR> @v_statistic_node0001: {threadShim} 54000/5060: Too many data partitions
        HINT:  Verify that the table partitioning expression is correct
        LOCATION:  handlePartitionKey, /scratch_a/release/16125/vbuild/vertica/EE/Operators/DataTarget.cpp:1478
2016-09-01 15:30:54.070 TM Moveout:0x7f9438012440 [Util] <INFO> Task 'TM Moveout' enabled

Seems like I choose wrong field for partitioning and reached limit of partitions in WOS as described here.

Task SELECT do_tm_task('moveout'); raise the following error:

 Task: moveout
(Table: staging.rosing_schema_migrations) (Projection: staging.rosing_schema_migrations_super)
...
(Table: staging.rosing_epg_program_events) (Projection: staging.rosing_epg_program_events_super)
On node v_statistic_node0001:
  ERROR 5060:  Too many data partitions

(1 row)

Anybody know how to fix this problem?

Update:

I can't remove partitioning from this table:

ALTER TABLE rosing_epg_program_events REMOVE PARTITIONING

because this SQL raise the same error: Too many data partitions

UPDATE 2

I fixed this problem use woot answer. Thank you a lot!

Here is my steps for fix it:

  1. Create copy of rosing_epg_program_events table:

    CREATE TABLE staging.rosing_epg_program_events2
LIKE staging.rosing_epg_program_events;

  2. Remove partitioning from new table:

    ALTER TABLE staging.rosing_epg_program_events2 REMOVE PARTITIONING;

  3. Copy data from old to new table. Seems like old table contains all (!) data inserted before and after appear problem:

    INSERT /*+ DIRECT */ INTO staging.rosing_epg_program_events2
SELECT * FROM staging.rosing_epg_program_events;

  4. Drop old table:

    DROP TABLE staging.rosing_epg_program_events;

  5. Rename new table:

    ALTER TABLE staging.rosing_epg_program_events2 RENAME TO rosing_epg_program_events;

  6. Run Moveout operation for any case. Now it works fine:

    SELECT do_tm_task('moveout');

  7. Check last good epoch for any case. Now it show actual value:

    SELECT GET_LAST_GOOD_EPOCH();
SELECT * FROM epochs WHERE epoch_number = 3064; // result of previous command

Seems like all works fine now.

Upvotes: 1

Views: 1412

Answers (1)

woot
woot

Reputation: 7616

Do a CREATE TABLE AS SELECT or CREATE TABLE LIKE INCLUDING PROJECTIONS, remove the partitions, then INSERT /*+ DIRECT */ SELECT to copy the data out and drop the table then rename. Also, when creating partitions, try to target a granularity somewhere under 40 partitions. You didn't specify, but if using a timestamp, use a formula to extract out a less granular value. For example, to do monthly, do: 

EXTRACT (year FROM mydate) * 100 + EXTRACT (month FROM mydate)

You don't have to worry about using formulas in the partitioning for Vertica. It uses min/max values for the fields instead of direct matching on the partition key.

Upvotes: 2

Related Questions