Forum

Thread tagged as: Question, Runway

perch2_resource_log size

Hi,

I'm using Perch Runway (2.7.6).

perch2_resource_log table has become very large - over 4 million rows.

There are only a few hundred assets uploaded and the website is not really that large.

Is it correct that this table has become this large?

Just concerned this is going to get bigger and bigger and make migrating the database to the production server troublesome.

Thanks,

Rob
Robert Chambers

Robert Chambers 0 points

  • 6 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

6 years ago

It should not be that large, no.

What can you tell me about how you're using assets?
Robert Chambers

Robert Chambers 0 points

6 years ago

I'm using a few different Collection templates. They mainly have 2 images - thumbnail and hero image. Then a repeating region (x 4 at most) with an image each. So maybe 7 images total max per Collection item.

It actually seems like this table has become corrupt now - couldn't open it or even run a repair. I've deleted the table and created a fresh one (structure only - no data). Is that ok or will it cause me issues?

I do have a backup of the table, but don't really want to restore 4 million records if not needed.
Drew McLellan

Drew McLellan 2638 points
Perch Support

6 years ago

That will cause you issues. Turn off resource clean up or you images will be removed.

I think the repeaters are the issue - I'm looking into it.
Robert Chambers

Robert Chambers 0 points

6 years ago

Thought that might be the case, so I'd put this in my config file:

define('PERCH_CLEAN_RESOURCES', false);

Just let me know if you need anything from me - can send you a dump of the table if needed.
Drew McLellan

Drew McLellan 2638 points
Perch Support

6 years ago

I've done a bunch of testing and code auditing, and I can't find any way that causes the resource log to grow more than expected.

Can you show me the output of these two statements for your DB?

SHOW CREATE TABLE `perch2_resources`;
SHOW CREATE TABLE `perch2_resource_log`;
Robert Chambers

Robert Chambers 0 points

6 years ago

Sure, here you go (I've changed the table prefix to ptbl)...

CREATE TABLE `ptbl_resources` (
  `resourceID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `resourceApp` char(32) NOT NULL DEFAULT 'content',
  `resourceBucket` char(16) NOT NULL DEFAULT 'default',
  `resourceFile` char(255) NOT NULL DEFAULT '',
  `resourceKey` enum('orig','thumb') DEFAULT NULL,
  `resourceParentID` int(10) NOT NULL DEFAULT '0',
  `resourceType` char(4) NOT NULL DEFAULT '',
  `resourceCreated` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
  `resourceUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resourceAWOL` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `resourceTitle` char(255) DEFAULT NULL,
  `resourceFileSize` int(10) unsigned DEFAULT NULL,
  `resourceWidth` int(10) unsigned DEFAULT NULL,
  `resourceHeight` int(10) unsigned DEFAULT NULL,
  `resourceCrop` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `resourceDensity` float NOT NULL DEFAULT '1',
  `resourceTargetWidth` int(10) unsigned DEFAULT NULL,
  `resourceTargetHeight` int(10) unsigned DEFAULT NULL,
  `resourceMimeType` char(64) DEFAULT NULL,
  `resourceInLibrary` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`resourceID`),
  UNIQUE KEY `idx_file` (`resourceBucket`,`resourceFile`),
  KEY `idx_app` (`resourceApp`),
  KEY `idx_key` (`resourceKey`),
  KEY `idx_type` (`resourceType`),
  KEY `idx_awol` (`resourceAWOL`),
  KEY `idx_library` (`resourceInLibrary`),
  FULLTEXT KEY `idx_search` (`resourceTitle`)
) ENGINE=MyISAM AUTO_INCREMENT=836 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED

CREATE TABLE `ptbl_resource_log` (
  `logID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `appID` char(32) NOT NULL DEFAULT 'content',
  `itemFK` char(32) NOT NULL DEFAULT 'itemRowID',
  `itemRowID` int(10) unsigned NOT NULL,
  `resourceID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`logID`),
  KEY `idx_resource` (`resourceID`),
  KEY `idx_fk` (`itemFK`,`itemRowID`),
  KEY `idx_uni` (`appID`,`itemFK`,`itemRowID`,`resourceID`)
) ENGINE=MyISAM AUTO_INCREMENT=6677093 DEFAULT CHARSET=utf8
Drew McLellan

Drew McLellan 2638 points
Perch Support

6 years ago

I think we've got it. idx_uni as the name suggests should be a UNIQUE index.

We insert into the resource log with an INSERT IGNORE INTO expecting many (most, even) inserts to fail due to the unique key constraint. It's faster to do that than to search and only insert if it exists.

Your table has an index, but it's not unique, so the insert would succeed every time. I'm not sure how that gets up to 4 million - but as we're then into bug territory it almost isn't important. I think there must be exponential duplication happening when a new revision is created.

So, how to fix this for you...

We can't just make the key unique - that will fail as you already have duplicate content. We'll need to de-duplicate it first. I'll see what I can figure out.
Drew McLellan

Drew McLellan 2638 points
Perch Support

6 years ago

Ok, can you make a backup of your database, and then try this:

DROP INDEX `idx_uni` ON `ptbl_resource_log`;
ALTER IGNORE TABLE `ptbl_resource_log` ADD UNIQUE INDEX `idx_uni` (`appID`, `itemFK`, `itemRowID`, `resourceID`);
Robert Chambers

Robert Chambers 0 points

6 years ago

Thanks Drew.

That's reduced the rows to 3k ish.
Drew McLellan

Drew McLellan 2638 points
Perch Support

6 years ago

That sounds much better. Let me know how it goes - it should grow as you edit, but only moderately.