![]() |
![]() |
![]() |
![]() |
The MME's time is a 64-bit, internally-derived value that is guaranteed to be monotonically increasing, even across system restarts. This value is guaranteed on systems with or without a Real-Time Clock, and on systems on which the real-time is changed forward or backward.
This behavior permits time-based comparisons of entries in the database with other database entries, such as, for example, the lastseen and last_sync fields in the mediastores table to determine if a mediastore requires resynchronization.
The table below lists MME database table columns (fields) that use the MME's internally derived time. These fields can be compared to determine the relative sequence of events, as in the example above.
Table | Columns (Fields) |
---|---|
folders | |
library | |
mediastores | lastseen and last_sync |
![]() |
For systems that do not have a stable real-time clock, you should enable the <TimebaseSet> configuration element. This option causes the MME to run a routine at startup to adjust its internal timebase so that all time values used in the database increase monotonically. See “Database time base” in the MME Configuration Guide chapter Configuring Database Behavior, and mme_timebase_set() in the MME API Library Reference.. |
Database deadlock issues have been observed on some MME projects. The causes for these issues have been identified, and the solutions are described below:
Different database file attached orders for QDB and an external SQLite client result in different locking orders, which cause database deadlocks.
To prevent database deadlocks caused by different database file attached orders, ensure that your projects lock databases in the same order as they are attached:
If you don't have an mme_custom table, use this order:
![]() |
Locking your database files in any other order causes database deadlocks. |
Before attaching database files in an external external client, you can have the client ask QDB the attached order for the files. Below is an example of how to ask QDB the attached order of database files, and the result:
qdbc -d mme 'pragma database_list;' Rows: 5 Cols: 3 Names: +seq+name+file+ 00000: |0|main|/fs/tmpfs/mme.db| 00001: |1|temp|| 00002: |2|mme_temp|/fs/tmpfs/mme_temp| 00003: |3|mme_custom|/fs/tmpfs/mme_custom.db| 00004: |4|mme_library|/fs/tmpfs/mme_library.db|
![]() |
If a file doesn't have a filename (row 1), then don't attach it. |
During the development phase of your project you should configure your systems to ensure that you are able to correctly separate performance problems from deadlock problems, and understand and solve each problem accordingly:
You can change your system configuration when you prepare your system for the production environment.
If an operation that uses SQLite, such as those performed by qdb_statement() or qdb_vacuum(), fails because of a corrupt database, the function now returns EBADF and logs an error. Client applications can now check for EBADF, and take appropriate steps to correct the problem with their databases.
![]() |
For information about checking for and correcting inconsistencies, see the information provided with the mme_sync_db_check() function in the MME API Library Reference. |
This section provides a few tips on how to optimize your SQL when working with the MME.
SQL is very flexible and can perform the same job in many different ways. Not all SQL statements are equal, however, and it is important to optimize your client application's requests to the MME database. SQLite is fast, but it can take time to complete an operation if the query statement is not optimized.
For an overview of how to optimize SQL statements for SQLite, see “SQLite Optimizer Overview” on the SQLite web site www.sqlite.org.
The QDB (qdb) resource manager is a resource manager interface on top of the SQLite database engine. Through the QDB, the MME uses SQLite to query and write to its databases. This section offers recommendations for composing queries and other SQL statements for the MME.
![]() |
SQL statements are not case sensitive. For example, the three queries below are equivalent:
select fid,msid,filename from library SELECT fid,msid,filename from library SELECT fid,msid,filename FROM library By convention, however, we use capitals for the SQL keywords to improve the legibility of query statements: SELECT fid,msid,filename FROM library. |
An SQL database can become very large very quickly, with hundreds of thousands of entries. The MME database is designed to scale well, but it's best to limit your queries and to design these queries to avoid duplicating information in the database tables.
Indexes improve database performance. When a query is made against a table, if a column doesn't have an index, it requires a table scan; and if an unindexed column is of type TEXT, SQL will perform a full table scan string comparing all rows with the requested value.
Joins are convenient, but they don't scale well and are often much slower than sub-selections for large tables, because the complexity of JOINs is exponential, while the complexity of sub-selections is linear. As you add more rows to the tables, the query sub-selection will increasingly perform better than the query with the JOIN.
The following examples produce the same results, but the statement with the sub-select is much faster, especially with larger tables.
SELECT fid FROM library INNER JOIN mediastores on library.msid = mediastores.msid WHERE mediastores.available = 1;
SELECT fid FROM library WHERE msid IN (SELECT msid FROM mediastores WHERE available=1);
If you join two small tables that will never be large, then using a JOIN is acceptable, as it won't impact performance. However, the query with the JOIN won't scale well and performance will cause performance to degrade if either one of the tables increases in size.
Media files on external devices, such as a PFS device, remain in the MME library after the device has been removed from the system. The exception to this rule is if the device and its files are pruned from the library to keep the MME database within its configured size limits. For information about database pruning, see “Database pruning” in the MME Configuration Guide. In addition, files that were synchronized but are later found to be unplayable remain in the library, though they are marked as unplayable. For more information, see “Marking unplayable tracks” in the chapter Playback Errors.
To avoid building track sessions with tracks that aren't available, which could cause gaps in playback, your client application should filter out tracks on unavailable mediastores when it builds its track sessions. It should include either WHERE available=1 or WHERE active=1 in its select statement. The example query statement below selects all tracks in the playlist “Favorites” that are on available mediastores:
SELECT fid FROM playlistdata WHERE plid = (SELECT plid from playlists WHERE name = 'Favorites') AND msid IN (SELECT msid FROM mediastores WHERE available=1);
![]() |
![]() |
![]() |
![]() |