MySQL编译优化
1. -static 13%
–with-client-ldflags=-all-static
–with-mysqld-ldflags=-all-static
静态链接提高13%性能
2. -pgcc 1%
CFLAGS="-O3 -mpentiumpro -mstack-align-double" CXX=gcc
CXXFLAGS="-O3 -mpentiumpro -mstack-align-double
-felide-constructors -fno-exceptions -fno-rtti"
如果是Inter处理器，使用pgcc提高1%性能
(使用Intel ICC compiler则相比标准GCC-compiler编译将使MYSQL数据库提升20%性能)
3. Unix Socket 7.5%
–with-unix-socket-path=/path/mysql.sock
使用unix套接字链接提高7.5%性能，所以在windows下mysql性能肯定不如unix下面
4. –enable-assembler 允许使用汇编模式(优化性能)
详见下文:
How Compiling and Linking Affects the Speed of MySQL
Most of the following tests were performed on Linux with the MySQL benchmarks, but they should give some indication for other operating systems and workloads.
You get the fastest executables when you link with -static.
On Linux, you will get the fastest code when compiling with pgcc and -O3. You need about 200MB memory to compile sql_yacc.cc with these options, because gcc/pgcc needs a lot of memory to make all functions inline. You should also set CXX=gcc when configuring MySQL to avoid inclusion of the libstdc++ library, which is not needed. Note that with some versions of pgcc, the resulting code will run only on true Pentium processors, even if you use the compiler option indicating that you want the resulting code to work on all x586-type processors (such as AMD).
By just using a better compiler and better compiler options, you can get a 10-30% speed increase in your application. This is particularly important if you compile the MySQL server yourself.
We have tested both the Cygnus CodeFusion and Fujitsu compilers, but when we tested them, neither was sufficiently bug-free to allow MySQL to be compiled with optimizations enabled.
The standard MySQL binary distributions are compiled with support for all character sets. When you compile MySQL yourself, you should include support only for the character sets that you are going to use. This is controlled by the --with-charset option to configure.
Here is a list of some measurements that we have made:If y
- ou use pgcc and compile everything with -O6, the mysqld server is 1% faster than with gcc 2.95.2.
- If you link dynamically (without -static), the result is 13% slower on Linux. Note that you still can use a dynamically linked MySQL library for your client applications. It is the server that is most critical for performance.
- If you strip your mysqld binary with strip mysqld, the resulting binary can be up to 4% faster.
- For a connection from a client to a server running on the same host, if you connect using TCP/IP rather than a Unix socket file, performance is 7.5% slower. (On Unix, if you connect to the hostname localhost, MySQL uses a socket file by default.)
- For TCP/IP connections from a client to a server, connecting to a remote server on another host will be 8-11% slower than connecting to the local server on the same host, even for connections over 100Mb/s Ethernet.
- When running our benchmark tests using secure connections (all data encrypted with internal SSL support) performance was 55% slower than for unencrypted connections.
- If you compile with --with-debug=full, most queries will be 20% slower. Some queries may take substantially longer; for example, the MySQL benchmarks ran 35% slower. If you use --with-debug (without =full), the slowdown will be only 15%. For a version of mysqld that has been compiled with --with-debug=full, you can disable memory checking at runtime by starting it with the --skip-safemalloc option. The end result in this case should be close to that obtained when configuring with --with-debug.
- On a Sun UltraSPARC-IIe, a server compiled with Forte 5.0 is 4% faster than one compiled with gcc 3.2.
- On a Sun UltraSPARC-IIe, a server compiled with Forte 5.0 is 4% faster in 32-bit mode than in 64-bit mode.
- Compiling with gcc 2.95.2 for UltraSPARC with the -mcpu=v8 -Wa,-xarch=v8plusa options gives 4% more performance.
- On Solaris 2.5.1, MIT-pthreads is 8-12% slower than Solaris native threads on a single processor. With more load or CPUs, the difference should be larger.
- Compiling on Linux-x86 using gcc without frame pointers (-fomit-frame-pointer or -fomit-frame-pointer -ffixed-ebp) makes mysqld 1-4% faster.
Binary MySQL distributions for Linux that are provided by MySQL AB used to be compiled with pgcc. We had to go back to regular gcc due to a bug in pgcc that would generate code that does not run on AMD. We will continue using gcc until that bug is resolved. In the meantime, if you have a non-AMD machine, you can get a faster binary by compiling with pgcc. The standard MySQL Linux binary is linked statically to make it faster and more portable.
[...] mysql的静态编译可以提高性能，具体见http://www.aease.org/2009/05/mysql-1.html ＜pre lang=”php” line=“1” collas＝“＋”＞ ./configure –prefix=/app/mysql –libdir=/usr/lib –with-mysqld-user=mysql –without-debug –with-big-tables –with-zlib-dir=bundled –with-extra-charsets=all –with-pthread –enable-static –enable-thread-safe-client –with-mysqld-ldflags=-all-static –with-client-ldflags=-all-static –enable-assembler –with-ssl CFLAGS=”-O2″ CXX=gcc CXXFLAGS=”-O2 -felide-constructors -fno-exceptions -fno-rtti” ＜/pre> [...]
Wow this is a great resource.. I’m enjoying it.. good article