MySQL: Global Variables and Configuration

by Mark Nielsen
Copyright 2022

In time, we will adding more sections.

Links
Linux system configuration
MySQL, Percona, MariaDB
Galera

Linux

You should have twice the ram, cpu, diskspace, and IO you think you need for the next 3 years.
Swappiness to be set to 1 :
```
echo 1 > /proc/sys/vm/swappiness
```
NOTE: You should have a swap partition. The Linux system needs it and if memory gets into a bad state a swap partition might save you. Always monitor the swap partition.
You might to use the XFS, ext4, or other filesystems.
- EXT4 and XFS: notime --- this speeds up a database so that it doesn't write to disk each time something accesses the file.
- EXT4 and XFS: obarrier: "use this only if the disk has battery backup or is ssd." This is quoted from one of the docs above.
- EXT4: data=writeback : This appears to be minor.
You might want to mount a ramdisk for tmp directory of Creation of files (for example internal temp tables) or for a database where you will recreate an empty schema on database. Any data lots of a restart can be gotten again. We aware of replication (you might want it on the slaves too).
Disk Subsystem – I/O scheduler : none or deadline. You may want to analyze the best one as the Percona doc says.
For disks,
The best disks in a RAID 5 or RAID 10 is probably the best.
Often "data" usually has it own partition and "logs" have their own partition. If you do LVM snapshotting, you might want the data and binlogs on the same partition.
Use SSD or high performance disks.
Mount a NFS or attached storage or a high performance for doing backups and restores to all the servers even it is inactive for the most part.
You want your clock times sycned.

MySQL, Percona, MariaDB

We will include INNODB settings in the lists below. We manually add sections for other engines later.

Very important configurations

Not really a configuration, but run "explain" on queries and analyze your slow log. Queries might not use indexes right, might have temporary tables that right to disk, buffers (join, read, write, sort, etc) might be big enough, there might be an "or" condition in a join, or many others reasons why it is not efficient.
Make sure you innodb buffer pool uses 75% of ram.
Make sure you have big innodb logs. If they are small and fill up queries go slow. Some people like small logs so failovers happen quick. But if the logs are big enough a failover might not have to happen. There is no logical reason why to have small innodb logs if you have good failover technology. You might want to GTID, automatic failovers, and semisynchronous replication.
sync
You should have multiple buffer pools for innodb. "1" per gig is good.
Make one file per table for innodb is turned on.
Make sure your table cache is big enough for all tables and table partitions.
Make sure the amount of memory to innodb buffer pool covers all hot data if your data is bigger than the amount of ram.
Use a diagnosis tool like Solar Winds. Not a configuration change but can lead to configuration changes.
You might want to configure indirect things like slow logs and Performance Schema correctly.
skip_name_resolve makes it faster.
Some other variables: thread_cache_size, max_connections, turn off query cache, thread_pool_size, temp tables (tmp_table_size, max_heap_table_size), table_open_cache, table_open_cache, table_definition_cache, max_allowed_packet (if you have big data), innodb_flush_method, innodb_adaptive_flushing, slave_compressed_protocol( might help),
Use multi-threaded replication and maybe slave_parallel_type = LOGICAL_CLOCK;
Increasing threads for read, write, purge, concurrency.
Set innodb_log_buffer_size to 64M, innodb_log_file_size to 1G or higher is you have SSD drives.

Temporary Speed Boost

There are times when you need a speed boost. This can be the master or on a Slave. If this becomes a regular habit, consider setting the server for upgrading, horizontal scaling, or vertical scaling, a cache like Redis, load balancing, sharding, etc. I might add more variables here in the future.

innodb_flush_log_at_trx_commit=1 is very normal for use. If you want to speed it up temporarily you can set it to 2, which is mostly safe. "0" is even faster, but dangerous. I wouldn't do "0" on a Master.
sync_binlog is normally 1, set it to 0 to speed a Slave up temporarily. Setting higher than 1 make it faster, maybe, and it will effect binlog_group_commit_sync_delay.
Run pt-stalk when problems occur. It may help. Not really a speed boot now, but it may help for the future. Solar Winds and other monitoring systems can also provide information on this.

Galera

I don't have much to say on Galera right now. I only have had a few instances where I had to figure out issues.