MySql, $query->batch() from more millions of record

[AstRonin]

I tried run yii\db\Query::batch() for big table and got memory leak. This happens because method \yii\db\Command::queryInternal() run

$this->pdoStatement->execute();

I changed attribute PDO::MYSQL_ATTR_USE_BUFFERED_QUERY -> false and it helps me.
I think, we should add description about this feature into documentation.

[yujin1st]

Could you specify where did you set the attribute?
I tried it in config and runtime(Yii::$app->db->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,false)), and got error 2014.

[AstRonin]

As I understand this problem, you did not use 'fetch' or 'execute' method after creation request.
Also you can try the next solutions before I will see my code in the evening:

• $command->reset()
• read issues 3695

Nevertheless, you can attach code and error stack to better understand your problem.

Other way, you can use setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,false) for mehod batch() and after that return back attribute to true for other cases

[AstRonin]

Unfortunately, I cannot find my codes with this attribute, maybe it was a previous project. But you may use advice indicated above or show your code and we will try to help.

[cebe]

unable to reproduce the issue, see #11152

[cebe]

#9269 (comment)

[ihipop]

Any Update on this issue?

[SamMousa]

I can reproduce the issue, working on a fix.
What is the opinion of the yii team concerning configurability?
One could argue that it never makes sense to use batch in combination with buffered mode and that the default mode should be unbuffered.
Alternatively we could make the default unchanged and just add a parameter that allows explicitly disabling buffered mode (meaning it won't break BC).

Currently testing with unbuffered mode, there are some issues:

1. We need to guarantee the database schema is known (otherwise the AR layer will try to do a SHOW FULL COLUMNS FROM xx).
2. Any logic inside the batch processing cannot run queries.

I've just made a proof of concept that circumvents this issue, I'd love some feedback on it:
When running an unbuffered query, clone the database connection and use the cloned connection for the unbuffered query.

The advantage of this approach is that we can guarantee there are no issues with other queries (since the cloned connection is not accessible outside the scope of the batch query object(s)).

The downside is that it costs an extra connection (meaning connection set-up delay). Also it could give strange behavior when combined with transactions.

[SamMousa]

Also, unbuffered mode will not work with relations that are not loaded through explicit joins since these require separate queries.

We can conclude several things:

1. This is complex (should this be removed from 2.0.12?)
2. If we want truly functional stuff we need to use multiple database connections.
3. The simple approach will work in a number of specific cases only so it should not be the default behavior but instead be explicitly specified.
4. Before any specific solution can be created the Yii team needs to decide what direction they want to go in since there is no simple "just make it work in all cases" solution.

[samdark]

It doesn't make sense to batch in buffered mode. That is for sure. Issues with unbuffered mode are significant though...

[SamMousa]

Does it even make sense to have batch() as a function? I always assumed it would actually get the results one batch at a time (reducing delays caused by roundtrips), but this does not seem to be the case.

Using ->each() is just as efficient at the moment..

We could create a separate unbufferedBatch that comes with appropriate warnings (I am willing to implement it), that just clones the connection and uses that.
Not sure this needs to be in the core though, it could just as well be released as a separate yii2-big-data extension.

[samdark]

Probably not considering the difficulties.

[samdark]

I'd deprecate batch() altogether...

[samdark]

@klimov-paul, @cebe, @SilverFire, @arogachev opinions?

[beowulfenator]

@ihipop That's not a bad idea, actually. Not really ugly, and it is essentially what we do in DataProvider with pagination. @samdark, what do you think about using offset/limit to implement batch() and each()?

[SamMousa]

It is a really bad idea. Test it, the performance will be terrible. It will also not provide a consistent view of the data unless implemented with severe locking effectively blocking all writes to the table(s) in question.

…

On Apr 21, 2017 8:33 AM, "Konstantin Sirotkin" ***@***.***> wrote: @ihipop <https://github.com/ihipop> That's not a bad idea, actually. Not really ugly, and it is essentially what we do in DataProvider with pagination. @samdark <https://github.com/samdark>, what do you think about using offset/limit to implement batch() and each()? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#8420 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAhYzad6PguOPc2dpXXsix-jRZDSloN7ks5ryE2ogaJpZM4EamFy> .

[beowulfenator]

OK.

[ihipop]

@SamMousa the consistent view of the data is as mentioned

the problem is that maybe an order by primary key or something else should be done to keep the order of the dataset

[SamMousa]

@ihipop It is not.

Let me explain all my arguments against your method.

1. It is slow, for every page the whole query needs to be executed only to just take a few records from them.
2. It is inconsistent, in any scenario where the keys is not an auto incremented integer. If I have keys 1 and 15 what happens if I insert 2 during your iteration, most likely it'll be skipped or cause some other entry to be returned twice.
3. Sorting by primary key sounds simple, but for every join a sort by primary key of that table will need to be added to guarantee consistent ordering, also it breaks any other order clauses.

An alternative, if you really want one is this, it is still far from optimal and only works for integer keys. We use this method in production for slow running jobs. And it only provides an upper bound for the numbe r of results.

1. Get min and max of primary key.
2. Divide the range so that the maximum number of records it can return, including joined records fits in memory.
3. Iterate over the query using a condition like: where id > 10 and <= 20.

This approach is a lot faster but can still miss records inserted during processing. It will never return records twice.

As I've said before, no solution is valid for all cases and therefore we should not just implement something. Instead implement it yourself as a custom function or behavior in your query class.

@beowulfenator you mention buffered queries require RAM on the server; this is not clear to me. Do you mean the PHP or DB server?

[beowulfenator]

@SamMousa I mean the DB server. Executing a buffered MySQL query with the result set that is too large to fit into MySQL server's RAM brings down the MySQL server. At least that's how it is for me.

[SamMousa]

Hmm, that seems strange to me, since the buffering is happening in the PDO driver as far as I know

[ihipop]

Finally，I write a new static method to get a unBuffered Query PDO $db instance to solve my Problem at acceptable price

    public static function getUnbufferedMysqlDb($db, $db_identifier = null)
    {
        $db_string = '';
        if (is_string($db)) { //TO SUPPORT the $db of Component Definition ID passed in string  ,for example $db='db'
            $db_string = $db;
            if (empty($db_identifier)) {
                $db_identifier = $db;
            }
            $db = Yii::$app->get($db); // Convert string Component Definition ID to a Component
        }
        if (!($db instanceof \yii\db\Connection) || !strstr($db->getDriverName(), 'mysql')) { //Safe Check
            throw  new InvalidParamException('Not a Mysql Component');
        };
        if (empty($db_identifier)) { //Generate a New String Component Definition ID if $db_identifier is not Provided
            $db_identifier = md5(sprintf("%s%s%s%s", $db->dsn, $db->username, $db->password,
                var_export($db->attributes, true)));
        }
        $db_identifier = 'unbuffered_' . $db_identifier;
        if (!Yii::$app->has($db_identifier)) {
            if ($db_string) {
                $_unbuffered_db = Yii::$app->getComponents()[$db_string];//Clone a Configuration 、、克隆一个配置
                $_unbuffered_db['attributes'][\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY] = false;
            } else {
                $_ = clone $db;
                $_->close(); //Ensure that it is not an active Connection Because PDO can not be serialize
                /** @var  $_unbuffered_db \yii\db\Connection */
                $_unbuffered_db = unserialize(serialize($_)); //Clone a Expensive Object //deep copy for safe
                $_unbuffered_db->attributes[\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY] = false;
            }
            Yii::$app->setComponents([$db_identifier => $_unbuffered_db]);
        }

        return Yii::$app->get($db_identifier);
    }

When Using ,pass it to the $db parameters of batch()/each()
It Will establish a new Connection to mysql which PDO::MYSQL_ATTR_USE_BUFFERED_QUERY is false
,and Only Used for batch()/each(),Other query could be done as normal during batch()/each()

$unbuffered_db = SomeClass::getUnbufferedMysqlDb($Model::getDb());
foreach($activeQuery->batch(100,$unbuffered_db) as $foobars){
        #foobars
}

Also this acceptable.

$unbuffered_db = SomeClass::getUnbufferedMysqlDb('some-db');

@mikehaertl

[tom--]

It may be worth keeping this feature but the guide is misleading

When working with large amounts of data, methods such as yii\db\Query::all() are not suitable because they require loading all data into the memory. To keep the memory requirement low, Yii provides the so-called batch query support. A batch query makes use of the data cursor and fetches data in batches.

And then proceeds to demonstrate the methods that fail to do this.

There needs to be a warning about memory exhaustion in PDO's buffered mode and advice how to avoid it.

Some elaboration on "makes use of the data cursor" would also be welcome.

[beowulfenator]

@tom-- You're welcome to submit your PR for the documentation.

[tom--]

I believe this is the first time I have seen "PRs welcome" deployed in the Yii project.

I did not submit my PR because I do not have one. I was unable to author one because I don't understand the mechanisms Yii and its use of PDO well enough to explain the problem and advise on workarounds. I am still struggling to debug my own problem with it and the difficulty comes mostly from this lack of knowledge.

More expert contributors than myself have expressed differing opinion on what to do about this bug including scrap it, fix it, and keep it. My comment is simply that unless we scrap it pretty soon, the guide needs a fix. I also wanted to revive the discussion. I regret that now.

[ihipop]

@tom-- #8420 (comment) just do as this.
it will solve your problem ,maybe it is not the best one,but it would work

[mikehaertl]

@ihipop I tried your workaround now as my solution (i.e. "hack") did not work with with() queries. Unfortunately I found that your solution suffers from the same problem. Say you have "product has many variations". And your batch query is for products using ->with('variations'). Yii then performs a 2nd query for the related Variation records, using the main buffered db connection and an product_id IN ( ... ) condition with all the product ids.

Unfortunately it seesm like the list of these product ids is not reset for each batch. It gets bigger and bigger so that the same related records are queried again and again - plus the new ids on top.

So I still end up with an out of memory error.

UPDATE: Forget what I said - it was rather an issue with too many related records. I've decreased the batch size and it works now.

[lubobill1990]

As a 6 year developer of Yii2, I feel surprised that the each() method can exhaust memory until today, that I query on a table with a column of MEDIUMTEXT.

To sum up, basically, there are three methods to handle iterating on each record in a query set problem:

1. the current one, to leave the dirty work to pdo extension, however, pdo will exhaust memory when there are too much records or there are one column with too much data(MEDIUMTEXT for example)
2. to iterate on query with offset/limit. Pros is simplicity, cons are i) pagination become slow when offset increases, and DB can exhaust memory, ii) records will be queried twice when record insert during the query, iii) can't apply to query which already has offset/limit
3. to iterate on query with primary key range. Pros are i) it is faster than offset/limit and will never exhaust memory, ii) can avoid record queried twice, cons are i) B tree index of integer column is required(hash index doesn't support range query) ii) the result is unexpected in some requirements if we don't need the record inserted after query, iii) can't apply to query which already has offset/limit
4. to query twice, first query all the primary keys(can be implemented with current batch mechanism, leave it to pdo), then iterate on query with IN condition. Pros are i) the speed is acceptable, considering ActiveQuery::with() is implemented with IN condition, ii) can apply to any table with any kind of index, iii) Record will not repeat, iii) can apply to query which already has offset/limit. Cons are i) can exhaust memory when records exceed tens of million

