Scott Farquhar added a comment - 12/Mar/2008 6:38 AM Unfortunately MySQL does not support functions on indexes at the moment: http://bugs.mysql.com/bug.php?id=9639 I know that Postgres does, which would make it possible to create index on lower(spaceKey), but it appears not MySQL.

dave (Inactive) added a comment - 10/Mar/2008 1:00 AM - edited Neeraj, We use lower() pervasively in Confluence to ensure there is only one page or one space key with a certain title or key. That is, we use it to stop two pages with titles "Documentation" and "documentation" from being considered different. As far as Confluence is concerned, they are the same and Confluence will prevent the second page from being created, citing it as a duplicate. As such, the fix you are asking for is neither simple or recommended, as you are trying to change core behaviour in Confluence. Hence, we cannot supply you with a workaround and don't recommend that you do this. Cheers, Dave

Neeraj Jhanji added a comment - 07/Mar/2008 8:16 AM Reference Support Issue: https://support.atlassian.com/browse/CSP-11031 The performance problem is not solved yet and pending information regarding which file in the source code should be changed if we want to try turning off the use of lower() in the SQL statements generated by Confluence? http://confluence.atlassian.com/display/DOC/Obtaining+Confluence+Instance+Metrics Here are additional metrics about the customer's instance: Usage statistics gather from database Content created per day +------------------+------------------------+------------------------+------------------------+ | CONTENTTYPE | min(number_of_changes) | max(number_of_changes) | avg(number_of_changes) | +------------------+------------------------+------------------------+------------------------+ | BLOGPOST | 1 | 43 | 9.4814 | | COMMENT | 1 | 200 | 16.5749 | | MAIL | 1 | 1 | 1.0000 | | PAGE | 1 | 4213 | 212.1202 | | SPACEDESCRIPTION | 1 | 112 | 3.8370 | | USERINFO | 1 | 86 | 5.9203 | +------------------+------------------------+------------------------+------------------------+ 6 rows in set (9.89 sec) Content edited per day +------------------+------------------------+------------------------+------------------------+ | CONTENTTYPE | min(number_of_changes) | max(number_of_changes) | avg(number_of_changes) | +------------------+------------------------+------------------------+------------------------+ | BLOGPOST | 1 | 60 | 12.8382 | | COMMENT | 1 | 200 | 18.5626 | | MAIL | 1 | 1 | 1.0000 | | PAGE | 1 | 16666 | 1790.7956 | | SPACEDESCRIPTION | 1 | 112 | 4.3905 | | USERINFO | 1 | 86 | 6.7593 | +------------------+------------------------+------------------------+------------------------+ 6 rows in set (1 min 9.83 sec) Number of existing pages +------------------+----------+ | CONTENTTYPE | count(*) | +------------------+----------+ | BLOGPOST | 6586 | | COMMENT | 9040 | | MAIL | 12 | | PAGE | 1306516 | | SPACEDESCRIPTION | 1852 | | USERINFO | 3274 | +------------------+----------+ 6 rows in set (14.36 sec) Number of links per page +------+----------------+----------------+----------------+-------------------+---------------------+ | http | max(linkcount) | min(linkcount) | avg(linkcount) | STDDEV(linkcount) | VARIANCE(linkcount) | +------+----------------+----------------+----------------+-------------------+---------------------+ | 0 | 7575 | 1 | 8.9439 | 40.4860 | 1639.1198 | | 1 | 932 | 1 | 5.5395 | 15.6849 | 246.0159 | +------+----------------+----------------+----------------+-------------------+---------------------+ 2 rows in set (8.20 sec) Number of characters per content body +--------------+--------------+--------------+-----------------+-------------------+ | max(blength) | min(blength) | avg(blength) | stddev(blength) | variance(blength) | +--------------+--------------+--------------+-----------------+-------------------+ | 902875 | 1 | 4845.2369 | 7293.3190 | 53192501.3637 | +--------------+--------------+--------------+-----------------+-------------------+ 1 row in set (4 min 45.54 sec) Number of characters per page body +--------------+--------------+--------------+-----------------+-------------------+ | max(blength) | min(blength) | avg(blength) | stddev(blength) | variance(blength) | +--------------+--------------+--------------+-----------------+-------------------+ | 902875 | 1 | 4895.7827 | 7321.6202 | 53606122.1733 | +--------------+--------------+--------------+-----------------+-------------------+ 1 row in set (3 min 52.85 sec) Attachments +----------+---------------+---------------+---------------+------------------+---------------+ | count(*) | max(FILESIZE) | min(FILESIZE) | avg(FILESIZE) | stddev(FILESIZE) | sum(FILESIZE) | +----------+---------------+---------------+---------------+------------------+---------------+ | 70194 | 10483197 | 0 | 227141.2404 | 694042.0475 | 15943952228 | +----------+---------------+---------------+---------------+------------------+---------------+ 1 row in set (0.41 sec) Configuration / plugin data stored in Bandana +----------+---------------------------+ | count(*) | sum(length(BANDANAVALUE)) | +----------+---------------------------+ | 13 | 18120 | +----------+---------------------------+ 1 row in set (0.02 sec) All of the information +----------+---------------------------+ | count(*) | sum(length(BANDANAVALUE)) | +----------+---------------------------+ | 5802 | 1370753 | +----------+---------------------------+

Neeraj Jhanji added a comment - 06/Mar/2008 7:39 AM Quoting from my earlier comment: It seems using lower() with MySQL is not effectively able to use the Index, so wondering if it is possible to somehow turn off the use of lower() in the SQL statements from Confluence to achieve better performance with InnoDB? Could you let us know which file in the source code should be changed if we want to try turning off the use of lower() in the SQL statements generated by Confluence? regards, Neeraj

Igor Minar added a comment - 22/Aug/2007 9:43 PM btw your own docs say that use of MyISAM is discouraged: The Default storage engine for MySQL is MyISAM. This storage engine does not support Foreign Key constraints. This may cause data corruption and is not recommended for use. source Don't fight it and make InnoDB the default for MySQL

Igor Minar added a comment - 22/Aug/2007 3:39 AM This is weird: lower(page0_.TITLE)='Login' - because you are matching lower cased string against Login with capital L, it will never return any results. Anyway, I wonder if MySQL can index results from functions like lower() . btw I 'd rather look at overall Confluence performance, instead of focusing on a single query that might not be a bottleneck at all.

Neeraj Jhanji added a comment - 22/Aug/2007 3:03 AM Here is some more information regarding the performance issue with InnoDB: On a Confluence installation with MySQL, our customer is trying to switch the MySQL storage engine from MyISAM to InnoDB but the DB read performance drops with InnoDB: When reading a space (/display/confluence/HOME) for example, Confluence creates this SQL statement select page0_.CONTENTID as CONTENTID, page0_.PARENTID as PARENTID, page0_.SPACEID as SPACEID , page0_.TITLE as TITLE, page0_.VERSION as VERSION, page0_.CREATOR as CREATOR , page0_.CREATIONDATE as CREATION6_, page0_.LASTMODIFIER as LASTMODI7_ , page0_.LASTMODDATE as LASTMODD8_, page0_.VERSIONCOMMENT as VERSIONC9_ , page0_.PREVVER as PREVVER, page0_.CONTENT_STATUS as CONTENT11_ from SPACES space1_, CONTENT page0_ where page0_.CONTENTTYPE= 'PAGE' and ((lower(space1_.SPACEKEY)=1 and page0_.SPACEID=space1_.SPACEID) and (lower(page0_.TITLE)= 'Login' ) and (page0_.PREVVER is null ) and (page0_.CONTENT_STATUS= 'current' )); response time 1.48 sec but if you run the same SQL statement without lower() as below, response time improves considerably. select page0_.CONTENTID as CONTENTID, page0_.PARENTID as PARENTID, page0_.SPACEID as SPACEID , page0_.TITLE as TITLE, page0_.VERSION as VERSION, page0_.CREATOR as CREATOR , page0_.CREATIONDATE as CREATION6_, page0_.LASTMODIFIER as LASTMODI7_ , page0_.LASTMODDATE as LASTMODD8_, page0_.VERSIONCOMMENT as VERSIONC9_ , page0_.PREVVER as PREVVER, page0_.CONTENT_STATUS as CONTENT11_ from SPACES space1_, CONTENT page0_ where page0_.CONTENTTYPE= 'PAGE' and ((space1_.SPACEKEY=1 and page0_.SPACEID=space1_.SPACEID) and (page0_.TITLE= 'Login' ) and (page0_.PREVVER is null ) and (page0_.CONTENT_STATUS= 'current' )); response time 0.00 sec It seems using lower() with MySQL is not effectively able to use the Index, so wondering if it is possible to somehow turn off the use of lower() in the SQL statements from Confluence to achieve better performance with InnoDB?

Igor Minar added a comment - 22/Aug/2007 2:54 AM Hi Ivan, The raw db throughput is usually lower for InnoDB tables/databases compared to MyISAM ones. That is an expected behavior because of the overhead cause by FK and other features. But considering all the caching that Confluence does the difference should not be noticeable in most cases. MyISAM cheats and sacrifices data integrity in order to gain maximum performance. You can look at it as a sports car without breaks, seat belts and air bags IMO running Confluence with MyISAM db engine is suicidal :-D

Ivan Benko [Atlassian] added a comment - 21/Aug/2007 1:24 AM - edited Seems as there may also be a distinct difference in performance in the SQL queries. "For our customer using Confluence Unlimited 2-node installation with MySQL, they are trying to switch the MySQL storage engine from MyISAM to InnoDB but the DB read performance drops with InnoDB:"