On Thu, 16 Sep 2010 05:34:33 -0700, Oliver Hookins wrote: > > On Sep 16, 12:38 pm, Ben Tullis <b...@tiger-computing.co.uk> wrote: > > Thanks for the response, but I think you've misunderstood the first > > bit. > > > > > Rails logs are not rotated by default. The rails world would use a > > > capistrano task: > > > > >http://blog.daeltar.org/logrotate-with-capistrano-generated-configura > > > > > This is creating a logrotate file - which can be done with puppet too. > > > > It's not a log file that is causing the problem, it's the sheer size > > of the database reports table. > > I have already put logrotate files in place for the rails log files, > > and they're fine. > > > > > > Might there be any indexes missing from my database? I think I > > > > installed > > > > the dashboard at version 1.0, but ran the database upgrade script > > > > between 1.0.1 and 1.0.3. > > > > > Rack::Bug is a tool you can use to get an idea about missing indexes: > > > > >http://github.com/brynary/rack-bug > > > > That's an interesting technique, but I'd rather not get into modifying > > the application itself to put diagnostics in-line, especially as it's > > in production. > > > > For reference, the indexes that I have on the reports table are these: > > > > mysql> show indexes in reports; > > +---------+------------+--------------------------+-------------- > > +-------------+-----------+-------------+----------+--------+------ > > +------------+---------+ > > | 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 | 380511 | NULL | NULL | | > > BTREE | | > > | reports | 1 | index_reports_on_node_id | 1 | > > node_id | A | 229 | NULL | NULL | YES | > > BTREE | | > > | reports | 1 | index_reports_on_time | 1 | > > time | A | 380511 | NULL | NULL | YES | > > BTREE | | > > +---------+------------+--------------------------+-------------- > > +-------------+-----------+-------------+----------+--------+------ > > +------------+---------+ > > 3 rows in set (0.06 sec) > > > > I've got mysql logging queries that can't use an index, so I'll > > analyse that to see if anything jumps out at me. > > > > This table currently has 800MB of data and uses 14MB for the indexes. > > Consider yourself lucky, mine takes up over 2GB. To save on space, if > you are using MySQL 5.1 you might consider using the InnoDB plugin and > Barracuda compressed row format. As the reports are the big space hog > and are mostly text, they compress well. > > There are some details regarding database performance on the following > ticket: > http://projects.puppetlabs.com/issues/4357 > > In particular, upgrading to 1.0.4 (which I see has just made RC1) > should help front-page performance. My latest update on the above > issue also includes how to add another index to the reports table > which speeds up front-page performance yet again. >
This new index is planned to appear in RC2, and the 1.0.4 final. -- Jacob Helwig
signature.asc
Description: Digital signature
