Moodle Community Sites

Maintenance: Gather DB statistics of moodle.org (and others)

Details

  • Type: Task Task
  • Status: Open Open
  • Priority: Major Major
  • Resolution: Unresolved
  • Component/s: moodle.org
  • Labels:
    None

Description

In the past we used to recalculate monthly DB statistics for moodle.org database. That really helps the engine to find the best execution paths for queries.

Right now, we are executing them daily at least for this DBs:

  • demo.moodle.org
  • dev.moodle.org
  • tracker.moodle.org
  • demo.moodle.org
  • demomaster.moodle.org
  • talk.moodle.org

(daily, via cron, and receiving one mail with analyze results)

It would be great to add such process to moodle.org database (weekly, in a similar fashion) and other dbs here and there

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Jordan Tomkinson added a comment -

Adding this to my todo list for maintenance

Show
Jordan Tomkinson added a comment - Adding this to my todo list for maintenance
Hide
Permalink
Jordan Tomkinson added a comment -

As moodle.org now runs InnoDB this could have draw backs.

From the MySQL reference manual: For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.

Obviously the table is locked whilst it is being ALTER'd, rebuilding table index also takes some time.

I just ran the analyze script on the DB server and it took 28 minutes - do we still want this to be scheduled, if so on what frequency?
I think daily is too frequent, perhaps monthly would be OK to start with?

Show
Jordan Tomkinson added a comment - As moodle.org now runs InnoDB this could have draw backs. From the MySQL reference manual: For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. Obviously the table is locked whilst it is being ALTER'd, rebuilding table index also takes some time. I just ran the analyze script on the DB server and it took 28 minutes - do we still want this to be scheduled, if so on what frequency? I think daily is too frequent, perhaps monthly would be OK to start with?
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

Yes, that locking is really annoying (in fact I've always thought it's a BUG, a big one).

In any case... perhaps we could do this:

1) Put moodle.org in maintenance mode* (with a nice message).
2) Run statistics
3) Put moodle.org back to normal operation

*maintenance mode is one feature within Moodle that shows one maintenance message to everybody until disabled.

About frequency... well, the often, the better (specially for InnoDB, afaik), but if weekly (sundays) is too much (causing moodle.org to be under maintenance 1/2 hour weekly), then I think we could survive with a monthly approach.

Also, perhaps... it could have sense to run statistics for some tables often, leaving some heavy tables that don't get real benefit (like logs, caches...) to be calculated less frequently.

FYC, ciao

Show
Eloy Lafuente (stronk7) added a comment - Yes, that locking is really annoying (in fact I've always thought it's a BUG, a big one). In any case... perhaps we could do this: 1) Put moodle.org in maintenance mode* (with a nice message). 2) Run statistics 3) Put moodle.org back to normal operation *maintenance mode is one feature within Moodle that shows one maintenance message to everybody until disabled. About frequency... well, the often, the better (specially for InnoDB, afaik), but if weekly (sundays) is too much (causing moodle.org to be under maintenance 1/2 hour weekly), then I think we could survive with a monthly approach. Also, perhaps... it could have sense to run statistics for some tables often, leaving some heavy tables that don't get real benefit (like logs, caches...) to be calculated less frequently. FYC, ciao

People

Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Moodle. Try JIRA - bug tracking software for your team.