Postgresql Backend - Deprecated

For people working on the C++ code.
User avatar
Prot
Member
Posts: 36
Joined: Thu Apr 02, 2015 13:20
GitHub: EuPhobos
In-game: EuPhobos
Location: SaratOFF

Re: Postgresql Backend

by Prot » Post

Argos wrote:FYI: I created a postgresql branch on my version of minetestmapper. It supports both johnnyjoy's implementation and ShadowNinja's implementation.
I don't understand how to use it..

Code: Select all

$ git clone https://github.com/Rogier-5/minetest-mapper-cpp.git
$ cd minetest-mapper-cpp
$ git checkout postgresql
$ mkdir build
$ cd build
$ cmake ..
$ make -j 8
$ ./minetestmapper -i /mnt/extrabig/minetest/test_psql/worlds/test_psql -o .
Exception: Failed to find or failed to open a colors.txt file.
$ find /mnt/extrabig/minetest/test_psql -name colors.txt
/mnt/extrabig/minetest/test_psql/util/colors.txt
$ ./minetestmapper --color /mnt/extrabig/minetest/test_psql/util/colors.txt -i /mnt/extrabig/minetest/test_psql/worlds/test_psql -o .
Exception: unable to open database file, Database file: /mnt/extrabig/minetest/test_psql/worlds/test_psql/map.sqlite
$ ./minetestmapper --postgress --color /mnt/extrabig/minetest/test_psql/util/colors.txt -i /mnt/extrabig/minetest/test_psql/worlds/test_psql -o .
./minetestmapper: unrecognized option '--postgress'
By the way, i have 2 servers with this backend, and of course each world use diferrent database name, not default 'minetest', this settings i wrate in world.mt

Code: Select all

$ cat test_psql/worlds/test_psql/world.mt 
gameid = minetest
creative_mode = true
enable_damage = true
backend=postgresql
pg_connection_info = postgresql:///test_psql?application_name=minetest

$ cat creative_psql/worlds/creative_psql/world.mt 
gameid = minetest
backend = postgresql
pg_connection_info = postgresql:///creative_psql?application_name=minetest

Argos
Member
Posts: 40
Joined: Tue Mar 04, 2014 21:47

Re: Postgresql Backend

by Argos » Post

Code: Select all

$ ./minetestmapper --color /mnt/extrabig/minetest/test_psql/util/colors.txt -i /mnt/extrabig/minetest/test_psql/worlds/test_psql -o .
Exception: unable to open database file, Database file: /mnt/extrabig/minetest/test_psql/worlds/test_psql/map.sqlite
Thanks for the bugreport :-) I'll fix it.

As a workaround, make sure there are spaces around the '=' in world.mt. I.e.:

Code: Select all

backend = postgresql
Edit: I fixed the problem & pushed a new version.

Code: Select all

$ ./minetestmapper --postgress --color /mnt/extrabig/minetest/test_psql/util/colors.txt -i /mnt/extrabig/minetest/test_psql/worlds/test_psql -o .
./minetestmapper: unrecognized option '--postgress'
Just like for the regular version of minetestmapper, the option to select a backend is '--backend <backend-type>'. Type 'minestmapper --help' for a list of options, or see the manual (in doc/) for a full description.

Note that the argument to -o should be a filename for the map. E.g.: '-o map.png'.
My mods & tools:
My fork of Minetestmapper - much improved - features, manual, Windows downloads
[MOD] Wrench (rotate:wrench) - improved screwdriver alternative - manual, download

User avatar
Johnny Joy
Member
Posts: 56
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Post

I've logged in and it looks great.
Prot wrote:I had a similar problem at the beginning. When I used TCP/IP to access psql and valley mapgen. Now I switched to UNIX socket and ran 2 servers, the standard minetest_game, and a server with a bunch of mods, including the ethereal mapgen. For more than a month without falling. You can try it at the public server lists "EuPhobos PostgreSQL tests" and "EuPhobos creative psql"
I would like to convert my old server from sqlite to psql, but something the author of this project does not respond, I'm afraid the project is dead. =(

User avatar
Johnny Joy
Member
Posts: 56
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Post

This is great information. I would have been further along, but working too much cause a repetitive stress injury to my right arm and I had to go cold turkey on computes for a while, and then only with my left hand. I'm back now and updating my pull. This is great Argos!
Argos wrote:

Code: Select all

$ ./minetestmapper --color /mnt/extrabig/minetest/test_psql/util/colors.txt -i /mnt/extrabig/minetest/test_psql/worlds/test_psql -o .
Exception: unable to open database file, Database file: /mnt/extrabig/minetest/test_psql/worlds/test_psql/map.sqlite
Thanks for the bugreport :-) I'll fix it.

As a workaround, make sure there are spaces around the '=' in world.mt. I.e.:

Code: Select all

backend = postgresql
Edit: I fixed the problem & pushed a new version.

Code: Select all

$ ./minetestmapper --postgress --color /mnt/extrabig/minetest/test_psql/util/colors.txt -i /mnt/extrabig/minetest/test_psql/worlds/test_psql -o .
./minetestmapper: unrecognized option '--postgress'
Just like for the regular version of minetestmapper, the option to select a backend is '--backend <backend-type>'. Type 'minestmapper --help' for a list of options, or see the manual (in doc/) for a full description.

Note that the argument to -o should be a filename for the map. E.g.: '-o map.png'.

nrz
Developer
Posts: 131
Joined: Sat Feb 07, 2015 17:16
GitHub: nerzhul
IRC: nrzkt
In-game: nrz
Location: France
Contact:

Re: Postgresql Backend

by nrz » Post

We now have a postgresql backend in core:

https://github.com/minetest/minetest/co ... ea3db02b78

This backend uses postgresql 9.5 upserts to have the maximum map updating performance.

User avatar
burli
Member
Posts: 1643
Joined: Fri Apr 10, 2015 13:18

Re: Postgresql Backend

by burli » Post

That's great to hear. But I have a question

Minetest only uses key/value storage. Wouldn't it be more effecient to use key/value databases like leveldb, redis or maybe couchdb instead of SQL databases? Is there any reason except that DBs like Postgresql are more common? Is there a difference in the performance?

It's no rant. Just curious

User avatar
ExeterDad
Member
Posts: 1717
Joined: Sun Jun 01, 2014 20:00
In-game: ExeterDad
Location: New Hampshire U.S.A

Re: Postgresql Backend

by ExeterDad » Post

Thanks a bunch for this @nrz! I've been following your pr eagerly and have to say you really earned this one :)

User avatar
Johnny Joy
Member
Posts: 56
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Post

There are a number of reasons why a relational database would be great for key/value pairing tables.

1. Faster map scanning

Use triggers to set a timestamp of all updated blocks in the database. This would allow programs that would normally have to scan the whole database to only scan actual changed data. Imagine you just wanted to update a map. You would only need to scan the changed blocks since the last time the map was generated. There are programs that scan the map for protection blocks, shops, or locked chests. Again, scanning only the changes is far faster.

2. High Availability
PostgreSQL can be clustered, allowing any since server in the cluster to fail or be taken down while the database is in use.

3. Deployment
Deploying MT servers is much easier if you can just create a container on a 5400rpm drive. Storing the database on the local machine might not be desirable. Imagine having 4 servers with many containers on them. The software doesn't need 15k rpm drives. The game and it's accompanying files are normally referenced only at the time the server is started, but the database is reading and writing all the time.

4. Backups
With PostgreSQL, you can have continuous backups where transaction logs are written to a different server or device. This allows for recovery up to the second should anything happen.

5. Speed
PostgreSQL allows for table partitioning. This breaks up a large table into a smaller series of tables that act like a big table. It's possible to pre-generate tables that represent different segments of the MT world and write them to entirely different tables. This would be done with leveldb, but not without significant work and coding.

6. Customization
Imagine you had a specific set of contributing factors that went into creating blocks and you wanted to save that information for debugging purposes. You could create a table containing your server configuration, such as the MT version. mods, and mod version. Something like 'Minetest 0.4.10, ethereal 1.10m', in a table with numeric Ids as the key. You could add a column to blocks such as config_id that would reference the max config ID. This would allow you to know how each block was generated.

7. Map refresh
With PostgreSQL, I used to save blocks when inserted into the database in two different tables, blocks and blocks_orig. When the map got too messy I could easily refresh the block using the originally generated block. I could just delete the block and allow it to regenerate, but changes in the mapgen system and mods often make this a messy process. I saw this first hand when a server operator wanted to replace a corrupt block and got something completely unexpected which has to be world edited away quickly.
burli wrote:That's great to hear. But I have a question

Minetest only uses key/value storage. Wouldn't it be more effecient to use key/value databases like leveldb, redis or maybe couchdb instead of SQL databases? Is there any reason except that DBs like Postgresql are more common? Is there a difference in the performance?

It's no rant. Just curious

User avatar
Linuxdirk
Member
Posts: 3218
Joined: Wed Sep 17, 2014 11:21
In-game: Linuxdirk
Location: Germany
Contact:

Re: Postgresql Backend

by Linuxdirk » Post

Johnny Joy wrote:There are a number of reasons why a relational database would be great for key/value pairing tables.
Well, then use one of them instead of exhuming a multiple years old thread requesting one :D

https://wiki.minetest.net/Database_backends

User avatar
Johnny Joy
Member
Posts: 56
Joined: Fri Sep 05, 2014 20:26
GitHub: johnnyjoy
In-game: jjb

Re: Postgresql Backend

by Johnny Joy » Post

I'm doing exactly that, but the backend I use would most likely not be accepted easily. I have been using Memcached as a wrapper that can be used with all other database backends. The Memcached database backend is like all the others, but it also contains a database backend object. Calls to load blocks first check Memcached and if not found the database is checked. If the block data is found in the database it's saved in Memcached and returned. Saving blocks are first saved to Memcached and then to the database. I have found that during map generation that a block will be read a number of times. When using Memcached combined with SQLite3 I found that over 85% of reads never hit the database. I could, of course, use Couchbase, but my goal was to create a simple and easy to use an accelerator that was so easy to use that kids used it to speed up their Drupal sites.

It's almost ready for prime time. Anyone would be able to speed up their server and reduce disk access by a huge percentage using a very common utility used by most websites. Even so, I think that only servers would find it useful.

Here is some of the code, but it still needs some refinement.

Code: Select all

Database_Memcached::Database_Memcached(Settings &conf, Database *dbase)
{
        m_dbase  = dbase;
        m_config = conf.get("memcached");

        // Automatic configuration for the lazy minimalist
        if (m_config == "true" || m_config == "1")
                m_config = "--SERVER=localhost";

        connect();

        infostream << "Backend: Memcached initialized." << std::endl;
}

bool Database_Memcached::saveBlock(const v3s16 &pos, const std::string &data)
{
        setCache(pos, data);

        return m_dbase->saveBlock(pos, data);
}

std::string Database_Memcached::loadBlock(const v3s16 &pos)
{
        std::string data = getCache(pos);

        if (data.size() < 1)
                data = m_dbase->loadBlock(pos);

        if (data.size() > 1)
                setCache(pos, data);

        return data;
}

Linuxdirk wrote:
Johnny Joy wrote:There are a number of reasons why a relational database would be great for key/value pairing tables.
Well, then use one of them instead of exhuming a multiple years old thread requesting one :D

https://wiki.minetest.net/Database_backends

Post Reply

Who is online

Users browsing this forum: No registered users and 6 guests