Advertisement:

Author Topic: looking for PostgreSQL tester  (Read 6058 times)

Offline albertlast

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 171
looking for PostgreSQL tester
« on: May 27, 2016, 02:04:03 AM »
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.


Offline albertlast

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 171
Re: looking for PostgreSQL tester
« Reply #1 on: November 05, 2016, 03:02:09 AM »
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.


Offline albertlast

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 171
Re: looking for PostgreSQL tester
« Reply #2 on: August 06, 2017, 04:27:33 PM »
Again small update min version is raised up again now the min version is 9.2

Offline albertlast

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 171
Re: looking for PostgreSQL tester
« Reply #3 on: August 16, 2017, 06:52:22 AM »
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.