Forums | Mahara Community
Developers
/
High load on Postgres DB doing with two queries
02 April 2019, 10:47
Hi there,
We are experience quite high db load on Postgres 9.2.24 when these two queries below run. It seems like it is just doing a count on collections and a search for collections however when multiple instances of this runs we are getting such high load on the database that Mahara cannot connect to it intermittently. These queries look like normal queries and we are not sure why it would generate such high load now when we have had not had this previously.
Query 1:
SELECT COUNT(*)
FROM "view" v
LEFT OUTER JOIN "collection_view" cv ON cv.view = v.id
LEFT OUTER JOIN "collection" c ON cv.collection = c.id
LEFT OUTER JOIN "usr" qu ON (v.owner = qu.id)
LEFT OUTER JOIN "group" sg ON sg.id = v.group
WHERE (v.owner IS NULL OR (v.owner > 0 AND v.owner != $1)) AND v.template != 2
AND (v.group IS NULL OR (v.group > 0 AND sg.deleted <> 1))
AND (qu.suspendedctime is null OR v.owner = $2) AND v.type IN ('portfolio')
AND (cv.displayorder = 0 OR cv.displayorder IS NULL)
AND (v.owner = $3 -- user owns the view
OR EXISTS ( -- group view, editable by the user
SELECT m.group
FROM "group_member" m
WHERE
sg.id IS NOT NULL
AND m.group = sg.id
AND m.member = $4
AND (
m.role = 'admin'
OR sg.editroles = 'all'
OR (sg.editroles != 'admin' AND m.role != 'member')
)
)
OR ( -- user has permission to see the view
(v.startdate IS NULL OR v.startdate < current_timestamp)
AND (v.stopdate IS NULL OR v.stopdate > current_timestamp)
AND (v.id IN (-- public access
SELECT va.view
FROM "view_access" va
WHERE va.accesstype = 'public'
AND (va.startdate IS NULL OR va.startdate < current_timestamp)
AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
UNION -- loggedin access
SELECT va.view
FROM "view_access" va
WHERE va.accesstype = 'loggedin'
AND (va.startdate IS NULL OR va.startdate < current_timestamp)
AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
UNION -- friend access
SELECT va.view
FROM "view_access" va
JOIN "view" vf ON va.view = vf.id AND vf.owner IS NOT NULL
JOIN "usr_friend" f ON ((f.usr1 = $5 AND f.usr2 = vf.owner) OR (f.usr1 = vf.owner AND f.usr2 = $6))
WHERE va.accesstype = 'friends'
AND (va.startdate IS NULL OR va.startdate < current_timestamp)
AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
UNION -- user access
SELECT va.view
FROM "view_access" va
WHERE va.usr = $7
AND (va.startdate IS NULL OR va.startdate < current_timestamp)
AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
UNION -- group access
SELECT va.view
FROM "view_access" va
JOIN "group_member" m ON va.group = m.group AND (va.role = m.role OR va.role IS NULL)
WHERE
m.member = $8
AND (va.startdate IS NULL OR va.startdate < current_timestamp)
AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
UNION -- institution access
SELECT va.view
FROM "view_access" va
JOIN "usr_institution" ui ON va.institution = ui.institution
WHERE
ui.usr = $9
AND (va.startdate IS NULL OR va.startdate < current_timestamp)
AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
))))
Query 2:
SELECT
v.id AS viewid,
-- generic id column needed by get_extra___info methods
(CASE
WHEN c.id IS NOT NULL
THEN c.id
ELSE v.id
END) AS id,
(CASE
WHEN c.id IS NOT NULL
THEN c.name
ELSE v.title
END) AS title,
(CASE
WHEN c.id IS NOT NULL
THEN c.description
ELSE v.description
END) AS description,
(CASE
WHEN c.id IS NOT NULL
THEN (SELECT COUNT(*) FROM "collection_view" cv2 WHERE cv2.collection=c.id)
ELSE 0
END) AS numpages,
v.owner, v.ownerformat, v.group, v.institution, v.template, v.mtime, v.ctime,
c.id as collid, c.name, c.framework, v.type, v.urlid, v.submittedtime, v.submittedgroup, v.submittedhost
FROM "view" v
LEFT OUTER JOIN "collection_view" cv ON cv.view = v.id
LEFT OUTER JOIN "collection" c ON cv.collection = c.id
LEFT OUTER JOIN "usr" qu ON (v.owner = qu.id)
LEFT OUTER JOIN "group" sg ON sg.id = v.group
WHERE (v.owner IS NULL OR (v.owner > 0 AND v.owner != $1)) AND v.template != 2
AND (v.group IS NULL OR (v.group > 0 AND sg.deleted <> 1))
AND (qu.suspendedctime is null OR v.owner = $2) AND v.type IN ('portfolio')
AND (cv.displayorder = 0 OR cv.displayorder IS NULL)
AND (v.owner = $3 -- user owns the view
OR EXISTS ( -- group view, editable by the user
SELECT m.group
FROM "group_member" m
WHERE
sg.id IS NOT NULL
AND m.group = sg.id
AND m.member = $4
AND (
m.role = 'admin'
OR sg.editroles = 'all'
OR (sg.editroles != 'admin' AND m.role != 'member')
)
)
OR ( -- user has permission to see the view
(v.startdate IS NULL OR v.startdate < current_timestamp)
AND (v.stopdate IS NULL OR v.stopdate > current_timestamp)
AND (v.id IN (-- public access
SELECT va.view
FROM "view_access" va
WHERE va.accesstype = 'public'
AND (va.startdate IS NULL OR va.startdate < current_timestamp)
AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
UNION -- loggedin access
SELECT va.view
FROM "view_access" va
WHERE va.accesstype = 'loggedin'
AND (va.startdate IS NULL OR va.startdate < current_timestamp)
AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
UNION -- friend access
SELECT va.view
FROM "view_access" va
JOIN "view" vf ON va.view = vf.id AND vf.owner IS NOT NULL
JOIN "usr_friend" f ON ((f.usr1 = $5 AND f.usr2 = vf.owner) OR (f.usr1 = vf.owner AND f.usr2 = $6))
WHERE va.accesstype = 'friends'
AND (va.startdate IS NULL OR va.startdate < current_timestamp)
AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
UNION -- user access
SELECT va.view
FROM "view_access" va
WHERE va.usr = $7
AND (va.startdate IS NULL OR va.startdate < current_timestamp)
AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
UNION -- group access
SELECT va.view
FROM "view_access" va
JOIN "group_member" m ON va.group = m.group AND (va.role = m.role OR va.role IS NULL)
WHERE
m.member = $8
AND (va.startdate IS NULL OR va.startdate < current_timestamp)
AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
UNION -- institution access
SELECT va.view
FROM "view_access" va
JOIN "usr_institution" ui ON va.institution = ui.institution
WHERE
ui.usr = $9
AND (va.startdate IS NULL OR va.startdate < current_timestamp)
AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
))))
ORDER BY mtime DESC, v.id ASC LIMIT 5
09 April 2019, 8:33
Hi Jawyei,
This is a known issue in Mahara. It is related to https://bugs.launchpad.net/mahara/+bug/1117034 and requires quite a bit of re-architecting to change the queries.
Cheers
Kristina
09 April 2019, 9:49
Hi Kristina
Anything you can suggest to ease the impact of this? At times our Mahara instant cannot make a connection to the DB. Could we do something on the DB's end?
Thanks,
Jawyei
12 April 2019, 11:30
Our DBA asks, would re-indexing the tables the queries are access help ease the load?
16 April 2019, 6:17
Hi Jawyei,
I'm thinking it shouldn't hurt - but may only give temporary relief for the problem.
The tables involved are already indexed and foreign keyed quite well.
What is needed as a long term fix is a more efficient PHP/SQL structure to work out who currently has access to what. One that both deals with the current view access rules and also is flexible to handle new rules in the future.
Cheers
Robert
28 May 2019, 9:23
Hi
We had another load issue yesterday. The DBA has suggested to automatically kill off the query to keep the load down on the server. Is this something you would recommend?
Kind regards
Shen
31 May 2019, 9:17
Hi Shen,
The query comes from view_search() function and is used to fetch what views one can access - it is used in a variety of ways with a variety of permissions.
A site admin doing a search for anything shared to them will build a far more complex sql query with much nesting compared to a user checking if they can copy a view with certain ID.
Most of the uses for view_search() are used in conjunction with finding / displaying a list of views (pages) and so could be killed without much fear - it will mean no results are returned.
But a few instances, like page copy, it is used to check if page can be copied.
However if queries are taking too long to complete then it will cause other issues.
So if the auto killing of query was set up then it would be good to know if the bad queries are ones that are just fetching/displaying views
Hope that helps
Robert
30 July 2019, 13:51
Kristina mentioned this issue was this bug https://bugs.launchpad.net/mahara/+bug/1117034. In the description it says "...slower servers would be ill equipped to serve moderately large sites.". What is consider a slower server? Would you recommend hosting the database on a separate server? What would be the recommended specs for this server?
On our staging server (a replication of our production) we have 109460 rows in the view table. After deleting a user within the GUI we I checked and their rows in the view table got removed. We deleted 10732 users who have never logged in and in theory (each user having at least two rows in the view table) it should remove 21464 rows however only 2427 rows were removed. When I checked to see if the views were still there for the deleted users they were removed. Not sure why removing that many users did not reduce the row count of the view table in a significant way. Any other ideas on how to reduce the size of the view table or any other tables involved with this query?
31 July 2019, 7:33
Hi Jawyei,
In regards to "We deleted 10732 users who have never logged in and in theory (each user having at least two rows in the view table) it should remove 21464 rows however only 2427 rows were removed."
Normally when a user is created they have a profile page and dashboard page created. But if users are added in bulk via a CSV upload then their profile page and dashboard page only get created when they first login (or someone masquerades as them) to save time with bulk upload.
So it sounds like the bulk of of your never logged in users were created via CSV upload.
Another thing to look at in regards to reducing the number of views would be the 'grouphomepage' type views. You could check to see if there are any groups without members (or all members are deleted users) and see if some/most of them can be deleted.
Cheers
Robert