We have recently noticed our objects_entity table is growing to something quite large. The table is currently sitting around 400mb with about 500,000 entries, which matches up with about the same in the entities table so no worries there.
Can anyone confirm if this is normal behavior though? We did notice that there was a fulltext index on the title and description fields so this is probably contributing to the size of the table. Looking forward to any comments.
> We have recently noticed our objects_entity table is growing to something quite large. The > table is currently sitting around 400mb with about 500,000 entries, which matches up with
Just curious, are you using any plugin that refactors comments to be stored as objects instead of annotations? E.g. to allow likes on/threaded comments?
We aren't doing anything funky or messing with annotations or comments as a whole but we are using a threaded discussion that we developed which uses entities. This obviously will add to the size of that table over time but you could say that for any entity type.
The main problem with it being so big is the ability to search at a decent speed.
One contributor to the size maybe the site notifications. From what I can tell, if you have 100 members in a group with site notifications on, and someone uploads a file - that creates a 100 entities of the subtype message. Does that sound right?
Cheers Hayden
On 18 April 2012 16:33, Steve Clay <st...@mrclay.org> wrote:
>> We have recently noticed our objects_entity table is growing to something >> quite large. The >> table is currently sitting around 400mb with about 500,000 entries, which >> matches up with
> Just curious, are you using any plugin that refactors comments to be > stored as objects instead of annotations? E.g. to allow likes on/threaded > comments?
> -- > You received this message because you are subscribed to the Google > Groups "Elgg development" group. > To post to this group, send email to elgg-development@googlegroups.**com<elgg-development@googlegroups.com> > To unsubscribe from this group, send email to > elgg-development+unsubscribe@**googlegroups.com<elgg-development%2Bunsubscr ibe@googlegroups.com>
a. the Elgg notification system is broken in the sense that using it with groups of any size or even individuals with a lot of friends/followers can break your site. I have advised some of my clients to keep it turned off. Fixing it is supposed to be a major focus of Elgg 1.9.
b. Given that there *is* a full text index on title and description, I am puzzled as to why a table with a size of 400mb would make your site run slowly. That does not seem like a large table size to me. Properly indexed, it should run fine. You are running Elgg on a dedicated server, right?
On Wednesday, April 18, 2012 5:32:28 AM UTC+2, Hayden Shaw wrote:
> Hi all,
> We have recently noticed our objects_entity table is growing to something > quite large. The table is currently sitting around 400mb with about > 500,000 entries, which matches up with about the same in the entities table > so no worries there.
> Can anyone confirm if this is normal behavior though? We did notice that > there was a fulltext index on the title and description fields so this is > probably contributing to the size of the table. Looking forward to any > comments.
We do have some fairly large groups with a large amount of notifications set for them so I have turned off the site notifications in the interim. I think just having email notifications will be sufficient. Doesn't solve the speed issue that is existing now but next weeks job will be trying to remove the notification entities without touching the standard user to user messages from the database for testings sake to see if that makes a difference at all. However not sure if that will be possible as I don't think there's a way to tell the differentiate them in the database.
The site works smoothly until you do a 'search' for a term that is quite generic and common and then it seems to grind to a holt. The slow query log is showing it's struggling with that object table. And yep, it's sitting on it's own dedicated server.
Thanks again.
Cheers Hayden
On 18 April 2012 18:03, Kevin Jardine <kevinjard...@gmail.com> wrote:
> a. the Elgg notification system is broken in the sense that using it with > groups of any size or even individuals with a lot of friends/followers can > break your site. I have advised some of my clients to keep it turned off. > Fixing it is supposed to be a major focus of Elgg 1.9.
> b. Given that there *is* a full text index on title and description, I am > puzzled as to why a table with a size of 400mb would make your site run > slowly. That does not seem like a large table size to me. Properly indexed, > it should run fine. You are running Elgg on a dedicated server, right?
> Kevin
> On Wednesday, April 18, 2012 5:32:28 AM UTC+2, Hayden Shaw wrote:
>> Hi all,
>> We have recently noticed our objects_entity table is growing to something >> quite large. The table is currently sitting around 400mb with about >> 500,000 entries, which matches up with about the same in the entities table >> so no worries there.
>> Can anyone confirm if this is normal behavior though? We did notice that >> there was a fulltext index on the title and description fields so this is >> probably contributing to the size of the table. Looking forward to any >> comments.
>> Cheers Hayden
> -- > You received this message because you are subscribed to the Google > Groups "Elgg development" group. > To post to this group, send email to elgg-development@googlegroups.com > To unsubscribe from this group, send email to > elgg-development+unsubscribe@googlegroups.com
> a. the Elgg notification system is broken in the sense that using it with groups of any > size or even individuals with a lot of friends/followers can break your site. I have > advised some of my clients to keep it turned off. Fixing it is supposed to be a major
Do you mean simply disabling the "notifications" plugin? With it off, besides the user settings forms, what's the extend of functionality that you lose? Are e-mail notifications still sent on new content/comments?
Is it possible to just disable "Site" notifications?
Steve - just commenting out the notification handler call in messages/start.php seemed to do the trick. Won't interfere with the email notifications, they will still work.
register_notification_handler("site", "messages_site_notify_handler") - line 50.
On 19 April 2012 09:08, Steve Clay <st...@mrclay.org> wrote:
>> a. the Elgg notification system is broken in the sense that using it with >> groups of any >> size or even individuals with a lot of friends/followers can break your >> site. I have >> advised some of my clients to keep it turned off. Fixing it is supposed >> to be a major
> Do you mean simply disabling the "notifications" plugin? With it off, > besides the user settings forms, what's the extend of functionality that > you lose? Are e-mail notifications still sent on new content/comments?
> Is it possible to just disable "Site" notifications?
> -- > You received this message because you are subscribed to the Google > Groups "Elgg development" group. > To post to this group, send email to elgg-development@googlegroups.**com<elgg-development@googlegroups.com> > To unsubscribe from this group, send email to > elgg-development+unsubscribe@**googlegroups.com<elgg-development%2Bunsubscr ibe@googlegroups.com>
Now that you mention it, I vaguely remember a client who found that certain searches returned thousands of hits and that really slowed down the site. If you can investigate the problem further and report back here I think that would be useful. I think the problem was more in the design of the search system rather than the table structure itself.
On Wednesday, April 18, 2012 10:52:57 PM UTC+2, Hayden Shaw wrote:
> The site works smoothly until you do a 'search' for a term that is quite > generic and common and then it seems to grind to a holt. The slow query > log is showing it's struggling with that object table. And yep, it's > sitting on it's own dedicated server.
Really appreciate you putting further thought into this. This has
become such an issue that we have disabled search for now and added a
google custom search in the interim. It does seem to be a problem with
searches that get lots of results. If you enter quite a narrow search
term which gets few results it isn't a problem. Below is an entry from
the mysql slow query log for one search query that took 32 seconds to
run with the very broad keyword 'discussion'. We are going to fix the
problem in the interim by deleting all the extraneous notification
objects to bring the table size down - but it would be good to resolve
the issue for the time when the table may get that big with useful
data. Hayden will do some debugging on the query next week probably -
but if you have any thoughts in advance about what to look for that
would be appreciated.
Glen
# Query_time: 32.527234 Lock_time: 0.025197 Rows_sent: 1
Rows_examined: 808800
SET timestamp=1334791361;
SELECT count(DISTINCT e.guid) as total FROM elgg_entities e JOIN
elgg_objects_entity oe ON e.guid = oe.guid WHERE (MATCH
(oe.title,oe.description) AGAINST ('+\"discussion\"' IN BOOLEAN MODE))
AND ((e.type = 'object' AND e.subtype IN (21))) AND (e.site_guid IN
(1)) AND ( (e.access_id IN (2)
OR (e.owner_guid = -1)
OR (
e.access_id = 0
AND e.owner_guid = -1
)
) and e.enabled='yes');
On Apr 20, 6:51 am, Kevin Jardine <kevinjard...@gmail.com> wrote:
> Now that you mention it, I vaguely remember a client who found that certain
> searches returned thousands of hits and that really slowed down the site.
> If you can investigate the problem further and report back here I think
> that would be useful. I think the problem was more in the design of the
> search system rather than the table structure itself.
> Kevin
> On Wednesday, April 18, 2012 10:52:57 PM UTC+2, Hayden Shaw wrote:
> > The site works smoothly until you do a 'search' for a term that is quite
> > generic and common and then it seems to grind to a holt. The slow query
> > log is showing it's struggling with that object table. And yep, it's
> > sitting on it's own dedicated server.
It's the MATCH on the two fields ('title' and 'description') that seems to be killing it in our case. Remove either of 'title' or 'description' so it's just searching the one field and it runs much more promptly.
WHERE (MATCH(oe.title,oe.description) AGAINST ('+\"discussion\"' IN BOOLEAN MODE))
On 20 April 2012 09:21, Glen <glendavie...@gmail.com> wrote:
> Really appreciate you putting further thought into this. This has > become such an issue that we have disabled search for now and added a > google custom search in the interim. It does seem to be a problem with > searches that get lots of results. If you enter quite a narrow search > term which gets few results it isn't a problem. Below is an entry from > the mysql slow query log for one search query that took 32 seconds to > run with the very broad keyword 'discussion'. We are going to fix the > problem in the interim by deleting all the extraneous notification > objects to bring the table size down - but it would be good to resolve > the issue for the time when the table may get that big with useful > data. Hayden will do some debugging on the query next week probably - > but if you have any thoughts in advance about what to look for that > would be appreciated.
> Glen
> # Query_time: 32.527234 Lock_time: 0.025197 Rows_sent: 1 > Rows_examined: 808800 > SET timestamp=1334791361; > SELECT count(DISTINCT e.guid) as total FROM elgg_entities e JOIN > elgg_objects_entity oe ON e.guid = oe.guid WHERE (MATCH > (oe.title,oe.description) AGAINST ('+\"discussion\"' IN BOOLEAN MODE)) > AND ((e.type = 'object' AND e.subtype IN (21))) AND (e.site_guid IN > (1)) AND ( (e.access_id IN (2) > OR (e.owner_guid = -1) > OR ( > e.access_id = 0 > AND e.owner_guid = -1 > ) > ) and e.enabled='yes');
> On Apr 20, 6:51 am, Kevin Jardine <kevinjard...@gmail.com> wrote: > > Now that you mention it, I vaguely remember a client who found that > certain > > searches returned thousands of hits and that really slowed down the site. > > If you can investigate the problem further and report back here I think > > that would be useful. I think the problem was more in the design of the > > search system rather than the table structure itself.
> > Kevin
> > On Wednesday, April 18, 2012 10:52:57 PM UTC+2, Hayden Shaw wrote:
> > > The site works smoothly until you do a 'search' for a term that is > quite > > > generic and common and then it seems to grind to a holt. The slow > query > > > log is showing it's struggling with that object table. And yep, it's > > > sitting on it's own dedicated server.
> > > Thanks again.
> > > Cheers Hayden
> -- > You received this message because you are subscribed to the Google > Groups "Elgg development" group. > To post to this group, send email to elgg-development@googlegroups.com > To unsubscribe from this group, send email to > elgg-development+unsubscribe@googlegroups.com
Is this an index key problem then? Perhaps there needs to be a full text index key combining title and description? Any MySQL wizards around who could look at this?
On Monday, April 23, 2012 1:52:07 AM UTC+2, Hayden Shaw wrote:
> Bit of information for anyone that is interested:
> It's the MATCH on the two fields ('title' and 'description') that seems to > be killing it in our case. Remove either of 'title' or 'description' so > it's just searching the one field and it runs much more promptly.
> WHERE (MATCH(oe.title,oe.description) AGAINST ('+\"discussion\"' IN > BOOLEAN MODE))
> Is this an index key problem then? Perhaps there needs to be a full text index key > combining title and description? Any MySQL wizards around who could look at this?
It already has an index with both title and descriptions columns, although it's confusing called "title".
