-
Suggestion
-
Resolution: Unresolved
-
1
-
NOTE: This suggestion is for Confluence Cloud. Using Confluence Server? See the corresponding suggestion.
This is taken from Support ticket:
Attachment indexing seems very slow and database server intensive. So we took a look at the queries being made to investigate.
I took a quick look at one of the queries and ran EXPLAIN EXTENDED on it to see the query plan:
select attachment0_.ATTACHMENTID as ATTACHME1_, attachment0_.TITLE as TITLE, attachment0_.CONTENTTYPE as CONTENTT3_, attachment0_.PAGEID as PAGEID, attachment0_.CREATOR as CREATOR, attachment0_.CREATIONDATE as CREATION6_ , attachment0_.LASTMODIFIER as LASTMODI7_, attachment0_.LASTMODDATE as LASTMODD8_, attachment0_.FILESIZE as FILESIZE, attachment0_.MINOR_EDIT as MINOR_EDIT, attachment0_.ATTACHMENT_COMMENT as ATTACHM11_, attachment0_.ATTVERSION as ATTVERSION, attachment0_.PREVVER as PREVVER from ATTACHMENTS attachment0_ where (attachment0_.ATTVERSION in(select max(attachment1_.ATTVERSION) from ATTACHMENTS attachment1_ where (attachment1_.PREVVER=80807965 )or(attachment1_.ATTACHMENTID=80807965 ) group by attachment1_.LASTMODIFIER))and((attachment0_.PREVVER=80807965 )or(attachment0_.ATTACHMENTID=80807965 )) order by attachment0_.ATTVERSION asc --------------
MySQL shows this as the output:
*************************** 1. row ***************************
ATTACHME1_: 80807965
TITLE: CC for new user - returning new user - on page load.png
CONTENTT3_: image/png
PAGEID: 80652240
CREATOR: 8a3a28b1455348d80145534a829111f5
CREATION6_: 2012-08-29 11:39:28
LASTMODI7_: 8a3a28b1455348d80145534a829111f5
LASTMODD8_: 2012-08-29 11:39:28
FILESIZE: 56247
MINOR_EDIT:
ATTACHM11_: NULL
ATTVERSION: 2
PREVVER: NULL
1 row in set (1.43 sec)
Notice the run time. (quite slow).
The EXPLAIN plan shows:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: attachment0_
type: ALL
possible_keys: PRIMARY,att_prevver_idx
key: NULL
key_len: NULL
ref: NULL
rows: 283170
filtered: 100.00
Extra: Using where; Using filesort
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY
table: attachment1_
type: index
possible_keys: PRIMARY,att_prevver_idx,FK_ATTACHMENTS_LASTMODIFIER
key: FK_ATTACHMENTS_LASTMODIFIER
key_len: 768
ref: NULL
rows: 283170
filtered: 100.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)
The explain plan shows that to execute this query it’s scanning the attachments table and then for each row of the attachments table it’s scanning it again.
That is doing 283,170 x 283,170 row scans which is 80,185,248,900 rows for each attachment query.
If you’re doing this for each attachment that’s going to take ~ 1 second x 283,170 rows which is a long time.
The actual number of rows in the table (MySQL shows the estimated number of rows):
[confluence566]> select count(*) from attachments; +----------+ | count(*) | +----------+ | 288015 | +----------+ 1 row in set (0.19 sec)
I looked at the data structure of the table:
root@dev-server [confluence566]> show create table attachments\G
*************************** 1. row ***************************
Table: attachments
Create Table: CREATE TABLE `attachments` (
`ATTACHMENTID` bigint(20) NOT NULL DEFAULT '0',
`TITLE` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`CONTENTTYPE` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`PAGEID` bigint(20) NOT NULL DEFAULT '0',
`CREATOR` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`CREATIONDATE` datetime DEFAULT NULL,
`LASTMODIFIER` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`LASTMODDATE` datetime DEFAULT NULL,
`FILESIZE` bigint(20) DEFAULT NULL,
`ATTACHMENT_COMMENT` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`ATTVERSION` int(11) DEFAULT NULL,
`PREVVER` bigint(20) DEFAULT NULL,
`MINOR_EDIT` bit(1) DEFAULT NULL,
PRIMARY KEY (`ATTACHMENTID`),
KEY `att_pageid_idx` (`PAGEID`),
KEY `att_prevver_idx` (`PREVVER`),
KEY `FK_ATTACHMENTS_LASTMODIFIER` (`LASTMODIFIER`),
KEY `FK_ATTACHMENTS_CREATOR` (`CREATOR`),
CONSTRAINT `FK54475F9017D4A070` FOREIGN KEY (`PREVVER`) REFERENCES `attachments` (`ATTACHMENTID`),
CONSTRAINT `FK54475F908C38FBEA` FOREIGN KEY (`PAGEID`) REFERENCES `content` (`CONTENTID`),
CONSTRAINT `FK_ATTACHMENTS_CREATOR` FOREIGN KEY (`CREATOR`) REFERENCES `user_mapping` (`user_key`),
CONSTRAINT `FK_ATTACHMENTS_LASTMODIFIER` FOREIGN KEY (`LASTMODIFIER`) REFERENCES `user_mapping` (`user_key`),
CONSTRAINT `_FK54475F908C38FBEA` FOREIGN KEY (`PAGEID`) REFERENCES `content` (`CONTENTID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
So it looks like this table is perhaps not indexed in a way which makes it easy to find the data that’s being looked for.
The table structure seems to have the same same issue.
Joining the data in a single query forces MySQL to combine things in a way which is not very efficient. It might be better to do some of these queries separately as they will be PK or index lookups and given the results adapt the remaining query that needs to be done. This seems slower but if each individual query takes say 1ms then you get a gain of 1500x compared to the current behaviour.
- is related to
-
- Closed