You are viewing a plain text version of this content. The canonical link for it is here.

Posted to derby-user@db.apache.org by kosurusekhar <ko...@gmail.com> on 2016/02/05 13:22:02 UTC

Performance issue with same index name in multiple schemas

Hi All,

We are having three schemas with almost same table structure, indexes,
queries, stored procedures in one database. But in one schema we are facing
some performance issue. We are having same index names and table names in
all schemas. In another schemas it is running without any problem. In one
schema with basic load only application is becoming slow. 

Is there any relationship with table names and index names with multiple
schemas? What ever we have queries/stored procedure's with performance
issues, these queries/stored procedure's running with out any issue in
another schema's. I am not understanding what is going wrong.

I could see some times single transaction is locking couple of tables more
than once and not releasing. At this point of time we are feeling the
slowness. 

Please help me with some points to sort out this situation.

Thanks in advance.

Regards
Sekhar.



--
View this message in context: http://apache-database.10148.n7.nabble.com/Performance-issue-with-same-index-name-in-multiple-schemas-tp145507.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Performance issue with same index name in multiple schemas

Posted by "Bergquist, Brett" <BB...@canoga.com>.

You should run your queries and take a look at the query plans being used.  It may be possible that your indexes are not being used as you think.   See

http://db.apache.org/derby/docs/10.10/tuning/index.html

the section on "How you use the RUNTIMESTATISTICS attribute”

Possibly the statistics used by the optimizer are not up to date.  Initially when an index is created and if the table is empty, no statistics are created for the index and subsequently the optimizer may not choose to use the index even though later there may be many rows in the table and the index may be the preferred mechanism.

Also take a look at "Selectivity and cardinality statistics” and especially how to update the statistics in

http://db.apache.org/derby/docs/10.10/ref/index.html

for the “SYSCS_UTIL.SYSCS_UPDATE_STATISTICS” function.  Also look to see if the index statistics daemon is enabled.   Look at the “derby.storage.indexStats.auto” property.

> On Feb 5, 2016, at 7:22 AM, kosurusekhar <ko...@gmail.com> wrote:
>
> Hi All,
>
> We are having three schemas with almost same table structure, indexes,
> queries, stored procedures in one database. But in one schema we are facing
> some performance issue. We are having same index names and table names in
> all schemas. In another schemas it is running without any problem. In one
> schema with basic load only application is becoming slow.
>
> Is there any relationship with table names and index names with multiple
> schemas? What ever we have queries/stored procedure's with performance
> issues, these queries/stored procedure's running with out any issue in
> another schema's. I am not understanding what is going wrong.
>
> I could see some times single transaction is locking couple of tables more
> than once and not releasing. At this point of time we are feeling the
> slowness.
>
> Please help me with some points to sort out this situation.
>
> Thanks in advance.
>
> Regards
> Sekhar.
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Performance-issue-with-same-index-name-in-multiple-schemas-tp145507.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.

Canoga Perkins
20600 Prairie Street
Chatsworth, CA 91311
(818) 718-6300

This e-mail and any attached document(s) is confidential and is intended only for the review of the party to whom it is addressed. If you have received this transmission in error, please notify the sender immediately and discard the original message and any attachment(s).

Re: Performance issue with same index name in multiple schemas

Posted by kosurusekhar <ko...@gmail.com>.

Hi Kristian,

 Thanks for the reply, Yes the insertion rate and accessing Stored
procedures and queries are exactly same. Coming to load wise in other
schema's with huge load also it is not creating problem. In this schema with
basic load, I could observe the hanging behavior. 

I have observed the "Lock_Table" & "Transaction_Table", with one
Transaction_ID I could see many Row level locks and Table level locks. That
to Table level lock count is always more than 2 or 3.

Even for "Selection query" also it is having the table level lock. 

Can you please guide me how to catch the root cause?

Thanks in Advance!

Regards
Sekhar.



--
View this message in context: http://apache-database.10148.n7.nabble.com/Performance-issue-with-same-index-name-in-multiple-schemas-tp145507p145580.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Performance issue with same index name in multiple schemas

Posted by Kristian Waagan <kr...@gmail.com>.

Den 05.02.2016 13.22, skrev kosurusekhar:
> Hi All,
> 
> We are having three schemas with almost same table structure, indexes,
> queries, stored procedures in one database. But in one schema we are facing
> some performance issue. We are having same index names and table names in
> all schemas. In another schemas it is running without any problem. In one
> schema with basic load only application is becoming slow. 

Hi Sekhar,

Are the schemas approximately the same size in terms of data inserted
into the database?
Also, are the access patterns / loads roughtly the same?

Otherwise I'd follow some of Brett's pieces of advice. If you can
identiy the slow query (and the tables / indexes involved), you can
compare the plans for the performant schema and the slow schema.


Regards,
-- 
Kristian

> 
> Is there any relationship with table names and index names with multiple
> schemas? What ever we have queries/stored procedure's with performance
> issues, these queries/stored procedure's running with out any issue in
> another schema's. I am not understanding what is going wrong.
> 
> I could see some times single transaction is locking couple of tables more
> than once and not releasing. At this point of time we are feeling the
> slowness. 
> 
> Please help me with some points to sort out this situation.
> 
> Thanks in advance.
> 
> Regards
> Sekhar.
> 
> 
> 
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Performance-issue-with-same-index-name-in-multiple-schemas-tp145507.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>