Foros de discusión
Upgrade 6.2 GA3 to GA6
Bijan Vakili, modificado hace 6 años.
Upgrade 6.2 GA3 to GA6
Expert Mensajes: 375 Fecha de incorporación: 10/03/09 Mensajes recientes
Folks, this note's for those who will upgrade; and have lots of assets in their DB; since I ran into a bit of snag; fixed thanks to freenode#mysql.
Issue was following DB Query:
https://github.com/liferay/liferay-portal/blob/6.2.x/portal-impl/src/custom-sql/documentlibrary.xml#L131
Since this query was taking forever; as shown in MySQL process list:
https://hastebin.com/retodocupa.sql
Turns out we're missing couple indices; which normally we may not have cared for; since we use Lucene/Solr to get them; though in upgrade process, they matter much; since hitting DB for everything. The two columns of concern here were:
Each table had tens of thousands of rows; hence why the query was taking long time.
In short, the solution was quite simple:
Here's what we found before the problem (note the EXPLAIN SELECT LEFT JOIN output):
https://hastebin.com/ejamawasoj.vbs
After adding the indices, we got:
https://hastebin.com/cugafecumi.vbs
Of course, followed the instructions here:
https://dev.liferay.com/discover/deployment/-/knowledge_base/6-2/upgrading-liferay
Just FYI in case you're upgrading; and happen to have bunch of DB data.
Issue was following DB Query:
https://github.com/liferay/liferay-portal/blob/6.2.x/portal-impl/src/custom-sql/documentlibrary.xml#L131
SELECT
{DLFileEntry.*}
FROM
DLFileEntry
LEFT JOIN
AssetEntry ON
(AssetEntry.classPK = DLFileEntry.fileEntryId)
WHERE
(AssetEntry.classPK IS NULL)
Since this query was taking forever; as shown in MySQL process list:
https://hastebin.com/retodocupa.sql
|
| 878 | liferay | 192.168.RRR.RRR:58592 | RRR62ga3upgrade | Query | 343 | Sending data | SELECT DLFileEntry.fileEntryId as fileEntr1_394_0_, DLFileEntry.uuid_ as uuid2_394_0_, DLFileEntry.groupId as groupId394_0_, DLFileEntry.companyId as companyId394_0_, DLFileEntry.userId as userId394_0_, DLFileEntry.userName as userName394_0_, DLFileEntry.createDate as createDate394_0_, DLFileEntry.modifiedDate as modified8_394_0_, DLFileEntry.classNameId as classNam9_394_0_, DLFileEntry.classPK as classPK394_0_, DLFileEntry.repositoryId as reposit11_394_0_, DLFileEntry.folderId as folderId394_0_, DLFileEntry.treePath as treePath394_0_, DLFileEntry.name as name394_0_, DLFileEntry.extension as extension394_0_, DLFileEntry.mimeType as mimeType394_0_, DLFileEntry.title as title394_0_, DLFileEntry.description as descrip18_394_0_, DLFileEntry.extraSettings as extraSe19_394_0_, DLFileEntry.fileEntryTypeId as fileEnt20_394_0_, DLFileEntry.version as version394_0_, DLFileEntry.size_ as size22_394_0_, DLFileEntry.readCount as readCount394_0_, DLFileEntry.smallImageId as smallIm24_394_0_, DLFileEntry.largeImageId as largeIm25_394_0_, DLFileEntry.custom1ImageId as custom26_394_0_, DLFileEntry.custom2ImageId as custom27_394_0_, DLFileEntry.manualCheckInRequired as manualC28_394_0_ FROM DLFileEntry LEFT JOIN AssetEntry ON (AssetEntry.classPK = DLFileEntry.fileEntryId) WHERE (AssetEntry.classPK IS NULL) |
Turns out we're missing couple indices; which normally we may not have cared for; since we use Lucene/Solr to get them; though in upgrade process, they matter much; since hitting DB for everything. The two columns of concern here were:
DLFileEntry.fileEntryId
AssetEntry.classPK
Each table had tens of thousands of rows; hence why the query was taking long time.
In short, the solution was quite simple:
ALTER TABLE `AssetEntry` ADD INDEX `classPK` (`classPK`)
ALTER TABLE `DLFileEntry` ADD INDEX `fileEntryId` (`fileEntryId`)
Here's what we found before the problem (note the EXPLAIN SELECT LEFT JOIN output):
https://hastebin.com/ejamawasoj.vbs
mysql> -- QUERY:
mysql> SELECT DLFileEntry.* FROM DLFileEntry LEFT JOIN AssetEntry ON (AssetEntry.classPK = DLFileEntry.fileEntryId) WHERE (AssetEntry.classPK IS NULL);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> -- Takes forever ^^^
mysql> SELECT * from DLFileEntry INNER JOIN AssetEntry ON AssetEntry.classPK = DLFileEntry.fileEntryId WHERE AssetEntry.classPK IS NULL;
Empty set (0.00 sec)
mysql> SELECT * from DLFileEntry INNER JOIN AssetEntry ON AssetEntry.classPK = DLFileEntry.fileEntryId WHERE AssetEntry.classPK IS NULL;
Empty set (0.00 sec)
mysql> select count(*) from AssetEntry; +----------+
| count(*) |
+----------+
| 95864 |
+----------+
1 row in set (0.03 sec)
mysql> select count(*) from DLFileEntry;
+----------+
| count(*) |
+----------+
| 37259 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) from DLFileEntry LEFT JOIN AssetEntry ON AssetEntry.classPK = DLFileEntry.fileEntryId where AssetEntry.classPK IS NOT NULL;
+----------+
| count(*) |
+----------+
| 37246 |
+----------+
1 row in set (0.28 sec)
mysql> select count(*) from DLFileEntry JOIN AssetEntry ON AssetEntry.classPK = DLFileEntry.fileEntryId;
+----------+
| count(*) |
+----------+
| 37246 |
+----------+
1 row in set (0.31 sec)
mysql> SHOW CREATE TABLE DLFileEntry;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DLFileEntry | CREATE TABLE `DLFileEntry` (
`uuid_` varchar(75) DEFAULT NULL,
`fileEntryId` bigint(20) NOT NULL,
`groupId` bigint(20) DEFAULT NULL,
`companyId` bigint(20) DEFAULT NULL,
`userId` bigint(20) DEFAULT NULL,
`userName` varchar(75) DEFAULT NULL,
`createDate` datetime DEFAULT NULL,
`modifiedDate` datetime DEFAULT NULL,
`classNameId` bigint(20) DEFAULT NULL,
`classPK` bigint(20) DEFAULT NULL,
`repositoryId` bigint(20) DEFAULT NULL,
`folderId` bigint(20) DEFAULT NULL,
`treePath` longtext,
`name` varchar(255) DEFAULT NULL,
`extension` varchar(75) DEFAULT NULL,
`mimeType` varchar(75) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
`description` longtext,
`extraSettings` longtext,
`fileEntryTypeId` bigint(20) DEFAULT NULL,
`version` varchar(75) DEFAULT NULL,
`size_` bigint(20) DEFAULT NULL,
`readCount` int(11) DEFAULT NULL,
`smallImageId` bigint(20) DEFAULT NULL,
`largeImageId` bigint(20) DEFAULT NULL,
`custom1ImageId` bigint(20) DEFAULT NULL,
`custom2ImageId` bigint(20) DEFAULT NULL,
`manualCheckInRequired` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`fileEntryId`),
UNIQUE KEY `IX_5391712` (`groupId`,`folderId`,`name`),
UNIQUE KEY `IX_ED5CA615` (`groupId`,`folderId`,`title`),
UNIQUE KEY `IX_BC2E7E6A` (`uuid_`,`groupId`),
KEY `IX_4CB1B2B4` (`companyId`),
KEY `IX_772ECDE7` (`fileEntryTypeId`),
KEY `IX_8F6C75D0` (`folderId`,`name`),
KEY `IX_F4AF5636` (`groupId`),
KEY `IX_93CF8193` (`groupId`,`folderId`),
KEY `IX_29D0AF28` (`groupId`,`folderId`,`fileEntryTypeId`),
KEY `IX_43261870` (`groupId`,`userId`),
KEY `IX_D20C434D` (`groupId`,`userId`,`folderId`),
KEY `IX_D9492CF6` (`mimeType`),
KEY `IX_64F0FE40` (`uuid_`),
KEY `IX_31079DE8` (`uuid_`,`companyId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE AssetEntry;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| AssetEntry | CREATE TABLE `AssetEntry` (
`entryId` bigint(20) NOT NULL,
`groupId` bigint(20) DEFAULT NULL,
`companyId` bigint(20) DEFAULT NULL,
`userId` bigint(20) DEFAULT NULL,
`userName` varchar(75) DEFAULT NULL,
`createDate` datetime DEFAULT NULL,
`modifiedDate` datetime DEFAULT NULL,
`classNameId` bigint(20) DEFAULT NULL,
`classPK` bigint(20) DEFAULT NULL,
`classUuid` varchar(75) DEFAULT NULL,
`classTypeId` bigint(20) DEFAULT NULL,
`visible` tinyint(4) DEFAULT NULL,
`startDate` datetime DEFAULT NULL,
`endDate` datetime DEFAULT NULL,
`publishDate` datetime DEFAULT NULL,
`expirationDate` datetime DEFAULT NULL,
`mimeType` varchar(75) DEFAULT NULL,
`title` longtext,
`description` longtext,
`summary` longtext,
`url` longtext,
`layoutUuid` varchar(75) DEFAULT NULL,
`height` int(11) DEFAULT NULL,
`width` int(11) DEFAULT NULL,
`priority` double DEFAULT NULL,
`viewCount` int(11) DEFAULT NULL,
PRIMARY KEY (`entryId`),
UNIQUE KEY `IX_1E9D371D` (`classNameId`,`classPK`),
KEY `IX_FC1F9C7B` (`classUuid`),
KEY `IX_7306C60` (`companyId`),
KEY `IX_75D42FF9` (`expirationDate`),
KEY `IX_1EBA6821` (`groupId`,`classUuid`),
KEY `IX_FEC4A201` (`layoutUuid`),
KEY `IX_2E4E3885` (`publishDate`),
KEY `IX_9029E15A` (`visible`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain select count(*) from DLFileEntry LEFT JOIN AssetEntry ON AssetEntry.classPK = DLFileEntry.fileEntryId WHERE AssetEntry.classPK IS NULL;
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------------------------------------------+
| 1 | SIMPLE | DLFileEntry | NULL | index | NULL | IX_4CB1B2B4 | 9 | NULL | 36318 | 100.00 | Using index |
| 1 | SIMPLE | AssetEntry | NULL | index | NULL | IX_1E9D371D | 18 | NULL | 94227 | 10.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)
After adding the indices, we got:
https://hastebin.com/cugafecumi.vbs
mysql> explain select count(*) from DLFileEntry LEFT JOIN AssetEntry ON AssetEntry.classPK = DLFileEntry.fileEntryId WHERE AssetEntry.classPK IS NULL; +----+-------------+-------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------------------------------------------+
| 1 | SIMPLE | DLFileEntry | NULL | index | NULL | IX_4CB1B2B4 | 9 | NULL | 36621 | 100.00 | Using index |
| 1 | SIMPLE | AssetEntry | NULL | index | NULL | IX_1E9D371D | 18 | NULL | 94564 | 10.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> ALTER TABLE `DLFileEntry` ADD INDEX `fileEntryId` (`fileEntryId`)
-> ;
Query OK, 0 rows affected (1.79 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select count(*) from DLFileEntry LEFT JOIN AssetEntry ON AssetEntry.classPK = DLFileEntry.fileEntryId WHERE AssetEntry.classPK IS NULL;
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------------------------------------------+
| 1 | SIMPLE | DLFileEntry | NULL | index | NULL | fileEntryId | 8 | NULL | 36621 | 100.00 | Using index |
| 1 | SIMPLE | AssetEntry | NULL | index | NULL | IX_1E9D371D | 18 | NULL | 94564 | 10.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> select count(*) from DLFileEntry LEFT JOIN AssetEntry ON AssetEntry.classPK = DLFileEntry.fileEntryId WHERE AssetEntry.classPK IS NULL;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> ALTER TABLE `AssetEntry` ADD INDEX `classPK` (`classPK`)
-> ;
Query OK, 0 rows affected (2.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select count(*) from DLFileEntry LEFT JOIN AssetEntry ON AssetEntry.classPK = DLFileEntry.fileEntryId WHERE AssetEntry.classPK IS NULL;
+----------+
| count(*) |
+----------+
| 13 |
+----------+
1 row in set (0.23 sec)
Of course, followed the instructions here:
https://dev.liferay.com/discover/deployment/-/knowledge_base/6-2/upgrading-liferay
Just FYI in case you're upgrading; and happen to have bunch of DB data.
Bijan Vakili, modificado hace 6 años.
RE: Upgrade 6.2 GA3 to GA6
Expert Mensajes: 375 Fecha de incorporación: 10/03/09 Mensajes recientes
Note recommend remove these two once done; after upgrade process has completed:
ALTER TABLE `AssetEntry` ADD INDEX `classPK` (`classPK`)
ALTER TABLE `DLFileEntry` ADD INDEX `fileEntryId` (`fileEntryId`)
DROP INDEX `classPK` ON `AssetEntry`
DROP INDEX `fileEntryId` ON `DLFileEntry`
Bijan Vakili, modificado hace 6 años.
RE: Upgrade 6.2 GA3 to GA6
Expert Mensajes: 375 Fecha de incorporación: 10/03/09 Mensajes recientes
Another note applies to more upgrades; since query is same on LR7:
https://github.com/liferay/liferay-portal/blob/7.0.x/portal-impl/src/custom-sql/documentlibrary.xml#L144
Though haven't tested this out yet, but soon! Hoping to get on 7 train shortly; though per Minchau's post, I'll need ample time; to dig around and find coordinates.
https://github.com/liferay/liferay-portal/blob/7.0.x/portal-impl/src/custom-sql/documentlibrary.xml#L144
Though haven't tested this out yet, but soon! Hoping to get on 7 train shortly; though per Minchau's post, I'll need ample time; to dig around and find coordinates.