[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15230768#comment-15230768 ] ASF GitHub Bot commented on CLOUDSTACK-9340: GitHub user nvazquez opened a pull request: https://github.com/apache/cloudstack/pull/1466 CLOUDSTACK-9340: General DB Optimization ## Description In some production environments there were being experimented delays in most of the jobs. A search for DB optimization was taken and some deficiencies were discovered, we can group them in 4 groups: * Incorrect PRIMARY key * Duplicate PRIMARY KEY * Missing indexes (Add indexes to avoid full table scans) * Some views query (Change view to improve account retrieval speed) You can merge this pull request into a Git repository by running: $ git pull https://github.com/nvazquez/cloudstack graldboptimization Alternatively you can review and apply these changes as the patch at: https://github.com/apache/cloudstack/pull/1466.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #1466 commit 248677437ac8d1369bf353683a64b21c613a62e7 Author: nvazquez Date: 2016-04-07T18:20:48Z CLOUDSTACK-9340: General DB Optimization > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15230892#comment-15230892 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rafaelweingartner commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207057213 @nvazquez there is something wrong with one of your SQLs. > Unable to upgrade the database: Unable to execute upgrade script: /home/travis/build/apache/cloudstack/developer/target/db/db/schema-481to490.sql: Incorrect table definition; there can be only one auto column and it must be defined as a key > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15231032#comment-15231032 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207081102 @rafaelweingartner I'll check this > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232180#comment-15232180 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207434839 Hi @rafaelweingartner I executed sql in my env and succeeded, is there a way to trace which line failed? > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232205#comment-15232205 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rafaelweingartner commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207440848 @nvazquez, it is complain about a column called "size" at the "schema-40to410.sql" > schema-40to410.sql: Duplicate column name 'size' That is a little weird because you did not change anything there. It is looking like an environment problem. Maybe with a little luck and a push -f, the Jenkins job is executed somewhere else and that problem goes away? > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232264#comment-15232264 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207454618 It failed again, I can try pushing sql by parts to find out which part is failing > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232309#comment-15232309 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rafaelweingartner commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207471025 @nvazquez did it fail with the problem? for me it is showing as executing and not failed. > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232314#comment-15232314 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207472873 @rafaelweingartner sorry I pushed force again, after the whole file failed I pushed a part of sql file and pushed it and started again > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232316#comment-15232316 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rafaelweingartner commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207473461 Now the error is different, right? > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232418#comment-15232418 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207498927 Yes, this test failed, but I didn't see schema error this time === TestName: test_listVM_by_id_as_user_vmsfromotherdomain | Status : EXCEPTION === ERROR > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232559#comment-15232559 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez closed the pull request at: https://github.com/apache/cloudstack/pull/1466 > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232560#comment-15232560 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rafaelweingartner commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207534182 are you still going to push this forward? > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232568#comment-15232568 ] ASF GitHub Bot commented on CLOUDSTACK-9340: GitHub user nvazquez reopened a pull request: https://github.com/apache/cloudstack/pull/1466 CLOUDSTACK-9340: General DB Optimization ## Description In some production environments there were being experimented delays in most of the jobs. A search for DB optimization was taken and some deficiencies were discovered, we can group them in 4 groups: * Incorrect PRIMARY key * Duplicate PRIMARY KEY * Missing indexes (Add indexes to avoid full table scans) * Some views query (Change view to improve account retrieval speed) You can merge this pull request into a Git repository by running: $ git pull https://github.com/nvazquez/cloudstack graldboptimization Alternatively you can review and apply these changes as the patch at: https://github.com/apache/cloudstack/pull/1466.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #1466 commit fa0e61f183b361f4544dd2904da53380c1e12cfe Author: nvazquez Date: 2016-04-08T17:47:50Z Dummy commit for testing travis > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232574#comment-15232574 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207535135 @rafaelweingartner yes, it was my mistake. I wanted to leave the pr with no changes to test if Travis passes, I didn't know that removing my commits will close the pr. I reopened it with a dummy commit > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232597#comment-15232597 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user swill commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207537254 Just for the future reference. If you want to kick off the rebuild of a PR, you can do a `push -f` which will trigger a new build even if the code did not change in the PR. I am learning all these little hacks to make things easier... :P > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232689#comment-15232689 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207550783 Thanks @swill, I just wanted to remove all my commits for Travis test to run without my changes, but doing that closed the pr. Now I pushed a commit including a comment line just for Travis test to run without my changes. I plan to include my changes incrementally to see where the failure is > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232695#comment-15232695 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user swill commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207551688 Oh, I understand. Thanks... :) > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232734#comment-15232734 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207562190 You're welcome :) Now Travis failed without including changes (only comment line). Do I try again after Jenkins finishes using push -f? > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15232868#comment-15232868 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user swill commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207591089 I saw travis failing with this earlier. I will see if I can figure out what is going on. Thx... > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15233009#comment-15233009 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user bhaisaab commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207617920 @swill @nvazquez I saw the failing job: https://travis-ci.org/apache/cloudstack/jobs/121766436 It failed due to missing Marvin distribution: IOError: [Errno 2] No such file or directory: '/home/travis/build/apache/cloudstack/tools/marvin/dist/Marvin-*.tar.gz' The actual error seems to be hidden as only parts of mvn build output are shown in the output. It is likely that one of the unit tests have failed (likely one unit test related to kvm disk i/o where it checks some disk activity -- this has happened in my VM env few time though does not happen on baremetal). I'll open a PR to increase verbosity of the Travis build to output which test/module failed. > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15233041#comment-15233041 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user swill commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207624970 Thank you very much for the quick follow up on this, it is greatly appreciated. :+1: I will review the linked PR. > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15233671#comment-15233671 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207823632 Thanks @rafaelweingartner, @swill and @bhaisaab for your help. I pushed changes again > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15233676#comment-15233676 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user bhaisaab commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207824671 @nvazquez can you explain how did you find out which keys (i.e indexes) to drop? > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15233761#comment-15233761 ] Sergey Levitskiy commented on CLOUDSTACK-9340: -- @bhaisaab Most of the indexes being dropped are duplicate. In create-schema.sql quite a lot of tables are created like this: CREATE TABLE `cloud`.`version` ( `id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT COMMENT 'id', `version` char(40) NOT NULL UNIQUE COMMENT 'version', `updated` datetime NOT NULL COMMENT 'Date this version table was updated', `step` char(32) NOT NULL COMMENT 'Step in the upgrade to this version', PRIMARY KEY (`id`), INDEX `i_version__version`(`version`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; When column ID is marked as UNIQUE MySQL creates an unique index ID. Then below a primary key ID is defined. Primary key in its nature already guarantees uniqueness so it is safe to drop index ID leaving only primary key to remain. There are more than 50 tables like this plus 3 more tables where unique index based on ID column was created twice. For example for domain_router table one duplicate index was created initially in create-schema.sql and another duplicate was generated in schema-442to450.sql when the following line was executed: ALTER TABLE `cloud`.`domain_router` MODIFY `id` bigint unsigned AUTO_INCREMENT UNIQUE NOT NULL; > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15234140#comment-15234140 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-207994438 @bhaisaab Most of the indexes being dropped are duplicate. In create-schema.sql quite a lot of tables are created like this: CREATE TABLE `cloud`.`version` ( `id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT COMMENT 'id', `version` char(40) NOT NULL UNIQUE COMMENT 'version', `updated` datetime NOT NULL COMMENT 'Date this version table was updated', `step` char(32) NOT NULL COMMENT 'Step in the upgrade to this version', PRIMARY KEY (`id`), INDEX `i_version__version`(`version`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; When column ID is marked as UNIQUE MySQL creates an unique index ID. Then below a primary key ID is defined. Primary key in its nature already guarantees uniqueness so it is safe to drop index ID leaving only primary key to remain. There are more than 50 tables like this plus 3 more tables where unique index based on ID column was created twice. For example for domain_router table one duplicate index was created initially in create-schema.sql and another duplicate was generated in schema-442to450.sql when the following line was executed: ALTER TABLE `cloud`.`domain_router` MODIFY `id` bigint unsigned AUTO_INCREMENT UNIQUE NOT NULL; > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[
https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15234396#comment-15234396
]
ASF GitHub Bot commented on CLOUDSTACK-9340:
Github user rafaelweingartner commented on a diff in the pull request:
https://github.com/apache/cloudstack/pull/1466#discussion_r59146682
--- Diff: setup/db/db/schema-481to490.sql ---
@@ -413,3 +413,89 @@ VIEW `user_vm_view` AS
-- Add cluster.storage.operations.exclude property
INSERT INTO `cloud`.`configuration` (`category`, `instance`, `component`,
`name`, `description`, `default_value`, `updated`, `scope`, `is_dynamic`)
VALUES ('Advanced', 'DEFAULT', 'CapacityManager',
'cluster.storage.operations.exclude', 'Exclude cluster from storage
operations', 'false', now(), 'Cluster', '1');
+
+-- Added in CLOUDSTACK-9340: General DB optimization, 4 cases:
+
+- 1) Incorrect PRIMARY key
+ALTER TABLE `cloud`.`ovs_tunnel_network`
--- End diff --
Here you change this table "ovs_tunnel_network", you dropped the primary
key "id"; Will that remove only the index or the field too?
Also, this change on database structures should be reflected on the pseudo
"JPA" mapping that we use right?
For instance, the class "com.cloud.network.ovs.dao.OvsTunnelNetworkVO" that
represents the "ovs_tunnel_network" table, it is annotated as the "id" field
being the id, and not the "ovs_tunnel_network"
> General DB Optimization
> ---
>
> Key: CLOUDSTACK-9340
> URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340
> Project: CloudStack
> Issue Type: Improvement
> Security Level: Public(Anyone can view this level - this is the
> default.)
>Affects Versions: 4.9.0
>Reporter: Nicolas Vazquez
> Labels: database
> Fix For: 4.9.0
>
>
> h2. General DB Optimization
> In some production environments there were being experimented delays in most
> of the jobs. A search for DB optimization was taken and some deficiencies
> were discovered, we can group them in 4 groups:
> * Incorrect PRIMARY key
> * Duplicate PRIMARY KEY
> * Missing indexes (Add indexes to avoid full table scans)
> * Some views query (Change view to improve account retrieval speed)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[
https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15234398#comment-15234398
]
ASF GitHub Bot commented on CLOUDSTACK-9340:
Github user rafaelweingartner commented on a diff in the pull request:
https://github.com/apache/cloudstack/pull/1466#discussion_r59146860
--- Diff: setup/db/db/schema-481to490.sql ---
@@ -413,3 +413,89 @@ VIEW `user_vm_view` AS
-- Add cluster.storage.operations.exclude property
INSERT INTO `cloud`.`configuration` (`category`, `instance`, `component`,
`name`, `description`, `default_value`, `updated`, `scope`, `is_dynamic`)
VALUES ('Advanced', 'DEFAULT', 'CapacityManager',
'cluster.storage.operations.exclude', 'Exclude cluster from storage
operations', 'false', now(), 'Cluster', '1');
+
+-- Added in CLOUDSTACK-9340: General DB optimization, 4 cases:
+
+- 1) Incorrect PRIMARY key
+ALTER TABLE `cloud`.`ovs_tunnel_network`
+DROP PRIMARY KEY,
+ADD PRIMARY KEY (`id`),
+DROP INDEX `id` ,
+ADD UNIQUE INDEX `i_to_from_network_id` (`to` ASC, `from` ASC,
`network_id` ASC);
+
+- 2) Duplicate PRIMARY KEY
+ALTER TABLE `cloud`.`user_vm` DROP INDEX `id_2` ,DROP INDEX `id` ;
--- End diff --
Here you remove duplicated primary keys.
You are removing indexes and not primary keys per se, right?
I see you removing two indexes, does that mean that the table has another
one? A third one?
Because if you remove the ID field index, if we use a select by that field,
it would cause a full table scan, right?
> General DB Optimization
> ---
>
> Key: CLOUDSTACK-9340
> URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340
> Project: CloudStack
> Issue Type: Improvement
> Security Level: Public(Anyone can view this level - this is the
> default.)
>Affects Versions: 4.9.0
>Reporter: Nicolas Vazquez
> Labels: database
> Fix For: 4.9.0
>
>
> h2. General DB Optimization
> In some production environments there were being experimented delays in most
> of the jobs. A search for DB optimization was taken and some deficiencies
> were discovered, we can group them in 4 groups:
> * Incorrect PRIMARY key
> * Duplicate PRIMARY KEY
> * Missing indexes (Add indexes to avoid full table scans)
> * Some views query (Change view to improve account retrieval speed)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[
https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15234400#comment-15234400
]
ASF GitHub Bot commented on CLOUDSTACK-9340:
Github user rafaelweingartner commented on a diff in the pull request:
https://github.com/apache/cloudstack/pull/1466#discussion_r59146919
--- Diff: setup/db/db/schema-481to490.sql ---
@@ -413,3 +413,89 @@ VIEW `user_vm_view` AS
-- Add cluster.storage.operations.exclude property
INSERT INTO `cloud`.`configuration` (`category`, `instance`, `component`,
`name`, `description`, `default_value`, `updated`, `scope`, `is_dynamic`)
VALUES ('Advanced', 'DEFAULT', 'CapacityManager',
'cluster.storage.operations.exclude', 'Exclude cluster from storage
operations', 'false', now(), 'Cluster', '1');
+
+-- Added in CLOUDSTACK-9340: General DB optimization, 4 cases:
+
+- 1) Incorrect PRIMARY key
+ALTER TABLE `cloud`.`ovs_tunnel_network`
+DROP PRIMARY KEY,
+ADD PRIMARY KEY (`id`),
+DROP INDEX `id` ,
+ADD UNIQUE INDEX `i_to_from_network_id` (`to` ASC, `from` ASC,
`network_id` ASC);
+
+- 2) Duplicate PRIMARY KEY
+ALTER TABLE `cloud`.`user_vm` DROP INDEX `id_2` ,DROP INDEX `id` ;
+ALTER TABLE `cloud`.`domain_router` DROP INDEX `id_2` ,DROP INDEX `id` ;
+ALTER TABLE `cloud`.`vm_instance` DROP INDEX `id_2` ,DROP INDEX `id` ;
+ALTER TABLE `cloud`.`account_vlan_map` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`account_vnet_map` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`baremetal_rct` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`cluster` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`conditions` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`counter` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`data_center` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`dc_storage_network_ip_range` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`dedicated_resources` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`host_pod_ref` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`iam_group` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`iam_policy` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`iam_policy_permission` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`image_store_details` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`instance_group` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`netapp_lun` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`netapp_pool` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`netapp_volume` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`network_acl_item_cidrs` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`network_offerings` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`nic_secondary_ips` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`nics` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`op_ha_work` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`op_host` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`op_host_transfer` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`op_networks` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`op_nwgrp_work` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`op_vm_ruleset_log` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`op_vpc_distributed_router_sequence_no` DROP INDEX
`id` ;
+ALTER TABLE `cloud`.`pod_vlan_map` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`portable_ip_address` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`portable_ip_range` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`region` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`remote_access_vpn` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`snapshot_details` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`snapshots` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`storage_pool` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`storage_pool_details` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`storage_pool_work` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`user_ip_address` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`user_ipv6_address` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`user_statistics` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`version` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`vlan` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`vm_disk_statistics` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`vm_snapshot_details` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`vm_work_job` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`vpc_gateways` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`vpn_users` DROP INDEX `id` ;
+
+- 3) Missing indexes (Add indexes to avoid full table scans)
--- End diff --
did you execute some evaluation to see which fields were being most used to
create an index for them?
For instance, why did you create an index for the field called "type" of
table "op_it_work"
> General DB Optimization
> ---
>
> Key: CLOUDSTACK-9340
> URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340
> Project: CloudStack
> Issue Type: Improvement
> Secur
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[
https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15234402#comment-15234402
]
ASF GitHub Bot commented on CLOUDSTACK-9340:
Github user rafaelweingartner commented on a diff in the pull request:
https://github.com/apache/cloudstack/pull/1466#discussion_r59147080
--- Diff: setup/db/db/schema-481to490.sql ---
@@ -413,3 +413,89 @@ VIEW `user_vm_view` AS
-- Add cluster.storage.operations.exclude property
INSERT INTO `cloud`.`configuration` (`category`, `instance`, `component`,
`name`, `description`, `default_value`, `updated`, `scope`, `is_dynamic`)
VALUES ('Advanced', 'DEFAULT', 'CapacityManager',
'cluster.storage.operations.exclude', 'Exclude cluster from storage
operations', 'false', now(), 'Cluster', '1');
+
+-- Added in CLOUDSTACK-9340: General DB optimization, 4 cases:
+
+- 1) Incorrect PRIMARY key
+ALTER TABLE `cloud`.`ovs_tunnel_network`
+DROP PRIMARY KEY,
+ADD PRIMARY KEY (`id`),
+DROP INDEX `id` ,
+ADD UNIQUE INDEX `i_to_from_network_id` (`to` ASC, `from` ASC,
`network_id` ASC);
+
+- 2) Duplicate PRIMARY KEY
+ALTER TABLE `cloud`.`user_vm` DROP INDEX `id_2` ,DROP INDEX `id` ;
+ALTER TABLE `cloud`.`domain_router` DROP INDEX `id_2` ,DROP INDEX `id` ;
+ALTER TABLE `cloud`.`vm_instance` DROP INDEX `id_2` ,DROP INDEX `id` ;
+ALTER TABLE `cloud`.`account_vlan_map` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`account_vnet_map` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`baremetal_rct` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`cluster` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`conditions` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`counter` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`data_center` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`dc_storage_network_ip_range` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`dedicated_resources` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`host_pod_ref` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`iam_group` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`iam_policy` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`iam_policy_permission` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`image_store_details` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`instance_group` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`netapp_lun` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`netapp_pool` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`netapp_volume` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`network_acl_item_cidrs` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`network_offerings` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`nic_secondary_ips` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`nics` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`op_ha_work` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`op_host` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`op_host_transfer` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`op_networks` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`op_nwgrp_work` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`op_vm_ruleset_log` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`op_vpc_distributed_router_sequence_no` DROP INDEX
`id` ;
+ALTER TABLE `cloud`.`pod_vlan_map` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`portable_ip_address` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`portable_ip_range` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`region` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`remote_access_vpn` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`snapshot_details` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`snapshots` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`storage_pool` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`storage_pool_details` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`storage_pool_work` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`user_ip_address` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`user_ipv6_address` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`user_statistics` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`version` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`vlan` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`vm_disk_statistics` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`vm_snapshot_details` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`vm_work_job` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`vpc_gateways` DROP INDEX `id` ;
+ALTER TABLE `cloud`.`vpn_users` DROP INDEX `id` ;
+
+- 3) Missing indexes (Add indexes to avoid full table scans)
+ALTER TABLE `cloud`.`op_it_work` ADD INDEX `i_type_and_updated` (`type`
ASC, `updated_at` ASC);
+ALTER TABLE `cloud`.`vm_root_disk_tags` ADD INDEX `i_vm_id` (`vm_id` ASC);
+ALTER TABLE `cloud`.`vm_compute_tags` ADD INDEX `i_vm_id` (`vm_id` ASC);
+ALTER TABLE `cloud`.`vm_network_map` ADD INDEX `i_vm_id` (`vm_id` ASC);
+ALTER TABLE `cloud`.`ssh_keypairs` ADD INDEX `i_public_key` (`public_key`
ASC);
+ALTER TABLE `cloud`.`user_vm_details` ADD INDEX `i_name_vm_
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15234416#comment-15234416 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user serg38 commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-208121230 @rafaelweingartner Here you change this table "ovs_tunnel_network", you dropped the primary key "id"; Will that remove only the index or the field too? Also, this change on database structures should be reflected on the pseudo "JPA" mapping that we use right? For instance, the class "com.cloud.network.ovs.dao.OvsTunnelNetworkVO" that represents the "ovs_tunnel_network" table, it is annotated as the "id" field being the id, and not the "ovs_tunnel_network" > We don't change any table structure but indexes so no changes in the mapping should be required. For ovs_tunnel_network it was the only table in ACS DB that had primary key not based on ID whereas ID was an unique index. In create-schema.sql CREATE TABLE `cloud`.`ovs_tunnel_network`( `id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT, `from` bigint unsigned COMMENT 'from host id', `to` bigint unsigned COMMENT 'to host id', `network_id` bigint unsigned COMMENT 'network identifier', `key` int unsigned COMMENT 'gre key', `port_name` varchar(32) COMMENT 'in port on open vswitch', `state` varchar(16) default 'FAILED' COMMENT 'result of tunnel creatation', PRIMARY KEY(`from`, `to`, `network_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; What we are proposing is to swap them by making ID a primary key and creating a new unique index based on 3 columns `from`, `to` and `network_id`. > Here you remove duplicated primary keys. You are removing indexes and not primary keys per se, right? I see you removing two indexes, does that mean that the table has another one? A third one? Because if you remove the ID field index, if we use a select by that field, it would cause a full table scan, right? That's correct. We are removing indexes that are duplicate to the primary key. In some tables there are 2 such indexes. > did you execute some evaluation to see which fields were being most used to create an index for them? For instance, why did you create an index for the field called "type" of table "op_it_work" > Yes we did. Our prod DB has 1 mil+ VMs and volumes and 20K+ accounts. We started seeing degradation in simple list calls. We reviewed and analyzed all MySQL slow queries to see if any of them use full table scans. So far we found 7 cases where extra index could help. Altogether these changes reduced the load on Mysql by 30% (it doesn't spend time any more constantly doing full table scans) and improved some API calls by up to 200%. >> This will remove from the projection the VMs already removed. Aren't they needed > We analyzed both the code and other DB tables. account_vmstats_view is only used in account_view and only in the join using these clauses LEFT JOIN `account_vmstats_view` `runningvm` ON (((`account`.`id` = `runningvm`.`account_id`) AND (`runningvm`.`state` = 'Running' LEFT JOIN `account_vmstats_view` `stoppedvm` ON (((`account`.`id` = `stoppedvm`.`account_id`) AND (`stoppedvm`.`state` = 'Stopped' Since only Running and Stopped VMs used there is no need to bring Expunged one into the vmstats view. This changes alone improve listAccounts call retrieval from 11 sec to 3 ( x4 improvement). > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15234873#comment-15234873 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rafaelweingartner commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-208285159 Thanks @serg38, You answer helped a lot. I just do not fully understand the deletion of the indexes, for instance, you delete index “id” and “id_2” from table “domain_router”. Does that mean that the table has three (3) indexes? > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15235228#comment-15235228 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user serg38 commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-208387324 @rafaelweingartner. That's correct. 3 indexes: 1 - primary + 2 unique indexes, all based on ID column. Those 2 unique indexes are not needed and only consume disk space. > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15235242#comment-15235242 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rafaelweingartner commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-208393777 @serg38 thanks, now it is clear to me. Thanks for the great job. BTW: it seems that travis is with problems again. > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15235301#comment-15235301 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-208402795 Thanks @serg38 @rafaelweingartner. I rebased master branch and pushed again > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15258583#comment-15258583 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-214831953 @rafaelweingartner @rhtyd @swill I did `push -f` tests start again, I'm getting an error on Travis test due to duplicate field name 'size' in `schema-40to410.sql`, but I added this changes in `schema-481to490.sql`, do you know why is it failing? `[ERROR] Failed to execute goal org.codehaus.mojo:exec-maven-plugin:1.2.1:java (create-schema-simulator) on project cloud-developer: An exception occured while executing the Java class. null: InvocationTargetException: Unable to upgrade the database: Unable to execute upgrade script: /home/travis/build/apache/cloudstack/developer/target/db/db/schema-40to410.sql: Duplicate column name 'size' -> [Help 1]` > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15258738#comment-15258738 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rhtyd commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-214856702 @nvazquez I'm not sure, can you try to reproduce and fix it locally; test with MySQL 5.6 and 5.5 (both major versions) > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15258761#comment-15258761 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rafaelweingartner commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-214861439 On Jenkins I saw an error that is due to environment misconfigurations: > [FATAL] Non-parseable settings /home/jenkins/.m2/settings.xml: unexpected character in markup % (position: START_TAG seen ...\n <%... @14:18) @ /home/jenkins/.m2/settings.xml, line 14, column 18 And then on Travis there is an error that is caused by a wrong table definition: > [ERROR] Failed to execute goal org.codehaus.mojo:exec-maven-plugin:1.2.1:java (create-schema) on project cloud-developer: An exception occured while executing the Java class. null: InvocationTargetException: Unable to upgrade the database: Unable to execute upgrade script: /home/travis/build/apache/cloudstack/developer/target/db/db/schema-481to490.sql: Incorrect table definition; there can be only one auto column and it must be defined as a key -> [Help 1] That seems to be something you changed in "schema-481to490.sql" > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15262753#comment-15262753 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-215529568 @rhtyd @rafaelweingartner I could discover the problem by deleting `cloud` and `cloud_usage` database, then `cloudstack-databases-setup` to create them blank and restarting CS to create schemas. Error was whis: 2016-04-27 08:35:19,850 ERROR [utils.db.ScriptRunner] (main:null) Error executing: ALTER TABLE `cloud`.`remote_access_vpn` DROP INDEX `id` 2016-04-27 08:35:19,851 ERROR [utils.db.ScriptRunner] (main:null) com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Incorrect table definition; there can be only one auto column and it must be defined as a key That shouldn't be a problem because in `schema-410to420-cleanup.sql` it was: ALTER TABLE `cloud`.`remote_access_vpn` DROP primary key; ALTER TABLE `cloud`.`remote_access_vpn` ADD primary key (`id`); However, it was noticed that `schemaXXXtoYYY-cleanup.sql` files hadn't been executed before the failure, so it was decided to move some sentences from `schema-481to490.sql` to `schema-481to490-cleanup.sql` so they can be executed after all `schemaXXXtoYYY.sql` files, it succeeded. > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15262876#comment-15262876 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rafaelweingartner commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-215546030 @nvazquez, What does that mean? First we are executing all of the `schema-XXXtoYYY.sql`, then all of the `schemaXXXtoYYY-cleanup.sql`? Instead of` schema-XXXtoYYY.sql, schemaXXXtoYYY-cleanup.sql` and then `schema-XXX+1toYYY+1.sql, schemaXXX+1toYYY+1-cleanup.sql` I have just looked at the code at "com.cloud.upgrade.DatabaseUpgradeChecker.upgrade(String, String)", that is exactly what happens. First we are executing all of the `schema-XXXtoYYY.sql`, then all of the `schemaXXXtoYYY-cleanup.sql` So, I am ok with your changes to create the "schema-410to420-cleanup.sql" So, I am ok with you moving some scripts to > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15262894#comment-15262894 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-215548103 @rafaelweingartner exactly, I meant to explain that I realized that first all `schema-XXXtoYYY.sql` were executed and after they finished, all `schema-XXXtoYYY-cleanup.sql` were executed, that's why I moved to cleanup sql :) > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15262901#comment-15262901 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rhtyd commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-215548664 @nvazquez that's by design -- all upgrade paths (migration code and the schema files) are run first and after that only the cleanup scripts are ran > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15262909#comment-15262909 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rafaelweingartner commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-215549970 I confess that without looking the code, I thought the same as @nvazquez. First we would execute schema-XXXtoYYY.sql, schemaXXXtoYYY-cleanup.sql and then schema-XXX+1toYYY+1.sql, schemaXXX+1toYYY+1-cleanup.sql. It is good that we have clarified that ;) > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15263084#comment-15263084 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user serg38 commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-215572026 With this change PR passed Travis and Jenkins. @rafaelweingartner and @rhtyd will you be able to give your blessing? > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15263086#comment-15263086 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rafaelweingartner commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-215572438 off course ;) I was only waiting for that LGTM > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15263158#comment-15263158 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user swill commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-215581686 should I be running some sort of CI or validation for this change? How do I validate this works and more importantly, not break anything? I also need another LGTM. Thx. :) @rhtyd have you had a chance to review this? > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15263177#comment-15263177 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rafaelweingartner commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-215583632 If you run your CI tests, it should detect if it breaks something. But more importantly would be to check if the performance was improved. But, I am sure @nvazquez and @serg38 have checked and re-checked that (given the work I have seen them doing here). > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15263196#comment-15263196 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user swill commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-215586085 OK. I will just do a standard CI run then. Thx. > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15266548#comment-15266548 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rhtyd commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-216226707 @nvazquez thanks, can you rebase and squash changes to one commit LGTM, a CI and Travis run should confirm the fix. tag:mergeready > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15266692#comment-15266692 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-216251605 Done, thanks @rhtyd @swill @rafaelweingartner > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15266729#comment-15266729 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user swill commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-216258261 Running CI now... > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15268935#comment-15268935 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user serg38 commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-216574386 All checks have been passed after squashing. @swill did you CI run pass? If so can you merge it please? > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15268941#comment-15268941 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user swill commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-216576269 I was having problems with this in my CI, but I am still trying to determine if it is my environment or not. I will try again once I get master tested (since we fixed a merge conflict last night). > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[
https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15270150#comment-15270150
]
ASF GitHub Bot commented on CLOUDSTACK-9340:
Github user koushik-das commented on a diff in the pull request:
https://github.com/apache/cloudstack/pull/1466#discussion_r61992331
--- Diff: setup/db/db/schema-481to490.sql ---
@@ -413,3 +413,26 @@ VIEW `user_vm_view` AS
-- Add cluster.storage.operations.exclude property
INSERT INTO `cloud`.`configuration` (`category`, `instance`, `component`,
`name`, `description`, `default_value`, `updated`, `scope`, `is_dynamic`)
VALUES ('Advanced', 'DEFAULT', 'CapacityManager',
'cluster.storage.operations.exclude', 'Exclude cluster from storage
operations', 'false', now(), 'Cluster', '1');
+
+- 3) Missing indexes (Add indexes to avoid full table scans)
+ALTER TABLE `cloud`.`op_it_work` ADD INDEX `i_type_and_updated` (`type`
ASC, `updated_at` ASC);
+ALTER TABLE `cloud`.`vm_root_disk_tags` ADD INDEX `i_vm_id` (`vm_id` ASC);
+ALTER TABLE `cloud`.`vm_compute_tags` ADD INDEX `i_vm_id` (`vm_id` ASC);
+ALTER TABLE `cloud`.`vm_network_map` ADD INDEX `i_vm_id` (`vm_id` ASC);
+ALTER TABLE `cloud`.`ssh_keypairs` ADD INDEX `i_public_key` (`public_key`
ASC);
--- End diff --
The public_key field is varchar(5120) and creating an index on this is
failing with error
Error Code: 1071. Specified key was too long; max key length is 767 bytes
> General DB Optimization
> ---
>
> Key: CLOUDSTACK-9340
> URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340
> Project: CloudStack
> Issue Type: Improvement
> Security Level: Public(Anyone can view this level - this is the
> default.)
>Affects Versions: 4.9.0
>Reporter: Nicolas Vazquez
> Labels: database
> Fix For: 4.9.0
>
>
> h2. General DB Optimization
> In some production environments there were being experimented delays in most
> of the jobs. A search for DB optimization was taken and some deficiencies
> were discovered, we can group them in 4 groups:
> * Incorrect PRIMARY key
> * Duplicate PRIMARY KEY
> * Missing indexes (Add indexes to avoid full table scans)
> * Some views query (Change view to improve account retrieval speed)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15270153#comment-15270153 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user koushik-das commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-216751340 I am getting this error during deploy DB "Specified key was too long; max key length is 767 bytes". I have provided another inline comment in the code which is failing. Mysql version is 5.6.12. > Processing upgrade: com.cloud.upgrade.DatabaseUpgradeChecker [WARNING] java.lang.reflect.InvocationTargetException at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.codehaus.mojo.exec.ExecJavaMojo$1.run(ExecJavaMojo.java:297) at java.lang.Thread.run(Thread.java:724) Caused by: com.cloud.utils.exception.CloudRuntimeException: Unable to upgrade the database at com.cloud.upgrade.DatabaseUpgradeChecker.upgrade(DatabaseUpgradeChecker.java:394) at com.cloud.upgrade.DatabaseUpgradeChecker.check(DatabaseUpgradeChecker.java:490) at com.cloud.upgrade.DatabaseCreator.main(DatabaseCreator.java:217) ... 6 more Caused by: com.cloud.utils.exception.CloudRuntimeException: Unable to execute upgrade script: /Users/koushik/code/cloudstack-apache/cloudstack/developer/target/db/db/schema-481to490.sql at com.cloud.upgrade.DatabaseUpgradeChecker.runScript(DatabaseUpgradeChecker.java:306) at com.cloud.upgrade.DatabaseUpgradeChecker.upgrade(DatabaseUpgradeChecker.java:363) ... 8 more Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185) at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:87) at com.cloud.upgrade.DatabaseUpgradeChecker.runScript(DatabaseUpgradeChecker.java:297) ... 9 more [INFO] [INFO] BUILD FAILURE [INFO] [INFO] Total time: 40.361 s [INFO] Finished at: 2016-05-04T11:07:42+05:30 [INFO] Final Memory: 70M/263M [INFO] [ERROR] Failed to execute goal org.codehaus.mojo:exec-maven-plugin:1.2.1:java (create-schema) on project cloud-developer: An exception occured while executing the Java class. null: InvocationTargetException: Unable to upgrade the database: Unable to execute upgrade script: /Users/koushik/code/cloudstack-apache/cloudstack/developer/target/db/db/schema-481to490.sql: Specified key was too long; max key length is 767 bytes -> [Help 1] [ERROR] [ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch. [ERROR] Re-run Maven using the -X switch to enable full debug logging. [ERROR] [ERROR] For more information about the errors and possible solutions, please read the following articles: [ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15270674#comment-15270674 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user serg38 commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-216876560 Thanks @koushik-das. Looks like before 5.6 Mysql auto restricted length of InnoDB indexes to 255 characters without giving trouble if index length is not specified. For public_key index in ssh_keypairs table first 64 bytes is enough to make good index. @nvazquez let's change this index creation to ALTER TABLE `cloud`.`ssh_keypairs` ADD INDEX `i_public_key` (`public_key`(64) ASC); > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15270779#comment-15270779 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user swill commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-216893394 ### CI RESULTS ``` Tests Run: 85 Skipped: 0 Failed: 1 Errors: 0 Duration: 4h 28m 09s ``` **Summary of the problem(s):** ``` FAIL: Test redundant router internals -- Traceback (most recent call last): File "/data/git/cs1/cloudstack/test/integration/smoke/test_routers_network_ops.py", line 483, in test_02_RVR_Network_FW_PF_SSH_default_routes_egress_false "Attempt to retrieve google.com index page should be successful once rule is added!" AssertionError: Attempt to retrieve google.com index page should be successful once rule is added! -- Additional details in: /tmp/MarvinLogs/test_network_2XAB1I/results.txt ``` **Associated Uploads** **`/tmp/MarvinLogs/DeployDataCenter__May_04_2016_05_43_43_5D72GT:`** * [dc_entries.obj](https://objects-east.cloud.ca/v1/e465abe2f9ae4478b9fff416eab61bd9/PR1466/tmp/MarvinLogs/DeployDataCenter__May_04_2016_05_43_43_5D72GT/dc_entries.obj) * [failed_plus_exceptions.txt](https://objects-east.cloud.ca/v1/e465abe2f9ae4478b9fff416eab61bd9/PR1466/tmp/MarvinLogs/DeployDataCenter__May_04_2016_05_43_43_5D72GT/failed_plus_exceptions.txt) * [runinfo.txt](https://objects-east.cloud.ca/v1/e465abe2f9ae4478b9fff416eab61bd9/PR1466/tmp/MarvinLogs/DeployDataCenter__May_04_2016_05_43_43_5D72GT/runinfo.txt) **`/tmp/MarvinLogs/test_network_2XAB1I:`** * [failed_plus_exceptions.txt](https://objects-east.cloud.ca/v1/e465abe2f9ae4478b9fff416eab61bd9/PR1466/tmp/MarvinLogs/test_network_2XAB1I/failed_plus_exceptions.txt) * [results.txt](https://objects-east.cloud.ca/v1/e465abe2f9ae4478b9fff416eab61bd9/PR1466/tmp/MarvinLogs/test_network_2XAB1I/results.txt) * [runinfo.txt](https://objects-east.cloud.ca/v1/e465abe2f9ae4478b9fff416eab61bd9/PR1466/tmp/MarvinLogs/test_network_2XAB1I/runinfo.txt) **`/tmp/MarvinLogs/test_vpc_routers_9HY5WR:`** * [failed_plus_exceptions.txt](https://objects-east.cloud.ca/v1/e465abe2f9ae4478b9fff416eab61bd9/PR1466/tmp/MarvinLogs/test_vpc_routers_9HY5WR/failed_plus_exceptions.txt) * [results.txt](https://objects-east.cloud.ca/v1/e465abe2f9ae4478b9fff416eab61bd9/PR1466/tmp/MarvinLogs/test_vpc_routers_9HY5WR/results.txt) * [runinfo.txt](https://objects-east.cloud.ca/v1/e465abe2f9ae4478b9fff416eab61bd9/PR1466/tmp/MarvinLogs/test_vpc_routers_9HY5WR/runinfo.txt) Uploads will be available until `2016-07-04 02:00:00 +0200 CEST` *Comment created by [`upr comment`](https://github.com/cloudops/upr).* > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15270783#comment-15270783 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user swill commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-216893678 The one failure above is unrelated to this fix and pops up periodically in my environment. Above is a passing CI result... > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15270794#comment-15270794 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user swill commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-216896697 @serg38, I just saw your communication with @koushik-das. I will let him verify the fix you are working on and we will go from there. Thanks... > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15270839#comment-15270839 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-216901961 @koushik-das @swill @serg38 I modified index length for ssh_keypairs table > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15270880#comment-15270880 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user rhtyd commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-216910739 Thanks @nvazquez did you test this against mysql 5.1, 5.6 and 5.7? The Travis run will check against MySQL-Server 5.5 > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15271213#comment-15271213 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user serg38 commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-216967912 @rhtyd I just finished QA of changes on MySQL 5.1, 5.5, 5.6 and 5.7. No issues. 64byte index on ssh_keypairs table is created fine on all DB platforms. @koushik-das To be on the safe side can you re-test in your environment? > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15271938#comment-15271938 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user koushik-das commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-217082822 @serg38 LGTM. Verified on my test env. @swill This is ready for merge. > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15277497#comment-15277497 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user nvazquez commented on the pull request: https://github.com/apache/cloudstack/pull/1466#issuecomment-218043423 Thanks @koushik-das, @swill now that there are 2 LGTM can we have this merged? > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15279478#comment-15279478 ] ASF subversion and git services commented on CLOUDSTACK-9340: - Commit ccf226546007a82594866c82cd4f279758bb91d9 in cloudstack's branch refs/heads/master from [~williamstev...@gmail.com] [ https://git-wip-us.apache.org/repos/asf?p=cloudstack.git;h=ccf2265 ] Merge pull request #1466 from nvazquez/graldboptimization CLOUDSTACK-9340: General DB Optimization## Description In some production environments there were being experimented delays in most of the jobs. A search for DB optimization was taken and some deficiencies were discovered, we can group them in 4 groups: * Incorrect PRIMARY key * Duplicate PRIMARY KEY * Missing indexes (Add indexes to avoid full table scans) * Some views query (Change view to improve account retrieval speed) * pr/1466: CLOUDSTACK-9340: General DB Optimization Signed-off-by: Will Stevens > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15279477#comment-15279477 ] ASF subversion and git services commented on CLOUDSTACK-9340: - Commit ccf226546007a82594866c82cd4f279758bb91d9 in cloudstack's branch refs/heads/master from [~williamstev...@gmail.com] [ https://git-wip-us.apache.org/repos/asf?p=cloudstack.git;h=ccf2265 ] Merge pull request #1466 from nvazquez/graldboptimization CLOUDSTACK-9340: General DB Optimization## Description In some production environments there were being experimented delays in most of the jobs. A search for DB optimization was taken and some deficiencies were discovered, we can group them in 4 groups: * Incorrect PRIMARY key * Duplicate PRIMARY KEY * Missing indexes (Add indexes to avoid full table scans) * Some views query (Change view to improve account retrieval speed) * pr/1466: CLOUDSTACK-9340: General DB Optimization Signed-off-by: Will Stevens > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15279476#comment-15279476 ] ASF subversion and git services commented on CLOUDSTACK-9340: - Commit 8b6e6185f53cbdcfc13cced34ad531a6df3e9b09 in cloudstack's branch refs/heads/master from [~nvazquez] [ https://git-wip-us.apache.org/repos/asf?p=cloudstack.git;h=8b6e618 ] CLOUDSTACK-9340: General DB Optimization > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CLOUDSTACK-9340) General DB Optimization
[ https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15279480#comment-15279480 ] ASF GitHub Bot commented on CLOUDSTACK-9340: Github user asfgit closed the pull request at: https://github.com/apache/cloudstack/pull/1466 > General DB Optimization > --- > > Key: CLOUDSTACK-9340 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340 > Project: CloudStack > Issue Type: Improvement > Security Level: Public(Anyone can view this level - this is the > default.) >Affects Versions: 4.9.0 >Reporter: Nicolas Vazquez > Labels: database > Fix For: 4.9.0 > > > h2. General DB Optimization > In some production environments there were being experimented delays in most > of the jobs. A search for DB optimization was taken and some deficiencies > were discovered, we can group them in 4 groups: > * Incorrect PRIMARY key > * Duplicate PRIMARY KEY > * Missing indexes (Add indexes to avoid full table scans) > * Some views query (Change view to improve account retrieval speed) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
