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.f.rogers@gmail.com'

Timothy Rogers

Timothy Rogers is founder and owner of Zetamex, a company offering low-cost hosting of OpenSim and Aurora-Sim regions.

  • John Simmons

    Oliveira Virtual Lands is my hosting solution. Fernando is the bomb!

    • Fernando Francisco de Oliveira

      Thank you John

  • Michelle Argus

    Hi,
    is the OAR available for sonwload somewere? I have my DB on an external server and would like to test what improvements would show using the same OAR.

    • The Linda Kellie shopping mall OAR?

      Yes: http://zadaroo.com/?page_id=1647

      And if you haven’t heard of this resource yet — Linda Kellie’s content is all original works and all CC0 licensed (as close to public domain as you can get), and free to use in any way shape or form, for any reason, including commercial.

      • Michelle Argus

        Thx Maria, the article did not mention which oar was used…

        • There were two tests — one with a large IAR file (I don’t know which one), and one with the Linda Kellie shopping mall OAR.

          • hack13

            The IAR was my personal inventory backup from OSgrid.

      • hack13

        I do agree, it is an amazing resource. However I love the license of it, but it really bothers me when I am on the SecondLife marketplace and I notice people just selling the creations for several hundred linden. With no modification at all, it is such a shame. But they are entitled to do so under the license.

        • It doesn’t bother Linda… I guess the folks on the marketplace are paying for the convenience of to avoid having to find and download her stuff themselves. They might have a lot of money, for example, and very little time.

          And the unscrupulous person doing the selling is using time to upload the content and maintain the listings, time Linda herself doesn’t have to spend.

          She loses nothing — she wasn’t selling the content anyway. People who don’t want to look for it and download it themselves, have the convenience of just buying it.

          The unscrupulous merchant gets money in their pocket (though I would bet they have a marketing challenge since they can’t advertise it as a “Linda Kellie” product). Plus, anyone can write a review pointing potential buyers to the Linda Kellie site. So no reputable merchant would do this — think of the embarrassment of being caught reselling Linda Kellie items!

          I guess I don’t really have a problem with it. It hurts nobody, some people benefit a bit.

          I have the same attitude toward stuff that I create. Either I sell it, in which case I protect the content (that is the stuff I write on my day job.) Or I give it away for free, with a CC0 license, and don’t care what people do with it — including selling it. Selling is hard work, and selling virtual content has little reward. I’m not going to bother. But if someone in Pakistan or somewhere can make a little money to feed their kids by reselling my textures or scripts or whatever, more power to them.

          • To add to that — I hereby give permission to anyone reselling my stuff to say that it is a “Hypergrid Business” item or that it was created by me, and is being resold with creator permission.

          • hack13

            Actually it was Linda who pointed it out to me. She does not care what people do with the content, but she does agree with me that it is kinda silly that they sell it for such a high mark up when upload fees are simply 10L$ in Second Life.

        • wolftimber

          They are entitled to do so per the terms of the license, the thing is as Maria points out- they are doing the legwork of managing the “store” and buyers, still, it’s odd when I see people selling print copies of old photos taken off the Library of Congress web site archive which is offered to the public for free as a public owned resource, but I guess that’s legal too even if it seems unethical to me.

          • It’s easy to forget about labor costs. For example, we have no problem going into a store and paying for prints of public-domain art — Mona Lisa, old photographs, etc… Because we understand that we’re paying for the paper and ink, for the printing, the rent for the store, and so on.

            In case of digital content on a virtual marketplace, the only significant expense is labor.

            In SL Markeplace, a framed copy of the Mona Lisa costs L$10. That’s … 4 cents. Let’s say you’re a barely-above minimum wage slave making $10 an hour. It would take you… 15 seconds to earn that much money. Plus, you have to pay the image upload fee! Then you have to adjust the proportions, fit the frame… maybe do some photoshop work to get the frame looking right.

            Now the free, public domain image doesn’t sound as good of a bargain.

          • wolftimber

            True, the only issue I have with in the case of the Library of Congress material is that entity is a 100% taxpayer funded Govt archive, I don’t feel it’s ethical for a business to take the content the public is paying for to be there and making a personal profit off it while the public absorbs all the cost to provide that image for the public to access in the first place.

            It may be 100% legal and all that but it just doesn’t seem right if you see what I mean.
            Producing the final product – the print costs money, but should they get the product that enables them to do that for absolutely no cost to them and the public is paying the costs- thereby subsidizing their profits?
            They should be paying royalties of some sort back to the LOC where they get the images in the first place, this is standard fare for a lot of things that are published- you pay royalties or a license fee to the author or producer to redistribute their work.
            I know the LOC is not the author of the works, but still, they have a cost in running that whole entity and the web site, scanning the images and uploading them too.

          • Or you could look at it this way: the business also pays taxes. It has as much right to use the images as you do. They might get more functionality out of them (say, if they use them as part of a product) — but then, they also pay more taxes. (Aside from those supposedly non-criminal organizations who hide all their money offshore — I have no truck with that.)

  • wolftimber

    Anything that improves the database issue would be a good improvement! mySQL has been around a long time, it along with PHPmyadmin are the standard you normally get with web hosting. I just updated my mySQL with a little bit of directory ownership issues at first, but I found an easy to use replacement for PHPmyadmin called Sequel pro which is free.

    Wonder if something like Sequel Pro Or PHPmyadmin would work with this different database, I think they probably would.

  • Sambo

    I would really like to check out PostgreSQL and went to that link, but I don’t find a download link or any instructions on installing it there.

  • Samantha Atkins

    Excellent. Postgresql is better in multiple ways including support of more than just regular fully normalized relational stuff. Recent editions have pretty good support for JSON documents in table fields for instance.
    I would also be interested in seeing NoSQL databases used as stores. As much of the data to my understanding in write once read many I think a CouchDB or MongoDB would be very interesting to try. The last time i looked it didn’t seem like the Opensim database interfaces were sufficiently abstracted to make creating new database drivers and plugging them in that easy. But I admit that I am not expert in either C# or opensim server so I may have missed something despite being quite expert in persistence systems.