[jboss-jira] [JBoss JIRA] Commented: (JBPORTAL-2444) Add primary keys to JBP_PORTAL_MODE and JBP_PORTAL_WINDOW_STATE tables

[Chris Laprun (JIRA)]

    [ https://jira.jboss.org/jira/browse/JBPORTAL-2444?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12481180#action_12481180 ] 

Chris Laprun commented on JBPORTAL-2444:
----------------------------------------

Unfortunately, looking into the issue more closely, it won't be possible to change the DB creation scheme for the 2.7 / EPP 4.3 scope as this would imply a change in the Hibernate mapping. I do agree with you that the current mapping is less than ideal but the solution would require changing window states and modes from aggregrated objects to properly persisted entities (which would indeed remove all duplication and performance issue). However, this is not something that can be done in a backward compatible way and is therefore not possible to be done for the 2.7 / EPP 4.3 scope.

> Add primary keys to JBP_PORTAL_MODE and JBP_PORTAL_WINDOW_STATE tables
> ----------------------------------------------------------------------
>
>                 Key: JBPORTAL-2444
>                 URL: https://jira.jboss.org/jira/browse/JBPORTAL-2444
>             Project: JBoss Portal
>          Issue Type: Task
>      Security Level: Public(Everyone can see) 
>    Affects Versions: 2.7.2 Final
>         Environment: Testing JBoss Portal with a large user base. The database (MS SQL Server) has 100.000 users with dashboards. And the following records in the "Top tables":
> 2.500.100 JBP_PORTAL_OBJECT_PROPS
> 500.031 JBP_OBJECT_NODE
> 300.018 JBP_WINDOW
> 500.015 JBP_PORTAL_MODE
> 100.002 jbp_users
> 500.031 JBP_PORTAL_OBJECT
> 300.009 JBP_PORTAL_WINDOW_STATE
> 200.035 JBP_PORTAL_OBJECT_DNAMES
> 100.002 jbp_role_membership
> 100.008 JBP_PAGE
> 100.003 JBP_PORTAL
>            Reporter: Martin Putz
>            Assignee: Chris Laprun
>
> We have found the most "heavy/cost" query during the login, which produces a continue 100% use of the CPU during 20 minutes in our database server, is the following one:
> select portalobje0_.PK as PK64_0_,
> portalobje0_.LISTENER as LISTENER64_0_,
> portalobje0_4_.INSTANCE_REF as INSTANCE2_72_0_,
> case
> when portalobje0_1_.PK is not null then 1
> when portalobje0_2_.PK is not null then 2
> when portalobje0_3_.PK is not null then 3
> when portalobje0_4_.PK is not null then 4
> when portalobje0_.PK is not null then 0
> end as clazz_0_, declaredpr1_.OBJECT_KEY as OBJECT1_2_,
> declaredpr1_.jbp_VALUE as jbp2_2_,
> declaredpr1_.NAME as NAME2_,
> modes2_.PK as PK3_, modes2_.name as name3_,
> windowstat3_.PK as PK4_, windowstat3_.name as name4_
> from JBP_PORTAL_OBJECT portalobje0_
> left outer join JBP_CONTEXT portalobje0_1_
> on portalobje0_.PK=portalobje0_1_.PK
> left outer join JBP_PORTAL portalobje0_2_
> on portalobje0_.PK=portalobje0_2_.PK
> left outer join JBP_PAGE portalobje0_3_
> on portalobje0_.PK=portalobje0_3_.PK
> left outer join JBP_WINDOW portalobje0_4_
> on portalobje0_.PK=portalobje0_4_.PK
> left outer join JBP_PORTAL_OBJECT_PROPS declaredpr1_
> on portalobje0_.PK=declaredpr1_.OBJECT_KEY
> left outer join JBP_PORTAL_MODE modes2_
> on portalobje0_.PK=modes2_.PK
> left outer join JBP_PORTAL_WINDOW_STATE windowstat3_
> on portalobje0_.PK=windowstat3_.PK
> where portalobje0_.PK= 8
> 8 is the general dashboard object
> Our DBA has analized the query and tables related and found in the Execution Plan two table scans. One in JBP_PORTAL_MODE (half million records) and the second one in JBP_PORTAL_WINDOW_STATE (300 K records). The problem with the table scan is that the DBMS is not using indexes.
> JBP_PORTAL_MODE has 5 record per user in the databases all with names 'help', 'admin', 'edit', 'edit_defaults', 'view' and your only index is a FK (Foreign key) but there is no Primary Key.
> JBP_PORTAL_WINDOW_STATE has 3 record per user in the databases all with names 'maximized', 'minimized', 'normal' and again your only index is a FK (Foreign key) but there is no Primary Key.
> Our DBA creates those two new Primary Keys
> ALTER TABLE [dbo].[JBP_PORTAL_MODE] ADD  CONSTRAINT [PK_JBP_PORTAL_MODE] PRIMARY KEY CLUSTERED
> (
> [PK] ASC,
> [name] ASC
> )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
> ALTER TABLE [dbo].[JBP_PORTAL_WINDOW_STATE] ADD  CONSTRAINT [PK_JBP_PORTAL_WINDOW_STATE] PRIMARY KEY CLUSTERED
> (
> [PK] ASC,
> [name] ASC
> )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
> With these two new indexes the query uses the indexes and the performance is much better. Even more, the use of the CPU reach the 100% but only for less than a minute and in average is used 55%
> Could you please include this PK in your Portal DB creation?

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira