Daten archivieren ohne Produktivsystem zu beschädigen

Das Problem: Große Tabellen und ihre Folgen

In einem wachsenden E-Commerce-System wachsen Datenbanktabellen kontinuierlich. Bestellungen, Log-Einträge, Suchanfragen, Session-Daten — all das akkumuliert sich Jahr für Jahr. Eine sales_order-Tabelle mit 10 Millionen Zeilen, eine sales_order_item mit 40 Millionen Zeilen — das sind realistische Zahlen für mittelgroße Shops nach fünf Betriebsjahren.

Die Konsequenzen großer Tabellen sind weitreichend: Abfragen, die früher Millisekunden brauchten, dauern jetzt Sekunden — auch wenn Indizes vorhanden sind. Backups mit mysqldump dauern Stunden statt Minuten. ANALYZE TABLE und OPTIMIZE TABLE werden zu stundenlangen Operationen. Der MySQL-Buffer-Pool füllt sich mit selten genutzten alten Daten statt mit aktuellen Betriebsdaten. Das führt zu langsamen Ladezeiten, instabilem Verhalten unter Last und steigendem Infrastrukturaufwand.

Die Daten-Archivierung ist die Lösung: Alte Daten, die für den laufenden Betrieb nicht mehr benötigt werden, werden in Archivtabellen oder separaten Datenbanken ausgelagert. Die Produktionstabellen bleiben schlank und performant. Dabei ist entscheidend, dass die Archivierung selbst das Produktivsystem nicht belastet — also in kleinen Chunks, mit Pausen zwischen Batches und ohne lange Sperren.

pt-archiver: Chunk-weises Archivieren ohne Produktionsstörungen

pt-archiver aus dem Percona Toolkit ist das empfohlene Werkzeug für das kontrollierte, chunk-weise Auslagern von Daten. Es löscht oder kopiert Zeilen in kleinen Batches, wartet zwischen den Batches eine konfigurierbare Zeit und überwacht dabei die Datenbankauslastung. Wenn die Last zu hoch wird, drosselt pt-archiver automatisch.

pt-archiver kann in drei Modi betrieben werden: Im ersten Modus löscht es Zeilen aus der Quelltabelle und schreibt sie parallel in eine Archivtabelle. Im zweiten Modus löscht es nur. Im dritten Modus kopiert es nur. Der wichtigste Parameter ist --chunk-size, der die Anzahl der Zeilen pro Batch kontrolliert, und --sleep, der die Pause zwischen Batches in Sekunden angibt. Der Parameter --max-load setzt automatische Bremsschwellen basierend auf MySQL-Status-Variablen wie Threads_running.

-- pt-archiver is run from the command line (not within MySQL)
-- First: create the archive table with identical structure + archived_at column
CREATE TABLE sales_order_archive LIKE sales_order;
ALTER TABLE sales_order_archive
  ADD COLUMN archived_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  AFTER updated_at;

-- Run pt-archiver to move old completed orders to archive table:
-- pt-archiver \
--   --source  h=127.0.0.1,D=magento,t=sales_order,u=user,p=secret \
--   --dest    h=127.0.0.1,D=magento,t=sales_order_archive \
--   --where   "state = 'complete' AND created_at < NOW() - INTERVAL 2 YEAR" \
--   --chunk-size   500 \
--   --sleep        0.2 \
--   --limit     100000 \
--   --progress    5000 \
--   --statistics \
--   --bulk-insert \
--   --bulk-delete

-- Verify results after archiving
SELECT
  (SELECT COUNT(*) FROM sales_order
     WHERE state = 'complete'
       AND created_at < NOW() - INTERVAL 2 YEAR) AS remaining_old_in_source,
  (SELECT COUNT(*) FROM sales_order_archive)     AS archived_rows;
  

DIY Chunked DELETE: Mit LIMIT und SLEEP selbst bauen

Wenn pt-archiver nicht verfügbar ist oder eine einfachere Lösung ausreicht, lässt sich chunk-weises Archivieren auch mit einfachem SQL und einem Shell-Skript umsetzen. Das Grundprinzip ist dasselbe: Zeilen in kleinen Batches mit LIMIT verschieben, zwischen Batches schlafen, wiederholen bis keine Zeilen mehr übrig sind.

Ein wichtiger Hinweis: Ein großes DELETE ohne LIMIT in der Produktion ist gefährlich. Es hält eine lange Transaktion offen, die den Undo-Log aufbläht, den Replikations-Lag erhöht und andere Abfragen blockieren kann. Das LIMIT-Muster erzeugt kurze, schnell abgeschlossene Transaktionen und ist produktionssicher. Die Chunk-Größe sollte zwischen 500 und 2000 Zeilen liegen — je nach Zeilengröße und Serverlast.

