Migrating MySQL Directory

Once I created a raid, mounted it as a disk system partition and transferred the MySQL directory directly to it, but in this case there were drawbacks, the lost+found directory could appear, it could not be used for other data, etc., after that I began to transfer the MySQL directory not directly to the partition, but created a directory on it and transferred it to it.

Continue reading “Migrating MySQL Directory”

How to change innodb_io_capacity in MySQL

innodb_io_capacity – determines the number of write IOPS for background InnoDB tasks, such as flushing pages from a buffer pool, etc. If the cleanup operation is lagging, then InnoDB can perform cleanup more aggressively at a higher IOPS, which is defined in the innodb_io_capacity_max variable.

Continue reading “How to change innodb_io_capacity in MySQL”

Installing and configuring xt_NAT

I will give an example of installing and configuring xt_NAT on Ubuntu Server 14.04 with kernel version 4.4.0-142. xt_NAT may not work on newer kernel versions. According to the developer, it should work on Linux Kernel 3.18 and 4.1. To make it work on newer versions of the kernel, you can find patches.

Continue reading “Installing and configuring xt_NAT”

How to change innodb_stats_on_metadata in MySQL

innodb_stats_on_metadata – Lets you collect statistics on metadata operations such as SHOW TABLE STATUS when accessing INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS tables.
In newer versions of MySQL, the innodb_stats_on_metadata parameter is disabled by default, which makes working with these tables faster.

Continue reading “How to change innodb_stats_on_metadata in MySQL”

Changing innodb_flush_log_at_trx_commit in MySQL

Default value is 1, possible values are 0-2.

0 – Logs are written and flushed to disk once per second. Transactions that have not been flushed out can be lost as a result of a crash.
1 – Logs are written and flushed to disk every time a transaction is committed.
2 – Logs are written after each commit of a transaction and flushed to disk once per second. Transactions that have not been flushed out can be lost in the event of a failure.

Continue reading “Changing innodb_flush_log_at_trx_commit in MySQL”

How to change innodb_thread_concurrency in MySQL

I will give an example of changing innodb_thread_concurrency in MySQL.
Since InnoDB uses operating system threads to process user transactions, the innodb_thread_concurrency parameter allows you to limit them. By default in new MySQL versions the value is 0, which means that there is no limit on the number of simultaneously executed threads and this is correct for modern servers. If you want to limit, then when the limit is reached, the extra threads will wait a certain number of microseconds specified in the innodb_thread_sleep_delay parameter, and then try to get into the queue. Also in MySQL 5.6.3 and higher, the innodb_adaptive_max_sleep_delay parameter was added which allows you to specify the maximum number of microseconds for the innodb_thread_sleep_delay parameter and then InnoDB automatically adjusts innodb_thread_sleep_delay.

Continue reading “How to change innodb_thread_concurrency in MySQL”