Talk:Database Settings

MySQL: using a Unix socket instead of a TCP socket is possible?...

Hi!

For security reasons, I do not wish to launch MySQL/MariaDB with an open 3306 port — also, it saves some precious resources (every byte counts!).

These instructions claim that it's possible to use a Unix socket instead, by using 'Data Source=/path/to/socket;...' but this seems not to be the case:

17:13:04 - [FSASSETS]: Can't connect to database: Unable to connect to any of the specified MySQL hosts.

and later:

System.Reflection.TargetInvocationException: Exception has been thrown by the ta
rget of an invocation. ---> MySql.Data.MySqlClient.MySqlException: Unable to con
nect to any of the specified MySQL hosts. ---> System.Net.Sockets.SocketExceptio
n: Could not resolve host '/var/run/mysqld/mysqld.sock'
  at System.Net.Dns.Error_11001 (System.String hostName) [0x00015] in <88f564ea6
9dd4dc8ba9bf979e48d5996>:0 
  at System.Net.Dns.GetHostByName (System.String hostName) [0x00021] in <88f564ea69dd4dc8ba9bf979e48d5996>:0 
  at System.Net.Dns.GetHostEntry (System.String hostNameOrAddress) [0x00052] in <88f564ea69dd4dc8ba9bf979e48d5996>:0 
  at MySql.Data.Common.MyNetworkStream.GetHostEntry (System.String hostname) [0x0000c] in <0004ab8b375b422f9000ac25a68089d9>:0 
  at MySql.Data.Common.MyNetworkStream.CreateStream (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings, System.Boolean unix) [0x00008] in <0004ab8b375b422f9000ac25a68089d9>:0 
  at MySql.Data.Common.StreamCreator.GetTcpStream (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings) [0x00000] in <0004ab8b375b422f9000ac25a68089d9>:0 
  at MySql.Data.Common.StreamCreator.GetStream (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings) [0x00021] in <0004ab8b375b422f9000ac25a68089d9>:0 
  at MySql.Data.MySqlClient.NativeDriver.Open () [0x00007] in <0004ab8b375b422f9000ac25a68089d9>:0 
   --- End of inner exception stack trace ---
  at MySql.Data.MySqlClient.NativeDriver.Open () [0x00041] in <0004ab8b375b422f9000ac25a68089d9>:0 
  at MySql.Data.MySqlClient.Driver.Open () [0x0000b] in <0004ab8b375b422f9000ac25a68089d9>:0 
  at MySql.Data.MySqlClient.Driver.Create (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings) [0x0004e] in <0004ab8b375b422f9000ac25a68089d9>:0 
  at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection () [0x00000] in <0004ab8b375b422f9000ac25a68089d9>:0 
  at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection () [0x0008a] in <0004ab8b375b422f9000ac25a68089d9>:0 
  at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver () [0x0003f] in <0004ab8b375b422f9000ac25a68089d9>:0 
  at MySql.Data.MySqlClient.MySqlPool.GetConnection () [0x0001c] in <0004ab8b375b422f9000ac25a68089d9>:0 
  at MySql.Data.MySqlClient.MySqlConnection.Open () [0x0016d] in <0004ab8b375b422f9000ac25a68089d9>:0 
  at OpenSim.Data.MySQL.MySQLAssetData.Initialise (System.String connect) [0x00013] in <e8223c686f534fcfbb8f82618e6d0c9d>:0 
  at OpenSim.Services.AssetService.AssetServiceBase..ctor (Nini.Config.IConfigSource config, System.String configName) [0x000ef] in <13354e9a2d8f4ffbbbe91addeaa7bcc4>:0 
  at OpenSim.Services.AssetService.AssetService..ctor (Nini.Config.IConfigSource config, System.String configName) [0x00000] in <13354e9a2d8f4ffbbbe91addeaa7bcc4>:0 
  at OpenSim.Services.AssetService.AssetService..ctor (Nini.Config.IConfigSource config) [0x00000] in <13354e9a2d8f4ffbbbe91addeaa7bcc4>:0 
  at (wrapper managed-to-native) System.Reflection.RuntimeConstructorInfo.InternalInvoke(System.Reflection.RuntimeConstructorInfo,object,object[],System.Exception&)
  at System.Reflection.RuntimeConstructorInfo.InternalInvoke (System.Object obj, System.Object[] parameters, System.Boolean wrapExceptions) [0x00005] in <f2b3ab7dfff746f594d2ef5b16ec3c90>:0 
   --- End of inner exception stack trace ---
  at System.Reflection.RuntimeConstructorInfo.InternalInvoke (System.Object obj, System.Object[] parameters, System.Boolean wrapExceptions) [0x0001a] in <f2b3ab7dfff746f594d2ef5b16ec3c90>:0 
  at System.Reflection.RuntimeConstructorInfo.DoInvoke (System.Object obj, System.Reflection.BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) <0x7fa9f8226fc0 + 0x000c8> in <f2b3ab7dfff746f594d2ef5b16ec3c90>:0 
  at System.Reflection.RuntimeConstructorInfo.Invoke (System.Reflection.BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) <0x7fa9f8227140 + 0x0001f> in <f2b3ab7dfff746f594d2ef5b16ec3c90>:0 
  at System.RuntimeType.CreateInstanceImpl (System.Reflection.BindingFlags bindingAttr, System.Reflection.Binder binder, System.Object[] args, System.Globalization.CultureInfo culture, System.Object[] activationAttributes, System.Threading.StackCrawlMark& stackMark) [0x0022b] in <f2b3ab7dfff746f594d2ef5b16ec3c90>:0 
  at System.Activator.CreateInstance (System.Type type, System.Reflection.BindingFlags bindingAttr, System.Reflection.Binder binder, System.Object[] args, System.Globalization.CultureInfo culture, System.Object[] activationAttributes) <0x7fa9f8058380 + 0x0013a> in <f2b3ab7dfff746f594d2ef5b16ec3c90>:0 
  at System.Activator.CreateInstance (System.Type type, System.Object[] args) <0x7fa9f8058560 + 0x00022> in <f2b3ab7dfff746f594d2ef5b16ec3c90>:0 
  at OpenSim.Services.Base.ServiceBase.LoadPlugin[T] (System.String dllName, System.String className, System.Object[] args) [0x00066] in <9f09c2e64286470d8fa2cd78e2dbf52f>:0 

17:13:04 - [FSASSETS]: Failed to load fallback service

... and so forth.

This also seems consistent with the report by Greg Weber, made 10 years ago. I've also tried a few combinations, e.g. using the keyword 'Socket' to specify the socket (that keyword is not recognised) and a few more suggestions according to this page.

So, what's the trick?

Gwyneth Llewelyn (talk) 09:23, 9 June 2020 (PDT)

Ten years ago, it seemed to have worked with the simple trick of adding Mono.Posix.dll to the bin directory: OS Grid Forums Some of the official Microsoft documentation also seems to imply that this way of connecting is possible: MySQL Connection String -- Gwyneth Llewelyn (talk) 10:00, 9 June 2020 (PDT)

Last but not least, there is a Mantis filed for it. It's not been fixed in the past three years. -- Gwyneth Llewelyn (talk) 10:04, 9 June 2020 (PDT)

MySQL: what database engine is recommended?

I'm actually using MariaDB under Ubuntu 20.04.1 LTS (10.4.17-MariaDB-1:10.4.17+maria~focal-log as of writing) and I was wondering what database engine is recommended for OpenSimulator.

In ancient times (i.e. when I installed OpenSimulator for the first time), MySQL's default database/table engine was MyISAM. InnoDB was still experimental and was only recommended if you wished to be ACID-compliant (at the cost of some overhead), but you'd lose some features such as native fulltext search. Today, of course, InnoDB is the default; it has far better performance while still remaining robust; and, over time, all 'bells and whistles' available on MyISAM were implemented in InnoDB, more or the less.

Thus, the last time I switched servers, when moving over all the OpenSimulator tables, I just kept the defaults — that means InnoDB. Is that still the recommended engine? If not, what engine would be recommended?

InnoDB is rather good for scenarios where there is little writing and a lot of reading. However, I've recently noticed that OpenSimulator does a fair amount of writing — especially on the assets/prims/primshapes tables (and a few others). I didn't do a formal performance test. But I tried to experiment with a different database engine. MariaDB also implements the RocksDB engine (originally developed by Facebook and currently being used by a lot of RDBMS, not only MySQL/MariaDB) which is allegedly better at managing many database writes (as well as being optimised for SSD disks). It also requires less space on disk (due to better compression). I experimented a bit with that engine, and couldn't see much of a difference, except that all instances seemed to have a slight degradation of performance and require a bit more CPU than usual.

I had done experiments on other platforms (for instance, WordPress) and what I found out is that RocksDB is not particularly well-suited for complex database queries — i.e. those involving intricate JOINs, dealing with several indexes, filters, and all sorts of things. It does the job, but it's not particularly efficient at doing so. At the end of the day, I thought it would be better to use a different engine, or revert to good, old InnoDB.

I've also experimented with the Aria engine. It's an improved version of the old, venerable MyISAM engine, with even more performance and low overhead, a redesigned caching system, and, most importantly, a transaction journal (to recover tables when the database crashes). It isn't ACID-compliant (no commit/rollback), just like the original MyISAM, but it ought to do what MyISAM did, with a bit of more performance.

Now I wonder if any of you have experimented with the different engines and which one do you recommend. I think that this ought to be mentioned on this page. AFAIK, OpenSimulator will create the database tables with whatever engine is set by default, which is, as of 2021, InnoDB for both MySQL and MariaDB. But maybe this is not the 'best' choice, at least perhaps not for all the tables. What do you say?

Gwyneth Llewelyn (talk) 04:18, 3 January 2021 (PST)

MySQL: what database engine is recommended?

I'm actually using MariaDB under Ubuntu 20.04.1 LTS (10.4.17-MariaDB-1:10.4.17+maria~focal-log as of writing) and I was wondering what database engine is recommended for OpenSimulator.

