Since version 3.22, MySQL can read default startup options for the server and clients from option files. On Unix, the default MySQL parameters are read from the following files:

DATADIR is the MySQL data directory (usually "/usr/local/mysql/data" for binary installation or "/usr/local/var" for installation from source). Note that this is the directory that was specified during setup, not the one specified with –datadir when starting mysqld! (–datadir has no effect on how parameter files are viewed by the server, since they are viewed before the command line arguments are processed).

MySQL tries to read the parameter files in the order listed above. If there are several such files, then the parameter specified in the file coming later takes precedence over the same parameter specified in the file located earlier. The parameters specified in command line, have higher priority than the parameters specified in any of the parameter files. Some parameters can be set using environment variables. Options specified on the command line or in option files take precedence over environment variables.

Here is a list of programs that support parameter files: mysql, mysqladmin, mysqld, mysqld_safe, mysql.server, mysqldump, mysqlimport, mysqlshow, mysqlcheck, myisamchk and myisampack.

Any parameter that can be specified on the command line when running a MySQL program can also be specified in the parameters file (without the leading double slash). To get a list available parameters, you should run the program with the –help parameter.

My.cnf MySQL 5.5 parameters (UTF8 encodings)

What is utf8mb4? utf8mb4 is a character set used to store 4 bytes in MySQL, introduced in 2010 starting with version 5.5.3. The main difference between utf8mb4 and utf8 is that utf8mb4 uses more full capabilities UTF8 encoding, allowing support for all languages ​​and Special symbols, which do not support utf8 (for example, Japanese or emoticons from ios - emoji). However, as you might guess, if utf8mb4 uses 4 bytes to store 1 character, then the database may increase in size when compared with exactly the same database in utf8. Nowadays, a slightly increased database size is not a significant problem, so if you are faced with the choice of using a utf8 or utf8mb4 character set, use utf8mb4.

Some my.cnf parameters in MySQL 5.5.22 have been deprecated and have been replaced and removed. For example, changing the default encoding in my.cnf in the section would look like this:

#... character_set_server = utf8 # previously default-character-set = utf8 and character_set_server = utf8 collation-server = utf8_unicode_ci # previously collation_server = utf8_unicode_ci

collation-server = utf8_unicode_ci or collation-server = utf8_general_ci? utf8_unicode_ci supports expansions, unlike utf8_general_ci, that is, it can match one character to several (for example, in Germany ß = ss). More like Unicode Character Sets.

Comparison utf8_unicode_ci _ci case insensitive, utf8_unicode_bin _bin case sensitive.

my.cnf parameters

> ee /etc/my.cnf # The following options will be passed to all MySQL clients #password = your_password port = 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M default-character- set = utf8 character_set_server = utf8 collation_server = utf8_unicode_ci bind-address = 127.0.0.1 # Don"t listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless ! # #skip-networking #...

There are 2 main engines in MySQL: InnoDB And MyISAM. Both types of database tables can exist on the same server. If necessary, they can be converted; the use of MyISAM is justified when there is a predominant number of operations with data of the same type: for example, SELECT or INSERT.

In all other cases, it is more profitable to use InnoDB - this engine provides better data safety and more high speed working with them (a row is blocked during an operation with a table, and not the entire table as is the case with MyISAM).

Any MySQL tuning - any fine tuning should begin with determining the predominant number of tables of a certain type.

Determining table type in MySQL

This is done using a query like this (for innodb):

WHERE engine = "innodb";

Or (for myisam):

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = "myisam";

It is worth running both queries and estimating the number of tables related to each engine.

MySQL configuration in my.cnf

The following describes the main options when configuring MySQL to primarily use InnoDB.

All further modifications are made in the configuration file /etc/mysql/my.cnf

innodb_buffer_pool_size— buffer size for InnoDB tables and indexes. If InnoDB tables predominate, it is worth setting the value equal to 80% of the total amount of RAM (8 GB for a server with 10 GB RAM is the norm). For more powerful servers given value you can increase it up to 95% of the available RAM.

innodb_buffer_pool_instances- Very important parameter, which determines the number of instances that can exist, the default value of the parameter is 1, a more reasonable value is calculated as the sum innodb_buffer_pool_size in gigabytes and CPU cores, divided in half.

innodb_buffer_pool_size in Gb + CPUs)/2

innodb_flush_log_at_trx_commit- the value is set to 0, 1, 2. 0 means that the log is flushed to disk once per second, regardless of transactions. When set to 1, the log is reset for each completed transaction. 2 - the log is stored in RAM. The database server will run fastest at 0.

innodb_log_buffer_size- log buffer size 1-8 MB are good values

innodb_log_file_size- maximum size of each log file. You can increase the value, this will give better performance since you will not need to rotate the log too often, open and close new files.

General parameters (not related to engine type)

max_connections=2000— set the parameter to the minimum possible if necessary to save server resources; if entries like “Too many connections…” appear in the log, increase the value. 4000 clients is the maximum. Can be brought maximum amount clients up to 7000, but for standard builds 4000 is the limit.

key_buffer=1024M— buffer size for indexes in random access memory. Optimal value 20-25% of available RAM. If the parameter value is too small, data will begin to be written to SWAP, which will reduce the operating speed significantly.

Enough memory is allocated for indexes if the ratio of the Key_reads/Key_read_request values ​​is< 0,01

You can find out the parameter values ​​by running a query in the database server console

SHOW STATUS LIKE "Key%";

The output will also contain the values ​​Key_write_requests and Key_writes

table_cache=2048– maximum number open tables for all threads.

Increasing the parameter means increasing the file descriptors used to run MySQL.

To reveal required value need to complete a request

SHOW STATUS LIKE "Opened_tables%";

Then set the value of the variable to be slightly larger than the value in the output:
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Opened_tables | 1756 |
+—————+——-+

open_files_limit = 2048

The value should be determined based on the existing quantity open files MySQL

A higher value is specified in the configuration file.

sort_buffer=128M– speeds up ORDER BY or GROUP BY queries. Installs not very large (usually depending on the environment)

The amount of RAM allocated will be determined as the value of sort_buffer multiplied by Threads_running

SHOW STATUS LIKE "Threads_running%";

record_buffer=720M— a good value would be the equivalent of sort_buffer multiplied by 4-6.

query_cache_limit=2M– the maximum size of the sample result (or other query) that will be cached. The value can be increased; the default is 1 MB.

max_join_size=1000000— the maximum number of records that can be processed at once. A kind of “dupak protection” when using JOIN.

thread_cache_size=64— sets the number of data processing threads in the cache, i.e. the number of threads that are not created again for each request. You can increase the parameter, this will have a positive effect on performance

August 10, 2009 at 03:41 pm

What needs to be configured in mySQL immediately after installation?

• MySQL

• Translation

Free translation of a rather old article from the MySQL Performance Blog about what is best to configure immediately after installation basic version mySQL.

It's amazing how many people install mySQL on their servers and leave it with default settings.

Although there are quite a few settings in mySQL that you can change, there are a set of really very important characteristics, which must be optimized for own server. Usually, after such small tweaks, server performance increases noticeably.

• key_buffer_size- an extremely important setting when using MyISAM tables. Set it to about 30-40% of available RAM if you are using MyISAM only. Right size depends on the size of the indexes, the data and the load on the server - remember that MyISAM uses the operating system (OS) cache to store the data, so you need to leave enough RAM space for the data, and the data can take up significantly more space than the indexes. However, be sure to check that all the space allocated by the directive key_buffer_size for cache, was constantly used - you can often see situations where 4 GB are allocated for the index cache, although the total size of all .MYI files does not exceed 1 GB. Doing this is completely useless; you will only waste resources. If you have practically no MyISAM tables, then key_buffer_size should be set to about 16-32 MB - they will be used to store indexes of temporary tables created on disk in memory.
• innodb_buffer_pool_size- an equally important setting, but for InnoDB, be sure to pay attention to it if you are going to mainly use InnoDB tables, because they are much more sensitive to buffer size than MyISAM tables. MyISAM tables, in principle, can work well even with a large amount of data and with the standard value key_buffer_size, however mySQL can be very slow if the value is incorrect innodb_buffer_pool_size. InnoDB uses its own buffer to store both indexes and data, so there is no need to leave memory for the OS cache - install innodb_buffer_pool_size in 70-80% of available RAM (if, of course, only InnoDB tables are used). Relatively maximum size this option - similar key_buffer_size- don’t get carried away, you need to find optimal size, find the best use of available memory.
• innodb_additional_mem_pool_size- this option has virtually no effect on the performance of mySQL, but I recommend leaving about 20 MB (or a little more) for InnoDB for various internal needs.
• innodb_log_file_size- an extremely important setting in database environments with frequent operations records in tables, especially when large volumes. B O Larger sizes increase performance, but be careful - data recovery time will also increase. I usually set it to around 64-512 MB depending on the size of the server.
• innodb_log_buffer_size- the standard value of this option is quite suitable for most systems with an average number of write operations and small transactions. If your system experiences bursts of activity, or you actively work with BLOB data, then I recommend increasing the value slightly innodb_log_buffer_size. However, don't overdo it - too much great importance would be a waste of memory: the buffer is flushed every second, so you won't need more space than required during that second. The recommended value is about 8-16 MB, and for small databases even less.
• - complaining that InnoDB is 100 times slower than MyISAM? You probably forgot about the setting innodb_flush_log_at_trx_commit. The default value of 1 means that every UPDATE transaction (or similar non-transactional command) must flush the buffer to disk, which is quite resource-intensive. Most applications, especially those that have previously used MyISAM tables, will work well with a value of "2" (i.e. "do not flush the buffer to disk, only to the OS cache"). The log, however, will still be flushed to disk every 1-2 seconds, so in case of an accident you will lose a maximum of 1-2 seconds of updates. A value of "0" will improve performance, but you risk losing data even if the mySQL server crashes, while setting the value to innodb_flush_log_at_trx_commit in “2” you will lose data only if the entire operating system crashes.
• table_cache- opening tables can be quite resource-intensive. For example, MyISAM tables mark the headers of .MYI files as "used in this moment" It's generally not a good idea to open tables too often, so it's best to have a cache large enough to keep all your tables open. This uses some OS resources and RAM, but this is usually not a significant problem on modern servers. If you have several hundred tables, then the starting value for the option table_cache could be "1024" (remember that each connection requires its own handle). If you have even more tables or a lot of connections, increase the value of the parameter. I saw a mySQL server with the value table_cache equal to 100,000.
• thread_cache- creating/destroying threads is also a resource-intensive operation that occurs every time a connection is established and every connection is broken. I usually set this option to 16. If your application may have jumps in the number of concurrent connections and by variable Threads_Created If you see a rapid increase in the number of threads, then you should increase the value thread_cache. The goal is to prevent the creation of new threads under conditions normal functioning server.
• query_cache_size- if your application reads data a lot and frequently, and you do not have an application-level cache, this option can be very helpful. Don't set this value too high, as maintaining a large query cache will be costly in itself. The recommended value is from 32 to 512 MB. Don't forget to check how well the query cache is being used - in some conditions (with a small number of hits in the cache, i.e. when almost no identical data is fetched) using a large cache can degrade performance.

