New database option for OpenSim

For those of you who haven’t been watching the latest Google+ feeds, Fernando Francisco de Oliveira, a long and involved OpenSim enthusiast and programmer and founder of OpenSim hosting company Oliveira Virtual Lands, has released to the world a module that lets OpenSim use PostgreSQL. For those who do not know, PostgreSQL is the open source equivalent to Oracle’s enterprise database solution. This means OpenSim now has the power to utilize enterprise class databases.

Currently, OpenSim commonly uses MySQL, another open source database, but owned by Orace. MySQL is substantially more popular, but Postgre offers more enterprise-level functionality and compatibility with international standards, making it easier to migrate to proprietary databases later on if needed.

MySQL also starts to decrease noticeably in speed as the database grows. This is where PostgreSQL is different, as it is meant to be an enterprise-level database service and is designed to not slow down with the database growing in size.

Why Should I Care and How Does This Affect Me?

When I asked Fernando about what he thinks about what this module can do for OpenSim, he responded, “I believe as database server, PostgreSQL was designed to work in a high volume environment. It fits well with OpenSim .”

He is correct — OpenSim grids can generate very large databases, and, let’s face it, the size of the data OpenSim can reach needs enterprise-level response.

Fernando load testing PostgreSQL. (Image courtesy Fernando Oliveira.)
Fernando load testing PostgreSQL. (Image courtesy Fernando Oliveira.)

In Testing

Fernando and I both ran tests with his new module, and came up with some pretty interesting results. Where he saw slightly slower responses, I believe it was due to our hardware differences. But from a fresh OpenSim install importing a 556mb IAR inventory archive, the results I found were quite amazing:

MySQL: 28 minutes and 15 seconds
PostgreSQL: 17 minutes and 12 seconds

After a couple passes of the database servers running results did improve:

MySQL: 11 minutes and 22 seconds
PostgreSQL: 9 minutes and 29 seconds

I also tested PostgreSQL and MySQL in loading a large OAR region archive file, specifically the 23,000-prim Linda Kellie Freebie Mall OAR.

MySQL: 46 seconds
PostgreSQL: 20 seconds

The even nicer thing about PostgreSQL is that as MySQL grows in size, it starts to eat a lot more system resources. This includes both RAM and CPU.

Many grid owners will agree that during peak upload times asset and inventory servers can go up to 90% CPU usage.

This, however, is not the case in PostgreSQL, as it is designed for these heavy world loads and uses separate processes for processing data transfer. I notice a significant resource difference, of up to 67%, when I was using PostgreSQL instead of MySQL.

So What Are The Plans?

When I spoke to Fernando about what he plans to do with this module, he tells me that he hopes it will get accepted into the OpenSim core code. At the moment, it is only available on his public github which you can get to here. But you will need to compile it and configure it yourself, not too difficult for most to do. He really wants everyone to benefit from this contribution and hopes that it can really push opensim forward.

I also just have to point out when I asked him, what inspired him to write this I was amazed at his response.

“The real reason to restart it was just to try see if another database would improve Opensim performance, and the challenge to do it,” he said. “Challenge moves me. As I love programming and I love the OpenSim project, I just did it.”

I am unsure if he plans on migrating his OpenSim hosting customers over to PostgreSQL anytime soon, but I am sure he is thinking about it. Zetamex is looking into this as well, as I hope many others do too. This can help keep hosting costs down, as the resource consumption is much lower than that of MySQL.

Timothy Rogers

Timothy Rogers is a developer and the founder and former owner of OpenSim hosting company Zetamex Networks. He is also the host of the Zadaroo website, which offers a complete collection of free Linda Kellie content.
timothy.f.rogers@gmail.com'

Latest posts by Timothy Rogers (see all)