Всем Привет! Есть, сайт на Битриксе, веб-сервер Nginx.
В общем сложность с настройкой tmp_table_size и max_heap_table_size
Вот что пишет битрикс:
Временные таблицы (диск) = 37.65%.
Процент временных таблиц потребовавших создание на диске (Created_tmp_disk_tables / (Created_tmp_tables + Created_tmp_disk_tables)). Процент более 30% и требуется увеличить параметры tmp_table_size (текущее значение: 2 ГБ) и max_heap_table_size (текущее значение: 2 ГБ). Убедитесь, что значения этих параметров равны. Так же возможно требуется сократить количество SELECT DISTINCT запросов без LIMIT.
Изменял значения от 128мб до 2гб, процент коррелируется от 43% до 37%
-bash-4.1# mysqltuner
Результат>> MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
>> 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
спойлер# 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
результат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
Простой
Средний
