There are optimization techniques for database server, majorly managing system configuration rather than tweaking SQL statements. It is suitable for DBAs who desire to assure performance as well as scalability over the servers they handle, for developer initiating installation scripts which comprise of establishing the database and for those running MySQL themselves for development, testing and more to enhance their productivity.
System Factors
Some system level aspects also impact the performance in a great way:
• If you have sufficient RAM, you can get rid of all swap devices. Often OS use a swap device in some regards regardless of having free memory.
• Ignore exterior locking for MyISAM tables. The default is for exterior locking to be restricted. The exterior locking and skip exterior locking alternatives unambiguously enable and disable exterior locking, Disabling exterior locking doesn’t impact MySQL functionality till the time you run just one server. Make sure you take down the server before running myisamchk. On few systems, it is important to disable exterior locking because it won’t work.
You cannot disable the external locking when you run several MySQL servers on the similar data, or when you run myisamchk to evaluate a table without seeing the server to level and lock the tables primarily. Remember that making use of several MySQL servers to evaluate the same data synchronously isn’t usually recommended, except when you’re utilizing NDB cluster.
Optimizing Disk I/O
It shows pathways to organize storage devices when you can devote better and quicker storage hardware to the database server.
Disk seeks are a big performance blockage. The issue becomes more obvious when the data amount commences to grow so big that efficient caching gets impossible.
Enhance the number of present disk spindles by symliking files to other disks or disks striping.
A good idea is to differ the RAID level according to the critical kind of data.
Using NFS with MySQL
You need to be cautious when thinking of using NFS with MySQL. Possible problems which differ by OS and NFS version comprise of:
• Log files and MySQL data files sited on NFS volumes get locked and are unavailable for usage.
• Data inconsistencies produced because to messages received out of order or poor network traffic. To get rid of it, make use of TCP with hard and intr mount support.
• High file size restrictions.
Use symbolic links
You can shift database from database directory to any other place or replace it with symbolic links to a new place. You may wish to do this, for instance, to shift a database to a file system with higher free space or enhance your system’s speed by spreading your table to varying disks.
The suggested idea to do it is to symlink complete database directories to a separate disk. Symlink MYISAM tables just as a last choice.
1. You can use symbolic links for databases on Unix
2. You can use symbolic links for MyISAM tables on Unix
3. You can use symbolic links for databases on Windows
