Use of CAST() causes extreme slowdown in large MySQL sites

Bug #1081947 reported by Andrea Gordon on 2012-11-22
16
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Mahara
High
Robert Lyon
1.6
High
Unassigned
1.7
High
Son Nguyen
1.8
High
Unassigned

Bug Description

Mahara version 1.5.2
Linux CentOS release 5.8
PHP Version 5.3.15
MySQL 5.0.77

When editing a page and trying to add a normal text box by dragging it into the page, this loads for approx 2 minutes or more and then eventually appears.

It happens with a journal too but all others are fine and are instant as they should be.

I'm not getting any apache log errors for this nor general server errors. The only thing I am able to see is the query that it hangs on for this length of time.... it is the below...

I hope someone can help as obviously this is causing quite a lot of issues for the users!! Anyone able to diagnose what is the issue here?

The thing is, there is another exact version of the Mahara site alongside this one but just a blank version which runs perfectly fine so this must be an issue within the database somewhere or the maharadata.

Query below: this hangs for about 1 minute 45..

SELECT a.*, CAST(a.owner IS NOT NULL AND a.owner = '1739' 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 = 371) WHERE (
                a.owner = '1739'
                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 = '1739' AND aar.can_republish = 1
                )
                OR a.id IN (SELECT artefact FROM "artefact_access_usr" WHERE usr = '1739' AND can_republish = 1)
                OR a.institution IN ('test','mahara')
            ) AND artefacttype IN('blog')ORDER BY title ASC LIMIT 10 |

Then this one for the rest of the time until eventually the text box or journal appears on the page:

SELECT COUNT(*) FROM "artefact" a
            LEFT OUTER JOIN "artefact_parent_cache" apc ON (a.id = apc.artefact AND a.institution = 'mahara' AND apc.parent = 371) WHERE (
                a.owner = '1739'
                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 = '1739' AND aar.can_republish = 1
                )
                OR a.id IN (SELECT artefact FROM "artefact_access_usr" WHERE usr = '1739' AND can_republish = 1)
                OR a.institution IN ('test','mahara')
            ) AND artefacttype IN('blogpost') |

Thank you for your help

ok........ we have managed to fix this in case anyone was wondering..........

it was happening just due to the huge size of the database..

we added two new indexes to the below tables:

artefact_access_role
artefact_access_usr

index type = index, column = artefact and saved...

all of the sudden - instant results...

maybe something to add into the database for installations in future! :-)

Changed in mahara:
status: New → Confirmed
importance: Undecided → Medium
milestone: none → 1.7.0
Yaju Mahida (yvm) wrote :
Download full text (7.0 KiB)

In December 2012 we upgraded to Mahara 1.5.4 and since the upgrade the apache error log was full with "MySQL server has gone away". After investigation we found two very slow queries related to TextBox.

To solve this we put the index create index arteaccerole_roleart_ix on artefact_access_role (role,artefact) which temporarily solved this issue but still it is very slow taking around 16 seconds for text box to create/open and save.

With so many concurrent users this slow downs the whole system consuming all MySQL resources and complains with the error "Failed to get a recordset: mysql error: [2013: Lost connection to MySQL server during query] in EXECUTE("SELECT COUNT(*) FROM "artefact" a , referer: https://mahara.localhost/view/blocks.php?id=50000".

Recently our functional support person reported following statistics after this fix.

The text box functionality has slowed a little – but still very usable:
Approx. 16 seconds for Text box: Configure to pop up
Approx. 16-21 seconds for the Text box to save (with just plain text in it)
There were 13 users online at time of test

The profile information block is slow :
Approx. 62 seconds for Profile information: Configure to pop up
Approx. 58 seconds for the Profile information to save
There were 10 users online at time of test

In peak days we have seen around 500 users logging on and I am scared to think that what will happen when there will be same heavy load!

---------------------------------------------------------------------
Investigation about the following two queries
---------------------------------------------------------------------

SELECT a.*, CAST(a.owner IS NOT NULL AND a.owner = '3542' 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 = 4)
WHERE (
  a.owner = '3542'
  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 = '3542' AND aar.can_republish = 1
  )
  OR a.id IN (SELECT artefact FROM artefact_access_usr WHERE usr = '3542' AND can_republish = 1)
  OR a.institution IN ('XXX','mahara')
 ) AND artefacttype IN('html') ORDER BY title ASC limit 5;

SELECT COUNT(*) FROM `artefact` a
            LEFT OUTER JOIN `artefact_parent_cache` apc ON (a.id = apc.artefact AND a.institution = 'mahara' AND apc.parent = 4) WHERE (
                a.owner = '3542'
                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 = '3542' AND aar.can_republish = 1
                )
                OR a.id IN (SELECT artefact FROM `artefact_access_usr` WHERE usr = '3542' AND can_republish = 1)
                OR (apc.parent IS NOT NULL)
                OR a.institution IN ('XXX')
            ) AND artefacttype IN('html');

-------------------------------
Without any indexes
---------------------...

Read more...

Aaron Wells (u-aaronw) on 2013-04-19
Changed in mahara:
milestone: 1.7.0 → 1.8.0
Donna Hrynkiw (donna-hrynkiw) wrote :

So let me get this straight.

We reported a problem with the use of CAST in the upgrade script -- and that was fixed, but it's still present in the Mahara codebase in other locations. So we can successfully upgrade (v1.4.0 to v1.5.11), but we can't *use* Mahara.

Erg!

Rahim Virani (rahim-virani) wrote :

Hi Aaron and the Mahara Community,

First of all thanks for fixing the following bug: https://bugs.launchpad.net/mahara/+bug/1039865.

As Donna indicates we were able to successfully upgrade to 1.5 but it is not usable. It seems there is quite a culture of ridicule surrounding MySQL among Mahara developers (referring to: https://mahara.org/interaction/forum/topic.php?id=302) even though MySQL is listed as being officially supported (https://wiki.mahara.org/index.php/System_Administrator's_Guide/Requirements).

We first implemented Mahara a long long time ago (we pre-date the 1.0 release), at the time I do not believe that Postgres was listed as the "recommended" option.

In the event that the CAST function will continue to cause problems with our Mahara installation (I realize this is a MySQL performance issue and Mahara is simply working around the performance issue -- again thank you). Do you know of any large installations that have migrated from MySQL to Postgres, we are stuck on 1.4 for now as it seems it is the last version that functioned properly with MySQL out of the box, we are mindful of the strong recommendation to move off of 1.4.

Is there a data migration script to move from MySQL to Postgres?
How would you recommend approach a migration?

I have pasted recent site statistics for your reference, any helpful guidance would be appreciated, personally we not committed to either DBMS (we do have more experience with MySQL) but if we need to move to Postgres under the communities recommendation then we will have no choice, I just cant see an easy migration path.

Site installed: 29 August 2008

Users: 1046
 Active users: 2 today, 147 since 02 August, 926 all time

Groups: 381
 On average, each user is in 2.1 groups

Pages: 28680
 Users who make pages have about 25.3 pages each

Database size: 1GB

Disk usage: 19.3GB

Mahara version: 1.5.11 (latest version is 1.7.2)

Thanks again for everyone's attention to these issues.

Rahim Virani (rahim-virani) wrote :

After doing some additional research it seems that most Mahara Users are on MySQL:
https://mahara.org/user/nigel

Aaron Wells (u-aaronw) wrote :

Sorry about the frustration. We try to fix issues as soon as we can, but we've got limited resources. :(

Really the main limitation on MySQL support is that the Mahara lead developers, my employer Catalyst IT (http://www.catalyst.net.nz), is an organization that primarily uses Postgres. So, pretty much the entirety of our paid Mahara development work is done on Postgres, because that's what all our clients are using, and consequently MySQL-specific work has to come out of our non-funded general bug fixing time, or from community code contributions. We do have one Mahara client running MySQL now, though, so you may see more active MySQL bug fixing in the future.

We would welcome a patch for this issue. On a quick search through the code base I see only about 12 instances of CAST() still present, so in theory it shouldn't be too much work to solve.

I haven't migrated a large site from MySQL to Postgres before, so I can't offer any specific insights into the process. There is a page about it on the Postgres site: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL . There was also a comment thread about it on mahara.org recently, in which Dajan suggested checking the Moodle forums for advice, since it does come up there occasionally. https://mahara.org/interaction/forum/topic.php?id=5573

Sorry I can't be of more help!

Aaron Wells (u-aaronw) on 2013-09-30
Changed in mahara:
milestone: 1.8rc1 → 1.8.0
Aaron Wells (u-aaronw) on 2013-10-22
Changed in mahara:
milestone: 1.8.0 → 1.8.1
summary: - Adding Journal or Text box to a page takes approx 2 minutes to load
+ Use of CAST() causes extreme slowdown in large MySQL sites
tags: added: mysql optimization
Changed in mahara:
importance: Medium → High
Robert Lyon (robertl-9) wrote :

From what I've found during initial investigations is that in Mahara v1.0 the artefact_access_role and artefact_access_usr tables did not exist - they were added in version 1.1.

If one does a fresh install of Mahara using v1.1 or later they get those two tables with the correct constraints/keys/foreign keys

However if they upgrade from version 1.0 they get those two tables missing all of the constraints/keys/foreign keys.

I've upgraded all the way form 1.0 to 1.8 in mysql and the keys are missing.

What is needed to start with is a patch to add the missing keys onto sites that have been upgraded from Mahara v1.0

Changed in mahara:
assignee: nobody → Robert Lyon (robertl-9)
Robert Lyon (robertl-9) wrote :

I've created a couple of patches - the first addresses the problem where if a site was first created with version 1.0 or earlier there were missing keys on some fields. The patch checks on upgrade if the keys exist and if not adds them
https://reviews.mahara.org/#/c/2744/

The second patch addresses a couple of places where CAST() is used for mysql sql queries.
https://reviews.mahara.org/#/c/2748/

If someone has a large mysql site can they check that patch 2 does
 A) work - eg one still gets the same results when using it as they do when it is not in place, and
 B) that it makes for faster db calls

