Poor DB performance, no indexes. Suggested Indexes

You found a bug or you're missing a major feature? Just ask and discuss
Forum rules
The forums were migrated over to https://central.owncloud.org which is based on the forum software Discourse. The forums here is put into read-only mode starting from today.

More background information about this move and the reasoning behind it is available in this blogpost:

https://daniel.molkentin.net/2016/07/20 ... d-central/
Van
Beginner
Posts: 35
Joined: Wed Aug 15, 2012 9:03 pm
ownCloud version: 4.5.3

Poor DB performance, no indexes. Suggested Indexes

Postby Van » Thu Sep 13, 2012 7:11 pm

ownCloud: 4.5b3a
MySQL: 5.5.27

How come not all the database tables have indexes?

Reported here: http://bugs.owncloud.org/thebuggenie/ow ... es/oc-1736
Last edited by Van on Fri Sep 14, 2012 2:35 pm, edited 4 times in total.

Van
Beginner
Posts: 35
Joined: Wed Aug 15, 2012 9:03 pm
ownCloud version: 4.5.3

Re: Database tables have no indexes?

Postby Van » Thu Sep 13, 2012 7:26 pm

Based on our observation, we did the following:

Code: Select all

ALTER TABLE `owncloud`.`oc_properties`   
  ADD  INDEX `oc_properties_idx1` (`userid`),
  ADD  INDEX `oc_properties_idx2` (`propertyname`);


Code: Select all

ALTER TABLE `owncloud`.`oc_locks`   
  ADD  INDEX `oc_locks_idx1` (`userid`);
ownCloud 4.5, Ubuntu 12.04, apache, MySQL 5.5.x, VirtualBox, glusterFS.
We have abandoned ownCloud. Read why here:
http://forum.owncloud.org/viewtopic.php?f=3&t=4582

Van
Beginner
Posts: 35
Joined: Wed Aug 15, 2012 9:03 pm
ownCloud version: 4.5.3

Re: Database tables have no indexes?

Postby Van » Thu Sep 13, 2012 7:31 pm

Also did this:

Code: Select all

ALTER TABLE `owncloud`.`oc_fscache`
  ADD INDEX `path_index` (`path`);
ownCloud 4.5, Ubuntu 12.04, apache, MySQL 5.5.x, VirtualBox, glusterFS.
We have abandoned ownCloud. Read why here:
http://forum.owncloud.org/viewtopic.php?f=3&t=4582

Van
Beginner
Posts: 35
Joined: Wed Aug 15, 2012 9:03 pm
ownCloud version: 4.5.3

Re: Database tables have no indexes? - Suggested Indexes

Postby Van » Fri Sep 14, 2012 12:03 am

Code: Select all

ALTER TABLE `owncloud`.`oc_preferences`   
  ADD  INDEX `oc_preferences_idx1` (`userid`),
  ADD  INDEX `oc_preferences_idx2` (`appid`, `configkey`);


Code: Select all

ALTER TABLE `owncloud`.`oc_appconfig`   
  ADD  INDEX `oc_appconfig_idx1` (`appid`, `configkey`);
ownCloud 4.5, Ubuntu 12.04, apache, MySQL 5.5.x, VirtualBox, glusterFS.
We have abandoned ownCloud. Read why here:
http://forum.owncloud.org/viewtopic.php?f=3&t=4582

User avatar
RandolphCarter
ownCloud master
Posts: 5134
Joined: Wed May 30, 2012 8:42 am
ownCloud version: 8.1.1
Webserver: Apache
Database: MySQL
OS: Linux
PHP version: 5.5.x

Re: Poor DB performance, no indexes. Suggested Indexes

Postby RandolphCarter » Fri Sep 14, 2012 10:23 am

@Van: Did you check if theses indexes help performance? Preferably by testing with a large owncloud installation ;)? Would be cool to see performance improvements...

RealRancor
ownCloud master
Posts: 17381
Joined: Sat May 26, 2012 3:00 pm
ownCloud version: 9.0.2
Webserver: nginx
Database: MySQL
OS: Linux
PHP version: 7.0.x

Re: Poor DB performance, no indexes. Suggested Indexes

Postby RealRancor » Fri Sep 14, 2012 10:26 am

Hi,

thanks for sharing this. I didn't noticed that some indexes where missing in the database.

@RandolphCarter
Yes that would be a nice info.

@Van

Did you started a feature request @ http://bugs.owncloud.org/thebuggenie/ ?
*gone*

Van
Beginner
Posts: 35
Joined: Wed Aug 15, 2012 9:03 pm
ownCloud version: 4.5.3

Re: Poor DB performance, no indexes. Suggested Indexes

Postby Van » Fri Sep 14, 2012 2:06 pm

We are currently testing ownCloud for a possible rollout in our company. We just currently expanded the test to 50 users (eventually will be 500+). The biggest drawback right now is the _conflict- file issue. If it isn't addressed soon then it will probably kill the project.

We are a large MySQL shop and we have many tools for monitoring performance. The best tool we use is MONyog for monitoring MySQL servers. We are very familiar with MySQL performance issues. By most accounts a FULL TABLE scan is bad news and that is exactly what was happening.

What we noticed is the tables above get references quite frequently when sync'ing files. Review this 15 minute sampling (copy-paste into a text editor to see it better) of the top 20 queries:

Code: Select all

Total   Avg   Max   Sent   Examined   Lock time   Count   Query Occurrence( % )   First Seen   Last Seen   User@Host   Query
44.082   0.027   0.06   0   17.964K   0   1623   15.459    Sep  13, 12 13:05:19    Sep  13, 12 13:17:33   owncloud[owncloud] @ localhost []   SELECT * FROM `oc_properties` WHERE `userid` = ? AND `propertypath` = ?;
03:02.913   0.187   5.202   0   97.062K   0   977   9.306    Sep  13, 12 13:05:19    Sep  13, 12 13:29:47   owncloud[owncloud] @ localhost []   SELECT `path` FROM `oc_fscache` WHERE `path` LIKE ?;
29.274   0.037   0.278   0   17.869K   0   795   7.572    Sep  13, 12 13:05:21    Sep  13, 12 13:17:33   owncloud[owncloud] @ localhost []   DELETE FROM `oc_properties` WHERE `userid` = ? AND `propertyname` = ? AND `propertypath` IN (?,?,?,?,?,?,?);
28.494   0.036   0.265   0   17.868K   0   784   7.467    Sep  13, 12 13:05:21    Sep  13, 12 13:17:33   owncloud[owncloud] @ localhost []   DELETE FROM `oc_properties` WHERE `userid` = ? AND `propertyname` = ? AND `propertypath` IN (?,?,?,?,?,?);
20.257   0.036   0.278   0   17.886K   0   564   5.372    Sep  13, 12 13:05:19    Sep  13, 12 13:17:32   owncloud[owncloud] @ localhost []   DELETE FROM `oc_properties` WHERE `userid` = ? AND `propertyname` = ? AND `propertypath` IN (?,?,?,?,?,?,?,?);
15.92   0.029   0.215   0   17.876K   0   541   5.153    Sep  13, 12 13:05:19    Sep  13, 12 13:17:30   owncloud[owncloud] @ localhost []   UPDATE `oc_properties` SET `propertypath` = ? WHERE `userid` = ? AND `propertypath` = ?;
0.215   0   0.001   0   0   0   462   4.4    Sep  13, 12 13:05:22    Sep  13, 12 13:23:39   owncloud[owncloud] @ localhost []   SELECT * FROM `oc_locks` WHERE `userid` = ? AND (`created` + `timeout`) > ? AND (( `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`uri` LIKE ?));
0.216   0   0.002   0   0   0   442   4.21    Sep  13, 12 13:05:20    Sep  13, 12 13:23:37   owncloud[owncloud] @ localhost []   SELECT * FROM `oc_locks` WHERE `userid` = ? AND (`created` + `timeout`) > ? AND (( `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`uri` LIKE ?));
14.931   0.041   0.342   0   17.887K   0   364   3.467    Sep  13, 12 13:05:14    Sep  13, 12 13:17:28   owncloud[owncloud] @ localhost []   DELETE FROM `oc_properties` WHERE `userid` = ? AND `propertyname` = ? AND `propertypath` IN (?,?,?,?,?,?,?,?,?);
10.267   0.033   0.114   0   17.873K   0   315   3    Sep  13, 12 13:05:23    Sep  13, 12 13:17:30   owncloud[owncloud] @ localhost []   DELETE FROM `oc_properties` WHERE `userid` = ? AND `propertyname` = ? AND `propertypath` IN (?,?,?,?,?);
0.167   0.001   0.001   0   0   0   306   2.915    Sep  13, 12 13:05:23    Sep  13, 12 13:23:32   owncloud[owncloud] @ localhost []   SELECT * FROM `oc_locks` WHERE `userid` = ? AND (`created` + `timeout`) > ? AND (( `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?));
0.186   0.001   0.026   0   0   0   280   2.667    Sep  13, 12 13:05:13    Sep  13, 12 13:23:39   owncloud[owncloud] @ localhost []   SELECT * FROM `oc_locks` WHERE `userid` = ? AND (`created` + `timeout`) > ? AND (( `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?));
0.13   0.001   0.002   0   0   0   246   2.343    Sep  13, 12 13:05:28    Sep  13, 12 13:23:19   owncloud[owncloud] @ localhost []   SELECT * FROM `oc_locks` WHERE `userid` = ? AND (`created` + `timeout`) > ? AND (( `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`uri` LIKE ?));
0.132   0.001   0.001   0   0   0   240   2.286    Sep  13, 12 13:05:13    Sep  13, 12 13:23:33   owncloud[owncloud] @ localhost []   SELECT * FROM `oc_locks` WHERE `userid` = ? AND (`created` + `timeout`) > ? AND (( `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`uri` LIKE ?));
0.099   0.001   0.002   0   0   0   158   1.505    Sep  13, 12 13:05:20    Sep  13, 12 13:23:35   owncloud[owncloud] @ localhost []   SELECT * FROM `oc_locks` WHERE `userid` = ? AND (`created` + `timeout`) > ? AND (( `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?));
3.472   0.022   0.041   1   18.165K   0   158   1.505    Sep  13, 12 13:05:29    Sep  13, 12 13:06:42   owncloud[owncloud] @ localhost []   SELECT * FROM `oc_properties` WHERE `userid` = ? AND `propertypath` IN (?);
0.098   0.001   0.002   0   0   0   157   1.495    Sep  13, 12 13:05:29    Sep  13, 12 13:23:20   owncloud[owncloud] @ localhost []   SELECT * FROM `oc_locks` WHERE `userid` = ? AND (`created` + `timeout`) > ? AND (( `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?) OR (`depth` != ? AND `uri` = ?));
3.137   0.023   0.044   2   18.158K   0   136   1.295    Sep  13, 12 13:05:29    Sep  13, 12 13:06:42   owncloud[owncloud] @ localhost []   SELECT * FROM `oc_properties` WHERE `userid` = ? AND `propertypath` IN (?,?);
3.415   0.031   0.05   0   17.877K   0   111   1.057    Sep  13, 12 13:05:20    Sep  13, 12 13:17:27   owncloud[owncloud] @ localhost []   DELETE FROM `oc_properties` WHERE `userid` = ? AND `propertyname` = ? AND `propertypath` IN (?,?,?,?,?,?,?,?,?,?);
2.025   0.024   0.046   3   18.154K   0   85   0.81    Sep  13, 12 13:05:36    Sep  13, 12 13:06:42   owncloud[owncloud] @ localhost []   SELECT * FROM `oc_properties` WHERE `userid` = ? AND `propertypath` IN (?,?,?);
2.172   0.032   0.072   0   18.049K   0   68   0.648    Sep  13, 12 13:05:27    Sep  13, 12 13:17:15   owncloud[owncloud] @ localhost []   DELETE FROM `oc_properties` WHERE `userid` = ? AND `propertyname` = ? AND `propertypath` IN (?,?,?);
2.154   0.037   0.146   0   17.885K   0   58   0.552    Sep  13, 12 13:05:49    Sep  13, 12 13:17:29   owncloud[owncloud] @ localhost []   DELETE FROM `oc_properties` WHERE `userid` = ? AND `propertyname` = ? AND `propertypath` IN (?,?,?,?);
0.022   0   0.001   0   0   0   48   0.457    Sep  13, 12 13:05:27    Sep  13, 12 13:06:51   owncloud[owncloud] @ localhost []   SELECT * FROM `oc_locks` WHERE `userid` = ? AND (`created` + `timeout`) > ? AND (( `uri` = ?) OR (`depth` != ? AND `uri` = ?));


The server is performing full table scans. oc_properties being the most guilty at 1623 hits followed by oc_fscache of 977. From what we can tell, these tables are hit for every file that is being sync'd with ownCloud.

So, Yes, we noticed a significant MySQL performance improvement when we added these indexes. I can just imagine the MySQL performance catastrophee a service provider of 1,000+ or even 10,000+ users must be having. This is a major development oversight on the part of the developers. I suspect the developers have no clue how to properly design or use a relational database. But, most developers don't so this is nothing new.
Last edited by Van on Fri Sep 14, 2012 2:37 pm, edited 1 time in total.
ownCloud 4.5, Ubuntu 12.04, apache, MySQL 5.5.x, VirtualBox, glusterFS.
We have abandoned ownCloud. Read why here:
http://forum.owncloud.org/viewtopic.php?f=3&t=4582

RealRancor
ownCloud master
Posts: 17381
Joined: Sat May 26, 2012 3:00 pm
ownCloud version: 9.0.2
Webserver: nginx
Database: MySQL
OS: Linux
PHP version: 7.0.x

Re: Poor DB performance, no indexes. Suggested Indexes

Postby RealRancor » Fri Sep 14, 2012 2:18 pm

Wow, thanks for this great research and the details you provide. I think this should definitely be added as a bug to the bugtracker so the Devs notice it.
*gone*

Van
Beginner
Posts: 35
Joined: Wed Aug 15, 2012 9:03 pm
ownCloud version: 4.5.3

Re: Poor DB performance, no indexes. Suggested Indexes

Postby Van » Fri Sep 14, 2012 2:35 pm

ownCloud 4.5, Ubuntu 12.04, apache, MySQL 5.5.x, VirtualBox, glusterFS.
We have abandoned ownCloud. Read why here:
http://forum.owncloud.org/viewtopic.php?f=3&t=4582

RealRancor
ownCloud master
Posts: 17381
Joined: Sat May 26, 2012 3:00 pm
ownCloud version: 9.0.2
Webserver: nginx
Database: MySQL
OS: Linux
PHP version: 7.0.x

Re: Poor DB performance, no indexes. Suggested Indexes

Postby RealRancor » Mon Sep 17, 2012 1:17 pm

Thanks, voted an +1 for this report. 8-)
*gone*


  • Similar Topics
    Replies
    Views
    Last post

Return to “Discuss possible bugs & feature requests”

Who is online

Users browsing this forum: No registered users and 1 guest