Menu
homeMicrosoft

Setting up MYSQL for Linux. What needs to be configured in mySQL immediately after installation

This article will describe various settings MySQL, mainly those that affect performance. For convenience, all variables are divided into sections (basic settings, restrictions, thread settings, query caching, timings, buffers, InnoDB). First, let's clarify the names of some variables that changed in version 4 of MySQL, and both old and new variants of names continue to be found on the Internet, which raises questions.

So, in version 4, a number of variables now have the ending _size. This applies to the thread_cache_size variable and variables from the section Buffers. And the read_buffer_size variable was called record_buffer before version 4. Also the skip_external_locking variable from section Basic settings before version 4 it was called skip_locking .

Variables fall into two main categories: value variables and flag variables. Variables with values ​​are written in the configuration file as variable = value , and flag variables are simply specified. Also, you probably noticed that in some cases “-” is used in variable names, and in others “_”. Variables with a hyphen are server starting options and cannot be changed while the server is running (using SET); variables with underscores are server operation options and can be changed on the fly. If we're talking about about a “status variable” or it is recommended to monitor the value of a variable whose name is written in the form Variable_Name , then you should run the SHOW STATUS LIKE “Variable_Name” request to get the value of this variable, or look at the status tab in phpMyAdmin, where there will be additional comments on the value of this variable.

Now let's start describing the variables and their possible values.

Basic settings

  • low-priority-updates- this option reduces the priority of INSERT/UPDATE operations compared to SELECT. This is relevant if it is important to read data faster than to write it faster.
  • skip-external-locking- the option is installed by default, starting from version 4. Instructs the MySQL server not to use external locks when working with the database. External locks are needed in situations where multiple servers are working with the same data files, i.e. have the same datadir , which is not used in practice.
  • skip-name-resolve- do not define domain names for the IP addresses of connecting clients. Wherein custom permissions you need to configure it not for hosts, but for IP addresses (with the exception of localhost). If you connect to the server only with local machine, then it doesn’t really matter. For external connections, it will speed up connection setup.
  • skip-networking- do not use the network, i.e. do not process TCP/IP connections at all. Communication with the server will occur exclusively through a socket. Recommended unless you have software that uses only TCP/IP to communicate with the server.

Restrictions

  • bind-address- the interface that the server will listen to. For security reasons, it is recommended to set 127.0.0.1 here if you are not using external connections with the server.
  • max_allowed_packet- the maximum size of data that can be transferred in one request. You should increase it if you encounter the "Packet too large" error.
  • max_connections - maximum amount parallel connections to the server. Increase it if you encounter the "Too many connections" problem.
  • max_join_size- prohibits SELECT statements, which are expected to parse more than a specified number of rows or more than a specified number of disk searches. Used to protect against crooked queries that try to count millions of rows. The default value is over 4 billion, so you'll likely want to reduce it significantly.
  • max_sort_length- indicates how many bytes from the beginning of BLOB or TEXT fields to use when sorting. The default value is 1024; if you are concerned about incorrectly designed tables or queries, you should reduce it.

Stream settings

  • thread_cache_size- indicates the number of cached threads. After processing the request, the server will not terminate the thread, but will place it in the cache if the number of threads in the cache is less than the specified value. The default value is 0, increase it to 8 or immediately to 16. If you observe an increase in the value of the Threads_Created state variable, then you should further increase thread_cache_size.
  • thread_concurrency- relevant only for Solaris/SunOS, contrary to what they write on the Internet. "Tells" the system how many threads to run simultaneously by executing a function call thr_setconcurrency. The recommended value is double or triple the number of processor cores.

Query Caching

  • query_cache_limit- the maximum size of a cached request.
  • query_cache_min_res_unit - minimum size block stored in cache.
  • query_cache_size- cache size. 0 disables cache use. For selection optimal value It is necessary to monitor the Qcache_lowmem_prunes state variable and ensure that its value increases only slightly. You also need to remember that an excessively large cache will create unnecessary load.
  • query_cache_type- (OFF, DEMAND, ON). OFF disables caching, DEMAND– caching will be performed only if there is a directive SQL_CACHE in the request, ON enables caching.
  • query_cache_wlock_invalidate- determines whether data will be taken from the cache if the table to which it belongs is locked for reading.

You can think of a query cache as a hash array whose keys are queries and whose values ​​are query results. In addition to the results, MySQL stores in its cache a list of tables, a selection from which is cached. If changes occur in any of the tables from which a sample is in the cache, MySQL removes such samples from the cache. MySQL also does not cache queries whose results may change.