Cheers

Robert

Changed in mahara:
status: Confirmed → In Progress
Aaron Wells (u-aaronw) wrote :

I spun off bug https://bugs.launchpad.net/mahara/+bug/1255378 for creating a proper method to check whether a key already exists in the database, as part of patch 2744

Robert Lyon (robertl-9) wrote :

Other places where db structure differs if upgrading from 1.0 vs fresh master (1.9dev) install.

I installed 1.0 with mysql then upgraded it thru each version until master - I then did another mysql install with master and checked what was different.

Table: `artefact_attachment`
missing: KEY `arteatta_art_ix` (`artefact`),

Table: `group`
missing: CONSTRAINT `grou_gro_fk` FOREIGN KEY (`grouptype`) REFERENCES `grouptype` (`name`),

Table: `grouptype_roles`
missing: PRIMARY KEY (`grouptype`,`role`),
               KEY `grourole_gro_ix` (`grouptype`),
               CONSTRAINT `grourole_gro_fk` FOREIGN KEY (`grouptype`)
               REFERENCES `grouptype` (`name`)

Table: `view_autocreate_grouptype`
missing: PRIMARY KEY (`view`,`grouptype`),
               KEY `viewautogrou_vie_ix` (`view`),
               KEY `viewautogrou_gro_ix` (`grouptype`),
               CONSTRAINT `viewautogrou_vie_fk` FOREIGN KEY (`view`)
               REFERENCES `view` (`id`),
               CONSTRAINT `viewautogrou_gro_fk` FOREIGN KEY (`grouptype`)
               REFERENCES `grouptype` (`name`)

Table: `view_rows_columns`
missing column: `id` bigint(10) NOT NULL AUTO_INCREMENT,
missing: PRIMARY KEY (`id`),

Aaron Wells (u-aaronw) wrote :

I've reassigned patch https://reviews.mahara.org/#/c/2744/ to https://bugs.launchpad.net/mahara/+bug/1174623 (correct schema drift in 1.0 -> 1.8 upgrades)

Reviewed: https://reviews.mahara.org/2748
Committed: http://gitorious.org/mahara/mahara/commit/2b35e42a9233a9951204cf29a8206afbd3cbb4a8
Submitter: Aaron Wells (<email address hidden>)
Branch: master

commit 2b35e42a9233a9951204cf29a8206afbd3cbb4a8
Author: Robert Lyon <email address hidden>
Date: Tue Nov 26 15:06:22 2013 +1300

