I have add an index
ALTER TABLE `reports` ADD INDEX `index_reports_on_kind_and_status` ( `kind`
, `status` )
it s better but maybe not perfect as the query is performed on each pages in
the dashboard
Thanks
Vincent
mysql> explain SELECT `nodes`.* FROM `nodes` INNER JOIN `reports` ON
reports.node_id = nodes.id WHERE ((reports.kind = 'apply' AND
reports.status != 'failed') AND (`nodes`.`hidden` = 0)) GROUP BY nodes.id;
+----+-------------+---------+--------+-----------------------------------------------------------+----------------------------------+---------+---------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref
| rows | Extra |
+----+-------------+---------+--------+-----------------------------------------------------------+----------------------------------+---------+---------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | reports | ref |
index_reports_on_node_id,index_reports_on_kind_and_status |
index_reports_on_kind_and_status | 768 | const |
19483 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | nodes | eq_ref | PRIMARY
| PRIMARY | 4 |
dashboard.reports.node_id | 1 | Using where
|
+----+-------------+---------+--------+-----------------------------------------------------------+----------------------------------+---------+---------------------------+-------+----------------------------------------------+
2 rows in set (0.00 sec)
2011/4/12 Charles Johnson <gm.johns...@gmail.com>
> A couple of things to notice. First, only the nodes.id index and
> index_reports_on_node_id
> index are used, and since index_reports_on_node_id is used as a ref type
> you will have 573 x 167 rows to examine (95,651 more or less) to produce the
> rows your sql finally fetches. You might --important: might-- be able to do
> better. There is an index: index_reports_on_time_and_node_id_and_status,
> which includes node_id and status, but is not used. Without hurting
> anything, and without changing the query you could create an index on
> reports.kind and reports.status. This might help if there are lots of
> different values for kind and status. For example, you generally do not want
> to create an index for a column where the only values are 'true' and
> 'false', say. That effectively divides the table in half, and if you have a
> million rows, reducing a full scan to 500,000 may not help much.
>
> But anyway, if you know how to create indexes, this is what I would try
> first.
>
> Others probably have much better ideas. You might ping a mysql list as
> well.
>
> On Tue, Apr 12, 2011 at 11:26 AM, vincent <vinc...@louviaux.com> wrote:
>
>> here is the result , I don't know what to do :(
>>
>> mysql> explain SELECT `nodes`.* FROM `nodes` INNER JOIN `reports` ON
>> reports.node_id = nodes.id WHERE ((reports.kind = 'apply' AND
>> reports.status != 'failed') AND (`nodes`.`hidden` = 0)) GROUP BY nodes.id
>> ;
>>
>> +----+-------------+---------+-------+--------------------------+--------------------------+---------+--------------------+------+-------------+
>> | id | select_type | table | type | possible_keys | key
>> | key_len | ref | rows | Extra |
>>
>> +----+-------------+---------+-------+--------------------------+--------------------------+---------+--------------------+------+-------------+
>> | 1 | SIMPLE | nodes | index | PRIMARY | PRIMARY
>> | 4 | NULL | 573 | Using where |
>> | 1 | SIMPLE | reports | ref | index_reports_on_node_id |
>> index_reports_on_node_id | 5 | dashboard.nodes.id | 167 | Using
>> where |
>>
>> +----+-------------+---------+-------+--------------------------+--------------------------+---------+--------------------+------+-------------+
>> 2 rows in set (0.00 sec)
>>
>> <http://nodes.id/>
>>
>> 2011/4/12 Charles Johnson <gm.johns...@gmail.com>
>>
>>> You have a partial (i.e., three-part) index 'time', 'node_id', 'status'
>>> from which you appear to be selecting against only two parts. On the mysql
>>> command line run the query by hand preceded by the keywprd "explain"
>>>
>>> explain select nodes blah blah blah
>>>
>>> You should get back a listing that will let you see how the indexes are
>>> being used (or abused!). You may discover that in addition to the composite
>>> index you have that two additional indexes will help.
>>>
>>> ~Charles~
>>>
>>>
>>> On Tue, Apr 12, 2011 at 9:15 AM, Vincent <vlouvi...@gmail.com> wrote:
>>>
>>>> I have noticed thaht when I remove ((reports.kind = ‘apply’ AND
>>>> reports.status = ‘failed’) part from the query, the query is very
>>>> quick.
>>>>
>>>> mysql> show index from reports from dashboard;
>>>> +---------+------------+----------------------------------------------
>>>> +--------------+-------------+-----------+-------------+----------
>>>> +--------+------+------------+---------+
>>>> | Table | Non_unique | Key_name
>>>> | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
>>>> Packed | Null | Index_type | Comment |
>>>> +---------+------------+----------------------------------------------
>>>> +--------------+-------------+-----------+-------------+----------
>>>> +--------+------+------------+---------+
>>>> | reports | 0 | PRIMARY
>>>> | 1 | id | A | 112252 | NULL |
>>>> NULL | | BTREE | |
>>>> | reports | 1 | index_reports_on_node_id
>>>> | 1 | node_id | A | 652 | NULL |
>>>> NULL | YES | BTREE | |
>>>> | reports | 1 | index_reports_on_time_and_node_id_and_status
>>>> | 1 | time | A | 112252 | NULL |
>>>> NULL | YES | BTREE | |
>>>> | reports | 1 | index_reports_on_time_and_node_id_and_status
>>>> | 2 | node_id | A | 112252 | NULL |
>>>> NULL | YES | BTREE | |
>>>> | reports | 1 | index_reports_on_time_and_node_id_and_status
>>>> | 3 | status | A | 112252 | NULL |
>>>> NULL | YES | BTREE | |
>>>> +---------+------------+----------------------------------------------
>>>> +--------------+-------------+-----------+-------------+----------
>>>> +--------+------+------------+---------+
>>>> 5 rows in set (0.01 sec)
>>>>
>>>>
>>>>
>>>> On Apr 12, 4:04 pm, Charles Johnson <gm.johns...@gmail.com> wrote:
>>>> > What indexes do you have on the respective tables?
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> > On Tue, Apr 12, 2011 at 5:25 AM, Vincent <vlouvi...@gmail.com> wrote:
>>>> > > I have increase the mysql like this but the queries takes still more
>>>> > > than 9sec for the first page.
>>>> > > any other suggestions for the mysql tuning ?
>>>> >
>>>> > > key_buffer=64M
>>>> > > sort_buffer=4M
>>>> > > query_cache_size = 20M
>>>> > > read_buffer_size=1M
>>>> >
>>>> > > Node Load (9090.5ms) SELECT `nodes`.* FROM `nodes` INNER JOIN
>>>> > > `reports` ON reports.node_id = nodes.id WHERE ((reports.kind =
>>>> 'apply'
>>>> > > AND reports.status != 'failed') AND (`nodes`.`hidden` = 0)) GROUP BY
>>>> > > nodes.id
>>>> > > Node Load (9082.9ms) SELECT `nodes`.* FROM `nodes` INNER JOIN
>>>> > > `reports` ON reports.node_id = nodes.id WHERE ((reports.kind =
>>>> 'apply'
>>>> > > AND reports.status = 'failed') AND (`nodes`.`hidden` = 0)) GROUP BY
>>>> > > nodes.id
>>>> >
>>>> > > On Apr 11, 4:19 pm, Vincent <vlouvi...@gmail.com> wrote:
>>>> > > > Thanks
>>>> >
>>>> > > > I have make the
>>>> > > > rake RAILS_ENV=production db:raw:optimize
>>>> > > > and in the my.cnf
>>>> > > > query_cache_size = 20000000
>>>> >
>>>> > > > Its really better
>>>> >
>>>> > > > Vincent
>>>> >
>>>> > > > On 11 avr, 14:26, Mohamed Lrhazi <lrh...@gmail.com> wrote:
>>>> >
>>>> > > > > There's some rake tasks, and other things, suggested here:
>>>> > >https://github.com/puppetlabs/puppet-dashboard
>>>> >
>>>> > > > > On Mon, Apr 11, 2011 at 5:29 AM, Vincent <vlouvi...@gmail.com>
>>>> wrote:
>>>> > > > > > Hello,
>>>> >
>>>> > > > > > since the last upgrade to V1.1.0
>>>> > > > > > The dashboard is very slow
>>>> >
>>>> > > > > > I notice this slow queries in the log :
>>>> >
>>>> > > > > > Node Load (14178.9ms) SELECT `nodes`.* FROM `nodes` INNER
>>>> JOIN
>>>> > > > > > `reports` ON reports.node_id = nodes.id WHERE ((reports.kind
>>>> =
>>>> > > 'apply'
>>>> > > > > > AND reports.status != 'failed') AND (`nodes`.`hidden` = 0))
>>>> GROUP BY
>>>> > > > > > nodes.id
>>>> > > > > > Node Load (13149.9ms) SELECT `nodes`.* FROM `nodes` INNER
>>>> JOIN
>>>> > > > > > `reports` ON reports.node_id = nodes.id WHERE ((reports.kind
>>>> =
>>>> > > 'apply'
>>>> > > > > > AND reports.status = 'failed') AND (`nodes`.`hidden` = 0))
>>>> GROUP BY
>>>> > > > > > nodes.id
>>>> >
>>>> > > > > > How can i optimize the DB ?
>>>> >
>>>> > > > > > Vincent
>>>> >
>>>> > > > > > --
>>>> > > > > > You received this message because you are subscribed to the
>>>> Google
>>>> > > Groups "Puppet Users" group.
>>>> > > > > > To post to this group, send email to
>>>> puppet-users@googlegroups.com.
>>>> > > > > > To unsubscribe from this group, send email to
>>>> > > puppet-users+unsubscr...@googlegroups.com.
>>>> > > > > > For more options, visit this group athttp://
>>>> > > groups.google.com/group/puppet-users?hl=en.
>>>> >
>>>> > > --
>>>> > > You received this message because you are subscribed to the Google
>>>> Groups
>>>> > > "Puppet Users" group.
>>>> > > To post to this group, send email to puppet-users@googlegroups.com.
>>>> > > To unsubscribe from this group, send email to
>>>> > > puppet-users+unsubscr...@googlegroups.com.
>>>> > > For more options, visit this group at
>>>> > >http://groups.google.com/group/puppet-users?hl=en.
>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google
>>>> Groups "Puppet Users" group.
>>>> To post to this group, send email to puppet-users@googlegroups.com.
>>>> To unsubscribe from this group, send email to
>>>> puppet-users+unsubscr...@googlegroups.com.
>>>> For more options, visit this group at
>>>> http://groups.google.com/group/puppet-users?hl=en.
>>>>
>>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "Puppet Users" group.
>>> To post to this group, send email to puppet-users@googlegroups.com.
>>> To unsubscribe from this group, send email to
>>> puppet-users+unsubscr...@googlegroups.com.
>>> For more options, visit this group at
>>> http://groups.google.com/group/puppet-users?hl=en.
>>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Puppet Users" group.
>> To post to this group, send email to puppet-users@googlegroups.com.
>> To unsubscribe from this group, send email to
>> puppet-users+unsubscr...@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/puppet-users?hl=en.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Puppet Users" group.
> To post to this group, send email to puppet-users@googlegroups.com.
> To unsubscribe from this group, send email to
> puppet-users+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/puppet-users?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"Puppet Users" group.
To post to this group, send email to puppet-users@googlegroups.com.
To unsubscribe from this group, send email to
puppet-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/puppet-users?hl=en.
