Understand SPE database
SPE database serves multiple purposes:
- stores SPE internal data
- stores various information about SPE entities created by SPE user
- audio files metadata
- speaker models and their voiceprints
- speaker groups and their voiceprints
- calibration sets
- keyword lists
- language packs
- audio source profiles
- stores cached processing results
(ON by default, can be set in SPE configuration file)
- optionally also stores SPE log data
(MariaDB / MySQL only, OFF by default, can be set in SPE configuration file)
To cache or not to cache?
Well, that’s a question… 😉 It depends on the particular use case AND on the design of your application, whether using the built-in results caching would be beneficial or not.
In general, the built-in results caching can be useful when creating simple lightweight application. When building a complex voiceprocessing system, using multiple SPE processing units, load balancing, etc., it should be generally better to disable the built-in results caching and create your own caching layer, tailored specifically for your particular system architecture and/or processing workflow.
Cached data persistency
Cached processing results are kept in database as long as the audio file exists. When the audio file is deleted from SPE storage, all related information, metadata and processing results are deleted from the database.
Stream processing data is not cached at all.
If data privacy and security is a concern, disabling the built-in results caching ensures that processing results are returned only via the REST API response and are not kept in the database at all.
SPE supports SQLite and MariaDB 10.x (SPE 3.46+) MySQL 5.x (SPE up to 3.45) database engine.
The database engine is configured in
phxspe.properties SPE configuration file – see the Database section of SPE configuration file article for more details.
SQLite is the out-of-the-box SPE default database type.
By its nature, SQLite is intended mainly as lightweight storage for configuration data. Still, it can handle also the results caching of course… unless we talk about real mass-processing.
When using results caching AND processing like hundreds of thousands or millions of audio files per day, the SQLite’s locking mechanism (simple global database lock) can become a performance bottleneck… and choosing a higher-performance MariaDB (MySQL) database is the way to go.
When SPE is configured to use SQLite database, the database is created and initialized automatically by running
SQLite database is typically created during first-time SPE setup, when configuring technologies using
phxadmin – it’s created silently behind the scenes, using values from
phxspe.properties configuration file (location, file name) and default SPE configuration (users, roles, etc.).
SQLite database updates are also handled automatically by SPE – from time to time, as we add new features or improve existing functionality, the database internal structure may get updated in newer SPE versions. When using SQLite, if new SPE version detects that database needs an update, it’s done fully automatically behind the scenes.
 If Speech Engine is used together with Phonexia Browser in so-called “embedded” mode (see details about “embedded SPE” mode in Browser manual), Phonexia Browser creates its own separate SPE configuration file and the SQLite database file is located in SPE
