More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
MySQL - System/Compile Time and Startup Parameter Tuning
We start with the system level things since some of these decisions have to be made very early. In other cases a fast look
at this part may suffice because it not that important for the big gains. However, it is always nice to have a feeling about
how much one could gain by changing things at this level.
The default OS to use is really important! To get the most use of multiple CPU machines one should use Solaris (because the
threads works really nice) or Linux (because the 2.2 kernel has really good SMP support). Also on 32-bit machines Linux has
a 2G file size limit by default. Hopefully this will be fixed soon when new filesystems are released (XFS/Reiserfs). If you
have a desperate need for files bigger than 2G on Linux-intel 32 bit, you should get the LFS patch for the ext2 file system.
Because we have not run MySQL in production on that many platforms, we advice you to test your intended platform before
choosing it, if possible.
Other tips:
If you have enough RAM, you could remove all swap devices. Some operating systems will use a swap device in some contexts
even if you have free memory.
Use the --skip-locking MySQL option to avoid external locking. Note that this will not impact MySQL's functionality as long
as you only run one server. Just remember to take down the server (or lock relevant parts) before you run myisamchk. On some
system this switch is mandatory because the external locking does not work in any case. The --skip-locking option is on by
default when compiling with MIT-pthreads, because flock() isn't fully supported by MIT-pthreads on all platforms. It's also
on default for Linux as Linux file locking are not yet safe. The only case when you can't use --skip-locking is if you run
multiple MySQL servers (not clients) on the same data, or run myisamchk on the table without first flushing and locking the
mysqld server tables first. You can still use LOCK TABLES/UNLOCK TABLES even if you are using --skip-locking
12.2.1 How Compiling and Linking Affects the Speed of MySQL
Most of the following tests are done on Linux with the MySQL benchmarks, but they should give some indication for other
operating systems and workloads.
You get the fastest executable when you link with -static.
On Linux, you will get the fastest code when compiling with pgcc and -O6. To compile `sql_yacc.cc' with these options, you
need about 200M memory 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 (it is not needed). Note that with some versions of pgcc, the
resulting code will only run on true Pentium processors, even if you use the compiler option that you want the resulting
code to be working on all x586 type processors (like AMD).
By just using a better compiler and/or better compiler options you can get a 10-30 % speed increase in your application.
This is particularly important if you compile the SQL 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 on.
When you compile MySQL you should only include support for the character sets that you are going to use. (Option
--with-charset=xxx). The standard MySQL binary distributions are compiled with support for all character sets.
Here is a list of some mesurements that we have done:
If you 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 dynamic linked
MySQL library. It is only the server that is critical for performance.
If you connect using TCP/IP rather than Unix sockets, the result is 7.5% slower on the same computer. (If you are connection
to localhost, MySQL will, by default, use sockets).
If you compile with --with-debug=full, then you will loose 20 % for most queries, but some queries may take substantially
longer (The MySQL benchmarks ran 35 % slower) If you use --with-debug, then you will only loose 15 %.
On a Sun SPARCstation 20, SunPro C++ 4.2 is 5 % faster than gcc 2.95.2.
Compiling with gcc 2.95.2 for ultrasparc with the option -mcpu=v8 -Wa,-xarch=v8plusa 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/CPUs the
difference should get bigger.
Running with --log-bin makes [MySQL 1 % slower.
Compiling without frame pointers -fomit-frame-pointer with gcc makes MySQL 1 % faster.
The MySQL-Linux distribution provided by MySQL AB used to be compiled with pgcc, but we had to go back to regular gcc
because of a bug in pgcc that would generate the 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 get it faster and more portable.
(Continued on next question...)
