mysql queries causing SLOW elgg response - elgg bug?

We recently found our elgg sites slow to a snails pace. My host traced it down as follows ...

" I found out what was causing the slow query times and higher load on your MySQL PS. It looks
like a couple optimize table queries were being run and had been running for quite some time:

***********************************************************************
--------+--------------------------+-----------------------------------------

Time    | State            | Info                
--------+--------------------------+-----------------------------------------

118278 | Waiting to get writelock | optimize table
elggsystem_log_1252406298

118278 | Waiting to get writelock | optimize table
elggsystem_log_1252406298

  83459 | Flushing tables       | NULL                
  67188 | Opening tables       | LOCK TABLES
`elggaccess_collection_membe

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

Above is the information for the running queries. The time is in seconds
so that optimize table query had been running for over 30 hours. I killed
it and now the load is back down to less than one as expected."

***********************************************************************

So my question for you pro-elggers is -- WHAT CAUSED THIS? The elgg sites were running perfectly when this happened. We had made no changes or no adjustments. It just happened. Is there an elgg bug that caused this? Or something else that you may know of?

I appreciate your assistance?

  • Ron
    It not to do w/ Elgg
    U wanna study MySql internals..

  • Well, my host came back saying it appears to be that the script that issued the query (although that doesn't explain why it was stuck). He said,

    "As for the optimize table query, that would
    generally only be run if something in the software you run initiated it
    or it was started manually. I don't believe we have anything in our
    system that initiates those automatically. "

    I have no idea what elgg does with the log tables (( elggsystem_log_1252406298 )) but if Dhrup says elgg has nothing to do with it, and the host says they don't have anything to do with it -- there must be ghosts someplace. I'm at a loss.

    Ron

  • table locks are killing you.. too many open tables.. kill all those elggsystem_log_1234567889 tbls b4 yr host kills down yr site... or ask your host tech supp if u can't get into phpmyadmin.

    ..

     

  • when you say "kill" all those elggsystem_log_1234567889 tbls. What do you mean by "kill"? I can delete them, is that what you mean when you say "kill"?

    In summary, is it ok and proper to delete the log files without causing other problems in elgg?

    ron

  • The log rotator creates the extra system log tables. You can export those for future access and then remove them from that database. The garbage collector runs table optimizations.

  • @Cash - Thanks for the info. Does it seem logical that a table optimization would take 30 hours and counting?  Appears something got stuck. At least now we know it happens, we just don't know the reason why. Maybe just a fluke. Time will tell

    So, then, are these extra log tables that are created and we can remove, ever suppose to be removed by elgg automatically? Or is this something we need to do manually on an ongoing basis?

    Thanks, -ron

  • Elgg doesn't automatically remove the logs. You can add this to the log rotating script. I think it doesn't automatically export and remove because people are going to want to store the logs different ways.

    Table optimization should not take 30 hours. I've never had a problem like that.

  • can it be related with the cron trigger? like running the optimization while it's already running? is cron trigger addon active on your site? or perhaps a restarted service on the host during an optimization or a damaged database...

  • Darkwing Duck - we've tossed all those idea around, thanks for confirming out thoughts. No proof yet in any case yet. Time will tell.

  • Cash - I had no idea it was necessary to remove the logs. This has been a good learning experience. Is this documented some place? Thanks.

Feedback and Planning

Feedback and Planning

Discussions about the past, present, and future of Elgg and this community site.