You are viewing a plain text version of this content. The canonical link for it is here.
Posted to olio-user@incubator.apache.org by Prashant Srinivasan <Pr...@Sun.COM> on 2009/02/25 18:50:25 UTC
Increasing MySQL CPU usage with RoR
Hi,
I'm running the RoR version; MySQL CPU consumption seems to be
increasing over time. I'm running 20 minutes steady state . . . and
would expect the CPU time to have stabilized pretty quickly into the run.
Just sending this along to see if others have seen this issue.
thanks
-ps
Re: Increasing MySQL CPU usage with RoR
Posted by Amanda Waite <Am...@Sun.COM>.
As requested:
mysql> explain SELECT count(DISTINCT `events`.id) AS count_all FROM
`events` LEFT OUTER JOIN `addresses` ON `addresses`.id =
`events`.address_id WHERE (event_date >= '2009-02-26');
+----+-------------+-----------+--------+----------------------------+---------+---------+-----------------------------------+------+-------------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
+----+-------------+-----------+--------+----------------------------+---------+---------+-----------------------------------+------+-------------+
| 1 | SIMPLE | events | ALL | index_events_on_event_date |
NULL | NULL | NULL | 6059 | Using
where |
| 1 | SIMPLE | addresses | eq_ref | PRIMARY |
PRIMARY | 4 | perf_production.events.address_id | 1 | Using
index |
+----+-------------+-----------+--------+----------------------------+---------+---------+-----------------------------------+------+-------------+
2 rows in set (0.18 sec)
Amanda
William Sobel wrote:
>
> On Feb 26, 2009, at 12:01 PM, Amanda Waite wrote:
>
>> SELECT count(DISTINCT `events`.id) AS count_all FROM `events` LEFT
>> OUTER JOIN `addresses` ON `addresses`.id = `events`.address_id WHERE
>> (event_date >= '2009-02-26')
>>
>> was really having an effect on overall DB CPU %usr time. Where I was
>> going from 28% to 54% %usr time over a 600 sec run, after modifying
>> the event_controller to not include the address when paginating the
>> events in the index action it went from 21% to 29% during the same
>> length run. It still creeps up, but less so.
>
> Interesting, this looks like it would have to be doing a table scan or
> is not using the correct index. This is what I get with MySQL, can you
> do the same on your database?
>
> mysql> explain SELECT count(DISTINCT `events`.id) AS count_all FROM
> `events` LEFT OUTER JOIN `addresses` ON `addresses`.id =
> `events`.address_id WHERE (event_date >= '2009-02-26')
> -> ;
> +----+-------------+-----------+--------+----------------------------+---------+---------+------------------------------------+------+-------------+
>
> | id | select_type | table | type | possible_keys |
> key | key_len | ref | rows |
> Extra |
> +----+-------------+-----------+--------+----------------------------+---------+---------+------------------------------------+------+-------------+
>
> | 1 | SIMPLE | events | ALL | index_events_on_event_date |
> NULL | NULL | NULL | 55 | Using
> where |
> | 1 | SIMPLE | addresses | eq_ref | PRIMARY |
> PRIMARY | 4 | perf_development.events.address_id | 1 | Using
> index |
> +----+-------------+-----------+--------+----------------------------+---------+---------+------------------------------------+------+-------------+
>
> 2 rows in set (0.03 sec)
>
> This looks correct, it's using the date index and then doing an
> eq-join on using the address' primary index. If we don't fetch the
> address in the :include it will be an extra query for each event. The
> issue may be the increased amount of data returned because of the
> product of the two tables. In 2.2.2 they changed the :include syntax
> to do it as a two part where they in first request the primary table
> and then the secondary as an in. I'll check out why it chose to
> execute the query this way.
>
> Cheers,
> - Will Sobel
>
>
Re: Increasing MySQL CPU usage with RoR
Posted by William Sobel <ws...@eecs.berkeley.edu>.
On Feb 26, 2009, at 12:01 PM, Amanda Waite wrote:
> SELECT count(DISTINCT `events`.id) AS count_all FROM `events` LEFT
> OUTER JOIN `addresses` ON `addresses`.id = `events`.address_id WHERE
> (event_date >= '2009-02-26')
>
> was really having an effect on overall DB CPU %usr time. Where I was
> going from 28% to 54% %usr time over a 600 sec run, after modifying
> the event_controller to not include the address when paginating the
> events in the index action it went from 21% to 29% during the same
> length run. It still creeps up, but less so.
Interesting, this looks like it would have to be doing a table scan or
is not using the correct index. This is what I get with MySQL, can you
do the same on your database?
mysql> explain SELECT count(DISTINCT `events`.id) AS count_all FROM
`events` LEFT OUTER JOIN `addresses` ON `addresses`.id =
`events`.address_id WHERE (event_date >= '2009-02-26')
-> ;
+----+-------------+-----------+--------+----------------------------
+---------+---------+------------------------------------+------
+-------------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows |
Extra |
+----+-------------+-----------+--------+----------------------------
+---------+---------+------------------------------------+------
+-------------+
| 1 | SIMPLE | events | ALL | index_events_on_event_date |
NULL | NULL | NULL | 55 | Using
where |
| 1 | SIMPLE | addresses | eq_ref | PRIMARY |
PRIMARY | 4 | perf_development.events.address_id | 1 | Using
index |
+----+-------------+-----------+--------+----------------------------
+---------+---------+------------------------------------+------
+-------------+
2 rows in set (0.03 sec)
This looks correct, it's using the date index and then doing an eq-
join on using the address' primary index. If we don't fetch the
address in the :include it will be an extra query for each event. The
issue may be the increased amount of data returned because of the
product of the two tables. In 2.2.2 they changed the :include syntax
to do it as a two part where they in first request the primary table
and then the secondary as an in. I'll check out why it chose to
execute the query this way.
Cheers,
- Will Sobel
Re: Increasing MySQL CPU usage with RoR
Posted by Amanda Waite <Am...@Sun.COM>.
William Sobel wrote:
> I'll take a look and see if we have a similar issue. We can add paging
> fairly easily to the attendee list.
>
> Cheers,
> - Will Sobel
>
The query:
SELECT count(DISTINCT `events`.id) AS count_all FROM `events` LEFT
OUTER JOIN `addresses` ON `addresses`.id = `events`.address_id WHERE
(event_date >= '2009-02-26')
was really having an effect on overall DB CPU %usr time. Where I was
going from 28% to 54% %usr time over a 600 sec run, after modifying the
event_controller to not include the address when paginating the events
in the index action it went from 21% to 29% during the same length run.
It still creeps up, but less so.
The change was from:
# @events = Event.paginate :page => params[:page], :conditions =>
conditions, :order => session[:order], :per_page => 10, :include => :address
To:
@events = Event.paginate :page => params[:page], :conditions =>
conditions, :order => session[:order], :per_page => 10
I've yet to evaluate the functional implication.
Amanda
Re: Increasing MySQL CPU usage with RoR
Posted by William Sobel <ws...@eecs.berkeley.edu>.
On Feb 26, 2009, at 9:07 AM, Shanti Subramanyam - PAE wrote:
> Will - since you're the person most familiar with the rails app, can
> you please figure out which operations are likely to be bitten by
> this problem ? We'll have to work around ActiveRecord if need be to
> fix the issue.
I'll take a look and see if we have a similar issue. We can add paging
fairly easily to the attendee list.
Cheers,
- Will Sobel
Re: Increasing MySQL CPU usage with RoR
Posted by Shanti Subramanyam - PAE <Sh...@Sun.COM>.
Prashant,
After taking a look at your stats, I believe the problem here is that
the rails app is the culprit - it is doing more database work as rows
are added to the database as part of the run and consequently, the
longer you run, the performance will get worse. This does not a good
benchmark make.
We discovered one such problem in the PHP app - see OLIO-52. This was
caused by the fact that we were displaying the full list of attendees
for an event. As we add events and attendees during a run, each new
EventDetail operation would fetch more data causing more work to be
done. We have fixed this in the PHP app by limiting the number of
attendees retrieved to 20. Of course, the correct solution is to provide
paging so that a user can page thru the entire list if he wants - that
will have to wait for now.
Will - since you're the person most familiar with the rails app, can you
please figure out which operations are likely to be bitten by this
problem ? We'll have to work around ActiveRecord if need be to fix the
issue.
Shanti
On 02/25/09 10:50, Prashant Srinivasan wrote:
> Hi,
> I'm running the RoR version; MySQL CPU consumption seems to be
> increasing over time. I'm running 20 minutes steady state . . . and
> would expect the CPU time to have stabilized pretty quickly into the run.
>
> Just sending this along to see if others have seen this issue.
>
> thanks
> -ps
Re: Increasing MySQL CPU usage with RoR
Posted by Shanti Subramanyam - PAE <Sh...@Sun.COM>.
Prashant,
After taking a look at your stats, I believe the problem here is that
the rails app is the culprit - it is doing more database work as rows
are added to the database as part of the run and consequently, the
longer you run, the performance will get worse. This does not a good
benchmark make.
We discovered one such problem in the PHP app - see OLIO-52. This was
caused by the fact that we were displaying the full list of attendees
for an event. As we add events and attendees during a run, each new
EventDetail operation would fetch more data causing more work to be
done. We have fixed this in the PHP app by limiting the number of
attendees retrieved to 20. Of course, the correct solution is to provide
paging so that a user can page thru the entire list if he wants - that
will have to wait for now.
Will - since you're the person most familiar with the rails app, can you
please figure out which operations are likely to be bitten by this
problem ? We'll have to work around ActiveRecord if need be to fix the
issue.
Shanti
On 02/25/09 10:50, Prashant Srinivasan wrote:
> Hi,
> I'm running the RoR version; MySQL CPU consumption seems to be
> increasing over time. I'm running 20 minutes steady state . . . and
> would expect the CPU time to have stabilized pretty quickly into the run.
>
> Just sending this along to see if others have seen this issue.
>
> thanks
> -ps