As you can see, these are global settings. These variables depend on the server hardware and the MySQL engines used, while session variables are usually configured specifically for specific tasks. If you mainly use simple queries, then there is no need to increase the value sort_buffer_size, even if you have an extra 64 GB of RAM. Moreover, large cache values ​​can only degrade server performance. It is better to leave session variables for later, for fine-tuning the server.

PS: the mySQL installation comes with several pre-installed my.cnf files, designed for different loads. If you don't have time to configure the server manually, then it's usually better to use them than the standard one configuration file, choosing the one that is more suitable for the load of your server.

The default configuration parameters in Mysql are designed to small bases data, working under low loads on very modest hardware. If your plans for Mysql extend beyond the table boundaries by several hundred records, you will definitely have to change the default settings. Process optimal settings Mysql consists of two parts - initial setup and adjusting parameters during operation. Adjusting parameters in operating mode largely depends on the specifics of your system and its monitoring - there are no special rules here. For starting settings There are a number of recommendations:

MySQL - free system database management. MySQL is developed and supported by Oracle Corporation, which has received the rights to trademark together with the acquired Sun Microsystems, which had previously acquired the Swedish company MySQL AB. The product is distributed under both the GNU General Public License and its own commercial license. In addition, developers create functionality at the request of licensed users, it is thanks to such an order that almost the most earlier versions a replication mechanism appeared.

Open mysql settings file, for example:

/etc/mysql/my.cnf

The most common parameters that you should pay attention to and change to suit your requirements:

key_buffer_size

If you only use MyIsam tables, set this value to 30%...40% of all available RAM on the server. MyIsam uses the operating system cache for data, so be aware that the remaining free memory you will need it for exactly this. If you have few MyIsam tables and their total size is small, leave this value within 32M.

innodb_buffer_pool_size

If you only use InnoDB tables, set this value to the maximum possible for your system. The InnoDB buffer caches both data and indexes (and the operating system cache is not used), so the value of this key should be set to 70%...80% of the available memory.

If your server runs on Linux or Unix, do not forget to set the innodb_flush_method parameter to “O_DIRECT” to avoid caching at the OS level what Mysql already caches.

innodb_log_file_size

Pay attention to this parameter if you have a large number of records. The larger the size of this key, the more efficient the data recording will be. But keep in mind that this will increase the system recovery time! This parameter is usually set to 64M-512M.

innodb_flush_log_at_trx_commit

