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.
