Hoe u uw MariaDB/MySQL-database kunt comprimeren, defragmenteren en optimaliseren

Ja, omgaan met opgeblazen databases kan een behoorlijke ellende zijn, vooral als ze in de loop der tijd als kool groeien. Als je MySQL- of MariaDB-instantie traag aanvoelt of je schijfruimte sneller verdwijnt dan je verwacht, is het misschien tijd om eens te kijken naar trucs voor tabelcompressie en defragmentatie. Deze methoden kunnen verrassend veel ruimte vrijmaken en de prestaties verbeteren, maar er is altijd dat lastige proces van eerst een back-up maken, controleren of de configuraties kloppen en hopen dat niemand het waagt om zonder vangnet de boel volledig te vernietigen. Hier zijn een paar dingen die in de praktijk goed hebben gewerkt, na wat vallen en opstaan.

Hoe los je ruimte- en prestatieproblemen met MySQL/MariaDB op?

De strijd aangaan met de ibdata1- en ib_log-bestanden — Splits uw InnoDB-gegevens

Als je die enorme ibdata1- en ib_log-bestanden hebt die veel ruimte innemen, komt dat waarschijnlijk doordat al je InnoDB-tabellen alles in één monolithisch bestand proppen. Dat is standaardgedrag, maar het is nogal inefficiënt en maakt opruimen een nachtmerrie. In sommige configuraties krimpen deze bestanden niet automatisch, wat vervelend is. De truc is dus om MySQL te vertellen elke tabel apart op te slaan — op die manier kun je later gemakkelijker ruimte vrijmaken.

Dit is wat je moet doen:

• Maak eerst een back-up van al je databases, vooral als ze belangrijk zijn — mysqldump is daarbij een handig hulpmiddel: # mysqldump -u [username] -p [database_name] > backup.sql
• Stop de MySQL- of MariaDB-server: # sudo systemctl stop mysqlof# sudo systemctl stop mariadb
• Bewerk je configuratiebestand — dit vind je meestal op /etc/mysql/my.cnf of /etc/mysql/mariadb.conf.d/50-server.cnf. Zoek of voeg de volgende regel toe onder [mysqld]: innodb_file_per_table=1
• Verwijder de grote bestanden ibdata1 en ib_log. MySQL doet dit natuurlijk niet automatisch; u moet ze handmatig verwijderen (zorg ervoor dat u een back-up hebt!): # sudo rm /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1
• Start de server opnieuw: # sudo systemctl start mysql
• Herstel uw back-ups indien nodig en controleer vervolgens of de nieuwe tabellen in hun eigen .ibd- bestanden zijn opgeslagen. Als u geluk hebt, zullen de oude bestanden niet verder groeien.

Deze methode is nogal drastisch, maar het helpt echt om die ibdata-bestanden onder controle te houden. Op sommige systemen moet je de database mogelijk dumpen, opnieuw aanmaken en opnieuw laden om de overstap correct te laten verlopen. Daarom zijn back-ups cruciaal.

Tabelcompressie in InnoDB — Bespaar ruimte met ROW_FORMAT=COMPRESSED

Als je tabellen met veel tekst of BLOB-gegevens hebt, kan het comprimeren daarvan veel schijfruimte vrijmaken. Het nadeel? Het kan de CPU-belasting verhogen, dus het is alleen een goed idee als de CPU van je server nog niet maximaal belast is of als schijfruimte een grotere zorg is.

Ga naar je MySQL-shell:

# mysql -u root -p

Schakel over naar de database die u wilt opschonen:

# use your_database_name;

Kijk welke tafels de meeste ruimte in beslag nemen:

SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in MB" FROM information_schema. TABLES WHERE table_schema = "your_database_name" ORDER BY (data_length + index_length) DESC;

Dit laat je zien welke tabellen het grootst zijn. Kies nu een of meer tabellen om te comprimeren. Als bijvoorbeeld de tabel b_crm_event_relationser dik uitziet, voer dan het volgende commando uit:

ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;