In ancient times (i.e. when I installed OpenSimulator for the first time), MySQL's default database/table engine was MyISAM. InnoDB was still experimental and was only recommended if you wished to be ACID-compliant (at the cost of some overhead), but you'd lose some features such as native fulltext search. Today, of course, InnoDB is the default; it has far better performance while still remaining robust; and, over time, all 'bells and whistles' available on MyISAM were implemented in InnoDB, more or the less.

Thus, the last time I switched servers, when moving over all the OpenSimulator tables, I just kept the defaults — that means InnoDB. Is that still the recommended engine? If not, what engine would be recommended?

InnoDB is rather good for scenarios where there is little writing and a lot of reading. However, I've recently noticed that OpenSimulator does a fair amount of writing — especially on the assets/prims/primshapes tables (and a few others). I didn't do a formal performance test. But I tried to experiment with a different database engine. MariaDB also implements the RocksDB engine (originally developed by Facebook and currently being used by a lot of RDBMS, not only MySQL/MariaDB) which is allegedly better at managing many database writes (as well as being optimised for SSD disks). It also requires less space on disk (due to better compression). I experimented a bit with that engine, and couldn't see much of a difference, except that all instances seemed to have a slight degradation of performance and require a bit more CPU than usual.

I had done experiments on other platforms (for instance, WordPress) and what I found out is that RocksDB is not particularly well-suited for complex database queries — i.e. those involving intricate JOINs, dealing with several indexes, filters, and all sorts of things. It does the job, but it's not particularly efficient at doing so. At the end of the day, I thought it would be better to use a different engine, or revert to good, old InnoDB.

I've also experimented with the Aria engine. It's an improved version of the old, venerable MyISAM engine, with even more performance and low overhead, a redesigned caching system, and, most importantly, a transaction journal (to recover tables when the database crashes). It isn't ACID-compliant (no commit/rollback), just like the original MyISAM, but it ought to do what MyISAM did, with a bit of more performance.

Now I wonder if any of you have experimented with the different engines and which one do you recommend. I think that this ought to be mentioned on this page. AFAIK, OpenSimulator will create the database tables with whatever engine is set by default, which is, as of 2021, InnoDB for both MySQL and MariaDB. But maybe this is not the 'best' choice, at least perhaps not for all the tables. What do you say?

Gwyneth Llewelyn (talk) 04:18, 3 January 2021 (PST)

MySQL: what database engine is recommended?

I'm actually using MariaDB under Ubuntu 20.04.1 LTS (10.4.17-MariaDB-1:10.4.17+maria~focal-log as of writing) and I was wondering what database engine is recommended for OpenSimulator.

In ancient times (i.e. when I installed OpenSimulator for the first time), MySQL's default database/table engine was MyISAM. InnoDB was still experimental and was only recommended if you wished to be ACID-compliant (at the cost of some overhead), but you'd lose some features such as native fulltext search. Today, of course, InnoDB is the default; it has far better performance while still remaining robust; and, over time, all 'bells and whistles' available on MyISAM were implemented in InnoDB, more or the less.

Thus, the last time I switched servers, when moving over all the OpenSimulator tables, I just kept the defaults — that means InnoDB. Is that still the recommended engine? If not, what engine would be recommended?

InnoDB is rather good for scenarios where there is little writing and a lot of reading. However, I've recently noticed that OpenSimulator does a fair amount of writing — especially on the assets/prims/primshapes tables (and a few others). I didn't do a formal performance test. But I tried to experiment with a different database engine. MariaDB also implements the RocksDB engine (originally developed by Facebook and currently being used by a lot of RDBMS, not only MySQL/MariaDB) which is allegedly better at managing many database writes (as well as being optimised for SSD disks). It also requires less space on disk (due to better compression). I experimented a bit with that engine, and couldn't see much of a difference, except that all instances seemed to have a slight degradation of performance and require a bit more CPU than usual.

I had done experiments on other platforms (for instance, WordPress) and what I found out is that RocksDB is not particularly well-suited for complex database queries — i.e. those involving intricate JOINs, dealing with several indexes, filters, and all sorts of things. It does the job, but it's not particularly efficient at doing so. At the end of the day, I thought it would be better to use a different engine, or revert to good, old InnoDB.

I've also experimented with the Aria engine. It's an improved version of the old, venerable MyISAM engine, with even more performance and low overhead, a redesigned caching system, and, most importantly, a transaction journal (to recover tables when the database crashes). It isn't ACID-compliant (no commit/rollback), just like the original MyISAM, but it ought to do what MyISAM did, with a bit of more performance.

Now I wonder if any of you have experimented with the different engines and which one do you recommend. I think that this ought to be mentioned on this page. AFAIK, OpenSimulator will create the database tables with whatever engine is set by default, which is, as of 2021, InnoDB for both MySQL and MariaDB. But maybe this is not the 'best' choice, at least perhaps not for all the tables. What do you say?

Gwyneth Llewelyn (talk) 04:18, 3 January 2021 (PST)