MySQL - System/Compile Time and Startup Parameter
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
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.
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
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
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
Here is a list of some measurements 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
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
MySQL - Disk Issues
As mentioned before, disks seeks are a big performance
bottleneck. This problems gets more and more apparent
when the data starts to grow so large that effective
caching becomes impossible. For large databases, where
you access data more or less randomly, you can be sure
that you will need at least one disk seek to read and a
couple of disk seeks to write things. To minimize this
problem, use disks with low seek times.
Increase the number of available disk spindles (and
thereby reduce the seek overhead) by either symlink
files to different disks or striping the disks.
Using symbolic links
This means that you symlink the index and/or data file(s)
from the normal data directory to another disk (that may
also be striped). This makes both the seek and read
times better (if the disks are not used for other
Striping means that you have many disks and put the
first block on the first disk, the second block on the
second disk, and the Nth on the (N mod number_of_disks)
disk, and so on. This means if your normal data size is
less than the stripe size (or perfectly aligned) you
will get much better performance. Note that striping is
very dependent on the OS and stripe-size. So benchmark
your application with different stripe-sizes.
Note that the speed
difference for striping is very dependent on the
parameters. Depending on how you set the striping
parameters and number of disks you may get a difference
in orders of magnitude. Note that you have to choose to
optimize for random or sequential access.
For reliability you may want to use RAID 0+1 (striping +
mirroring), but in this case you will need 2*N drives to
hold N drives of data. This is probably the best option
if you have the money for it! You may, however, also
have to invest in some volume-management software to
handle it efficiently.
A good option is to have semi-important data (that can
be regenerated) on RAID 0 disk while storing really
important data (like host information and logs) on a
RAID 0+1 or RAID N disk. RAID N can be a problem if you
have many writes because of the time to update the
You may also set the parameters for the file system that
the database uses. One easy change is to mount the file
system with the noatime option. That makes it skip the
updating of the last access time in the inode and by
this will avoid some disk seeks.
On Linux, you can get much more performance (up to 100 %
under load is not uncommon) by using hdpram to configure
your disk's interface! The following should be quite
good hdparm options for MySQL (and probably many other
applications): hdparm -m 16 -d 1
Note that the performance/reliability when using the
above depends on your hardware, so we strongly suggest
that you test your system throughly after using hdparm!
Please consult the hdparm man page for more information!
If hdparm is not used wisely, filesystem corruption may
result. Backup everything before experimenting!
On many operating systems you can mount the disks with
the 'async' flag to set the file system to be updated
asynchronously. If your computer is reasonable stable,
this should give you more performance without
sacrificing too much reliability. (This flag is on by
default on Linux.)
If you don't need to know when a file was last accessed
(which is not really useful on a database server), you
can mount your file systems with the noatime flag.
Page Numbers : 1