# Forums | Mahara Community

## Developers / High load on Postgres DB doing with two queries

Posts: 23

02 April 2019, 10:47 AM

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 (

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

Posts: 3780
##### Re: High load on Postgres DB doing with two queries

09 April 2019, 8:33 AM

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

Posts: 23
##### Re: High load on Postgres DB doing with two queries

09 April 2019, 9:49 AM

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

Posts: 23
##### Re: High load on Postgres DB doing with two queries

12 April 2019, 11:30 AM

Our DBA asks, would re-indexing the tables the queries are access help ease the load?

Posts: 490
##### Re: High load on Postgres DB doing with two queries

16 April 2019, 6:17 AM

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

Posts: 76
##### Re: High load on Postgres DB doing with two queries

28 May 2019, 9:23 AM

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

Posts: 490
##### Re: High load on Postgres DB doing with two queries

31 May 2019, 9:17 AM

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

Posts: 23
##### Re: High load on Postgres DB doing with two queries

30 July 2019, 1:51 PM

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?

Posts: 490
##### Re: High load on Postgres DB doing with two queries

31 July 2019, 7:33 AM

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

9 results