2

We have a dedicated server with a few applications running including a Vbulletin forum and Wordpress site. The main resource intensive application is a PHP chat application that utilizes MYSQL also.

Every day at around the same time the server seems to go offline or locks up. CPU load is higher but still within range. I do not see any events running. I suspect it is something happening with MYSQL and it goes down as whenever i restart MYSQL everything seems to fix, but that could be a coincidence.

The server has Cpanel installed. Engintron plugin for Nginx and MariaDB 10.1 for database. CentOS 7.

Here are my MYSQLTuner results:

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.33-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in MyISAM tables: 2G (Tables: 310)
[--] Data in InnoDB tables: 28G (Tables: 308)
[--] Data in MEMORY tables: 16M (Tables: 3)
[!!] Total fragmented tables: 111
[!!] failed to execute: SHOW TABLE STATUS FROM \`#mysql50#.ssh\`
[!!] FAIL Execute SQL / return code: 256

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 9h 41m 49s (37M q [182.400 qps], 12M conn, TX: 116G, RX: 12G)
[--] Reads / Writes: 85% / 15%
[--] Binary logging is disabled
[--] Physical Memory     : 62.7G
[--] Max MySQL memory    : 51.0G
[--] Other process memory: 2.5G
[--] Total buffers: 35.1G global + 16.3M per thread (1000 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 35.9G (57.19% of installed RAM)
[OK] Maximum possible memory usage: 51.0G (81.37% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (518/37M)
[OK] Highest usage of available connections: 4% (46/1000)
[OK] Aborted connections: 0.17%  (20794/12263809)
[!!] Query cache efficiency: 10.6% (2M cached / 24M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (22 temp sorts / 6M sorts)
[!!] Joins performed without indexes: 1016
[OK] Temporary tables created on disk: 0% (5K on disk / 15M total)
[OK] Table cache hit rate: 74% (1K open / 1K opened)
[OK] Open file limit used: 0% (787/1M)
[OK] Table locks acquired immediately: 99% (52M immediate / 52M locks)

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 12 thread(s).
[--] Using default value is good enough for your version (10.1.33-MariaDB)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 20.2% (867M used / 4B cache)
[OK] Key buffer size / total MyISAM indexes: 4.0G/617.7M
[OK] Read Key buffer hit rate: 100.0% (1B cached / 82K reads)
[!!] Write Key buffer hit rate: 42.3% (111K cached / 64K writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 97.9% (253K cached / 5K reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 30.0G/28.9G
[!!] InnoDB buffer pool instances: 20
[!!] InnoDB Used buffer: 54.49% (1071288 used/ 1966060 total)
[OK] InnoDB Read buffer efficiency: 99.99% (13800614528 hits/ 13801405527 total)
[OK] InnoDB Write log efficiency: 95.40% (68361750 hits/ 71657060 total)
[!!] InnoDB log waits: 0.00% (39 waits / 3295310 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Variables to adjust:

query_cache_limit (> 1M, or use smaller result sets)

join_buffer_size (> 4.0M, or always use indexes with joins)

innodb_buffer_pool_instances(=30)

innodb_log_buffer_size (>= 8M)

Thanks in advance for any help here.

UPDATE: The issue seems to have been alot of load on the server. I had csf installed and it was scanning to server too much so i took that down a notch. I also made some adjustments to my.cnf. This seems to have helped alot.

Administrator Dan
  • 31
  • 3
  • 2
    Your question is unclear. It says the server crashes, but you say you restart MySQl and it works again. That's not a server crash, that's likely a service problem. When this happens is your memory full? What does the PHP error log / web server log / syslog say? – Tim Jun 19 '18 at 04:16
  • 1
    Hi tim. Well all my sites are down but i can barely access WHM to restart MYSQL, but its very slow. I guess its not crashed but definitely lagging badly. Memory is fine. Has 64gb or ram. Any recommendations on which logs to check. I'm pretty new to all this. – Administrator Dan Jun 19 '18 at 04:58
  • 1
    Unrelated, but the reference to `#mysql50#.ssh` is a bit suspicious, hopefully just a misconfiguration rather than evidence of a compromise. This suggests that you have a stray `.ssh` directory inside your mysql data directory, which definitely should not be there. – Michael - sqlbot Jun 19 '18 at 07:42
  • 1
    @Michael-sqlbot Thanks i deleted a .ssh folder that was in there. – Administrator Dan Jun 24 '18 at 01:59

2 Answers2

1

Here are my new results after some tweaks: (I don't get those crashes anymore after mainly using some csf tweaks to control the amount of time it scans the server)

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 Variables to adjust: query_cache_size (>= 8M) join_buffer_size (> 512.0K, or always use indexes with joins)

Administrator Dan
  • 31
  • 3
0

Suggestions to consider for your my.cnf-ini [mysqld} section

key_buffer_size=3G  # from 4G - only 20% used
key_cache_age_threshold=64800  # from 300 second purge to reduce key_reads
key_cache_division_limit=50  # from 100 for Hot/Warm split of RAM
key_cache_block_size=16384  # from 1024 to reduce CPU usage to manage discards
query_cache_type=0  # for No QC that is only 10% efficient
query_cache_size=0  # from ? to use RAM for more useful purpose 
query_cache_limit=0  # from 1M to use RAM for more useful purpose
innodb_buffer_pool_size=38G  # from 30G for 60% of RAM remember CHG % setaside
innodb_buffer_pool_instances=8  # from 20 to continue to avoid mutex contention
innodb_lru_scan_depth=100 # from 1024 to save CPU every second - see refman
innodb_log_file_size=32M  # from ? for about 1hr before rotation
innodb_log_buffer_size=16M  # from 8M indicated by Mysqltuner
max_connections=100  # from 1000 - max_used_connections was 46 over 2 days

. to allow defaults to work for you and conserve RAM footprint, . LEAD the following with # and spacebar for defaults to be effective.

sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size

please see my profile for contact information.

We would like to know of your progress from time to time, please.

Wilson Hauck
  • 472
  • 5
  • 11
  • 1
    I have made some changes within csf to solve the problem. It has helped reduce the load significantly. I have also made some of the adjustments you advised above. Still getting a couple warnings: 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 Variables to adjust: query_cache_size (>= 8M) join_buffer_size (> 512.0K, or always use indexes with joins) – Administrator Dan Jun 24 '18 at 14:31
  • @AdministratorDan Everything Mysqltuner advises is NOT always correct. See my profile for contact info, please and get in touch and refer to your site and question #. Can you tell me where you stopped in the Suggestion list or did you pick and choose? If anything helped, upvote/accept, if you have enough reputation, please. – Wilson Hauck Jun 25 '18 at 00:00
  • @AdministratorDan Any chance you have 15 minutes for a Skype session on Friday? My Skype ID is wlhauck at aol dot com. Looking forward to talking with you. Thanks, Wilson – Wilson Hauck Jun 28 '18 at 20:09
  • @dministrator Please tell us what WHM and CSF stands for? Need to eliminate any obscure abbreviations. Thanks – Wilson Hauck Jul 02 '18 at 00:01
  • @AdministratorDan Are you past your server-crashing-everyday? If any suggestions have been helpful, please upvote and possibly Accept details that assisted in avoiding daily crashes. – Wilson Hauck Jul 14 '18 at 15:07