Re: Database having 10K transactions on daily basis

[James Day]

Several possibilities but using a master-slave setup is one of the better ways. If your application supports it you can easily add as any slaves as you need to handle the reads, while the master only needs to deal with the writes.

Since you mention transactions I assume that you're using the InnoDB engine. That can be helped by a write buffering disk controller and/or by putting the log files on different drives from the database.

There are many sections in the manual which discuss performance tuning for queries. Looking at the slow query log or using the free third party tool MYTOP and sorting its results into decreasing time order with the i option can help you to identify troublesome queries.

Using ALTER TABLE ENGINE=INNODB (asusming you use innoDB) can make a significant difference, particularly for tables with very regular changes of the data stored in each row. You might experiment to find the best frequency for using it. In my case I can see some visible benefits even doing it daily on some tables, while others can wait a month.