Re: About performance for Database of millions of records

[vishwas samant]

Hi,

I believe, Performance tuning is cont. process, not a one time activity.

As you wrote, you have database with millions of rows and want to improve performance then you may get something from below:

Server level:
1. check RAM and swap utilization (adjust your my.cnf to avoid swaping)
2. monitor server disk IO (you can use top, sar etc for this. track iowaits )
3. CPU utilization (track cpu use on system and userio)

Database engine:
1. select proper engine as per you requirement (myisam / innodb)

Proactive db activities:
1. Optimize tables
2. analyze tables
( as per inserts and updates)

my.cnf:
1.key_buffer, read_buffer_size, read_rnd_buffer_size /innodb_buffer_pool_size
2. sort_buffer_size, myisam_sort_buffer_size
3.query_cache_size

sql tuning:
1. keep digging slow query logs for bad queries.
2. look at where clauses of each sql query and explain each query to cross checl proper execution plan.