I have no idea why, perhaps one of my sites has had a big spike in traffic or perhaps there’s some other reason, but two of my digital ocean droplets running EE have had mysql crashing once or twice a day since updating to the latest EE. Is there anything I can use to auto-start mysql when it crashes? Just something I can use until I migrate one or two of the sites off of the droplet?
Sure thing, here’s one:
>> MySQLTuner 1.0.1 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-0ubuntu0.12.04.2
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 25M (Tables: 181)
[--] Data in InnoDB tables: 16M (Tables: 137)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 163
-------- Performance Metrics -------------------------------------------------
[--] Up for: 17h 8m 59s (325K q [5.267 qps], 5K conn, TX: 3B, RX: 60M)
[--] Reads / Writes: 46% / 54%
[--] Total buffers: 268.0M global + 2.7M per thread (70 max threads)
[OK] Maximum possible memory usage: 456.1M (45% of installed RAM)
[OK] Slow queries: 0% (0/325K)
[OK] Highest usage of available connections: 61% (43/70)
[OK] Key buffer size / total MyISAM indexes: 6.0M/3.4M
[OK] Key buffer hit rate: 98.5% (194K cached / 2K reads)
[OK] Query cache efficiency: 80.0% (199K cached / 249K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
[!!] Temporary tables created on disk: 37% (1K on disk / 4K total)
[OK] Thread cache hit rate: 99% (46 created / 5K connections)
[OK] Table cache hit rate: 22% (372 open / 1K opened)
[OK] Open file limit used: 40% (416/1K)
[OK] Table locks acquired immediately: 99% (99K immediate / 99K locks)
[OK] InnoDB data size / buffer pool: 16.2M/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
tmp_table_size (> 86M)
max_heap_table_size (> 86M)And here’s another:
>> MySQLTuner 1.0.1 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.37-0ubuntu0.12.04.1
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 28M (Tables: 122)
[--] Data in InnoDB tables: 896K (Tables: 27)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 51
-------- Performance Metrics -------------------------------------------------
[--] Up for: 19h 33m 50s (233K q [3.320 qps], 2K conn, TX: 492M, RX: 166M)
[--] Reads / Writes: 57% / 43%
[--] Total buffers: 198.0M global + 2.7M per thread (50 max threads)
[OK] Maximum possible memory usage: 332.4M (67% of installed RAM)
[OK] Slow queries: 0% (0/233K)
[OK] Highest usage of available connections: 8% (4/50)
[OK] Key buffer size / total MyISAM indexes: 6.0M/2.5M
[OK] Key buffer hit rate: 99.8% (1M cached / 2K reads)
[OK] Query cache efficiency: 48.1% (78K cached / 163K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 552 sorts)
[OK] Temporary tables created on disk: 25% (160 on disk / 622 total)
[OK] Thread cache hit rate: 99% (4 created / 2K connections)
[!!] Table cache hit rate: 15% (190 open / 1K opened)
[OK] Open file limit used: 28% (292/1K)
[OK] Table locks acquired immediately: 100% (145K immediate / 145K locks)
[OK] InnoDB data size / buffer pool: 896.0K/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
table_cache (> 400)
Anything stand out as a possible cause?
Hi coreyallen,
you need to tune your mysql-server
the above output is showing variables to adjust
just adjust their values in /etc/mysql/my.cnf and then restart mysql.
I’ll be honest here, I used EasyEngine pre-2.0 and then post 2.0 and never had a stable site using it.
Having followed directions properly, allowing an RTcamp dev to look at my server and following mysqltuner advice the crashes continued to persist. The MySQL crashing is what turned me off from EasyEngine – I know I am not the only one because I noticed numerous people mentioning the same problem on reddit. Even after ammending my.cnf the mysql crashes persisted. Allbeit I’m using a 512mb DO server there is no reason that the tuning should stop working and cause my entire site to stay down. Tweaks made and lessons learned I’ve gone back to Centminmod where stability remains a non concern. I think the top priority for EasyEngine should be making the releases stable and not focusing on new features until that is done. Best of luck with the project and thank you for all your support during my time using this software.
Yeah when I first got EE I experienced no crashing. I have one site where I followed every mysqltuner suggestion but still had mysql crashing. I switched from EE to Server Pilot and I haven’t had any issues plus my sites load times are loading slightly faster. Now I’ll never stop using EE since I want to support a great team and a great product, but I’ll only be able to use it on my sites that only get a few hundred hits per day.
Yeah the EE team is truly awesome, once they cross this hurdle there would be literally no reason for me not to use it.
I’ve continued to get crashes even after following the steps suggested by mysqltuner. This is one WP multisite on a 512 DO ubuntu box.
Hi coreyallen,
as you said you are running site on DO 512 ubuntu box. For this box you need to create swap space
use this .
also make changes in this file vim /etc/php5/fpm/pool.d/www.conf
and set pm.max_children = 20
Hey thanks
That value is currently set to 100 and I already have a swap setup using that tutorial, but I’ll change the max to 20 tomorrow. Thanks so much for the help!
This is awesome thanks so much guys.
I had already tweaked mysqltuner over the course of weeks so I was at my wits end. I already transferred one set of sites to another server but just changed pm.max_children. However the Wiki says change it to 10 and you suggested 20 so I took your suggestion.
Yep so far so good. Thanks so much for the help. I was at my wits end.
Hi,
Glad to know your issue is solved.