Question

Identify some bottlenecks in DBMS performance and propose some solutions used in DBMS performance tuning. Provide your recommendations and discuss some industry best practices.

Answer 1

Database is noting but storage to store data. We can perform any type of operations on that data that isstored in database. We can perform,alter,delete,retrive operations on data.While tha process depends on the perffoemanace of Database management system.

Database performance is most critical for application performance as this is the main reason for performance bottlenecks.Database software can really impact the whole system performance.

Here i'm listing some of the reasons for bottelenecks in DBMS performance.

• Iineffective SQL statement, that may be incorrect.
• Insufficient query plan.
• Issues in DB configuration or software may be out of date.
• Insufficient data cache storage
• lack of DB connections
• Excess rows at a time processing
• Missing indexes
• Outdated statistics
• Deadlocks

Suppose while we are trying to retrieve data, if there mey be a exess of data which is not indexed takes more time.
In every server there are many inter connections between users or clients or between servers, lack of connectivity also effects data mining.Deadlocks is the one of the common reason, thet some processes in server are strucked by some activities which are never end and we need to clear them. Otherwise they effects performance.
Indexes are introduced to point the data and used to retrieve data immediatly, missing indexes causes performance bottelnecks.

We can avvoid bottelencks by following ways:

Reducing unnecessery Object Allocations:
We all know that Garbage Collector highly impact system performance.While there is less space, allocation of unnecessery objects leads to low performance.
We should need to avoid distribution related objects, unnecessery array objects, pre-partition state objects. These objects are unnecessery and by avoiding we can avoid bottleneck performnace.

By redusing lock content:
Ofcource,Security of data is important but while locking the more data , most processors are in lock state and chance of taking requests decreases. Hence it decrease the performance.
Regularly we need to remove unnecessery locks .There are some locks that are needed that are useful to add transaction requests to queues in the
system. Thus, we cannot remove them entirely but we canreduce the amount of time that the locks are held.

Maintaining thread allocation:
If the system is executing only single-partition transactions,the PE threads should not conflict with each other.
But we see several PE threads that areblocked on each other waiting for network resources.
This is because that different PE threads try to put client response message to Post Queue thread at the same time.

By using the logging threads to resolve the lock contention between PE threads with the Post Queue thread.
Each PE thread passes client responses to its logging thread directly. This allows the PEthreads to go back and process more incoming transactions without blocking.