One solution is to add more parameters to batch() and each(), the first is mode, indicating the implementation we need to use, another is specific parameters for the implementation, such as the index column.

But these heterogeneous parameters will pollute the batch() and each() function.

Another solution is to add several services for each implementation. The usage is like:
new RangeQueryIterator(User::find()->where(...), $batchSize))

It can decouple ActiveQuery from Iterator, which is preferred.

[samdark]

Thanks for ideas. @terabytesoftw should be interesting for Yii 3.

[lubobill1990]

An example of RangeQueryIterator:

<?php


namespace ZhiShiQ\Yii\Db;


use yii\db\ActiveQuery;
use yii\db\BatchQueryResult;

class RangeQueryIterator implements \Iterator
{
    private $query;
    private $pk;
    /**
     * @var BatchQueryResult
     */
    private BatchQueryResult $batchIterator;

    private $_batch;
    private $_key;
    private $_value;

    public function __construct(ActiveQuery $query, $batchSize = 100)
    {
        $this->query = $query;
        $this->pk = ($query->modelClass)::primaryKey();
        $this->batchIterator = (clone $query)->select($this->pk)->batch($batchSize);
    }

    /**
     * Destructor.
     */
    public function __destruct()
    {
        $this->reset();
    }

    public function reset()
    {
        $this->_batch = null;
        $this->_value = null;
        $this->_key = null;
        $this->batchIterator->reset();
    }

