Unexplained linear increase in MySQL SELECT time for identical queries in a loop?

Question

PHP

Good afternoon! We ran into a very strange problem.
Unfortunately, I can't provide code snippets. But I will try to describe the essence.
Maybe someone faced a problem and according to my information it will be clear what we are doing wrong.
We have PHP 5.2.17 with Suhosin-Patch in CLI mode. The console application reads a certain array of users from Bitrix via the Bitrix API. Further, in a cycle, for each user there is a request for obtaining certain properties.
And so, for each new user time for request increases.
For example, for user 1, the request took 0.00002 seconds, and for user 10000, this is already 0.54323 seconds. Time increases always linearly. The memory occupied by the application also grows.
Tried on different operating systems (FreeBSD, latest Ubuntu, Windows) - everywhere is the same. We tried to set both a permanent connection to the database and NOT a permanent one (in the Bitrix config). We even tried to disconnect from the database every 100 iterations and reconnect by force. We clean all the variables. Already weary.
Question: what could be the reason for such degradation? The code seems to have already been polished and tested by several very good programmers.
Could this be due to the fact that Bitrix uses the standard PHP Mysql module?
Thanks in advance!
upd. Added code example:

 $subscriber = new Subscribers();
 
$arSubscriberList = $subscriber->getList();
 
$i = 0;
 
while ($i<=count($arSubscriberList)) {
 
 $subscriber = $arSubscriber[$i];
 
 $sql = 'SELECT NAME,LAST_NAME FROM b_user WHERE EMAIL='.subscriber['email'].';';
 $resSubscriber = $DB->Query($sql);
 
 $result[] = $resSubscriber->Fetch();
 
 $i++
}

UPD. Problem solved.
Who cares: everything was decided by rewriting our classes to singleton.
We thought of this only after using the xhprof profiler, or rather XH Gui.

Answer 1

Does this slowdown apply only to one launch, or to each subsequent one? To begin with, I would install a profiler and look at its memory report for a method in which a memory leak occurs.
Oh, and one more thing: why not get all the properties for all users at once with one request, and then group and form the desired $result?

Answer 2

I would try to implement the same example in pure php first, at least send a request and see the results.
if there is no time loss, congratulations, Bitrix.
*but on topic, but I would write the code like this
$subscriber = new Subscribers(); $arSubscriberList = $subscriber->getList(); foreach($arSubscriberLis as $subscriber) { $sql = 'SELECT NAME,LAST_NAME FROM b_user WHERE EMAIL='.subscriber['email'].';'; $resSubscriber = $DB->Query($sql); $result[] = $resSubscriber->Fetch(); }

Answer 3

Try streaming if possible.
As far as I understand, you collect all the data together in $result. If there is really a lot of data, then there may simply not be enough memory for the cache, or even for operation without swapping.

Answer 4

1 Config to the studio
2 Tried to execute the request in the console?
3 Is the cache configured in the muscle?

Answer 5

It's hard to tell without code.
Maybe there is a loop somewhere, or recursive calls.

Answer 6

How do you measure query execution time?
$subscriber->getList() - returns an array?

Answer 7

You are using the base inefficiently. Every time you make a request past the cache and even through a bunch of shims. If the cache is set to a large enough value, then it will grow until it hits the limit.
For a good request, there should be one - "take all users along with the name."
Although it’s hard for me to imagine why you suddenly needed all the users at once.
I hope everything is fine with the indexes in the database.

Answer 8

Of course, I'm an ass-handed pervert, but why can't we make a subscribers table, fill it once with all the values ​​​​when adding a subscriber, and stupidly pull it out of it when mailing?
And yet, in the order of delirium, the problem may be in the puff, at the stage of allocating RAM. Try for test 5.4 without changing the rest. I don't remember what version I had this problem on.

Answer 9

Who cares: everything was decided by rewriting our classes to singleton.
We thought of this only after using the xhprof profiler, or rather XH Gui.