Configuring and Tuning the Server - Learning MySQL - page 385

sort_buffer_size variable controls the amount of memory available for queries that
have an ORDER BY clause. The read_buffer_size and sort_buffer_size variables operate
on a per-thread basis.
As with options, variables can be specified on the command line or in an options file.
For example, the variable max_connections can be specified from the command line as:
$ mysqld --max_connections=200
or in an options file as:
[server]
max_connections=200
Some variables can also be set from within a client using the SET command; for example,
you could write:
mysql> SET sort_buffer_size=2000000;
Query OK, 0 rows affected (0.00 sec)
To set a variable to apply across the server, rather than to the current client session,
you need to add the GLOBAL keyword:
mysql> SET GLOBAL sort_buffer_size=2000000;
Query OK, 0 rows affected (0.01 sec)
To set GLOBAL variables, you need to have superuser privileges (in practice, you need to
be logged in as the user root ):
mysql> SET max_connections=200;
ERROR 1227 (HY000): Access denied; you need the SUPER privilege for this operation
Some variables are inherently related to the server, rather than to an individual session.
MySQL will complain if you try to set a value for such variables without using the
GLOBAL keyword:
mysql> SET max_connections=200;
ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be
set with SET GLOBAL
The Slow Query Log
To determine what you should optimize, you should identify the frequently used quer-
ies that take a long time to complete. If you start the server with the log-slow-quer
ies option, any queries that take more than 10 seconds to complete will be logged. You
can change this duration by modifying the value of the long_query_time variable. You
can add the log-queries-not-using-indexes option to ask the server to also log queries
that don't use an index. The default location of the slow queries log is in the data
directory, with a name in the form <hostname>-slow.log .
Let's look at an excerpt from a slow query log:
# Time: 060630 22:51:32
# User@Host: root[root] @ localhost []