Views: 4015
Last Modified: 10.10.2012
The database optimization is one of the most important problems you have to elaborate on to get the whole system fast and stable.
You should have a clear understanding that a standard database configuration usually implies minimum hardware, memory and disk space usage. Typical configurations cannot, and never consider your specific hardware and software. You must configure your database manually to get highest possible performance.
A common optimization strategy can be devises as follows.
- Minimum disk read operations. Increase the cache buffer to minimize database disk access.
- Refrain from sort operations at disk. Increase sort buffer to avoid two-pass or on-disk sorting.
- Increase concurrent queries by running many database processes; choose the proper data storage format to support concurrent queries.
- Buffer transaction disk write operations. You have to configure the database so that changes would not be written to disk immediately. This will apparently make the disk (and SQL) function calls much less time consuming and faster.
There are not so many common recommendations on the database configuration that can be given. A range of different database types exists; each is a complex and involved software application. To fine-tune your database, read the database documentation.
However, we can win a serious advantage from the creation of the two-tier configuration. The memory consumption is well balanced so we can derive the exact size of memory that we can reserve for the database. In most systems 60% to 80% of memory can be allocated for database, which allows to improve the overall performance significantly.
In the next lessons we shall discuss the most common approaches to optimizing MySQL and Oracle databases.