At starting MySQL allocates a memory block of size query_cache_size . When executing a query, as soon as the first rows of the result are received, the server begins to cache them: it allocates a memory block in the cache equal to query_cache_min_res_unit and writes the result of the selection into it. If not the entire selection fits into a block, then the server allocates the next block and so on. At the moment of beginning MySQL records does not know about the size of the resulting sample, so if the sample size written to the cache is greater than query_cache_limit , then writing stops and busy place is freed, therefore, if you know in advance that the result of the sample will be large, it is worth executing it with the directive SQL_NO_CACHE.

Timings

  • interactive_timeout- time in seconds during which the server waits for activity from an interactive connection (using the flag CLIENT_INTERACTIVE) before closing it.
  • log_slow_queries- tells the server to log long (“slow”) queries (executing longer than long_query_time). The value is passed full name file (for example /var/log/slow_queries).
  • long_query_time- if the request runs longer than the specified time (in seconds), then it will be considered “slow”.
  • net_read_timeout
  • net_write_timeout- time in seconds that the server will wait to receive data before the connection is terminated. If the server does not serve clients with very slow or unstable channels, then 15 seconds will be enough here.
  • wait_timeout- time in seconds during which the server waits for a connection to be active before terminating it. IN general case 30 seconds will be enough.

Buffers

All buffers have common feature- if due to installation big size buffer data will go to the paging file, then the buffer will do more harm than good. Therefore, always focus on the amount of physical RAM available to you.

  • key_buffer_size- the size of the buffer allocated for indexes and available to all threads. Very important setting, affecting performance. The default value is 8 MB, it is definitely worth increasing it. 15-30% of total RAM is recommended, but there is no point in installing more than overall size all .MYI files. Watch the Key_reads and Key_read_requests state variables, the Key_reads/Key_read_requests ratio should be as small as possible (< 0,01). Если это отношение велико, то размер буфера стоит увеличить.
  • max_heap_table_size- the maximum allowable size of a table stored in memory (MEMORY type). The default value is 16 MB, if you are not using MEMORY tables, then set this value to tmp_table_size.
  • myisam_sort_buffer_size- the size of the buffer allocated by MyISAM for sorting indexes when REPAIR TABLE or to create indexes when CREATE INDEX, ALTER TABLE. The default value is 8 MB, it should be increased up to 30-40% of RAM. Accordingly, the performance gain will only be achieved when executing the above queries.
  • net_buffer_length- the amount of memory allocated for the connection buffer and for the results buffer per thread. The connection buffer will be specified size and the results buffer will be the same size, i.e. Each thread will be allocated twice the net_buffer_length . The specified value is an initial value and buffers will be increased as needed up to max_allowed_packet . The default size is 16 KB. If memory is limited or only small queries are used, the value can be reduced. In the case of constant use of large queries and sufficient memory, the value should be increased to the expected average query size.
  • read_buffer_size- each thread, when scanning tables sequentially, allocates the specified amount of memory for each table. As tests show, this value should not be particularly increased. The default size is 128 KB, try increasing it to 256 KB and then to 512 KB and observe the speed of queries like SELECT COUNT(*) FROM table WHERE expr LIKE "a%"; on large tables.
  • read_rnd_buffer_size- relevant for queries with " ORDER BY", i.e. for queries whose result must be sorted and which access a table that has indexes. The default value is 256 KB, increase it to 1 MB or higher if memory allows. Note that the specified memory value is also allocated to every thread.
  • sort_buffer_size- each thread performing sorting operations ( ORDER BY) or groups ( GROUP BY), allocates a buffer of the specified size. The default value is 2 MB, if you use the specified types of requests and if memory allows, then the value should be increased. A large value for the Sort_merge_passes state variable indicates that sort_buffer_size needs to be increased. It is also worth checking the execution speed of queries like SELECT * FROM table ORDER BY name DESC on large tables; perhaps increasing the buffer will only slow down the work (in some tests this is the case).
  • table_cache (table_open_cache from version 5.1.3) - number of cached open tables for all threads. Opening a table file can be quite a resource-intensive operation, so it's best to keep open tables in a cache. Note that each entry in this cache uses a system handle, so you may need to increase the handle limit ( ulimit). The default value is 64, it is best to increase it to the total number of tables if the number is within acceptable limits. The Opened_tables state variable allows you to track the number of tables that are opened bypassing the cache, preferably keeping its value as low as possible.
  • tmp_table_size- the maximum size of memory allocated for temporary tables, created by MySQL for your internal needs. This value is also limited by the max_heap_table_size variable, so it will end up being selected minimum value from max_heap_table_size and tmp_table_size , and the remaining temporary tables will be created on disk. The default value depends on the system, try setting it to 32 MB and watch the Created_tmp_disk_tables state variable, its value should be as small as possible.

