Database is Dell R620.
2x 10 Core Intel Intel(R) Xeon(R) CPU E5-2660 v2 @ 2.20GHz
64 GB Memory
Raid 710P 512MB Cache
2x SSD OS Raid 1
6x SSD OS Raid 10
##My.CnF##
log-error = /var/log/mysql/mysqld.log
log_bin=/var/lib/mysql/mysql-bin
relay_log=/var/lib/mysql/mysql-relay-bin
binlog_format=mixed
datadir = /srv/mysql/data
server-id = 1
slave-skip-errors = 1032,1690,1062
slave_parallel_threads=2
slave-parallel-mode=optimistic
port = 3306
socket = /var/run/mysql/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION
skip-external-locking
skip-name-resolve
connect_timeout=60
long_query_time=3
slow-query-log=1
log_slow_verbosity=query_plan,explain
slow-query-log-file=/var/lib/mysql/mysqld-slow.log
max_connections = 2048
key_buffer_size = 10G
max_allowed_packet = 256M
table_definition_cache = 3000
table_open_cache = 8192
sort_buffer_size = 8M
net_buffer_length = 8K
read_buffer_size = 8M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 256M
query_cache_size=0 # This is a major bottleneck when you have a lot of concurrent connections so it's disabled by default
query_cache_type=0
default-storage-engine=MyISAM
expire_logs_days = 3
concurrent_insert = 2
myisam_recover_option=DEFAULT
myisam_repair_threads = 4
tmpdir = /tmp/
thread_cache_size=100
join_buffer_size=1M
myisam_use_mmap=1
key_cache_segments=64
open_files_limit=24576
max_heap_table_size=1024M
tmp_table_size = 512M # This might be a problem if you have huge report datasets but most people never have an issue
thread_concurrency=40
##MySql Tuner##
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MRG_MyISAM +SEQUENCE
[--] Data in MEMORY tables: 116M (Tables:
[--] Data in MyISAM tables: 18G (Tables: 261)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 7
-------- Performance Metrics -------------------------------------------------
[--] Up for: 15h 7m 13s (204M q [3K qps], 9M conn, TX: 111B, RX: 29B)
[--] Reads / Writes: 79% / 21%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 10.8G global + 49.3M per thread (2048 max threads)
[OK] Maximum reached memory usage: 49.3G (78.38% of installed RAM)
[!!] Maximum possible memory usage: 109.3G (173.91% of installed RAM)
[OK] Slow queries: 0% (182/204M)
[OK] Highest usage of available connections: 39% (800/2048)
[OK] Aborted connections: 0.00% (34/9448126)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (5 temp sorts / 19M sorts)
[OK] Temporary tables created on disk: 1% (2K on disk / 183K total)
[OK] Thread cache hit rate: 99% (47K created / 9M connections)
[OK] Table cache hit rate: 39% (2K open / 5K opened)
[OK] Open file limit used: 8% (2K/24K)
[!!] Table locks acquired immediately: 94%
[OK] Binlog cache memory access: 0% ( 0 Memory / 0 Total)
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 39.6% (4B used / 10B cache)
[OK] Key buffer size / total MyISAM indexes: 10.0G/6.7G
[OK] Read Key buffer hit rate: 99.9% (2B cached / 2M reads)
[!!] Write Key buffer hit rate: 51.0% (115M cached / 56M writes)
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is disabled.
[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine
-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
-------- Replication Metrics -------------------------------------------------
[--] This server is acting as master for 2 server(s).
[--] No replication setup for this server.
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (>= 8M)
##EDIT##
We started noticing that the port speed on the web server was more and more starting to change speed from 100MBPS to 1000MBPS. Upon review of the switch we we were seeing lots of packet errors on the port. Swapped cables and never looked back. Cheers!
|| DB Schema Version: 1609 || Asterisk 11.25.1-vici || BUILD: 190902-0839 ||VERSION: 2.14-718a||SVN: 3133||10xTelephony||1x Database||1x Slave||1x Web||1x Archive||ViciBox v.8.0.1