This parameter significantly affects the speed of operation (writing) of innoDB tables.
The value “1″ means that any completed transaction will synchronously flush the log to disk.
The value “2″ does the same thing, only it dumps the log not to disk, but to the operating system cache. This value is suitable in most cases, because... does not perform an expensive write operation after each transaction. In this case, the log is written to disk with a delay of several seconds, which is very safe from the point of view of data safety.
A value of “0” will give the best performance. In this case, the buffer will be flushed to the log file regardless of transactions. Set this parameter to “0” at your own risk, because in this case, the risk of data loss increases.

table_cache

This key determines the memory allocated to store open tables. If you have several hundred tables, set this value to 1024. If you have great amount connections, increase this value gradually, because A separate record is stored for each connection.

thread_cache

This parameter helps to avoid thread creation/destruction operations when connecting to the server. Set this parameter to 16 and increase as needed. Check the “Threads_created” indicator, ideally it should be equal to zero:

Mysql> show status like 'threads_created'; +-----–+--–+ | Variable_name | Value | +-----–+--–+ | Threads_created | 423312 | +-----–+--–+

query_cache_size

The value of this parameter determines how much memory should be used for the query cache. Don't get carried away with setting huge values. The query cache should not be large, because... mysql will eat up resources for managing data in the cache. Start with 32M...128M, and increase as needed.

Installing MySQL server from repository

For installation MySQL server in Debian, just type the command:
apt-get install mysql-server

IN operating system RHEL/CentOS is also quite simple:
yum install mysql-server

Please note: After installing the MySQL server, you need to start it. To do this (both on Debian and RHEL/CentOS) you need to run the command:
service mysqld start

Installing the MySQL client from the repository

To install the mysql client, run in debian next command:
apt-get install mysql-client

If you have RHEL / CentOS installed, run the following command:
yum install mysql

This will install the current one (at the time the command is executed) MySQL version. At the time of writing, this is version 5.5.

Installing MySQL libraries for compilation support (MySQL development) from the repository

If you install MySQL not of your own free will, but because it is necessary to install some program (for example, asterisk, cdr_mysql support), then simply installing the MySQL server (and/or client) is not enough. You must also install the MySQL libraries for a MySQL-dependent program to compile successfully.

For debian this would be the command:
apt-get install libmysqlclient-dev

For RHEL/CentOS run:
yum install mysql-devel

Initial MySQL setup

Even if you install MySQL on a home/test computer (not to mention installation in a production environment), you must take at least the minimum steps to configure the MySQL server (as a rule, you do not need to configure the client). For example, set the root user password in MySQL system. The default password for root is empty (no password).

Set MySQL root password

To easily set a password for the root user (in case the password was not requested during the installation of MySQL itself), run the command:

Where rootpass is the password for the root user. Advice: if you want this command not to appear in the command history (and no one could subsequently spy on the root password from the command history), simply put a space in front of this command. That is:
/usr/bin/mysqladmin -u root password "rootpass"

Setting up MySQL to work in a production environment

Run from the command line:
/usr/bin/mysql_secure_installation

This script (if you answer yes to the questions asked) will install New Password root (password will be requested), deletes the anonymous user, prohibits logging in with remote machines as root, will delete the test database.

Create the required database and user for it

To create a database in MySQL, you must first connect to MySQL and then run a mysql query to create the database. To do this, run from the Linux command line:
mysql -u root -p

This will prompt you for a password for the user whose name is specified after the -u option (in in this case- root user password). If you enter the password correctly, a MySQL prompt will appear to enter commands:
mysql>_

This is the MySQL command line. All SQL queries and commands to create databases, users, etc. are entered on this command line.

To create a database, run on the MySQL command line:
create database database-name character set database-encoding;

For example:
create database asterisk character set utf8;

You don't have to specify the encoding, but the default encoding will be used (see MySQL server settings):
create database mydatabase;

Now let's create MySQL user and let's give it to him full rights to the created database:
grant all privileges on database_name.* to user_name @localhost identified by "user_password";
For example:
grant all privileges on asterisk.* to asterisk_user@localhost identified by "asterisk_password";

adding tables from a file to the database

If you have tables in files that you would like to add to the database, run the following command:
mysql -u mysql-username -p database-name< имя-файла-с-таблицей
For example:
mysql -u root -p asterisk< asterisk-cdr.sql
in this case, after running the specified command, you will be asked for the MySQL user password (in this case, the root user password), after which (if the password is correct!) it will be executed MySQL query, located in a file (in this case, in asterisk-cdr.sql).

There are also a lot of additional options/options/recommendations for installing/configuring MySQL, in particular setting the default encoding/language (MySQL parameters are stored in my.cnf), searching for users with empty passwords and replacing passwords with non-empty ones, setting up iptables for remote connection to mysql, and so on. Here we have considered minimally necessary actions to quickly get started with MySQL.