SQL error a couple times/day


#1

Hi all,
I’m running EE on a 512 Digital Ocean Droplet with one site with low traffic. Several times per day I’ll get “Error establishing a database connection” error. ee stack restart fixes the issue but any ideas on how I can fix this? Thanks!


#2

Hello @coreyallen

Could you please provide me output of following command :

$ mysqltuner

ee stack restart fixes the issue

This seems to me may be your low configuration of 512MB RAM memory is causing the issue, it is frequently stopping the mysql server due to shortage of memory. I would suggest you to upgrade your RAM memory according to usage .


#3

Hi there, I have quite a few other sites running fine at 512, not sure why this would be different here’s the output [OK] Logged in using credentials from debian maintenance account.

MySQLTuner 1.6.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 [–] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 10.0.21-MariaDB-1~trusty-log [OK] Operating on 64-bit architecture

-------- Storage Engine Statistics ------------------------------------------- [–] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM [–] Data in MyISAM tables: 31M (Tables: 217) [–] Data in InnoDB tables: 2M (Tables: 14) [!!] Total fragmented tables: 19

-------- Security Recommendations ------------------------------------------- [OK] There is no anonymous account in all database users [OK] All database users have passwords assigned [!!] There is not basic password file list !

-------- Performance Metrics ------------------------------------------------- [–] Up for: 42m 40s (10K q [4.159 qps], 431 conn, TX: 115M, RX: 1M) [–] Reads / Writes: 95% / 5% [–] Binary logging is enabled (GTID MODE: OFF) [–] Total buffers: 624.0M global + 7.4M per thread (100 max threads) [!!] Maximum reached memory usage: 653.6M (133.38% of installed RAM) [!!] Maximum possible memory usage: 1.3G (278.48% of installed RAM) [OK] Slow queries: 0% (0/10K) [OK] Highest usage of available connections: 4% (4/100) [OK] Aborted connections: 0.00% (0/431) [OK] Query cache efficiency: 30.3% (3K cached / 12K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 415 sorts) [!!] Temporary tables created on disk: 84% (619 on disk / 735 total) [OK] Thread cache hit rate: 99% (4 created / 431 connections) [OK] Table cache hit rate: 113% (299 open / 263 opened) [OK] Open file limit used: 47% (491/1K) [OK] Table locks acquired immediately: 99% (5K immediate / 5K locks) [OK] Binlog cache memory access: 100.00% ( 159 Memory / 159 Total)

-------- MyISAM Metrics ----------------------------------------------------- [!!] Key buffer used: 18.7% (25M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/5.7M [!!] Read Key buffer hit rate: 94.8% (59K cached / 3K reads) [!!] Write Key buffer hit rate: 0.0% (248 cached / 248 writes)

-------- InnoDB Metrics ----------------------------------------------------- [–] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 256.0M/2.8M [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1). [!!] InnoDB Used buffer: 2.64% (432 used/ 16383 total) [OK] InnoDB Read buffer efficiency: 94.72% (7522 hits/ 7941 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 210 writes)

-------- AriaDB Metrics ----------------------------------------------------- [–] AriaDB 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 MySQL started within last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability When making adjustments, make tmp_table_size/max_heap_table_size equal 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 *** tmp_table_size (> 32M) max_heap_table_size (> 32M) innodb_buffer_pool_instances (=1)


#4

Hello @coreyallen

As I said on my previous comment and also from the report of mysqltunner i could see that your system is going shortage of RAM memory .

-------- Performance Metrics ------------------------------------------------- [–]

Maximum reached memory usage: 653.6M (133.38% of installed RAM) [!!]

Maximum possible memory usage: 1.3G (278.48% of installed RAM) [OK]

I would suggest you to upgrade your RAM memory to min 2G .


#5

Thanks This is new behavior since 512 used to be more than enough up until a recent ee update. I moved the domain to a shared host rather than double my hosting costs.


#6