Forums | Mahara Community

Developers /
High load on Postgres DB doing with two queries


Jawyei Wong's profile picture
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 (

                                 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

Kristina Hoeppner's profile picture
Posts: 3768

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

Jawyei Wong's profile picture
Posts: 23

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

Jawyei Wong's profile picture
Posts: 23

12 April 2019, 11:30 AM

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

Robert Lyon's profile picture
Posts: 488

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

Shen Zhang's profile picture
Posts: 76

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

Robert Lyon's profile picture
Posts: 488

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

 

Jawyei Wong's profile picture
Posts: 23

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?

Robert Lyon's profile picture
Posts: 488

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