Afbeeldingen opnieuw toewijzen (project 2021)
Het probleem
Site example.com heeft 23.000 producten: 800 zogenaamde Universals en 15.000 zogenaamde Specials. Eigenlijk zijn er maar 800 echte pr: De Specials zijn rebranded versies van deze producten. De afbeeldingen zijn echter dezelfde.
Oorspronkelijk waren voor alle 23.000 producten twee afzonderlijke SEO-geoptimaliseerde afbeeldingen aanwezig. Vanwege de verschillende presets, waren dit bij elkaar zo'n 250.000 afbeeldingen. De meeste daarvan stonden in één WordPress-upload-map, omdat het vullen van deze site geautomatiseerd ging en dus allemaal in dezelfde maand.
Dit bracht een paar problemen met zich mee:
Opslagruimte
De site was zo'n 11GB groot (zie schermafdruk) en dat was verdraaid onhandig:
- Snel effe een kloon maken om iets te testen, is lastig
- Up- & downloaden kost veel tijd. Denk hierbij aan uren. Vaak doe ik dat 's nachts. Omdat dit zoveel tijd kost, is het proces instabiel: Er kan gemakkelijk iets misgaan
- Backups maken is problematisch want ruimtegebruik & instabiliteit van het proces
- Webservers lijken standaard niet ingericht te zijn op zulke grote sites: Er is dus te weinig ruimte om daadkrachtig te werk te gaan
- Relatief kostbaar: Hosten van zulke grote sites is niet gratis
- Deze site willen we in de loop van 2021 een flink aantal keer klonen & vertalen. Dan is de omvang van 10GB een drempel om de site te klonen naar relatief kleine markten (bv. een Luxemburgse, Litouwse, Zwiters-Franse of Ierse site)
- Tijdens het importproces werkte ik met een eigen map met afbeeldingen binnen WordPress. Die map was ook een handjevol GB groot en dat was niet goed te beheren - Lastig om daadkrachtig te werk te gaan.
Onwerkbare upload-map
Bij elkaar zijn er 272.000 afbeeldingen in wp-content/uploads
, waaronder 263.000 bestanden in één map. Dat is onwerkbaar: In Nautilus kan ik de inhoud van deze map niet opvragen: De applicatie crasht of bevriest.
In Bash werkt het commando ls
wel, maar bv. alle bestanden hernoemen, verwijderen of verplaatsen, werkt niet: Als je wildcards gebruikt, word het eigenlijke commando geëxpandeerd om alle casussen te dekken. Er is te weinig geheugenruimte vooor expanderen. Dat geeft een argument too long-foutmelding (zie ook: cp, rsync). Dit probleem kwam later terug bij het verwijderen van de overtollige afbeeldingen (zie verderop in dit artikel). Vermoedelijk kun je die geheugenlimiet aanpassen, maar of dat wijsheid is?
Database-grootte?
Interessante bijkomstigheid: Database-dumps zijn geslonken van 480MB naar 376MB. Da's 22% kleiner. Ongetwijfeld heeft dat in ieder geval theoretisch invloed op de performance: Tabellen wp_postmeta
en wp_posts
zullen nu flink kleiner zijn en het staat me bij dat die niet al te geoptimaliseerd waren qua indexes. Of dit in de praktijk invloed heeft? Ik heb het nog niet gemerkt.
Business case?
Waarschijnlijk klopt de business case niet, tenzij het om sites (klonen) zou gaan met heel veel omzet: De toegevoegde waarde van de SEO-optimalisatie om per product twee unieke afbeeldingen te hebben, weegt waarschijnlijk niet op tegen de kosten.
Ontwikkeling
Redim-db vs. redim-php
Aanvankelijk heb ik geprobeerd dit 100% op database-niveau op te lossen (muv. Bash-script om overtollige afbeeldingen te verwijderen): Redim-db - Reduce Images - Database-niveau. Dat werkte niet: Het werd te complex. Conclusie: Gebruik de PHP-API, zodat allerlei details op een transparante manier geregeld worden en ik kan focussen op de hoofdlijnen. Dat subproject heb ik redim-php genoemd - Reduce images - PHP-API.
De rest van dit hoofdstuk gaat uitsluitend over redim-php:
Repeterende scripts vs. loops
Ik wordt er doorgaans niet zo blij van om in PHP loops te programmeren om data uit een database te peuteren. Daarom heb ik er voor gekozen om in SQL 'repeterende' PHP-scripts te genereren die alle bewerkingen verrichten zonder loop. Zie de eigenlijke scripts hier verderop. Dan snap je het in één keer.
Dit bleek een goede keus te zijn: Ik ben niet al te geoefend in PHP (en dat wil ik zo houden). Ik besteed daarom relatief veel tijd aan debuggen en m'n code is foutgevoelig. Daarom heel erg goed om het zo simpel te houden.
Zeven iteraties
Het proces heb ik voor zeven sites doorlopen: Vier testsites en drie productiesites. Uiteraard begon ik met de testsites en eindigde ik met de meest kritische productiesite. Deze iteratieve aanpak leverde een paar boeiende inzichten op, waaronder een paar dingen die erg voor de hand liggen, maar waarvan ik me niet bewust was aan het begin van het proces:
- CloudWays vs. eigen server: Het was verdraaid ingewikkeld om rechtstreeks op CloudWays-databases te werken. Ik kon bv. de scripts niet exporteren naar een bestand (dat heb ik via kopiëren-en-plakken opgelost). Ze waren ook nog eens verbazend traag: Eén van de laatste queries heb ik gesplitst in een handjevol queries, alleen maar omdat CloudWays er anders niet mee overweg kon. Conclusie: Voor ontwikkelwerk gaat er niets boven een machine waar ik volledige controle over heb
- Doe dit niet op een productieserver: Bijkomstige conclusie: Doe dit soort dingen liever niet binnen een productieomgeving: Als ik de naam van een database in een script vergat bij te werken, kon ik een productie-site al om zeep helpen (is niet gebeurd!)
- Doe het offline: Pas vrij laat bedacht ik me, dat ik alleen maar een redelijk actuele kopie van de database van een WordPress-site nodig heb. Zolang er geen producten worden toegevoegd of afbeeldingen worden toegevoegd of verwijderd, heb ik geen nieuwere versie van die database nodig. En die database hoeft ook niet te worden teruggezet. Vaak is het probleem bij onderhoudswerkzaamheden, dat er tussentijds orders worden geplaatst in zo'n database. Dat speelde hier totaal geen rol. Door te werken op een offline-kopie van zo'n site, werd het hele proces aanzienlijk minder kritisch
- Doe het op m'n laptop: De productie-database is zo'n 480MB groot. Gecomprimeerd bleef daar maar 25MB van over. Dat was dus heel gemakkelijk over te fietsen naar m'n laptop. En al vind ik m'n ontwikkelserver een prima apparaat (vooral voor processen die uren duren), niets werkt zo prettig als m'n laptop
- Omkeerbaar: Begin met handelingen die omkeerbaar zijn en het minst kritisch zijn. Dat klinkt zo logisch, maar dat is niet hoe de eerste vijf versies van het script in elkaar zaten.
- Test, test, test: Na de tweede iteratie zat alle SQL-code in één bestand van zo'n 500 regels (onderaan dit artikel toegevoegd). Dat begon met allerlei test-select-queries. Welliswaar was het na een tijdje lastig om nog te begrijpen waar die precies over gingen, maar dat legde ik uit in commentaarregels. Het voelde als de checklist die piloten afwerken voordat ze gaan vliegen.
Vier scripts
Het proces resulteert in vier scripts (met ietwat verschillende namen per iteratie):
delete_specials_image.php
reassign-thumbnail.php
reassign-gallery.php
delete-image-files
.
Deze scripts werden vervolgens geüpload naar de betreffende webserver, en daar geëxecuteerd.
Alle bestanden bij elkaar
Zie schermafdruk. Wat deze bestanden inhouden:
100-example.com-2021.05.07.sql.gz
Database van de productiesite. Die comprimeer ik server-sided: Ca. 400MB → 36MB. Uiteraard heb ik 'm uitgepakt en geïmporteerd. Die uitgepakte versie heb ik weggegooid.
110-sku_b3.sql
Losse tabel met sku's voor Universals en Specials. Die heb ik nodig om afbeeldingen opnieuw toe te wijzen aan producten. Dit is een partiële export uit een datawarehouse-tabel. Deze tabel importeer ik in de locale instantie van de website-database. Die hele database gooi ik naderhand weg, dus het is prima om z'n 'vreemde' tabel daarin in te voegen.
120-all.sql
Alle SQL-code zit in één bestand.
130 - 160
Verschillende resulterende scripts - Zie elders.
170-all-server-scripts.zip
Uploaden naar CloudWays-server lukte niet via Nautilus. Via de command line vind ik nogal gedoe. Vandaar dat ik ze had samengevoegd tot één archiefbestand voordat ik ze uploade.
ReadMe.txt
Mappen & bestanden zijn mijn primaire documentatie- & opslagsysteem. Dus niet Trello of wiki's (dit artikel is een uitzondering), maar gewoon mappen en bestanden. Dit bestand bevat documentatie. Het is een txt-bestand dus het is te scannen met de gebruikelijke tools om op Linux iets terug te vinden.
delete_specials_image.php - wp_delete_attachment
Gebruik PHP-API-call wp_delete_attachment
om de associatie tussen Specials en de bijbehorende unieke afbeeldingen ongedaan te maken. Ik dacht dat hiermee de afbeeldingen ook worden verwijderd uit het bestandssysteem, maar dat lijkt niet het geval te zijn.
Waren er per site vier scripts. Dit script (delete_specials_image.php
) was verreweg het meest rekenintensieve: Van een half uur tot een uur rekentijd.
Dit script (delete_specials_image.php
) was zo'n 30.000 regels lang. De eerste paar regels:
<?php require_once('../../wp-load.php'); wp_delete_attachment(20652, True); wp_delete_attachment(20653, True); wp_delete_attachment(20654, True); wp_delete_attachment(20655, True); wp_delete_attachment(20656, True); wp_delete_attachment(20657, True); wp_delete_attachment(20658, True);
In een WordPress-installatie heb ik een eigen boomstructuur. Vandaar de ../../
in het require-commando
reassign-thumbnail.php - set_post_thumbnail
De eerste regels van de 14.871 regels van bestand 140-reassign-thumbnail.php
:
<?php require_once('../../wp-load.php'); set_post_thumbnail(3897, 19294); set_post_thumbnail(3898, 19294); set_post_thumbnail(3899, 19294); set_post_thumbnail(3900, 19294); set_post_thumbnail(3901, 19294); set_post_thumbnail(3925, 19294); set_post_thumbnail(3926, 19296); set_post_thumbnail(3927, 19298); set_post_thumbnail(3928, 19298);
Hieraan kun je goed zien dat diverse posts (de eerste numerieke kolom) dezelfde thumbnail krijgen (de tweede numerieke kolom).
reassign-gallery.php - update_post_meta
De eerste regels van de 14.871 reges van 150-reassign-gallery.php
. Dit is vergelijkbaar met het vorige bestand:
<?php require_once('../../wp-load.php'); update_post_meta(3897, '_product_image_gallery', 19295); update_post_meta(3898, '_product_image_gallery', 19295); update_post_meta(3899, '_product_image_gallery', 19295); update_post_meta(3900, '_product_image_gallery', 19295); update_post_meta(3901, '_product_image_gallery', 19295); update_post_meta(3902, '_product_image_gallery', 19295); update_post_meta(3903, '_product_image_gallery', 19295); update_post_meta(3904, '_product_image_gallery', 19295); update_post_meta(3905, '_product_image_gallery', 19295);
delete-image-files.sh
Dit is een shell-script en het was hetzelfde voor alle iteraties. Hier zie je hoe ik de argument too long-error heb opgelost: Zorg dat expansies van comando's met wildcards beperkt blijven:
#!/bin/bash cd ../../wp-content/uploads/2020/05 rm -v *-cas-00* rm -v *-cas-01* rm -v *-cas-02* rm -v *-cas-03-0* rm -v *-cas-03-1* rm -v *-cas-03-2* rm -v *-cas-03-3* rm -v *-cas-03-4* rm -v *-cas-03-5* rm -v *-cas-03-6* rm -v *-cas-03-7* rm -v *-cas-03-8* rm -v *-cas-03-9* rm -v *-cas-03* rm -v *-cas-04* rm -v *-cas-05* rm -v *-cas-06* rm -v *-cas-07-0* rm -v *-cas-07-1* rm -v *-cas-07-2* rm -v *-cas-07-3* rm -v *-cas-07-4* rm -v *-cas-07-5* rm -v *-cas-07-6* rm -v *-cas-07-7* rm -v *-cas-07-8* rm -v *-cas-07-9* rm -v *-cas-07* rm -v *-cas-08* rm -v *-cas-09* rm -v *-cas-10* rm -v *-cas-11* rm -v *-cas-12* rm -v *-cas-13-0* rm -v *-cas-13-1* rm -v *-cas-13-2* rm -v *-cas-13-3* rm -v *-cas-13-4* rm -v *-cas-13-5* rm -v *-cas-13-6* rm -v *-cas-13-7* rm -v *-cas-13-8* rm -v *-cas-13-9* rm -v *-cas-13* rm -v *-cas-14* rm -v *-cas-15* rm -v *-cas-16* rm -v *-cas-17-0* rm -v *-cas-17-1* rm -v *-cas-17-2* rm -v *-cas-17-3* rm -v *-cas-17-4* rm -v *-cas-17-5* rm -v *-cas-17-6* rm -v *-cas-17-7* rm -v *-cas-17-8* rm -v *-cas-17-9* rm -v *-cas-17* rm -v *-cas-18* rm -v *-cas-19*
Tijd
Ik heb dit niet als een appart project bijgehouden, maar ik vermoed dat het zo'n 40h heeft gekost. We moeten flink wat sites klonen & vertalen, om dit terug te verdienen.
Resultaten
De nieuwe situaties naast de eerdere afbeelding uit dit artikel: De verschillen zijn indrukwekkend:
Gegevensopslag
Aantal afbeelding-bestanden
Database-omvang
De database is zo'n 22% kleiner geworden. Het verschil zal vermoedelijk vooral in tabellen wp_posts
en wp_postmeta
liggen. Ik ben benieuwd of dat een impact heeft op de performance.
Aanbevelingen volgende keer
Enerzijds was dit een ongebruikelijk complex en abstract project. Anderszijds ging het uiteindelijk moeiteloos. De belangrijkste aanbevelingen voor een volgend vergelijkbaar proces staan al eerder vermeld. Desalniettemin voor mezelf nog een paar dingen opnieuw benoemen:
- Moet je écht op een productieomgeving werken?
- Iteratief werken werkt super
- Het project is pas af na afronding. In dit geval is dat het schrijven van dit artikel. Doe dat gelijk, want anders komt het nooit af.
Script
En nu het klapstuk: Het eigenlijke SQL-script, vrijwel onaangepast:
# # Redim-php - knl - Laptop ################################################################################ # # * This is clearly the way to do this: Work on a copy of the production db # on my laptop and copy the resulting script files to the server for # execution # # ################################################################################ # Select database ################################################################################ # use knl; ################################################################################ # Test (1): Specials-IDs & b3-sku's ################################################################################ # # * This script: Connect Specials-post-IDs with b31- & b32-skus # * Execution takes about half an hour on CloudWays-server # * Execution takes a couple of seconds on laptop # * Use the collation-changing scripts for fixing errors. # # # What to look for ######################################## # # * Errors? # * Collation errors? Maybe use the scripts below # * Is table "sku_b3" available? # * Are there about 15.336 records? # # # Change collations wp_postmeta ######################################## # # (only when needed) # # alter table # wp_postmeta # change column # meta_key # meta_key varchar(255) character set 'utf8mb4' collate 'utf8mb4_0900_ai_ci' null default null, # change column # meta_value # meta_value longtext character set 'utf8mb4' collate 'utf8mb4_0900_ai_ci' null default null; # # Change collations wp_posts # ######################################## # # # # (only when needed) # # # alter table # wp_posts # change column # guid # guid varchar(255) character set 'utf8mb4' collate 'utf8mb4_0900_ai_ci' not null default ''; # # Actual script # ######################################## # # # select # ID as special_id, # post_title, # wp_postmeta.meta_value as sku_b32, # sku_b3.sku_b31 as sku_b31 # from # wp_posts # join # wp_postmeta # on # wp_posts.ID = wp_postmeta.post_id # and # wp_postmeta.meta_key = "_sku" # join # sku_b3 # on # wp_postmeta.meta_value = sku_b3.sku_b32 # where # wp_posts.post_type like 'product' # and # wp_posts.post_title like "% cas-%"; ################################################################################ # Test (2): Extract b31-sku's from image files ################################################################################ # # * We need to know what images correspond with what b31 (Universal) skus. # This is nowhere explicitly stated, so we'll filter it out from various # image fields # * So far, we've always used the b31-sku's from field sku_guid # * Execution is almost instantaneous # # # What to look for ######################################## # # * Check if field sku_guid is correct everywhere: That it is a reliable source # for Specials-skus. When stuff hasn't been stripped adequately from sku_guid, # *add* extra stripping functions. *Don't* remove existing functions: The more # such functions, the more flexible and robust this becomes # * Column sku_guid should *only* contain b31-skus. # # # Actual script ######################################## # # select # ID, # substring_index(substring_index(post_content, "SKU: ",-1), " - ",1) as sku_content, # substring_index(substring_index(post_title, "SKU: ",-1) , " - ",1) as sku_title, # substring_index(substring_index(post_excerpt, "SKU: ",-1), " - ",1) as sku_excerpt, # substring_index(substring_index(substring_index(post_name, "sku-",-1), "-en-vente",1), "-example",1) as sku_name, # guid, # substring_index(substring_index(substring_index(substring_index(substring_index(guid, "sku-",-1), "-te-koop",1), "-example",1), "Sblubs-",-1), "-Widget",1) as sku_guid # from # wp_posts # where # post_type = "attachment" # and # post_mime_type like "image%" # and # ( # post_content like "%SKU: ca-%" # or # post_title like "%SKU: ca-%" # or # post_excerpt like "%SKU: ca-%" # or # post_name like "%-ca-%" # or # guid like "%sku-ca-%" # ) # and # # # # This line is needed for filtering out Widget Specials with "ca" in their # # product type # # # guid not like "%-cas-%" # order by # length(sku_guid) desc, # sku_guid asc; ################################################################################ # Test (3): Image delete ################################################################################ # # Commands for deleting attachments of Specials # # # What to look for ######################################## # # * No errors # * All rows should be regular - Without any NULL values # * Column "product_title" should contain Specials (%-cas-%) # * About 30.000 rows # * Column "php_command" can contain NULL values # # # Actual script ######################################## # # select # wp_posts.ID as product_id, # wp_posts.post_title as product_title, # concat # ( # "wp_delete_attachment(", meta_value, ", True);" # ) as php_command # from # wp_postmeta # join # wp_posts # on # wp_postmeta.post_id = wp_posts.ID # where # wp_posts.post_type like 'product' # and # wp_posts.post_title like "% cas-%" # and # ( # wp_postmeta.meta_key like '_thumbnail_id' # or # wp_postmeta.meta_key like '_product_image_gallery' # ); ################################################################################ # Create table "reassign_00" ################################################################################ # # * This table is based on wp_posts. It contains the post-ids of Universal # images + b31-skus of these images # * Reason for creating this table: Doing this on the fly as part of # reassign_01, turns out to be too complicated # # # Actual script ######################################## # # drop table if exists reassign_00; # create temporary table # reassign_00 # ( # image_id bigint(20), # image_post_title varchar(255), # image_post_name varchar(255), # image_guid varchar(255), # image_sku_b31 varchar(511) # ); # insert ignore into # reassign_00 # ( # image_id, # image_post_title, # image_post_name, # image_guid # ) # select # ID, # post_title, # post_name, # guid # from # wp_posts # where # post_type = "attachment" # and # post_mime_type like "image%" # and # guid like "%-ca-%" # and # post_title like "%ca-%"; # # Extract sku_b31 from guid - CHANGE # ######################################## # # # update # reassign_00 # set # image_sku_b31 # = # substring_index(substring_index(image_guid, "-example",1), "-sku-",-1); # select * from reassign_00 order by length(image_guid) desc; ################################################################################ # Create table "reassign_01" ################################################################################ # # * Testing is done. Here comes the real work, starting with creating temporary # table "reassign_01" # * It is assumed, that table "sku_b3" has been incorporated in the WordPress # database # * Execution on srv23 took between 5 & 10 minutes # * On CloudWays, I stopped execution after 54 minutes. Strange that there # wasn't a timeout. There also wasn't any result # * This code has been rewritten because of these time-outs: Queries have # been separated. # # # Actual script ######################################## # # Drop existing table ######################################## # drop table if exists reassign_01; # Define table ######################################## # create temporary table reassign_01 ( special_id bigint(20), special_title text, special_sku_b32 varchar(40), special_sku_b31 varchar(40), image_id bigint(20), index(special_id), index(special_sku_b31), index(special_sku_b32) ); # Insert Specials-data from wp_posts ######################################## # # Execution time: 0,17s # insert into reassign_01 ( special_id, special_title ) select wp_posts.ID as special_id, wp_posts.post_title as special_title from wp_posts where wp_posts.post_type like 'product' and wp_posts.post_title like "% cas-%"; # Insert b32-skus from wp_postmeta ######################################## # # Almost no execution time # update reassign_01 join wp_postmeta on reassign_01.special_id = wp_postmeta.post_id set reassign_01.special_sku_b32 = wp_postmeta.meta_value where wp_postmeta.meta_key = "_sku"; # Insert b31-skus from sku_b3 ######################################## # # Execution time: 0,5s # update reassign_01 join sku_b3 on reassign_01.special_sku_b32 = sku_b3.sku_b32 set special_sku_b31 = sku_b3.sku_b31; # Universal image_id from reassign_00 ######################################## # update reassign_01 join reassign_00 on reassign_01.special_sku_b31 = reassign_00.image_sku_b31 set reassign_01.image_id = reassign_00.image_id; # Check ######################################## # select * from reassign_01; ################################################################################ # Create table "reassign_02" ################################################################################ # # drop table if exists reassign_02; # create temporary table reassign_02 # select distinct # special_id, # special_title, # special_sku_b32, # special_sku_b31, # image_id as thumbnail_id, # image_id+1 as gallery_image_id, # concat("set_post_thumbnail(", special_id, ", ", image_id, ");") as php_thumbnail, # concat("update_post_meta(", special_id, ", '_product_image_gallery', ", image_id+1, ");") as php_gallery # from # reassign_01 # group by # special_id; # select * from reassign_02; ################################################################################ # Export: Delete-file ################################################################################ # # * Exporting seems to work the same on server dvb7 as on my laptop: In # directory "/tmp" with mysql being the owner # * Executed directly from within "/tmp" # * CHANGE path in first line select statement # # # Actual script ######################################## # drop table if exists delete_specials_image; create temporary table delete_specials_image select "<?php require_once('../../wp-load.php'); " as php_command union select concat ( "wp_delete_attachment(", meta_value, ", True);" ) as php_command from wp_postmeta join wp_posts on wp_postmeta.post_id = wp_posts.ID where wp_posts.post_type like 'product' and wp_posts.post_title like "% cas-%" and ( wp_postmeta.meta_key like '_thumbnail_id' or wp_postmeta.meta_key like '_product_image_gallery' ) and meta_value is not null; # select * from delete_specials_image; # Write to file ######################################## # select * from delete_specials_image order by php_command into outfile '/tmp/delete_specials_image.php' character set utf8 fields terminated by '\t' escaped by '' lines terminated by '\n'; ################################################################################ # Export: thumbnail-script ################################################################################ # select "<?php require_once('../../wp-load.php'); " as php_command union select php_thumbnail from reassign_02 where php_thumbnail is not null into outfile '/tmp/reassign-thumbnail.php' character set utf8 fields terminated by '\t' escaped by '' lines terminated by '\n'; ################################################################################ # Export: gallery-script ################################################################################ # select "<?php require_once('../../wp-load.php'); " as php_command union select php_gallery from reassign_02 where php_gallery is not null into outfile '/tmp/reassign-gallery.php' character set utf8 fields terminated by '\t' escaped by '' lines terminated by '\n';
Zie ook
- Afbeeldingen koppelen met producten (PHP-API)
- cp - argument too long-foutmelding
- Productafbeeldingen toevoegen (Script, PHP-API)
- Productafbeeldingen in bulk verwijderen (PHP-API)
- rsync - argument too long-foutmelding