    public function rewind()
    {
        $this->reset();
        $this->next();
    }

    public function next()
    {
        if ($this->_batch === null || next($this->_batch) === false) {
            $this->batchIterator->next();

            $keyCols = array_map(function ($val) {
                $arr = [];
                foreach ($this->pk as $key) {
                    $arr[$key] = $val[$key];
                }
                return $arr;
            }, $this->batchIterator->current());

            $this->_batch = (clone $this->query)->andWhere(['IN', $this->pk, $keyCols])->all();
            //TODO to ensure the order is the same as $keyCols
            reset($this->_batch);
        }

        $this->_value = current($this->_batch);
        if ($this->query->indexBy !== null) {
            $this->_key = key($this->_batch);
        } elseif (key($this->_batch) !== null) {
            $this->_key = $this->_key === null ? 0 : $this->_key + 1;
        } else {
            $this->_key = null;
        }
    }

    public function key()
    {
        return $this->_key;
    }

    public function current()
    {
        return $this->_value;
    }

    public function valid()
    {
        return !empty($this->_batch);
    }
}

Usage

foreach(new RangeQueryIterator(User::find()->where(...), $batchSize)) as $user) {
    //xxx
}

[petr-panek]

As a 6 year developer of Yii2, I feel surprised that the each() method can exhaust memory until today, that I query on a table with a column of MEDIUMTEXT.

To sum up, basically, there are three methods to handle iterating on each record in a query set problem:

1. the current one, to leave the dirty work to pdo extension, however, pdo will exhaust memory when there are too much records or there are one column with too much data(MEDIUMTEXT for example)
2. to iterate on query with offset/limit. Pros is simplicity, cons are i) pagination become slow when offset increases, and DB can exhaust memory, ii) records will be queried twice when record insert during the query, iii) can't apply to query which already has offset/limit
3. to iterate on query with primary key range. Pros are i) it is faster than offset/limit and will never exhaust memory, ii) can avoid record queried twice, cons are i) B tree index of integer column is required(hash index doesn't support range query) ii) the result is unexpected in some requirements if we don't need the record inserted after query, iii) can't apply to query which already has offset/limit
4. to query twice, first query all the primary keys(can be implemented with current batch mechanism, leave it to pdo), then iterate on query with IN condition. Pros are i) the speed is acceptable, considering ActiveQuery::with() is implemented with IN condition, ii) can apply to any table with any kind of index, iii) Record will not repeat, iii) can apply to query which already has offset/limit. Cons are i) can exhaust memory when records exceed tens of million

One solution is to add more parameters to batch() and each(), the first is mode, indicating the implementation we need to use, another is specific parameters for the implementation, such as the index column.

But these heterogeneous parameters will pollute the batch() and each() function.

Another solution is to add several services for each implementation. The usage is like: new RangeQueryIterator(User::find()->where(...), $batchSize))

It can decouple ActiveQuery from Iterator, which is preferred.

Same surprice for me after 5 years of frekquent usage of each() and batch() to carefully optimize memmory usage, but no optimization happened 😠
It's for me another reason to leave this "old" world of PHP + MySQL in favor of NodeJS + MongoDB.

[terabytesoftw]

As a 6 year developer of Yii2, I feel surprised that the each() method can exhaust memory until today, that I query on a table with a column of MEDIUMTEXT.
To sum up, basically, there are three methods to handle iterating on each record in a query set problem:

1. the current one, to leave the dirty work to pdo extension, however, pdo will exhaust memory when there are too much records or there are one column with too much data(MEDIUMTEXT for example)
2. to iterate on query with offset/limit. Pros is simplicity, cons are i) pagination become slow when offset increases, and DB can exhaust memory, ii) records will be queried twice when record insert during the query, iii) can't apply to query which already has offset/limit
3. to iterate on query with primary key range. Pros are i) it is faster than offset/limit and will never exhaust memory, ii) can avoid record queried twice, cons are i) B tree index of integer column is required(hash index doesn't support range query) ii) the result is unexpected in some requirements if we don't need the record inserted after query, iii) can't apply to query which already has offset/limit
4. to query twice, first query all the primary keys(can be implemented with current batch mechanism, leave it to pdo), then iterate on query with IN condition. Pros are i) the speed is acceptable, considering ActiveQuery::with() is implemented with IN condition, ii) can apply to any table with any kind of index, iii) Record will not repeat, iii) can apply to query which already has offset/limit. Cons are i) can exhaust memory when records exceed tens of million

One solution is to add more parameters to batch() and each(), the first is mode, indicating the implementation we need to use, another is specific parameters for the implementation, such as the index column.
But these heterogeneous parameters will pollute the batch() and each() function.
Another solution is to add several services for each implementation. The usage is like: new RangeQueryIterator(User::find()->where(...), $batchSize))
It can decouple ActiveQuery from Iterator, which is preferred.

Same surprice for me after 5 years of frekquent usage of each() and batch() to carefully optimize memmory usage, but no optimization happened 😠 It's for me another reason to leave this "old" world of PHP + MySQL in favor of NodeJS + MongoDB.

Many db problems are solved in yii3, the problem is the BC.

For example the problem of varbinary, and the db cache in oracle are solved.

[SamMousa]

Can someone lock this topic. Developers moving away from php because they don't understand inner workings of stuff is really not a relevant issue. There's plenty of other languages in which those people can be bad developers. Commenting on a closed issue that's several years old just to complain about taking 6 years to understand something seems counterproductive.

[petr-panek]

Can someone lock this topic. Developers moving away from php because they don't understand inner workings of stuff is really not a relevant issue. There's plenty of other languages in which those people can be bad developers. Commenting on a closed issue that's several years old just to complain about taking 6 years to understand something seems counterproductive.

I don't want create any flame war here, but I need to react.
As I understand from the comments here the additional explanation about the each() and batch() was added to guide in 2017. I study Yii before, so there was no warning in documentation that each() and batch() can work different with MySQL and not as expected.

@SamMousa, your comment is simply arrogant (and counterproductive).

Thank you @lubobill1990 for providing solution 👍

[bizley]

Yes, thanks for the input from everyone, let's not vent our frustration like that. Please move this discussion to the forum if necessary.