Uploaded image for project: 'Jira Software Data Center'
  1. Jira Software Data Center
  2. JSWSERVER-11162

Indexing after installing JAG for the first time takes ages.

XMLWordPrintable

      Trying to install JAG for the first time or updating from versions previous to 6.4 where LexoRank was introduced makes Indexing take 11hs or more.

      There are 2 problems here:

      1) https://jira.atlassian.com/browse/GHS-11143
      This issue is impacting heavily over the speed of indexing and must be fixed.

      2) This ticket: https://jdog.jira-dev.com/browse/SW-432 introduce a heavy bug over Oracle. We should not do NSL_SORT over an index column cause it make the index useless and asks for a full table scan.

      I removed this part of the code in com.atlassian.greenhopper.manager.lexorank.SqlExplicitSortLanguageFunction:

      public String toSql(DatabaseType dbType)
    {
/*
        if(dbType.getFamily().equals(DatabaseFamily.ORACLE))
        {
            StringBuilder sql = new StringBuilder("NLSSORT(")
                    .append(columnName)
                    .append(", 'NLS_SORT = ").append(sortLanguage.getProperty(DatabaseFamily.ORACLE))
                    .append("')");

            return sql.toString();
        }
        else
        {
*/
            // just return the column name
            return columnName;
  //      }
    }

      And that also improved the performance.
      We should revert all the code about that ticket and document and advice customers which NSL_SORT we want in the db and not hardcoding what we would expect.

      We need to talk to acourtis if we can configure the index or the column to be created with the necessary configuration from scratch.

      Attached is the patch that improve the performance

      You can see here :

      That its taking 90% of the JDBC calls.

      While after the patch the same is taking :

      what a normal query should.

      We need to find a way to fix the search for Oracle
      For Oracle DB we should have a way to change the DB schema parameter or changing the index capability of AO.
      If not we should document or communicate to our Oracle users that this is going to rely on their configuration.

        1. fix-performance.patch
          3 kB

            [JSWSERVER-11162] Indexing after installing JAG for the first time takes ages.

            Michael Tokar added a comment -

            This issue should now be resolved in JIRA Agile 6.6.13. What we have done to fix this is to put in place additional checks to figure out what collation is currently being used by the underlying database. If those checks tell us that the collation is non-standard, we apply additional sorting functions to our SQL queries when performing rank operations, to ensure that our ranking algorithm still performs as expected.

            In this situation however, performance will be impacted, as applying these additional sorting functions slows down the operation of the database. There is also a message output in the logs when we detect this case:

            ****************************************************************************************************
 The database collation is set incorrectly for JIRA Agile. This can cause JIRA Agile to run slower. 
 If you are using PostgreSQL 9.0 or earlier, this may also cause ranking to fail.
 Please refer to the following documentation on the database configuration: https://confluence.atlassian.com/display/DOC/Database+Configuration
 If the problems persist, please refer to this KB on how to fix the database: https://confluence.atlassian.com/x/PAQWK
****************************************************************************************************

            Regards,
            JIRA Agile team

            Michael Tokar added a comment - This issue should now be resolved in JIRA Agile 6.6.13. What we have done to fix this is to put in place additional checks to figure out what collation is currently being used by the underlying database. If those checks tell us that the collation is non-standard, we apply additional sorting functions to our SQL queries when performing rank operations, to ensure that our ranking algorithm still performs as expected. In this situation however, performance will be impacted, as applying these additional sorting functions slows down the operation of the database. There is also a message output in the logs when we detect this case: **************************************************************************************************** The database collation is set incorrectly for JIRA Agile. This can cause JIRA Agile to run slower. If you are using PostgreSQL 9.0 or earlier, this may also cause ranking to fail. Please refer to the following documentation on the database configuration: https: //confluence.atlassian.com/display/DOC/Database+Configuration If the problems persist, please refer to this KB on how to fix the database: https: //confluence.atlassian.com/x/PAQWK **************************************************************************************************** Regards, JIRA Agile team

            Dattatray Borude added a comment -

            JIRA 5.2.11 and JIRA Agile 6.3.4.3 >> 2hrs 20 min
            JIRA 6.6.9 and JIRA 6.6.11 >> 4hrs 18 min

            SQL

            Tested in same environment with same data.

            Dattatray Borude added a comment - JIRA 5.2.11 and JIRA Agile 6.3.4.3 >> 2hrs 20 min JIRA 6.6.9 and JIRA 6.6.11 >> 4hrs 18 min SQL Tested in same environment with same data.

            Carlos Khatchikian added a comment -

            mtokar thanks for your answer.

            I have talked with mlassau and mquail about this.
            We should add a migration task that would set the DB in the correct NLS_SORT and not rely in this function.
            You can talk with them about this.
            The performance degradation is a lot over this function and we need to find a better way to handle with this.

            Carlos Khatchikian added a comment - mtokar thanks for your answer. I have talked with mlassau and mquail about this. We should add a migration task that would set the DB in the correct NLS_SORT and not rely in this function. You can talk with them about this. The performance degradation is a lot over this function and we need to find a better way to handle with this.

            Michael Tokar added a comment -

            ckhatchikian, if we remove that explicit sorting, we are guaranteeing that LexoRank will fail catastrophically in instances where the customer's database is not using the correct sorting.

            Given that this code currently lives in a plugin, not in JIRA core, we had few options available to us to ensure our code works for the largest set of users. There is no convenient documentation point to inform customers how to change these settings (and customers largely don't read documentation).

            Therefore I don't think removing this code is the best approach right now, but I'm happy to discuss further.

            Michael Tokar added a comment - ckhatchikian , if we remove that explicit sorting, we are guaranteeing that LexoRank will fail catastrophically in instances where the customer's database is not using the correct sorting. Given that this code currently lives in a plugin , not in JIRA core , we had few options available to us to ensure our code works for the largest set of users. There is no convenient documentation point to inform customers how to change these settings (and customers largely don't read documentation). Therefore I don't think removing this code is the best approach right now, but I'm happy to discuss further.

              Unassigned Unassigned
              ckhatchikian Carlos Khatchikian
              Affected customers:
              8 This affects my team
              Watchers:
              20 Start watching this issue

                Created:
                Updated:
                Resolved: