Jun 12 2015

Encapsulating complex query conditions using QueryCriteria in Laravel

Category: Laravel,PHPFractalizeR @ 9:56 pm

Very often my query conditions in repositories become complicated. To be able to reuse them across repositories I use the following simple pattern:

interface CriteriaInterface
{
    /**
     * @param EloquentBuilder|QueryBuilder $query
     *
     * @return void
     */
    public function attach($query);
}

All my query conditions implement this interface and accept condition parameters in the constructor. For example, this is how CategoryCriteria looks like:

/**
 * Class CategoryCriteria
 *
 */
class CategoryCriteria implements CriteriaInterface
{
    /**
     * @var null|int|array
     */
    private $categoryId;
 
    /**
     * CategoryCriteria constructor.
     *
     * @param null|int|array $categoryId
     */
    public function __construct($categoryId)
    {
        $this->categoryId = $categoryId;
    }
 
    /**
     * @param null|int|array $categoryId
     *
     * @return LocationCriteria
     */
    public static function create($categoryId)
    {
        return new static($categoryId);
    }
 
    /**
     * @param EloquentBuilder|QueryBuilder $query
     */
    public function attach($query)
    {
        if (empty($this->categoryId)) {
            return;
        }
 
        if (is_array($this->categoryId)) {
            $query->whereIn('category_id', $this->categoryId);
        } else {
            $query->where('category_id', $this->categoryId);
        }
    }
}


Jun 05 2015

Laravel’s Eloquent::has() and Eloquent::whereHas() performance problems

Category: Laravel,PHPFractalizeR @ 12:52 am

I had a task in my project where I needed to fetch all orders from the database, for which some user has posted an offer. The first intention was to use nice and shiny whereHas method on Eloquent builder like this:

Order::whereHas('offers', function($q) {
    $q->where('user_id', 1112485)
 });

 

But something stopped me and I went to investigate. It turned out, that this instruction generates the following SQL statement:

SELECT *
FROM orders
WHERE (SELECT count(*)
       FROM offers
       WHERE offers.order_id = orders.id AND "user_id" = 1112485) >= 1;

 

The plan for this statement is:

Seq Scan on orders (cost=0.00..249278.84 rows=9525 width=912) (actual time=271.528..465.900 rows=2 loops=1)
 Filter: ((SubPlan 1) >= 1)
 Rows Removed by Filter: 28574
 SubPlan 1
 -> Aggregate (cost=8.44..8.45 rows=1 width=0) (actual time=0.009..0.011 rows=1 loops=28576)
 -> Index Only Scan using offers_order_id_user_id_idx on offers (cost=0.42..8.44 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=28576)
 Index Cond: ((order_id = orders.id) AND (user_id = 1112485))
 Heap Fetches: 2
Planning time: 0.133 ms
Execution time: 465.953 ms

Pay attention to query cost and execution time. The same task may be achieved by  the following query:

SELECT *
FROM orders
WHERE id IN (SELECT DISTINCT order_id
             FROM offers
             WHERE user_id = 1112485)

 

And this one has much better plan:

Nested Loop (cost=0.71..6280.39 rows=5 width=912) (actual time=0.043..5.260 rows=2 loops=1)
 -> Unique (cost=0.42..6238.76 rows=5 width=4) (actual time=0.024..5.217 rows=2 loops=1)
 -> Index Only Scan using offers_order_id_user_id_idx on offers (cost=0.42..6238.62 rows=53 width=4) (actual time=0.019..5.201 rows=2 loops=1)
 Index Cond: (user_id = 1112485)
 Heap Fetches: 2
 -> Index Scan using orders_pkey on orders (cost=0.29..8.31 rows=1 width=912) (actual time=0.007..0.009 rows=1 loops=2)
 Index Cond: (id = offers.order_id)
Planning time: 0.191 ms
Execution time: 5.312 ms

Well, about 100x difference, right?

The same goes for simple

Order::has('offers')

 

Generated SQL for it is

SELECT *
FROM "orders"
WHERE (SELECT count(*)
       FROM "offers"
       WHERE "offers"."order_id" = "orders"."id") >= 1

 

With the query plan

Seq Scan on orders (cost=0.00..856965.30 rows=9525 width=912) (actual time=11.044..2987.939 rows=25603 loops=1)
 Filter: ((SubPlan 1) >= 1)
 Rows Removed by Filter: 2973
 SubPlan 1
 -> Aggregate (cost=29.71..29.72 rows=1 width=0) (actual time=0.095..0.097 rows=1 loops=28576)
 -> Index Only Scan using offers_order_id_user_id_idx on offers (cost=0.42..29.68 rows=13 width=0) (actual time=0.019..0.072 rows=9 loops=28576)
 Index Cond: (order_id = orders.id)
 Heap Fetches: 247734
Planning time: 0.260 ms
Execution time: 3039.399 ms

If you rewrite query like this

EXPLAIN ANALYZE
SELECT *
FROM "orders" O
WHERE id IN (SELECT DISTINCT order_id
             FROM offers);

 

You get the following plan:

Hash Join (cost=14061.99..21990.07 rows=20785 width=849) (actual time=940.164..1093.802 rows=28694 loops=1)
 Hash Cond: (o.id = offers.order_id)
 -> Seq Scan on orders o (cost=0.00..7439.19 rows=32119 width=849) (actual time=0.006..51.002 rows=32119 loops=1)
 -> Hash (cost=13802.18..13802.18 rows=20785 width=4) (actual time=939.942..939.942 rows=28694 loops=1)
 Buckets: 4096 Batches: 1 Memory Usage: 1009kB
 -> HashAggregate (cost=13594.33..13802.18 rows=20785 width=4) (actual time=852.582..898.002 rows=28694 loops=1)
 Group Key: offers.order_id
 -> Seq Scan on offers (cost=0.00..12887.66 rows=282666 width=4) (actual time=0.003..422.209 rows=282666 loops=1)
Planning time: 0.364 ms
Execution time: 1133.665 ms

3x improvement. Which is slightly better 🙂

So, the conclusion is always to check what is generated for you by ORM.


May 27 2015

Disabling Eager Loading Eloquent Model Relations in Laravel 4 and 5

Category: Laravel,Laravel 5FractalizeR @ 1:12 pm

Since accidental lazy-loading almost always lead to performance problems, I was seeking a way to disable it. The solution is easy. Just define somewhere the following trait and use it in your model when you need to disable lazy loading:

* Class OnlyEagerLoadedModelTrait
 *
 * @package FH\Stock\Model
 */
trait EagerLoadOnlyTrait
{
    /**
     * @param string $method
     */
    protected function getRelationshipFromMethod($method)
    {
        $modelName = static::class;
        throw new LogicException(
            "EagerLoadedOnlyTrait: Attempting to lazy-load relation '$method' on model '$modelName'"
        );
    }
}

 

class CategoryUser extends Eloquent
{
    use EagerLoadOnlyTrait;
}

Tags: , , ,


May 02 2015

Enabling Ansible Jinja2 template syntax highlighting in PHPStorm (and other editors)

Category: Linux administrationFractalizeR @ 1:14 am

Just do this:

    <key>fileTypes</key>
    <array>
        <string>j2</string>
    </array>
  • Add cloned repository folder to PHPStorm -> Settings -> Editor -> TextMate Bundles
  • It should work now, but may be you will have to add “*.j2” to PHPStorm -> Settings -> Editor -> Filetypes -> Files supported via TextMate bundles


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 20 2014

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

Category: Articles,Laravel,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.


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)”


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 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”


Oct 04 2010

Internet Brands claims against Xenforo

Category: Articles,VBulletinFractalizeR @ 9:40 pm

Today, at the very edge of XenForo sales starting day Internet Brands claimed against XenForo.

Continue reading “Internet Brands claims against Xenforo”


Sep 27 2010

Regular expression for russian letters in UTF-8

Category: Articles,Code snippets,PHP,PHPFractalizeR @ 1:27 pm
(\xD0[\x80-\xBF])|(\xD1[\x80-\xBF])


Sep 17 2010

VBulletin 4.0.7: they rocked me again

Category: Articles,VBulletinFractalizeR @ 12:19 pm

For some time I was deeply afraid of installing any new release of VBulletin to the forum I administer. But after 4.0.7 came out with several performance bugs fixed I though, that may be it’s time to install it and hoped that installation will go smooth. I closed the forum for 1 hour, blamed myself for writting “1 hour” in the closed message text because surely simple bugfix forum update cannot take longer than 15 mins, right? NOWAY. I was mistaken.

Continue reading “VBulletin 4.0.7: they rocked me again”

Tags:


Next Page »