The values ​​in the configuration file are specified in bytes, respectively, kilobytes and megabytes must be converted to bytes.

InnoDB

  • innodb_additional_mem_pool_size- the amount of memory allocated by InnoDB to store various internal structures. If InnoDB does not have enough of this memory, it will request memory from the OS and write a warning to the MySQL error log.
  • innodb_buffer_pool_size- the amount of memory allocated by InnoDB for storing both indexes and data. Meaning - the more, the better. Can be increased up to the total size of all InnoDB tables or up to 80% of RAM, whichever is less.
  • innodb_flush_log_at_trx_commit- has three acceptable values: 0, 1, 2. If the value is equal 0 , the log is flushed to disk once per second, regardless of ongoing transactions. With a value equal to 1 , the log is flushed to disk with each transaction. With a value equal to 2 , the log is written with each transaction, but is never flushed to disk, leaving this to the conscience of the OS. The default is 1, which is the most reliable setup, but not the fastest. In general, you can safely use 2, data can be lost only if the OS crashes and only in a few seconds (depending on the OS settings). 0 - the most fast mode, but data can be lost both when the OS crashes and when the MySQL server itself crashes (however, the data is only for 1-2 seconds).
  • innodb_log_buffer_size- log buffer size. The default value is 1 MB, it's worth increasing it if you know what will happen a large number of InnoDB transactions or if the value of the Innodb_log_waits state variable increases. You're unlikely to have to increase it beyond 8MB.
  • innodb_log_file_size- maximum size of one log file. When this size is reached, InnoDB will create new file. The default value is 5 MB, increasing the size will improve performance but increase data recovery time. Set this value in the range of 32 MB - 512 MB depending on the server size (judging it subjectively).

It is also convenient to use phpMyAdmin to monitor server operation; the tabs are of interest State And Variables. Additionally, phpMyAdmin gives advice on tuning certain variables depending on the server operating parameters.

