OpenSim can use 3 DBMSs: SQLite, MySQL and PostgreSQL. It used to support a 4th DBMS which is MSSQL (Microsoft SQL). Due to lack of development it was removed in 0.8.1.
The default choice in OpenSim is SQLite and it is terrible in many ways, especially for worlds that are big in size. The DBMSs can be chosen by changing the corresponding .ini files in bin/config-include. Care should be taken to change the adequate .ini files that pertain to the mode that OpenSim is running in (Standalone vs Grid/HyperGrid). It is usually done by keeping everything else (other DBMS) in the ini commented and uncommenting the DBMS in question. Two pieces of information are needed: A StorageProvider and a ConnectionString.
It should be mentioned that OpenSim allows the use of no persistence at all. You can say to OpenSim don’t use any DBMS by setting the StorageProvider to “OpenSim.Data.Null.dll”. What will happen here? In this case, OpenSim will keep all data in its memory instead of using a DBMS. That might be interesting to measure.
SQLite is Lightweight DBMS that comes bundled by default with OpenSimulator. It does not require any configuration or any additional installations. This DBMS is suitable for small worlds and not for big worlds or for any OpenSim installation intended for massive production use. Attachment persistence is not fully implemented in this DBMS.
How to access SQLite Databases?
There are many applications that can be installed on Linux that allow you to access SQLite files. I am sure there are many for MS Windows and Mac OS X but I did not have the time to look into these. I used one called “DB Browser for SQLite”, can be found on majority of Linux distros (Debian based or RedHat Based). Another set of tools specifically for SQLite are called SQLite Tools (A bundle of command-line tools for managing SQLite database files) can be found here. Another SQLite tool called Sqliteman can be found here.
These applications are quite easy to use, you only have to open the .db file with the application if you are allowed (sometime there are issues in permissions). You can then query and view table contents.
What are the SQLite databases available by default in OpenSim and what sort of information you can access?
SQLite database files ends normally with a .db extensions. The whole database pertaining to Inventory per example is in a single file with extension .db – that is a great advantage of using SQLite)
- Asset.db – for the AssetService, stores textures and avatars inventories
- avatars.db –
- griduser.db –
- OpenSim.db –
- auth.db – For the authentication service, stores
The PostgreSQL is installed by default on the Red Hat family of Linux distributions (Red Hat enterprise CentOS, Scientific Linux, Fedora etc…). I like to use normally Fedora. The only thing that should be done regularly is to keep the postgresql package up-to-date. Using the following command:
sudo yum update postgresql-server (earlier versions)
sudo dnf update postgresql-server
In case it is not installed for any reason, the following command can be used:
sudo yum install postgresql-server
sudo dnf install postgresql-server
PostgreeSQL works in OpenSim through a module developed by Fernando Francisco a founder of a famous OpenSim hosting company (Oliveira Virtual Lands). PostgreeSQL is the equivalent open source product of the Oracle enterprise solution offering enterprise-level functionality. This will in itself give great power to OpenSim and speed things up considerably.
MySQL a famous database system owned by Oracle
In standalone mode, both services and the simulator itself can use SQLite. In grid mode, SQLite is only supported for simulator data – the ROBUST instances must use a MySQL (or MSSQL) database.
In general a single MySQL instance for the ROBUST services instance will serve small, medium and even large grids perfectly well – it’s a configuration that’s widely used for even quite large websites.”
- MySQL decrease in speed of response when the database start to grow quickly
This is also called migrating OpenSimulator SQLite data to MySQL. The tools here are limited.
The OAR/IAR method: If we have our OpenSim world using SQLite, we can save the regions into OAR and then load them at a target server that uses MySQL