I have 6 sites first off are all on the same dedicated server, 2 of the sites get very steady traffic . I would say their is always at least 90 to 120 people on the 2 sites consistently and 15 to 25 on the rest.
The sites run decent currently until the last 3 days when a paid traffic share company was invoked giving the other 4 sites just as much traffic as the main ones all hosted on the same server - causing the site to instantly time out before making a proper request and if it does decide to load after 2 to 3 refreshes takes a few plus 30 seconds before pulling up. The sites will how ever start loading faster once you start clicking inward but will always drop back to shortly.
I've been using mysqltuner to make changes to the my.cnf file - but the settings are not working as hoped. As well as tweaking them to the right tune. the server is 20TB and has more Bandwidth to support its self.
The sites are all word press with WP- Super Cache installed so it should be more than fast. below is my config settings witch I'm sure have become a bit off balance. Please note the Table Errors for clean up are not tables from the WP sites and have nothing to do with the performance.
mysqltuner results
-------- Performance Metrics -------------------------------------------------
[--] Up for: 10h 23m 10s (11M q [295.373 qps], 666K conn, TX: 77G, RX: 1G)
[--] Reads / Writes: 50% / 50%
[--] Binary logging is disabled
[--] Total buffers: 4.8G global + 4.6M per thread (1000 max threads)
[OK] Maximum reached memory usage: 5.3G (67.58% of installed RAM)
[!!] Maximum possible memory usage: 9.3G (119.39% of installed RAM)
[OK] Slow queries: 0% (678/11M)
[OK] Highest usage of available connections: 10% (104/1000)
[OK] Aborted connections: 0.08% (528/666673)
[OK] Query cache efficiency: 69.3% (2M cached / 3M selects)
[!!] Query cache prunes per day: 130766
[OK] Sorts requiring temporary tables: 0% (109 temp sorts / 179K sorts)
[!!] Temporary tables created on disk: 58% (111K on disk / 191K total)
[OK] Thread cache hit rate: 99% (120 created / 666K connections)
[OK] Table cache hit rate: 40% (2K open / 5K opened)
[OK] Open file limit used: 19% (1K/10K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 29.0% (19M used / 68M cache)
[OK] Key buffer size / total MyISAM indexes: 65.0M/64.1M
[OK] Read Key buffer hit rate: 100.0% (30M cached / 13K reads)
[!!] Write Key buffer hit rate: 13.0% (239K cached / 208K writes)
-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 4.0G/8.7G
[!!] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 23.71% (62152 used/ 262144 total)
[OK] InnoDB Read buffer efficiency: 99.99% (390201875 hits/ 390245584 total)
[!!] InnoDB Write Log efficiency: 85.21% (1596393 hits/ 1873419 total)
[OK] InnoDB log waits: 0.00% (0 waits / 277026 writes)
-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.
-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Increasing the query_cache size over 128M may reduce performance
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 128M) [see warning above]
innodb_buffer_pool_size (>= 8G) if possible.
innodb_buffer_pool_instances(=4)
And this is the my.cnf file
[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/dev/shm
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
open_files_limit=10000
query_cache_size=128M
query_cache_type=1
max_connections=1000
max_user_connections=25
wait_timeout=300
tmp_table_size=512M
max_heap_table_size=512M
thread_cache_size=64
key_buffer_size=65M
max_allowed_packet=268435456
table_cache=2048
table_definition_cache=2048
#delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts
#delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts
myisam_sort_buffer_size=32M # can be increased per sessions if needed for alter tables (indexes, repair)
query_cache_limit=2M # leave at default unless there is a good reason
join_buffer=2M # leave at default unless there is a good reason
sort_buffer_size=2M # leave at default unless there is a good reason
#read_rnd_buffer_size=256K # leave at default unless there is a good reason
#read_buffer_size=2M # leave at default unless there is a good reason
collation_server=utf8_unicode_ci
character_set_server=utf8
general_log=0
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;
low_priority_updates=1
innodb_file_per_table=1
innodb_buffer_pool_size=4G # check mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';" - free vs total
innodb_additional_mem_pool_size=62M
innodb_log_buffer_size=62M
innodb_thread_concurrency=8 # Number of physical + virtual CPU's, preset when server is provisioned to have correct # of cores
default-storage-engine=MyISAM
[mysqld_safe]
I did here traffic sharing sites can hurt you from over pinging your server to get stats of traffic they send to you - not sure if this a rabbit hole worth going down or its just poor configuration on my end. Any help, thoughts or ideas would be most appreciated.
Many thanks!
