{"id":3474,"date":"2014-02-02T14:31:09","date_gmt":"2014-02-02T20:31:09","guid":{"rendered":"http:\/\/icesquare.com\/wordpress\/?p=3474"},"modified":"2014-07-06T14:11:56","modified_gmt":"2014-07-06T19:11:56","slug":"the-easiest-way-to-improve-the-performance-of-mysql-server-on-freebsd","status":"publish","type":"post","link":"https:\/\/icesquare.com\/wordpress\/the-easiest-way-to-improve-the-performance-of-mysql-server-on-freebsd\/","title":{"rendered":"The easiest way to improve the performance of MySQL server on FreeBSD"},"content":{"rendered":"

There are many different ways to improve the MySQL performance. In general, it breaks down into two different categories: Server side and client side.<\/p>\n

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.<\/p>\n

\nI 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.\n<\/p>\n

My solution is very simple: Compiling the MySQL server with static<\/strong> option enabled<\/p>\n

\nBy 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:<\/p>\n

\r\ncd \/usr\/ports\/databases\/mysql56-server\r\nmake BUILD_OPTIMIZED=yes BUILD_STATIC=yes\r\nmake install clean\r\n<\/pre>\n
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.<\/p>\n
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<\/strong><\/p>\n
\r\nsudo nano \/etc\/make.conf\r\n\r\nWITH_CHARSET=utf8\r\nWITH_COLLATION=utf8_general_ci\r\nBUILD_OPTIMIZED=yes\r\nBUILD_STATIC=yes\r\n<\/pre>\n
We can update the port tree and ports again. This will make the system to use the new settings.<\/p>\n
\r\nsudo portsnap fetch update\r\nsudo portmaster -Day\r\n<\/pre>\n
\r\n#Don't forget to restart the MySQL server.\r\nsudo \/usr\/local\/etc\/rc.d\/mysql-server start\r\n<\/pre>\n
Now your MySQL server and other applications are built using static.<\/p>\n
Please click here to learn more about building static versus building dynamics<\/a>.<\/p>\n
–Derrick<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
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 Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":3476,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[94,146,7,144,6],"tags":[171,9],"class_list":["post-3474","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-computer-problems-computer-help-pc-support","category-everyday-computer-problem","category-freebsd","category-sever-problem","category-technology","tag-freebsd","tag-mysql"],"jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/p1sqzr-U2","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":3023,"url":"https:\/\/icesquare.com\/wordpress\/freebsdmysql-server-could-not-start\/","url_meta":{"origin":3474,"position":0},"title":"[FreeBSD]MySQL server could not start","author":"Derrick","date":"November 22, 2012","format":false,"excerpt":"Today, I noticed that the MySQL server on my FreeBSD server is down for unknown reason, and today is Thanksgiving. Initially, I tried to restart the MySQL server using the following command, but it didn't help to restart the MySQL: sudo \/usr\/local\/etc\/rc.d\/mysql-server restart and it gives me the following error\u2026","rel":"","context":"In "Computer Problems"","block_context":{"text":"Computer Problems","link":"https:\/\/icesquare.com\/wordpress\/category\/technology\/computer-problems-computer-help-pc-support\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":613,"url":"https:\/\/icesquare.com\/wordpress\/starting-mysql-error-the-server-quit-without-updating-pid-file\/","url_meta":{"origin":3474,"position":1},"title":"Starting MySQL.. ERROR! The server quit without updating PID file","author":"Derrick","date":"April 13, 2010","format":false,"excerpt":"This article is a bit outdated. Please visit the following article instead. It includes a better discussion on how to solve this problem using different scenarios: MySQL: Starting MySQL\u2026.. ERROR! The server quit without updating PID file Thanks. --Derrick [ad#ad-bottom]","rel":"","context":"In "Computer Problems"","block_context":{"text":"Computer Problems","link":"https:\/\/icesquare.com\/wordpress\/category\/technology\/computer-problems-computer-help-pc-support\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":87,"url":"https:\/\/icesquare.com\/wordpress\/performance-benchmark-freebsd-vs-ubuntu-vs-os-x-which-one-is-better\/","url_meta":{"origin":3474,"position":2},"title":"Performance Benchmark: FreeBSD vs Ubuntu vs OS X, which one is better?","author":"Derrick","date":"May 22, 2009","format":false,"excerpt":"If you cannot decide whether you should go with FreeBSD or Ubuntu (or Linux), check out this article: FreeBSD or Linux in 6 Simple Questions Recently I am working on building a web farm which is formed by three ancient time computers (The sum of their ages is older than\u2026","rel":"","context":"In "FreeBSD"","block_context":{"text":"FreeBSD","link":"https:\/\/icesquare.com\/wordpress\/category\/sever-problem\/freebsd\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1028,"url":"https:\/\/icesquare.com\/wordpress\/mysql-starting-mysql-error-the-server-quit-without-updating-pid-file\/","url_meta":{"origin":3474,"position":3},"title":"MySQL: Starting MySQL….. ERROR! The server quit without updating PID file","author":"Derrick","date":"November 12, 2010","format":false,"excerpt":"This step-by-step guide is mainly for FreeBSD, however the idea is the same for Linux. Every once a while, when I update my FreeBSD box, the system likes to shutdown my MySQL server. Therefore, I need to start it again after the update is done. Unfortunately, the upgrade process is\u2026","rel":"","context":"In "Computer Problems"","block_context":{"text":"Computer Problems","link":"https:\/\/icesquare.com\/wordpress\/category\/technology\/computer-problems-computer-help-pc-support\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":31,"url":"https:\/\/icesquare.com\/wordpress\/installing-apache-php-mysql-and-memcached-on-freebsd\/","url_meta":{"origin":3474,"position":4},"title":"Installing Apache, PHP, MySQL and MemCached on FreeBSD","author":"Derrick","date":"May 19, 2009","format":false,"excerpt":"This is my note on how to to install Apache, PHP, MySQL and MemCached on a FreeBSD 7 Box. 1. MySQL Go to the mysql52-server port directory by typing the command: cd \/usr\/ports\/databases\/mysql51-server Build the port by typing: make BUILD_OPTIMIZED=yes BUILD_STATIC=yes Using these two options will speed up the execution\u2026","rel":"","context":"In "FreeBSD"","block_context":{"text":"FreeBSD","link":"https:\/\/icesquare.com\/wordpress\/category\/sever-problem\/freebsd\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3514,"url":"https:\/\/icesquare.com\/wordpress\/freebsdphp-does-not-work-after-upgrading-apache-to-2-2-27\/","url_meta":{"origin":3474,"position":5},"title":"[FreeBSD]PHP does not work after upgrading Apache to 2.2.27","author":"Derrick","date":"April 11, 2014","format":false,"excerpt":"I upgraded the Apache to 2.2.27 on my FreeBSD box via portmaster. The upgrade went very smooth. After the upgrade, I found that Apache no longer rendered the PHP page correctly. In the other words, it displayed the source code of the PHP files instead of executing the code. Before\u2026","rel":"","context":"In "Computer Problems"","block_context":{"text":"Computer Problems","link":"https:\/\/icesquare.com\/wordpress\/category\/technology\/computer-problems-computer-help-pc-support\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_featured_media_url":"https:\/\/icesquare.com\/wordpress\/wp-content\/uploads\/2014\/02\/speedometer.jpg","_links":{"self":[{"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/posts\/3474","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/comments?post=3474"}],"version-history":[{"count":10,"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/posts\/3474\/revisions"}],"predecessor-version":[{"id":3632,"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/posts\/3474\/revisions\/3632"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/media\/3476"}],"wp:attachment":[{"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/media?parent=3474"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/categories?post=3474"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/icesquare.com\/wordpress\/wp-json\/wp\/v2\/tags?post=3474"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}