There are many different ways to improve the MySQL performance. In general, it breaks down into two different categories: Server side and client side.
On the server side, we can optimize the database and table structure, such as indexing the columns etc. On the client side, we can optimize the queue to minimize the workload, or we can cache and share the result such that the traffic to the server will be minimized. However, these methods are doable if you have access to the source code, or you understand the logic of the software. If you are a system administrator, you may not want to touch the source code, because you never know what will happen after the modifications. Plus your modification may be overwritten in the next update.
I am going to show you a quick and easy way to solve this problem. First, I am assuming that you build the MySQL from source. In the other words, this article will not work if you install the MySQL through pkg_add, yum, apt-get etc.
My solution is very simple: Compiling the MySQL server with static option enabled
By default, we will compile the software from source, it is not built in static. According to MySQL documentation, building the binary using static will result a 13% improvement comparing to building the binary using dynamic. Here is an example how to build MySQL with static option enabled:
cd /usr/ports/databases/mysql56-server make BUILD_OPTIMIZED=yes BUILD_STATIC=yes make install clean
This method will work for the first time. It may be a problem in the long run. For example, I use portsnap to update the port tree, and I use portmaster to upgrade the application. By default, portmaster will use the default options to rebuild the port. In the other words, the MySQL will not be built using static.
To solve this problem, I will need to make the build static option as a default settings. First of all, try to include the following in /etc/make.conf
sudo nano /etc/make.conf WITH_CHARSET=utf8 WITH_COLLATION=utf8_general_ci BUILD_OPTIMIZED=yes BUILD_STATIC=yes
We can update the port tree and ports again. This will make the system to use the new settings.
sudo portsnap fetch update sudo portmaster -Day
#Don't forget to restart the MySQL server. sudo /usr/local/etc/rc.d/mysql-server start
Now your MySQL server and other applications are built using static.
Please click here to learn more about building static versus building dynamics.
–Derrick
Our sponsors:
