Category imports time out on production: every file-name lookup reads the whole entries table #650

Activity

1. lgelauff commented on Oct 5, 2026

   

   lgelauff

   on Oct 5, 2026

   CollaboratorAuthor

   More actions

   Tested on montage-dev with production's code and data: master cb50599 (what production runs) on the shuffled copy of production from #652, whose entries table has the same indexes as production (no index on name). Imports run in the request, as on production.

   Import	Files	Without ix_entries_name	With ix_entries_name
   Category of the Italy historic villas campaign	~6,000	request killed after 31 s, nothing saved	6,034 files in 3 s
   Category of the Russia campaign	15,676		≤ 13 s
   Images from Wiki Loves Monuments 2026 in Italy	26,428 (all new)		18.2 s

   The index was added with

   SET SESSION lock_wait_timeout = 10;
   ALTER TABLE entries ADD INDEX IF NOT EXISTS ix_entries_name (name), ALGORITHM=INPLACE, LOCK=NONE;

   which took seconds on the 2M-row table, without blocking reads or writes.

   Conclusion: restoring the non-unique ix_entries_name on production is enough to unblock the imports that time out now (the Italy villas and Russia campaigns), without a code change or deploy.

   I do see problems with the fact that files get renamed, encoding is not perfect, and other potential problems with using a name as a lookup.
2. lgelauff

   added a commit that references this issue on Oct 10, 2026

   fix(#510): a name taken by another file in Montage is caught by the c…

   ...

   a074aac
3. lgelauff commented on Oct 10, 2026

   

   lgelauff

   on Oct 10, 2026

   CollaboratorAuthor

   More actions

   Update (2026-10-10)

   The timeouts are gone for current campaigns. Since #654 (on production since 2026-10-05), imports into campaigns that open on or after 2026-06-01 look existing entries up by file_id (indexed), not by name. The WLM 2026 imports after that went through, for example 15,667 files for Russia and 6,071 for Italy historic. The timeouts of 2026-10-04 (at least ten import requests killed after 30 s) all fell before that deploy.

   What is still open:

   • Campaigns that open before 2026-06-01 still use the name lookup without an index, so a large import there still times out. A coordinator worked around it by changing the campaign's open date. Check first-round imports before importing; save a first round with its files (#510, #509, #447) #656 (Support file_id column in CSV imports as alternative to filename lookup #510) removes that cutoff: every checked import then looks entries up by file_id.
   • The index on entries.name is still missing on production (checked today). The non-unique index tested on montage-dev (see the comment above: seconds to add, no locking) would also speed up the name lookups that remain, such as files imported before 2026-05-31 that have no file_id yet. That is no longer urgent, but it is cheap.
   • Rows without a file_id: a file imported before 2026-05-31 gets a second entries row when it is imported again, because it cannot be found by file_id. The backfill script for Backfill file_id for entries imported before #505 migration #513 fills file_id for those rows; it is ready for a first run on montage-dev.
   • The 43,993 exact duplicate names and the choice of a unique key stay with Two different files whose names differ only in accents or case break category imports (unicode_ci unique index on entries.name) #645 and Backfill file_id for entries imported before #505 migration #513.

   Side effect of 2026-10-04, now fixed: each timed-out import left an empty round behind, which blocked creating a new one. Coordinators cancelled them. One campaign still has an empty paused round. #656 saves a first round and its files together, so this cannot happen again.

   Drafted by Claude (Claude Code)