The Elliquiy LAMP Stack: MySQL compilation and configuration

[Vekseid]

The Elliquiy LAMP Stack

1: Introduction and Overview
2: General Configuration
3: General Security
4: IPTables configuration
5: Postfix configuration
6: ntp configuration
7: Apache compilation and configuration
8: MySQL compilation and configuration
9: PHP compilation and configuration
10: Conclusion and future plans

MySQL

On VPSes, MySQL usually comes preinstalled, so set the root password via mysqladmin and delete extraneous entries from the mysql.user table. In the case of servers the password is prompted for on install but I still end up deleting the hostname entry (ie elliquiy.com). It's better to use ips than hostnames anyway, if you are using a separate database server.

After changing the size of the logfiles in my.cnf, stop mysql, delete them, and start mysql again to rebuild them at their new size. Set a crontab to import FLUSH QUERY CACHE; every half hour (or more) in order to keep the query cache defragmented.

The notes in general configuration and security are important for the my.cnf setup I use here.

MySQL Compilation

Long test suite is long. If you are playing with things, it may behoove your sanity to just disable them.

For custom-compiling MySQL, I don't add much. For compiler flags, I just add -march=core2 (or whatever is appropriate for the core I am currently running on). -O3, and many other optimizing flags do not provide universal benefits enough to be worth it.

The configure sequence in my debian/rules thus looks like:

Code Select Expand


sh -c  'PATH=$${MYSQL_BUILD_PATH:-"/bin:/usr/bin"} \
                CC=$${MYSQL_BUILD_CC:-gcc} \
                CFLAGS=$${MYSQL_BUILD_CFLAGS:-"-DBIG_JOINS=1 -O2 -march=core2 -fPIC"} \
                CXX=$${MYSQL_BUILD_CXX:-g++} \
                CXXFLAGS=$${MYSQL_BUILD_CXXFLAGS:-"-DBIG_JOINS=1 -felide-constructors -fno-rtti -O2 -march=core2"} \
            ./configure \
                --build=${DEB_BUILD_GNU_TYPE} \
                --host=${DEB_HOST_GNU_TYPE} \
                --prefix=/usr \
                --exec-prefix=/usr \
                --libexecdir=/usr/sbin \
                --datadir=/usr/share \
                --localstatedir=/var/lib/mysql \
                --includedir=/usr/include \
                --infodir=/usr/share/info \
                --mandir=/usr/share/man \
                --with-server-suffix="-$(DEBVERSION)" \
                --with-comment="($(DISTRIBUTION))" \
                --with-system-type="debian-linux-gnu" \
                --enable-shared \
                --enable-static \
                --without-debug \
                --enable-thread-safe-client \
                --enable-local-infile \
                --with-big-tables \
                --with-unix-socket-path=/var/run/mysqld/mysqld.sock \
                --with-mysqld-user=mysql \
                --with-libwrap \
                --with-mysqld-ldflags=-all-static \
                --without-openssl \
                --without-yassl \
                --without-docs \
                --with-bench \
                --without-readline \
                --with-extra-charsets=all \
                --with-innodb \
                --without-archive-storage-engine \
                --without-csv-storage-engine \
                --without-federated-storage-engine \
                --with-blackhole-storage-engine \
                --with-sphinx-storage-engine \
                --without-embedded-server \
                --with-ndbcluster \
                --with-ndb-ccflags="-fPIC" \
                --with-ndb-shm \
                --without-ndb-sci \
                --without-ndb-test \
                --with-embedded-server \
                --with-embedded-privilege-control \
                --without-ndb-docs'


If you are using an i386 (-not- amd64) architecture or sparc, don't forget to add --enable-assembler to the configure list. You may wish to also disable libwrap, as skip-networking in my.cnf and the iptables script I provide both block access. You may want to be a lot stingier with extra character sets. Choose your storage engines according to your needs.

MySQL Configuration

Here is my my.cnf. I document the effects of the individual settings in the comments.

Code Select Expand


[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket           = /var/run/mysqld/mysqld.sock
nice             = 0
# Make sure this is -at least- twice the size of your table cache if not a bit bigger.
# Don't go over your OS limit, of course, but for my 250 gig HD the OS limit is some 300k by default.
open-files-limit = 16384

[mysqld]
# Basic configuration.
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking
# Don't bother with networking unless we have to! Otherwise, bind to the ip address of the server you are willing to listen to.
skip-networking

# large-pages allows MySQL to allocate HugePages from the kernel. MySQL reserves:
# One Huge Page for itself
# Enough hugepages to cover innodb_buffer_pool_size
# Enough hugepages to cover most of key_buffer
# I'm not really sure about the algorithm used to pick out the reservation for key_buffer,
# it may be architecture dependent.
# It seems to default to 7 pages per 16 megs allocated to the buffer (assuming 2 meg pages), plus one.
# Yes I'm just too lazy to dig it out of the source. Nonetheless, large-pages has a pretty dramatic
# impact. While I have no benchmark measurements, enabling large pages has cut the maximum concurrent
# connections in half, and the rate of slow queries (already nice and low) down to a quarter.
large-pages
# Remember that MyISAM temporary tables also use the keybuffer, before setting this insanesmall just
# because you are using all InnoDB.
key_buffer                      = 272M
bulk_insert_buffer_size         = 8M
binlog_cache_size               = 256K
# I prefer to structure my global buffers such that all of my data fits comfortably into the buffers whereever possible.
innodb_buffer_pool_size         = 284M
innodb_log_file_size            = 256M
# 1M is the default and plenty for my current needs : )
innodb_log_buffer_size          = 1M
# This is 1M by default and normally there is little reason to increase this except to check memory usage.
innodb_additional_mem_pool_size = 1M
# This causes innodb to commit once per second, rather than once per transaction. Most InnoDB transactions on Elliquiy's server are actually user tracking functions, rather than important data storage.
innodb_flush_log_at_trx_commit   = 0
# MySQL is perfectly willing to allocate a bit of extra space for each of your tables when you do this, and it's not like we miss out. Want to see what table is taking up too much of your buffer pool, just look at the file sizes.
innodb_file_per_table
# 8 is the default value. I may worry about it if things get slow again.
innodb_thread_concurrency       = 8
# I'm going to test this out a bit to make sure it works with SMF-Phpbb-Drupal-Mediawiki, but this should give a slight performance boost.
transaction-isolation           = READ-COMMITTED
# Default value is 50 seconds. I will set this to 40 seconds after I've set things up appropriately.
innodb_lock_wait_timeout        = 120
# This would of course have higher integrity. Currently disabled by default.
#innodb_rollback_on_timeout
# O_DIRECT removes the double buffer, making flushes faster. Disabling for now.
#innodb_flush_method             = O_DIRECT
# The client defaults to 16M, but the server only to 1M. Set to 16M to let us do more when we want.
max_allowed_packet              = 16M
# I'm tempted to set thread_cache_size equal to max_connections. Since most transactions are extremely
# fast, it's rather rare to see more than a few connections open at once - high teens, low 20s, though
# before optimizing it could easily break 50.
thread_cache_size               = 64
# Think about raising these over 256K. Memory allocation switches from malloc () to mmap () at this point,
# which is significantly slower. You do not need to sacrifice every single query just to make sure each
# one fits in your buffers.
thread_stack                    = 256K
sort_buffer_size                = 256K
read_buffer_size                = 256K
join_buffer_size                = 256K
# I'm sortof tempted to hack this one down too >_>
read_rnd_buffer_size            = 4M
# I make sure to set these equal, especially when playing with shared memory.
tmp_table_size                  = 32M
max_heap_table_size             = 32M
myisam_sort_buffer_size         = 64M
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover                  = BACKUP
# Elliquiy was nearly brought down at 128 connections, so we want to stop a bit before then.
max_connections                 = 128
# Likewise, table cache peaks around 2-3k. Give a bit more room.
table_cache                     = 4096
# thread_concurrency does nothing on non-Solaris machines.
# thread_concurrency              = 2
# We are not concerned about wasting a megabyte or so on a machine with 190 gigabytes available.
# There is no reason ever to have concurrent_insert be anything other than 2 these days.
concurrent_insert               = 2
# Many of SMF's updates actually specify low priority, so the benefit here would not be that great.
#low_priority_updates            = 1
# The default value for optimizer_search_depth is 62! WTF. That is like taking for minutes to plan
# the best possible move in a five-minute chess match.
optimizer_search_depth          = 4

# A well tuned query cache will serve a single-server setup well. The implementation is... not the
# best, but keep in mind, we are going through a socket and skipping all of the overhead associated
# with networking for say, memcached.
# Still, watch your hits versus inserts rate. As that number goes down, it may be time to put a fork
# in it. That said, currently, roughly half of Elliquiy's queries are being served through the cache
# at this point, so it's performing rather well.
# Note that it really would not be doing that well if I weren't routinely flushing it. See above.
query_cache_size         = 64M
# limit is set to 256K to prevent Sphinx's reindexing from needlessly purging genuine queries.
query_cache_limit        = 256K
# The -average- size of a query stored in my query cache appears to be about 1800 bytes. So leaving it
# at the default 4K allocates twice as much space as it needs most of the time.
query_cache_min_res_unit = 2K

# With this configuration, one second is plenty long. Most slow queries on this server actually either
# occur during backups or involve phpBB's searching. The rest are simply insanely inefficient queries
# on the part of phpBB/SMF.
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 1
# Free forum software tends to do this a lot, it's not very helpful to fill this up with queries you
# already know are broken, so I comment it out.
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 7
max_binlog_size         = 256M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

# Big buffers are useful for checking tables...
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 16M
write_buffer = 16M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 16M
write_buffer = 16M

# I'm paranoid, I don't like including anything that I don't know what may be in it.
#!includedir /etc/mysql/conf.d/


This has proven to be an extremely efficient configuration for my needs. The current database size is about 2.1 gigabytes, and we use roughly half of the allocated buffers. We can safely use about four times this, so I'm guessing we'll be looking into another server when the database starts pushing > 16-20 GB.