Poor Performance / Stalls and One Solution - Add Index

Ask all your questions regarding OC 5.x Please read the Support Forum Rules
Forum rules
Before you post; make sure you are using at least PHP Version 5.3.x - Also read How To Solve Problems By Yourself

Poor Performance / Stalls and One Solution - Add Index

Postby macleajb » Wed Sep 25, 2013 12:28 am

Hi Folks,

Noticed intermittent stalls. After looking around, found some slow queries:

SELECT `oc_filecache`.`fileid` FROM `oc_filecache` LEFT JOIN `oc_lucene_status` ON `oc_filecache`.`fileid` = `oc_lucene_status`.`fileid` WHERE `storage` = '2016' AND `status` is null OR `status` = "N" limit 1;

Explain said no indexs used on the oc_lucene_status table. Adjusted it :

ALTER TABLE oc_lucene_status ADD INDEX (fileid);

Queries went from 595 seconds to under 3.

Important part is that this query locked the tables. That's a large chunk of time with everyone wondering where OwnCloud went :).

Cheers,
JES
macleajb
Newbie
 
Posts: 6
Joined: Fri Sep 20, 2013 1:15 pm
ownCloud version: 5.0.10
Webserver: Apache
Database: MySQL
OS: Linux

Re: Poor Performance / Stalls and One Solution - Add Index

Postby Rancor » Wed Sep 25, 2013 8:56 am

Hi,

and thanks for this information. Please post this directly to the bugtracker:

https://github.com/owncloud/apps/issues
*mostly inactive*
Rancor
OwnCloud master
 
Posts: 7478
Joined: Sat May 26, 2012 2:00 pm
ownCloud version: 6.0.4
Webserver: nginx
Database: MySQL
OS: Linux
PHP version: 5.5.x

Re: Poor Performance / Stalls and One Solution - Add Index

Postby macleajb » Wed Sep 25, 2013 10:54 am

Posted as issue #1382.
macleajb
Newbie
 
Posts: 6
Joined: Fri Sep 20, 2013 1:15 pm
ownCloud version: 5.0.10
Webserver: Apache
Database: MySQL
OS: Linux


Return to ownCloud Community Edition 5.x

Who is online

Users browsing this forum: Google [Bot] and 20 guests