MySQL is the world’s most popular relational database but many people don’t realise that their MySQL server is not working up to its full potential. Most people use default values and do not investigate further to improvise its working. Optimisation technique is more of changes in the managing system rather than making any changes in the SQL statements. These tips are for the DBA’s who desire to scale and improve their performance over the servers that they handle, for the developer who initiates the installation scripts, which establishes the database and for the people who run MySQL developers themselves to enhance their productivity.
1) System Factors
You can get rid of all your swap devices if you have sufficient memory. But OS still use a swap device anyway even if there is free space.
It’s always better to ignore the external locking for MyISAM tables. In the default settings, the external locking is restricted. The option of skip external locking alternatives disables the external locking systems. Disabling the external locking does not affect the functionality of MySQL at all till you are running only one server. You will have to take down the server before running myisamchk. A few systems will not even work without having the external locking removed. External locking cannot be enables if several servers are running on the same data. That is why it is never recommended to use MySQL to evaluate the same data simultaneously, except when you are using the NDB cluster.
2) Optimizing Disk
The disk is responsible to show the different pathways to organize storage devices. You can devote better and quicker storage to hardware to the database server. Disks are big performance blockers. The issue grows more prominent when the data amounts become so huge that efficient caching gets impossible. You will have to enhance number of disk spindles by symlinking files to other disks or by disk striping. Another way is to differ the RAID levels according to sensitivity of the data.
3) Configuration Optimization
All MySQL users should first and foremost upgrade their server to the latest version that is 5.7 that has much better defaults than its predecessors but it is easier to tweak them. You will have to make a few manual tweaks in the buffer pool. The buffer pool is the storage for caching data and indexes.
4) Indexes
Indexes are the main point for any hobbyist DB admins. On comprising the MySQL index to a normal book index, you know that an index lets you reach the desired content faster, without an index you will have to keep on searching till you reach your desired page. Therefore, adding indexes to the database speed up information finding and problem solving. But the index will also have to be stores and will cost you a bit more on the disk space. Tables that only have a few rows will not benefit from indexes.
5) Bottlenecks
Often during working on the servers you are bound to face a few bottlenecks. They will have to be detected and monitored. There are many solutions available for them
6) Using NFS with MySQL
You need to be cautious while using NFS with MySQL. Some of the possible problems are that log files and MySQL data that are sited on NFS volumes get locked and are not available for use. This could also give rise to data inconsistencies because of the poor network which hampers the messages received. You can use TCP with hard intr mount support to get rid of this problem.
7) Use Symbolic Links
You can shift a database from the database directory to another place and/or replace it with symbolic links to a new place. You can do this to shift a database to file system which has more free space available or it could also be done to increase the speed of your system by sending the data to varying disks. Symbolic links for databases can be used in Unix or windows. You can also use symbolic links for MyISAM tables on Unix.
10) Optimization Of Source Code
When using PHP to render websites, caching is the real performance dealer. That is why you will have to make your code run faster. Use as few ‘includes’ and ’requires’ as possible. Use full paths to interact with files. Avoid regular expressions whenever possible. A properly setup database should be at least an order of magnitude faster than you imitating relational operations on the php side. Avoid contacting other sites while rendering pages. If you have to do it, do it in the background and store the results locally. Try to pre-generate as much content as possible. Do not store BLOB fields in DB, instead store them on a disk. Evaluate your code architecture and simplify the class structures.
