09 January 2014, 3:58

Hi there,

I believe in our Mahara update to version 1.6.6 something went wrong
and the database does not have the status it should have.

The tables artefact_access_role, artefact_access_usr and grouptype_roles are
still of type MyISAM.
The default engine is MyISAM also registered with the server.

We noticed that when many users started to have difficulties
adding a text box to a porfolio. That takes very long if it works at all.

In slow-query.log then is, for example, the following:

# Time: 140108  9:47:32
# User@Host: mahara[mahara] @ xxx.xxx.uni-kassel.de [xxx.xxx.xxx.xxx]
# Query_time: 158.764502  Lock_time: 0.000345 Rows_sent: 5  Rows_examined: 64850
SET timestamp=1389170852;
SELECT a.*, CAST(a.owner IS NOT NULL AND a.owner = '287' AS UNSIGNED) AS editable FROM "artefact" a
            LEFT OUTER JOIN "artefact_parent_cache" apc ON (a.id = apc.artefact AND a.institution = 'mahara' AND apc.parent = 85507) WHERE (
                a.owner = '287'
                OR a.id IN (
                    SELECT aar.artefact
                    FROM "group_member" m
                        JOIN "artefact" aa ON m.group = aa.group
                        JOIN "artefact_access_role" aar ON aar.role = m.role AND aar.artefact = aa.id
                    WHERE m.member = '287' AND aar.can_republish = 1
                )
                OR a.id IN (SELECT artefact FROM "artefact_access_usr" WHERE usr = '287' AND can_republish = 1)
                OR (apc.parent IS NOT NULL)
                OR a.institution IN ('unikassel')
            ) AND artefacttype IN('html')ORDER BY title ASC LIMIT 5;

In Moodle there is the tool "XMLDB-Editor" with which you check the consistency of the
database, is there something like that for Mahara?

Thanks,

Wesley

09 January 2014, 10:09

Hi Wesley,

It has come to our attention that sites that use mysql and have been upgraded from version 1.0 have some tables where the foreign keys and indexes are not fully correct, and this can cause slowness on big sites.

A patch for this has been applied to master branch. https://reviews.mahara.org/#/c/2744/11/htdocs/lib/db/upgrade.php

The tables that need fixing are:

artefact_access_usr
artefact_access_role
artefact_attachment
group
grouptype_roles
view_autocreate_grouptype

You should change your tables to InnoDB (http://dev.mysql.com/doc/refman/5.6/en/converting-tables-to-innodb.html) and add the missing keys.

That should sort out the slowness problems.

A good way to check what the db structure should be is to install a clean 1.6.6 Mahara and compare the db structure between it and the one you upgraded.

Cheers

Robert

10 January 2014, 3:10

Hi Robert,
thank you very much!

Mahara now works fine again ;-)

Greetings from Kassel

Wesley