[rt-devel] Performance (was: Kill spam from mail)]

[Mark Vevers]

Jesse wrote

> There's a bunch of stuff that's untuned as of 1.3.70. I've put some time into
> it in the past couple of days.  What I'm currently hacking on has several 
> new indices that help considerably.   The next version of DBIx::Searchbuilder 
> also has a couple of improvements that drastically improve performance.  

Ok, just got your last CVS update for that

> 
> The two biggies are a much better Count method (when the search that's been
> described hasn't yet been performed) and Matt Knopp's amazing new
> DBIx::SearchBuilder::Record::Cachable, which caches Record objects pretty much
> completely transparently.  

I've updated my CVS working directory and also patched the DB to match the
latest Schema.  BTW Why do you create two indices each time?  The schema.pm
defines one, but you translate that to create two as the INDEX (column1, 
column2
column3) creates an index called column1.  You then create a new named index
with exactly the same parameters.

  PRIMARY KEY (id),
  INDEX (Scope, Value, Type, Owner)
  );
  CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner);

> On my stress-test database (25,000 tickets) this took queries from > 5 minutes
> to ~15 seconds. That's not as fixed as I'd like it, but it's definitely a start.

It certainly seems to have helped.  Much snappier.

> As in you're copying all the SQL commands out of the mysql logs and manually
> pasting them into the mysql monitor?

Well, at least most of them.  That's what pipes are for isn't it ;-)

>> From the cpu usage it all seems to be disappearing into apache, so I suspect
>> it is all the perl/mod-perl code. 
>> That's kind of odd and runs counter to all my experience.  
> 
I'll look at this in more detail if I get the time.

> Additionally, you should make sure that you have
> a 'perlmodule Apache::DBI' in your httpd.conf.

Already in there.

> We use placeholders (thanks to ivan) in the lookups stuff within Record.pm,
> but Searchbuilder objects are not currently using placeholders...then again
> the claim I've read is that pre-prepared SELECTS are not a speed issue with 
> mysql (though they certainly help with pg and oracle)

MySQL AB seem to think placeholders help. 

Also something else I spotted, make upgrade splats config.pm.  Good job 
I kept
a backup copy ... ;-)

Cheers
Mark

-- 
Mark Vevers.    mark at ifl.net / mvevers at rm.com
Internet Backbone Engineering Team
Internet for Learning, Research Machines Plc
Tel: +44 1235 823380,   Fax: +44 1235 823424