Good try! But I was not clear. Try an index just for kind, and another separate index just for status. You are down to 4 x 768 (3072) from 95691. That is much better. Again, drop the create separate indexes for kind and status and index_reports_on_kind_and_status .
On Tue, Apr 12, 2011 at 2:11 PM, vincent <vinc...@louviaux.com> wrote: > 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. > -- 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.
