Opened 4 years ago

Closed 4 years ago

#2216 closed task (fixed)

Fix slow queries

Reported by: shevek Owned by: shevek
Priority: major Milestone: 2.6
Component: BW Database Keywords:
Cc:

Description

The slow query log indicates that there is ample room for improvement.

Change History (13)

comment:1 Changed 4 years ago by shevek

# Time: 140621  9:07:11
# Query_time: 1.519376  Lock_time: 0.000032  Rows_sent: 1  Rows_examined: 660
SET timestamp=1403334431;
SELECT COUNT(*) AS n
FROM `messages`
WHERE `IdReceiver` = 1223
AND `Status` = 'Sent'
AND (NOT FIND_IN_SET('receiverdeleted', `DeleteRequest`))
AND `WhenFirstRead` = 0;

Add indices for DeleteRequest and WhenFirstRead.

Last edited 4 years ago by shevek (previous) (diff)

comment:2 Changed 4 years ago by shevek

Query in SignupModel->takeCareForNonUniqueEmailAddress($email).

# Time: 140621 13:30:06
# Query_time: 1.791551  Lock_time: 0.000041  Rows_sent: 0  Rows_examined: 422931
SET timestamp=1403350206;
SELECT `Username`, members.`Status`, members.`id` AS `idMember`
FROM `members`, `cryptedfields`
WHERE members.`id` = cryptedfields.`IdMember`
AND members.`id`!=xyz
AND `AdminCryptedValue` = '<admincrypted>$email</admincrypted>';

Change query to a RIGHT JOIN:

SELECT `Username`, members.`Status`, members.`id` AS `idMember`
FROM `cryptedfields`
RIGHT JOIN members ON members.id = cryptedfields.idmember AND members.`id`!=xyz
WHERE `AdminCryptedValue` = '<admincrypted>$email</admincrypted>';
Last edited 4 years ago by shevek (previous) (diff)

comment:3 Changed 4 years ago by shevek

Query in mailbot to determine which post notifications need to be send:

SELECT
    posts_notificationqueue.*,
    Username,
    TIMESTAMPDIFF( minute, posts_notificationqueue.created, now( )) as created_since_x_minute
FROM
    posts_notificationqueue,
    members
WHERE
    posts_notificationqueue.IdMember = members.id  AND
    (members.Status = 'Active' OR members.Status = 'ActiveHidden')  AND
    posts_notificationqueue.Status = 'ToSend';

Change to RIGHT JOIN:

SELECT
    posts_notificationqueue.*,
    Username,
    TIMESTAMPDIFF( minute, posts_notificationqueue.created, now( )) as created_since_x_minute
FROM
    posts_notificationqueue
RIGHT JOIN members ON posts_notificationqueue.IdMember = members.id  AND
    (members.Status = 'Active' OR members.Status = 'ActiveHidden')  
WHERE
    posts_notificationqueue.Status = 'ToSend';

comment:4 Changed 4 years ago by shevek

SELECT SQL_CALC_FOUND_ROWS
  `forums_threads`.`threadid`,
  `forums_threads`.`id`                 AS IdThread,
  `forums_threads`.`title`,
  `forums_threads`.`IdTitle`,
  `forums_threads`.`IdGroup`,
  `forums_threads`.`replies`,
  `forums_threads`.`stickyvalue`,
  `groups`.`Name`                       AS `GroupName`,
  `ThreadVisibility`,
  `ThreadDeleted`,
  `forums_threads`.`views`,
  `forums_threads`.`continent`,
  `first`.`postid`                      AS `first_postid`,
  `first`.`authorid`                    AS `first_authorid`,
  UNIX_TIMESTAMP(`first`.`create_time`) AS `first_create_time`,
  UNIX_TIMESTAMP(`last`.`create_time`)  AS `last_create_time`,
  `last`.`postid`                       AS `last_postid`,
  `last`.`authorid`                     AS `last_authorid`,
  UNIX_TIMESTAMP(`last`.`create_time`)  AS `last_create_time`,
  `first_member`.`Username`             AS `first_author`,
  `last_member`.`Username`              AS `last_author`,
  `geonames_cache`.`name`               AS `geonames_name`,
  `geonames_cache`.`geonameid`,
  `geonames_admincodes`.`name`          AS `adminname`,
  `geonames_admincodes`.`admin_code`    AS `admincode`,
  `geonames_countries`.`name`           AS `countryname`,
  `geonames_countries`.`iso_alpha2`     AS `countrycode`
FROM `forums_threads`
  LEFT JOIN `forums_posts` AS `first` ON (`forums_threads`.`first_postid` = `first`.`postid`)
  LEFT JOIN `groups` ON (`groups`.`id` = `forums_threads`.`IdGroup`)
  LEFT JOIN `forums_posts` AS `last` ON (`forums_threads`.`last_postid` = `last`.`postid`)
  LEFT JOIN `members` AS `first_member` ON (`first`.`IdWriter` = `first_member`.`id`)
  LEFT JOIN `members` AS `last_member` ON (`last`.`IdWriter` = `last_member`.`id`)
  LEFT JOIN `geonames_cache` ON (`forums_threads`.`geonameid` = `geonames_cache`.`geonameid`)
  LEFT JOIN `geonames_admincodes` ON (`forums_threads`.`admincode` = `geonames_admincodes`.`admin_code` AND
                                      `forums_threads`.`countrycode` = `geonames_admincodes`.`country_code`)
  LEFT JOIN `geonames_countries` ON (`forums_threads`.`countrycode` = `geonames_countries`.`iso_alpha2`)
WHERE 1 AND ((ThreadVisibility = 'NoRestriction') AND (ThreadDeleted != 'Deleted')) AND
      ((IdGroup != 0 AND ThreadVisibility = 'NoRestriction'))
ORDER BY `stickyvalue` ASC, `last_create_time` DESC
LIMIT 2525, 5;

Add indices for 'ThreadVisibility' and 'ThreadDeleted'.

Last edited 4 years ago by shevek (previous) (diff)

comment:5 Changed 4 years ago by shevek

  • Status changed from new to local_testing

comment:6 Changed 4 years ago by crumbking

more batches in the pipeline?

comment:7 Changed 4 years ago by Tsjoek

  • Status changed from local_testing to to_alpha

General remark: adding indices is something ideally to be done inside a bigger picture, because they also influence other queries. It is perfectly possible to lose performance on having too many indices.

Second remark: a mention in the slow queries log is not necessarily the queries fault (although a strong indication).

@crumbking: I guess so, when browsing around the testdb, I see enough indices and schema layouts that can be improved, but the whole thing will take longer than this milestone. We can make a 'part 2' etc. ticket to document the improvements in later stages, so it doesn't stand other things in the way, we document improvements with the right milestone and we don't need to wait with them until 'everything' is done.

That all being said, I see some really good ones here and the rest won't hurt too much I think, the migration runs smoothly up-and-down. Let's move forward with this one.

comment:8 Changed 4 years ago by shevek

Deployed to alpha.

comment:9 Changed 4 years ago by shevek

  • Status changed from to_alpha to testing

comment:10 Changed 4 years ago by shevek

  • Status changed from testing to alpha

comment:11 Changed 4 years ago by shevek

  • Status changed from alpha to testing

comment:12 Changed 4 years ago by beatnickgr

Closed, as crumbking requested. It's working faster indeed.

comment:13 Changed 4 years ago by beatnickgr

  • Resolution set to fixed
  • Status changed from testing to closed
Note: See TracTickets for help on using tickets.