News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

looking for PostgreSQL tester

Started by albertlast, May 27, 2016, 02:04:03 AM

Previous topic - Next topic

albertlast

I spend the last 6 months to bring postgresql to the same level as mysql.

Biggest change is the support of the native fulltext search and
the performance should be now at the same level.

smf requirement are pg 8.3+
my recommendation is
min 9.1
best 9.5+

php with pgsql support (not pdo pg).

Reason are the support of unlogged table which are invented with 9.1 and
the insert/update ignore syntax with 9.5.

I would like to see some person would test this on there postgresql installation.
But I warn you, the smf 2.1 is in beta (real beta no google "beta"),
work never with real data.

You will need to download the newst version from github: https://github.com/SimpleMachines/SMF2.1/archive/release-2.1.zip
and move the install.php and install_2-1_postgresql.sql from other directory to the root.
Small hint, please notice somewhere the date of the download,
so that I'm able to look which version of 2.1 you got.

When you find issues please post them here https://github.com/SimpleMachines/SMF2.1/issues
reason for a issue could be postgresql or other things with the date of download...
alternative http://www.simplemachines.org/community/index.php?board=254.0 but not recommendet.


albertlast

A small but important update the min version of pg was raised up for the github version from 8.3 to pg 9.1.

So the use of unlogged table is default now.

PG 9.6 was released some time ago, https://www.postgresql.org/docs/devel/static/release-9-6.html
there is no language extension which improve smf.

But there are some changes which could let smf run better/faster:
    Parallel execution of sequential scans, joins and aggregates - there are no many cases where this help (need to be activated in pg conf)
    Avoid scanning pages unnecessarily during vacuum freeze operations - only maintenance (improve the db tidy up)
    Full-text search can now search for phrases (multiple adjacent words) - didn't know any use case
    Substantial performance improvements, especially in the area of scalability on multi-CPU-socket servers - below this point are many thinks which could improve your smf
            like this one:

            Ignore GROUP BY columns that are functionally dependent on other columns (David Rowley)
            If a GROUP BY clause includes all columns of a non-deferred primary key,
            as well as other columns of the same table, those other columns are redundant and can be dropped from the grouping.
            This saves computation in many common cases.


albertlast

Again small update min version is raised up again now the min version is 9.2

albertlast

With the github version or with smf 2.1 after beta 3,
you can now use postgresql as cache.

For this exists two requirements you main database needs to be postgresql (you cant run smf on mysql and use pg as cache) and
your pg version needs to be 9.5 or higher.

As fare the test/benchmark shows,
this method is faster as the file cache solution.

Feel free to test.

albertlast

Some database got some strength im compare of the other one,
the result are the some feature exists only for one database setup.

Since mysql got no good fulltext search the implementation of how it is the best,
but when you got a postgresql setup.

You got one nice feature,
you can define in which language the fulltext works.
Default is here english,
but you can change this to other language.
The selection is independent from the language you setup smf,
it's depend on provided language on you postgresql setup.

When your language is not listed it's recommanded to select "simple" as language.

The result of this setting is,
that the right stop words are used and singular and plural words match as the same --> better search result.
For a more technical deap dive look at the doc: https://www.postgresql.org/docs/10/static/textsearch-dictionaries.html

albertlast

Support for unix socket connect for/to postgres was added in the last pr wave.

To use it keep the hostname empty on setup/settings.php.
In mysql use "localhost" as hostname.

Advertisement: