#6281: SELECT COUNT(*) ... GROUP BY causes memory spike with mysqli
+---
Reporter: sdewald | Owner:
Type: Bug |Status: new
Priority: Medium | Milestone: 1.2.x.x
Component: MySQLi | Version: 1.2 Final
Severity: Normal |Resolution:
Keywords: | Php_version: n/a
Cake_version: |
+---
Old description:
When doing a find('count', array('group' = array('Model.foo'))) the
get_peak_memory_uage() will spike. In my particular case, running this
query will use over 1gb of RAM, while the rest of the time my application
does not use more than 10mb. This was a dealbreaker for me, so I looked
into it a little more.
The problem results from the DboSource fetchAll fuction calling
$this-fetchResult() in a loop:
while ($this-hasResult() $item = $this-fetchResult()) {
$out[] = $item;
}
In the DboMysqli datasource $this-fetchResult() in turn uses
mysqli_fetch_row() to get each row the the results:
if ($row = mysqli_fetch_row($this-results)) {
...
}
This is where my understanding gets a little shoddy. For some reason
when doing a SELECT COUNT(*) ... GROUP BY the mysqli_fetch_row function
will return this array for EVERY ROW counted:
Array
(
[0] = 1
)
You can see this for yourself:
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
// in this example table 'items' has 100,000 rows
$result = $mysqli-query($result = $mysqli-query(SELECT COUNT(*) FROM
items GROUP BY items.id);
while ($row = mysqli_fetch_row($result)) {
$blah[] = $row;
}
$mysqli-close();
pr($blah);
echo(memory_get_usage());
Array
(
[0] = Array
(
[0] = 1
)
[1] = Array
(
[0] = 1
)
...
[999,999] = Array
(
[0] = 1
)
)
Hope this helps you guys find a solution. This issue causes my
application to crash from OOM every time.
New description:
When doing a find('count', array('group' = array('Model.foo'))) the
get_peak_memory_uage() will spike. In my particular case, running this
query will use over 1gb of RAM, while the rest of the time my application
does not use more than 10mb. This was a dealbreaker for me, so I looked
into it a little more.
The problem results from the DboSource fetchAll fuction calling
$this-fetchResult() in a loop:
{{{
while ($this-hasResult() $item = $this-fetchResult()) {
$out[] = $item;
}
}}}
In the DboMysqli datasource $this-fetchResult() in turn uses
mysqli_fetch_row() to get each row the the results:
{{{
if ($row = mysqli_fetch_row($this-results)) {
...
}
}}}
This is where my understanding gets a little shoddy. For some reason when
doing a SELECT COUNT(*) ... GROUP BY the mysqli_fetch_row function will
return this array for EVERY ROW counted:
{{{
Array
(
[0] = 1
)
}}}
You can see this for yourself:
{{{$mysqli = new mysqli('localhost', 'username', 'password',
'database');}}}
// in this example table 'items' has 100,000 rows
{{{
$result = $mysqli-query($result = $mysqli-query(SELECT COUNT(*) FROM
items GROUP BY items.id);
while ($row = mysqli_fetch_row($result)) {
$blah[] = $row;
}
$mysqli-close();
pr($blah);
echo(memory_get_usage());
Array
(
[0] = Array
(
[0] = 1
)
[1] = Array
(
[0] = 1
)
...
[999,999] = Array
(
[0] = 1
)
)
}}}
Hope this helps you guys find a solution. This issue causes my
application to crash from OOM every time.
Comment (by mark_story):
Fixed formatting
--
Ticket URL: https://trac.cakephp.org/ticket/6281#comment:1
CakePHP : The Rapid Development Framework for PHP https://trac.cakephp.org/
Cake is a rapid development framework for PHP which uses commonly known design
patterns like ActiveRecord, Association Data Mapping, Front Controller and MVC.
Our primary goal is to provide a structured framework that enables PHP users at
all levels to rapidly develop robust web applications, without any loss to
flexibility.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
tickets cakephp group.
To post to this group, send email to tickets-cakephp@googlegroups.com
To unsubscribe from this group, send email to
tickets-cakephp+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/tickets-cakephp?hl=en
-~--~~~~--~~--~--~---
