David Yin's Blog

Currently Viewing Posts Tagged phpBB 3.1.x

How to use Sphinx Search Engine on phpBB 3.1.6

October 26, 2015
0 Comments

This how-to is made on Centos6.7 64bit. It is a phpBB forum in Chinese with more than 680K posts. If I use phpBB native Fulltext or MySQL Fulltext search, the search tables are over 1.4GB. Too big. And it is very slow when do the search.

Now, my solution is use Sphinx Search Engine.

Step 1)

SSH to VPS as root

yum install sphinx

Step 2)

Sign in with Administrator account in phpBB 3.1.6, and sign in again to ACP

General >> Search settings

Choose Sphinx Fulltext from the Search backend list. Save.

Step 3)

In SSH window, edit /etc/sphinx/sphinx.conf

Add following into sphinx.conf to replace the original sample content. These content is copied from the Step 2 screen.


source source_phpbb_669b8066d06af641_main
{
type = mysql # mysql or pgsql
sql_host = localhost # SQL server host sphinx connects to
sql_user = [dbuser]
sql_pass = [dbpassword]
sql_db = fromchinatousa31x
sql_port =  # optional, default is 3306 for mysql and 5432 for pgsql
sql_query_pre = SET NAMES 'utf8'
sql_query_pre = UPDATE phpbb_sphinx SET max_doc_id = (SELECT MAX(post_id) FROM phpbb_posts) WHERE counter_id = 1
sql_query_range = SELECT MIN(post_id), MAX(post_id) FROM phpbb_posts
sql_range_step = 5000
sql_query = SELECT \
p.post_id AS id, \
p.forum_id, \
p.topic_id, \
p.poster_id, \
p.post_visibility, \
CASE WHEN p.post_id = t.topic_first_post_id THEN 1 ELSE 0 END as topic_first_post, \
p.post_time, \
p.post_subject, \
p.post_subject as title, \
p.post_text as data, \
t.topic_last_post_time, \
0 as deleted \
FROM phpbb_posts p, phpbb_topics t \
WHERE \
p.topic_id = t.topic_id \
AND p.post_id >= $start AND p.post_id <= $end
sql_query_post =
sql_query_post_index = UPDATE phpbb_sphinx SET max_doc_id = $maxid WHERE counter_id = 1
sql_query_info = SELECT * FROM phpbb_posts WHERE post_id = $id
sql_attr_uint = forum_id
sql_attr_uint = topic_id
sql_attr_uint = poster_id
sql_attr_uint = post_visibility
sql_attr_bool = topic_first_post
sql_attr_bool = deleted
sql_attr_timestamp = post_time
sql_attr_timestamp = topic_last_post_time
sql_attr_string = post_subject
}
source source_phpbb_669b8066d06af641_delta : source_phpbb_669b8066d06af641_main
{
sql_query_pre =
sql_query_range =
sql_range_step =
sql_query = SELECT \
p.post_id AS id, \
p.forum_id, \
p.topic_id, \
p.poster_id, \
p.post_visibility, \
CASE WHEN p.post_id = t.topic_first_post_id THEN 1 ELSE 0 END as topic_first_post, \
p.post_time, \
p.post_subject, \
p.post_subject as title, \
p.post_text as data, \
t.topic_last_post_time, \
0 as deleted \
FROM phpbb_posts p, phpbb_topics t \
WHERE \
p.topic_id = t.topic_id \
AND p.post_id >=  ( SELECT max_doc_id FROM phpbb_sphinx WHERE counter_id=1 )
}
index index_phpbb_669b8066d06af641_main
{
path = /home/admin/sphinx-forum/index_phpbb_669b8066d06af641_main
source = source_phpbb_669b8066d06af641_main
docinfo = extern
morphology = none
stopwords =
min_word_len = 2
charset_type = utf-8
charset_table = U+FF10..U+FF19->0..9, 0..9, U+FF41..U+FF5A->a..z, U+FF21..U+FF3A->a..z, A..Z->a..z, a..z, U+0149, U+017F, U+0138, U+00DF, U+00FF, U+00C0..U+00D6->U+00E0..U+00F6, U+00E0..U+00F6, U+00D8..U+00DE->U+00F8..U+00FE, U+00F8..U+00FE, U+0100->U+0101, U+0101, U+0102->U+0103, U+0103, U+0104->U+0105, U+0105, U+0106->U+0107, U+0107, U+0108->U+0109, U+0109, U+010A->U+010B, U+010B, U+010C->U+010D, U+010D, U+010E->U+010F, U+010F, U+0110->U+0111, U+0111, U+0112->U+0113, U+0113, U+0114->U+0115, U+0115, U+0116->U+0117, U+0117, U+0118->U+0119, U+0119, U+011A->U+011B, U+011B, U+011C->U+011D, U+011D, U+011E->U+011F, U+011F, U+0130->U+0131, U+0131, U+0132->U+0133, U+0133, U+0134->U+0135, U+0135, U+0136->U+0137, U+0137, U+0139->U+013A, U+013A, U+013B->U+013C, U+013C, U+013D->U+013E, U+013E, U+013F->U+0140, U+0140, U+0141->U+0142, U+0142, U+0143->U+0144, U+0144, U+0145->U+0146, U+0146, U+0147->U+0148, U+0148, U+014A->U+014B, U+014B, U+014C->U+014D, U+014D, U+014E->U+014F, U+014F, U+0150->U+0151, U+0151, U+0152->U+0153, U+0153, U+0154->U+0155, U+0155, U+0156->U+0157, U+0157, U+0158->U+0159, U+0159, U+015A->U+015B, U+015B, U+015C->U+015D, U+015D, U+015E->U+015F, U+015F, U+0160->U+0161, U+0161, U+0162->U+0163, U+0163, U+0164->U+0165, U+0165, U+0166->U+0167, U+0167, U+0168->U+0169, U+0169, U+016A->U+016B, U+016B, U+016C->U+016D, U+016D, U+016E->U+016F, U+016F, U+0170->U+0171, U+0171, U+0172->U+0173, U+0173, U+0174->U+0175, U+0175, U+0176->U+0177, U+0177, U+0178->U+00FF, U+00FF, U+0179->U+017A, U+017A, U+017B->U+017C, U+017C, U+017D->U+017E, U+017E, U+0410..U+042F->U+0430..U+044F, U+0430..U+044F, U+4E00..U+9FFF
min_prefix_len = 0
min_infix_len = 0
}
index index_phpbb_669b8066d06af641_delta : index_phpbb_669b8066d06af641_main
{
path = /home/admin/sphinx-forum/index_phpbb_669b8066d06af641_delta
source = source_phpbb_669b8066d06af641_delta
}
indexer
{
mem_limit = 40M
}
searchd
{
compat_sphinxql_magics = 0
listen = 127.0.0.1:9312
log = /home/admin/sphinx-forum/log/searchd.log
query_log = /home/admin/sphinx-forum/log/sphinx-query.log
read_timeout = 5
max_children = 30
pid_file = /home/admin/sphinx-forum/searchd.pid
max_matches = 20000
binlog_path = /home/admin/sphinx-forum/
}

Continue reading “How to use Sphinx Search Engine on phpBB 3.1.6”

Dirty Hack: Add CDN support into phpBB 3.1.6

October 17, 2015
0 Comments

It works for phpBB 3.1.x, not just phpBB 3.1.6. For a national forum or global forum, it is a must to have a CDN support.

The way I used is not a formal extension or plugin. It is a hack, actually a dirty hack. If you have no experience for it, you can close this page now.

Now, let’s start.

  1. Backup everything of your phpBB, include files and database.\
  2. URL path example.
    Forum url is http://www.myforum.com/
    CDN url is http://myforum.kxcdn.com/
  3.  Open /includes/functions.php
    add following in line 5112

    // Add CDN path
$cdn_cache = '//myforum.kxcdn.com/';

    Find following around Line 5216

    'T_ASSETS_PATH'         => "{$web_path}assets",
'T_THEME_PATH'         => "{$web_path}styles/" . rawurlencode($user->style['style_path']) . '/theme',
'T_TEMPLATE_PATH'      => "{$web_path}styles/" . rawurlencode($user->style['style_path']) . '/template',
      

    Replace by

    'T_ASSETS_PATH'         => $cdn_cache."assets",
'T_THEME_PATH'         => $cdn_cache."styles/" . rawurlencode($user->style['style_path']) . '/theme',
'T_TEMPLATE_PATH'      => $cdn_cache."styles/" . rawurlencode($user->style['style_path']) . '/template',

    Find following around Line 5230

    'T_STYLESHEET_LINK'      => "{$web_path}styles/" . rawurlencode($user->style['style_path']) . 'https://d22rw8pyewxbkl.cloudfront.net/theme/stylesheet.css' . $config['assets_version'],
'T_STYLESHEET_LANG_LINK'    => "{$web_path}styles/" . rawurlencode($user->style['style_path']) . '/theme/' . $user->lang_name . 'https://d22rw8pyewxbkl.cloudfront.net/stylesheet.css' . $config['assets_version'],

    Replace by

    'T_STYLESHEET_LINK'      => $cdn_cache."styles/" . rawurlencode($user->style['style_path']) . 'https://d22rw8pyewxbkl.cloudfront.net/theme/stylesheet.css' . $config['assets_version'],
'T_STYLESHEET_LANG_LINK'    => $cdn_cache."styles/" . rawurlencode($user->style['style_path']) . '/theme/' . $user->lang_name . 'https://d22rw8pyewxbkl.cloudfront.net/stylesheet.css' . $config['assets_version'],

    Save and overwrite the original file.

  4.   Open /includes/functions_content.php
    Find following around Line 902

    return preg_replace('#<!\-\- s(.*?) \-\-><img src="\{SMILIES_PATH\}\/(.*?) \/><!\-\- s\1 \-\->#', '<img class="smilies" src="' . $root_path . $config['smilies_path'] . '/\2 />', $text);

    Replace by

    return preg_replace('#<!\-\- s(.*?) \-\-><img src="\{SMILIES_PATH\}\/(.*?) \/><!\-\- s\1 \-\->#', '<img class="smilies" src="' .'//forum.kxcdn.com/forum/'.$root_path. $config['smilies_path'] . '/\2 />', $text);

    Save and overwrite the old file.

  5. Then purge cache in the ACP.

Look at the waterfall chat after this hack.

cdn-phpbb-hack

The CDN service provider is KEYCDN. You can apply through this link.

  • Archives