Back (Current repo: scraps)

commit 41c8fd99c1f8c8ca947e2cd89f9209c9ebfe64db
parent 98b6a731ab6af6dfe759779c1fa95bff6d479202
Author: root <root>
Date:   Sat, 26 Apr 2025 12:45:04 +0200

new bash script

Diffstat:
A
bash/funcs/daily_range_partition.sh
 | 
57
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1 file changed, 57 insertions(+), 0 deletions(-)
diff --git a/bash/funcs/daily_range_partition.sh b/bash/funcs/daily_range_partition.sh
@@ -0,0 +1,57 @@
+#!/bin/bash
+
+DD=$(date +%Y-%m-%d)
+TD=$(date -d "$DD + 1 day" +%Y-%m-%d)
+MARIADB="mysql -u root -A "
+
+#create new partition for today
+echo "ALTER TABLE some_table DROP PARTITION pFuture; ALTER TABLE some_table ADD PARTITION (PARTITION p$(date -d "$DD" +%Y%m%d) VALUES LESS THAN (TO_DAYS('$TD')), PARTITION pFuture VALUES LESS THAN MAXVALUE);" | $MARIADB
+
+#drop old partitions
+OLD_PART=$(date -d "$DD - 6 months" +%Y%m%d)
+
+for PART in $(echo "SELECT partition_name FROM information_schema.partitions WHERE table_name = 'some_table' AND partition_method = '
+RANGE';" | $MARIADB --skip-column-names | grep -v Future)
+do
+    PART_DATE="${PART:1}" #drop prefix p to extract date portion
+    PART_DATE_TEMP="${PART_DATE:0:4}-${PART_DATE:4:2}-${PART_DATE:6:2}"
+
+    if [[ "$OLD_PART" > "$PART_DATE_TEMP" ]]; then
+        echo "ALTER TABLE some_table DROP PARTITION ${PART};" | $MARIADB
+    fi
+done
+
+#EXPLAINER
+
+#The benefit of partitions in the case of large tables is that the
+#queries hitting the table can be told to use only specific partitions
+#which means that the query optimizer will not even need to look at
+#the rest of the table at all.
+
+#Effectively, partitioning splits the data in the big table down into separate tables,
+#while still getting treated as a single table. When creating a partition, the partition key
+#must be added into the PRIMARY KEY (e.g. id,parition_key). Queries with a WHERE clause should
+#specify the partition(s) to be used e.g.
+#SELECT <stuff> FROM some_table PARTITION(pYYYYMMDD)
+#or if using a JOIN, the join should ideally happen on the partition_key
+
+# Example of a RANGE partitionable table, e.g. some stuff gets written here daily
+# and we need to keep this data around for a long time, maybe transaction logs, or so
+
+#CREATE TABLE `some_table` (
+#    `id` varchar(36) NOT NULL,
+#    `user_id` varchar(36) DEFAULT NULL,
+#    `date` varchar(36) DEFAULT NULL,
+#    `interesting_value1` int(11) DEFAULT NULL,
+#    `interesting_value2` int(11) DEFAULT NULL,
+#    `partition_key` datetime NOT NULL DEFAULT current_timestamp(),
+#    PRIMARY KEY (`id`,`partition_key`)
+#) ENGINE=InnoDB;
+
+#ALTER TABLE some_table
+#PARTITION BY RANGE (TO_DAYS(partition_key)) (
+#    PARTITION pYYYYMMDD VALUES LESS THAN (TO_DAYS('YYYY-MM-DD+1')),
+#    PARTITION pFuture VALUES LESS THAN MAXVALUE
+#);
+
+