-- DIY chunked archiving: first copy to archive, then delete from source
-- Repeat both operations in a loop until 0 rows affected

-- Step A: Copy one chunk to archive table
INSERT INTO sales_order_archive
  SELECT *, NOW() AS archived_at
  FROM sales_order
  WHERE state    = 'complete'
    AND created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR)
  LIMIT 500;  -- chunk size: 500 rows per iteration

-- Step B: Delete the same chunk from source
-- Note: LIMIT 500 ensures a short transaction, low undo log pressure
DELETE FROM sales_order
WHERE state    = 'complete'
  AND created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR)
LIMIT 500;

-- Shell loop pattern (bash):
-- while true; do
--   ROWS=$(mysql -u root -p magento -N -e "
--     DELETE FROM sales_order
--     WHERE state = 'complete'
--       AND created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR)
--     LIMIT 500;
--     SELECT ROW_COUNT();")
--   echo "Deleted: $ROWS rows at $(date)"
--   [ "$ROWS" -eq 0 ] && break
--   sleep 0.3   # pause between batches to reduce replication lag
-- done

-- Check remaining rows:
SELECT COUNT(*) AS rows_pending
FROM sales_order
WHERE state    = 'complete'
  AND created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);
  

Archive-Tabellen-Design: Struktur und archived_at-Spalte

Eine gute Archivtabelle hat dieselbe Grundstruktur wie die Quelltabelle, damit SQL-Abfragen auf der Archivtabelle mit demselben Code funktionieren wie auf der Produktionstabelle. Zusätzlich gibt es mindestens eine Spalte archived_at (DATETIME oder TIMESTAMP), die festhält, wann die Zeile archiviert wurde. Der Primary Key der Quelltabelle bleibt als Primary Key erhalten, um Duplikate zu verhindern.

Sekundärindizes können in der Archivtabelle reduziert werden — da Archivdaten nur selten und selektiv abgefragt werden, sind nicht alle Produktions-Indizes nötig. Das spart Speicherplatz. Für sehr große Datenmengen empfiehlt sich die Archivtabelle in einer separaten Datenbank zu halten, damit Archivdaten den Buffer Pool des Produktionssystems nicht belasten.

-- Create archive table with minimal index set and compressed storage
CREATE TABLE sales_order_archive (
  entity_id       INT UNSIGNED   NOT NULL,
  increment_id    VARCHAR(50)    NOT NULL,
  store_id        SMALLINT UNSIGNED DEFAULT NULL,
  created_at      TIMESTAMP      NULL DEFAULT NULL,
  updated_at      TIMESTAMP      NULL DEFAULT NULL,
  customer_id     INT UNSIGNED   DEFAULT NULL,
  customer_email  VARCHAR(128)   DEFAULT NULL,
  grand_total     DECIMAL(20,4)  DEFAULT NULL,
  status          VARCHAR(32)    DEFAULT NULL,
  state           VARCHAR(32)    DEFAULT NULL,
  -- Archive metadata: when was this row archived?
  archived_at     DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- Minimal indexes: only what's needed for archive queries
  PRIMARY KEY (entity_id),
  INDEX idx_archived_at (archived_at),
  INDEX idx_created_at  (created_at),
  INDEX idx_state_store (state, store_id)
) ENGINE=InnoDB
  ROW_FORMAT=COMPRESSED   -- compressed format for archive data savings
  KEY_BLOCK_SIZE=8;

-- Compare sizes: source vs archive table
SELECT
  table_name,
  table_rows,
  ROUND(data_length  / 1024 / 1024, 1) AS data_mb,
  ROUND(index_length / 1024 / 1024, 1) AS index_mb
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
  AND table_name IN ('sales_order', 'sales_order_archive')
ORDER BY table_name;
  

Soft-Delete vs. Hard-Delete: Abwägungen

Beim Archivieren stellt sich die Frage: Sollen Zeilen physisch gelöscht werden (Hard-Delete), oder soll ein Flag gesetzt werden, das sie als archiviert markiert (Soft-Delete)? Beide Ansätze haben ihre Berechtigung, je nach Anwendungsfall.

Soft-Delete hat den Vorteil, dass Daten einfach reaktiviert werden können. Das ist nützlich für Produkte, die vorübergehend deaktiviert werden, oder für Kunden-Accounts, die aus datenschutzrechtlichen Gründen für eine Frist aufbewahrt werden müssen. Der Nachteil: Die Tabelle wächst weiterhin, und alle Abfragen müssen einen zusätzlichen Filter enthalten. Das erhöht die Komplexität und erschwert Index-Design.

