Setting tmp_table_size MySQL (CMS Bitrix)?

Question

MySQL

Hi all! Yes, Bitrix site, Nginx web server.
In general, the difficulty with setting tmp_table_size and max_heap_table_size
Here is what Bitrix writes:
Temporary tables (disk) = 37.65%.
Percentage of temporary tables requiring creation on disk (Created_tmp_disk_tables / (Created_tmp_tables + Created_tmp_disk_tables)). The percentage is over 30% and the tmp_table_size (current value: 2 GB) and max_heap_table_size (current value: 2 GB) parameters must be increased. Make sure the values ​​of these parameters are equal. You may also need to reduce the number of SELECT DISTINCT queries without LIMIT.
Changed the values ​​​​from 128mb to 2gb, the percentage is correlated from 43% to 37%
-bash-4.1# mysqltuner

Result

>>  MySQLTuner 1.1.1 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 729)
[--] Data in InnoDB tables: 1G (Tables: 5005)
[!!] Total fragmented tables: 2808

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 31m 15s (931K q [102.613 qps], 25K conn, TX: 3B, RX: 419M)
[--] Reads / Writes: 54% / 46%
[--] Total buffers: 8.8G global + 48.2M per thread (151 max threads)
[OK] Maximum possible memory usage: 15.9G (50% of installed RAM)
[OK] Slow queries: 0% (15/931K)
[OK] Highest usage of available connections: 15% (24/151)
[OK] Key buffer size / total MyISAM indexes: 2.0G/168.0M
[OK] Key buffer hit rate: 100.0% (4M cached / 11 reads)
[OK] Query cache efficiency: 73.1% (474K cached / 649K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (120 temp sorts / 32K sorts)
[!!] Joins performed without indexes: 1371
[!!] Temporary tables created on disk: 37% (14K on disk / 38K total)
[OK] Thread cache hit rate: 99% (24 created / 25K connections)
[OK] Table cache hit rate: 99% (5K open / 5K opened)
[OK] Open file limit used: 7% (1K/20K)
[OK] Table locks acquired immediately: 100% (485K immediate / 485K locks)
[OK] InnoDB data size / buffer pool: 2.0G/4.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    join_buffer_size (> 16.0M, or always use indexes with joins)

my.cnf file

spoiler

# Example MySQL config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking

tmpdir = /dev/shm

key_buffer_size = 4M
max_allowed_packet = 16M
table_open_cache = 256
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 192K
query_cache_size = 2M
thread_cache_size = 8
tmp_table_size = 1024M
max_heap_table_size = 1024M
transaction-isolation = READ-COMMITTED



# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (using the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking
server-id       = 1

# Uncomment the following if you want to log updates
#log-bin=mysql-bin

# binary logging format - mixed recommended
#binlog_format=mixed

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 100M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
innodb_flush_method = O_DIRECT
max_allowed_packet=64M
interactive_timeout=120
wait_timeout=120
key_buffer_size=2048M
sort_buffer_size=8M
join_buffer_size=8M
read_buffer_size=8M
read_rnd_buffer_size=16M
query_cache_size=128M
query_cache_limit=128M
query_cache_wlock_invalidate
low_priority_updates
max_tmp_tables=64
tmp_table_size=2048M
max_heap_table_size=2048M
bulk_insert_buffer_size=64M
preload_buffer_size=4M
memlock
myisam_use_mmap
innodb_additional_mem_pool_size=512M
innodb_buffer_pool_size=4096M
innodb_log_buffer_size=128M
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_file_per_table
innodb_doublewrite=0
table_definition_cache = 4096
thread_cache_size = 64
table_open_cache = 10240
join_buffer_size = 16M

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

ps-ax | grep mysqld

result

Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ
26056 ?        S      0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
26299 ?        SLl    5:01 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/=.err --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306
30518 pts/1    S+     0:00 grep mysqld

Answer 1

Here you need to look at how long the current mysql configuration is running. The issue of optimizing database settings depends on many factors. So in the forehead it will not be possible to optimally configure everything. My advice to you is to test under load. We corrected a number of parameters and left it for a couple of days / a week. Made a conclusion. I do it myself.
What can be said quickly:
You should be alerted by this parameter "Joins performed without indexes: 1371"
This means that you should go through the queries and see which of them are executed without indexes, put down, for example, infoblock IDs for selects, and so on. Look at this utility here: newrelic.com. It will allow you to get right into the guts and see what is loading. This is about the load on the database server. General recommendations.