DWH Issue on Retention Policy

Community Forums Backend Server Support
1

Hello,

Recently my DWH Analytics have stopped updating. I’ve found a couple of things from the FAQ that look like they would apply, but running through the fixes has not solved my problem. What I’ve found so far:
Errors in etl_perform_retention_policy:

> Move old partitions to archive - An error occurred executing this job entry :
> Couldn't execute SQL: CALL move_innodb_to_archive()
>Too many partitions (including subpartitions) were defined

Everything else looks clean in the log files. Checking the locks in the database I find that retention_lock is set at 1, so I updated the table to set it back to 0, and called kalturadw.add_partitions();

After that was finished, I ran the manual update script for the DWH, and end up with the same error in the DWH logs and retention_lock gets stuck at 1 again. Any ideas?

2

Hi @siactive,

See here:

3

Hi Jess,

Thanks for the article, I actually have already tried running that SP though. I am still getting the same errors on the DWH logs. Is there anyway I can verify that the SP is running properly?

Thanks,

4

Quick update,

After looking into the error
too many partitions (including subpartitions) were defined
It seems that it is more of a database issue. So I took a look in the database information_schema , and did a quick select from the table PARTITIONS. I noticed that any table that was marked as *_archive had approximately 1024 different partitions.

With my google-fu I was able to dig up some old posts about the maximum amount of partitions for MariaDB and MySQL. It seems at one point it was 1024, although it looks like it was increased to 8192 later on for MySQL (V 5.6.7 from what I’ve read).

I’m running MariaDB 5.5. Assuming that this is the cause, is there anyway to safely remove some of these partitions, or is it recommended to upgrade to MariaDB 10.1 ?

5

Hi @siactive,

No need to upgrade the DB. You don’t really need that many partitions backed up anyhow.
Let’s start by running:

mysql> select table_name, count(1) from information_schema.partitions where table_name like 'dwh_fact_%' group by table_name;

Please paste the output here.

Thanks,

6

Hi Jess,

Thanks for the input, here is the info you requested:
±------------------------------------±---------+
| table_name | count(1) |
±------------------------------------±---------+
| dwh_fact_api_calls | 62 |
| dwh_fact_api_calls_archive |1024 |
| dwh_fact_bandwidth_usage | 62 |
| dwh_fact_bandwidth_usage_archive | 1024 |
| dwh_fact_convert_job | 1 |
| dwh_fact_entries_sizes | 38 |
| dwh_fact_errors | 373 |
| dwh_fact_errors_archive | 713 |
| dwh_fact_events | 62 |
| dwh_fact_events_archive | 1024 |
| dwh_fact_fms_sessions | 62 |
| dwh_fact_fms_sessions_archive | 1024 |
| dwh_fact_fms_session_events | 62 |
| dwh_fact_fms_session_events_archive | 1024 |
| dwh_fact_incomplete_api_calls | 35 |
| dwh_fact_plays | 62 |
| dwh_fact_plays_archive | 312 |
±------------------------------------±---------+
17 rows in set (0.35 sec)

7

Hi @siactive,

This DB must have been upgraded from a pretty old version.
Let’s try:

mysql> update kalturadw_ds.retention_policy set archive_delete_days_back = 365 where archive_delete_days_back >= 1000
 
Then run:  
# su kaltura -c "/opt/kaltura/dwh/etlsource/execute/etl_perform_retention_policy.sh -p /opt/kaltura/dwh -k /opt/kaltura/pentaho/pdi/kitchen.sh"

And let’s see what happens.

8

Thanks Jess, that seems to have cleared up the errors I was seeing.

Appreciate your help!

9

Most welcome. Glad to hear we’re good.