Hard-Delete mit separater Archivtabelle ist für die meisten Archivierungszwecke die bessere Wahl. Die Produktionstabelle bleibt schlank, Abfragen werden nicht durch Soft-Delete-Filter verkompliziert, und die Archivtabelle enthält nur wirklich archivierte Daten mit einem klaren Zeitstempel.

Magento sales_order Archivierung

Magento Commerce (Adobe Commerce) bietet eine eingebaute Order-Archivierungsfunktion, die Bestellungen in einen separaten "Archiv"-Status verschiebt und aus dem aktiven Bestellgrid ausblendet. Die Archivtabellen tragen das Präfix magento_sales_archive. Diese Funktion ist in Magento Open Source nicht verfügbar.

Für Open-Source-Installationen oder als Ergänzung empfiehlt sich ein externes Archivierungskonzept für Bestelldaten älter als 2–3 Jahre. Dabei müssen verwandte Tabellen konsistent mitarchiviert werden: sales_order_item, sales_order_address, sales_order_payment, sales_invoice, sales_invoice_item, sales_shipment.

-- Analyze sales_order age distribution before archiving
SELECT
  YEAR(created_at) AS order_year,
  state,
  COUNT(*)         AS order_count,
  ROUND(SUM(grand_total), 2) AS total_revenue
FROM sales_order
GROUP BY YEAR(created_at), state
ORDER BY order_year DESC, order_count DESC;

-- Find archivable orders (completed, older than 2 years)
SELECT
  COUNT(*)         AS archivable_count,
  MIN(created_at)  AS oldest_date,
  MAX(created_at)  AS newest_archivable,
  ROUND(COUNT(*) * 0.5 / 1024, 1) AS estimated_mb  -- rough size estimate
FROM sales_order
WHERE state     = 'complete'
  AND created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);

-- Check if Magento's built-in archive is active (Commerce/EE only):
SHOW TABLES LIKE 'magento_sales_archive%';

-- After archiving: update statistics
ANALYZE TABLE sales_order, sales_order_item;
  

InnoDB-Fragmentierung nach großen Löschvorgängen

Nach dem Löschen vieler Zeilen aus einer InnoDB-Tabelle verbleiben die freien Seiten zunächst im B-Tree der Tabelle — InnoDB gibt den Speicherplatz nicht sofort an das Betriebssystem zurück. Das führt zu Fragmentierung: Die Datei auf der Festplatte ist noch genauso groß wie vor dem Löschen, obwohl deutlich weniger Daten enthalten sind. Neue INSERTs können zwar die freien Seiten wiederverwenden, aber der auf der Festplatte belegte Speicher schrumpft nicht automatisch.

Um Speicherplatz zurückzugewinnen und die Tabellenstruktur zu verdichten, wird OPTIMIZE TABLE verwendet. In modernen InnoDB-Versionen führt das einen Online-Rebuild durch (wie ALTER TABLE ENGINE=InnoDB). Für sehr große Tabellen ist pt-online-schema-change mit einem leeren ALTER die produktionssicherere Alternative, da es chunk-weise arbeitet und weniger Write-Overhead erzeugt.

-- Identify fragmented InnoDB tables (high data_free = unused space inside file)
SELECT
  table_name,
  table_rows,
  ROUND(data_length   / 1024 / 1024, 1) AS data_mb,
  ROUND(data_free     / 1024 / 1024, 1) AS free_mb,
  ROUND(data_free / (data_length + 1) * 100, 0) AS fragmentation_pct
FROM information_schema.TABLES
WHERE table_schema  = DATABASE()
  AND engine        = 'InnoDB'
  AND data_free     > 50 * 1024 * 1024   -- tables with >50MB free space
ORDER BY data_free DESC
LIMIT 10;

-- OPTIMIZE TABLE: reclaims free space (online rebuild in modern InnoDB)
-- Safe for small-to-medium tables during low-traffic windows
OPTIMIZE TABLE sales_order;

-- For large tables: use pt-osc with empty ALTER (chunk-wise, no full lock)
-- pt-online-schema-change \
--   --alter="ENGINE=InnoDB" \   -- empty alter triggers a full table rebuild
--   --chunk-size=2000 \
--   --execute \
--   h=127.0.0.1,D=magento,t=sales_order,u=user,p=secret

-- Verify free space after optimization:
SELECT table_name,
       ROUND(data_length / 1024 / 1024, 1) AS data_mb,
       ROUND(data_free   / 1024 / 1024, 1) AS free_mb
FROM information_schema.TABLES
WHERE table_schema = DATABASE() AND table_name = 'sales_order';
  

Partition-basierte Archivierung: DROP PARTITION ist instantan

