Long queries MySQL, (CentOS, nginx, 1C-Bitrix)?

Question

MySQL

Hi all!
The situation is as follows: very long requests during the backup of 2 sites, on one dedicated server (SQL database backup + file backup, all on cron)
requests in 63 (!!!) seconds, and in normal time page rendering takes 6 seconds, (and other indicators: DNS, server response, etc. is normal, this is Bitrix says)
CentOS system, Nginx server, CMS - Bitrix.
screenshot of query stats in Bitrix
hkar.ru/IsN2
mysqltuner

spoiler

-------- 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: 5007)
[!!] Total fragmented tables: 2811

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4d 1h 11m 49s (57M q [164.937 qps], 811K conn, TX: 183B, RX: 27B)
[--] Reads / Writes: 27% / 73%
[--] 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% (670/57M)
[OK] Highest usage of available connections: 66% (100/151)
[OK] Key buffer size / total MyISAM indexes: 2.0G/168.0M
[OK] Key buffer hit rate: 100.0% (70M cached / 7K reads)
[OK] Query cache efficiency: 86.8% (33M cached / 38M selects)
[!!] Query cache prunes per day: 26825
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 904K sorts)
[!!] Joins performed without indexes: 33045
[!!] Temporary tables created on disk: 43% (598K on disk / 1M total)
[OK] Thread cache hit rate: 99% (141 created / 811K connections)
[OK] Table cache hit rate: 87% (7K open / 8K opened)
[OK] Open file limit used: 7% (1K/20K)
[OK] Table locks acquired immediately: 99% (30M immediate / 30M locks)
[OK] InnoDB data size / buffer pool: 1.9G/4.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    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:
    query_cache_size (> 128M)
    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

cron script

spoiler

#!/bin/bash
echo $(date +%y%m%d)
cd /var/www/
echo "Add site first-site.ru to archive ...Start"
tar -czpf newshop.tar.gz ./newshop
echo "Add site to archive ...Done!"

echo "Move archive to backup directory"
mv newshop.tar.gz /backup/newshop-$(date +%y%m%d).tar.gz
echo "Move archive to backup directory ...Done!"

cd /backup/
echo "Make backup database newshop"
mysqldump -u root newshop > newshop-$(date +%y%m%d).sql
echo "Make backup database newshop ...Done!"

cd /var/www/
echo "Add sec-site.ru site to archive ...Start"
tar -czpf sec-site.tar.gz ./sec-site.ru
echo "Add site to archive ...Done!"

echo "Move archive to backup directory"
mv sec-site.tar.gz /backup/sec-site-$(date +%y%m%d).tar.gz
echo "Move archive to backup directory ...Done!"

cd /backup/
echo "Make backup database sec-site"
mysqldump -u root sec-site > sec-site-$(date +%y%m%d).sql
echo "Make backup database sec-site ...Done!"

top

spoiler

--PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
19211 nginx     20   0 1161m 185m 155m S  7.6  0.6   1:24.39 php-fpm
22024 nginx     20   0 1136m 164m 147m S  6.3  0.5   1:37.11 php-fpm
17054 nginx     20   0 99.4m  57m 2628 S  4.0  0.2  18:39.34 nginx
22033 nginx     20   0 1157m 183m 158m S  2.3  0.6   1:04.35 php-fpm
17053 nginx     20   0 92576  48m 2628 S  1.3  0.2  18:36.35 nginx
26299 mysql     20   0 12.7g 7.5g  13m S  1.3 23.9 169:42.73 mysqld
 1299 root      16  -4 93156  716  588 S  0.3  0.0   1:07.33 auditd
 2230 root      20   0 15412 1684  984 R  0.3  0.0   0:00.03 top
    1 root      20   0 19232  984  832 S  0.0  0.0   0:03.29 init

I changed the settings, tested for three days of change. All the same

Answer 1

The database prevents new data from being added during the backup period so that your backup is consistent across all tables. This is standard behavior.
Consistency is important so that it doesn’t turn out that you started backing up, backed up the first table, go to the second, and at that time the site wrote something to the first table, adding a related record to the third table. The backup will contain up-to-date data for the second, third, but not the first table.
If you want to backup without locking tables, but data consistency is important to you, you can:
If you want to backup without locking tables, but you don't need data consistency:
mysqldump --single-transaction ...