Databases in OpenSim – How to install, configure and use them?

****This post is not complete I will cover additional material.****

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

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?

In Linux

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
  • friends.db
  • inventory.db
  • userprofiles.db

PostgreSQL

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

How to configure OpenSim to use PostgreSQL?

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.

How to transform the worlds loaded in SQLite to PostgreSQL?

 

MySQL

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.”

[OpenSim Performance Wiki Article]

Pros vis-à-vis of OpenSim

 

Cons vis-à-vis of OpenSim

  1. MySQL decrease in speed of response when the database start to grow quickly

How to configure OpenSim to use MySQL?

 

How to transform the worlds loaded in SQLite to MySQL?

This is also called migrating OpenSimulator SQLite data to MySQL. The tools here are limited.

1st method

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

How to change the DBMS of OpenSim from MySQL to PostgreSQL and Vice Versa?

 

Useful links

MySQL

YouTube Videos

Video 1   Video 2

Others

OpenSim – MySQL Install Guide

PostgreSQL

Leave a Reply

Your email address will not be published. Required fields are marked *