puppet dashboard performance issue

[Vincent]

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

[Mohamed Lrhazi]

There's some rake tasks, and other things, suggested here:
https://github.com/puppetlabs/puppet-dashboard

> --
> You received this message because you are subscribed to the Google Groups "Puppet Users" group.
> To post to this group, send email to puppet...@googlegroups.com.
> To unsubscribe from this group, send email to puppet-users...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
>
>

[Vincent]

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

[Vincent]

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

[Vincent]

query on nodes or reports table are very quick, it s with this join
that the query is slow

Is an index missing ?

[Charles Johnson]

What indexes do you have on the respective tables?

[Vincent]

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)

[Charles Johnson]

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~

[vincent]

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)

2011/4/12 Charles Johnson <gm.jo...@gmail.com>

[Charles Johnson]

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.

[vincent]

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                                  | 
+----+-------------+---------+--------+-----------------------------------------------------------+----------------------------------+---------+---------------------------+-------+----------------------------------------------+

[Charles Johnson]

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 .

[vincent]

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                                  | 

+----+-------------+---------+--------+------------------------------------------------------------------------+-----------------------+---------+---------------------------+-------+----------------------------------------------+

[Charles Johnson]

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~ 

[Andrew Forgue]

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

[Vincent]

no other solution ?
Where this query is used in the code ?
Can we disable the query ?