Partitioning tables in MySQL is splitting a large table into parts according to the specified criteria, which will speed up reading and writing data to it if this table contains a very large amount of data.
Continue reading “Partitioning tables in MySQL”Category Archives: MySQL
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.
How to change default-storage-engine in MySQL
When creating a table, the ENGINE option is used, which specifies the storage engine, if this option is not specified in the SQL query, then the default storage engine is used, which is specified in the MySQL server configuration file or when it starts mysqld –default-storage-engine=InnoDB.
Continue reading “How to change default-storage-engine in MySQL”Changing thread_cache_size in MySQL
thread_cache_size – responsible for caching client threads after they are disconnected so that they can be reused, for example, when hundreds of connections are made to the server per second, this parameter can greatly improve performance.
Continue reading “Changing thread_cache_size 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.
How to change innodb_read_io_threads in MySQL
I will give an example of changing innodb_read_io_threads in MySQL.
The innodb_read_io_threads value determines the number of I/O threads for read operations in InnoDB. The default is 4, the minimum possible is 1, the maximum is 64.
How to change innodb_write_io_threads in MySQL
The innodb_write_io_threads value determines the number of I/O threads for writing to InnoDB. The default is 4, the minimum possible is 1, the maximum is 64.
Continue reading “How to change innodb_write_io_threads 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.
Changing max_allowed_packet in MySQL
max_allowed_packet is the maximum size of data that can be transferred in one request. Increase the value when the “Packet too large” error occurs so that the connection is not interrupted.
The default value for MySQL client is 16 MB, for MySQL server 64 MB.
Moving the MySQL data directory to a new location
In this article I will describe the process of moving the MySQL data directory to a separate location, namely on a level 10 raid with 4 disks, this will increase performance.
Continue reading “Moving the MySQL data directory to a new location”