Performance tuning while Working with large database

Performance is very important with any application.In this article, I will explain how can improve Performance with large databases.
If your database tables have millions of records then a simple SQL query will take 3-4 mins.but ideal time for a query should be at max 5 sec. Now the Question comes “How can improve performance with large databases.“ Now I am going explain the techniques or guideline which can improve database performance.

There are there main level where you can tune database performance :

1. Database Design level :

1. Select proper datatypes and size for table’s fields.
2. Use Indexing for primary key constraint or other constraint if required.
3. Use table partitioning if table contains millions of records.
4. Use appropriate Database engine and encoding.

2. Database Sever configuration level :

1. Increase database server hardware configuration such as RAM,CPU,Cache.
2. Properly configure the database setting

For example
max_connections=500
key_buffer_size=384M
max_allowed_packet=16M
table_cache=256
sort_buffer_size=2M
read_buffer_size=2M
join_buffer_size=2M
read_rnd_buffer_size=2M
max_heap_table_size=256M
tmp_table_size=256M
myisam_sort_buffer_size=64M
3. Query level :

1.  Write optimized query.
2.  Properly open and close data connection.

Google+