Basic steps while dealing with application slowness due to database

In this blog I will share my experience of improving web application performance. Application slowness is often faced in large scale applications. Slowness can happen due to multiple factors and one of the major factor is because of bad database design.

Recently I got a chance to work on an application optimization task. The whole activity includes many areas that are focused and optimized. The main performance improvements are though after database optimizations are done. I am listing down some of the major activities that are performed and found very helpful in finding performance improvement areas.

Is it really a database problem?

Get web server traces and find if there is performance issue in the application itself. Before saying that DB is the only issue just double check by getting and comparing the web server traces and SQL-Profiler traces. Confirm that most of the time taken by request is through database and not something from application itself.

Execute and check SQL-Profilers

Execute the SQL-Profiler and check the queries/stored-procedures that are taking most of time. Notice both duration and read counts in causing application slowness.

Check proper indexes

From the profiler traces one can easily identify the queries or stored-procedures that are taking time. Once those are identified, first thing you have to look is to check your indexes on tables used in queries or stored-procedures. Add appropriate indexes on required columns.

Execute query execution plans

Before I was not very friendly with this feature of Microsoft SQL Server. The Actual Execution Plan of a particular query or SP is very helpful in finding out the specific area that is taking time. The execution plan also suggests the indexes that can fasten up the execution. The suggested indexes can be applied on tables and the impact can be re-checked by executing query/SP again.

Use JOIN instead of IN statements on large data

Other than indexes do go through the statements either those can be overwritten more efficiently. For example instead of using IN clause use JOINS if the data is too much for IN clause.

Check for LOCKS

Specifically taking about MSSQL, it has different pre-defined SP calls that can hep in identifying locking information. For example sp_who2 and sp_lock SPs can be called to get locking information. Statements can be locked due to synchronous Update/Read hits are made on a same table in database. One of the statements can be blocked/locked until other statement releases the table lock.

Use WITH (NOLOCK) where ever required

Do not use WITH(NOLOCK) everywhere. Use it where ever required. I am not going to mention the details of using/not-using WITH(NOLOCK) but I will suggest to read some helpful articles related to this.

Check database statistics

Database statistics can be find out from sys.stats system table. sp_updatestats command can be used to update database statistics. Index rebuilding can also happen statistics update. These activities are not mandatory but if for some reason your database indexes goes bad you have to update/rebuild them. Bad database indexes cannot only return false results but can also make the query/SPs very slow to execute.

rizzz86

Installing DBDesigner 4 on Ubuntu

The only way I found to install DBDesigner 4 on Ubuntu is as follows:

• Download the *.rpm file of DBDesigner.

• Download the library package and extract it using:

tar xzvf kylixlibs3-borqt-3.0-2.tar.gz

• After extracting it do the following:

sudo cp kylixlibs3-borqt/libborqt-6.9.0-qt2.3.so /lib/libborqt-6.9-qt2.3.so

Now your DBDesigner will be working, the executable file DBDesigner4 can be found at /opt/DBDesigner.

Found the solution on the following link.

rizzz86

My Experience of Installing MongoDB on Ubuntu

In my last post I have write some very basic and initial points related to MongoDB. That was my initial observation of what MongoDB is and what are its basic features.

This article does not contains the detailed step by step tutorial of how to install MongoDB on Ubuntu. There are already lots of articles available on the installation of MongoDB on Ubuntu &Windows operating systems. In this post I am going to write my experience of Installing MongoDB on Ubuntu.

There are two common ways of installing any kind of software on Ubuntu. One is by using the "Ubuntu Software Center" and other is to download the package and install it manually. Option one is commonly used by users as it is very simple and it automatically download and install the target software on Ubuntu. In the case of MongoDB both option can be used for installation.

For testing purpose I have installed MongoDB using the Software Center (you can also use apt-get command that also download and install package from Software Center). Initially it works good by using it from terminal. I can able to start and stop MongoDB and execute different types of queries. The problem begins when I try to use MongoDB programmaticaly. I cannot able to connect with MongoDB using Java and Eclipse IDE even MongoDB is running and can be used from terminal. So, I have removed all the packages of MongoDB and install it manually. After that I am able to use it via terminal as well as programmatically.

This seems to be something related to version issue as also mentioned on this link. If someone knows what is the actual reason of this different behavior on different installation options do comment here. This is what I have experienced when installing and using MongoDB on Ubuntu.

rizzz86

MongoDB: {name:"mongo", type:"DB"}

“MongoDB” as the name suggest, is a database that can be used to store data and apply other operations on the data later on. It is not a relational database like (MySQL, Oracle, MSSQL etc), but it provides all the basic operations that eases developer’s life in playing with the stored content. The name is a bit funny for me when I first heard it, but really it is built on some serious concept that opens number of questions of something alternative of relational databases. The concept is of NoSql / non-relational database.

MongoDB (from "humongous") is a scalable, high-performance, open source, document-oriented database. Written in C++

RDBMS has ruled the database world alone for a long time and always remains the only option for most of the software industry to go for it as their first option. Yes, lots of questions are there to choose which RDBMS to use and how the data will be structured within it. With some powerful concept of querying, maintain the integrity, indexing of data etc within RDBMS how come MongoDB’s concept will compete with it? What is the concept on which MongoDB is built? Is MongoDB fully replaceable to RDBMS? Are there other databases built on the same concept? Many more questions are there in this debate, so just have a look how MongoDB is different from relational databases.

MongoDB store data in documents. Yes, a document that have key-value pair. There is a predefined structure of MongoDB that starts with Database -> Collections -> Documents & -> Fields. So there is not any brainstorming sessions on how to structure our data. Just answer “How many collections do we have, and what are they? “.

One of the most important features of MongoDB is that it provided Indexing on data. This feature makes it different from other No SQL databases. It also support to query data from database. 

 If you're moving to MongoDB from a relational database, you'll find that many SQL queries translate easily to MongoDB's document-based query language. 

Other important features include Replication, Auto-Sharding, Batch Processing etc. Detail of each feature will be part of other posts as soon as I explore them and create some examples.

rizzz86

 

MySQL Import/Export Commands

Importing and Exporting through commands for mysql:

Import File into Mysql:

• mysql -ppassword -hlocalhost schemaname < /filePath/file.sql

Export Mysql Schema in File:

• mysqldump -uusername -ppassword database_name > dump.sql
• mysqldump -uusername -ppassword -no-data database_name > dump.sql

rizzz86

DBDesigner 4 (ERD Generation Tools)

I have used DBDesigner-4 to model my ERD diagram on a Linux operating system. The good thing about DBDesigner is that it is opensource and can be used on both Windows and Linux platforms.

DBDesigner 4 is a visual database design system that integrates database design, modeling, creation and maintenance into a single, seamless environment. It combines professional features and a clear and simple user interface to offer the most efficient way to handle your databases. DBDesigner 4 is developed and optimized for the open source MySQL-Database to support MySQL users with a powerful and free available design tool. All MySQL specific features have been built in to offer the most convenient way to design and keep control of your MySQL-Databases.

DBDesigner provides number of features while modeling an ERD including:

• drop drag foreign key field creation,
  
• "regions" to group similar tables,
  
• exporting ERD in image format,
  
• generate SQL scripts,
  
• connect and open tables directly from database (i cannot able to perform that one),
  
• view ERD in query mode etc..

After designing full fledged ERD, I have also come up with some cons and limitations of this tool. Some of them are as follows:

• one of the biggest limitation is that it is developed specifincally for MySQL database. Which means that you cannot switch between databases by only using this tool.
• i haven't found in the whole tool that 'How to add a CONSTRAINT on any table ;('. I have to add all the constraints manually (in the script) at the end of ERD.
• why can,t I change the name of the foreign key in any table ;-?. The name of foreign is fixed (i.e. tablename_id) and cannot be edited.
• no type of comparisons at any level.

Overall a mediocre tool for MySQL based ERD modeling. I will recommend to use this tool if you are using myslq and your application scope is also not huge.


rizzz86

iBatis

Trying to design something like iBatis. Getting help from following link(s):

http://opensource.atlassian.com/confluence/oss/display/IBATIS/iBATIS+3.0+Whiteboard



rizzz86

Database Audit Trail

Its been a great experience for me to design a whole ERD of an ERP from the scratch. As a Developer I have found many new ideas and techniques for designing a proper database design. These days I am working with my senior members on Leaving an Audit Trail in the Database.

By leaving an Audit Trail in the database, one can keep track of all the activities (in the form of operations) going on the database. All this is concerned with the security of database. I am new to this important feature of Database and in a brainstorming session comes up with numerous ways to design it.

There are two most common ways to achieve this task, both have their pros and cons.

• One is to maintain a single table in the database that stores all the actions within a database.
• Another is to create a an Audit table for every table in the database.

Since our application consist of numerous tables having bulk of data load, it will be a good practice to have a separate table for every table. It can be possible to maintain a single table for all database tables but searching on a single flat table will be quite time consuming.

(More to Add)