Mar 14 2015

Пересчет сообщений пользователей на форуме XenForo: удачные и неудачные варианты

Category: XenForoFractalizeR @ 9:45 pm

Столкнулся с задачей пересчета сообщений у пользователей на форуме и решил поделиться с сообществом исследованиями на тему ее наиболее эффективного решения.

Вариант №1 (аддон sonnbRebuildUserPostCount 1.0.0 20120725).

Этот аддон пересчитывает сообщения пользователей в цикле, для каждого пользователя выполняя запрос

SELECT COUNT(*)
  FROM `xf_post` AS post
  INNER JOIN `xf_thread` AS thread
  ON (post.thread_id = thread.thread_id)
  WHERE post.user_id = $userId AND
  thread.node_id IN (" . XenForo_Application::getDb()->quote(array_keys($nodes)) . ")

Неудачные моменты:

  • Долго работает на больших форумах (на нашем сейчас 700.000+ пользователей). Выполняет количество запросов превышающее количество пользователей на форуме, что достаточно сильно нагружает сервер (нужно ведь дождаться, пока выполнятся эти 700 тысяч запросов)

Вариант №2 от patrikq

Выполнить запрос

UPDATE xf_user AS user
SET message_count = (
  SELECT COUNT(*)
  FROM xf_post AS post
  LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
  WHERE post.user_id = user.user_id
  AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
  GROUP BY post.user_id
);

Неудачные моменты:

  • Неэффективен, поскольку EXPLAIN показывает, что для выполнения такого запроса будут выполняться два подзапроса. Сильно нагружает базу.
  • Не учитывает, что некоторые форумы могут быть настроены так, чтобы не учитывать сообщения пользователей в этих разделах (встроенная возможность движка). Нужно добавить еще JOIN с таблицей xf_forum и условие на count_messages в WHERE.

Поиск альтернативы.

В XenForo существует таблица xf_thread_user_post, в которой закешировано количество сообщений пользователя в каждом треде форума. Запросы по этой таблице будут выполняться быстрее, чем запросы к xf_post поскольку она гораздо меньше по объему (если таблица в память не помещается, InnoDB читает информацию с диска страницами по 16KB даже в том случае, если из всей страницы нужен только 1 байт).

Вариант №3 (ForumHouse):

Выполнить запрос:

Код:
UPDATE xf_user U SET message_count = (
  SELECT
  SUM(post_count) AS user_post_count
  FROM xf_thread_user_post TUP
  INNER JOIN xf_thread T USING (thread_id)
  INNER JOIN xf_forum F USING (node_id)
  WHERE count_messages AND TUP.user_id = U.user_id
  )

Неудачные моменты:

  • EXPLAIN показывает, что будут выполняться три SUBQUERIES, что недостаточно эффективно.

Вариант №4 (ForumHouse):

Выполнить запрос

UPDATE xf_user U
  INNER JOIN (
  SELECT
  SUM(post_count) AS user_post_count,
  TUP.user_id
  FROM xf_thread_user_post TUP
  INNER JOIN xf_thread T USING (thread_id)
  INNER JOIN xf_forum F USING (node_id)
  WHERE count_messages AND T.discussion_state = 'visible'
GROUP BY TUP.user_id) AS PC ON U.user_id = PC.user_id SET message_count = user_post_count

На нащих объемах (700.000+ пользователей, 200.000+ тем и 8.700.000+ сообщений) отрабатывает за 3 секунды. Если посмотреть его EXPLAIN, будет видно, что SUBQUERIES нет. Есть только несколько DERIVED таблиц.

Разница по времени выполнения между вариантом №3 и 4 примерно 10 раз. Так что остановились пока на нем ;)


Jun 26 2014

XenForo Importer: import user avatars from VLDPersonals

Category: PortfolioFractalizeR @ 10:55 pm

Information

Importer to get user avatars from VLD Personals

  • Status: commercial
  • Customer: www.agrorad.ru
  • Platform/language: PHP, XenForo

 

Features

  • User matching is implemented by email


Jun 22 2014

XenForo Importer: import albums from VLDPersonals into Xen Media Gallery

Category: PortfolioFractalizeR @ 10:02 pm

Information

Importer to move user albums from VLD Personals into Xen Media Gallery addon for XenForo.

  • Status: commercial
  • Customer: www.agrorad.ru
  • Platform/language: PHP, XenForo

 

Features

  • User matching is implemented by email
  • User album thumbnails are built using the first image from album after all data is imported


Jun 20 2014

Laravel4: Calls to Config::get() return empty values

Category: Articles,Laravel4,PHPFractalizeR @ 2:05 am

I’ve made one mistake developing my service provider. Here it is:

 

class AuthorizationServiceProvider extends ServiceProvider
{
 
    /**
     * Indicates if loading of the provider is deferred.
     *
     * @var bool
     */
    protected $defer = false;
 
    /**
     * Bootstrap the application events.
     *
     * @return void
     */
    public function boot()
    {
        $this->package('fractalizer/authorization', 'frauth');
    }
 
    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        $this->app->bind(
            'fractalizer.authorization',
            function () {
                return new Manager();
            }
        );
 
        \Route::filter(\Config::get('frauth::aliases.role_filter_name'), '\Fractalizer\Authorization\Filter@hasRole');
    }
}

 

 

If you make even a single c all to Config::get() inside register() method of your service provider, all the next calls to your configuration namespace will return empty data. The correct way to do things is like this:

class AuthorizationServiceProvider extends ServiceProvider
{
 
    /**
     * Indicates if loading of the provider is deferred.
     *
     * @var bool
     */
    protected $defer = false;
 
    /**
     * Bootstrap the application events.
     *
     * @return void
     */
    public function boot()
    {
        $this->package('fractalizer/authorization', 'frauth');
        \Route::filter(\Config::get('frauth::aliases.role_filter_name'), '\Fractalizer\Authorization\Filter@hasRole');
    }
 
    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        $this->app->bind(
            'fractalizer.authorization',
            function () {
                return new Manager();
            }
        );
    }
}

E.g. move all the calls to Config into boot() method.


Jun 16 2014

XenForo Addon: Thread Moderator

Category: PortfolioFractalizeR @ 12:41 am

Information

Allows administrator to grant specific user permission to moderate only specific thread

 

Features

  • Member username autocompletes
  • You can edit thread moderators together with thread title edition

Скриншот 2014-06-16 00.58.41


Jun 15 2014

XenForo Addon: timer for specific thread or post deletion

Category: PortfolioFractalizeR @ 5:10 pm

Information

Addon allows members of specific usergroups to schedule post or thread deletion on forum.

 

Features

  • Permissions: per-forum or per-usergroup
  • Date periods are completely customizable (PHP’s DateInterval syntax is used)
  • You can edit deletion or cancel it from any edit window in XenForo including quick edit
  • Actual deletion is done by XenForo’s cron
  • When thread is displayed, posts scheduled for deletion are marked with a small icon and descriptive text

 


May 10 2012

Getting rid of “error:: no video mode activated” when using full disk encryption in Ubuntu 12.04

Category: Articles,Linux administrationFractalizeR @ 9:59 pm
sudo cp usr/share/grub/*.pf2 /boot/grub
sudo update-grub


May 09 2012

One-liner to make gEdit 3 correctly autodetect russian encoding

Category: Articles,Linux administrationFractalizeR @ 10:54 pm

Recently I installed Ubuntu 12.04 and it’s gEdit doesn’t recognize cyrillic (Windows-1251) encoding correctly. I discovered one-liner to make it do the task:

sudo gsettings set org.gnome.gedit.preferences.encodings auto-detected "['UTF-8', 'CURRENT', 'WINDOWS-1251', 'ISO-8859-15', 'UTF-16']"


Mar 20 2012

84 PHP functions for disable_functions directive

Category: PHPFractalizeR @ 12:57 pm

A list of php function functions, disabled on the servers I configured:
Continue reading “84 PHP functions for disable_functions directive”


Nov 08 2011

Autoloading PEAR incompatible classes, installed from PEAR packages

Category: Articles,PHPFractalizeR @ 12:42 pm

I like PEAR distribution system. It’s simple to use and installed packages are globally available to all your server scripts (of course, this is an advantage only when you mean it to be). But to easily use packages, installed into PEAR, they should follow some conventions, which are rather old these days, when PSR-0 is out there.

I installed Monolog library using PEAR into my system and found, that it hasn’t bundled autoloader. So, I cannot just require() one file in Monolog and start using it, after it has been installed into PEAR. I need to configure my autloader. And to configure my autoloader, I need to know the exact path of PEAR installation.

The best way I found so far to do that is stream_resolve_include_path() function. So, I’ve done that like this:

Continue reading “Autoloading PEAR incompatible classes, installed from PEAR packages”


Oct 30 2011

Symfony2: how to redirect user to a previous page correctly (without using HTTP_REFERER)

Category: Articles,Symfony2FractalizeR @ 6:09 pm

I was implementing simple menu, that would allow user to change site language.  The site uses standard symfony2 localized urls like http://www.example.com/en/contact. The problem was to return user to a page, where he previously was, but rendered in a selected language. I didn’t like to do any find/replace on REFERER.

Let me show you my solution. Continue reading “Symfony2: how to redirect user to a previous page correctly (without using HTTP_REFERER)”


May 02 2011

phpSweetPDO on GitHub

Category: PHP,Software releasesFractalizeR @ 1:50 am

Some time ago I blogged about creating nice PDO wrapper. Recently I’ve created a repository for my small PDO wrapper on GitHub: https://github.com/FractalizeR/phpSweetPDO

Library is released under Apache License. Feel free to use it!

Tags: ,


Apr 23 2011

Smarty extention for Silex framework

Category: Silex framework,Software releasesFractalizeR @ 10:56 pm

I’ve just released a first version of Smarty extension for Silex framework. You can take it here

Silex is a PHP microframework for PHP 5.3. It is built on the shoulders of Symfony2 and Pimple and also inspired by sinatra.

A microframework provides the guts for building simple single-file apps.


Jan 25 2011

vBTruth: the truth about VBulletin and Internet Brands

Category: VBulletinFractalizeR @ 12:37 pm

Today I stumbled upon a good site – http://vbtruth.com/

If you want to know anything about Internet Brands suit against XenForo, you should definitely visit it and read some pages about so-called “idea stealing proofs” of IB.

I enjoyed reading it really.


Nov 17 2010

Joomla’s Slick RSS (mod_slick_rss) improved and reworked for Joomla 1.5 and PHP 5.3

Category: Joomla addonsFractalizeR @ 2:02 pm

Hello.

Recently I was upgrading my websites on Joomla and moved them to PHP 5.3. I noticed too many errors from mod_slick_rss extension coming to PHP log. I’ve fixed them and added some new options:

  • Some new CSS classes to further style layoout
  • Ability to sort feeds by date or by their original order in the feed
  • Header and footer texts to display before and after the feed
  • Ability to output item date in the feed in a given format

You can download updated version here.


Nov 11 2010

Splitting string at capitalized letters

Category: Articles,Code snippets,PHP,PHPFractalizeR @ 7:42 pm

Recently I had a task to split a given string at capitalized characters, having those saved in PHP. I’ve invented the following solution:

<?php
$str = 'TestMyFuncCall';
var_dump(preg_split('/(?=[A-Z])/', $str, null, PREG_SPLIT_NO_EMPTY));

The result is:

array(4) {
  [0]=>
  string(4) "Test"
  [1]=>
  string(2) "My"
  [2]=>
  string(4) "Func"
  [3]=>
  string(4) "Call"
}

I utilize regular expressions assertions here to avoid character loss.


Nov 08 2010

Practising OOP in PHP5: emulating mixins (Ruby-like)

Category: Articles,PHPFractalizeR @ 12:25 am

Some evening I was implementing behavorial patterns in my framework’s ORM and I desperately needed something like Ruby mixin or C# extension method or like trait/graft in PHP6+. So I was curious to see how can I implement mixins in PHP. If you don’t know what mixin is, I will tell you at once.

So, I invite you to follow me on paths to implementing mixins in PHP and programming a small library to support them. This article targets beginner and average level programmers. Just be sure you know what OOP is. During the process, I will also make some small mistake about nuances of PHP 5.3 work with classes, will point to it and suggest my solution to it. Good reading!

Continue reading “Practising OOP in PHP5: emulating mixins (Ruby-like)”

Tags: , ,


Nov 05 2010

Munin: Pango-WARNING **: Invalid UTF-8 string passed to pango_layout_set_text()

Category: Articles,Linux administrationFractalizeR @ 5:15 pm

To fix this bug, you need to patch a file /usr/share/munin/munin-graph in the following way:

Continue reading “Munin: Pango-WARNING **: Invalid UTF-8 string passed to pango_layout_set_text()”


Nov 05 2010

Munin troubles: Fatal error: Could not open “/var/lib/munin/datafile” for writing

Category: Articles,Linux administrationFractalizeR @ 4:09 pm

Hello.

Having installed Munin recently on my servers, I started to get emails from cron saying

Fatal error: Could not open "/var/lib/munin/datafile" for writing: Permission denied at /usr/lib/perl5/vendor_perl/5.8.8/Munin.pm line 727.
(process:18039): Pango-WARNING **: Invalid UTF-8 string passed to pango_layout_set_text()

I also got some errors about /var/log/munin/munin-update.log being inaccessible. Somehow these files does not belong to munin:munin, but to rrdcache:rrdcache, so the solution is just to make them chmod 0777 and that should solve the problem.


Oct 07 2010

XenForo 1.0 Beta 1 Admin Control Panel in Pictures

Category: Articles,XenForoFractalizeR @ 10:50 pm

If you are going to make a decision about purchasing XenForo, I think, it will be useful for you to catch an image of XenForo admin area because it really reveals almost full XenForo potential. No text below. Just images. Have fun!

Continue reading “XenForo 1.0 Beta 1 Admin Control Panel in Pictures”


Next Page »