Getting mysql not to use CAST() (bug #1081947)

In the 1.8+ codebase there are only two places where both mysql and
postgres use CAST(). So I've tried altering the calls so that mysql
doens't need to use CAST().

This will probably need to be tested with a large data mysql db site
to see if any speed gains are made, and also the calls are still
working correctly.

All other places it is being used by postgres only
Signed-off-by: Robert Lyon <email address hidden>

Change-Id: I9eaf196d062ef2a62bfdae0df179281f3411c392

Aaron Wells (u-aaronw) wrote :

Backporting https://reviews.mahara.org/#/c/2748/ (which is the patch for the CAST() issue specifically) to 1.8 for the 1.8.1 release. When we do the next 1.7 & 1.6 releases, we can backport it there as well.

Mahara Bot (dev-mahara) wrote :

Reviewed: https://reviews.mahara.org/2814
Committed: http://gitorious.org/mahara/mahara/commit/d52499125a0bb8cdab342aaab0cb0cb4e3efce16
Submitter: Aaron Wells (<email address hidden>)
Branch: 1.8_STABLE

commit d52499125a0bb8cdab342aaab0cb0cb4e3efce16
Author: Robert Lyon <email address hidden>
Date: Tue Nov 26 15:06:22 2013 +1300

Getting mysql not to use CAST() (bug #1081947)

In the 1.8+ codebase there are only two places where both mysql and
postgres use CAST(). So I've tried altering the calls so that mysql
doens't need to use CAST().

This will probably need to be tested with a large data mysql db site
to see if any speed gains are made, and also the calls are still
working correctly.

All other places it is being used by postgres only
Signed-off-by: Robert Lyon <email address hidden>

Change-Id: I9eaf196d062ef2a62bfdae0df179281f3411c392

Aaron Wells (u-aaronw) on 2013-12-18
Changed in mahara:
status: Fix Committed → Fix Released
Mahara Bot (dev-mahara) wrote :

Patch for "1.7_STABLE" branch: https://reviews.mahara.org/3163

Reviewed: https://reviews.mahara.org/3158
Committed: http://gitorious.org/mahara/mahara/commit/e35d3ea8b72a26057a5edf7e8e9f1ed0ad138391
Submitter: Son Nguyen (<email address hidden>)
Branch: 1.6_STABLE

commit e35d3ea8b72a26057a5edf7e8e9f1ed0ad138391
Author: Robert Lyon <email address hidden>
Date: Tue Nov 26 15:06:22 2013 +1300

Getting mysql not to use CAST() (bug #1081947)

In the 1.8+ codebase there are only two places where both mysql and
postgres use CAST(). So I've tried altering the calls so that mysql
doens't need to use CAST().

This will probably need to be tested with a large data mysql db site
to see if any speed gains are made, and also the calls are still
working correctly.

All other places it is being used by postgres only
Signed-off-by: Robert Lyon <email address hidden>

Change-Id: I9eaf196d062ef2a62bfdae0df179281f3411c392

Mahara Bot (dev-mahara) wrote :

Reviewed: https://reviews.mahara.org/3163
Committed: http://gitorious.org/mahara/mahara/commit/c6d8ccbf5a4ad538bddb18e96a2a2a64eb18b3fb
Submitter: Aaron Wells (<email address hidden>)
Branch: 1.7_STABLE

commit c6d8ccbf5a4ad538bddb18e96a2a2a64eb18b3fb
Author: Robert Lyon <email address hidden>
Date: Tue Nov 26 15:06:22 2013 +1300

Getting mysql not to use CAST() (bug #1081947)

In the 1.8+ codebase there are only two places where both mysql and
postgres use CAST(). So I've tried altering the calls so that mysql
doens't need to use CAST().

This will probably need to be tested with a large data mysql db site
to see if any speed gains are made, and also the calls are still
working correctly.

All other places it is being used by postgres only
Signed-off-by: Robert Lyon <email address hidden>

Change-Id: I9eaf196d062ef2a62bfdae0df179281f3411c392

Aaron Wells (u-aaronw) on 2014-04-03
no longer affects: mahara/1.9
Robert Lyon (robertl-9) on 2014-04-22
Changed in mahara:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers