We have a bit of an issue with keeping transformations around for a long time, which leaves a lot of jobs in our system that are not removed until the the transformations are completed or cleaned. So looking at the JobDB, there are at least two ways its size can be reduced, which makes for faster maintenance.
- HeartBeatLoggingInfo: I think this information is only used by the StalledJobAgent, and after jobs are finalized this information could be removed. The cleanup could be added to the JobCleaningAgent as an optional feature, which slowly removes this information.
2.) JobJDLs: The JDLs could be compressed (gzip) before being given to the database.
While the DB can be instructed to compress columns on its own, it seems to be recommended to compress on the client side, and thus taking the load of the DB and reducing traffic to and from the DB. Recreating the JobJDLs table on our test instance with a 1200 JDLs shows a reduction of more than three (looking at what show table status reports, note that many of the values are approximate, the row count is the same for the two tables.)
mysql> show table status ;
+----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| CompJDLs | InnoDB | 10 | Dynamic | 906 | 6383 | 5783552 | 0 | 0 | 2097152 | 87098 | 2019-12-10 09:45:18 | NULL | NULL | latin1_swedish_ci | NULL | | |
| JobJDLs | InnoDB | 10 | Dynamic | 697 | 26350 | 18366464 | 0 | 0 | 2097152 | 87098 | 2019-12-10 09:45:01 | NULL | NULL | latin1_swedish_ci | NULL | |
+----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
All of this can be implemented without altering the tables, and be made optionally.
What do people think?
We have a bit of an issue with keeping transformations around for a long time, which leaves a lot of jobs in our system that are not removed until the the transformations are completed or cleaned. So looking at the JobDB, there are at least two ways its size can be reduced, which makes for faster maintenance.
2.) JobJDLs: The JDLs could be compressed (gzip) before being given to the database.
While the DB can be instructed to compress columns on its own, it seems to be recommended to compress on the client side, and thus taking the load of the DB and reducing traffic to and from the DB. Recreating the JobJDLs table on our test instance with a 1200 JDLs shows a reduction of more than three (looking at what
show table statusreports, note that many of the values are approximate, the row count is the same for the two tables.)All of this can be implemented without altering the tables, and be made optionally.
What do people think?