Learning Mysql

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 beset 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 beset with SET GLOBAL
The Slow Query Log
To determine what you should optimize, you should identify the frequently used queries that take along 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 -slow.log.
Let’s look at an excerpt from a slow query log Time 060630 22:51:32
# User@Host: root[root] @ localhost []

Download 4.24 Mb.

Share with your friends: