[kronolith] performance on large systems

[John Madden]

> I would need to see the #s for MySQL, since I know it can struggle  
> with subselects, but I think there's some promise here. To be  
> committed, it would need to change to:
> 
> - restore checking of creator and default permissions somehow
> 
> - use $this->_table . '_users' instead of hardcoding kronolith_shares_users
> 
> - query the list of groups from the Horde_Groups object and generate  
> an IN() clause from that, instead of subselecting from  
> horde_groups_members (since the groups backend might not be SQL).
> 
> I'll upload your patch to the ticket for reference, regardless.

Yeah, understood, it was just a rough hack to get our install back on 
its feet.

> What do you think of the alternate approach of removing the bitmap  
> fields and storing the permission levels (show, read, write, delete)  
> separately instead?

I'm not as concerned with the bitmaps as I was before, it was more the 
joins in the end that were problematic for me.  In fact, I had to do 
quite a bit more work on this recently.  The query was still taking 
around 60-70ms to execute and even that was far too long for us.  (This 
site, incidentally, runs in the neighborhood of 20 million hits a day 
and Horde is a major component of that.)  By switching to UNIONs I was 
able to get it down into the sub-millisecond range and that seems to do 
nicely. =)

My current ./lib/Horde/Share/sql.php is attached.  It's a mess, original 
code still there and commented out so I'd rather not submit a patch off 
of this, but it's what we're running in production at the moment and 
again, it's quite fast.  If it's any help, please feel free to make use 
of it.

John






-- 
John Madden
Sr UNIX Systems Engineer
Ivy Tech Community College of Indiana
jmadden at ivytech.edu
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sql.php.gz
Type: application/x-gzip
Size: 7196 bytes
Desc: not available
URL: <http://lists.horde.org/archives/kronolith/attachments/20100125/da74006c/attachment.bin>