P.s. от apache отказался в пользу php-fpm ;)
Объединять я не стал в одну сеть. Я проверил пинг между серверами нормальный:
ping mysite.com
PING mysite.com (xxx.xxx.xx.xx) 56(84) bytes of data.
64 bytes from mysite.com (xxx.xxx.xx.xx): icmp_req=1 ttl=62 time=0.319 ms
64 bytes from mysite.com (xxx.xxx.xx.xx): icmp_req=2 ttl=62 time=0.498 ms
64 bytes from mysite.com (xxx.xxx.xx.xx): icmp_req=3 ttl=62 time=0.223 ms
И сервера с Gb каналом.
Нагрузку всю разрулил:
Но сейчас изредка особенно при онлайне свыше 4000 человек бывает выдаёт:
Can't connect to MySQL server on 'xxx.xxx.xx.xx' (99).
Нагрузка не выскокая:
Привожу свой конфиг my.cnf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
default-character-set = cp1251
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
default_storage_engine=MyISAM
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = xxx.xxx.xx.xxx
#
# * Fine Tuning
#
key_buffer_size = 15360M
join_buffer_size = 16M
max_connect_errors = 30
max_allowed_packet = 32M
table_open_cache = 4096
thread_stack = 384K
thread_cache_size = 500
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
max_user_connections = 700
max_connections = 1400
table_cache = 2048
myisam_sort_buffer_size = 512M
net_buffer_length = 96K
thread_concurrency = 24
#
# * Query Cache Configuration
#
query_cache_type = 1
query_cache_limit = 128M
query_cache_size = 2048M
#
max_heap_table_size=1024M
tmp_table_size=2048M
sort_buffer_size = 128M
read_buffer_size = 256M
read_rnd_buffer_size = 128M
net_read_timeout=500
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
innodb_buffer_pool_size = 1024M
innodb_thread_concurrency = 14
innodb_log_file_size = 5M
innodb_log_buffer_size = 5M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 80
innodb_file_per_table
innodb_thread_concurrency = 24
character-set-server=cp1251
collation-server=cp1251_general_ci
init-connect="SET NAMES cp1251"
skip-character-set-client-handshake
[mysqldump]
quick
quote-names
max_allowed_packet = 32M
default-character-set = cp1251
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
default-character-set = cp1251
[isamchk]
key_buffer = 32M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
mysql> show variables like '%timeout%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 80 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 500 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+----------------------------+----------+
10 rows in set (0.00 sec)
mysql-server проработал безе перезагрузок 29 часов (324 720 271 запросов принял за это время) вот что выдал mysqltuner:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.46-0+deb7u1
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 335M (Tables: 55)
[--] Data in InnoDB tables: 1M (Tables: 2)
[--] Data in MEMORY tables: 237M (Tables: 4)
[!!] Total fragmented tables: 13
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 5h 47m 22s (323M q [3K qps], 43M conn, TX: 1001B, RX: 23B)
[--] Reads / Writes: 64% / 36%
[--] Total buffers: 19.0G global + 528.4M per thread (1400 max threads)
[!!] Maximum possible memory usage: 741.4G (1186% of installed RAM)
[OK] Slow queries: 0% (4/323M)
[OK] Highest usage of available connections: 19% (271/1400)
[OK] Key buffer size / total MyISAM indexes: 15.0G/124.5M
[OK] Key buffer hit rate: 100.0% (4B cached / 1M reads)
[!!] Query cache efficiency: 18.4% (18M cached / 102M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1M sorts)
[!!] Temporary tables created on disk: 33% (316 on disk / 932 total)
[OK] Thread cache hit rate: 99% (271 created / 43M connections)
[!!] Table cache hit rate: 2% (760 open / 37K opened)
[OK] Open file limit used: 11% (787/7K)
[!!] Table locks acquired immediately: 89%
[OK] InnoDB data size / buffer pool: 1.6M/1.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_limit (> 128M, or use smaller result sets)
table_cache (> 2048)
Сервер БД (12 ядер. 64гиг ОЗУ). Сервер морды сайта (8 ядер. 32 гиг ОЗУ). соединяюсь с удалённой базой по айпи.
Нашёл единственный ответ по поводу того что изредка выкидывает Can't connect to MySQL server on 'xxx.xxx.xx.xx' (99). на стековерфлов. что возможно из за тайма-утов. У меня они по умолчанию все кроме innodb_lock_wait_timeout. Может с ними поиграться как нибудь?