[Puppet Users] Re: puppet dashboard performance issue
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+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
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+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
[Puppet Users] Re: puppet dashboard performance issue
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 = 2000 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.
[Puppet Users] Re: puppet dashboard performance issue
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 = 2000 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.
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 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 = 2000 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.
[Puppet Users] Re: puppet dashboard performance issue
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 = 2000 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.
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 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 = 2000 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
Re: [Puppet Users] Re: puppet dashboard performance issue
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 = 2000 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
Re: [Puppet Users] Re: puppet dashboard performance issue
I have add an index ALTER TABLE `reports` ADD INDEX `index_reports_on_kind_and_status` ( `kind` , `status` ) it s better but maybe not perfect as the query is performed on each pages in the dashboard Thanks Vincent mysql explain SELECT `nodes`.* FROM `nodes` INNER JOIN `reports` ON reports.node_id = nodes.id WHERE ((reports.kind = 'apply' AND reports.status != 'failed') AND (`nodes`.`hidden` = 0)) GROUP BY nodes.id; ++-+-++---+--+-+---+---+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+-++---+--+-+---+---+--+ | 1 | SIMPLE | reports | ref| index_reports_on_node_id,index_reports_on_kind_and_status | index_reports_on_kind_and_status | 768 | const | 19483 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | nodes | eq_ref | PRIMARY | PRIMARY | 4 | dashboard.reports.node_id | 1 | Using where | ++-+-++---+--+-+---+---+--+ 2 rows in set (0.00 sec) 2011/4/12 Charles Johnson gm.johns...@gmail.com A couple of things to notice. First, only the nodes.id index and index_reports_on_node_id index are used, and since index_reports_on_node_id is used as a ref type you will have 573 x 167 rows to examine (95,651 more or less) to produce the rows your sql finally fetches. You might --important: might-- be able to do better. There is an index: index_reports_on_time_and_node_id_and_status, which includes node_id and status, but is not used. Without hurting anything, and without changing the query you could create an index on reports.kind and reports.status. This might help if there are lots of different values for kind and status. For example, you generally do not want to create an index for a column where the only values are 'true' and 'false', say. That effectively divides the table in half, and if you have a million rows, reducing a full scan to 500,000 may not help much. But anyway, if you know how to create indexes, this is what I would try first. Others probably have much better ideas. You might ping a mysql list as well. On Tue, Apr 12, 2011 at 11:26 AM, vincent vinc...@louviaux.com wrote: here is the result , I don't know what to do :( mysql explain SELECT `nodes`.* FROM `nodes` INNER JOIN `reports` ON reports.node_id = nodes.id WHERE ((reports.kind = 'apply' AND reports.status != 'failed') AND (`nodes`.`hidden` = 0)) GROUP BY nodes.id ; ++-+-+---+--+--+-++--+-+ | id | select_type | table | type | possible_keys| key | key_len | ref| rows | Extra | ++-+-+---+--+--+-++--+-+ | 1 | SIMPLE | nodes | index | PRIMARY | PRIMARY | 4 | NULL | 573 | Using where | | 1 | SIMPLE | reports | ref | index_reports_on_node_id | index_reports_on_node_id | 5 | dashboard.nodes.id | 167 | Using where | ++-+-+---+--+--+-++--+-+ 2 rows in set (0.00 sec) http://nodes.id/ 2011/4/12 Charles Johnson gm.johns...@gmail.com You have a partial (i.e., three-part) index 'time', 'node_id', 'status' from which you appear to be selecting against only two parts. On the mysql command line run the query by hand preceded by the keywprd explain explain select nodes blah blah blah You should get back a listing that will let you see how the indexes are being used (or abused!). You may discover that in addition to the composite index you have that two additional indexes will help. ~Charles~ On Tue, Apr 12, 2011 at 9:15 AM, Vincent vlouvi...@gmail.com wrote: I have noticed thaht when I remove ((reports.kind = ‘apply’ AND reports.status = ‘failed’) part from the query, the query is very quick. mysql show index from reports from dashboard; +-++--
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 vinc...@louviaux.com wrote: I have add an index ALTER TABLE `reports` ADD INDEX `index_reports_on_kind_and_status` ( `kind` , `status` ) it s better but maybe not perfect as the query is performed on each pages in the dashboard Thanks Vincent mysql explain SELECT `nodes`.* FROM `nodes` INNER JOIN `reports` ON reports.node_id = nodes.id WHERE ((reports.kind = 'apply' AND reports.status != 'failed') AND (`nodes`.`hidden` = 0)) GROUP BY nodes.id ; ++-+-++---+--+-+---+---+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+-++---+--+-+---+---+--+ | 1 | SIMPLE | reports | ref| index_reports_on_node_id,index_reports_on_kind_and_status | index_reports_on_kind_and_status | 768 | const | 19483 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | nodes | eq_ref | PRIMARY | PRIMARY | 4 | dashboard.reports.node_id | 1 | Using where | ++-+-++---+--+-+---+---+--+ 2 rows in set (0.00 sec) 2011/4/12 Charles Johnson gm.johns...@gmail.com A couple of things to notice. First, only the nodes.id index and index_reports_on_node_id index are used, and since index_reports_on_node_id is used as a ref type you will have 573 x 167 rows to examine (95,651 more or less) to produce the rows your sql finally fetches. You might --important: might-- be able to do better. There is an index: index_reports_on_time_and_node_id_and_status, which includes node_id and status, but is not used. Without hurting anything, and without changing the query you could create an index on reports.kind and reports.status. This might help if there are lots of different values for kind and status. For example, you generally do not want to create an index for a column where the only values are 'true' and 'false', say. That effectively divides the table in half, and if you have a million rows, reducing a full scan to 500,000 may not help much. But anyway, if you know how to create indexes, this is what I would try first. Others probably have much better ideas. You might ping a mysql list as well. On Tue, Apr 12, 2011 at 11:26 AM, vincent vinc...@louviaux.com wrote: here is the result , I don't know what to do :( mysql explain SELECT `nodes`.* FROM `nodes` INNER JOIN `reports` ON reports.node_id = nodes.id WHERE ((reports.kind = 'apply' AND reports.status != 'failed') AND (`nodes`.`hidden` = 0)) GROUP BY nodes.id; ++-+-+---+--+--+-++--+-+ | id | select_type | table | type | possible_keys| key | key_len | ref| rows | Extra | ++-+-+---+--+--+-++--+-+ | 1 | SIMPLE | nodes | index | PRIMARY | PRIMARY | 4 | NULL | 573 | Using where | | 1 | SIMPLE | reports | ref | index_reports_on_node_id | index_reports_on_node_id | 5 | dashboard.nodes.id | 167 | Using where | ++-+-+---+--+--+-++--+-+ 2 rows in set (0.00 sec) http://nodes.id/ 2011/4/12 Charles Johnson gm.johns...@gmail.com You have a partial (i.e., three-part) index 'time', 'node_id', 'status' from which you appear to be selecting against only two parts. On the mysql command line run the query by hand preceded by the keywprd explain explain select nodes blah blah blah You should get back a listing that will let you see how the indexes are being used (or abused!). You may discover that in addition to the composite index you have that two additional indexes will help. ~Charles~
Re: [Puppet Users] Re: puppet dashboard performance issue
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.johns...@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 vinc...@louviaux.com wrote: I have add an index ALTER TABLE `reports` ADD INDEX `index_reports_on_kind_and_status` ( `kind` , `status` ) it s better but maybe not perfect as the query is performed on each pages in the dashboard Thanks Vincent mysql explain SELECT `nodes`.* FROM `nodes` INNER JOIN `reports` ON reports.node_id = nodes.id WHERE ((reports.kind = 'apply' AND reports.status != 'failed') AND (`nodes`.`hidden` = 0)) GROUP BY nodes.id; ++-+-++---+--+-+---+---+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+-++---+--+-+---+---+--+ | 1 | SIMPLE | reports | ref| index_reports_on_node_id,index_reports_on_kind_and_status | index_reports_on_kind_and_status | 768 | const | 19483 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | nodes | eq_ref | PRIMARY | PRIMARY | 4 | dashboard.reports.node_id | 1 | Using where | ++-+-++---+--+-+---+---+--+ 2 rows in set (0.00 sec) 2011/4/12 Charles Johnson gm.johns...@gmail.com A couple of things to notice. First, only the nodes.id index and index_reports_on_node_id index are used, and since index_reports_on_node_id is used as a ref type you will have 573 x 167 rows to examine (95,651 more or less) to produce the rows your sql finally fetches. You might --important: might-- be able to do better. There is an index: index_reports_on_time_and_node_id_and_status, which includes node_id and status, but is not used. Without hurting anything, and without changing the query you could create an index on reports.kind and reports.status. This might help if there are lots of different values for kind and status. For example, you generally do not want to create an index for a column where the only values are 'true' and 'false', say. That effectively divides the table in half, and if you have a million rows, reducing a full scan to 500,000 may not help much. But anyway, if you know how to create indexes, this is what I would try first. Others probably have much better ideas. You might ping a mysql list as well.
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+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
[Puppet Users] Re: puppet dashboard performance issue
Thanks I have make the rake RAILS_ENV=production db:raw:optimize and in the my.cnf query_cache_size = 2000 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.
