Recently I've noticed that my server load sometimes gets pretty high and will result in "unexpected database error has occurred" and using Chrome inspect shows "already reached max_connections".
I'm on HostGator's Standard dedicated server. With only one fairly active Xenforo forum, usually around 800-1k users on at any given time.
Here is my my.cnf settings:
Running mysql tuner gives:
I'm on HostGator's Standard dedicated server. With only one fairly active Xenforo forum, usually around 800-1k users on at any given time.
Here is my my.cnf settings:
Code:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
open_files_limit=3072
query_cache_size=64M
max_connections=150
max_user_connections=25
wait_timeout=20
tmp_table_size=119M
max_heap_table_size=119M
thread_cache_size=64
key_buffer_size=32M
max_allowed_packet=16M
table_cache=768
table_definition_cache=3072
myisam_sort_buffer_size=2M # can be increased per sessions if needed for alter tables (indexes, repair)
collation_server=utf8_unicode_ci
character_set_server=utf8
slow_query_log=1
log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;
long_query_time=5 # select * from mysql.slow_log order by start_time desc limit 10;
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=119M # check mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';" - free vs total
innodb_additional_mem_pool_size=29M
innodb_log_buffer_size=29M
innodb_thread_concurrency=8 # Number of physical + virtual CPU's, preset when server is provisioned to have correct # of cores
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pidRunning mysql tuner gives:
Code:
>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.36-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 348M (Tables: 26)
[--] Data in InnoDB tables: 1G (Tables: 361)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 8M (Tables: 8)
[!!] Total fragmented tables: 65
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 22h 7m 18s (19M q [241.694 qps], 1M conn, TX: 862B, RX: 5B)
[--] Reads / Writes: 69% / 31%
[--] Total buffers: 392.0M global + 2.8M per thread (150 max threads)
[OK] Maximum possible memory usage: 804.5M (21% of installed RAM)
[OK] Slow queries: 0% (1K/19M)
[OK] Highest usage of available connections: 52% (78/150)
[OK] Key buffer size / total MyISAM indexes: 32.0M/317.4M
[OK] Key buffer hit rate: 99.9% (1B cached / 1M reads)
[OK] Query cache efficiency: 71.3% (10M cached / 14M selects)
[!!] Query cache prunes per day: 85252
[OK] Sorts requiring temporary tables: 0% (761 temp sorts / 445K sorts)
[!!] Joins performed without indexes: 177065
[!!] Temporary tables created on disk: 47% (190K on disk / 398K total)
[OK] Thread cache hit rate: 99% (78 created / 1M connections)
[OK] Table cache hit rate: 47% (768 open / 1K opened)
[OK] Open file limit used: 4% (126/3K)
[OK] Table locks acquired immediately: 99% (10M immediate / 10M locks)
[!!] Connections aborted: 6%
[!!] InnoDB buffer pool / data size: 119.0M/1.8G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (> 64M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 119M)
max_heap_table_size (> 119M)
innodb_buffer_pool_size (>= 1G)