Für Tabellen, die von Anfang an für Archivierung konzipiert werden, ist Partitionierung die eleganteste Lösung. Das Prinzip: Die Tabelle wird nach einem Zeitintervall partitioniert. Wenn alle Daten einer Partition archiviert oder gelöscht werden sollen, wird die Partition mit einem einzigen ALTER TABLE ... DROP PARTITION-Befehl entfernt. Dieser Vorgang ist atomar und nahezu instantan — unabhängig von der Zeilenzahl.

Im Vergleich: Ein DELETE FROM log_table WHERE YEAR(created_at) = 2020 auf einer Tabelle mit Millionen Zeilen kann Stunden dauern und dabei massiven Lock-Overhead erzeugen. ALTER TABLE log_table DROP PARTITION p2020 dauert Millisekunden. Das ist der entscheidende Vorteil der Partitionierungs-basierten Daten-Archivierung.

-- Create a partitioned log table for easy archiving (yearly partitions)
CREATE TABLE application_log (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at  DATE            NOT NULL,
  level       TINYINT         NOT NULL DEFAULT 0,
  message     TEXT            NOT NULL,
  context     JSON            DEFAULT NULL,
  PRIMARY KEY (id, created_at)  -- partition key must be part of primary key
) ENGINE=InnoDB
  PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
  );

-- Check partition sizes
SELECT
  partition_name,
  table_rows,
  ROUND(data_length / 1024 / 1024, 1) AS data_mb
FROM information_schema.PARTITIONS
WHERE table_schema = DATABASE()
  AND table_name   = 'application_log'
ORDER BY partition_name;

-- Archive: first copy partition data, then DROP the partition (INSTANT!)
INSERT INTO application_log_archive SELECT *, NOW() AS archived_at
  FROM application_log PARTITION (p2022);

-- Drop the entire partition — this is INSTANTANEOUS regardless of row count
ALTER TABLE application_log DROP PARTITION p2022;

-- Add a new future partition before pmax fills up
ALTER TABLE application_log
  REORGANIZE PARTITION pmax INTO (
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
  );
  

Tabellenwachstum überwachen mit information_schema

Daten-Archivierung ist kein einmaliges Ereignis, sondern ein kontinuierlicher Prozess. Um zu wissen, wann eine Tabelle wieder archiviert werden muss, braucht man eine regelmäßige Messung des Tabellenwachstums. Die Tabelle information_schema.TABLES liefert Schätzungen für Zeilenzahl und Datengröße — allerdings sind diese Werte für InnoDB erst nach einem ANALYZE TABLE aktuell.

Ein effektiver Monitoring-Ansatz ist, die Messwerte regelmäßig in eine separate Monitoring-Tabelle zu speichern und so eine historische Wachstumskurve aufzubauen. Damit lässt sich das Wachstum pro Woche oder Monat berechnen und ein Archivierungszeitplan erstellen, der rechtzeitig greift, bevor die Tabellen zu groß werden.

-- Snapshot current table sizes for the most important tables
SELECT
  table_name,
  table_rows,
  ROUND(data_length   / 1024 / 1024, 1)  AS data_mb,
  ROUND(index_length  / 1024 / 1024, 1)  AS index_mb,
  ROUND(data_free     / 1024 / 1024, 1)  AS free_mb,
  ROUND((data_length + index_length) / 1024 / 1024, 1) AS total_mb,
  update_time
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
  AND engine = 'InnoDB'
ORDER BY (data_length + index_length) DESC
LIMIT 20;

-- Estimate archiving potential per table
SELECT
  'sales_order'    AS tbl,
  COUNT(*)         AS archivable_rows,
  MIN(created_at)  AS oldest,
  MAX(created_at)  AS newest_archivable
FROM sales_order
WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR)
  AND state = 'complete'
UNION ALL
SELECT 'sales_order_item', COUNT(*), MIN(created_at), MAX(created_at)
FROM sales_order_item
WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR)
UNION ALL
SELECT 'customer_log', COUNT(*), MIN(logged_at), MAX(logged_at)
FROM customer_log
WHERE logged_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
  

Zusammenfassung

Daten-Archivierung in MySQL ist eine Betriebsnotwendigkeit für wachsende Systeme. Große Tabellen verlangsamen Abfragen, Backups und Wartungsoperationen. Die wichtigsten Strategien sind: pt-archiver für kontrolliertes chunk-weises Archivieren, DIY Chunked DELETE mit LIMIT und SLEEP für einfachere Fälle, Partition-basierte Archivierung für instantanes DROP PARTITION bei vorausgeplanten Tabellen, und regelmäßiges OPTIMIZE TABLE oder pt-osc für Defragmentierung nach großen Löschvorgängen. Magento-Shops profitieren besonders von der Archivierung alter Bestellungen, Logs und Session-Daten.

FAQ: Daten-Archivierung MySQL