Item Edit triggers 1000s of identical MySQL queries

Hello friends,

I am developing a site using Omeka-S 3.2.3 . I’ve noticed up to a minute of lag-time when bringing up the admin New Item or Edit Item screens.

Reading some of the other topics here about slow performance turned me on to the usefulenss of enabling the general log as a table in MySql/Mariadb. This can be done by typing a couple of SQL commands into Adminer or other MSQL front end. The general_log can be piped to a general_log table in the mysql database, which records all of the queries that are processed by the database server.

In my case, I see that the following query is being issued in identical form more than 1000 times while Omeka-S is trying to load the Edit Item page.

SELECT t0.id AS id_1, t0.email AS email_2, t0.name AS name_3, t0.created AS created_4, t0.modified AS modified_5, t0.password_hash AS password_hash_6, t0.role AS role_7, t0.is_active AS is_active_8 FROM user t0 WHERE t0.id = 3 AND t0.is_active = 1 LIMIT 1

It seems to have something to do with authentication. I have not gotten around to hooking up my Omeka to SMPT yet, which might be part of the problem.

I’ve tried searching through the Application/src directory for strings that may be related to this, without any luck.

Any help for me today? Thanks .

You can log queries to a file from the Omeka side of things too, it’s an option in the database config. But it should be the same information as you get from logging on the database server side.

The query you’re showing there is for retrieving a user’s data… it’s odd for a few reasons that it would be so repeated, for one, the database library we use tends to cache these kinds of single lookups so they aren’t repeated even if we request the same object again.

My guess would be that this is either related to a module (which you could check for by disabling modules and seeing if the poor performance remains), or otherwise to something scaling in a way we’re not expecting. Does your site have a very large number of users, item sets, things like that?

Thank you for the tip, John.

Uninstalling all modules reduces the lag time substantially. Yet the redundant query quoted above is still generated lots (but fewer times)

With all modules uninstalled the following

  • Operations Generates
  • List items: 350 queries
  • Edit an Item: 200 queries
    Of these queries it looks like 90% of them are duplicates of the query quoted in the original post, above.

I don’t have an unusual number of users. Only one at this point, Admin, which owns everything and is SuperUser.

There are only about 25 items, 10 of which make reference to several related items.

It does look like some thing is scaling in an odd way.

  • When the Mapping module installed, the Edit Items lag time goes from about 1 second to 3.
  • Adding the File Sideload module increases the lag time to about 8 seconds.
  • Adding the Value Suggest module causes the edit-item lag to go up to 30 seconds
  • Adding the custom ontology module increases the lag to 45 seconds.

None of these modules seems to increase the number of repetitive queries, if the log is cleared between trials.

Maybe the lag is actually an issue with disk access to my Modules folder, which is mounted as a volume.

The repeated queries continue to be weird, but maybe not the crux of the lag issue.

I am going to try rebuilding my dev container from scratch and placing the modules in the container or mounded from inside the WSL2 host.

Will update with results. Any further insights would be appreciated.

It looks like there is a regression in performance related to accessing the currently logged-in user in version 3.2.3: as a result of a bug fix, the query to get the current user is re-performed every time some piece of code is looking at the current user (to check permissions or similar). It’s a very fast query so the actual speed penalty is highly variable depending on your setup, but it does lead to a very large number of redundant queries, as you report.

I’ve written a fix that should eliminate the unnecessary additional queries: it will be released with the upcoming major version of Omeka. I’m undecided at the moment as to whether we’ll backport the fix to a 3.2.4 release. You can apply that linked patch to your install if you’re interested in immediate results, or to confirm the problem is resolved.

Epilog: The very slow loading of Item Edit pages seems to have been caused by an inefficient “bind mount” of my modules folder (and others) in my windows - WSL2 docker set-up. I followed docker’s advice for setting up a volume, through Docker Desktop, and mounting it as a “volume” in my docker-compose-yml file.

Docker volumes are exposed on the windows file explorer at:
\\wsl$\docker-desktop-data\version-pack-data\community\docker\volumes\
So they can be populated and managed from the windows side. Apparently, the file-io is more than 10x faster from the perspective of the docker machine…

With all of the necessary modules loaded, the lag for loading an Item Edit page, is about 1 second.

Also, I should clarify that the claim in the subject line that this operation launches thousands of identical queries is an overestimate. It is probably more like 500. Nevertheless, as John says, this does not seem to slow tings down with my tightly-coupled Omeka-S<->MariaDB set-up.

I very much appreciate your support, John.

–pbc