Ok, we run a growing auto parts shopping cart. Our server runs slow and is always crashing so we asked our web host to assist us. They sent us these report but I need some advice in how to process it.
Our plan right now is to take the MySQL database and host it on AWS, but I need to know how to gauge what size instance we need and what service would work best.
I also need to know any tweaks to apache that would improve performance.
Here is the analysis the web host send us
The way these issues come into play are:
1) The very large RAM footprint of your web application processes over 512M plus improper tuning of Apache that allows these web application processes to consume more RAM than is available on your server.
2) The MySQL service on your server like Apache is tuned to allocate much more RAM than is available on your server. This makes the MySQL service unstable thus will require restarts to fix problems that would not occur if it were properly tuned not to use more RAM than is installed.
APACHE
Your server's Apache MaxClients setting is the default == 256
Your server has 32232 MB of memory
The largest Apache web application process is using 572.73 MB of memory
The smallest Apache web application process is using 35.02 MB of memory
The average Apache web application process is using 94.93 MB of memory
Going by the average Apache process, Apache can potentially use 24302.09 MB RAM (75.40 % of available RAM)
Going by the largest Apache process, Apache can potentially use 146618.89 MB RAM (454.89 % of available RAM)
Your server's Apache MaxClients setting should be no greater than 50 if you were only allocating 100% of your server's RAM to Apache web application processes.
Max potential memory usage: 146618.88 MB(454.89 % of available RAM)
Percentage of RAM allocated to Apache 454.89 %
NOTE: this analysis does not take into account any other processes like the MySQL database service running on your server that also requires significant RAM resources to run efficiently, currently a minimum of 1.2GB of RAM.
MySQL
-------- Performance Metrics -------------------------------------------------
[--] Up for: 14h 11m 51s (9M q [195.147 qps], 41K conn, TX: 22B, RX: 1B)
[--] Reads / Writes: 59% / 41%
[--] Total buffers: 1.6G global + 5.0G per thread (151 max threads)
[!!] Maximum possible memory usage: 756.7G (2403% of installed RAM)
[OK] Slow queries: 0% (2K/9M)
[OK] Highest usage of available connections: 23% (35/151)
[OK] Key buffer size / total MyISAM indexes: 1.0G/2.2G
[OK] Key buffer hit rate: 100.0% (9B cached / 1M reads)
[OK] Query cache efficiency: 90.4% (8M cached / 9M selects)
[!!] Query cache prunes per day: 725309
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 147K sorts)
[!!] Joins performed without indexes: 1544
[!!] Temporary tables created on disk: 47% (96K on disk / 203K total)
[OK] Thread cache hit rate: 99% (59 created / 41K connections)
[!!] Table cache hit rate: 5% (512 open / 10K opened)
[OK] Open file limit used: 1% (910/65K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[!!] Connections aborted: 6%
Suggested MySQL mitigtion:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Your applications are not closing MySQL connections properly have your developers fix the code so MySQL connections are explicitly closed when the query results are returned.