Databases Reference
In-Depth Information
locations; we're just advising you to make sure the
my.cnf
file mentions those locations
explicitly, so they won't change and break things if you upgrade the server.)
We also specified that
mysqld
should run as the
mysql
user account on the operating
system. You'll need to make sure this account exists, and that it owns the data directory.
The port is set to the default of 3306, but sometimes that is something you'll want to
change.
We've chosen the default storage engine to be InnoDB, and this is worth explaining.
We think InnoDB is the best choice in most situations, but that's not always the case.
Some third-party software, for example, might assume the default is MyISAM, and will
create tables without specifying the engine. This might cause the software to malfunc-
tion if, for example, it assumes that it can create full-text indexes. And the default
storage engine is used for explicitly created temporary tables, too, which can cause
quite a bit of unexpected work for the server. If you want your permanent tables to use
InnoDB but any temporary tables to use MyISAM, you should be sure to specify the
engine explicitly in the
CREATE TABLE
statement.
In general, if you decide to use a storage engine as your default, it's best to configure it
as the default. Many users think they use only a specific storage engine, but then dis-
cover another engine has crept into use because of the configured default.
We'll illustrate the basics of configuration with InnoDB. All InnoDB really needs to run
well in most cases is a proper buffer pool size and log file size. The defaults are far too
small. All of the other settings for InnoDB are optional, although we've enabled
innodb_file_per_table
for manageability and flexibility reasons. Setting the InnoDB
log file size is a topic that we'll discuss later in this chapter, as is the setting of
innodb
_flush_method
, which is Unix-specific.
There's a popular rule of thumb that says you should set the buffer pool size to around
75% or 80% of your server's memory. This is another accidental ratio that seems to
work okay sometimes, but isn't always correct. It's a better idea to set the buffer pool
roughly as follows:
1. Begin with the amount of memory in the server.
2. Subtract out a bit for the operating system and perhaps for other programs, if
MySQL isn't the only thing running on the server.
3. Subtract some more for MySQL's memory needs; it uses various buffers for per-
query operations, for example.
4. Subtract enough for the InnoDB log files, so the operating system has enough
memory to cache them, or at least the recently accessed portion thereof. (This
advice applies to standard MySQL; in Percona Server, you can configure the log
files to be opened with
O_DIRECT
, bypassing the operating system caches.) It might
also be a good idea to leave some memory free for caching at least the tail of the
binary logs, especially if you have replicas that are delayed, because they can some-
times read old binary log files on the master, causing some pressure on its memory.
