MySQL

MySQL IntroductionComment MySQLInstall MySQL container with Docker-ComposeMySQL AdminMySQL ALTER TABLEMySQL ArithmeticMySQL BackticksMySQL Backup using mysqldumpMySQL Change PasswordMySQL Character Sets and CollationsMySQL clientMySQL ClusteringMySQL Configuration and tuningMySQL Connecting with UTF-8 Using Various Programming language.MySQL Converting from MyISAM to InnoDBMySQL Create New UserMySQL Creating databasesMySQL Customize PS1MySQL Data TypesMySQL Date and Time OperationsMySQL Dealing with sparse or missing dataMySQL DELETEMySQL Drop TableMySQL Dynamic Un-Pivot Table using Prepared StatementMySQL ENUMMySQL Error 1055: ONLY FULL GROUP BY: something is not in GROUP by clause ...MySQL Error codesMySQL EventsMySQL Extract values from JSON typeMySQL Full-Text searchMySQL Group byMySQL Handling Time ZonesMySQL Indexes and KeysMySQL INSERTMySQL JoinsMySQL JOINS: Join 3 table with the same name of id.MySQL JSONMySQL Limit and OffsetMySQL LOAD DATA INFILEMySQL LOCK TABLEMySQL Log filesMySQL Many-to-many Mapping tableMySQL MyISAM EngineMySQL mysqlimportMySQL NULLMySQL One to ManyMySQL ORDER byMySQL PartitioningMySQL Performance TipsMySQL Performance TuningMySQL Pivot queriesMySQL PREPARE StatementsMySQL Recover from lost root passwordMySQL Regular ExpressionsMySQL ReplicationMySQL Reserved WordsMySQL Security via GRANTsMySQL SELECTMySQL Server InformationMySQL SSL Connection SetupMySQL Stored routines (procedures and functions)MySQL String operationsMySQL Table CreationMySQL Temporary TablesMySQL Time with subsecond precisionMySQL TransactionMySQL TRIGGERSMySQL UNIONMySQL UnionsMySQL UPDATEMySQL Using VariablesMySQL VIEWRecover and reset the default root password for MySQL 5.7+



MySQL Configuration and tuning

From WikiOD

Remarks[edit | edit source]

Configuration happens in one of 3 ways:

  • command line options
  • the my.cnf configuration file
  • setting variables from within the server

Command Line options takes the form mysqld --long-parameter-name=value --another-parameter. The same parameters can be placed in the my.conf configuration file. Some parameters are configurable using system variables from within MySQL. Check the official documentation for a complete list of parameters.

Variables can have dash - or underscore _. Spaces may exist around the =. Large numbers can be suffixed by K, M, G for kilo-, mega-, and giga-. One setting per line.

Flags: Usually ON and 1 are synonymous, ditto for OFF and 0. Some flags have nothing after them.

When placing the settings in my.cnf, all settings for the server must be in the [mysqld] section, so don't blindly add settings to the end of the file. (Note: For tools that allow multiple mysql instances to share one my.cnf, the section names may be different.)

InnoDB performance[edit | edit source]

There are hundreds of settings that can be placed in my.cnf. For the 'lite' user of MySQL, they won't matter as much.

Once your database becomes non-trivial, it is advisable to set the following parameters:

innodb_buffer_pool_size

This should be set to about 70% of available RAM (if you have at least 4GB of RAM; a smaller percentage if you have a tiny VM or antique machine). The setting controls the amount of cache used by the InnoDB ENGINE. Hence, it is very important for performance of InnoDB.

Parameter to allow huge data to insert[edit | edit source]

If you need to store images or videos in the column then we need to change the value as needed by your application

max_allowed_packet = 10M

M is Mb, G in Gb, K in Kb

Increase the string limit for group_concat[edit | edit source]

group_concat is used to concatenate non-null values in a group. The maximum length of the resulting string can be set using the group_concat_max_len option:

SET [GLOBAL | SESSION] group_concat_max_len = val;

Setting the GLOBAL variable will ensure a permanent change, whereas setting the SESSION variable will set the value for the current session.

Minimal InnoDB configuration[edit | edit source]

This is a bare minimum setup for MySQL servers using InnoDB tables. Using InnoDB, query cache is not required. Reclaim disk space when a table or database is DROPed. If you're using SSDs, flushing is a redundant operation (SDDs are not sequential).

default_storage_engine = InnoDB
query_cache_type = 0
innodb_file_per_table = 1
innodb_flush_neighbors = 0

Concurrency

Make sure we can create more than than the default 4 threads by setting innodb_thread_concurrency to infinity (0); this lets InnoDB decide based on optimal execution.

innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64

Hard drive utilization

Set the capacity (normal load) and capacity_max (absolute maximum) of IOPS for MySQL. The default of 200 is fine for HDDs, but these days, with SSDs capable of thousands of IOPS, you are likely to want to adjust this number. There are many tests you can run to determine IOPS. The values above should be nearly that limit if you are running a dedicated MySQL server. If you are running any other services on the same machine, you should apportion as appropriate.

innodb_io_capacity = 2500
innodb_io_capacity_max = 3000

RAM utilization

Set the RAM available to MySQL. Whilst the rule of thumb is 70-80%, this really depends on whether or not your instance is dedicated to MySQL, and how much RAM is available. Don't waste RAM (i.e. resources) if you have a lot available.

innodb_buffer_pool_size = 10G

Secure MySQL encryption[edit | edit source]

The default encryption aes-128-ecb uses Electronic Codebook (ECB) mode, which is insecure and should never be used. Instead, add the following to your configuration file:

block_encryption_mode = aes-256-cbc

Credit:Stack_Overflow_Documentation