Daarna zou de tabel kleiner moeten worden — ik heb gezien dat de grootte daalde van bijvoorbeeld 26 MB naar 11 MB, wat erg fijn is. Houd er wel rekening mee dat compressie de lees- en schrijfsnelheid tijdelijk kan vertragen, dus ga hier niet te ver mee aan de slag midden in een drukke periode.

MyISAM-tabellen — Comprimeren en opslaan

Als je met ouderwetse MyISAM-tabellen werkt, kun je die comprimeren met een tool genaamd myisampack. Dit is meer een soort zip-bestand van de tabel dan compressie binnen de database zelf.

Voer dit commando uit in je terminal — vervang het pad door de locatie van je tabel:

# myisampack -b /var/lib/mysql/test/modx_session

Dit maakt een gecomprimeerde versie van je. MYD-bestand aan, bijvoorbeeld modx_session. MYI. Je kunt de grootte voor en na de compressie controleren met het commando `du -sh`.

Zorg ervoor dat je myisampack uitvoert op het. MYD-bestand en daarna myisamchk -rq op de tabel. Het is misschien een beetje vreemd, maar dit helpt echt om die kleine tabellen te optimaliseren wanneer er weinig ruimte is.

Regelmatig onderhoud: tabellen defragmenteren en optimaliseren

Na verloop van tijd kunnen tabellen gefragmenteerd raken en vol komen te zitten met ongebruikte ruimte. Door OPTIMIZE TABLE uit te voeren, kan deze ruimte vaak worden teruggewonnen en de gegevenstoegang worden versneld.

Log in bij uw MySQL-shell en selecteer uw database:

# mysql -u root -p

Kies uw database:

# USE your_database;

En ren:

OPTIMIZE TABLE b_crm_timeline_bind, b_disk_deleted_log_v2, b_disk_object_path;

Dit zal de tabellen opnieuw opbouwen en hun grootte verkleinen als er sprake is van fragmentatie. Verwacht een uitvoer te zien die aangeeft dat het gelukt is, waarbij data_free naar nul daalt. In sommige gevallen kan er “Tabel ondersteunt geen optimalisatie” staan, waarna er wordt teruggevallen op een heropbouw en analyse. Hoe dan ook, dit zorgt er doorgaans voor dat de tabelgrootte aanzienlijk kleiner wordt en dat de tabellen sneller werken.

Wil je snel een alles-in-één oplossing? Ga dan gewoon naar:

# mysqlcheck -o --all-databases -u root -p

Het is een goede gewoonte om dit zo nu en dan te doen. Vergeet alleen niet om eerst een back-up te maken van alles, want ja, de wet van Murphy en zo.

Samenvatting

• Maak een back-up van je databases voordat je gaat experimenteren met configuraties of compressie.
• Bewerk /etc/mysql/my.cnf om dit in te schakelen innodb_file_per_table=1.
• Verwijder de oude ibdata1- en ib_log-bestanden na het stoppen van de server; het aanmaken van nieuwe bestanden houdt de groei onder controle.
• Gebruik dit ALTER TABLE...ROW_FORMAT=COMPRESSEDspaarzaam bij grote tekstblokken of BLOB-tabellen om ruimte te besparen.
• Comprimeer MyISAM-tabellen met myisampack en voer myisamchk -rq uit.
• Voer periodiek OPTIMIZE TABLE of mysqlcheck -o uit om te defragmenteren.

Samenvatting

Door deze tips uit te proberen, kunt u uw database soepeler laten draaien en kostbare schijfruimte vrijmaken, vooral bij het beheren van oude tabellen of groeiende projecten. Het vergt wel wat werk, maar de winst in snelheid en ruimte is het zeker waard. Zorg er wel voor dat u een back-up maakt, de configuraties controleert en vergeet de cronjob niet om alles in de gaten te houden. Hopelijk bespaart dit iemand een paar uur, want databases die soepeler draaien, maken iedereen blijer.