tmpdir /tmp/
version 3.22.23b
wait_timeout 28800To change any of these values, use the following syntax:
safe_mysqld –O variable = value
Where variable is one of the system variables and value is a logical value for that variable.
Changes are not persistent. So if you end your session and start MySQL without any options, MySQL
automatically uses the defaults. None of your previous changes will be present. To use your options
every time, you must change your startup script to reflect your variable changes.Most of the variables are self explanatory. The following are the most important variables for optimizing
your system:
back_log Set this to a higher value if you expect a lot of connections to occur at the
same time. This option controls how many connections MySQL will hold while it is
making new threads.
delayed_queue_size This option controls the number of rows that will be queued
when using an INSERT DELAYED command. Increase this if you expect a lot of
inserts that will need to be queued.
flush_time This option controls the amount of time, in seconds, before MySQL
writes what is in cache to disk. The more I/O operations to disk, the slower a
database becomes. Set this number higher to delay writing the cache to disk.
table_cache This option controls the number of open tables for all threads.
Increasing this size will increase the number of tables that can be open at the same
time. This decreases the amount of overhead and could speed things up. However,
the operating system may have limits that might affect how many files can be open
at the same time.
wait_timeout Controls the amount of time before a connection is closed due to
inactivity. A lower number may increase speed.
buffers Increasing any of the buffers will help speed up your database. Setting
these buffers too high can be detrimental. These settings should be based on the
amount of available memory.
Tip If you have a lot of tables and a lot of memory, you can speed MySQL up by setting
the buffer size equal to the amount of memory you have; for example, buffer =
32M.mysqld and Its Parameters
mysqld is the heart of MySQL; It is the actual server program. In effect, when you think of MySQL, this is
what it is. The database server can be started with some options that may help with optimization. The
following is a list of some of the more important parameters that mysqld can take:
-b or -basedir=full path This parameter points the MySQL server to the mysql
installation directory. This tells the server where everything is located.
--big-tables When MySQL is started with this option, it will allow large result sets.
This will eliminate table-full errors.
-h or -datadir=full path This option points the MySQL server to the data
directory. Set this option if you have moved your data do another place.
--enable-locking This argument allows system locking. MySQL no longer
defaults to system locking.
-l -log=[filename]] Causes MySQL to log system messages to this file. If no
file name is given, messages are written to mysql-log-file.
--log-update=filename Causes MySQL to log all transactions that affect the
database. This is a great tool for creating backups of your database. This option has
been covered in detail in Day 19, "Administrating MySQL."
--log-long-format=filename This feature causes MySQL to log more
information than the -update option. Again, refer to Day 19 for more details.
--low-priority-insert This option causes MySQL to execute SELECT
statements before INSERT statements.
--skip-grant-tables A handy feature for when you forget the root password.
This option skips the grant tables when starting up, so all passwords and user-
defined functions will not be enforced.