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+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
Mohamed Lrhazi
2011-Apr-11 12:26 UTC
Re: [Puppet Users] puppet dashboard performance issue
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 <vlouviaux@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+unsubscribe@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+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
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+unsubscribe@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+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
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+unsubscribe@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+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
query on nodes or reports table are very quick, it s with this join that the query is slow Is an index missing ? On Apr 12, 12:25 pm, 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+unsubscribe@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+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
Charles Johnson
2011-Apr-12 14:04 UTC
Re: [Puppet Users] Re: puppet dashboard performance issue
What indexes do you have on the respective tables? On Tue, Apr 12, 2011 at 5:25 AM, Vincent <vlouviaux@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+unsubscribe@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+unsubscribe@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+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
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+unsubscribe@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+unsubscribe@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+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
Charles Johnson
2011-Apr-12 14:56 UTC
Re: [Puppet Users] Re: puppet dashboard performance issue
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 <vlouviaux@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
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.johns276@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 <vlouviaux@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/puppet-users?hl=en.
Charles Johnson
2011-Apr-12 18:39 UTC
Re: [Puppet Users] Re: puppet dashboard performance issue
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 <vincent@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.johns276@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 <vlouviaux@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
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.johns276@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 <vincent@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.johns276@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
<vlouviaux@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/puppet-users?hl=en.
Charles Johnson
2011-Apr-12 20:08 UTC
Re: [Puppet Users] Re: puppet dashboard performance issue
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 <vincent@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.johns276@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 <vincent@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.johns276@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 <vlouviaux@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
the rows are respectively 19450 and 1
So I have down from 95691 to 19450
with one index on kind and one on status , it s worst
it use only index_reports_on_kind and 1 X 58631 rows ..
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,index_reports_on_status |
index_reports_on_kind | 768 | const | 58631 | 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.johns276@gmail.com>
> 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 <vincent@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.johns276@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
<vincent@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.johns276@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
<vlouviaux@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/puppet-users?hl=en.
Charles Johnson
2011-Apr-12 21:10 UTC
Re: [Puppet Users] Re: puppet dashboard performance issue
What seems the best set of indexes to you? Next, you need to read your mysql docs to "optimize table" and to reindex a table, especially if there have been lots of writes and/or lots of deletes. ~Charles~ -- 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+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
Andrew Forgue
2011-Apr-17 22:49 UTC
Re: [Puppet Users] Re: puppet dashboard performance issue
I also tried to look at this; since it was causing me issues as well. I had
800,000 rows or so in the reports table but I accidentally corrupted it
(kill -9) so I won''t have that many for a few more days.
I created the separate indexex for kind and status but MySQL didn''t use
them. It uses indexes for everything except the report.status != failed and
then it switches to a full table scan, even if the query is a simple select
with only the where and no join. The documentation for MySQL indexes says
that if MySQL thinks not using an index will be better it will not use
indexes.
mysql> explain select * from reports WHERE reports.status =
''failed'';
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: reports
type: ref
possible_keys: index_reports_on_status
key: index_reports_on_status
key_len: 258
ref: const
rows: 274
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from reports WHERE reports.status !=
''failed'';
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: reports
type: ALL
possible_keys: index_reports_on_status
key: NULL
key_len: NULL
ref: NULL
rows: 108828
Extra: Using where
1 row in set (0.00 sec)
Presumably it''s doing this because the number of rows that are !=
failed are
the vast majority so a full table scan is actually faster.
It looks like it''s selecting all the reports, and then grouping so we
get
the latest one. Doing a cartesian join and then grouping seems extremely
slow. I re-wrote the query to use a sub-select:
select reports.*,nodes.* from nodes, (select node_id,max(time) from reports
where reports.kind = ''apply'' and reports.status !=
''failed'' GROUP by
node_id) as reports WHERE reports.node_id = nodes.id;
1424 rows in set (0.12 sec).
So you''re only getting the latest report and then the nodes for each.
How fast does that query run?
-Andrew
--
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+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/puppet-users?hl=en.
no other solution ? Where this query is used in the code ? Can we disable the query ? On Apr 18, 12:49 am, Andrew Forgue <andrew.for...@gmail.com> wrote:> I also tried to look at this; since it was causing me issues as well. I had > 800,000 rows or so in the reports table but I accidentally corrupted it > (kill -9) so I won''t have that many for a few more days. > > I created the separate indexex for kind and status but MySQL didn''t use > them. It uses indexes for everything except the report.status != failed and > then it switches to a full table scan, even if the query is a simple select > with only the where and no join. The documentation for MySQL indexes says > that if MySQL thinks not using an index will be better it will not use > indexes. > > mysql> explain select * from reports WHERE reports.status = ''failed''; > *************************** 1. row *************************** > id: 1 > select_type: SIMPLE > table: reports > type: ref > possible_keys: index_reports_on_status > key: index_reports_on_status > key_len: 258 > ref: const > rows: 274 > Extra: Using where > 1 row in set (0.00 sec) > > mysql> explain select * from reports WHERE reports.status != ''failed''; > *************************** 1. row *************************** > id: 1 > select_type: SIMPLE > table: reports > type: ALL > possible_keys: index_reports_on_status > key: NULL > key_len: NULL > ref: NULL > rows: 108828 > Extra: Using where > 1 row in set (0.00 sec) > > Presumably it''s doing this because the number of rows that are != failed are > the vast majority so a full table scan is actually faster. > > It looks like it''s selecting all the reports, and then grouping so we get > the latest one. Doing a cartesian join and then grouping seems extremely > slow. I re-wrote the query to use a sub-select: > > select reports.*,nodes.* from nodes, (select node_id,max(time) from reports > where reports.kind = ''apply'' and reports.status != ''failed'' GROUP by > node_id) as reports WHERE reports.node_id = nodes.id; > 1424 rows in set (0.12 sec). > > So you''re only getting the latest report and then the nodes for each. > > How fast does that query run? > > -Andrew-- 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+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.