home directory and named
This might be important in certain scenarios, e.g. when registering LID language pack using
phxadmin – you need to point the
phxadmin to the appropriate SPE configuration file in order to make the changes to correct database.
MariaDB / MySQL
MariaDB / MySQL database is a high-performance alternative for SQLite.
As opposed to SQLite, MariaDB (or MySQL) uses fine-grained locking mechanisms, resulting in higher performance in environments with high concurrency – e.g. in mass-processing deployments with multiple SPE processing units and results caching in central database, etc.
When SPE is configured to use MariaDB / MySQL database, the database must be created and initialized manually first using SQL scripts provided in SPE distribution package.
Similarly, when updating SPE to newer version, any eventual required MariaDB / MySQL database updates must be done manually as part of the manual SPE update process using the SQL scripts.
See more details in Understanding SPE database scripts article.
Database is not being vacuumed/optimized/shrinked… however, the database space freed by deleted data is re-used by newly added data.
Therefore it is normal that database size grows over time to a certain extent. Assuming that a) the daily input load is more-or-less the same, and b) that processed/unneeded audio files get removed from SPE storage, the database would grow to a certain size and then stay at that size, as the number/size of new DB records gets in balance with the number/size of deleted DB records.
In any case, if the database gets oversized e.g. by one-off processing of unusual amount of audio, it can still be manually vacuumed/optimized/shrinked using commands appropriate for the database type you are using.
NOTE: (Excessive) database growth may be a sign of missing housekeeping in your workflow design.
For example, you may not be deleting uploaded audio files after processing using the appropriate REST API call. Another example would be that you are not unregistering files after processing (if using the files registering technique instead of uploading the audio files – see the Understanding SPE home directory article). This makes the files information AND the cached processing results to be kept in database.
Or, you may be saving stream data to file, but not deleting the created stream audio files using the REST API call when/if they are not needed anymore… again, stacking up the files metadata in the database.
Database structure and content
SPE database consists of tables and views with
rest_ prefix (this comes from SPE’s predecessor named Phonexia REST Server).
Based on type of data they contain, these can be divided to following groups:
- SPE internal data
- information about files and directories in SPE storage
- internal data: resource types, resource locks, users, user roles, user sesssions, technology models
- user-created entities data
- SID speaker models and their voiceprints, speaker groups, calibration sets, audio source profiles
- LID language models
- KWS keyword lists
- cached processing results
- if caching is enabled, processing results for each technology
SPE internal data
Tables containing SPE internal data:
||list of internal directory types|
||list of information about files registered in SPE – path, creation and modification timestamps, owner (SPE user), directory|
||SPE log data, see above|
||list of internal resource types – file, SID speaker model, SID speaker group, SID calibration set, SID audio source profile, KWS keyword list, LID language pack|
||list of resources locked during processing|
||list of pre-defined SPE user roles|
||list of SPE users and their settings and status – login, password, active/inactive, max. pending operations, current pending operations|
||associations between users and roles|
||list of active user API sessions|
||list of technology model names|
User-created SPE entities data
Tables containing data about entities created by SPE users:
||list of SID speaker models – name, owner (SPE user), modification timestamp|
||list of files used as sources for SID speaker models creation|
||list of files used as SID speaker models metafiles|
||list of SID speaker groups – name, owner (SPE user)|
||associations between SID speaker groups and speaker models|
||SID voiceprints – voiceprint data, technology model used to create the voiceprint, speaker model to which the voiceprint belongs (speaker model voiceprints), calibration set to which the voiceprint belongs (FAR calibration set voiceprints)|
||SID speaker model voiceprints calibrated to FAR – voiceprint data, speaker model, technology model used to create the voiceprint, max. FAR, calibration set used to calibrate the voiceprint|
||list of SID FAR calibration sets – name and modification timestamp, owner (SPE user)|
||list of files used as sources for SID FAR calibration sets creation|
||list of files used as SID FAR calibration sets metafiles|
||number of chunks in SID FAR calibration sets|
||list of SID4 Audio Source Profiles – name, owner (SPE user), technology model used to create the profile, file with the profile content, hash|
||list of files used as SID4 Audio Source Profiles metafiles|
||list of LID language packs – name, owner (SPE user), technology model to which the language pack belongs (i.e. technology model used to create source languageprints/language models)|
||list of LID language packs metafiles|
||KWS keyword lists – keyword list JSON data, keyword list name, owner (SPE user), technology model to which the keyword list belongs|
Processing results data
Tables containing cached processing results (if results caching is enabled):
||AGE processing results – file, used technology model, results JSON data|
||DIAR processing results – file, used technology model, used processing parameters, results JSON data|
||GID processing results – file, used technology model, results JSON data|
||KWS processing results – file, used technology model, used keyword list, results JSON data|
||LID processing results – file, used technology model, used language pack, results JSON data|
||PHNREC processing results – file, used technology model, results JSON data|
||SID processing results – file, used technology model, used speaker model, used FAR calibration set, max. FAR, results JSON data|
||SID4 processing results – file, used technology model, used speaker model, used file- and speaker model Audio Source Profile, results JSON data|
||SQE processing results – file, used technology model, results JSON data|
||STT processing results – file, used technology model, results JSON data|
||TAE processing results – file, used technology model, results JSON data|
||VAD processing results – file, used technology model, results JSON data|
SPE logging to database
Storing SPE logs to database is available only for MariaDB / MySQL.
This is mainly for performance reasons – SQLite is not designed for high concurrency, i.e. its locking mechanism would create a bottleneck… especially in setups where multiple SPE instances are configured to store the logging data into the same database.
Log data is stored in
rest_log table and includes the following columns:
||identifier of SPE subsystem which created the log record|
||identifier of source SPE which created the log record
can be set by
||numeric PID of the process which created the log record|
||identifier of thread which created the log record|
||numeric ID of thread which created the log record|
||priority of the operation which created the log record|
||raw log text as it would be written into log file or console|
||log record creation timestamp|