Tuning Mysql

[dwh2]

I was reading up on how to tune mysql and noticed that there can be severe improvements on compiling it.

See http://www.databasejournal.com/features/mysql/article.php/10897_1402311_1

    *  Firstly, compile statically (--static). This requires more disk space, but runs faster (13% on Linux, according to MySQL themselves)
    * Optimize to the highest level possible (-O3 with gcc)
    * Compile without debug (--without-debug). This runs 20-35% faster.
    * Compile without frame pointers. (-fomit-frame-pointer). This is from 1-4% faster.
    * Compile with only the minimum character sets that you need (e.g. --with-extra- charsets=none)

I'm not a unix pro by any means so excuse me for ignorance I display.

By compiling is he referring to when you download and install Mysql? Or the startup options? Because I don't see stuff like --static when restarting mysql.

I'm sure I'm not alone to say that if I can improve 34% to 52% mysql performace by turning on static, turning off debug and frame pointers, I'll gladly do so. Of course I don't know what debug static and frame pointers do, but I think I can do without them.

Is there a way to add these options to mysql w/ cpanel? If so, will I lose changes when cpanel upgrade to a later mysql version?

Also I've got some freebsd servers and some redhat servers so I don't know if that matters.

While I'm obviously writing this to help my own configuration, some general principles would be good to share with everyone since a lot of people can see major improvements potentially. Thanks.

 

[Blue|Fusion]

Compiling from source rquires you to download the source code for MySQL. Normally, MySQL is installed using pre-compiled RPMs, which can easily be installed, updated,and removed. Compiling from source has its advantages, but also takes a little more time and may be more stressful if you're not careful.

Download the tar.gz source packages from dev.mysql.com. Extract it, then go into the extracted directory. In there, run the command ./configure [options]. Replace [options] with whatever you want, like the example below:
./configure --without-debug --static -O3 -fomit-frame-pointer --with-extra- charsets=none (options from your post).

Once that's done and no errors are present, go ahead and do make && make install

 

[dwh2]

Thank you. I'm a bit too scared to do that on a production environment and I don't have a test one to play around with, so I guess I'll leave it be for now. But out of curiosity, would doing that allow WHM to restart mysql the same way? Would it matter what directory it was installed in?

 

[Blue|Fusion]

It would definately have to have the same directory structure, but the rest, I don't think would cause a problem, as long as mysql 4.1 is selected in tweak settings.