When preparing an article, in addition to official documentation and your own head, the following materials were used.

  • 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 this small settings Server performance increases noticeably.

  • key_buffer_size- an extremely important setting when using MyISAM tables. Set it to about 30-40% of available random access memory, if you only use MyISAM. Right size depends on index sizes, data and server load - remember that MyISAM uses a cache operating system(OS) to store data, so you need to leave enough RAM space for data, and data can take up significantly more space than 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 can, in principle, work well even with big amount data and at 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 databases with frequent write operations to tables, especially with 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 server size.
  • 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 “currently in use.” 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 for 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 still have more tables or too many connections - increase the parameter value. I have seen mySQL servers with meaning 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 - under some conditions (if small quantity 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 values caches can only worsen server performance. Session variables are best left for later, for fine tuning server.

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

The default configuration parameters in Mysql are designed for small databases running 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 is a free database management system. Development and MySQL support is carried out by Oracle Corporation, which has acquired 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 custom functionality licensed users, it is thanks to this 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. How larger size 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 in to a large extent 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.

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 a directory MySQL data(usually /usr/local/mysql/data for a binary installation or /usr/local/var for an installation from source texts). 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 the server's viewing of parameter files, since they are viewed before the arguments are processed command line).

On Windows, the default MySQL parameters are read from the following files:

Please note that on Windows all paths must be specified with / instead of \. If you must use \, you must specify it twice because \ is an escape character in MySQL.

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 options specified on the command line have more high priority in relation to 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 (see Appendix F, 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.

Parameter files may contain lines like the following:

    Comment lines begin with a " # " or " ; " character. Blank lines are ignored.

    group is the name of the program or group for which you want to set parameters. Any parameters or strings specifying variable values ​​that are specified after a group line will be assigned to the named group until the parameter file ends or another group line is specified.

    Equivalent to --option on the command line.

    Equivalent to --option=value on the command line.

    set-variable = variable=value

    Equivalent to --set-variable variable=value on the command line. This syntax must be used to set mysqld variables. Note that --set-variable is not used as of MySQL 4.0. Just use --variable=value .

The client group provides the ability to set parameters that apply to all MySQL clients (except mysqld itself). This group is great for specifying a password to use when connecting to the server (but make sure that only you have permission to read and write this file).

Note that for parameters and values, any spaces entered before or after them are automatically removed. The following escape sequences can be used in value strings: "\b", "\t", "\n", "\r", "\\" and "\s" ("\s" is a space).

Example of a typical global options file:

Port=3306 socket=/tmp/mysql.sock port=3306 socket=/tmp/mysql.sock set-variable = key_buffer_size=16M set-variable = max_allowed_packet=1M quick

Example typical file user parameters:

# Specified password will be sent to all standard MySQL clients password=my_password no-auto-rehash set-variable = connect_timeout=2 interactive-timeout

If you have a source code distribution, then examples configuration files named my-xxxx.cnf can be found in the support-files directory. In case of a binary distribution, you should refer to the DIR/support-files directory, where DIR is the directory name MySQL installations(usually /usr/local/mysql). On this moment there are examples of configuration files for small, medium, large and very large systems. To experiment with the file, you can copy my-xxxx.cnf to your home directory (rename the copy to .my.cnf).

All MySQL clients that support option files accept the following options:

Please note that the above parameters must come first on the command line! However, the --print-defaults option can be used immediately after the --defaults-xxx-file commands.

Note to developers: Parameter file processing occurs as follows: all matching parameters (that is, parameters in matching groups) are processed before any command line arguments. This algorithm is well suited for programs that, if the same parameter is specified several times, use the last instance of the parameter. If you are working with old program, which reads multiple-specified parameters in the specified manner, but does not read parameter files, you only need to add two lines to give it this capability. To see how this is done, just read source code any standard MySQL client.

I don’t know why, but by default MySQL settings are designed for desktops of the 90s. For example, 8Mb of memory for InnoDB indexes. Remember how Bill Gates said that “640 KB of memory should be enough for everyone.” Default MySQL settings from this series.

To begin with, my extract from the config (4G RAM, AMD Athlon 64 X2 Dual 5600+)

#UTF ONLY! ONLY HARDCORE! collation_server=utf8_general_ci character_set_server=utf8 default-character-set = utf8 # let the default be InnoDB default-storage-engine = InnoDB key_buffer_size = 512M innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 16M innodb_flush_method = O _DIRECT innodb_flush_log_at_trx_commit = 2 innodb_thread_concurrency = 8 join_buffer_size = 8M sort_buffer_size = 8M read_rnd_buffer_size = 8M tmp_table_size = 64M max_heap_table_size = 32M table_cache = 256 log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 1 query_cache_type = 2 query_cache_limit = 1M query_cache_size = 32M

How to see if something is wrong with the database
Most quick option go to phpMyAdmin in the “Current MySQL Status” tab
There you will see tips on what settings you need to tweak.

The settings themselves can be viewed in the same phpMyAdmin in the “System Variables” tab.

MySQL has several settings with which you can overclock the database to the first space one. First, the settings for storing indexes in memory. Not only do indexes significantly speed up retrievals, but if they are stored in memory and not on disk (where they are usually located), the profit will be significant.

key_buffer_size = 512M
Thus, we allocate 512 Mb for indexes of MyISAM tables. The fact is that I have half of my databases in MyISAM (this is how it happened historically). 99.9% of these databases are used for reading, so there is no point in switching to InnoDB.

innodb_buffer_pool_size = 512M
We allocate the same amount of memory to InnoDB tables.
Here you need to know when to stop. If you have 1 database with a size of 100 Mb, then there is no point in allocating 1 GB of memory - it will not be used anyway.
Secondly, you need to look not at the size of the table, but at the size of the indexes. Real life example: a table of 300,000 comments weighs 300 MB, and its indexes take up 15 times less, which is quite logical, since indexes are usually placed on numeric and temporary columns, and not on text. You can again view this in phpMyAdmin

innodb_additional_mem_pool_size = 16M
The amount of memory allocated by InnoDB to store various internal structures.

innodb_flush_method = O_DIRECT
Here we turn off table buffering for file system and tell MySQL to access the files directly.

innodb_flush_log_at_trx_commit = 2
With each transaction, MySQL writes a log and flushes it to disk (value 1). Value 2 – reset to memory. It is not critical for me to lose transactions in the last 2 seconds if the server crashes.

join_buffer_size = 8M
Memory for queries with joins, when the join occurs without using indexes.

sort_buffer_size = 8M
read_rnd_buffer_size = 8M
Useful for queries with ORDER BY sorting and GROUP BY grouping. If the value is small, sorting occurs in a temporary table on disk.

tmp_table_size = 64M
max_heap_table_size = 32M
Settings for storing temporary tables in memory. Temporary tables are often formed during large joins.

table_cache = 256
Maximum number of tables open at the same time.

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1