I’ve been experimenting with various combinations of search terms. Searches that require more than a couple of seconds cause the UI to hang. Some searches don’t return control for a minute or more and interacting with the UI causes “not responding” to appear on the title bar. If the search takes longer than 60 seconds, Windows reports that the “application appears to be frozen” and offers to kill it.
If audio is being rendered in a player, it continues to do so while the UI is unresponsive. Once the search concludes, the UI is immediately updated to reflect the current playout state.
On one occasion, I left it for 20 minutes and it hadn’t completed the search.
I’ve tried many combinations of search terms and fields, with and without advanced turned on. Most return results within a handful of seconds. Several take a long time. I’ve found one that never seems to complete.
Our lab setup comprises a single playout station (a mac mini running Bootcamp+Win7) and a PostgreSQL DB server (Ubuntu server 16.04). Client and server both have 1Gbps NICs and are connected via 1Gbps ports on a managed switch. There is negligible network traffic. The server is effectively idle while the search is completing.
I’ve stripped the playout system back to its out-of-the-box state. The config directory contains only database.ini and default.mlb.
The DB has about 85,000 items. I repeated the experiments after vacuuming and subsequently reindexing the database. These maintenance tasks didn’t change the outcome.
This is something of a show-stopper and I welcome any advice.
I’m finding in the database window, even clicking on a folder, say 1500 items, it’s slow. Takes 2-4 secs to fill out, even see the progress bar moving across but on v5 it was snappy. The DB access (local) just seems slower.
The main issue is that the UI locks up and can produce a scary-looking dialog box that has an option to kill the application. A lesser but still problematic issue is the long search times.
As you know, I have 200 volunteer broadcasters who we can coach on appropriate use of search. However coaching will not completely mitigate the problem.
Unfortunately (for us), a search that never returns is the single term “australian”. As our broadcasters are required to play at least 35% local content, this term will be used a lot.
Today, the term mostly (95%) appears in the genre attribute. This is a legacy from our old system.
I’m experimenting with a tri-valued (local/australian/overseas) custom attribute that I’m hopeful will allow us to use genre for its intended purpose. The other 5%are using this attribute.
I’ve been playing with SQL queries on the command line. I’ve written a query that simulates the way I’ve configured the search parameters on the mAirList UI context menu. It returns the “australian” result set (~20,000 rows) in less than 5 seconds.
The problem is that the data is distributed among multiple tables. For example, attributes and cue markers are in separate tables.
mAirList does not do an SQL join (that would produce an exponential number of rows), but does the join in memory instead, so only a single query per table is used.
The other problem is that we are doing full text search here (LIKE ‘%term%’), and no SQL indices can be used then. Perhaps we should make a switch to turn on/off full text search? This should speed things up. Only values that match the exact term, or at least start with the search term, would be returned that. (LIKE ‘term%’ should be able to leverage indices.)
I just uploaded snapshot 3638 of mAirList 6.0 which adds a new “Full-text search” option to the right-click menu of the search field. Disabling this will only return results where the search text appears at the beginning of the artist/title/etc. field, so much fewer results in general.
Those who use PostgreSQL 9.1 or later can also try to run the following SQL commands on their database:
CREATE EXTENSION pg_trgm;
CREATE INDEX trgm_idx_items_artist ON items USING gin (artist gin_trgm_ops);
CREATE INDEX trgm_idx_items_title ON items USING gin (title gin_trgm_ops);
CREATE INDEX trgm_idx_items_comment ON items USING gin (comment gin_trgm_ops);
CREATE INDEX trgm_idx_items_externalid ON items USING gin (externalid gin_trgm_ops);
CREATE INDEX trgm_idx_item_attributes_value ON item_attributes USING gin (value gin_trgm_ops);
My test database isn’t too huge, but using the PostgreSQL EXPLAIN ANALYZE command, I could see speed improvements up to factor 50. Search string must be at least 3 characters long though.