anthonyaccetturo's picture

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 besides the ones that involve analyizing the code and joins as we have developers working on that. Any tools to diagnose this will be appreicated. Our server is also running RHEL if that helps.

 

 

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.

Forum: 
Jeremy Davis's picture

TurnKey Linux is a Debian based headless server OS. We build quite generic products that are intended as a "good starting point"; not necessarily as a tuned specialised product. Having said that, we have many DIY and/or SOHO and SMB users that successfully use TurnKey appliances "as is". We also have many IT consultant and IT specialist users who use TurnKey as a base (which they then tune and specialise themselves - as it sounds you are seeking to do).

One of our community members may be able to help you out, but it will be from a perspective of a Debian based system (theoretically it should be somewhat irrelevant but never having used RHEL or any of it's derivatives I can't be sure). The info you are seeking will be quite specific to you and your requirements. Although I note that you provide enough info for at least a preliminary recommendation from someone that knows enough about it!

FWIW TurnKey provide both a standalone MySQL appliance appliance as well as a LAMP that may be of interest to you. We also have a web UI called the Hub for managing AWS servers and backups.

Finally my thought is that once you have tuned your Apache and DB a little then you can run some more tests on how much RAM your system is actually using and re-evaluate. Then you'll be in a much better place to judge what size DB server you'll need. Although personally I'd be inclined to just jump in and try one out. The beauty of AWS is that you don't have any long term commitment (you can pay for server usage by the hour). So if you start crashing a Small instance; then test a Medium! Once you have fine tuned things and know what size server works best; then you can save yourself some coin and 'reserve' it (i.e. AWS talk for making a longer term commitment). :)

Add new comment